Hello, I understand that D1 is known for its excellent `read` performance and scalability, but I'm u

Hello,
I understand that D1 is known for its excellent

read

read

read

read performance and scalability, but I'm uncertain about its

write

write

write

write performance.
Do you know where I can find comprehensive information about the performance of writes and the locking mechanism when executing

db.batch()

db.batch()

db.batch()

db.batch() (whether it uses file locks, table locks, record locks, etc.)?
If you know of any resources where I can find consolidated information, including findings from official documentation and other blogs, please let me know.

Nnatsumesou Hello, I understand that D1 is known for its excellent `read` performance and sc...

avsaase•4/15/24, 2:00 PM

There's no read replication yet so I wouldn't say it's know for it's excellent read performance. Or at least, it should not be known for that (right now, we'll see what happens in the future)

CCole Xemi Will read replication help in instances where the DB is in the same region? To i...

avsaase•4/15/24, 2:12 PM

Idk

Aavsaase There's no read replication yet so I wouldn't say it's know for it's excellent r...

natsumesouOP•4/15/24, 2:34 PM

I wasn't aware that there's no read replication yet.
Therefore, currently, D1 may not have as many performance advantages over regular SQLite as I might have assumed.
Thank you for bringing this to my attention.

Original message was deleted

Max (@rozenmd)•4/15/24, 2:34 PM

just nudged it onto another metal, did wall time get better?

Original message was deleted

Max (@rozenmd)•4/15/24, 6:08 PM

ive never seen a metal do this, will investigate this week

Unsmart•4/15/24, 6:11 PM

"works on my metal" :Kapp:

Max (@rozenmd)•4/15/24, 6:13 PM

only alpha dbs shouldn't have a usage dashboard, what do you see?

Hannes•4/15/24, 6:39 PM

Hi, I'm having trouble importing my database. I'm using `npx wrangler d1 execute --config packages/root/wrangler.toml --e production --remote --file "mydb.sql" mydb-prod-main
While that works locally (with --local), it fails with a 504 Gateway timeout each time I try it... Any idea how to work around that? The .sql file is 13MB.

hungtruong•4/15/24, 7:57 PM

So I think I found a bug where simply looking at a D1 database in the dashboard causes a huge number of rows to be read. I have indexes for all of my queries and the high number of rows read is happening only when I pull up the database (simply looking at the table data).

Screenshot_2024-04-15_at_12.57.32_PM.png

adjlsncm•4/16/24, 12:02 AM

online consensus for SQLite ranges from unusable for anything with a serious amount of users to its capable of anything

adjlsncm•4/16/24, 12:03 AM

in the context of an app like letterboxd, would SQLite be a bad pick?

HowlOftheSun•4/16/24, 3:41 AM

Should a migration file contain only the new changes or contain the db whole definition?

HHowlOftheSun Should a migration file contain only the new changes or contain the db whole def...

adjlsncm•4/16/24, 4:29 AM

new changes only

adjlsncm•4/16/24, 4:29 AM

also remove the pragma lines if there are any

adjlsncm•4/16/24, 4:29 AM

breaks it

HowlOftheSun•4/16/24, 4:30 AM

Thanks! I know it's basic but I couldn't find anything on it.

LLiudmila I have a worker which calls LLM provider and streams response to the user. If us...

Max (@rozenmd)•4/16/24, 7:59 AM

https://developers.cloudflare.com/workers/runtime-apis/context/#waituntil

Patrick M•4/16/24, 9:01 AM

It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appears to be due to a json_array() that is pulling around 34 columns :

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

select json_array("id", "location_type", "name", "status", "brand_id", "address", "google_maps_url" /* , ... */)
from location;

The query is generated via Drizzle Query API (https://orm.drizzle.team/docs/rqb) so it uses json_array for the nested items.
Unfortunately all of them are pretty legitimate columns...I guess I'll have to find some columns I can do without.

It seems that regular sqlite has a limit of 100 arguments (https://www.sqlite.org/limits.html). Any particular reason why D1 has settled on a limit of 32? I found this: https://github.com/cloudflare/workerd/commit/cfe19fdd962c2898dcb4a4bd0c4e1b2a60949f74

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

GitHub

Increasing `SQLITE_LIMIT_FUNCTION_ARG` to 32 · cloudflare/workerd@c...

Also adding a test to demonstrate a json_objectjson_object query that requires the higher limit

PPatrick M It seems I've hit SQLITE_LIMIT_FUNCTION_ARG = 32. After some debugging, it appea...

Max (@rozenmd)•4/16/24, 9:33 AM

Any particular reason why D1 has settled on a limit of 32?

https://www.sqlite.org/security.html has 8, someone made a GitHub issue asking for more, we increased it - we'd appreciate a github issue if you'd like more: https://github.com/cloudflare/workerd/issues

Hannes•4/16/24, 3:15 PM

Hi, quick question about Indices for D1. I'm trying to improve my queries so that fewer rows have to be read. I'm using Prisma as my ORM and all my IDs are @id @default(uuid())@id @default(uuid()). Do I need to add an explicit index for each id column? The docs say that they are added automatically, but only for INTEGER PRIMARY KEYSINTEGER PRIMARY KEYS. Prisma generates TEXT NOT NULL PRIMARY KEYTEXT NOT NULL PRIMARY KEY id columns. SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index');SELECT name, type, sql FROM sqlite_schema WHERE type IN ('index'); shows one sqlite autoindex for each table, for example sqlite_autoindex_Organization_1sqlite_autoindex_Organization_1. Is that sufficient or will explicit id indices help with querying?
Thanks in advance!

HHannes Hi, quick question about Indices for D1. I'm trying to improve my queries so tha...

Matt Silverlock•4/16/24, 5:22 PM

You likely want an index on your ID PK if there isn't one.

MMatt Silverlock You likely want an index on your ID PK if there isn't one.

Hannes•4/16/24, 5:23 PM

Hi Matt, that is what I suspected. Thanks for the clarification!

HHannes Hi Matt, that is what I suspected. Thanks for the clarification!

Matt Silverlock•4/16/24, 5:31 PM

Surprised Prisma doesn't auto-create one, actually!

MMatt Silverlock Surprised Prisma doesn't auto-create one, actually!

Hannes•4/16/24, 5:37 PM

I can't find them. There are indices for foreign keys on the other side, but not for ids. I'll see if I create an issue there

adjlsncm•4/16/24, 10:09 PM

When developing against D1 locally, every single line change requires a rebuild using cloudflare@next-on-pages

adjlsncm•4/16/24, 10:09 PM

Is there a way to have the D1 locally running and usable without needing to do all that

adjlsncm•4/16/24, 10:10 PM

So I can still use fast refresh from next via run dev instead?

Original message was deleted

adjlsncm•4/16/24, 10:27 PM

thank you!

Original message was deleted

adjlsncm•4/16/24, 11:28 PM

do i also have to change how im accessing the DB? using setupDevPlatform in next.config didnt seem to change anything

adjlsncm•4/16/24, 11:36 PM

the docs are just really bad at telling you how to access the env variables for some reason theres a billion different ways

adjlsncm•4/16/24, 11:52 PM

everything seems to be the same/aligned so yeah i have no idea

adjlsncm•4/16/24, 11:52 PM

seeing about 6 different variations of getRequestContext being called in the docs

Fabian•4/16/24, 11:53 PM

Is it only me or are D1 queries extremely slow in deployed Workers?
Running the same query using wrangler d1 execute it says executed in ~0.1ms, and local performance measuring also outputs <1ms.
The second it's deployed I can run the same query 100 times and it always takes >10ms. It's a very very simple query on a small table so I expected it to behave more like in wrangler (<1ms)

kian•4/16/24, 11:54 PM

Are you measuring the .meta.duration.meta.duration or how long it takes to get a response?

adjlsncm•4/16/24, 11:57 PM

i have @cloudflare/next-on-pages installed via npm so what gives

Kkian Are you measuring the `.meta.duration` or how long it takes to get a response?

Fabian•4/17/24, 12:06 AM

Yeah it seems like .meta.duration is indeed the same as reported in wrangler d1 execute. But why is the difference so extreme?

Unsmart•4/17/24, 12:08 AM

network

Fabian•4/17/24, 12:10 AM

Probably because the worker runs in the nearest location to my machine, but the D1 replica is not in this location, right?

Unsmart•4/17/24, 12:12 AM

D1 doesnt have replicas (yet) but yes D1 only runs in some locations

Fabian•4/17/24, 12:15 AM

Ok, thanks for the info :)

Aadjlsncm i have @cloudflare/next-on-pages installed via npm so what gives

adjlsncm•4/17/24, 1:43 AM

apparently getRequestContext just doesnt work sometimes? so you cant develop locally with D1? im just going to opt for planetscale then because half my time is spent researching why local development with D1 isnt working

Brayn•4/17/24, 2:16 AM

Hello why am I getting this on all my website I uploaded

Hello, I understand that D1 is known for its excellent `read` performance and scalability, but I'm u

Similar Threads

Similar Threads

Similar Threads