d1 info is sorely unoptimised, i think it does something silly like scan the entire list of DBs inst

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)OP•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)OP•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)OP•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)OP•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)OP•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)OP•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)OP•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)OP•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...

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

M

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

oh phewww

Yeah I can see the error - it's an issue parsing the output of the response, rather than actually restoring

G

gregor•5/27/24, 3:34 PM

Heya, I want to give a data-curious team mate access to our D1. Is there an option to generate a read-only connection string for his SQL Viewer tool?

Ggregor Heya, I want to give a data-curious team mate access to our D1. Is there an opti...

M

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

nothing like that yet, but you could run

wrangler d1 export

wrangler d1 export

wrangler d1 export

wrangler d1 export on a newer version of wrangler and send them the sql?

MMax (@rozenmd)nothing like that yet, but you could run `wrangler d1 export` on a newer version...

G

gregor•5/27/24, 3:38 PM

Thanks, I might do that. I wanted him to be able to do it on new data, even if I'm not around. So I guess I'll just teach him how to wrangle

B

benjamin•5/27/24, 6:43 PM

I've been getting a lot a errors using wrangler and D1 the past couple days. I've signed out and in multiple times with the CLI. Occasionally commands work but mostly I'm getting this:

wrangler d1 list

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

  Authentication error [code: 10000]

wrangler d1 list

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

  Authentication error [code: 10000]

wrangler d1 list

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

  Authentication error [code: 10000]

wrangler d1 list

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

  Authentication error [code: 10000]

B

benjamin•5/27/24, 6:44 PM

with wrangler 3.57.1. Any ideas or suggestions?

Bbenjamin I've been getting a lot a errors using wrangler and D1 the past couple days. I'v...

M

Max (@rozenmd)OP•5/27/24, 7:01 PM

I ran into this today,

wrangler logout

wrangler logout

wrangler logout

wrangler logout and

wrangler login

wrangler login

wrangler login

wrangler login worked

Bbenjamin I've been getting a lot a errors using wrangler and D1 the past couple days. I'v...

M

Max (@rozenmd)OP•5/27/24, 7:03 PM

do any commands work after you log back in? or always failing?

MMax (@rozenmd)do any commands work after you log back in? or always failing?

B

benjamin•5/27/24, 8:15 PM

Thanks, today always failing. Yesterday it would work and then immediately fail. Now failing after login.

Successfully logged in.

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

  Authentication error [code: 10000]


Getting User settings...
👋 You are logged in with an OAuth Token, associated with the email xxxxxxx!
┌──────────────────────────────────────────┬──────────────────────────────────┐
│ Account Name                             │ Account ID                       │
├──────────────────────────────────────────┼──────────────────────────────────┤
│ xxxxxxx's Account │ xxxxxxx │
└──────────────────────────────────────────┴──────────────────────────────────┘
🔓 Token Permissions: If scopes are missing, you may need to logout and re-login.
Scope (Access)
- account (read)
- user (read)
- workers (write)
- workers_kv (write)
- workers_routes (write)
- workers_scripts (write)
- workers_tail (read)
- d1 (write)
- pages (write)
- zone (read)
- ssl_certs (write)
- constellation (write)
- ai (write)
- queues (write)
- offline_access

Successfully logged in.

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

  Authentication error [code: 10000]


Getting User settings...
👋 You are logged in with an OAuth Token, associated with the email xxxxxxx!
┌──────────────────────────────────────────┬──────────────────────────────────┐
│ Account Name                             │ Account ID                       │
├──────────────────────────────────────────┼──────────────────────────────────┤
│ xxxxxxx's Account │ xxxxxxx │
└──────────────────────────────────────────┴──────────────────────────────────┘
🔓 Token Permissions: If scopes are missing, you may need to logout and re-login.
Scope (Access)
- account (read)
- user (read)
- workers (write)
- workers_kv (write)
- workers_routes (write)
- workers_scripts (write)
- workers_tail (read)
- d1 (write)
- pages (write)
- zone (read)
- ssl_certs (write)
- constellation (write)
- ai (write)
- queues (write)
- offline_access

Successfully logged in.

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

  Authentication error [code: 10000]


Getting User settings...
👋 You are logged in with an OAuth Token, associated with the email xxxxxxx!
┌──────────────────────────────────────────┬──────────────────────────────────┐
│ Account Name                             │ Account ID                       │
├──────────────────────────────────────────┼──────────────────────────────────┤
│ xxxxxxx's Account │ xxxxxxx │
└──────────────────────────────────────────┴──────────────────────────────────┘
🔓 Token Permissions: If scopes are missing, you may need to logout and re-login.
Scope (Access)
- account (read)
- user (read)
- workers (write)
- workers_kv (write)
- workers_routes (write)
- workers_scripts (write)
- workers_tail (read)
- d1 (write)
- pages (write)
- zone (read)
- ssl_certs (write)
- constellation (write)
- ai (write)
- queues (write)
- offline_access

Successfully logged in.

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

  Authentication error [code: 10000]


Getting User settings...
👋 You are logged in with an OAuth Token, associated with the email xxxxxxx!
┌──────────────────────────────────────────┬──────────────────────────────────┐
│ Account Name                             │ Account ID                       │
├──────────────────────────────────────────┼──────────────────────────────────┤
│ xxxxxxx's Account │ xxxxxxx │
└──────────────────────────────────────────┴──────────────────────────────────┘
🔓 Token Permissions: If scopes are missing, you may need to logout and re-login.
Scope (Access)
- account (read)
- user (read)
- workers (write)
- workers_kv (write)
- workers_routes (write)
- workers_scripts (write)
- workers_tail (read)
- d1 (write)
- pages (write)
- zone (read)
- ssl_certs (write)
- constellation (write)
- ai (write)
- queues (write)
- offline_access

B

benjamin•5/27/24, 9:08 PM

I created a new project and everything seems to be working fine. My earlier one had an older wranger.toml file and I've updated it. Seems to be working now.

K

kian•5/27/24, 9:32 PM

Did you have an

account_id

account_id

account_id

account_id in your Wrangler.toml?

B

benjamin•5/27/24, 10:23 PM

Yes, was working off an open-source starter that had it there and I updated it with mine.

F

FroggyKnight•5/28/24, 3:18 AM

What's the disadvantage of using one db in D1 over one db per user? Is it just the 10GB limit?

F

FroggyKnight•5/28/24, 3:18 AM

Also what is the cost of a db after the 10 free db's?

M

Mrinank•5/28/24, 5:23 AM

Why d1 reads/writes are cheaper than kv's?

MMrinank Why d1 reads/writes are cheaper than kv's?

M

Max (@rozenmd)OP•5/28/24, 6:01 AM

D1's read/writes only need to be replicated to a few dbs, and scale is limited (currently) to a single DO instance
while KV can handle millions of RPS and gets replicated globally

FFroggyKnight What's the disadvantage of using one db in D1 over one db per user? Is it just t...

M

Max (@rozenmd)OP•5/28/24, 6:01 AM

Managing the schema becomes annoying

FFroggyKnight Also what is the cost of a db after the 10 free db's?

M

Max (@rozenmd)OP•5/28/24, 6:02 AM

we don't charge per DB - only read/writes and storage https://developers.cloudflare.com/d1/platform/pricing/

d1 info is sorely unoptimised, i think it does something silly like scan the entire list of DBs inst

Similar Threads

Similar Threads

Similar Threads