Hi, is there a limit on the number of statements in a batch call? - Cloudflare Developers

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

Gerbuuun

G

Gerbuuun•6/7/24, 9:31 AM

I use auto incrementing IDs for internal use (server and joins) and have a nanoid which is used by users

Original message was deleted

berniesumption

B

berniesumption•6/7/24, 9:31 AM

Yes ULIDs are great - provided that you don't mind leaking the creation time of every entity that you expose the ID of. They look nicer too

Also, I like to prefix my IDs with a short version of the table name, e.g. an employee might be

emp01FHZXHK8PTP9FVK99Z66GXQTX

emp01FHZXHK8PTP9FVK99Z66GXQTX

. I took that idea from Stripe, it makes it so much easier to debug when you have random IDs being passed around.

jepcd

Jjepcd Hi, is there a limit on the number of statements in a batch call?

Max (@rozenmd)

M

Max (@rozenmd)•6/7/24, 9:55 AM

how ever many queries you can fit before running out of memory

Max (@rozenmd)

M

Max (@rozenmd)•6/7/24, 9:56 AM

I've seen 100k queries in a batch

jepcd

Jjepcd Hi, is there a limit on the number of statements in a batch call?

notjoemartinez

N

notjoemartinez•6/8/24, 7:21 AM

The docs are a bit vague on this. I've done about 30k at once, the limit was that the query needs to run in under 30 seconds and I accomplished this by removing some triggers that were slowing it down. I think you still need to consider the number of bindings depending on if they are counted within the batch call. The upper limit for bindings is "approximately" 25 million.

notjoemartinez

Nnotjoemartinez The docs are a bit vague on this. I've done about 30k at once, the limit was tha...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/8/24, 7:24 AM

As you noted, the binding itself doesn’t place any limits on the number of statements, so the only actual limit is however many you can fit in memory, and that they are created before you overrun the CPU time limit

Singer

S

Singer•6/8/24, 9:46 AM

There's probably layers to this question but: D1 or supabase

Singer

SSinger There's probably layers to this question but: D1 or supabase

Hello, I’m Allie!

H

Hello, I’m Allie!•6/8/24, 9:51 AM

How big is your app? What kind of data are you storing? How many reads/writes are you doing?

Hello, I’m Allie!

HHello, I’m Allie!How big is your app? What kind of data are you storing? How many reads/writes ar...

Singer

S

Singer•6/8/24, 9:58 AM

I'm very new to backend developement and I'm taking things as they come. I'm not sure how big the app is going to get, but I need tables for users, weather data, news api data, events(id,date,actors involved, description, location, image blobs....)

I want to use workers AI which is why I was leaning towards d1 for better integration but I read somewhere that sqlite isn't great for handling lots of data. Again I'm not sure how big this thing can get and I'm fairly new to this realm . A tl;dr or any resources that I can use to improve my knowledge would be great

Singer

SSinger I'm very new to backend developement and I'm taking things as they come. I'm not...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/8/24, 10:10 AM

D1 should be fine? Though for larger datasets, I would probably go with Supabase

Singer

SSinger I'm very new to backend developement and I'm taking things as they come. I'm not...

maj

M

maj•6/8/24, 12:08 PM

Whatever "handling lots of data" means. Are you talking about millions of records or more? I'm working on an application which has users with > 1M records in a single table, with many relations to other tables. And it's working perfectly fine on a regular Windows PC using SQLite. I'd expect d1 to perform even better.

Michał Wróblewski

M

Michał Wróblewski•6/8/24, 1:08 PM

will it be ever possible to do ATTACH DATABASE on D1? It would be great to split some DBs but be able to query them together

Hello, I’m Allie!

HHello, I’m Allie!D1 should be fine? Though for larger datasets, I would probably go with Supabase

Singer

S

Singer•6/8/24, 4:09 PM

Thanks, just don't want to get to a point in the near future where I regret going with d1 or sqlite but I don't think I'd reach 1m anytime soon so that's great. Thanks again for the clarity @maj @Hello, I’m Allie!

Iaci

I

Iaci•6/8/24, 9:56 PM

