Also: https://discord.com/channels/595317990191398933/992060581832032316/1211617743082758165

MMatt Silverlock Also: https://discord.com/channels/595317990191398933/992060581832032316/1211617...

M

we also plan on building UI for this, so you won't have to use

wrangler d1 insights

wrangler d1 insights

MMax (@rozenmd)we also plan on building UI for this, so you won't have to use `wrangler d1 insi...

M

Matt SilverlockOP•3/15/24, 3:17 PM

^ this!

Example via

wrangler

wrangler

today:

➜  npx wrangler@latest d1 insights db-enam --sort-by=reads

➜  npx wrangler@latest d1 insights db-enam --sort-by=reads

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

[
  {
    "query": "SELECT * FROM [Order] ORDER BY random() LIMIT ?",
    "avgRowsRead": 2000,
    "totalRowsRead": 2842000,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 3.9992672061928225,
    "totalDurationMs": 5682.958700000001,
    "numberOfTimesRun": 1421
  },
  {
    "query": "DROP TABLE IF EXISTS \"Order\";",
    "avgRowsRead": 13,
    "totalRowsRead": 17004,
    "avgRowsWritten": 0,
    "totalRowsWritten": 0,
    "avgDurationMs": 0.35294311926605504,
    "totalDurationMs": 461.6496,
    "numberOfTimesRun": 1308
  },

N

natsumesou•3/15/24, 3:21 PM

@Matt @Max (@rozenmd) Its cool! thank you so much.

F

florent•3/15/24, 10:07 PM

D1 can only be queried in a worker or pages environment right? So I couldnt query it from a vercel hosted nextjs app?

Fflorent D1 can only be queried in a worker or pages environment right? So I couldnt quer...

I

itsmatteomanf•3/15/24, 10:12 PM

You can, you need a Worker to be a proxy.

M

matt•3/17/24, 1:17 AM

if using the

/query

/query

/query

/query endpoint with multiple statements, is the operation atomic?

M

matt•3/17/24, 1:19 AM

is read replication enabled? the top comment at https://news.ycombinator.com/item?id=36208568 suggests it is

thunderbong

Why SQLite is so great for the edge

Hacker News

•

6/6/23, 5:37 AM

Mmatt is read replication enabled? the top comment at https://news.ycombinator.com/ite...

I

Isaac McFadyen•3/17/24, 1:27 AM

Replication is not currently enabled for D1.

M

matt•3/17/24, 2:23 AM

thanks isaac, looking forward to the day when we get replication. i currently use upstash but I need multiple indexes, and my workaround is starting to get a bit dicey

M

matt•3/17/24, 4:13 AM

how is the /query endpoint implemented? it seems to accept multiple statements, which I think only works with

exec()

exec()

exec()

exec()

but then it also accepts params, which will only work with
```
prepare()
```
prepare()
```
prepare()
```
prepare()
-- but prepare doesn't work with multiple statements

M

matt•3/17/24, 4:45 AM

nvm, it's not supported - The request is malformed: params with multiple statements is not supported

D

dakom•3/17/24, 6:08 AM

I am a bit confused about the migrations API... let's say I have two different databases:

db-env

db-env

db-env

db-env and

db-prod

db-prod

db-prod

db-prod

even if these are created under completely separate env sections in the toml and separate bindings, like this:

[env.prod]
build = { command = "worker-build --release" }
d1_databases = [{ binding = "DB_PROD", database_name = "foo-prod", database_id = "id-2" }]

[env.dev]
build = { command = "worker-build --dev" }
d1_databases = [{ binding = "DB_DEV", database_name = "foo-dev", database_id = "id-1" }]

[env.prod]
build = { command = "worker-build --release" }
d1_databases = [{ binding = "DB_PROD", database_name = "foo-prod", database_id = "id-2" }]

[env.dev]
build = { command = "worker-build --dev" }
d1_databases = [{ binding = "DB_DEV", database_name = "foo-dev", database_id = "id-1" }]

[env.prod]
build = { command = "worker-build --release" }
d1_databases = [{ binding = "DB_PROD", database_name = "foo-prod", database_id = "id-2" }]

[env.dev]
build = { command = "worker-build --dev" }
d1_databases = [{ binding = "DB_DEV", database_name = "foo-dev", database_id = "id-1" }]

[env.prod]
build = { command = "worker-build --release" }
d1_databases = [{ binding = "DB_PROD", database_name = "foo-prod", database_id = "id-2" }]

[env.dev]
build = { command = "worker-build --dev" }
d1_databases = [{ binding = "DB_DEV", database_name = "foo-dev", database_id = "id-1" }]

there seems to be no separation of migrations, e.g. this will produce

migrations/0001_init.sql

migrations/0001_init.sql

migrations/0001_init.sql

migrations/0001_init.sql and

migrations/0002_init.sql

migrations/0002_init.sql

migrations/0002_init.sql

migrations/0002_init.sql:

npx wrangler d1 migrations create foo-dev init --env=dev
npx wrangler d1 migrations create foo-prod init --env=prod

npx wrangler d1 migrations create foo-dev init --env=dev
npx wrangler d1 migrations create foo-prod init --env=prod

npx wrangler d1 migrations create foo-dev init --env=dev
npx wrangler d1 migrations create foo-prod init --env=prod

npx wrangler d1 migrations create foo-dev init --env=dev
npx wrangler d1 migrations create foo-prod init --env=prod

D

dakom•3/17/24, 6:10 AM

what I'm thinking is that I should instead have completely separate local directories with their own

wrangler.toml

wrangler.toml

, just for maintaining database migrations?

D

dakom•3/17/24, 6:16 AM

oh I missed the

migrations_dir

migrations_dir

migrations_dir

migrations_dir key... though, I think having it in its own dir is better for my personal taste anyway, I don't necessarily want the db associated with the same dir as the worker

D

dakom•3/17/24, 7:43 AM

aaand nm, having a separate dir means local dev won't see the sqllite files

Original message was deleted

D

dakom•3/17/24, 2:08 PM

but writing the files locally, I may want to test out some ideas in a dev db before actually commiting those migrations to prod

D

dakom•3/17/24, 2:41 PM

anyhoo - adding the

migrations_dir

migrations_dir

migrations_dir

migrations_dir key worked perfectly

N

Nob•3/17/24, 3:57 PM

Hi, is D1 globally replicated or its just in the region we select at start ?

NNob Hi, is D1 globally replicated or its just in the region we select at start ?

C

Cyb3r-Jak3•3/17/24, 3:59 PM

Not globally replicated yet

CCyb3r-Jak3 Not globally replicated yet

N

Nob•3/17/24, 4:00 PM

ah ok that's what I thought with 200 ms to read my db from another country

N

Nob•3/17/24, 4:00 PM

is there a date for release of replicated db?

NNob is there a date for release of replicated db?

C

Cyb3r-Jak3•3/17/24, 4:01 PM

No. I believe they said they wanted to target for GA release but not hard release date

O

ozgurgul1203•3/17/24, 5:14 PM

Hello, I have a question

Is there a way to connect to remote D1 database from a GUI (except CF dashboard) ?

Oozgurgul1203 Hello, I have a question 1. Is there a way to connect to remote D1 database from...

C

Cyb3r-Jak3•3/17/24, 5:21 PM

The GUI client will have needed to add support for D1 HTTP API

CCyb3r-Jak3 The GUI client will have needed to add support for D1 HTTP API

O

ozgurgul1203•3/17/24, 5:41 PM

are you aware of any existing solution?

MMatt Silverlock 1. Right now, deletes are treated as writes. Subject to change. Unless you're de...

J

John Spurlock•3/17/24, 6:56 PM

Right now, deletes are treated as writes.

is this still the case wrt pricing? if so (actually either way), may warrant a mention in https://developers.cloudflare.com/d1/platform/pricing/

JJohn Spurlock > Right now, deletes are treated as writes. is this still the case wrt pricing?...

M

Matt SilverlockOP•3/17/24, 6:59 PM

Yes. For whatever reason this PR didn’t build: https://github.com/cloudflare/cloudflare-docs/pull/13234/files

GitHub

d1: pricing - explain deletes by elithrar · Pull Request #13234 · c...

Explains that writes include deletes.

J

jkomyno•3/18/24, 11:38 AM

Does D1 implement

ON UPDATE RESTRICT

ON UPDATE RESTRICT

ON UPDATE RESTRICT

ON UPDATE RESTRICT, to prevent updates from happening if there's any foreign keys that relies on a field that's being changed?

Jjkomyno Does D1 implement `ON UPDATE RESTRICT`, to prevent updates from happening if the...

M

Max (@rozenmd)•3/18/24, 11:41 AM

looks like it should: https://www.sqlite.org/foreignkeys.html#fk_actions

MMax (@rozenmd)looks like it should: https://www.sqlite.org/foreignkeys.html#fk_actions

J

jkomyno•3/18/24, 12:20 PM

SQLite does, but and equivalent sequence of CREATE + UPDATE on D1 results in a different behavior on the same scenario. The code is currently entangled with an ORM usage, but I hope to be able to provide a pure SQL reproduction soon.

J

jkomyno•3/18/24, 12:22 PM

Similar question: it there a public page containing all known D1 limitations/differences compared to the latest version of SQLite?

I

Isaac McFadyen•3/18/24, 12:24 PM

D1 should be functionally equivalent - there's currently a single patch applied to SQLite to get it to work with D1, plus the backend implementation (which shouldn't affect behavior)

