Is there a cost different on sqllite in do vs using d1 and any other limiting factors?

crossbeauOP•3/26/25, 11:23 PM

Im working on building a discord bot, with d1 as a backend, but considering speed is important with an interactive bot kind of tempted to switch. Especially as all commands come from minimally changing Data centers

Ccrossbeau Is there a cost different on sqllite in do vs using d1 and any other limiting fa...

Isaac McFadyen•3/27/25, 12:24 AM

SQLite in DO is limited to 1GB per instantiated DO at the moment.

Isaac McFadyen•3/27/25, 12:25 AM

That limit should increase to 10GB (same as D1) once it's out of beta, though.

Ccrossbeau Im working on building a discord bot, with d1 as a backend, but considering spee...

James•3/27/25, 4:15 AM

You pay wall time on DO. And expensive computations in sql will cost you cpu time plus R/W in DO. The upside is you can do fancy stored procedure type app logic on the same thread as your DB with DO.

Florencia•3/27/25, 12:08 PM

@Isaac McFadyen

Ccrossbeau Is there a cost different on sqllite in do vs using d1 and any other limiting fa...

John Spurlock•3/27/25, 3:19 PM

you've probably already considered this, but on the cost side: after you've burned through the free tier, D1 storage is also 3.75x more expensive than SQLite in DO

Sébastien Morel•3/27/25, 5:48 PM

hello, is there something going on ?

Screenshot_2025-03-27_at_10.48.06_AM.png

SSébastien Morel hello, is there something going on ?

lambrospetrou•3/27/25, 7:32 PM

There isn't anything service wide. What queries are you doing? That seems like a scan to me. You already have weird spikes before for queries taking 500ms.

Sébastien Morel•3/27/25, 9:11 PM

hey @lambrospetrou thx for this, I am investigating, it went down already but that's odd it's like 4.5 sec !

xeon06•3/29/25, 8:19 PM

Is it possible to create a read only endpoint for D1? For dashboard stats and such for instance. It looks like via the rest API I could possibly just use a

D1 Read

D1 Read

token API token and then any writes would fail? Doesn't seem like there's a way to do this through workers API

Xxeon06 Is it possible to create a read only endpoint for D1? For dashboard stats and su...

Hello, I’m Allie!•3/29/25, 8:47 PM

You could just expose a Worker with some pre-determined routes that only ever perform selects?

xeon06•3/30/25, 4:14 AM

I want the queries to be driven by the UI à la BI tools like Periscope and Tableau

Marcel Overdijk•3/31/25, 3:04 PM

As per docs D1 has TypeScript support for representing rows like:

// Row definition
type OrderRow = {
  Id: string;
  CustomerName: string;
  OrderDate: number;
};

// Elsewhere in your application
const result = await env.MY_DB.prepare(
  "SELECT Id, CustomerName, OrderDate FROM [Order] ORDER BY ShippedDate DESC LIMIT 100",
).run<OrderRow>();

// Row definition
type OrderRow = {
  Id: string;
  CustomerName: string;
  OrderDate: number;
};

// Elsewhere in your application
const result = await env.MY_DB.prepare(
  "SELECT Id, CustomerName, OrderDate FROM [Order] ORDER BY ShippedDate DESC LIMIT 100",
).run<OrderRow>();

What if the database has snake case column names, but I want to keep camel case;
is there a simple (automatic) to convert his?

e.g. like Drizzle supports with

const db = drizzle({ connection: process.env.DATABASE_URL, casing: 'snake_case' })

const db = drizzle({ connection: process.env.DATABASE_URL, casing: 'snake_case' })

If not wouldn't that a nice feature request?

Marcel Overdijk•3/31/25, 3:04 PM

