Does D1 implement `ON UPDATE RESTRICT`, to prevent updates from happening if there's any foreign key

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

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

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

Mmare arcis Is D1 effectively limited to 32000 insertions per worker invocation? 1000 subreq...

M

Max (@rozenmd)•3/19/24, 6:47 AM

what's your use case?

NNicolas Hi all, So I am trying to see how I can use D1 in a multi-tenant fashion. Su...

D

digendee•3/19/24, 9:23 AM

Hi Nick, did you get a reply about this, or figure anything out about it on your own?

B

Billy•3/19/24, 12:21 PM

I'm trying to get my local d1 database from one local project to another local project, i.e., from one dir to another dir without re-running the

wrangler d1 execute --local --file=.sql

wrangler d1 execute --local --file=.sql

wrangler d1 execute --local --file=.sql

wrangler d1 execute --local --file=.sql approach. I thought I could just move the

.wrangler/state/v3/d1

.wrangler/state/v3/d1

.wrangler/state/v3/d1

.wrangler/state/v3/d1 dir over to another dir and spin things up but that did not work. Is there some other approach I could use?

My problem is that I use a separate worker to load portions of the database and I was hoping to avoid moving that worker and its config over to another dir temporarily to get the database fully loaded.

Thanks!

BBilly I'm trying to get my local d1 database from one local project to another local p...

H

Hello, I’m Allie!•3/19/24, 12:22 PM

Just to confirm, do both projects have the same

database_id

database_id

database_id

database_id &

database_name

database_name

database_name

database_name?

BBilly I'm trying to get my local d1 database from one local project to another local p...

M

Max (@rozenmd)•3/19/24, 12:23 PM

if you use --persist-to I think both Workers can talk to the same local db

HHello, I’m Allie!Just to confirm, do both projects have the same `database_id` & `database_name`?

B

Billy•3/19/24, 12:27 PM

yes they do

MMax (@rozenmd)if you use --persist-to I think both Workers can talk to the same local db

B

Billy•3/19/24, 12:27 PM

that's a good idea, I'll give that shot. Thanks

B

Billy•3/19/24, 12:29 PM

just to confirm, there's no way to dump the local db into a .sql file, correct?

BBilly just to confirm, there's no way to dump the local db into a .sql file, correct?

M

Max (@rozenmd)•3/19/24, 12:29 PM

locally if you have the sqlite3 binary you can open the

.sqlite

.sqlite

.sqlite

.sqlite file and run

.dump

.dump

.dump

.dump

BBilly just to confirm, there's no way to dump the local db into a .sql file, correct?

M

Max (@rozenmd)•3/19/24, 12:30 PM

https://developers.cloudflare.com/d1/build-with-d1/import-data/#convert-sqlite-database-files

MMax (@rozenmd)locally if you have the sqlite3 binary you can open the `.sqlite` file and run `...

B

Billy•3/19/24, 12:51 PM

Right, I need the database from D1 since it's different than the DB made from the sqlite file

BBilly Right, I need the database from D1 since it's different than the DB made from th...

H

Hello, I’m Allie!•3/19/24, 12:52 PM

The D1 Database is the

.sqlite

.sqlite

.sqlite

.sqlite file, unless you are referring to a remote D1 Database?

HHello, I’m Allie!The D1 Database **is** the `.sqlite` file, unless you are referring to a remote ...

B

Billy•3/19/24, 12:54 PM

Initially that is true but updates are made to the D1. I was hoping to get the updated DB from D1 out into a .sql

B

Billy•3/19/24, 12:55 PM

This is all local btw. I'd love the ability to get the remote data pulled down into a . sql as well if that were possible

B

Billy•3/19/24, 12:56 PM

The docs state there's no way to get a backup per se

BBilly Initially that is true but updates are made to the D1. I was hoping to get the u...

H

Hello, I’m Allie!•3/19/24, 12:56 PM

When those updates are made, they are made to the

.sqlite

.sqlite

.sqlite

.sqlite file. D1 in local development is just a wrapper around that

.sqlite

.sqlite

.sqlite

.sqlite file

BBilly This is all local btw. I'd love the ability to get the remote data pulled down ...

H

Hello, I’m Allie!•3/19/24, 12:56 PM

To dump a remote database, you can use https://github.com/nora-soderlund/cloudflare-d1-backups

GitHub

GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a ...

A package to dump a copy of your experimental D1 database through a worker. - nora-soderlund/cloudflare-d1-backups

HHello, I’m Allie!When those updates are made, they are made to the `.sqlite` file. D1 in local de...

B

Billy•3/19/24, 12:59 PM

so I can find that sqlite file in the

.wrangler

.wrangler

directory?

BBilly so I can find that sqlite file in the `.wrangler` directory?

H

Hello, I’m Allie!•3/19/24, 1:00 PM

That is correct

HHello, I’m Allie!That is correct

B

Billy•3/19/24, 1:01 PM

gotcha, I'll open each of those and see which one has the updates and then I can dump it for sure. Thank you!

B

Billy•3/19/24, 1:33 PM

Thanks for all the help, I'm rolling again locally. Have an awesome week

M

Matt Silverlock•3/19/24, 3:49 PM

The wonderful @gift-egwuenu built a full-stack demo application using D1, HonoX, and Cloudflare Pages called Staff Directory: https://github.com/lauragift21/staff-directory

GitHub

GitHub - lauragift21/staff-directory: an intuitive resource hub des...

an intuitive resource hub designed to showcase the teams within an organization. - lauragift21/staff-directory

MMatt Silverlock The wonderful @gift-egwuenu built a full-stack demo application using D1, HonoX,...

M

Matt Silverlock•3/19/24, 3:50 PM

You can see it live here: https://staff-directory-4to.pages.dev/

and the D1 specific / database handlers defined here: https://github.com/lauragift21/staff-directory/blob/main/app/db.ts

Mmare arcis Use a worker to import property listings into D1 from central source on a cadenc...

M

Max (@rozenmd)•3/19/24, 8:48 PM

are you using db.batch?

T

trogdor•3/20/24, 12:22 AM

Is

db.exec

db.exec

db.exec

db.exec disabled inside pages functions? Works fine locally with wrangler, but not after deployed.

Does D1 implement `ON UPDATE RESTRICT`, to prevent updates from happening if there's any foreign key

Similar Threads

Similar Threads

Similar Threads