😭 wth is happening here? Why is D1 unable to fulfill standard queries? Explanation: I got a Unix

wth is happening here?
Why is D1 unable to fulfill standard queries?

Explanation: I got a Unix timestamp and convert it via the

DATETIME

DATETIME

DATETIME

DATETIME SQL function to a datetime. When I then try to search that in the database it returns invalid rows which should never match as the date is to far back.

And time created_at column is of the type

timestamp

timestamp

timestamp

timestamp

KKavatch 😭 wth is happening here? Why is D1 unable to fulfill standard queries? Expla...

beelzabub•4/2/25, 5:00 PM

Try datetime(crated_at, 'unixepoch') < datetime(<value>, 'unixepoch')?

Bbeelzabub Like I can shard into a fixed pool of databases but certainly not per customer /...

Hard@Work•4/2/25, 5:00 PM

IIRC the current recommendation is to use SQLiteDOs

Hard@Work•4/2/25, 5:00 PM

Don't know whether that's the "official" thing or not

HHard@Work IIRC the current *recommendation* is to use SQLiteDOs

beelzabub•4/2/25, 5:01 PM

Do SQLite DOs have the migration utilities that D1 does?

Bbeelzabub Try datetime(crated_at, 'unixepoch') < datetime(<value>, 'unixepoch')?

KavatchOP•4/2/25, 5:03 PM

That results in no data being returned at all

SELECT * FROM crowd_levels WHERE DATETIME(created_at, 'unixepoch') < DATETIME(1743526055, 'unixepoch') ORDER BY created_at ASC LIMIT 3
This query returned no data.

SELECT * FROM crowd_levels WHERE DATETIME(created_at, 'unixepoch') < DATETIME(1743526055, 'unixepoch') ORDER BY created_at ASC LIMIT 3
This query returned no data.

I found out this bug only happens if I have the order by included in the query. If I leave it out the filtering works as I expect it too

