?d1-remote-connections

Accessing D1 from outside workers:

D1 is based on SQLite, which does not natively support remote connections and isn't MySQL/PostgreSQL wire compatible, nor does D1 itself offer that functionality. As such, your options are:

1 -> The Cloudflare API Query Endpoint (https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database), with the upside of needing less setup, all requests have to go back to Cloudflare's core, so request latency is far from optimal from some locations. You would also be subject to the API Rate limit of 1200 requests per 5 minutes.

2 -> Create a worker to proxy requests to your D1 Database. Either tightly bound to your application/schema or loose and just accepting any query. This would allow your worker to execute potentially closer to your D1 Database and avoid the api rate limit.

CCyb3r-Jak3 ?d1-remote-connections

Minions•4/5/25, 12:12 AM

Oh, you're smart.

CCyb3r-Jak3 ?d1-remote-connections

Minions•4/5/25, 12:16 AM

I think option 2 is good,.

Minions•4/5/25, 12:17 AM

I tried with 'npx create-payload-app' project.

Minions•4/5/25, 12:18 AM

But the problem here is to implement admin dashboard

Disciple of Caoximeihao•4/5/25, 3:07 AM

Error: D1_ERROR: Currently processing a long-running export.

I need to synchronize the database from remote to local, but when I export, the system will be interrupted, my system needs to operate 24/7

DDisciple of Caoximeihao Error: D1_ERROR: Currently processing a long-running export. I need to synchron...

lambrospetrou•4/5/25, 7:20 AM

With the current design imports/exports block your database. Improvements of this are in the works but for now that's the state of it.

gruntlord6•4/6/25, 12:06 AM

Maybe use another database? Copy the data to it and use it for the export so it doesn’t block your application

Original message was deleted

gruntlord6•4/6/25, 12:32 AM

This looks really handy as a stop gap until dynamic bindings are eventually (hopefully) added

will smith•4/6/25, 3:48 PM

I see a bunch of questions in the past asking about per-user/tenant D1. How can I stay up to date on the topic over the next few months/years?

I've migrated off D1 for now but would like to migrate back once that's possible

James•4/6/25, 4:00 PM

There'll be a blog post I imagine when it's actually possible. For now, if you want to do that, you can with Durable Objects, but it's basically not possible in any ergonomic way with D1. Nor would I recommend it for the vast majority of apps over a more traditional DB solution.

martinklepsch•4/7/25, 11:18 AM

Hey all, I saw @yusukebe's recent Hono Vite template and wrote a prompt to add D1 (with drizzle), the prompt worked really well for me — would love to hear if it works for others too!

https://ctxs.ai/weekly/d1-database-setup-for-classifieds-app-a0m502

Deltachaos•4/7/25, 12:02 PM

Regarding scaleing: So if D1 uses SQLite under the hood, does it scale for databases with hundrets of millions of records within one table?

DDeltachaos Regarding scaleing: So if D1 uses SQLite under the hood, does it scale for datab...

lambrospetrou•4/7/25, 12:10 PM

"Scaling" doesn't mean much without specifying for which queries. If you are queries use proper indices, and provide WHEREWHERE filters to not scan the table for every query and limit the results you get back, it can do fine. We have folks with millions of rows.
The caveat is that if at any point you try to modify millions of rows, things will not work well.

So, having millions of rows is not an issue (assuming they fit the storage limits), but modifying in your query millions of rows each time will lead to issues most likely.

Zack•4/7/25, 2:17 PM

So I have a question around D1 wrt dropping large tables. We have tables that sum to ~3.5 million rows and ~2.5GB each. Generally we will only have one of these, but when we need to update the data stored in them (they are used as a lookup of sorts) we will create a brand new table, switch to that, then drop the old table. The last part of this is causing us some significant issues and I am not sure how to work around it. While the DROP TABLE {name}DROP TABLE {name} query is running, the table becomes completely unresponsive and can't serve other queries. We always get returned a failure for this query, whether using worker bindings or the API, however sometimes it actually does work and sometimes it doesn't (presumably the operation times out and a rollback is initiated). I wanted to know if this is something you guys are aware of, and if there are plans to improve this, because right now we can't really delete our old data without causing a temporary outage, and we may have to even try multiple times to delete the table. Cheers!

Corsix👾•4/7/25, 2:32 PM

It says in the documentation that the response from a .batch() call should be a array but I'm getting a json object with numberd keys for the batch statments? Is this something new?

Corsix👾•4/7/25, 2:33 PM

rn_image_picker_lib_temp_3a29382b-9326-42a9-8e53-22a8161ec4d8.jpg

ZZack So I have a question around D1 wrt dropping large tables. We have tables that su...

lambrospetrou•4/7/25, 2:40 PM

Yes, we are aware, and unfortunately it's not something there is an obvious solution right now. When you delete 3.5 million of rows, that's causing pretty much writes for all those SQLite pages, and at some point you are hitting the Durable Object CPU limits (a D1 database is backed by a DO). Yes, each query is in a transaction and if it fails it rolls back.

The only recommendation we have for now is that those deletions/writes should be done in lower chunks.

Llambrospetrou Yes, we are aware, and unfortunately it's not something there is an obvious solu...

