Are those statements supported though? - Cloudflare Developers

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

conqr ᯅ

C

conqr ᯅOP•9/24/23, 11:39 AM

I've resorted to using a combination

INSERT INTO ... SELECT

INSERT INTO ... SELECT

with a bunch of

JOIN

JOIN

s

conqr ᯅ

Cconqr ᯅ Are those statements supported though?

Matt Silverlock

M

Matt Silverlock•9/24/23, 11:40 AM

Yes. INSERT / UPSERT … ON CONFLICT work.

conqr ᯅ

C

conqr ᯅOP•9/24/23, 1:40 PM

Is the LIMIT modifier not allowed on DELETE commands?

DELETE FROM offers WHERE price=5 LIMIT 1

near "LIMIT": syntax error

kian

K

kian•9/24/23, 1:43 PM

correct

conqr ᯅ

C

conqr ᯅOP•9/24/23, 1:43 PM

Seems to not work on UPDATE commands too

UPDATE users SET balance = balance + 10 WHERE id = 'test' LIMIT 1

near "LIMIT": syntax error

kian

K

kian•9/24/23, 1:43 PM

It's an optional feature in SQLite and isn't enabled in D1.

conqr ᯅ

C

conqr ᯅOP•9/24/23, 1:44 PM

It works for other commands though

kian

K

kian•9/24/23, 1:44 PM

The feature is about UPDATE and DELETE statements.

kian

K

kian•9/24/23, 1:44 PM

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

SQLITE_ENABLE_UPDATE_DELETE_LIMIT

This option enables an optional ORDER BY and LIMIT clause on UPDATE and DELETE statements.

https://www.sqlite.org/compile.html

conqr ᯅ

C

conqr ᯅOP•9/24/23, 1:46 PM

captain

C

captain•9/24/23, 3:33 PM

I'm noticing in the dasboard for d1 its only text, integer, real and blob when setting up a table. Does tha mean if I run something like:

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

CREATE TABLE IF NOT EXISTS messages (
  title text NOT NULL,
  body text NOT NULL,
  amount INTEGER NOT NULL,
  color VARCHAR(10) NOT NULL,
);

That the VARCHAR(10) is really just text and I don't need to state VARCHAR(10) because there will be no performance or size settings by setting that limitation behind the scenes?

Original message was deleted

captain

C

captain•9/24/23, 3:42 PM

got it, so so I don't need to waste time worrying about varchar(10) i can set it to text. Follow up question, an epoch timestamp is bigger than an integer, should sometime saving that info be saved as text or what do you recommend for setting timestamps on entries?

Original message was deleted

captain

C

captain•9/24/23, 3:45 PM

for instance in this thread I see somebody setting: time_stamp timestamp NOT NULL DEFAULT,

captain

C

captain•9/24/23, 3:45 PM

how is that converted?

Original message was deleted

captain

C

captain•9/24/23, 3:47 PM

for instance in my scenario, i have different customer ids, and I'm tracking specific log actions via their id and the time it happened. then someday i might want to go back and query the last week of action organized in newest to old, or go back to 2 months ago for only a range of a month. What would be the best way of storing that timestamp?

captain

C

captain•9/24/23, 3:48 PM

currently i'm saving the epoch time in my key of my KV entry

captain

C

captain•9/24/23, 3:48 PM

but listing keys and doing that sort isn't really possible

captain

C

captain•9/24/23, 3:49 PM

got it

captain

C

captain•9/24/23, 3:53 PM

so an epoch time generated via worker would be 1695071510081, but that is bigger than an INT, and BIGINT would probably get converted to REAL, so better to just set that value to REAL, correct?

captain

C

captain•9/24/23, 3:53 PM

but behind the scenes does any of this matter, and could it just be stored as TEXT?

captain

C

captain•9/24/23, 3:55 PM

or nevermind, best to do it as an integer

captain

C

captain•9/24/23, 3:55 PM

okay well thank LEO, appreciate your time

Original message was deleted

captain

C

captain•9/24/23, 4:01 PM

thanks Leo, appreciate your time

captain

C

captain•9/24/23, 4:36 PM

