FYI — **if you're using D1's new `--experimental-backend` and want to try D1's new Time Travel featu

FYI — if you're using D1's new
--experimental-backend
--experimental-backend
--experimental-backend
--experimental-backend and want to try D1's new Time Travel feature — you now can. This is a work-in-progress but sharing here. This preview branch may break at any time.

You can now use a temporary wrangler branch
```
npx wrangler@d1 d1 time-travel restore DB_NAME --timestamp=(unix timestamp|RFC3339 timestamp)
```
npx wrangler@d1 d1 time-travel restore DB_NAME --timestamp=(unix timestamp|RFC3339 timestamp)
```
npx wrangler@d1 d1 time-travel restore DB_NAME --timestamp=(unix timestamp|RFC3339 timestamp)
```
npx wrangler@d1 d1 time-travel restore DB_NAME --timestamp=(unix timestamp|RFC3339 timestamp)
to restore a database to any minute within the last 30 days.
Our preview documentation is at https://silverlock-d1-time-travel.cloudflare-docs-7ou.pages.dev/d1/learning/time-travel/#docs-content
For the initial release,
```
d1 time-travel restore DB_NAME
```
d1 time-travel restore DB_NAME
```
d1 time-travel restore DB_NAME
```
d1 time-travel restore DB_NAME
overwrites your existing database (with confirmation). In the future, we'll allow you to fork and/or clone to a new DB or over the top of an existing database.
You can read more about Time Travel from our original announcement post back in May: https://blog.cloudflare.com/d1-turning-it-up-to-11/#time-travel

LLiudmila is it correct that the plan is to make time travel default and thus we won't nee...

Matt SilverlockOP•7/12/23, 8:46 PM

Per the docs, yes: Time Travel will be the default. We address durability by writing databases to cold (object) storage: this is where idle databases are restored from.

Way too early to comment on anything non-JSON. Unlikely to be Protobuf.

Hello, I’m Allie!•7/12/23, 8:56 PM

https://capnproto.org/ time! /s

slowpoke•7/12/23, 10:20 PM

If I already created a D1 database on the dashboard, how can I interact with it during local development?

slowpoke•7/12/23, 10:22 PM

my wrangler.toml is this:

[[d1_databases]]
binding="DB"
database_name = "mydbname"
database_id = "db id string from the dashboard"

[[d1_databases]]
binding="DB"
database_name = "mydbname"
database_id = "db id string from the dashboard"

although when doing wrangler pages dev, it's undefined

slowpoke•7/12/23, 10:22 PM

honestly I'm just completely lost

slowpoke•7/13/23, 12:06 AM

Ok I've figured out what I need to do now, but it does feel janky.

slowpoke•7/13/23, 12:08 AM

I have my binding working in Qwik, once the site is uploaded to Cloudflare Pages.

However, locally, the only way I can see testing with the "same" D1 db that's on cloudflare is by making a bootstrap.sqlbootstrap.sql / db-setup.sqldb-setup.sql similar to the one in the tutorial, executing that with fake data, and going on from there.

Sslowpoke If I already created a D1 database on the dashboard, how can I interact with it ...

Matt SilverlockOP•7/13/23, 2:05 AM

Something the Pages team is working on improving. In the meantime (and this is not officially supported; could break/change in the future):

Create a
```
wrangler.toml
```
```
wrangler.toml
```
with a preview_database_idpreview_database_id key that maps to the same name used for --d1=<BINDING>--d1=<BINDING> you pass to wrangler pages devwrangler pages dev
- e.g. "DB" or "PROD_DB" (as appropriate)
Execute wrangler d1wrangler d1 commands with the
```
--local
```
```
--local
```
flag against that local database to seed it / run migrations / etc - e.g. wrangler d1 execute db-name --file=seed.sql --localwrangler d1 execute db-name --file=seed.sql --local
Ensure that you pass --d1=BINDING_NAME--d1=BINDING_NAME to any wrangler pages devwrangler pages dev commands so that Pages references the same local DB.

Sslowpoke I have my binding working in Qwik, once the site is uploaded to Cloudflare Pages...

Matt SilverlockOP•7/13/23, 2:06 AM

Your

wrangler.toml

wrangler.toml

doesn't need anything more than just a [[d1_database]][[d1_database]] definition: e.g. https://github.com/elithrar/remix-plus-d1/blob/main/wrangler.toml#L1-L5

katopz•7/13/23, 4:21 AM

My feedback

# This will yell Unknown argument: local
wrangler d1 create SUBSCRIPTIONS --local

# That's OK, it's local by default so I try
wrangler d1 create SUBSCRIPTIONS

# Then migrate...
wrangler d1 migrations apply SUBSCRIPTIONS

