there's no create step for local dbs, you should be able to just start querying

C

cosbgn•1/15/25, 10:06 AM

I cache my D1 connection like this (on workers):

let cached_db = null;
export const useDb = () => {
  if (!cached_db) {
    const binding = process?.env?.DB || globalThis?.__env__?.DB || globalThis?.DB
    cached_db = drizzle(binding, { schema })
  }
  return cached_db
}

let cached_db = null;
export const useDb = () => {
  if (!cached_db) {
    const binding = process?.env?.DB || globalThis?.__env__?.DB || globalThis?.DB
    cached_db = drizzle(binding, { schema })
  }
  return cached_db
}

let cached_db = null;
export const useDb = () => {
  if (!cached_db) {
    const binding = process?.env?.DB || globalThis?.__env__?.DB || globalThis?.DB
    cached_db = drizzle(binding, { schema })
  }
  return cached_db
}

let cached_db = null;
export const useDb = () => {
  if (!cached_db) {
    const binding = process?.env?.DB || globalThis?.__env__?.DB || globalThis?.DB
    cached_db = drizzle(binding, { schema })
  }
  return cached_db
}

Can this be the cause of:

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

C

cosbgn•1/15/25, 10:06 AM

Should I avoid caching the connection?

Ccosbgn I cache my D1 connection like this (on workers): ```js let cached_db = null; ex...

L

lambrospetrou•1/15/25, 10:08 AM

I don't know how Drizzle works internally, but D1 doesn't have the concept of a long-lived connection like a traditional database. Every query you do sends a request to the database using connections managed by the runtime.
Our recommendation is to always use the fresh

env.{DATABASE_BINDING}

env.{DATABASE_BINDING}

env.{DATABASE_BINDING}

env.{DATABASE_BINDING} you get in each request, otherwise you will definitely hit issues at some point.

This is what their example is doing as well: https://orm.drizzle.team/docs/connect-cloudflare-d1#step-2---initialize-the-driver-and-make-a-query
Looking at https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/d1/driver.ts#L39 it's not doing expensive stuff, so just create it per request.

Llambrospetrou I don't know how Drizzle works internally, but D1 doesn't have the concept of a ...

L

lambrospetrou•1/15/25, 10:10 AM

The specific error you got above is not relevant to that anyway, that error says that your query took too long and was killed.
Check if you are doing any kind of scans over thousands/millions of rows each time.

C

cosbgn•1/15/25, 10:14 AM

Does efficiency 0 means it's really bad?

 "avgRowsRead": 0,
    "totalRowsRead": 13401,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.33640454375886003,
    "totalDurationMs": 4508.830100000001,
    "numberOfTimesRun": 13403,
    "queryEfficiency": 0

 "avgRowsRead": 0,
    "totalRowsRead": 13401,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.33640454375886003,
    "totalDurationMs": 4508.830100000001,
    "numberOfTimesRun": 13403,
    "queryEfficiency": 0

 "avgRowsRead": 0,
    "totalRowsRead": 13401,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.33640454375886003,
    "totalDurationMs": 4508.830100000001,
    "numberOfTimesRun": 13403,
    "queryEfficiency": 0

 "avgRowsRead": 0,
    "totalRowsRead": 13401,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.33640454375886003,
    "totalDurationMs": 4508.830100000001,
    "numberOfTimesRun": 13403,
    "queryEfficiency": 0

Ccosbgn Does efficiency 0 means it's really bad? ``` "avgRowsRead": 0, "totalRowsR...

L

lambrospetrou•1/15/25, 10:16 AM

Your query took

0.3ms

0.3ms

0.3ms

0.3ms on average so why are you worried about it?

C

cosbgn•1/15/25, 10:16 AM

mainly because I keep getting this:

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

[request error] [unhandled] [500] D1_ERROR: D1 DB storage operation exceeded timeout which caused object to be reset.

C

cosbgn•1/15/25, 10:16 AM

like every day

