Cloudflare Developers•3y ago

Related: https://discord.com/channels/595317990191398933/992060581832032316/1085261317423779982

Related: d1-database

MMatt Silverlock Related: https://discord.com/channels/595317990191398933/992060581832032316/1085...

Matt SilverlockOP•5/24/23, 8:37 PM

(They had the inverse problem but see their notes about the app config)

AAnna | DevMiner Having D1 put it's error cause into `Error.cause` is super unfortunate because i...

James•5/24/23, 10:10 PM

I completely agree that the DX here isn't great. Nothing else really follows this pattern and it's not super intuitive yet. Error.causeError.cause is an awesome thing slowly surfacing within JS, but it feels a little out of place right now.
Filing an issue at https://github.com/cloudflare/workers-sdk might be the best place to further discuss this?

JJames I completely agree that the DX here isn't great. Nothing else really follows thi...

Matt SilverlockOP•5/24/23, 10:12 PM

We're already planning to address this, but have a couple of other higher priority items to address first. Hold tight.

James•5/24/23, 10:12 PM

Awesome to hear, ty for the update

Original message was deleted

Max (@rozenmd)•5/25/23, 2:55 PM

the fix for this should be rolled out tomorrow evening, thanks for reporting it

Sameer Ali•5/25/23, 6:54 PM

any rough ETA on storage limit increase?

SSameer Ali any rough ETA on storage limit increase?

DaniFoldi•5/25/23, 7:00 PM

I don't think there was a timeline shared, but I wouldn't be surprised if it was coming very soontm

44x4notfound Would those performance improvements be part of that?

Matt SilverlockOP•5/25/23, 10:01 PM

If you use the new backend (per the blog post) you can already get the performance improvements: https://blog.cloudflare.com/d1-turning-it-up-to-11/ (caveat: no backups on the new backend yet).

Existing non-new-backend databases will not get a storage increase. The original, prototype backend just doesn’t scale for it. Wasn’t the intent.

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

44x4notfound Can we migrate?

Matt SilverlockOP•5/25/23, 10:48 PM

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?