not yet, we're working on it

A

Hi! I've some questings about D1. I'm curious about how does it handle concurrent writes/read. I haven't seen anything on the docs. Does write lock the database, table, or just the row?
Also I'm curious about how many write operations per second can it handle on avg?

A

antonio eldelpan•5/22/24, 9:33 PM

I know intensive write operations are not the best fit for D1. But I want to know how well could it handle a peak of traffic when a lot of people start doing actions on the service that result on write operations being performed, like user registration, adding comments, or editing their settings

B

bun•5/22/24, 10:19 PM

is there no way to download the database anymore?

Bbun is there no way to download the database anymore?

C

Cyb3r-Jak3•5/22/24, 10:29 PM

https://developers.cloudflare.com/workers/wrangler/commands/#export ?

Cloudflare Docs

Commands - Wrangler · Cloudflare Workers docs

Create, develop, and deploy your Cloudflare Workers with Wrangler commands.

B

bun•5/22/24, 10:29 PM

does it consume reads

C

Cyb3r-Jak3•5/22/24, 10:30 PM

I'd be surprised if it didn't

B

bun•5/22/24, 10:33 PM

if the read count on dash.cloudflare.com updates immediatelly then it doesnt

MMatt Silverlock Can you still confirm it’s using the index for me? I want to see the output: htt...

V

Vikash•5/23/24, 4:00 AM

The error happened again today on same table, while it's a primary key index.

Here is the

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

result -

Bbun if the read count on dash.cloudflare.com updates immediatelly then it doesnt

M

Max (@rozenmd)OP•5/23/24, 5:46 AM

if our metrics could update immediately on dash.cloudflare.com that'd mean we solved the speed of light problem

N

notjoemartinez•5/23/24, 6:17 AM

I'm using D1 to keep track of objects in R2 and I have a function that syncs all R2 Etags and keys to a table from a cloudflare worker. I'm running into the error

Error: Too many API requests by single worker invocation

Error: Too many API requests by single worker invocation

Error: Too many API requests by single worker invocation

Error: Too many API requests by single worker invocation I know workers has a 1000 request invocation limit but I was wondering if there was a way insert large amounts of rows with a single request like pythons

executemany

executemany

executemany

executemany command. I'm currently inserting a single row per request like this

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

N

notjoemartinez•5/23/24, 6:24 AM

I see there is a batch() command but it looks like it's for running multiple db prepare statements not inserting multiple rows.

N

notjoemartinez•5/23/24, 6:47 AM

nvm I figured it out using

execute()

execute()

execute()

execute() on a giant sql insert statement string

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

Nnotjoemartinez nvm I figured it out using `execute()` on a giant sql insert statement string ``...

M

Max (@rozenmd)OP•5/23/24, 6:56 AM

on a big enough string sqlite will reject the query, also massive sql injection there

M

Max (@rozenmd)OP•5/23/24, 6:59 AM

something like

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

should work though?

B

bun•5/23/24, 2:28 PM

Does it consume reads or not? If yes, is there a way to copy/extract db in a way that it wont consume reads for everything in my database

B

bun•5/23/24, 2:29 PM

@Max (@rozenmd)

M

Max (@rozenmd)OP•5/23/24, 2:29 PM

we do not yet have a means of pulling your database out without reading it

VVikash The error happened again today on same table, while it's a primary key index. ...

L

landon•5/23/24, 3:52 PM

we have been seeing similar errors intermittently d1-database

Hhill Not worried about hitting the limit any time soon, but I find it odd that CF put...

H

Hello, I’m Allie!•5/23/24, 5:05 PM

IIRC they are going to increase it, but not infinitely

C

Chaika•5/23/24, 5:05 PM

?d1-scaling

F

Flare•5/23/24, 5:05 PM

The aim of D1 is to have you spread out your data over many D1 databases instead of having a single large one: d1-database

There is a 10GB size limit per database which cannot currently be raised. However, there is work being done to support Dynamic Bindings so you can dynamically create and query D1 Databases within your Worker instead of having to create and bind ahead of time.

The number of unique D1 Databases you can create is basically limitless, although there is a soft limit of 50k which can be raised (see the blog).
Support for Read Replicas is also coming to help with scaling (see the blog).

As it is right now, your D1 database is a single-threaded Durable Object living in a single region and so it has a total amount of requests per second, which may vary depending on how expensive your queries are.

C

Chaika•5/23/24, 5:06 PM