Ccosbgn mainly because I keep getting this: `[request error] [unhandled] [500] D1_ERROR:...

L

lambrospetrou•1/15/25, 10:17 AM

Then check the query that's failing, what it's doing. Get insights to see your slowest queries: https://developers.cloudflare.com/d1/observability/metrics-analytics/#query-insights

C

cosbgn•1/15/25, 10:18 AM

the thing is that I get the error on different queries, it's not one specific

C

cosbgn•1/15/25, 10:18 AM

anyways I'll run the query insight till I find something. Thanks for the tips!

R

Robin Genz•1/15/25, 11:42 AM

Hey

I get the following error when I try to apply a migration:

✘ [ERROR] Migration 0043_cultured_the_stranger.sql failed with the following errors:
  
  
  
  ✘ [ERROR] <html>
  
  
    <head><title>504 Gateway Time-out</title></head>
    <body>
    <center><h1>504 Gateway Time-out</... (length = 168)
    POST /accounts/***/d1/database/<DATABASE_ID>/query -> 504 Gateway Time-out

✘ [ERROR] Migration 0043_cultured_the_stranger.sql failed with the following errors:
  
  
  
  ✘ [ERROR] <html>
  
  
    <head><title>504 Gateway Time-out</title></head>
    <body>
    <center><h1>504 Gateway Time-out</... (length = 168)
    POST /accounts/***/d1/database/<DATABASE_ID>/query -> 504 Gateway Time-out

✘ [ERROR] Migration 0043_cultured_the_stranger.sql failed with the following errors:
  
  
  
  ✘ [ERROR] <html>
  
  
    <head><title>504 Gateway Time-out</title></head>
    <body>
    <center><h1>504 Gateway Time-out</... (length = 168)
    POST /accounts/***/d1/database/<DATABASE_ID>/query -> 504 Gateway Time-out

✘ [ERROR] Migration 0043_cultured_the_stranger.sql failed with the following errors:
  
  
  
  ✘ [ERROR] <html>
  
  
    <head><title>504 Gateway Time-out</title></head>
    <body>
    <center><h1>504 Gateway Time-out</... (length = 168)
    POST /accounts/***/d1/database/<DATABASE_ID>/query -> 504 Gateway Time-out

I am using the latest version of Wrangler. The migration is very simple:

ALTER TABLE `app_devices` DROP COLUMN `force_app_bundle_id`;
ALTER TABLE `app_devices` DROP COLUMN `force_app_channel_id`;

ALTER TABLE `app_devices` DROP COLUMN `force_app_bundle_id`;
ALTER TABLE `app_devices` DROP COLUMN `force_app_channel_id`;

ALTER TABLE `app_devices` DROP COLUMN `force_app_bundle_id`;
ALTER TABLE `app_devices` DROP COLUMN `force_app_channel_id`;

ALTER TABLE `app_devices` DROP COLUMN `force_app_bundle_id`;
ALTER TABLE `app_devices` DROP COLUMN `force_app_channel_id`;

How can I fix this? It is currently blocking our production deployments.

MMax (@rozenmd)there's no create step for local dbs, you should be able to just start querying

A

Alex Patterson•1/15/25, 11:52 AM

I totally see now I have have been going through the tutorial and it seemed like the only way to start locally was with

npx wrangler d1 create

npx wrangler d1 create

npx wrangler d1 create

npx wrangler d1 create. Without a wrangler project with the DB in it that is probably true, but once I have one it is no problem.

I have restructured my local project to all point to a shared local persistence area

--persist-to ../../../cloudflare/.workers/cache/

--persist-to ../../../cloudflare/.workers/cache/

--persist-to ../../../cloudflare/.workers/cache/

--persist-to ../../../cloudflare/.workers/cache/ which will allow for access to the DB from multiple microservices.

I now have a D1 only project file with just the required toml file for the db, prior to doing this it was generating a DB per running worker which didn't have the correct data because they were both in their own database. Is there a best spot to update the docs for this setup? Maybe at least a knowlege add to the local development area?

A

Alex Patterson•1/15/25, 11:53 AM

here is how I handle the shared persistence.

AAlex Patterson I totally see now I have have been going through the tutorial and it seemed like...

M

Moh•1/15/25, 1:13 PM

i came here to ask about this, if you have a d1 db shared between workers, where do the migrations go?

MMoh i came here to ask about this, if you have a d1 db shared between workers, where...

L

lambrospetrou•1/15/25, 1:24 PM

Your migrations should be owned by 1 process/application/worker. Choose which one and put them there. A database can be used by many apps/workers but one of them is the owner of the database. Same as any other application using a database.

M

Moh•1/15/25, 1:29 PM

i was thinking of workers as smaller than a service, but I guess I should treat them as microservices

M

Moh•1/15/25, 1:29 PM

in which case I should probably not share the DB

L

lambrospetrou•1/15/25, 1:42 PM

Multiple workers querying a single D1 database is fine, and expected. Multiple worker projects doing migrations on it is not expected since you need to have a single source of truth. You could even put them outside your worker projects entirely and run them in your CI.

RRobin Genz Hey 👋 I get the following error when I try to apply a migration: ``` ✘ [ERROR]...

