does d1 support delete and return record? - Cloudflare Developers

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

Paul

PPaul does d1 support delete and return record?

GuillaumeLakano

G

GuillaumeLakano•11/7/23, 4:09 PM

Yes: https://www.sqlite.org/lang_returning.html
Be careful, this cost 2 reads + 2 writes

Paul

P

PaulOP•11/7/23, 4:10 PM

it's also kinda working half the time

Paul

P

PaulOP•11/7/23, 4:11 PM

DELETE FROM verification_token where token = ?1 RETURNING *DELETE FROM verification_token where token = ?1 RETURNING *

Paul

P

PaulOP•11/7/23, 4:11 PM

does that look right?

Paul

P

PaulOP•11/7/23, 4:11 PM

token is a uuid v4 string

Paul

P

PaulOP•11/7/23, 4:12 PM

i'm trying to figure out how it could be deleting the token, but at the same time not returning any results

Paul

P

PaulOP•11/7/23, 4:12 PM

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];

GuillaumeLakano

G

GuillaumeLakano•11/7/23, 4:13 PM

This seems to be the good syntax yes

Paul

P

PaulOP•11/7/23, 4:14 PM

Weird how result.results.length <= 0result.results.length <= 0 is TRUE on production

Paul

P

PaulOP•11/7/23, 4:14 PM

even tho it deletes the token

kian

K

kian•11/7/23, 4:14 PM

Because run()run() does not return resultsresults

kian

K

kian•11/7/23, 4:14 PM

https://developers.cloudflare.com/d1/platform/client-api/#await-stmtrun

Cretezy

C

Cretezy•11/7/23, 4:15 PM

Would it be possible to add my and @nora's exporters to https://developers.cloudflare.com/d1/platform/community-projects/ ?

https://github.com/Cretezy/cloudflare-d1-backup
https://github.com/nora-soderlund/cloudflare-d1-backups

kian

Kkian Because `run()` does not return `results`

Paul

P

PaulOP•11/7/23, 4:16 PM

How are you getting your results from queries?

Paul

PPaul How are you getting your results from queries?

kian

K

kian•11/7/23, 4:17 PM

Use another method, like all()all() or first()first().

Paul

P

PaulOP•11/7/23, 4:18 PM

wow, thats insane. so my intellisense is just wrong

Paul

P

PaulOP•11/7/23, 4:18 PM

or was this a recent change?

Paul

P

PaulOP•11/7/23, 4:19 PM

and how the heck are all my other queries working then

Paul

P

PaulOP•11/7/23, 4:19 PM

haha

Paul

P

PaulOP•11/7/23, 4:19 PM

i use run() everywhere

Paul

P

PaulOP•11/7/23, 4:19 PM

maybe because they are simple SELECT statements?

kian

K

kian•11/7/23, 4:32 PM

run()run() should throw an error if it returns results.

kian

K

kian•11/7/23, 4:33 PM

Well, it did in Wrangler 3.1.1. Seems like it doesn't in 3.15.0.

kian

K

kian•11/7/23, 4:34 PM

@rozenmd is the documentation wrong about run()run()? It currently does exactly the same as all()all().

Paul

P

PaulOP•11/7/23, 4:36 PM

yep that exactly. It looks like my problem is unrelated to D1. It seems the worker function is running twice, so the first one is eating the verification_token, and the second run fails.

Max (@rozenmd)

M

Max (@rozenmd)•11/7/23, 4:56 PM

i don't recall us changing run()run()'s behaviour tbh

johnjoz

J

johnjoz•11/7/23, 6:24 PM

D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with wrangler fails with mysterious node errors, and I’ve followed the sundry blog examples. I can rclone to r2 with impunity. Is there any facility to load a d1 sqlite db from r2?

Max (@rozenmd)

MMax (@rozenmd)i don't recall us changing `run()`'s behaviour tbh

kian

K

kian•11/7/23, 6:28 PM

I've tried it in local & remote development and run()run() returns resultsresults.

kian

K

kian•11/7/23, 6:28 PM

In Wrangler 3.1.1, you'd get an error thrown saying "Execute returned results" or something similar

kian

K

kian•11/7/23, 6:29 PM

kian

K

kian•11/7/23, 6:29 PM

kian

K

kian•11/7/23, 6:33 PM

Miniflare used to check on it's own and throw (hence why I had the issue on an old Wrangler version) but now the API seems to send results rather than an error.

johnjoz

Jjohnjoz D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with w...

Max (@rozenmd)

M

Max (@rozenmd)•11/7/23, 8:16 PM

you might be overloading the db - what exactly are the errors?

johnjoz

J

johnjoz•11/7/23, 8:57 PM

% wrangler d1 execute ipbistrosql --file ./searchtree.sql
FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal

johnjoz

J

johnjoz•11/7/23, 8:57 PM

Plus many more nondescript lines from v8 and node

johnjoz

J

johnjoz•11/7/23, 8:59 PM

If i copy the sql file and truncate at a hundred lines that works as in blogs. But of course is useless. The sql file (generated by dump from sqlite3 file tested locally) is about 21 million lines. The db is about 1.5 gb.

johnjoz

J

johnjoz•11/7/23, 9:06 PM

Perhaps I should rclone the sql file to r2 (i can do that no problem) then write a worker which loads from r2 into d1, but it seems i hit some unintended unchecked limit inside a node dependency or v8 itself (on which i was long ago a contributor via a qualcomm google joint effort)

johnjoz

Jjohnjoz % wrangler d1 execute ipbistrosql --file ./searchtree.sql FATAL ERROR: v8::ToLoc...

Matt Silverlock

M

Matt Silverlock•11/7/23, 9:12 PM

What is your account ID & database ID?

johnjoz

J

johnjoz•11/7/23, 9:23 PM

johnjoz

J

johnjoz•11/7/23, 9:24 PM

1b4eb314bfee79ccebd94f33e9bde8ee

johnjoz

J

johnjoz•11/7/23, 9:24 PM

d6ac38a9-20be-4fb4-9839-02a379d944da

johnjoz

J

johnjoz•11/7/23, 9:25 PM

That says uuid on the wrangler output for d1 list

johnjoz

J

johnjoz•11/7/23, 9:29 PM

I did upload the sqlite3 file to r2 but not the sql dump script

johnjoz

Jjohnjoz Click to see attachment

Matt Silverlock

M

Matt Silverlock•11/7/23, 9:38 PM

This is wrangler failing to parse 1.5GB locally. As an interim fix, you'll need to break up the upload into smaller chunks.

johnjoz

J

johnjoz•11/7/23, 9:40 PM

Thank you

Victor

V

Victor•11/8/23, 12:25 AM

Does the sqlite engine inside D1 not support unhex()unhex()?

Victor

V

Victor•11/8/23, 12:26 AM

Since it's listed here as a core function https://www.sqlite.org/lang_corefunc.html#unhex

Victor

V

Victor•11/8/23, 12:28 AM

Which I find weird because hex()hex() works just fine

kian

K

kian•11/8/23, 12:32 AM

do you get not authorized or an error about it not existing

Victor

V

Victor•11/8/23, 12:32 AM

If I have a uuid stored as binary BLOBBLOB (initially generated with (randomblob(16))(randomblob(16))), this works:

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

(lower(format('%s-%s-%s-%s-%s', substr(hex(t_id),1,8), substr(hex(t_id),9,4), substr(hex(t_id),13,4), substr(hex(t_id),17,4), substr(hex(t_id),21))))

to get the normal looking utf8 uuid

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];