# Then run the app and got...
Uncaught (async) Error: D1_ERROR: Error: SqliteError: no such table: Subscriptions

# So i realized that I will need --local for migration (Took me an hour)
wrangler d1 migrations apply SUBSCRIPTIONS --local

# This will yell Unknown argument: local
wrangler d1 create SUBSCRIPTIONS --local

# That's OK, it's local by default so I try
wrangler d1 create SUBSCRIPTIONS

# Then migrate...
wrangler d1 migrations apply SUBSCRIPTIONS

# Then run the app and got...
Uncaught (async) Error: D1_ERROR: Error: SqliteError: no such table: Subscriptions

# So i realized that I will need --local for migration (Took me an hour)
wrangler d1 migrations apply SUBSCRIPTIONS --local

So some is needed

--local

--local

some is not. My expectation is all should default to

--local

--local

and

--remote

--remote

when needed.

# local
wrangler d1 create SUBSCRIPTIONS
wrangler d1 migrations apply SUBSCRIPTIONS

# remote
wrangler d1 create SUBSCRIPTIONS --remote
wrangler d1 migrations apply SUBSCRIPTIONS --remote

# local
wrangler d1 create SUBSCRIPTIONS
wrangler d1 migrations apply SUBSCRIPTIONS

# remote
wrangler d1 create SUBSCRIPTIONS --remote
wrangler d1 migrations apply SUBSCRIPTIONS --remote

Would be nicer.

Kkatopz My feedback ``` # This will yell Unknown argument: local wrangler d1 create SUBS...

Matt SilverlockOP•7/13/23, 10:40 AM

Remote is always the default for any non-local-dev (wrangler dev, wrangler pages dev) commands. You’re creating databases: you don’t want to think you’ve created one and find out on deploy it doesn’t exist.

samratarmas•7/13/23, 11:37 AM

Hi team, does D1 support using sqlite extensions? For example, if I want to use full text search capability there is a built in Fts5 extension: https://www.sqlite.org/fts5.html, can I use this in D1 without additional configuration?

samratarmas•7/13/23, 11:47 AM

And second question, is js transactions coming anytime this year or is that a much longer term feature? ( read a value from d1, use that read value to do some calculation then write the result back, all inside a transaction)

MMatt Silverlock Your `wrangler.toml` doesn't need anything more than just a `[[d1_database]]` de...

slowpoke•7/13/23, 1:04 PM

Thanks for your response :) Yep! This is basically what I have right now and it works great!

I suppose once DB backups are available to download, it would be nice to download that to use for local development. Making a seed in the meantime is nice anyways because it declares the database structure for reference

Ssamratarmas Hi team, does D1 support using sqlite extensions? For example, if I want to use ...

Matt SilverlockOP•7/13/23, 1:08 PM

FTS5 is built in. On our list to include in our documentation, but works exactly as the SQLite docs say. We also support JSON5 - https://developers.cloudflare.com/d1/learning/querying-json/

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

micromashor•7/13/23, 6:06 PM

I keep randomly encountering this error while using wrangler d1 executewrangler d1 execute: (some details redacted)

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

micromashor•7/13/23, 6:09 PM

it's also weird bc about half the time I see the error, the query went through successfully

Matt SilverlockOP•7/13/23, 6:55 PM

Are you using the new experimental backend? You can (should) log error.cause.messageerror.cause.message as well - https://developers.cloudflare.com/d1/platform/client-api/#errors

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

Original message was deleted

micromashor•7/14/23, 1:27 AM

yes that's correct - it was produced by wrangler

MMatt Silverlock Are you using the new experimental backend? You can (should) log `error.cause.me...

micromashor•7/14/23, 2:14 AM

and yes, I am using the experimental backend. I was trying to do an INSERTINSERT operation with fewer than 20 rows, so I can't imagine it was failing due to any kind of size limit

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

Matt SilverlockOP•7/14/23, 11:35 AM

Without more details it’s hard to debug. Is the data static? Not all rows are created equal.

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

Matt SilverlockOP•7/14/23, 11:54 AM

I’d take a look through https://developers.cloudflare.com/d1/platform/limits/ to make sure you aren’t hitting limits. We have some changes queued up that will better expose error messages.

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public alpha. Join the #d1-open-alpha channel in the Cloudflare Developer Discord to keep up to date …

Murder Chicken•7/14/23, 12:31 PM

I seem to be getting a lot of Error: D1_ERROR: Error: Network connection lost.Error: D1_ERROR: Error: Network connection lost. errors with fairly simple (yet critical) queries my worker is running. The DB is using the experimental d1d1 backend. Is there a tried and true way to get to the bottom of why this is happening?

MMurder Chicken I seem to be getting a lot of `Error: D1_ERROR: Error: Network connection lost.`...

