nvm I figured it out using `execute()` on a giant sql insert statement string ```ts let 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!•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.

M

Martial•5/24/24, 3:03 PM

Encountered the same error, I was making a transaction so that I can get the return value of the first statement and use it in the second statement. Is this achievable using the batch API?

MMartial Encountered the same error, I was making a transaction so that I can get the ret...

H

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

No. If you can combine them into a single query, it might be possible, but afaik there isn't any JS-level transactions for D1 yet

N

nejc•5/24/24, 3:49 PM

hi all, is experimental-backend still parameter that is used for D1 create API? I see it in wrangler, but not sure if it does anything now?

M

Martial•5/24/24, 3:55 PM

I have been able to execute this locally with wrangler, but couldn't make this work in my code:

BEGIN TRANSACTION;

INSERT INTO user (email, name) VALUES ("my@email.com", "name");
SELECT last_insert_rowid() INTO @lastUserId;
INSERT INTO oauth (provider, user_id) VALUES ("google", @lastUserId);
COMMIT;

SELECT @lastUserID

BEGIN TRANSACTION;

INSERT INTO user (email, name) VALUES ("my@email.com", "name");
SELECT last_insert_rowid() INTO @lastUserId;
INSERT INTO oauth (provider, user_id) VALUES ("google", @lastUserId);
COMMIT;

SELECT @lastUserID

BEGIN TRANSACTION;

INSERT INTO user (email, name) VALUES ("my@email.com", "name");
SELECT last_insert_rowid() INTO @lastUserId;
INSERT INTO oauth (provider, user_id) VALUES ("google", @lastUserId);
COMMIT;

SELECT @lastUserID

BEGIN TRANSACTION;

INSERT INTO user (email, name) VALUES ("my@email.com", "name");
SELECT last_insert_rowid() INTO @lastUserId;
INSERT INTO oauth (provider, user_id) VALUES ("google", @lastUserId);
COMMIT;

SELECT @lastUserID

I got this error

A prepared SQL statement must contain only one statement

A prepared SQL statement must contain only one statement

. It's a useful kind of query that avoid one round trip.

Nnejc hi all, is experimental-backend still parameter that is used for D1 create API? ...

M

Max (@rozenmd)•5/24/24, 4:27 PM

the API ignores that parameter now, there's only the production backend available for new D1 dbs

MMartial I have been able to execute this locally with wrangler, but couldn't make this w...

M

Max (@rozenmd)•5/24/24, 4:28 PM

You'll need to remove the BEGIN TRANSACTON/COMMIT for D1 to work, it's already running your query in a DO transaction

J

Jens•5/24/24, 9:12 PM

Hi, I would like to fill my remote d1 database with my local data. Therefore I want to execute the following command:

❯ wrangler d1 execute database --file=data.sql

❯ wrangler d1 execute database --file=data.sql

But I get this error:

🌀 Mapping SQL input into an array of statements
🌀 Parsing 180 statements
🌀 Executing on database (...):

✘ [ERROR] A request to the Cloudflare API (/accounts/.../d1/database/.../query) failed.

  not authorized [code: 7500]

🌀 Mapping SQL input into an array of statements
🌀 Parsing 180 statements
🌀 Executing on database (...):

✘ [ERROR] A request to the Cloudflare API (/accounts/.../d1/database/.../query) failed.

  not authorized [code: 7500]

🌀 Mapping SQL input into an array of statements
🌀 Parsing 180 statements
🌀 Executing on database (...):

✘ [ERROR] A request to the Cloudflare API (/accounts/.../d1/database/.../query) failed.

  not authorized [code: 7500]

🌀 Mapping SQL input into an array of statements
🌀 Parsing 180 statements
🌀 Executing on database (...):

✘ [ERROR] A request to the Cloudflare API (/accounts/.../d1/database/.../query) failed.

  not authorized [code: 7500]

Does anyone know what the problem could be?
Thanks!

JJens Hi, I would like to fill my remote d1 database with my local data. Therefore I w...

L

Lemοn•5/24/24, 9:14 PM

authentication token, are you logged in to your cf account via wrangler?

LLemοn authentication token, are you logged in to your cf account via wrangler?

J

Jens•5/24/24, 9:16 PM

yes.

I created the sql file from my local miniflare sqlite db

sqlite3 f025eb6fc80fddcfe00b28c2b12457379be412d5d8d8fd306b42c59a9536d18c.sqlite .dump > data.sql

sqlite3 f025eb6fc80fddcfe00b28c2b12457379be412d5d8d8fd306b42c59a9536d18c.sqlite .dump > data.sql

sqlite3 f025eb6fc80fddcfe00b28c2b12457379be412d5d8d8fd306b42c59a9536d18c.sqlite .dump > data.sql

sqlite3 f025eb6fc80fddcfe00b28c2b12457379be412d5d8d8fd306b42c59a9536d18c.sqlite .dump > data.sql

I feel like the sql file cause issues

JJens yes. I created the sql file from my local miniflare sqlite db ``` sqlite3 f025e...

J

Jens•5/24/24, 9:33 PM

Ohh, it was the sql file. I had the remove the TRANSACTION, COMMIT, CREATE TABLE...

P

Pol•5/24/24, 11:09 PM

Hey, I'm getting

D1_ERROR: no such table: main.lists

D1_ERROR: no such table: main.lists

D1_ERROR: no such table: main.lists

D1_ERROR: no such table: main.lists when a trigger is ran. Might be something to do with main., but I don't know where it's coming from. Here is the trigger declaration:

CREATE TRIGGER limit_user_lists
BEFORE INSERT ON list_member
FOR EACH ROW
BEGIN
    SELECT (CASE
        WHEN (SELECT COUNT(*)
              FROM list_member lm
              INNER JOIN lists l ON lm.listId = l.id
              WHERE lm.userId = NEW.userId AND l.deteled = false) >= 20
        THEN
            RAISE(ABORT, 'User has reached the limit of 20 lists')
    END);
END;

CREATE TRIGGER limit_user_lists
BEFORE INSERT ON list_member
FOR EACH ROW
BEGIN
    SELECT (CASE
        WHEN (SELECT COUNT(*)
              FROM list_member lm
              INNER JOIN lists l ON lm.listId = l.id
              WHERE lm.userId = NEW.userId AND l.deteled = false) >= 20
        THEN
            RAISE(ABORT, 'User has reached the limit of 20 lists')
    END);
END;

CREATE TRIGGER limit_user_lists
BEFORE INSERT ON list_member
FOR EACH ROW
BEGIN
    SELECT (CASE
        WHEN (SELECT COUNT(*)
              FROM list_member lm
              INNER JOIN lists l ON lm.listId = l.id
              WHERE lm.userId = NEW.userId AND l.deteled = false) >= 20
        THEN
            RAISE(ABORT, 'User has reached the limit of 20 lists')
    END);
END;

CREATE TRIGGER limit_user_lists
BEFORE INSERT ON list_member
FOR EACH ROW
BEGIN
    SELECT (CASE
        WHEN (SELECT COUNT(*)
              FROM list_member lm
              INNER JOIN lists l ON lm.listId = l.id
              WHERE lm.userId = NEW.userId AND l.deteled = false) >= 20
        THEN
            RAISE(ABORT, 'User has reached the limit of 20 lists')
    END);
END;

Any idea?

P

Pol•5/24/24, 11:11 PM

The trigger needs to do an inner join with the lists table, but I don't really know where that main. is coming from

nvm I figured it out using `execute()` on a giant sql insert statement string ```ts let masiveInsert

Similar Threads

Similar Threads

Similar Threads