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

H

Hello, I’m Allie!OP•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!OP•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!OP•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

?

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

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

I

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

I have one bound to 4 pages projects and one worker

MMatt Silverlock Can you still confirm it’s using the index for me? I want to see the output: htt...

V

Vikash•5/22/24, 4:57 AM

This one had index, but there was another inline query in same function without an index. I just created one...let's see how it improve it.

By the way, it will be really good to show slow/not index queries on dashboard with suggestion index to quickly create to improve performance. Something like this in Atlas dashboard.