Matt SilverlockOP•7/14/23, 2:43 PM

Can you share your database ID here, and an example query? Is this deterministic - e.g. a specific query always causes the error?

Murder Chicken•7/14/23, 2:45 PM

Seems to be, will DM

Murder Chicken•7/14/23, 2:52 PM

@Matt sent.

Original message was deleted

itsmatteomanf•7/14/23, 3:51 PM

Do you have a primary key column?

itsmatteomanf•7/14/23, 3:55 PM

When you configure a database you need a primary key column (or columns) so that the database knows what data should unique. If you don't set that up, how would the DB know what is new and what is data to be replaced?

Original message was deleted

itsmatteomanf•7/14/23, 3:57 PM

Yes!

Original message was deleted

itsmatteomanf•7/14/23, 4:03 PM

I'd personally learn to use an actual SQL query (either via the browser console, or directly from wrangler d1 executewrangler d1 execute), which allows more granular control than the Ui in the dashboard, and repeatable creations.

itsmatteomanf•7/14/23, 4:03 PM

But this would work.

MMatt Silverlock Without more details it’s hard to debug. Is the data static? Not all rows are cr...

micromashor•7/14/23, 6:06 PM

yeah it's 3 text fields (all fields <100chars) and an ID, so I seriously doubt I'm hitting any limits. Will watch out for better error messages though

Lucian•7/14/23, 6:56 PM

,Hi all,

I am having an issue with my D1 database. When attempting to index one of the columns I get the error below:
ERROR 9015: SQL engine error: query error: Error code 11: The database disk image is malformed (database disk image is malformed).

CREATE INDEX idx_teams_team_name ON teams(team_name);

Any idea what can I do to get around this? The table has about 90k rows; I surely need an index to search on this column

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

Cyb3r-Jak3•7/14/23, 7:36 PM

Do you have the database ID? Sounds like something that the D1 team is going to want to look at on their side

CCyb3r-Jak3 Do you have the database ID? Sounds like something that the D1 team is going to ...

Lucian•7/14/23, 7:40 PM

I restored the database to a 1 day old copy and now it works, the index created. I will need to reload about 40k records, but it is what it is

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

Matt SilverlockOP•7/14/23, 9:46 PM

It sounds like you’re using the original alpha backend - that error is very specific to it.

Matt SilverlockOP•7/14/23, 9:47 PM

see the note about the experimental backend - https://developers.cloudflare.com/d1/get-started/#2-create-a-database

Get started · Cloudflare D1 docs

This guide will instruct you through:

staticmedia•7/15/23, 7:15 AM

Hi, is the performance between getting data from KV versus from D1 neglible ? (assuming the DB lookup is a find on PK)

staticmedia•7/15/23, 7:20 AM

I'm building on a website that caches all the data from the headless CMS on cloudflare using KV. When the DB has an update a webhook pings the worker and updates the cache. The website makes requests to the same worker that fetches from the cache, and hydrates the HTML before sending back to the user

staticmedia•7/15/23, 7:21 AM

Because KV doesn't provide cache tagging its quite difficult to manage the cache (with nested data and making sure those nested items stay fresh). I want to store the cache in a D1 so I can more easily lookup data that needs refreshing

Tony Leung•7/15/23, 1:41 PM

I'm pretty curious about this: KV is not transaction-ready, is D1 transaction ready? If I have an user from US made a request to my page function using D1 in APAC, will the request take longer? Is there any worker+D1 replica in US which can handle the request for me? If there is and D1 supports transaction, will there any penalty on performance or request time?

vassbence•7/15/23, 2:47 PM

D1 is single writer multi reader (or at least will be)

vassbence•7/15/23, 2:48 PM

but KV and D1 are entirely different products for different usecases

theLaCzar•7/15/23, 11:55 PM

quick question, i designed microservices with 1ms : 1db design, however, we are limited to 10DB currently per account, knowing i have several sites under my account, so i guess i have to fit them in, my question is does D1 experience the same issues with normal relations db like table locking, running out of resource etc?

theLaCzar•7/15/23, 11:57 PM

because if D1 is efficient enough that table locking is not much of an issue, i guess ill go with a monolitic DB design and lots of workers

TtheLaCzar because if D1 is efficient enough that table locking is not much of an issue, i ...

Matt SilverlockOP•7/16/23, 12:25 AM

You can have multiple concurrent readers. Reads don’t block.

Unsmart•7/16/23, 9:27 PM

Its rounded up. So a unit of 0.00000000000001 would still be 1 full unit

FYI — **if you're using D1's new `--experimental-backend` and want to try D1's new Time Travel featu

Similar Threads

Similar Threads

Similar Threads