R

Robin Genz•1/15/25, 2:29 PM

Can someone from the D1 team please take a look at this? We can no longer perform any migrations on our database. :/

EDIT: Let me know if you need any other information. I am happy to provide everything.

RRobin Genz Can someone from the D1 team please take a look at this? We can no longer perfor...

L

lambrospetrou•1/15/25, 2:53 PM

You didn't provide enough information to be able to help.
What is your database ID?
Are other queries working, and only that specific migration failing?
If you run the same (or similar) statement in the D1 console does it work or also fail?

Llambrospetrou You didn't provide enough information to be able to help. What is your database ...

R

Robin Genz•1/15/25, 2:56 PM

What is your database ID?

I've just send you a DM.

Are other queries working, and only that specific migration failing?

I don't know if other migrations will work, as our deployment is currently on hold due to this failing migration.

If you run the same (or similar) statement in the D1 console does it work or also fail?

I haven't tried that because I don't want to break Production. Our CI/CD deploys the new (compatible) worker versions directly after the migration.

Llambrospetrou Multiple workers querying a single D1 database is fine, and expected. Multiple w...

M

Moh•1/15/25, 2:58 PM

You could even put them outside your worker projects entirely and run them in your CI

tried to find documentation on this but doesn't seem to have any examples I could reference, I assume a wrangler.json is required but I would rather not have a dummy worker project just to house the migrations

MMoh > You could even put them outside your worker projects entirely and run them in ...

L

lambrospetrou•1/15/25, 3:17 PM

Yeah, I didnt say you should move them out, just that it's possible and your migrations are not tied to any worker. You can choose to put them wherever you want, but keep them in one place.

Llambrospetrou Yeah, I didnt say you should move them out, just that it's possible and your mig...

J

James•1/15/25, 4:09 PM

but the example above with the guy with prod migration issues and deployments, ideally you dont want migration issues blocking deployments unless they're tightly coupled

FFriday Candour They don't want you to get away not using workers even while you are going to pa...

D

Devils•1/15/25, 8:50 PM

This is kind of what I was thinking as well. Not really sure why we cant just have normal sql connections.

Kind of makes absolutely no sense other than you having to pay extra for a worker, on top of DB costs.

DDevils This is kind of what I was thinking as well. Not really sure why we cant just ha...

I

Isaac McFadyen•1/15/25, 8:54 PM

why we cant just have normal sql connections

D1 is built on top of SQLite. There is no "normal SQL connections" because unlike Postgres/MySQL/SQL Server, SQLite DBs are normally used locally and have no connection protocol or way to connect remotely.

D

Devils•1/15/25, 8:54 PM

I suppose thats true, but I've seen custom middleware for connecting remotely before.

D

Devils•1/15/25, 8:55 PM

I guess D1 is nice for testing/developing though.

I

Isaac McFadyen•1/15/25, 8:55 PM

If Cloudflare were to have some sort of SQLite connection it would be entirely proprietary and almost nothing would even work with it because it would be non-standard.

DDevils This is kind of what I was thinking as well. Not really sure why we cant just ha...

L

lambrospetrou•1/15/25, 9:32 PM

As others said, SQLite doesn't have "normal" connections. Even if you use standard SQLite in your own local application you need extra layer to expose that over the network. That's the D1 HTTP API (albeit slow at the moment for reasons), and the Workers API to access that database from your Worker regardless of where it runs.

At the moment D1 really has value if you use it with Workers, otherwise you tradeoff a lot of things, so it depends what you want to do with it. If you mainly want to use it from outside Workers, and you also care about latency, then it's not ideal right now.

