Can you still confirm it’s using the index for me? I want to see the output: https://developers.clou

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

, 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 SilverlockOP•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

, 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

B

bun•5/22/24, 10:19 PM

is there no way to download the database anymore?

Bbun is there no way to download the database anymore?

C

Cyb3r-Jak3•5/22/24, 10:29 PM

https://developers.cloudflare.com/workers/wrangler/commands/#export ?

Cloudflare Docs

Commands - Wrangler · Cloudflare Workers docs

Create, develop, and deploy your Cloudflare Workers with Wrangler commands.

B

bun•5/22/24, 10:29 PM

does it consume reads

C

Cyb3r-Jak3•5/22/24, 10:30 PM

I'd be surprised if it didn't

B

bun•5/22/24, 10:33 PM

if the read count on dash.cloudflare.com updates immediatelly then it doesnt

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

V

Vikash•5/23/24, 4:00 AM

The error happened again today on same table, while it's a primary key index.

Here is the

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN

result -

Bbun if the read count on dash.cloudflare.com updates immediatelly then it doesnt

M

Max (@rozenmd)•5/23/24, 5:46 AM

if our metrics could update immediately on dash.cloudflare.com that'd mean we solved the speed of light problem

N

notjoemartinez•5/23/24, 6:17 AM

I'm using D1 to keep track of objects in R2 and I have a function that syncs all R2 Etags and keys to a table from a cloudflare worker. I'm running into the error

Error: Too many API requests by single worker invocation

Error: Too many API requests by single worker invocation

I know workers has a 1000 request invocation limit but I was wondering if there was a way insert large amounts of rows with a single request like pythons

executemany

executemany

command. I'm currently inserting a single row per request like this

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    await db.prepare(
        `INSERT OR IGNORE INTO r2_objects_metadata 
        (object_key, eTag) VALUES (?, ?)`
    ).bind(Key, ETag).run();
}

N

notjoemartinez•5/23/24, 6:24 AM

I see there is a batch() command but it looks like it's for running multiple db prepare statements not inserting multiple rows.

N

notjoemartinez•5/23/24, 6:47 AM

nvm I figured it out using

execute()

execute()

on a giant sql insert statement string

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

let masiveInsert = "";

for (let i = 0; i < newBucketObjects.length; i++) {
    const Key = newBucketObjects[i].Key;
    const ETag = newBucketObjects[i].ETag;
    let insert = `INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES ('${Key}', '${ETag}');`;
    masiveInsert += '\n';
    masiveInsert += insert;
}
await db.exec(masiveInsert);

Nnotjoemartinez nvm I figured it out using `execute()` on a giant sql insert statement string ``...

M

Max (@rozenmd)•5/23/24, 6:56 AM

on a big enough string sqlite will reject the query, also massive sql injection there

M

Max (@rozenmd)•5/23/24, 6:59 AM

something like

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

const prepared = db.prepare("INSERT OR IGNORE INTO r2_objects_metadata (object_key, eTag) VALUES (?1, ?2)");

const queriesToRun = newBucketObjects.map(bucketObject => prepared.bind(bucketObject.Key, bucketObject.ETag));

await db.batch(queriesToRun);

should work though?

B

bun•5/23/24, 2:28 PM

Does it consume reads or not? If yes, is there a way to copy/extract db in a way that it wont consume reads for everything in my database

B

bun•5/23/24, 2:29 PM

@Max (@rozenmd)

Can you still confirm it’s using the index for me? I want to see the output: https://developers.clou

Similar Threads

Similar Threads

Similar Threads