Yes: per the blog, you can export (https://developers.cloudflare.com/d1/learning/backups/#downloadin

Yes: per the blog, you can export (https://developers.cloudflare.com/d1/learning/backups/#downloading-a-backup-locally) and then import (https://developers.cloudflare.com/d1/learning/importing-data/#import-an-existing-database) - we’ll be publishing a single guide once we get to beta and make the new backend the default.

We don’t have near term plans for an automatic migration (such is the trade-off for an alpha).

Backups · Cloudflare D1 docs

D1 has built-in support for creating and restoring backups of your databases, including support for scheduled automatic backups and manual backup …

Importing data · Cloudflare D1 docs

D1 allows you to import existing SQLite tables and their data directly, enabling you to migrate existing data into D1 quickly and easily. This can be …

Cyb3r-Jak3•5/25/23, 11:59 PM

Anyone have tips for debugging not authorized [code: 7500]not authorized [code: 7500] when trying to execute an sql file? I can manually run the commands on the same database and table using --command--command but

--file

--file

always fails.

Ggeelen can you post your command/file somewhere? You can DM me if you don't want to sha...

Cyb3r-Jak3•5/26/23, 1:23 AM

If you break it down into smaller files it works

charge.sql4.83KB

Cyb3r-Jak3•5/26/23, 1:36 AM

I'm using wrangler 2 but using the experimental backend

Cyb3r-Jak3•5/26/23, 1:38 AM

Oh that worked

Cyb3r-Jak3•5/26/23, 1:43 AM

I was following a migration guide

Zzhendalf Is it possible to use Prisma with D1?

Max (@rozenmd)•5/26/23, 7:20 AM

not yet: https://github.com/prisma/prisma/issues/13310

senojsitruc•5/26/23, 2:32 PM

Does D1 specifically not support "LIMIT" on "DELETE" queries?

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

DELETE FROM 'some-table' WHERE key = 'some-key' LIMIT 1

Produces the error:

near "LIMIT": syntax error

near "LIMIT": syntax error

Dropping the "LIMIT" part of the query allows it to execute successfully.

kian•5/26/23, 2:34 PM

ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite which isn’t currently enabled on D1 - https://github.com/cloudflare/workerd/pull/608

GitHub

d1: enable UPDATE_DELETE and STAT4 at build by elithrar · Pull Requ...

Enables two SQLite options:

The STAT4 query planner, which is enabled in the alpha: https://sqlite.org/draft/compile.html#enable_stat4

the "downside" is that the query planner becomes ...

Kkian ORDER BY and LIMIT on UPDATE and DELETE is a non-standard feature on SQLite whic...

Matt SilverlockOP•5/26/23, 2:36 PM

To add: it’s complicated. It’s not part of the standard amalgamation. It’s not likely we’ll support this soon.

senojsitruc•5/26/23, 2:37 PM

I used it for sanity reasons, but it definitely isn't strictly necessary for me. Thanks for the info.

Ggeelen ah yeah the `.dump` statement includes transactions... we should automatically r...

Max (@rozenmd)•5/26/23, 2:57 PM

the next wrangler release will trim the first BEGIN TRANSACTION/COMMITBEGIN TRANSACTION/COMMIT it sees in an sqlite dump

Jason Hostetter•5/26/23, 3:31 PM

Getting D1 Error: Expression tree is too large (maximum depth 20)Expression tree is too large (maximum depth 20)

Jason Hostetter•5/26/23, 3:31 PM

Same query that was working on the old backend

Jason Hostetter•5/26/23, 3:32 PM

can post the query if needed, it's relatively complex (for me), but not super deeply nested. Though it's not totally clear to me what the expression tree depth is reflecting

kian•5/26/23, 4:06 PM

That limit is SQLITE_LIMIT_EXPR_DEPTHSQLITE_LIMIT_EXPR_DEPTH which is 20 on the experimental backend so you can probably find more information by searching for that

Jason Hostetter•5/26/23, 4:13 PM

Yeah all I could find is that it limits the expression depth to limit stack size when parsing the statement, but I can't find anything detailing how to figure out what your statement's depth is (am I at 21? 500?) or strategies to reduce it. The default sqlite depth limit seems to be 1000, are there any plans to increase going forward? I don't know if it's even possible to get under that limit with the type of query I need.

Unsmart•5/26/23, 4:15 PM

Limits are subject to change especially when going from alpha -> ga but I would assume that is there to reduce query complexity and thus keep performance reliable so I probably wouldnt bank on it increasing a lot but

Jason Hostetter•5/26/23, 4:26 PM

eeuuugh sounds like i'm gonna have to completely change my schema then, or else maybe do three separate queries instead of one with CTEs, which will really slow things down as i'll get 3x the network latency for db queries

JJason Hostetter Yeah all I could find is that it limits the expression depth to limit stack size...

Matt SilverlockOP•5/26/23, 5:28 PM

Do you get clarity on the sub-query depth from EXPLAIN QUERY PLANEXPLAIN QUERY PLAN

Matt SilverlockOP•5/26/23, 5:30 PM

These limits aren't fixed in stone, but we need to be mindful about how and where we increase them. Understanding what practical applications are blocked on a lower limit -> helps me understand the trade-offs (vs. just blindly increasing to 1000)

Jason Hostetter•5/26/23, 5:58 PM

I’ll run that when I get a chance and report back

MMatt Silverlock Do you get clarity on the sub-query depth from `EXPLAIN QUERY PLAN` - ?

Jason Hostetter•5/26/23, 7:46 PM

Here is the output of EXPLAIN QUERY PLANEXPLAIN QUERY PLAN: https://pastebin.com/cXxQtVhe

Pastebin

3 0 0 MATERIALIZE subQuery16 3 0 SCAN im0 USING COVERING INDEX sqli...

Pastebin.com is the number one paste tool since 2002. Pastebin is a website where you can store text online for a set period of time.

Jason Hostetter•5/26/23, 7:47 PM

There are 30 rows, does that correspond 1:1 to expression depth?

Ian•5/26/23, 11:32 PM

How plausible is it to get one db per user ? currently limited to 10

Ian•5/26/23, 11:32 PM

Ian•5/26/23, 11:32 PM

or at least one table per user, but then there is the issue of db size limit

IIan How plausible is it to get one db per user ? currently limited to 10

Max (@rozenmd)•5/27/23, 6:14 AM

I reckon it's plausible, but tell @elithrar what you're building

PPrisal When you use wrangler migrate apply, with sql files, how does cloudflare workers...

Max (@rozenmd)•5/27/23, 6:15 AM

there's a migration table we add to your db to track it: https://github.com/cloudflare/workers-sdk/blob/main/packages/wrangler/src/d1/migrations/helpers.ts#L83

Original message was deleted

Matt SilverlockOP•5/27/23, 1:29 PM

The example here is in TS: https://developers.cloudflare.com/d1/platform/client-api/#errors

you can also use e?.cause?.messagee?.cause?.message

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 …

JJason Hostetter There are 30 rows, does that correspond 1:1 to expression depth?

Matt SilverlockOP•5/27/23, 1:30 PM

No, rows != depth. Depth correlates to sub-queries - if you have a sub-query that executes over every row, then it will correlate to depth. But that’s not a given.

MMax (@rozenmd)I reckon it's plausible, but tell @elithrar what you're building

Ian•5/27/23, 1:37 PM

I was thinking that every entity/user has their own "partition" or "account" and all the data and actions will be running against that partition, I reckon this is not SQL-like structure, was just curious if it was possible at all because of the good price for reads even at table sizes of 10M rows.

Original message was deleted

Max (@rozenmd)•5/27/23, 3:14 PM

problem is it's native, folks just don't know about it: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Error/cause

Error: cause - JavaScript | MDN

The cause data property of an Error instance indicates the specific original cause of the error.

kian•5/27/23, 3:16 PM

fwiw it exists in TS's native types too

kian•5/27/23, 3:18 PM

notably, it's a ES2022 thing (which is the target you should use for Workers)

kian•5/27/23, 3:18 PM

https://github.com/microsoft/TypeScript/commit/363e3a78f4def6084cc7371c8b1104485a7fe5f8

Lettucebowler•5/27/23, 5:22 PM

just switched to the experimental backend and am seeing huge performance gains. Love it, thanks for the hard work and I can't wait to see d1 get even better!

Ashley•5/27/23, 6:05 PM

Simple Q: is there a way to get the row(s) written to the DB when executing an INSERT with run()? (or a way to insert data and get the response without running a secondary query)

Original message was deleted

Ashley•5/27/23, 6:38 PM

Nice, thank you - still two queries, but at least better than doing two sequentialy in the code

Ashley•5/27/23, 6:58 PM

Oh wow TIL also

ty!

Unsmart•5/27/23, 7:31 PM

Afaik it’s always been there

Original message was deleted

Matt SilverlockOP•5/27/23, 7:31 PM

All errors should (will) be thrown. An empty result set isn’t an error though, but D1ResultD1Result will have no rows

Yes: per the blog, you can export (https://developers.cloudflare.com/d1/learning/backups/#downloadin

Similar Threads