Hey guys. Im using drizzle with D1. Left the project for around 6 months and when I got back to it,

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.

MMax (@rozenmd)not yet, we're working on it

P

Pavlo•5/27/24, 6:38 AM

Do you have an approximate date for when that feature will be available?

PPavlo Do you have an approximate date for when that feature will be available?

M

Max (@rozenmd)•5/27/24, 8:41 AM

no, but we'll let you know when it's ready to try out

G

Gyurmatag•5/27/24, 3:08 PM

Is anybody having issue with time travel? Our instance is displaying this error:

✘ [ERROR] A request to the Cloudflare API (/accounts/67d05e471e1ffe25b7ba89dc94d53c1e/d1/database/7daadf1d-3893-420b-a61f-e77cc2963a0e/time_travel/restore?bookmark=0000050f-00000004-00004d9e-8e5306bc9a4d7f84b25d329535288b3f) failed.

  The destination execution context for this RPC was canceled while the call was still running.
  [code: 7500]

✘ [ERROR] A request to the Cloudflare API (/accounts/67d05e471e1ffe25b7ba89dc94d53c1e/d1/database/7daadf1d-3893-420b-a61f-e77cc2963a0e/time_travel/restore?bookmark=0000050f-00000004-00004d9e-8e5306bc9a4d7f84b25d329535288b3f) failed.

  The destination execution context for this RPC was canceled while the call was still running.
  [code: 7500]

✘ [ERROR] A request to the Cloudflare API (/accounts/67d05e471e1ffe25b7ba89dc94d53c1e/d1/database/7daadf1d-3893-420b-a61f-e77cc2963a0e/time_travel/restore?bookmark=0000050f-00000004-00004d9e-8e5306bc9a4d7f84b25d329535288b3f) failed.

  The destination execution context for this RPC was canceled while the call was still running.
  [code: 7500]

✘ [ERROR] A request to the Cloudflare API (/accounts/67d05e471e1ffe25b7ba89dc94d53c1e/d1/database/7daadf1d-3893-420b-a61f-e77cc2963a0e/time_travel/restore?bookmark=0000050f-00000004-00004d9e-8e5306bc9a4d7f84b25d329535288b3f) failed.

  The destination execution context for this RPC was canceled while the call was still running.
  [code: 7500]

GGyurmatag Is anybody having issue with time travel? Our instance is displaying this error:...

M

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

This looks like it could be related to D1's move to DO RPC, will investigate

GGyurmatag Is anybody having issue with time travel? Our instance is displaying this error:...

M

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

dumb question, does it actually complete the time travel despite the error?

MMax (@rozenmd)dumb question, does it actually complete the time travel despite the error?

G

Gyurmatag•5/27/24, 3:32 PM

Actually yes. Just noticed this! It is working, but the error is confusing...