But it is very useful for decreasing load on the db and to serve the requests faster. If I'm getting

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 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.

PPorush Manjhi I was able to migrate by running the migration commands on the D1 console. Howev...

H

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

That’s a D1 internal table

H

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

Should be fine to just leave it be

PPorush Manjhi Was it the right thing to create it manually? It's showing +1 table count now.

H

Hello, I’m Allie!•5/22/24, 8:27 AM

Uh… I guess not?

H

Hello, I’m Allie!•5/22/24, 8:27 AM

I don’t remember what it is used for, but it shouldn’t be an issue to just not insert it. I misread your earlier message

Original message was deleted

M

Matt Silverlock•5/22/24, 9:56 AM

This isn’t true at all. It’s a table to back the DO storage API if/when we move it to be backed by D1’s SQLite engine.

PPorush Manjhi Was it the right thing to create it manually? It's showing +1 table count now.

M

Max (@rozenmd)•5/22/24, 12:28 PM

there's a bug in the table count for tables starting with

d1_

d1_

d1_

d1_ btw

M

Matt Silverlock•5/22/24, 3:26 PM

Drizzle Studio now supports D1: https://x.com/DrizzleORM/status/1793293520934949101

Drizzle ORM (@DrizzleORM) on X

We've landed support for @CloudflareDev D1 HTTP API in Drizzle Kit, Drizzle Studio and Studio Chrome Extension

extension - https://t.co/FjgYV4LVjx
release - https://t.co/Dd5vAeSICF

Twitter

•

5/22/24, 2:51 PM

MMatt Silverlock Drizzle Studio now supports D1: https://x.com/DrizzleORM/status/1793293520934949...

H

Hello, I’m Allie!•5/22/24, 3:36 PM

Any chance there will be togglable support for local/remote DBs?

H

Hello, I’m Allie!•5/22/24, 3:37 PM

Atm you can have local support by having a custom config that hard-points to the

.sqlite

.sqlite

.sqlite

.sqlite file in

.wrangler

.wrangler

.wrangler

.wrangler, but that seems kind of jank

H

Hello, I’m Allie!•5/22/24, 3:37 PM

Also that you have to switch out the configs manually

HHello, I’m Allie!Any chance there will be togglable support for local/remote DBs?

M

Matt Silverlock•5/22/24, 3:37 PM

Best to ask the Drizzle folks / file an issue to ask!

A

Alex•5/22/24, 3:52 PM

I've got a quick question - if I create a migration, which say creates a table

User

User

User

User, then run

DROP TABLE User;

DROP TABLE User;

DROP TABLE User;

DROP TABLE User; - is it expected that running the migration command again returns

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!

:white_check_mark: No migrations to apply!?

AAlex I've got a quick question - if I create a migration, which say creates a table `...

A

Alex•5/22/24, 3:54 PM

This came about because I use the wrong value for a field

id

id

, but running the migration again gave me the no migrations to apply message. So I figured dropping the table and running the migration again would work. Am I misunderstanding how you update tables?

K

kingmesal•5/22/24, 3:55 PM

In the last day I've experienced at least 2 different occasions of

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

Anything going on?

P

Pavlo•5/22/24, 6:38 PM

Hi guys, I have just read an article about the Global D1 database with read replicas, do you know if this feature has been released? https://blog.cloudflare.com/building-d1-a-global-database/?utm_campaign=cf_blog&utm_content=20240401&utm_medium=organic_social&utm_source=facebook,linkedin,twitter

The Cloudflare Blog

Building D1: a Global Database

D1, Cloudflare’s SQL database, is now generally available. With new support for 10GB databases, data export, and enhanced query debugging, we empower developers to build production-ready applications with D1 to meet all their relational SQL needs

PPavlo Hi guys, I have just read an article about the Global D1 database with read repl...

M

Max (@rozenmd)•5/22/24, 6:40 PM

not yet, we're working on it

A

antonio eldelpan•5/22/24, 9:30 PM

Hi! I've some questings about D1. I'm curious about how does it handle concurrent writes/read. I haven't seen anything on the docs. Does write lock the database, table, or just the row?
Also I'm curious about how many write operations per second can it handle on avg?

A

antonio eldelpan•5/22/24, 9:33 PM

I know intensive write operations are not the best fit for D1. But I want to know how well could it handle a peak of traffic when a lot of people start doing actions on the service that result on write operations being performed, like user registration, adding comments, or editing their settings