,Hi all, I am having an issue with my D1 database. When attempting to index one of the columns I g

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

C

Cyb3r-Jak3•7/14/23, 7:36 PM

Do you have the database ID? Sounds like something that the D1 team is going to want to look at on their side

CCyb3r-Jak3 Do you have the database ID? Sounds like something that the D1 team is going to ...

L

LucianOP•7/14/23, 7:40 PM

I restored the database to a 1 day old copy and now it works, the index created. I will need to reload about 40k records, but it is what it is

LLucian ,Hi all, I am having an issue with my D1 database. When attempting to index one...

M

Matt Silverlock•7/14/23, 9:46 PM

It sounds like you’re using the original alpha backend - that error is very specific to it.

M

Matt Silverlock•7/14/23, 9:47 PM

see the note about the experimental backend - https://developers.cloudflare.com/d1/get-started/#2-create-a-database

Get started · Cloudflare D1 docs

This guide will instruct you through:

S

staticmedia•7/15/23, 7:15 AM

Hi, is the performance between getting data from KV versus from D1 neglible ? (assuming the DB lookup is a find on PK)

S

staticmedia•7/15/23, 7:20 AM

I'm building on a website that caches all the data from the headless CMS on cloudflare using KV. When the DB has an update a webhook pings the worker and updates the cache. The website makes requests to the same worker that fetches from the cache, and hydrates the HTML before sending back to the user

S

staticmedia•7/15/23, 7:21 AM

Because KV doesn't provide cache tagging its quite difficult to manage the cache (with nested data and making sure those nested items stay fresh). I want to store the cache in a D1 so I can more easily lookup data that needs refreshing

T

Tony Leung•7/15/23, 1:41 PM

I'm pretty curious about this: KV is not transaction-ready, is D1 transaction ready? If I have an user from US made a request to my page function using D1 in APAC, will the request take longer? Is there any worker+D1 replica in US which can handle the request for me? If there is and D1 supports transaction, will there any penalty on performance or request time?

V

vassbence•7/15/23, 2:47 PM

D1 is single writer multi reader (or at least will be)

V

vassbence•7/15/23, 2:48 PM

but KV and D1 are entirely different products for different usecases

T

theLaCzar•7/15/23, 11:55 PM

quick question, i designed microservices with 1ms : 1db design, however, we are limited to 10DB currently per account, knowing i have several sites under my account, so i guess i have to fit them in, my question is does D1 experience the same issues with normal relations db like table locking, running out of resource etc?

T

theLaCzar•7/15/23, 11:57 PM

because if D1 is efficient enough that table locking is not much of an issue, i guess ill go with a monolitic DB design and lots of workers

TtheLaCzar because if D1 is efficient enough that table locking is not much of an issue, i ...

M

Matt Silverlock•7/16/23, 12:25 AM

You can have multiple concurrent readers. Reads don’t block.

U

Unsmart•7/16/23, 9:27 PM

Its rounded up. So a unit of 0.00000000000001 would still be 1 full unit

S

samratarmas•7/17/23, 6:12 AM

Hi team, can we use SQLite triggers in d1?

S

samratarmas•7/17/23, 6:13 AM

My usecase is as follows:

S

samratarmas•7/17/23, 6:14 AM


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


-- Create a table. And an external content fts5 table to index it.


CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);


CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');

-- Triggers to keep the FTS index up to date.


CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;


CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;


CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
  INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
  INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;

S

samratarmas•7/17/23, 6:14 AM

Basically using triggers to keep the fts5 virtual table indexes in sync with a content table, is this possible?

Ssamratarmas Hi team, can we use SQLite triggers in d1?

M

muslax•7/17/23, 7:23 AM

I currently use triggers in D1. So yes.

C

chientrm.com•7/17/23, 11:26 AM

is there anything wrong with the latest

"@cloudflare/workers-types": "^4.20230710.1"

"@cloudflare/workers-types": "^4.20230710.1"

"@cloudflare/workers-types": "^4.20230710.1"

"@cloudflare/workers-types": "^4.20230710.1"

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName: string): Promise<T | null>;
  first<T = unknown>(): Promise<Record<string, T> | null>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T[]>>;
  raw<T = unknown>(): Promise<T[]>;
}

C

chientrm.com•7/17/23, 11:26 AM

why is the

first

first

first

first return

Record<string, T>

Record<string, T>

Record<string, T>

Record<string, T> but not

?

C

chientrm.com•7/17/23, 11:27 AM

Here is version

4.20230518.0

4.20230518.0

4.20230518.0

4.20230518.0:

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

declare abstract class D1PreparedStatement {
  bind(...values: any[]): D1PreparedStatement;
  first<T = unknown>(colName?: string): Promise<T>;
  run<T = unknown>(): Promise<D1Result<T>>;
  all<T = unknown>(): Promise<D1Result<T>>;
  raw<T = unknown>(): Promise<T[]>;
}

Original message was deleted

C

Cyb3r-Jak3•7/17/23, 2:29 PM

d1-database

Cchientrm.com why is the `first` return `Record<string, T>` but not `T`? 🤔

M

Matt Silverlock•7/17/23, 5:02 PM

This is a bug: d1-database cc @geelen

《

《 Sarge 》•7/17/23, 5:42 PM

Hey has anyone come across a bug when attempting to execute a

DROP TABLE IF EXISTS [table001]

DROP TABLE IF EXISTS [table001]

DROP TABLE IF EXISTS [table001]