R

rivage•1/15/25, 11:14 PM

Hey, why is there such a high delta between query time and response time? In the same region as my database. Is there something I can do to optimize response time?

Rrivage Hey, why is there such a high delta between query time and response time? In the...

L

lambrospetrou•1/15/25, 11:17 PM

Query time is the raw SQL execution time.
Response time in the D1 console UI is NOT the performance you will get when using D1 in your worker code. Our REST API now flows through our central datacenters for auth reasons, therefore it's slow. That response time is the total time to send the query and get the result back to your browser.
When you use D1 from your worker the latency will be pretty much as much as the latency for 1 round trip from your location to your database location + the query time you see above.

Llambrospetrou 1. Query time is the raw SQL execution time. 2. Response time in the D1 console ...

R

rivage•1/15/25, 11:23 PM

The documentation is a little ambiguous. If I make raw SQL queries using the binding, are there global read replicas? Is there just one instance of SQLite running?

Rrivage The documentation is a little ambiguous. If I make raw SQL queries using the bin...

C

Chaika•1/15/25, 11:43 PM

Currently there's just one instance, replicas are something they've talked about adding eventually

Rrivage The documentation is a little ambiguous. If I make raw SQL queries using the bin...

L

lambrospetrou•1/16/25, 12:11 AM

Where did you see replicas mentioned in the documentation? They are mentioned in a blog post as an upcoming feature, but not in the docs as far as I am aware.

Llambrospetrou Where did you see replicas mentioned in the documentation? They are mentioned in...

R

rivage•1/16/25, 12:16 AM

i said the documentation was ambigious, but i was confused by posts such as these: https://x.com/CloudflareDev/status/1774791372483998147

Cloudflare Developers (@CloudflareDev) on X

Wondering how we built D1, our globally distributed SQL database technology?
Learn more in this blog post.https://t.co/aXXKqlesgZ

X

•

4/1/24, 1:30 PM

Rrivage i said the documentation was ambigious, but i was confused by posts such as thes...

L

lambrospetrou•1/16/25, 6:47 AM

yes that post describes the upcoming read replication

Llambrospetrou Are you sure your changes on the index are applied? The workload before and afte...

L

Leo•1/16/25, 11:31 AM

It fixed itself somehow, but I don't ask why as long as it works

LLeo It fixed itself somehow, but I don't ask why as long as it works 🙂

L

lambrospetrou•1/16/25, 12:02 PM

I really suggest checking your database insights for your queries. If you are on a FREE plan, we will soon enforce limits, and the above usage is definitely above those limits. So, to avoid any breakage in your application, tune them before that time comes.

B

Bulbasaur•1/16/25, 3:46 PM

Hi, I’m wondering if there’s any news regarding d1 becoming actually distributed?

My company has been early adopters since ~2 years ago during the beta, but unfortunately it not actually being distributed has been a persistent latency issue for us.

Combined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/

B

Bulbasaur•1/16/25, 3:48 PM

With something like AWS DSQL coming down the pipe for truly serverless, multi region active Postgres viable, it’s becoming harder to justify D1 which hasn’t improved much in this realm…

BBulbasaur Hi, I’m wondering if there’s any news regarding d1 becoming actually distributed...

L

lambrospetrou•1/16/25, 3:54 PM

D1 latencies dropped almost half over the past few weeks, when using the Workers API (not the REST API).
Read replication is coming in the next few weeks/months, when it's fully tested and ready.

If you are accessing D1 through a Worker now, you should be having competitive latencies with accessing a centralized database in a single region, unless your compute is also in the same region. There is no magic for speed of light.

Combined with db round trips (even with smart placement) being consistently high latency, has led us to have to move to Postgres. We notice that “cold start” times and round trips are consistently better from across the globe, even with a single Postgres instance in us-east :/

Can you provide any comparison metrics for this? If you use smart placement and have enough traffic, latencies now should be very similar between D1 and a postgres in a single location.
Are you using Hyperdrive with Postgres, or purely over the network?

M

Max (@rozenmd)OP•1/17/25, 6:12 PM

D1's metrics tab can now tell you a little bit about the queries your DB runs (essentially the start of building