hi all, i'm developing a worker that has access to D1 databases through D1 database bindings (set as variables under D1 Database Bindings). how am i able to reach those databases when i'm developing locally? using queries like this: {results} = await env.IRADB.prepare(<here goes the query>).bind(<binds>).run();

Murder Chicken

M

Murder Chicken•6/8/24, 9:58 PM

Is it possible to request an increase in the column limit of 100? If so, what's the best route to do that on the Worker Paid plan?

Murder Chicken

M

Murder Chicken•6/8/24, 10:03 PM

Never mind, found the limit increase form!

Murder Chicken

MMurder Chicken Is it possible to request an increase in the column limit of 100? If so, what's...

bun

B

bun•6/9/24, 12:52 AM

bro for what possibly you have 100+ columns if i may ask

Murder Chicken

M

Murder Chicken•6/9/24, 2:18 PM

I'd like to work with a different table model for a storing calendar availability data.

Murder Chicken

M

Murder Chicken•6/9/24, 2:20 PM

As sqlite doesn't offer

pivot

pivot

or

crosstab

crosstab

queries, calendar querying (for the type of data I'm dealing with) is very complex. I'd like to try a different approach. One column per day per resource with the value of the availability stored as a

tinyint

tinyint

.

quisi.do

Q

quisi.do•6/9/24, 5:20 PM

Is it realistic to run 2 queries per request? I'm wanting to query something like

SELECT id FROM projects WHERE url = ?

SELECT id FROM projects WHERE url = ?

then

SELECT something FROM metadata WHERE id = ?

SELECT something FROM metadata WHERE id = ?

using that ID (where there could be multiple

something

something

s in the latter table).
I'm worried this design may bottleneck me. My last experience with databases was PHP/MySQL era, and the sheer number of queries ran on pageload was the most common bottleneck for me back then. Is this an acceptable pattern?

AmaraFray

A

AmaraFray•6/9/24, 8:06 PM

has anyone tried to make dynamic tables

i have multiple organisations (A,B,C,D) part of my application, with each having tables 1-10

so each table is A1,A2,A3.. and so on. I want 1-10 to have the same structure (is it possible to template, like using the 'like' operator in sql)

or is there a better approach, I could merge it all into one, but I'd rather not as each is distributed to a different place, and i want it to be optimised like that

AmaraFray

A

AmaraFray•6/9/24, 8:07 PM

and if you have, any chance u can suggest your orm structure too

quisi.do

Qquisi.do Is it realistic to run 2 queries per request? I'm wanting to query something lik...

IEatBeans

I

IEatBeans•6/9/24, 10:42 PM

Could you use joins instead of 2 separate queries? You may be able to use batch as well, though I’m not sure because you are using the Id from the first one in the second one.

Either way, D1 is really fast and many of my routes involve 3-5 queries (most batched)

dpkp

D

dpkp•6/10/24, 1:00 AM

what's the best way to drop a local d1 db? I looked around for commands but didn't find any docs. I ended up manually finding and removing sqlite files in .wrangler/state/v3/d1 ...

predaytor

P

predaytor•6/10/24, 7:38 AM

Does D1 support transactions? Seems like not:

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
Error: D1_ERROR: not authorized
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
Error: D1_ERROR: not authorized
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

몰라세스

몰

몰라세스•6/10/24, 7:38 AM

not supported? maybe.

predaytor

Ppredaytor Does D1 support transactions? Seems like not: ```bash To execute a transaction, ...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/10/24, 9:04 AM

All queries/batches are run within transactions,but you can’t manually create your own

predaytor

P

predaytor•6/10/24, 11:21 AM

what is the correct way to store a reference to an uploaded cloudflare r2 image in the database? save id of the uploaded image, and then somehow get the necessary data such as width/height and other metadata (causing waterfalls?) or save the data during upload directly to the separate table, for example:

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: text('avatar_id', { mode: 'text', length: 255 }),
});

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: text('avatar_id', { mode: 'text', length: 255 }),
});

vs

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: integer('avatar_id').references(() => media.id, { onDelete: 'set null' }),
});

// ...

export const media = sqliteTable('media', {
    id: integer('id').notNull().primaryKey(),
    createdAt: integer('created_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    fileName: text('file_name', { mode: 'text', length: 255 }).notNull(),
    mediaType: text('file_type', { mode: 'text' }).notNull(),
    mimeType: text('mime_type', { mode: 'text', length: 100 }).notNull(),
    size: integer('size', { mode: 'number' }).notNull(),
    width: integer('width', { mode: 'number' }).notNull(),
    height: integer('height', { mode: 'number' }).notNull(),
    src: text('src', { mode: 'text' }).notNull(),
    srcSet: text('srcset').notNull(),
    alt: text('alt', { mode: 'text' }).notNull(),
});

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: integer('avatar_id').references(() => media.id, { onDelete: 'set null' }),
});

// ...

export const media = sqliteTable('media', {
    id: integer('id').notNull().primaryKey(),
    createdAt: integer('created_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    fileName: text('file_name', { mode: 'text', length: 255 }).notNull(),
    mediaType: text('file_type', { mode: 'text' }).notNull(),
    mimeType: text('mime_type', { mode: 'text', length: 100 }).notNull(),
    size: integer('size', { mode: 'number' }).notNull(),
    width: integer('width', { mode: 'number' }).notNull(),
    height: integer('height', { mode: 'number' }).notNull(),
    src: text('src', { mode: 'text' }).notNull(),
    srcSet: text('srcset').notNull(),
    alt: text('alt', { mode: 'text' }).notNull(),
});

JoeDoe

J

JoeDoe•6/10/24, 6:42 PM

how to cache d1 requests?

JoeDoe

J

JoeDoe•6/10/24, 6:42 PM

I'm using workers for a web api, but I need simple data like total users to be cached for like 60seconds or similiar

JoeDoe

JJoeDoe how to cache d1 requests?

Unsmart

U

Unsmart•6/10/24, 6:57 PM

the cache api https://developers.cloudflare.com/workers/runtime-apis/cache/

Unsmart

UUnsmart the cache api <https://developers.cloudflare.com/workers/runtime-apis/cache/>

JoeDoe

J

JoeDoe•6/10/24, 7:05 PM

is there some good examples on how to use this with workers?

JoeDoe

JJoeDoe is there some good examples on how to use this with workers?

Max (@rozenmd)

M

Max (@rozenmd)•6/10/24, 7:14 PM

https://developers.cloudflare.com/workers/examples/cache-api/

Cloudflare Docs

Using the Cache API · Cloudflare Workers docs

Documentation for Cloudflare Workers, a serverless execution environment that allows you to create entirely new applications or augment existing ones …

Using the Cache API · Cloudflare Workers docs

JoeDoe

J

JoeDoe•6/10/24, 7:21 PM

hmmmmm interesting thank you

maj

M

maj•6/10/24, 10:45 PM

FWIW: I'm using KV to cache some query results I'd like to share across requests. The Cache API would only cache the result of HTTP requests, wouldn't it?

Unsmart

U

Unsmart•6/10/24, 11:06 PM

The cache api can cache anything... normal http requests you can cache with just the fetch api

maj

M

maj•6/11/24, 4:45 AM

Maybe I'm missing the obvious, but you have to pass it a response object, with caching headers set etc. How would I define the expiration if I wanted to cache a random blob?

maj

M

maj•6/11/24, 4:46 AM

From https://developers.cloudflare.com/workers/reference/how-the-cache-works/#cache-api:

The Cache API can be thought of as an ephemeral key-value store, whereby the Request object (or more specifically, the request URL) is the key, and the Response is the value.

Cloudflare Docs

How the Cache works · Cloudflare Workers docs

How Workers interacts with the Cloudflare cache.

How the Cache works · Cloudflare Workers docs

maj

Mmaj From https://developers.cloudflare.com/workers/reference/how-the-cache-works/#ca...

Max (@rozenmd)

M

Max (@rozenmd)•6/11/24, 7:21 AM

make a new Response, put your blob in the body

Max (@rozenmd)

MMax (@rozenmd)make a new Response, put your blob in the body

maj

M

maj•6/11/24, 7:30 AM

My comment was in response to use @Unsmart | Tech debt claiming that the Cache API could cache anything. But you basically confirm that I can't cache the blob alone (because I want to reuse it in other handlers), don't you? Using the Cache API I can only cache the full response?

maj

Mmaj My comment was in response to use @Unsmart | Tech debt claiming that the Cache A...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 7:39 AM

Well no? You create a new, completely separate Response, that is just the blob+cache headers. That Response is never served to the client, it is just used to interact with the Cache API

Hello, I’m Allie!

HHello, I’m Allie!Well no? You create a new, completely separate Response, that is just the blob+c...

maj

M

maj•6/11/24, 9:15 AM

It's not a general purpose cache, is it? It's based on HTTP requests and responses. Even if you provide a random string as the cache key "it is interpreted as the URL for a new Request object." (https://developers.cloudflare.com/workers/runtime-apis/cache/). So in order to cache the raw result of a d1 query I'd have to wrap and unwrap the result. This only adds complexity and overhead, and still doesn't provide a global cache like using KV does. The original question was "how to cache d1 requests?". Not "how to cache http requests".

maj

Mmaj It's not a general purpose cache, is it? It's based on HTTP requests and respons...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:19 AM

Well, yeah, it isn't a perfect system, but it is the best option to do it for free. You can use KV/DOs/an external service like Redis, but that would be at cost to you

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:19 AM

And it not being a "general-purpose" cache isn't much of an issue. Open a custom cache that you reserve for D1 Caching, then use bogus URLs

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:20 AM

It won't be cached in more than one datacenter, but it is better than no cache at all

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:24 AM

Though it would be cool to have an arbitrary-object cache that didn't require wrapping stuff in a Request/Response, especially if it could auto-serialize values like DO storage can

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:24 AM

Since that is one thing that you can't do manually unless you bring a serializer yourself(which is kind of heavy-handed)

Max (@rozenmd)

M

Max (@rozenmd)•6/11/24, 9:32 AM

you could also write an abstraction on top of the cache api so it'd just be

.get(key)

.get(key)

and

.put(key,value)

.put(key,value)

Max (@rozenmd)

MMax (@rozenmd)you could also write an abstraction on top of the cache api so it'd just be `.ge...

Hello, I’m Allie!

H

Hello, I’m Allie!•6/11/24, 9:34 AM

That too

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
Error: D1_ERROR: not authorized
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

To execute a transaction, please use the state.storage.transaction() API instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
Error: D1_ERROR: not authorized
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: integer('avatar_id').references(() => media.id, { onDelete: 'set null' }),
});