(even if it was raised, they've explicitly said before double digits make sense but not 100gb per db, so doubt it would go that high/far. At the end of the way they want you to scale across many dbs)

Mminderov How does pricing work on importing large SQlite databases into D1? Following thi...

C

Chaika•5/23/24, 5:07 PM

it's the same as executing a statement so it should charge you, might just be a bit slow and they did just have an analytics incident. Out of curiosity, what has 4 billion rows and would fit in 10 gb?

C

Chaika•5/23/24, 5:08 PM

worth noting if you don't do --remote it just did it locally

Hhill Any idea how I could go about sharding or sharing db resources? Figure I could c...

C

Chaika•5/23/24, 5:15 PM

it depends on how your data is structured on how you should shard it.
There are patterns that make more sense, for example if each user has a lot of data/sort of their own instance, shard by user. If you do it by table you might eventually run out anyway, as I imagine most setups would have a few huge tables and not just a ton of smaller ones, but again that depends.

C

Chaika•5/23/24, 5:16 PM

D1 will eventually... support dynamic bindings so you can freely create and bind to D1 DBs

C

Chaika•5/23/24, 5:16 PM

as of right now you'd need to bind ahead of time or use api to query (the latter being generally a bad idea due to the API funneling all requests back to core, and then to edge db)

C

Chaika•5/23/24, 5:37 PM

there's not really any magic for that right now. When you bind your db in wrangler.toml you specify the name

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB2" # available in your Worker on env.DB
database_name = "prod-d1-tutorial-v2"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB2" # available in your Worker on env.DB
database_name = "prod-d1-tutorial-v2"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB2" # available in your Worker on env.DB
database_name = "prod-d1-tutorial-v2"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB" # available in your Worker on env.DB
database_name = "prod-d1-tutorial"
database_id = "<unique-ID-for-your-database>"

[[d1_databases]]
binding = "DB2" # available in your Worker on env.DB
database_name = "prod-d1-tutorial-v2"
database_id = "<unique-ID-for-your-database>"

then you would just send your queries to them (env.DB and env.DB2) as normal. You could query them in parallel to try to help with the performance

C

Cory•5/23/24, 9:30 PM

Anyone using D1 with Drizzle + SvelteKit?

I am wondering how to handle prepared statements since you have to run D1 from the events rather just initiating like normal Databases

CCory Anyone using D1 with Drizzle + SvelteKit? I am wondering how to handle prepare...

H

Hello, I’m Allie!•5/23/24, 10:11 PM

Create a Drizzle Instance on request, if that is what you mean?

HHello, I’m Allie!Create a Drizzle Instance on request, if that is what you mean?

C

Cory•5/23/24, 10:45 PM

Yea but before I had a file with all prepared queries that imported the database instance, so I could import a prepared query if needed. Now I am not sure how to handle them, since I can only access the instance within the request

C

Cory•5/23/24, 10:46 PM

I guess I would have to create a function for each one and then pass in the DB instance as a param? But then are they being cached properly?

C

Cory•5/23/24, 10:46 PM

And every time I call the function it would have to re initiate the prepared statement instead of having it in memory>

C

Cory•5/23/24, 10:47 PM

Unless I am missing something

CCory I guess I would have to create a function for each one and then pass in the DB i...

H

Hello, I’m Allie!•5/23/24, 10:49 PM

Prepared Statement caching happens in the database, not in the Worker, so rebuilding it from scratch on every request is fine

HHello, I’m Allie!Prepared Statement caching happens in the database, not in the Worker, so rebuil...

C

Cory•5/23/24, 10:51 PM

ohh interesting

C

Cory•5/23/24, 10:51 PM

That makes more sense

A

arguskao•5/24/24, 8:40 AM

這裡應該沒有人講中文吧

Aarguskao 這裡應該沒有人講中文吧

H

Hello, I’m Allie!•5/24/24, 8:44 AM

我們這個伺服器沒有任何中文頻道，如果這是你的意思？

HHello, I’m Allie!我們這個伺服器沒有任何中文頻道，如果這是你的意思？

A

arguskao•5/24/24, 10:08 AM

我想如果有人講中文就好了

H

Hyper•5/24/24, 11:50 AM

Hey guys. Im using drizzle with D1. Left the project for around 6 months and when I got back to it, im now getting this error when using transactions:

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.

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.

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.

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.

How should I fix this? rewrite the code to use batch statements instead or are sql transactions now supported?

HHyper Hey guys. Im using drizzle with D1. Left the project for around 6 months and whe...

M

Matt Silverlock•5/24/24, 12:35 PM

You should use the batch API. This error shouldn’t be propagated back to users - will fix.

Similar Threads

Similar Threads

Similar Threads