DROP TABLE IF EXISTS [table001] statement, and wrangler returns an error

no such table: main.[table001]?

no such table: main.[table001]?

no such table: main.[table001]?

no such table: main.[table001]?
I can execute the same script with no issue via DBeaver, but when attempting to do it via wrangler 3.20 and wrangler 2.20 I get this no such table error?

《

《 Sarge 》•7/17/23, 5:57 PM

I have been using brackets, and it didn't seem to cause an issue for me.

《

《 Sarge 》•7/17/23, 5:57 PM

The brakets here are just an example, I don't have that in the code.

《

《 Sarge 》•7/17/23, 5:59 PM

here is a better example, I'm just being careful not to share IP that is not my own.

Original message was deleted

《

《 Sarge 》•7/17/23, 6:12 PM

Okay, I'll attempt to change all my square brackets to be '`'
The thing is, it doesn't even seem to get past the initial DROP IF EXISTS statement shown above, and that has not brackets around it.
Probably should just ignore that original example I gave, and look at the screenshots.

Original message was deleted

《

《 Sarge 》•7/17/23, 6:17 PM

Yeah, tried that, and again it all ran fine in DBeaver, but failed with the same error in wrangler@3.20

《《 Sarge 》Hey has anyone come across a bug when attempting to execute a`DROP TABLE IF EXIS...

M

Matt Silverlock•7/17/23, 6:50 PM

Don't refer to it as

main.table_name

main.table_name

main.table_name

main.table_name. Just

table_name

table_name

table_name

table_name.

M

Matt Silverlock•7/17/23, 6:51 PM

e.g. (this is all junk data, not real emails)

Original message was deleted

《

《 Sarge 》•7/17/23, 6:51 PM

The

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS

DROP TABLE IF EXISTS runs fine in DBeaver, but not wrangler@3.20

MMatt Silverlock e.g. (this is all junk data, not real emails)

《

《 Sarge 》•7/17/23, 6:54 PM

No I'm not querying the 'main.' in SQL at all. I have a build data script that drops everything and rebuilds the database from scratch.
at he beginning of it, I have the drops, then I have the entire DB created again.
The script is rather large @ikea.se, and I'd probably not be able to post it all here anyway

《

《 Sarge 》•7/17/23, 6:54 PM

The I run wrangler pointing to a file

《

《 Sarge 》•7/17/23, 6:55 PM

I've used a binary search algo, to narrow it down to the drop if exists statements

《《 Sarge 》The I run wrangler pointing to a file

M

Matt Silverlock•7/17/23, 6:55 PM

I've yet to see

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute report this.

M

Matt Silverlock•7/17/23, 6:56 PM

or

--command='PRAGMA table_list'

--command='PRAGMA table_list'

--command='PRAGMA table_list'

--command='PRAGMA table_list'

《

《 Sarge 》•7/17/23, 6:58 PM

okay I'll give it a go. @ikea.se
Yeah, It has to be something in the script. I've been rebuilding this for a while, and not had this issue in the past @Matt
What throws me off is the script works and rebuilds fine form DBeaver. So smells like a support thing.

《

《 Sarge 》•7/17/23, 7:00 PM

Yeah that passes @ikea.se.
So might be a symptom then...interesting

《

《 Sarge 》•7/17/23, 7:03 PM

But when I run my large script it comes back with the same error. painful.
I'll try to comment out things again and see what it doesn't like again.

MMatt Silverlock or `--command='PRAGMA table_list'`

《

《 Sarge 》•7/17/23, 7:10 PM

Yes that works as well. Lists out the tables in there.

《

《 Sarge 》•7/17/23, 7:21 PM

Hmmm I have created a sub set of the scripts that I'm working with.
I have a list of all the DROP TABLE IF EXISTS,
and a sub set of CREATE table x4.
and I'm seeing the no such table error again.

I assume this is the correct command to see locally?

wrangler d1 execute <db> --command='PRAGMA table_list' --local

wrangler d1 execute <db> --command='PRAGMA table_list' --local

wrangler d1 execute <db> --command='PRAGMA table_list' --local

wrangler d1 execute <db> --command='PRAGMA table_list' --local
Because when I check the table_list, it seems like nothing changed.

《

《 Sarge 》•7/17/23, 7:22 PM

Maybe I can DM more details to one of you, and share a little more detail, then come back with a conclusion here?
I don't want to share IP, nor spam this room too much.

《《 Sarge 》Hmmm I have created a sub set of the scripts that I'm working with. I have a li...

M

Matt Silverlock•7/17/23, 7:29 PM

Yes,

--local

--local

is local. If you don't pass

--local

--local

, and run

wrangler d1 execute [...]

wrangler d1 execute [...]

wrangler d1 execute [...]

wrangler d1 execute [...]

it runs on the Cloudflare network or "production" to some. If you haven't created that table in prod == you get errors.

MMatt Silverlock Yes, `--local` is local. If you don't pass `--local`, and run `wrangler d1 execu...

《

《 Sarge 》•7/17/23, 7:31 PM

okay cool. So everything I'm doing right now is --local. I do have a production and develop database in the network, and have previously used this method to update them with no issue.

《

《 Sarge 》•7/17/23, 7:32 PM

I did attempt to run ths same script against my development database, and wittnessed the same error I'm getting locally.

《

《 Sarge 》•7/17/23, 7:33 PM

SO at least it's consistent...

,Hi all, I am having an issue with my D1 database. When attempting to index one of the columns I g

Similar Threads