Hello, wondering if anybody can see why this wont run?
sudo wrangler d1 execute mydevdatabase --file schemas/schema.sql

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

however getting this error:

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:38 PM

You forgot a default on time_stamp

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:39 PM

time_stamp INTEGER NOT NULL DEFAULT,

There's no value for DEFAULT. It should be

time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:39 PM

See section 3.2 here: https://www.sqlite.org/lang_createtable.html

Isaac McFadyen

IIsaac McFadyen You forgot a default on time_stamp

captain

C

captain•9/24/23, 4:51 PM

ahh, thanks for that, it worked! I actually removed the default so that I can manually set it via epoch time stamp generated via my worker. I am reading the worker time could slightly differ than the D1 time, so thinking not setting it via the D1 is a better idea, your thoughts?

captain

Ccaptain ahh, thanks for that, it worked! I actually removed the default so that I can ma...

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:52 PM

Both D1 time and Worker time aren't exact, because of security concerns. But they're close.

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:53 PM

Usually milliseconds difference between the observed time and the real time, so unless you need that precision you should be fine to set it wherever.

Isaac McFadyen

IIsaac McFadyen Usually milliseconds difference between the observed time and the real time, so ...

captain

C

captain•9/24/23, 4:55 PM

my use case is tracking webhooks from stripe and then showing the non paid messages to the user and telling them to get their act together! So wanting the timestamp webhook sending to act as the time stamp that i can then sort by

captain

Ccaptain my use case is tracking webhooks from stripe and then showing the non paid messa...

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:55 PM

Ah, OK. Does Stripe provide a timestamp within the webhook message?

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:55 PM

If so you could use that.

Isaac McFadyen

IIsaac McFadyen Ah, OK. Does Stripe provide a timestamp within the webhook message?

captain

C

captain•9/24/23, 4:57 PM

true, but either way best not to have D1 set it i think

captain

C

captain•9/24/23, 4:58 PM

anyways thanks for pointing out that error, D1, very exciting stuff rather than using all this crazy logic i have written around KV storage

captain

Ccaptain true, but either way best not to have D1 set it i think

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:58 PM

Yeah - D1 setting it would occur on insert, not on message received.

captain

Ccaptain anyways thanks for pointing out that error, D1, very exciting stuff rather than ...

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 4:58 PM

No worries, happy to help

captain

C

captain•9/24/23, 5:14 PM

more D1 questions! I created a D1 database many many months ago, but now reading this: https://blog.cloudflare.com/d1-turning-it-up-to-11/ it looks like things have changed and running the command wrangler d1 info <DB_NAME> I can see my db is using version alpha. The question, is there a single command to move this db to the newer version? Or is the proper move to simply manually recreate the DB and update the ID's in the TOML file

The Cloudflare Blog

D1: We turned it up to 11

We've been heads down iterating on D1, and we've just shipped a major new version that's substantially faster, more reliable, and introduces Time Travel: the ability to restore a D1 database to any point in time.

captain

C

captain•9/24/23, 5:24 PM

@Matt is "INSERTZ INFO" found on https://developers.cloudflare.com/d1/learning/debug-d1/ a typo? Or is this something specific for D1?

Debug D1 · Cloudflare D1 docs

D1 allows you to capture exceptions and log errors returned when querying a database. To debug D1, you will use the same tools available when …

captain

Ccaptain @Matt is "INSERTZ INFO" found on https://developers.cloudflare.com/d1/learning/d...

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 5:28 PM

That's an intentional typo, yup.

captain

C

captain•9/24/23, 6:25 PM

question, is it not possible to backup an alpha database and then restore it into a beta database. When I attempt the basic method of restoring in that situation i get:
Internal error: failed to restore backup: Experimental DBs do not support backups yet:
74244d9f-afa8-4ca2-ab7f-8067f4505c97 (v3) [code: 7500]

captain

Ccaptain question, is it not possible to backup an alpha database and then restore it int...

Isaac McFadyen

I

Isaac McFadyen•9/24/23, 6:43 PM

I believe you need to dump it to a SQL file and reupload.

Isaac McFadyen

IIsaac McFadyen I believe you need to dump it to a SQL file and reupload.

captain

C

captain•9/24/23, 7:12 PM