Zack•4/7/25, 2:41 PM

Right, and that's something we considered. However that gets pretty expensive, given that a DROP TABLEDROP TABLE is 1 write, and DELETE FROMDELETE FROM is 1 write per row deleted :/

CCorsix👾Click to see attachment

lambrospetrou•4/7/25, 2:41 PM

How are you printing that? An array in JS is an object with numbers as keys, and those numbers are the array's indices.

Zack•4/7/25, 2:42 PM

Maybe something could be done around that to make it less prohibitively expensive to delete tables in chunks, while this problem exists

Mmarshallswain Just going off the numbers in Zack's message (2 after yours) He reports "~3.5 mi...

lambrospetrou•4/7/25, 2:44 PM

A row value can be 8 bytes, or 2MBs. Really it depends.

ZZack So I have a question around D1 wrt dropping large tables. We have tables that su...

kerohije•4/7/25, 2:45 PM

Can't you replace the DB instead of dropping the table?

Create new DB + insert data
Replace worker binding
Delete old DB

Zack•4/7/25, 2:45 PM

Yea, I wouldn't base anything necessarily off of my tables, we store some decent size blobs of data within each row

amanzad•4/7/25, 3:48 PM

Hey! Quick question about D1—if I run a query to check whether a specific id exists in a table, would that count as just one read, or would it count as one read per row in the table?

Aamanzad Hey! Quick question about D1—if I run a query to check whether a specific id exi...

Zack•4/7/25, 3:51 PM

If you have an index that SQLite can use for that query, it'd be a single read

Zack•4/7/25, 3:51 PM

If not, it'll scan the entire table

Kkerohije Can't you replace the DB instead of dropping the table? 1. Create new DB + inse...

gruntlord6•4/7/25, 6:18 PM

thats what I would do, might be harder in practice

Ggruntlord6 thats what I would do, might be harder in practice

James•4/7/25, 6:29 PM

Any regular process which involves dropping and recreating millions of rows of a table should be rethought architecture-wise, regardless if this was D1 or Postgres. If I was stuck on such a process, i'd probably run a blue/green deployment on this table to keep both pre-post hot to avoid migration downtime.

Llambrospetrou How are you printing that? An array in JS is an object with numbers as keys, and...

Corsix👾•4/7/25, 6:38 PM

Corsix👾•4/7/25, 6:39 PM

startDataUUIDs is a array of uuids

JJames Any regular process which involves dropping and recreating millions of rows of a...

gruntlord6•4/7/25, 9:05 PM

yea often times its easier to re-think the issue rather then solve obscure edge cases

Kkerohije Can't you replace the DB instead of dropping the table? 1. Create new DB + inse...

Zack•4/7/25, 11:16 PM

That's an option, but one that will suck once D1 global replication exists

Zack•4/7/25, 11:16 PM

Given that will presumably replicate over time, and increase the PoP's it exists in, replacing the entire database will destroy that replication and you'll start from step 1

JJames Any regular process which involves dropping and recreating millions of rows of a...

Zack•4/7/25, 11:17 PM

Something like this would be totally fine in postgres, it's an extremely simple operation that won't impact the database whatsoever

ZZack Given that will presumably replicate over time, and increase the PoP's it exists...

Isaac McFadyen•4/7/25, 11:31 PM

Once it lands, D1 replication is extremely quick so this should still be a reasonable method of replacing the DB

Zack•4/7/25, 11:32 PM

I've not seen any info on that tbh

Zack•4/7/25, 11:34 PM

I imagine the process of replication is fast, but I expect the process of learning where to replicate too is not necessarily as fast

Zack•4/7/25, 11:35 PM

And switching out your DB and dumping all load onto a single node while it replicates out is not going to be great

Isaac McFadyen•4/7/25, 11:38 PM

I can't really say too much at the moment other than the fact that switching out a DB will still be a reasonable thing to do once replication lands

Zack•4/7/25, 11:41 PM

We'll see whenever that lands I guess, regardless, dropping a table should not be something that takes out a production database

gruntlord6•4/7/25, 11:41 PM

if its anything like other platforms the writes go to all replicas without any work on your end

gruntlord6•4/7/25, 11:41 PM

and it's basically instant

Zack•4/7/25, 11:41 PM

If you replace the entire database, there are no replicas

gruntlord6•4/7/25, 11:41 PM

but like they said can't speak for something we don't have

ZZack We'll see whenever that lands I guess, regardless, dropping a table should not b...

Isaac McFadyen•4/7/25, 11:43 PM

Sure but if you want zero downtime, you duplicate the DB, drop the table, and switch the "connection" (binding)

IIsaac McFadyen Sure but if you want zero downtime, you duplicate the DB, drop the table, and sw...

Zack•4/7/25, 11:44 PM

Yes, that is something we've considered, it's just more work

Zack•4/7/25, 11:44 PM

We're already having to work around multiple other issues that we didn't expect lol

く

くもりにくもったクラウドサービスlocaler•4/9/25, 2:00 PM

Hi. I encountered a consistent issue when editing a row in a D1 table via the dashboard. Attempting to edit the following string causes a 404 Bad Request:

data.txt51B

?d1-remote-connections

Similar Threads

Similar Threads

Similar Threads