It's not billed on queries but rows. You are inserting 100 rows (writes) so you'll be billed for 100

I

Isaac McFadyenOP•5/17/24, 1:50 PM

Ah, think I misunderstood. Are you referring to D1 analytics or billing?

IIsaac McFadyen It's not billed on queries but rows. You are inserting 100 rows (writes) so you'...

R

roftt•5/17/24, 1:50 PM

Yes, I'm aware of pricing by rows, thank you. I was just checking if it's a bug since I thought the batch operation should be executed in one query.

I

Isaac McFadyenOP•5/17/24, 1:51 PM

As far as I know a D1 batch is just a bunch of statements that are wrapped in a transaction for you by the D1 engine internally.

I

Isaac McFadyenOP•5/17/24, 1:51 PM

So it'll still be more than 1 query if you have more than 1 item in the batch.

M

Max (@rozenmd)•5/17/24, 2:41 PM

batch just saves you network trips from the Worker to D1

K

kindaraffy•5/19/24, 2:30 PM

Is anyone else experience degraded D1 performance when querying data? I have a 640kB database and it will sometimes (around 1/8 times) take a really long time (~ 5-10 secs) to execute the query.

I was blaming my SQL queries at the start, but I just saw this happen on a

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table>

SELECT * FROM <table> query against a table with 4 rows from the D1 dashboard console.

I've noticed this occurring from the start of this day. I've attached a screenshot of my network health from

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com

speed.cloudflare.com. I'm in Melb, Aus.

K

kindaraffy•5/19/24, 2:43 PM

K

kindaraffy•5/19/24, 2:44 PM

D1 returns a result at around

0:28

0:28

0:28

0:28

Kkindaraffy Is anyone else experience degraded D1 performance when querying data? I have a 6...

M

Max (@rozenmd)•5/19/24, 7:05 PM

whats your databaseId?

MMax (@rozenmd)whats your databaseId?

K

kindaraffy•5/19/24, 9:04 PM

Hi Max, thank you for looking into this :). The database id is

bfd358ee-8478-4a08-a37d-75dcabbf231b

bfd358ee-8478-4a08-a37d-75dcabbf231b

bfd358ee-8478-4a08-a37d-75dcabbf231b

bfd358ee-8478-4a08-a37d-75dcabbf231b

N

notjoemartinez•5/20/24, 5:48 AM

Is there a way run a worker locally while it talks to a remote d1-database? The get-started tutorial only shows you how to develop local db then creates a separate db for remote usage. I'd like to be able to query the remote db from a local dev environment.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

H

Hello, I’m Allie!•5/20/24, 5:56 AM

Not at the moment. Either you run the Worker locally, or you run it remotely, and get access to a real D1 instance

N

notjoemartinez•5/20/24, 6:00 AM

~~darn back to testing in prod~~ I guess I could just run a script to dump remote into a local db when testing. I'm not sure how that's going to scale when I enable FTS and virtual tables though. Would be intrested to see how other people handle this.

Nnotjoemartinez Is there a way run a worker locally while it talks to a remote d1-database? The ...

J

James Flores•5/20/24, 7:26 AM

Use the API instead and you should be able to

JJames Flores Use the API instead and you should be able to

H

Hello, I’m Allie!•5/20/24, 7:30 AM

This can work, though note first that this requires an API wrapper around the normal D1 API, it doesn't support the same operations as the normal D1 API, and will in most cases perform worse than a real bound D1 database

HHello, I’m Allie!This can work, though note first that this requires an API wrapper around the no...

J

James Flores•5/20/24, 7:31 AM

Thanks for that info. I couldn’t find any JS examples for React and D1 so I ended up using the API.

JJames Flores Thanks for that info. I couldn’t find any JS examples for React and D1 so I ende...

H

Hello, I’m Allie!•5/20/24, 7:46 AM

I generally wouldn’t recommend using D1, or any Cloudflare API directly from the frontend

H

Hello, I’m Allie!•5/20/24, 7:47 AM

Other than direct upload stuff for R2/Stream/Calls etc

HHello, I’m Allie!I generally wouldn’t recommend using D1, or any Cloudflare API directly from the...

J

James Flores•5/20/24, 7:47 AM

Oh sorry it’s in my backend

J

James Flores•5/20/24, 7:47 AM

I got that wrong.

HHello, I’m Allie!This can work, though note first that this requires an API wrapper around the no...

N

notjoemartinez•5/20/24, 9:26 AM

Well that's basically what I'm trying to build to begin with so I have a bit of a chicken and the egg problem here. I'm considering executing wrangler SQL commands through a child process if workers will allow it. I'll look into the D1 API before I open that can of worms.

E

Enduriel•5/20/24, 9:27 AM

What guarantees does D1 provide for its randomblob generation? Can't find any reference for that but it'd be interesting to know how much we can use it

Nnotjoemartinez Well that's basically what I'm trying to build to begin with so I have a bit of ...

H

Hello, I’m Allie!•5/20/24, 9:41 AM

If possible, I would probably deploy a proxy Worker then make calls through it instead

H

Hello, I’m Allie!•5/20/24, 9:41 AM

That would also be more performant

O

Owl Reddy•5/20/24, 12:37 PM

why doesn't Hyperdrive support D1? I'm confused.

K

kian•5/20/24, 12:40 PM

Hyperdrive is for connecting to your own, traditional databases hosted elsewhere

K

kian•5/20/24, 12:41 PM

There's no need for it when connecting to something already on Cloudflare

Kkian There's no need for it when connecting to something already on Cloudflare

O

Owl Reddy•5/20/24, 2:18 PM

