Hi team, does D1 support using sqlite extensions? For example, if I want to use full text search cap

S

samratarmasOP•7/13/23, 11:47 AM

And second question, is js transactions coming anytime this year or is that a much longer term feature? ( read a value from d1, use that read value to do some calculation then write the result back, all inside a transaction)

MMatt Silverlock Your `wrangler.toml` doesn't need anything more than just a `[[d1_database]]` de...

S

slowpoke•7/13/23, 1:04 PM

Thanks for your response :) Yep! This is basically what I have right now and it works great!

I suppose once DB backups are available to download, it would be nice to download that to use for local development. Making a seed in the meantime is nice anyways because it declares the database structure for reference

Ssamratarmas Hi team, does D1 support using sqlite extensions? For example, if I want to use ...

M

Matt Silverlock•7/13/23, 1:08 PM

FTS5 is built in. On our list to include in our documentation, but works exactly as the SQLite docs say. We also support JSON5 - https://developers.cloudflare.com/d1/learning/querying-json/

Query JSON · Cloudflare D1 docs

D1 has built-in support for querying and parsing JSON data stored within a database. This enables you to:

M

micromashor•7/13/23, 6:06 PM

I keep randomly encountering this error while using

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute: (some details redacted)

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

✘ [ERROR] A request to the Cloudflare API (/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>/query) failed.

  Internal error: Error sending request to D1 for database <DATABASE_ID>
  [code: 7501]

M

micromashor•7/13/23, 6:09 PM

it's also weird bc about half the time I see the error, the query went through successfully

M

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

Are you using the new experimental backend? You can (should) log

error.cause.message

error.cause.message

error.cause.message

error.cause.message as well - https://developers.cloudflare.com/d1/platform/client-api/#errors

D1 client API · Cloudflare D1 docs

As part of our Client API, both static and prepared statements are supported. Best practice is to use prepared statements which are precompiled …

Original message was deleted

M

micromashor•7/14/23, 1:27 AM

yes that's correct - it was produced by wrangler

MMatt Silverlock Are you using the new experimental backend? You can (should) log `error.cause.me...

M

micromashor•7/14/23, 2:14 AM

and yes, I am using the experimental backend. I was trying to do an

INSERT

INSERT

INSERT

INSERT operation with fewer than 20 rows, so I can't imagine it was failing due to any kind of size limit

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

M

Matt Silverlock•7/14/23, 11:35 AM

Without more details it’s hard to debug. Is the data static? Not all rows are created equal.

Mmicromashor and yes, I am using the experimental backend. I was trying to do an `INSERT` ope...

M

Matt Silverlock•7/14/23, 11:54 AM

I’d take a look through https://developers.cloudflare.com/d1/platform/limits/ to make sure you aren’t hitting limits. We have some changes queued up that will better expose error messages.

Limits · Cloudflare D1 docs

Many of these limits will increase during D1’s public alpha. Join the #d1-open-alpha channel in the Cloudflare Developer Discord to keep up to date …

M

Murder Chicken•7/14/23, 12:31 PM

I seem to be getting a lot of

Error: D1_ERROR: Error: Network connection lost.

Error: D1_ERROR: Error: Network connection lost.

Error: D1_ERROR: Error: Network connection lost.

Error: D1_ERROR: Error: Network connection lost. errors with fairly simple (yet critical) queries my worker is running. The DB is using the experimental

d1

d1

d1

d1 backend. Is there a tried and true way to get to the bottom of why this is happening?

MMurder Chicken I seem to be getting a lot of `Error: D1_ERROR: Error: Network connection lost.`...

M

Matt Silverlock•7/14/23, 2:43 PM

Can you share your database ID here, and an example query? Is this deterministic - e.g. a specific query always causes the error?

M

Murder Chicken•7/14/23, 2:45 PM

Seems to be, will DM

M

Murder Chicken•7/14/23, 2:52 PM

@Matt sent.

Original message was deleted

I

itsmatteomanf•7/14/23, 3:51 PM

Do you have a primary key column?

I

itsmatteomanf•7/14/23, 3:55 PM

When you configure a database you need a primary key column (or columns) so that the database knows what data should unique. If you don't set that up, how would the DB know what is new and what is data to be replaced?

Original message was deleted

I

itsmatteomanf•7/14/23, 3:57 PM

Yes!

Original message was deleted

I

itsmatteomanf•7/14/23, 4:03 PM

I'd personally learn to use an actual SQL query (either via the browser console, or directly from

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute

wrangler d1 execute), which allows more granular control than the Ui in the dashboard, and repeatable creations.

I

itsmatteomanf•7/14/23, 4:03 PM

But this would work.

MMatt Silverlock Without more details it’s hard to debug. Is the data static? Not all rows are cr...

M

micromashor•7/14/23, 6:06 PM

yeah it's 3 text fields (all fields <100chars) and an ID, so I seriously doubt I'm hitting any limits. Will watch out for better error messages though

L

Lucian•7/14/23, 6:56 PM

,Hi all,

I am having an issue with my D1 database. When attempting to index one of the columns I get the error below:
ERROR 9015: SQL engine error: query error: Error code 11: The database disk image is malformed (database disk image is malformed).

CREATE INDEX idx_teams_team_name ON teams(team_name);

Any idea what can I do to get around this? The table has about 90k rows; I surely need an index to search on this column

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

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

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

Hi team, can we use SQLite triggers in d1?

S

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

My usecase is as follows:

S

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

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

T

T?

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.

Hi team, does D1 support using sqlite extensions? For example, if I want to use full text search cap

Similar Threads

Similar Threads

Similar Threads