KKavatch That results in no data being returned at all ``` SELECT * FROM crowd_levels WHE...

beelzabub•4/2/25, 5:04 PM

I'm pretty sure datetime converts into a string and you're trying to compare a number in the string in the first place and lexicographical strings in the second. Bad advice on my part - just omit the datetime altogether

beelzabub•4/2/25, 5:06 PM

You actually want unixepoch(created_at) < 1743526055

beelzabub•4/2/25, 5:06 PM

You should try out your queries in normal sqlite - I would be very surprised if the results differ from what cloudflare returns

Bbeelzabub Do SQLite DOs have the migration utilities that D1 does?

Hard@Work•4/2/25, 5:06 PM

Like, via the CLI?

Hard@Work•4/2/25, 5:06 PM

If that's the question, then no

Bbeelzabub You should try out your queries in normal sqlite - I would be very surprised if ...

KavatchOP•4/2/25, 5:06 PM

Never mind its all my fault

Hard@Work•4/2/25, 5:07 PM

If you use an ORM, there is some tooling to make it a bit smoother

KavatchOP•4/2/25, 5:07 PM

<< it should have been >>

KavatchOP•4/2/25, 5:07 PM

Sveinn•4/2/25, 9:57 PM

Hey, wondering if anyone here has any tips or experience with this. Cloudflare charged my credit card a huge amount and I haven’t gotten a proper explanation from them, even after reaching out to support multiple times over the past two months. All I’ve gotten are generic “we’re looking into it” replies.

It’s gotten to the point where I can’t afford to pay the credit card bill, and it’s messing with rent and other important stuff. Has anyone had luck actually getting a real response from Cloudflare support? Or escalated it somehow?

And has anyone ever done a chargeback with Cloudflare? I’m seriously considering it because I don’t know what else to do.

ac•4/3/25, 12:29 AM

I have this same question (d1-database). Are dynamic bindings coming anytime soon, or has anyone found a usable pattern within Cloudflare to dynamically select a database at runtime?

Aac I have this same question (https://discord.com/channels/595317990191398933/99206...

Brayden•4/3/25, 12:34 AM

You can achieve dynamic SQLite databases with durable objects if that works for your use case, but dynamic bindings aren't avaialble afaik

Aac I have this same question (https://discord.com/channels/595317990191398933/99206...

kooliebwoykg•4/3/25, 1:50 AM

Cloudflare API to create the database and a D1 worker as an API with the binding. Then drizzle proxy to the api. Once setup, it works and I get to keep D1 batteries included and continue to use drizzle as well. Not pretty but it works.

Aac I have this same question (https://discord.com/channels/595317990191398933/99206...

lambrospetrou•4/3/25, 9:05 AM

If you need to shard across tens thousands (or more) databases, please use SQLite DOs. Even if D1 ships dynamic bindings, it's not happening in just a few weeks. Use DOs that provide that scalability already.
The underlying SQLite features are identical.

Llambrospetrou If you need to shard across tens thousands (or more) databases, please use SQLit...

Silvan•4/3/25, 10:58 AM

is there a good example of this? at the end of the day you still need to bind the DO or am i forgetting something? running wrangler d1 create dosent seem like the issue to me personally

SSilvan is there a good example of this? at the end of the day you still need to bind th...

lambrospetrou•4/3/25, 11:21 AM

You only bind the DO namespace once, and create millions of Durable Object instances. With D1, you need to create each one individually and add it in your wrangler config and redeploy.

Silvan•4/3/25, 11:31 AM

well yea with the downside of needing to run migrations on every Do invocation because you cant know if its a new do/db right? at least thats what i did with my DO better-auth example

SSilvan well yea with the downside of needing to run migrations on every Do invocation b...

lambrospetrou•4/3/25, 11:45 AM

You do know if you check the storage and keep in memory while your DO is alive. Not sure why you would run your migrations all the time. All SQL schema tools/libraries record what migrations they already ran and don't do anything if there is nothing new.

Silvan•4/3/25, 11:53 AM

https://github.com/Sillyvan/better-auth-do-example/blob/main/src/server.ts

id still assume something like this brings a bit of overhead? but its not too big of a deal of course!

GitHub

better-auth-do-example/src/server.ts at main · Sillyvan/better-aut...

Contribute to Sillyvan/better-auth-do-example development by creating an account on GitHub.

Llambrospetrou If you need to shard across tens thousands (or more) databases, please use SQLit...

ac•4/3/25, 2:42 PM

I appreciate the response! Does this mean that for the tenant DBs we would have different DOs for each one, but we would only have to use one binding somehow?

ac•4/3/25, 2:42 PM

Forgive me if it's a dumb question, I haven't really used DOs but we have used D1 extensively

Aac I appreciate the response! Does this mean that for the tenant DBs we would have ...

Brayden•4/3/25, 3:42 PM

You could do something like the following for example where you have one Durable Object class in your wrangler binding (e.g. MY_DURABLE_OBJECT) but when you do the idFromNameidFromName you can pass something specific to the tenant you want. Each unique

id

id

creates its own isolated DO with its own SQLite storage.

let id = env.MY_DURABLE_OBJECT.idFromName('user_1');
let stub = env.MY_DURABLE_OBJECT.get(id);
return stub.fetch(request);

let id = env.MY_DURABLE_OBJECT.idFromName('user_1');
let stub = env.MY_DURABLE_OBJECT.get(id);
return stub.fetch(request);

So above I could change user_1user_1 to be any of my users and I'd be routed to their specific DO instance.

BBrayden You could do something like the following for example where you have one Durable...

ac•4/3/25, 4:03 PM

That's helpful, thank you!!

Aac That's helpful, thank you!!

lambrospetrou•4/3/25, 5:01 PM

Take a look into https://developers.cloudflare.com/reference-architecture/diagrams/storage/durable-object-control-data-plane-pattern/ as well.

Cloudflare Docs

Control and data plane architectural pattern for Durable Objects ·...

Durable Objects are built on-top of Cloudflare Workers spanning several locations across our global infrastructure network.
Each Durable Object instance has its own durable storage persisted across requests, in-memory state, single-threaded execution, and can be placed in a specific region.

coinnp•4/4/25, 7:46 AM

Why I'm getting database disk image is malformed: SQLITE_CORRUPT [code: 7500]

Ccoinnp Why I'm getting database disk image is malformed: SQLITE_CORRUPT [code: 7500]

Acе•4/4/25, 10:58 AM

Because the database disk image is malformed

jakepez•4/4/25, 5:44 PM

Hi all - Does a D1 batch() count as a single (1) sub-request against the Workers limit or are the individual transactions in the batch counted against the subrequest limit?

Original message was deleted

jakepez•4/4/25, 5:57 PM

Thank you!

Victor•4/4/25, 9:50 PM

I'm weirdly getting WAF bot detection blocked on api.cloudflare.comapi.cloudflare.com hitting the d1 /query/query
one of the rays: 92b3ef8f6aea1703-SJC92b3ef8f6aea1703-SJC

Minions•4/4/25, 11:38 PM

Hi @Walshy

Minions•4/4/25, 11:38 PM

Nice to meet you

Minions•4/4/25, 11:38 PM

Having a nice weekend?

Minions•4/4/25, 11:55 PM

I need some advice.

Minions•4/4/25, 11:55 PM

Is it possible to access coudflare d1 database outside of worker/page?

MMinions Hi @Walshy

Cyb3r-Jak3•4/4/25, 11:58 PM

?pings

SuperHelpflare•4/4/25, 11:58 PM

Please do not ping community members for non-moderation reasons. Doing so will not solve your issue faster and will make people less likely to want to help you.

MMinions Is it possible to access coudflare d1 database outside of worker/page?

Cyb3r-Jak3•4/4/25, 11:59 PM

The general recommendation is to deploy a worker which acts as a HTTP API for your D1 database. You can just use the barebones Cloudflare HTTP API but that is slower

SSuperHelpflare Please ***do not ping*** community members for non-moderation reasons. Doing so ...

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

okay

CCyb3r-Jak3 The general recommendation is to deploy a worker which acts as a HTTP API for yo...

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

So, I need to access Worker, not D1 database, right?

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

How can I get the url of Cloudflare D1 database, so that I can set the db attribute of Payload CMS backend?

Cyb3r-Jak3•4/5/25, 12:12 AM

?d1-remote-connections

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

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.

😭 wth is happening here? Why is D1 unable to fulfill standard queries? Explanation: I got a Unix

Similar Threads

Similar Threads

Similar Threads