Any chance there will be togglable support for local/remote DBs?

H

Atm you can have local support by having a custom config that hard-points to the

.sqlite

.sqlite

.sqlite

.sqlite file in

.wrangler

.wrangler

, but that seems kind of jank

H

Hello, I’m Allie!OP•5/22/24, 3:37 PM

Also that you have to switch out the configs manually

HHello, I’m Allie!Any chance there will be togglable support for local/remote DBs?

M

Matt Silverlock•5/22/24, 3:37 PM

Best to ask the Drizzle folks / file an issue to ask!

A

Alex•5/22/24, 3:52 PM

I've got a quick question - if I create a migration, which say creates a table

User

User

User

User, then run

DROP TABLE User;

DROP TABLE User;

DROP TABLE User;

DROP TABLE User; - is it expected that running the migration command again returns

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!?

AAlex I've got a quick question - if I create a migration, which say creates a table `...

A

Alex•5/22/24, 3:54 PM

This came about because I use the wrong value for a field

id

id

, but running the migration again gave me the no migrations to apply message. So I figured dropping the table and running the migration again would work. Am I misunderstanding how you update tables?

K

kingmesal•5/22/24, 3:55 PM

In the last day I've experienced at least 2 different occasions of

Error: D1_ERROR: D1 storage operation exceeded timeout which caused object to be reset.

Anything going on?

P

Pavlo•5/22/24, 6:38 PM

Hi guys, I have just read an article about the Global D1 database with read replicas, do you know if this feature has been released? https://blog.cloudflare.com/building-d1-a-global-database/?utm_campaign=cf_blog&utm_content=20240401&utm_medium=organic_social&utm_source=facebook,linkedin,twitter

The Cloudflare Blog

Building D1: a Global Database

D1, Cloudflare’s SQL database, is now generally available. With new support for 10GB databases, data export, and enhanced query debugging, we empower developers to build production-ready applications with D1 to meet all their relational SQL needs

PPavlo Hi guys, I have just read an article about the Global D1 database with read repl...

M

Max (@rozenmd)•5/22/24, 6:40 PM

not yet, we're working on it

A

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

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

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN result -

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

M

Max (@rozenmd)•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()

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)•5/23/24, 6:56 AM

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

M

Max (@rozenmd)•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)•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!OP•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!OP•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

Any chance there will be togglable support for local/remote DBs?

Similar Threads

Similar Threads

Similar Threads