// ...

export const media = sqliteTable('media', {
    id: integer('id').notNull().primaryKey(),
    createdAt: integer('created_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    fileName: text('file_name', { mode: 'text', length: 255 }).notNull(),
    mediaType: text('file_type', { mode: 'text' }).notNull(),
    mimeType: text('mime_type', { mode: 'text', length: 100 }).notNull(),
    size: integer('size', { mode: 'number' }).notNull(),
    width: integer('width', { mode: 'number' }).notNull(),
    height: integer('height', { mode: 'number' }).notNull(),
    src: text('src', { mode: 'text' }).notNull(),
    srcSet: text('srcset').notNull(),
    alt: text('alt', { mode: 'text' }).notNull(),
});

export const user = sqliteTable('user', {
    id: integer('id').notNull().primaryKey(),
    avatarId: integer('avatar_id').references(() => media.id, { onDelete: 'set null' }),
});

// ...

export const media = sqliteTable('media', {
    id: integer('id').notNull().primaryKey(),
    createdAt: integer('created_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    updatedAt: integer('updated_at', { mode: 'timestamp' })
        .notNull()
        .default(sql`(unixepoch())`),
    fileName: text('file_name', { mode: 'text', length: 255 }).notNull(),
    mediaType: text('file_type', { mode: 'text' }).notNull(),
    mimeType: text('mime_type', { mode: 'text', length: 100 }).notNull(),
    size: integer('size', { mode: 'number' }).notNull(),
    width: integer('width', { mode: 'number' }).notNull(),
    height: integer('height', { mode: 'number' }).notNull(),
    src: text('src', { mode: 'text' }).notNull(),
    srcSet: text('srcset').notNull(),
    alt: text('alt', { mode: 'text' }).notNull(),
});