I

Isaac McFadyen•3/18/24, 12:25 PM

https://github.com/cloudflare/workerd/blob/03619f82c6bad4bd9d305942701bbd17fd524852/patches/sqlite/0001-row-counts-plain.patch is the patch if you're interested.

I

Isaac McFadyen•3/18/24, 12:26 PM

Maybe a compile flag affects that behavior and your local SQLite was compiled differently than D1? I'm unsure of that though.

M

Max (@rozenmd)•3/18/24, 12:26 PM

You can see our compile flags here: https://github.com/cloudflare/workerd/blob/main/build/BUILD.sqlite3

Original message was deleted

M

Max (@rozenmd)•3/18/24, 2:54 PM

what's the error?

Original message was deleted

M

Max (@rozenmd)•3/18/24, 3:35 PM

seems to work for me, using this as a schema:

CREATE TABLE `experiences` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `experience_owners` (
    `experience_id` text NOT NULL,
    `user_id` text NOT NULL,
    PRIMARY KEY(`experience_id`, `user_id`),
    FOREIGN KEY (`experience_id`) REFERENCES `experiences`(`id`) ON UPDATE no action ON DELETE cascade
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

insert into experiences (id, name) VALUES (1, 'owning a house');
insert into users (id, name) VALUES (1, 'some guy');
insert into experience_owners (experience_id, user_id) VALUES (1,1);

CREATE TABLE `experiences` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `experience_owners` (
    `experience_id` text NOT NULL,
    `user_id` text NOT NULL,
    PRIMARY KEY(`experience_id`, `user_id`),
    FOREIGN KEY (`experience_id`) REFERENCES `experiences`(`id`) ON UPDATE no action ON DELETE cascade
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

insert into experiences (id, name) VALUES (1, 'owning a house');
insert into users (id, name) VALUES (1, 'some guy');
insert into experience_owners (experience_id, user_id) VALUES (1,1);

CREATE TABLE `experiences` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `experience_owners` (
    `experience_id` text NOT NULL,
    `user_id` text NOT NULL,
    PRIMARY KEY(`experience_id`, `user_id`),
    FOREIGN KEY (`experience_id`) REFERENCES `experiences`(`id`) ON UPDATE no action ON DELETE cascade
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

insert into experiences (id, name) VALUES (1, 'owning a house');
insert into users (id, name) VALUES (1, 'some guy');
insert into experience_owners (experience_id, user_id) VALUES (1,1);

CREATE TABLE `experiences` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `users` (
    `id` text PRIMARY KEY NOT NULL,
    `name` text NOT NULL
);

CREATE TABLE `experience_owners` (
    `experience_id` text NOT NULL,
    `user_id` text NOT NULL,
    PRIMARY KEY(`experience_id`, `user_id`),
    FOREIGN KEY (`experience_id`) REFERENCES `experiences`(`id`) ON UPDATE no action ON DELETE cascade
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON UPDATE no action ON DELETE no action
);

insert into experiences (id, name) VALUES (1, 'owning a house');
insert into users (id, name) VALUES (1, 'some guy');
insert into experience_owners (experience_id, user_id) VALUES (1,1);

E

Envy•3/18/24, 5:13 PM

I recently changed some of my write queries that were doing INSERT OR REPLACE to UPDATE since I'm only updating a few fields and I have indexes that might have to recalculate. I have noticed that the UPDATE queries are getting a row_written for each field that is updated instead of 1 row_written per row.

EDIT: It was the indexes not the UPDATE statement

K

kelbs•3/18/24, 5:16 PM

I see scale to zero is offered with D1. Does that mean the database spins down / goes to sleep after a period of inactivity? If so, what is the latency to wake it from inactive?

EEnvy I recently changed some of my write queries that were doing INSERT OR REPLACE to...

I

Isaac McFadyen•3/18/24, 5:21 PM

Indexes count for a row written - could that be the reason?

I

Isaac McFadyen•3/18/24, 5:21 PM

If you have 4 indexes it will write 5 times (row + 4 indexes)

Kkelbs I see scale to zero is offered with D1. Does that mean the database spins down /...

I

Isaac McFadyen•3/18/24, 5:22 PM

D1 is built on SQLite. So it's actually not running at all when it's not being used. SQLite also makes spin-ups near-instant though.

K

kelbs•3/18/24, 5:23 PM

Turso claims 500ms latency to start up an inactive sqlite db on their system. Maybe there are differences between that and D1 though e.g. they're using libSQL

K

kelbs•3/18/24, 5:24 PM

https://docs.turso.tech/features/scale-to-zero

IIsaac McFadyen If you have 4 indexes it will write 5 times (row + 4 indexes)

E

Envy•3/18/24, 5:25 PM

Yeah that might be it

Kkelbs Turso claims 500ms latency to start up an inactive sqlite db on their system. Ma...

I

Isaac McFadyen•3/18/24, 5:27 PM

So one of the differences between Turso and D1 is that Turso actually runs a server for serving SQLite queries using libSQL as you said. D1 directly accesses SQLite on the node where your data is stored so the latency is basically the amount of time to open the DB and actually run the query (plus the time to get to the node where your DB is of course).

I

Isaac McFadyen•3/18/24, 5:28 PM

I believe Turso actually runs on Fly.io Machines so that would explain the spin-up latency.

K

kelbs•3/18/24, 5:29 PM

Gotcha, thank you. Would like to compare query latencies between the two if I can get everything hooked up

IIsaac McFadyen Indexes count for a row written - could that be the reason?

E

Envy•3/18/24, 5:30 PM

Yeah it was the indexes. I forgot I added a bunch of indexes that I really don't need on that table based on the extra cost of all the writes

Also: https://discord.com/channels/595317990191398933/992060581832032316/1211617743082758165

Similar Threads