(I'm trying to avoid writing a lot of mappers)

Marcel Overdijk•3/31/25, 3:05 PM

or maybe best to use something like https://www.npmjs.com/package/camelcase-keys ?

MMarcel Overdijk As per docs D1 has TypeScript support for representing rows like: ``` // Row de...

lambrospetrou•3/31/25, 3:27 PM

No, out of the box we don't do that name conversion for you with the current D1 binding.

Llambrospetrou No, out of the box we don't do that name conversion for you with the current D1 ...

Marcel Overdijk•3/31/25, 4:51 PM

Is it worth a feature request? (like the Drizzle example)

Burrito•3/31/25, 4:55 PM

This is probably something better done in user land.

Nicolasmp•3/31/25, 7:48 PM

Hi folks! We are looking to use D1 for a multi tenant application. We are looking to ways to keep d1 databases isolated at a tenant level. Right now the only limitation I see is that bindings are static, is there any way to dynamically bind to my d1 instances? Or any other recommendation on how to achieve this?

MMarcel Overdijk Is it worth a feature request? (like the Drizzle example)

Isaac McFadyen•3/31/25, 8:30 PM

Probably not, D1 is SQLite and SQLite doesn't perform any inspection of the query itself because it's intended as a raw SQL executor. Adding this behavior would require adding non-standard parsing in front of SQLite.

Original message was deleted

xeon06•4/1/25, 1:36 AM

It's what all these BI tools let you do

gruntlord6•4/1/25, 2:42 AM

Is there a place I can accuratly see how many reads/writes I have done in a billing period? The billing section says I have done 0 which is very much not true, although if you want to leave it that way for calculating billing I'm ok with it

Xxeon06 It's what all these BI tools let you do

sathoro•4/1/25, 3:45 AM

no, they don't. they generate SQL from sanitized user input, they don't use raw input as the query

Vlady•4/1/25, 3:42 PM

Hey folks, what is D1's current limit for requests per second for paid accounts? I found this response but not sure if it applies. d1-database

Vlady•4/1/25, 3:44 PM

I'm working on a ticket service with high surge potential and I need to let my customers know.

VVlady Hey folks, what is D1's current limit for requests per second for paid accounts?...

Isaac McFadyen•4/1/25, 3:45 PM

That applies, yup.

Isaac McFadyen•4/1/25, 3:45 PM

If you need more performance/capacity you'll need to shard across multiple DBs.

mr.niko.la•4/2/25, 3:16 PM

is there a recommenday way to get better ux on the d1. new to cf and sqlite. the webgui is very basic. is there any recomendation?

do i just get local replicate and even then what is a good viewer for the db

Mmr.niko.la is there a recommenday way to get better ux on the d1. new to cf and sqlite. t...

lambrospetrou•4/2/25, 3:22 PM

You can use https://www.outerbase.com/blog/now-supporting-cloudflare-d1/ although I haven't personally tested it, but saw happy users before.

Llambrospetrou You can use https://www.outerbase.com/blog/now-supporting-cloudflare-d1/ althoug...

mr.niko.la•4/2/25, 3:40 PM

not bad was very easy to setup and much better ux. should be ok for now thx.

Silvan•4/2/25, 3:59 PM

am i the only one that just sqlite3s the db file inside .wrangler?

beelzabub•4/2/25, 4:57 PM

The docs say this:

Each D1 database can store up to 10 GB of data, and you can create up to thousands of separate D1 databases. This allows you to split a single monolithic database into multiple, smaller databases, thereby isolating application data by user, customer, or tenant

But AFAICT this guidance is purely theoretical as there's no way to actually do the sharding?

beelzabub•4/2/25, 4:58 PM

Is there something obvious I'm missing?

beelzabub•4/2/25, 4:59 PM

Like I can shard into a fixed pool of databases but certainly not per customer / tenant. And the number of bindings to manage grows quite severely with a larger number of shards.

Kavatch•4/2/25, 4:59 PM

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

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

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')?

Kavatch•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 ...

Kavatch•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

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

<< it should have been >>

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

Is there a cost different on sqllite in do vs using d1 and any other limiting factors?

Similar Threads

Similar Threads

Similar Threads