But it is very useful for decreasing load on the db and to serve the requests faster. If I'm getting a bunch of requests for a few records from 1000s of records it'll save me massive amount of time and save the db from load if cached. I'm considering self hosting postgres but I'm a bit afraid of shooting myself in the foot

C

cosbgn•5/20/24, 2:48 PM

With:

npx wrangler d1 export

npx wrangler d1 export

npx wrangler d1 export

npx wrangler d1 export can I blacklist a specific table (migrations)?

C

cosbgn•5/20/24, 2:49 PM

or can I alternatively list multiple tables in

--table

--table

--table

--table ? From the docs it seem that you can either export all tables or only one specific. I would like all but migrations.

C

cosbgn•5/20/24, 2:50 PM

The idea is to make a copy of my production db locally, this works with

export && execute

export && execute

export && execute

export && execute but migrations mess everything up

✘ [ERROR] UNIQUE constraint failed: d1_migrations.id

✘ [ERROR] UNIQUE constraint failed: d1_migrations.id

✘ [ERROR] UNIQUE constraint failed: d1_migrations.id

✘ [ERROR] UNIQUE constraint failed: d1_migrations.id

C

cosbgn•5/20/24, 2:52 PM

This is what I do:

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

"db:export": "npx wrangler d1 export db_name --remote --no-schema --output export.sql && npx wrangler d1 execute db_name --file=export.sql"

V

Vikash•5/21/24, 3:45 AM

Random error in D1, is there anything I can do to fix this?

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

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

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

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

Code

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

export const getUser = async (
  env: Bindings,
  userId: number
): Promise<Resource | null> => {
  const sqlQuery = env.DB.prepare(
    `SELECT * FROM Users WHERE user_id = ?`
  ).bind(userId);
  return await sqlQuery.first();
};

V

Vikash•5/21/24, 3:46 AM

OOwl Reddy But it is very useful for decreasing load on the db and to serve the requests fa...

M

Max (@rozenmd)•5/21/24, 10:47 AM

https://developers.cloudflare.com/workers/examples/cache-api/ might help

Cloudflare Docs

Using the Cache API · Cloudflare Workers docs

Documentation for Cloudflare Workers, a serverless execution environment that allows you to create entirely new applications or augment existing ones …

R

roftt•5/21/24, 12:18 PM

Any update on the bug with importing a database with large rows? [wrangler 3.53.1] https://github.com/cloudflare/workers-sdk/issues/4510

VVikash Random error in D1, is there anything I can do to fix this? ``` D1_ERROR: D1 s...

M

Matt Silverlock•5/21/24, 12:19 PM

This typically means your query is running for too long. Do you have an index on

user_id

user_id

user_id

user_id ?

MMatt Silverlock This typically means your query is running for too long. Do you have an index on...

V

Vikash•5/21/24, 12:29 PM

That’s primary key

VVikash That’s primary key

M

Matt Silverlock•5/21/24, 1:28 PM

Can you still confirm it’s using the index for me? I want to see the output: https://developers.cloudflare.com/d1/build-with-d1/use-indexes/#test-an-index

Otherwise please file a support ticket.

Cloudflare Docs

Use indexes · Cloudflare D1 docs

Indexes enable D1 to improve query performance over the indexed columns for common (popular) queries by reducing the amount of data (number of rows) …

P

Pol•5/21/24, 6:46 PM

Hey, I'm trying to create a trigger on my D1 Database. Applying the query directly with sqlite works, but when done with

execute

execute

execute

execute or with migrations, it outputs this:

✘ [ERROR] incomplete input

✘ [ERROR] incomplete input

✘ [ERROR] incomplete input

✘ [ERROR] incomplete input

This is the trigger:

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

CREATE TRIGGER delete_item_tags
    AFTER DELETE ON list_item
begin
    DELETE FROM list_item_tags
    WHERE item_id = OLD.id;
END;

P

Pol•5/21/24, 6:47 PM

This happens when running execute with a migration file (

--file

--file

--file

--file) and as a command (

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

--command "CREATE TRIGGER delete_item_tags after delete on list_item begin DELETE FROM list_item_tags WHERE item_id = OLD.id; END;"

)

PPol Hey, I'm trying to create a trigger on my D1 Database. Applying the query direct...

K

kian•5/21/24, 6:53 PM

Try making

begin

begin

begin

begin fully caps, i.e

BEGIN

BEGIN

BEGIN

BEGIN

P

Pol•5/21/24, 7:03 PM

oh god, that solved it

P

Pol•5/21/24, 7:03 PM

I had to forgot begin

P

Pol•5/21/24, 7:04 PM

Thank you

K

kian•5/21/24, 7:08 PM

Wrangler itself splits the input SQL into statements, and it checks specifically for

BEGIN

BEGIN

BEGIN

BEGIN (case-sensitive) to see if it's a compound statement.

Might be worth making it case-insensitive @Max (@rozenmd)?

I

Isaac McFadyenOP•5/21/24, 7:40 PM

^ yeah the sql specification is case-insensitive so would make sense

P

Pol•5/21/24, 8:45 PM

Just created a PR for this https://github.com/cloudflare/workers-sdk/pull/5889

GitHub

[wrangler] Made D1 BEGIN and CASE regex case insensitive by polvall...

What this PR solves / how to test
Follows sqlite spec for case insensitive BEGIN and CASE (discussed in Discord)
Solves not using these operators not in caps leading into a wrangler exception.
✘ [E...

Hhill Does anybody have experience with binding multiple workers to a D1 database? Cur...

I

IEatBeans•5/22/24, 12:25 AM

I have done this without issue

It's not billed on queries but rows. You are inserting 100 rows (writes) so you'll be billed for 100

Similar Threads

Similar Threads

Similar Threads