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

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

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;

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

This happens when running execute with a migration 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;"

)

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

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

Try making

begin

begin

fully caps, i.e

BEGIN

BEGIN

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

oh god, that solved it

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

I had to forgot begin

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

Thank you

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

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

BEGIN

BEGIN

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

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

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

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

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

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

I have done this without issue

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

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

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

That’s a D1 internal table

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.

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

Uh… I guess not?

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

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.

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

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

d1_

d1_

btw

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

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

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

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

file in

.wrangler

.wrangler

, but that seems kind of jank

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?

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

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

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;

- is it expected that running the migration command again returns

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

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?

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?

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

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

not yet, we're working on it

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?

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

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?

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.

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

does it consume reads

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

I'd be surprised if it didn't

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

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

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

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();
}

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.

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);

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

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

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

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);

should work though?

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

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

@Max (@rozenmd)

Max (@rozenmd)•5/23/24, 2:29 PM

we do not yet have a means of pulling your database out without reading it

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

landon•5/23/24, 3:52 PM

we have been seeing similar errors intermittently d1-database

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

Similar Threads

Similar Threads

Similar Threads