success!

captain

C

captain•9/24/23, 7:44 PM

another question...did a dump from an alpha database, created a new database, importanted the dump, success. NOW to the that new database I'm running the sql:

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

and getting the following error:

SQL code did not contain a statement. [code: 7500]

This same statement seemed to work on the alpha database, thoughts?

Resolved, I had a comment with a semi colon which is an bug documented here: https://github.com/cloudflare/workers-sdk/issues/3892 whats interesting is this bug only applies to the beta D1 and not the alpha

GitHub

🐛 BUG: d1 execution with comment including semicolon causes SQL cod...

Which Cloudflare product(s) does this pertain to? D1 What version(s) of the tool(s) are you using? 3.6.0 What version of Node are you using? 20.5.1 What operating system are you using? Mac Describe...

Matt Silverlock

MMatt Silverlock Thanks: looking into why it's sticking.

benthecarman

B

benthecarman•9/24/23, 9:42 PM

it's still down, any updates here?

benthecarman

Bbenthecarman it's still down, any updates here?

Matt Silverlock

M

Matt Silverlock•9/24/23, 9:43 PM

We’re still chasing down the cause of the bug. I’ll provide an update when we have one.

benthecarman

B

benthecarman•9/24/23, 9:44 PM

Is it possible we can just get the sqlite file? We are just trying to move off of this because it is unusable

benthecarman

Bbenthecarman Is it possible we can just get the sqlite file? We are just trying to move off o...

Matt Silverlock

M

Matt Silverlock•9/24/23, 11:17 PM

There isn't the concept of a 'single SQLite file' in D1 (as that wouldn't allow us to be distributed).

benthecarman

Bbenthecarman Is it possible we can just get the sqlite file? We are just trying to move off o...

Matt Silverlock

M

Matt Silverlock•9/24/23, 11:18 PM

And I understand the frustration: we don't want these kind of bugs, but this is an alpha/beta.

benthecarman

B

benthecarman•9/24/23, 11:20 PM

okay, if guys can get it back up for a couple hours we can transfer off of it

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL DEFAULT,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

  ERROR 9009: SQL prepare error: near ",": syntax error in CREATE TABLE IF NOT EXISTS messages (
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    amount INTEGER NOT NULL,
    color TEXT NOT NULL,
    event_type TEXT NOT NULL,
    e_id TEXT NOT NULL,
    i_id TEXT NOT NULL, -- invoice id
    o_id TEXT NOT NULL, --
    s_id TEXT NOT NULL, -- subscription id
    p_id TEXT NOT NULL, -- payment id
    c_id TEXT NOT NULL, -- customer id
    service TEXT NOT NULL,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    link TEXT NOT NULL,
    related_locations TEXT NOT NULL,
    time_stamp INTEGER NOT NULL DEFAULT,
    customer_dismiss INTEGER,
    location_id TEXT NOT NULL,
    status TEXT NOT NULL, -- new or dismissed
    status_type TEXT NOT NULL, -- notification /
    other TEXT NOT NULL
  ); at offset 524 [code: 7500]

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);

DROP TABLE IF EXISTS messages;
CREATE TABLE IF NOT EXISTS messages (
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  amount INTEGER NOT NULL,
  color TEXT NOT NULL,
  event_type TEXT NOT NULL,
  e_id TEXT NOT NULL, 
  i_id TEXT NOT NULL, -- invoice id
  o_id TEXT NOT NULL, --
  s_id TEXT NOT NULL, -- subscription id
  p_id TEXT NOT NULL, -- payment id
  c_id TEXT NOT NULL, -- customer id
  service TEXT NOT NULL, 
  email TEXT NOT NULL,
  name TEXT NOT NULL,
  link TEXT NOT NULL,
  related_locations TEXT NOT NULL,
  time_stamp INTEGER NOT NULL, -- time_stamp INTEGER NOT NULL DEFAULT CURRENT_TIMESTAMP,
  customer_dismiss INTEGER, -- 0 or 1
  location_id TEXT NOT NULL,
  status TEXT NOT NULL, -- new or dismissed
  status_type TEXT NOT NULL, -- notification / 
  other TEXT NOT NULL
);