Anyone using D1 with Drizzle + SvelteKit? I am wondering how to handle prepared statements since y

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

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

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

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

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

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

ohh interesting

C

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

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

❯ 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

R

roftt•5/25/24, 3:03 AM

Hey! After I created ~3,000 databases in my account, commands like "npx wrangler d1 info my-db" are taking more than a minute to execute. It took a few seconds before. Is it a bug?

Rroftt Hey! After I created ~3,000 databases in my account, commands like "**npx wrangl...

M

Max (@rozenmd)•5/25/24, 8:32 AM

d1 info is sorely unoptimised, i think it does something silly like scan the entire list of DBs instead of searching by name

MMax (@rozenmd)d1 info is sorely unoptimised, i think it does something silly like scan the ent...

R

roftt•5/25/24, 11:57 AM

The same thing happens with exporting/importing: npx wrangler d1 execute my-db --remote --file=table.sql — it takes almost 2 minutes (the file is very small, containing only 1 row). But a simple command works fast, in just a few seconds: npx wrangler d1 execute my-db --remote --command="SELECT * FROM Customers". It definitely feels like the large number of databases affects this. Can someone confirm?

M

Martial•5/25/24, 1:48 PM

    const newUserId = await DB.prepare(
      `
    INSERT INTO user (email, name) VALUES (?,?);
    SELECT last_insert_rowid() INTO @lastID;
    INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ("google", ?, @lastID);
    SELECT @lastID;`,
    )
      .bind(email, name, provider_user_id)
      .run();

    const newUserId = await DB.prepare(
      `
    INSERT INTO user (email, name) VALUES (?,?);
    SELECT last_insert_rowid() INTO @lastID;
    INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ("google", ?, @lastID);
    SELECT @lastID;`,
    )
      .bind(email, name, provider_user_id)
      .run();

    const newUserId = await DB.prepare(
      `
    INSERT INTO user (email, name) VALUES (?,?);
    SELECT last_insert_rowid() INTO @lastID;
    INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ("google", ?, @lastID);
    SELECT @lastID;`,
    )
      .bind(email, name, provider_user_id)
      .run();

    const newUserId = await DB.prepare(
      `
    INSERT INTO user (email, name) VALUES (?,?);
    SELECT last_insert_rowid() INTO @lastID;
    INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ("google", ?, @lastID);
    SELECT @lastID;`,
    )
      .bind(email, name, provider_user_id)
      .run();

I removed the "BEGIN TRANSACTION" but still get this error locally:

A prepared SQL statement must contain only one statement.

A prepared SQL statement must contain only one statement.

A prepared SQL statement must contain only one statement.

A prepared SQL statement must contain only one statement.

MMartial ```javascript const newUserId = await DB.prepare( ` INSERT INTO us...

M

Max (@rozenmd)•5/25/24, 1:51 PM

remove the newline at the start of the string, it's a bug

MMartial ```javascript const newUserId = await DB.prepare( ` INSERT INTO us...

M

Max (@rozenmd)•5/25/24, 1:52 PM

also you can do

RETURNING *

RETURNING *

RETURNING *

RETURNING * to get the id from an insert/update

M

Martial•5/25/24, 2:35 PM

Sorry but it doesn't work, I am still having the same error.
Does D1 only accepts one statement at a time ?

M

Martial•5/25/24, 2:35 PM

const newUser = await DB.prepare(
  `INSERT INTO user (email, name) VALUES (?,?);
  RETURNING *;
  INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ('google', ?, @lastID)`
)
  .bind(email, name, provider_user_id)
  .run();

const newUser = await DB.prepare(
  `INSERT INTO user (email, name) VALUES (?,?);
  RETURNING *;
  INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ('google', ?, @lastID)`
)
  .bind(email, name, provider_user_id)
  .run();

const newUser = await DB.prepare(
  `INSERT INTO user (email, name) VALUES (?,?);
  RETURNING *;
  INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ('google', ?, @lastID)`
)
  .bind(email, name, provider_user_id)
  .run();

const newUser = await DB.prepare(
  `INSERT INTO user (email, name) VALUES (?,?);
  RETURNING *;
  INSERT INTO oauth_account (provider_id, provider_user_id, user_id) VALUES ('google', ?, @lastID)`
)
  .bind(email, name, provider_user_id)
  .run();

MMartial ```javascript const newUser = await DB.prepare( `INSERT INTO user (email, name...

M

Max (@rozenmd)•5/25/24, 3:07 PM

As in add

returning *

returning *

returning *

returning * to the end of an update/insert to be able to use it in a subsequent query, by itself it's not valid

M

Max (@rozenmd)•5/25/24, 3:08 PM

it supports multiple statements at a time

MMax (@rozenmd)it supports multiple statements at a time

M

Max (@rozenmd)•5/25/24, 3:19 PM

oh wait, db.prepare might not

MMax (@rozenmd)oh wait, db.prepare might not

I

Isaac McFadyen•5/25/24, 3:35 PM

Pretty sure it doesn't and it's batch-only for those.

I

Isaac McFadyen•5/25/24, 3:35 PM

At least I've only seen people using batches for multiple statements in the past.

L

Luca (steebchen)•5/25/24, 4:52 PM

Shouldn't I be able to press the restore button? As I have entered the bookmark ID

When I set a date above the button becomes available, but I already have the bookmark ID.. is this a bug or am I missing something?

L

Luca (steebchen)•5/25/24, 4:52 PM

W

Walshy•5/25/24, 4:53 PM

That seems like the current bookmark id - guessing it's disabled as it'd be a no-op

L

Luca (steebchen)•5/25/24, 4:55 PM

oh right, I just thought about that.. when is a new one coming? As I refreshed the page but the ID is still the same. What is the time increment on these?

W

Walshy•5/25/24, 4:55 PM

it changes with every db change

L

Luca (steebchen)•5/25/24, 4:56 PM

ah nice, I see. thx for the instant help :MeowHeartCloudflare:

MMartial Sorry but it doesn't work, I am still having the same error. Does D1 only accep...

L

Luca (steebchen)•5/25/24, 5:12 PM

Yes I had success using .batch @Max (@rozenmd) @Isaac McFadyen | YYZ01, EWR01 @Martial

M

Martial•5/25/24, 5:17 PM

Batching is only for independent queries. In my case query 2 depends on query one.

M

Martial•5/25/24, 5:18 PM

Curious to see your query

MMartial Batching is only for independent queries. In my case query 2 depends on query on...

L

Luca (steebchen)•5/25/24, 5:21 PM

hm you're right, doesn't work in that case. could you generate the ID in your app and then you could generate two independent queries from this?

M

Martial•5/25/24, 5:47 PM

Yes that approach would work but is moving the responsibility to generate id to the JS part of my app instead of the DB.

M

Murder Chicken•5/25/24, 8:49 PM

D1 Metrics Dashboard feature recommendation: Allow us to build a query in the Console whose results can be added as a new custom tab (along with Queries, Latency and Row Metrics) to the Metrics section of the D1 Dashboard.

Anyone using D1 with Drizzle + SvelteKit? I am wondering how to handle prepared statements since y

Similar Threads

Similar Threads

Similar Threads