I got another question. Since D1 uses SQlite dialect, can I store a column of type `TIMESTAMP` ?
I got another question. Since D1 uses SQlite dialect, can I store a column of type
TIMESTAMP
Lbecause if not, I gotta store ISO timestamps in text format
Luuuh, what?
LSee https://developers.cloudflare.com/d1/worker-api/#type-conversion for details on some types specific gotchas as well.
Supported SQLite types are in https://www.sqlite.org/datatype3.html (I don't see TIMESTAMP)
Supported SQLite types are in https://www.sqlite.org/datatype3.html (I don't see TIMESTAMP)
IThey mean that D1 actually runs SQLite itself, it's not a custom SQLite compatible engine.
Lah
Lmakes sense
LI'm storing as iso timestamps now 
Ddoes the replication when enabled actually show any metrics for it, I have it turned on, can see the X-D1-Bookmark: responce header etc but there seems to be no metrics in the D1 Ui, the dropbox also says eplicas 0 ?
1d1 jurisdiction is huge
1and it maintains replicas within the jurisdiction which is also huge
1???? only happens on the jurisdiction table not sure why its throwing a r2 bucket error lol
1recreated a bunch of databases and this error only occurs for jurisdiction created databases, not for normal location hint creations both in the same EEUR region
FI have been able to reproduce it on my end. I'm forwarding it internally.
LYou will see metrics for each region serving queries, yes. But, replicas are used only on-demand for queries inside each region, they don't run all the time. For example, if you have traffic in ENAM and EEUR, you should see the primary and the replicas in those two regions being in your metrics.
Sis d1 down?
Smy workers endpoints using it take forever to load
Salso I cannot use cloudflare's endpoints to make changes externally
S504, {"result":null,"success":false,"errors":[{"code":7009,"message":"Upstream service unavailable"}],"messages":[]}
Skeep getting these
LThe fix for this should be out now everywhere. Thank you for reporting.
J@Visal .In not sure if you are the correct one to ping, but I am having really bad D1 performance. I have a simple query
where is an indexed column in the table, but that query took 16.59 seconds to complete in a table that has items in it
where is an indexed column in the table, but that query took 16.59 seconds to complete in a table that has items in it
JScratch that. was a bad index I had taking up too much index space
AIs d1 suitable for web apps with say thousands of daily users? It feels complicated to setup multiple d1s so I would prefer to just have like one for all users like a ”normal” database
AI'm using it for this, just keep in mind it's single threaded so there are limits to scalability. But I'm using replication and it's working great since enabling that for my few thousand users.
AOh okay, could you point me to guides or docs on how to achieve this? Also how well does this structure handle many writes and limits of like 10gb storage or the 128mb memory limit?
BHi I don't know if this is known but maybe someone from Cloudflare decides to push on this. Sadly because cloudflare uses PRAGMA defer_foreign_keys not the simple turn on or off the migrations generated by drizzle are invalid and often broken. This means if we want to use drizzle with cloudflare we need to hand edit migrations
LI am hoping to merge this docs update on Monday, and it contains some guidance on D1 performance: https://github.com/cloudflare/cloudflare-docs/pull/26369
If you have thousands of active users at the same time, which will translate to high hundreds or thousands of queries to D1 at the same time (e.g. per second), then you will hit performance limitations and overload the single database.
You will definitely need to shard across several databases.
If you are going to have many thousands of queries per second and would need tens of D1 DBs, then using SQLite Durable Objects would be preferable and my suggestion. You can create thousands of Durable Objects dynamically in your code without having to mess with bindings, and it's the same underlying SQLite engine.
If you have thousands of active users at the same time, which will translate to high hundreds or thousands of queries to D1 at the same time (e.g. per second), then you will hit performance limitations and overload the single database.
You will definitely need to shard across several databases.
If you are going to have many thousands of queries per second and would need tens of D1 DBs, then using SQLite Durable Objects would be preferable and my suggestion. You can create thousands of Durable Objects dynamically in your code without having to mess with bindings, and it's the same underlying SQLite engine.
TBefore I test, how many rows per second can D1 / DO with SQL scan per second? any unofficial data around it?
LI have seen high hundreds of thousands or single digit millions reads per second. Obviously it's different if your rows are 10 bytes vs 2MB!
Also, with such high number of reads per second, if you are doing that constantly you need to be careful of hitting CPU limits, or overload limits since a DO is single threaded.
Also, with such high number of reads per second, if you are doing that constantly you need to be careful of hitting CPU limits, or overload limits since a DO is single threaded.
CHi, I have a question about D1. I'm using it together with Drizzle ORM. I have a table that stores user balances, and each request deducts from the balance. The issue is that a simple query like might cause a problem known as a race condition (I think). It seems D1 doesn't support transactions, so could async operations cause issues here?
LI have seen high hundreds of thousands or single digit millions reads per second.Wait! How is that possible? I've been using no more 1,000 request/messages per second as design ceiling for DOs, eventhough, I've seen as high as 5,000. Even assuming 5,000, you'd need 200 read replicas to hit 1M. Does D1 go to that level?
T@Larry if think we are discussing different things, I was asking how many rows per second can be scanned, so if you have a table with 100k rows, how long would it take to scan it, vs request per second which would be how many queries can run in a second
LDuh, I should read more carefully. Thanks for the clarification! That makes a lot more sense.
TNo problems
CWould an atomic update be sufficient?
Or using durable objects.
Or using durable objects.
JI like using DOs for transactions more simply because you control the thread of processing. Run a bunch of SQL in synchronous code with the right error handling in place and you have transactions.
Another approach I use when coordinating changes async across D1/DO other resources in a saga pattern.
Another approach I use when coordinating changes async across D1/DO other resources in a saga pattern.
BI saw the post about the rollout of data jurisdictions. Love it! Will it be possible to enable retroactively for existing DBs? I've got a project in staging that's about to go live and I really don't want to recreate the DB
ZThere should definitely be something like development branches for D1. This would be a great addition as it would compliment preview deployments from git repositories. That way, developers could test database changes in a staging enviroment without modifying the prod database.
LIf you can build all the queries you want to run in a transaction before hand, you can send them all in a single query and they will all run within the same transaction.
So, for example, if you want to update two tables, or two different rows but with separate queries, you can prepare both of them and send them together.
https://developers.cloudflare.com/d1/worker-api/d1-database/#batch
So, for example, if you want to update two tables, or two different rows but with separate queries, you can prepare both of them and send them together.
https://developers.cloudflare.com/d1/worker-api/d1-database/#batch
Cloudflare Docs
To interact with your D1 database from your Worker, you need to access it through the environment bindings provided to the Worker (env).
CThis is great — but could it still lead to a race condition issue?
LYou can always add race conditions if you split something that should be a single transaction across multiple requests.
I am just mentioning that there is the method for running independent queries in a transaction.
If you need to intervleave JS code with SQL queries, then D1 cannot do that, and your best bet would be SQLite DOs.
I am just mentioning that there is the method for running independent queries in a transaction.
If you need to intervleave JS code with SQL queries, then D1 cannot do that, and your best bet would be SQLite DOs.
CI got it, thanks.
MIn both the CF dash and via the D1 REST API's list DBs endpoint, it says my database has zero tables. This isn't true; it has 2, and I can successfully query data. These tables have existed for several days. Why would this be happening?
LI believe the GET database details should give you the correct information.
The list endpoint I think returns stale data due to a control plane change few weeks ago. I will report it internally to be fixed.
The list endpoint I think returns stale data due to a control plane change few weeks ago. I will report it internally to be fixed.
TAbout database scaling limits, is there a way I can check my current usage?
LThe graphql API returns rows read/written and db sizes per database. The dashboard also shows that data in the metrics tab.
JIm curious (and forgive my stupidity here) if there's an officiall or unofficially blessed way to do D1 multitenancy that allows for using parameters in queries. Everything I'm seeing seems to indicate people are doing like hash sharding and stuff to allow them to leverage worker bindings when they scale a per tenant or per entity D1 architechture. Im trying to do a rewrite to pivot that direction (multitenancy) but you can't bind parameters to multi statement queries over http (im not even sure you could do that with bindings I've never tried). And it seems the efficiency and performance hit of doing an independant api call for each query would be pretty bad... Should I just be abandoning parameters and doing my own sanitization so I can batch calls over the API with all my values written into it? or am missing a "blessed" pathway here?
JIt just seems like hacky "redeploys" where I eternally bind more D1 databases to my wrangler.json can't be the best way to go about this... but also I don't want to run unsafe SQL statements and I don't want to run an api call for each query... If there's no official way I'll figure something out but it seems like I must be missing something here.
Alternatively, is this something I should be solving with Queues?
Alternatively, is this something I should be solving with Queues?
LIf you want thousands of DBs (tenants) you should use SQLite Durable Objects. Same underlying SQLite engine as D1, but you can dynamically create thousands of instances programmatically.
LHey folks, we updated the section "How much work can a D1 database do?" in our docs: https://developers.cloudflare.com/d1/platform/limits/#how-much-work-can-a-d1-database-do
It tries to give a guidance on what to expect, and set expectations a bit more realisitically. Let me know how you find it.
It tries to give a guidance on what to expect, and set expectations a bit more realisitically. Let me know how you find it.
Cloudflare Docs
Cloudflare also offers other storage solutions such as Workers KV, Durable Objects, and R2. Each product has different advantages and limits. Refer to Choose a data or storage product to review which storage option is right for your use case.