We have folks using it already via a rather clunky `user asks for a db -> create a new d1 db via api

We have folks using it already via a rather clunky

user asks for a db -> create a new d1 db via api -> update worker script bindings via api

user asks for a db -> create a new d1 db via api -> update worker script bindings via api

workflow, but we'll be streamlining that as much as possible over the coming weeks/months

MMax (@rozenmd)We have folks using it already via a rather clunky `user asks for a db -> create...

RexNormous•3/27/24, 3:35 PM

Interesting... that unfortunately seems like a lot of separate migrations to worry about

My unhelpful 2 cents is that it's be awesome to have selecting db instance be like choosing a Durable Object to interface with, e.g.:

env.DB.getByShardKey(id: string)

env.DB.getByShardKey(id: string)

. Then all DB's under that shard key would share a schema, get the same migrations, and effectively be "grouped' together, if that makes sense.

Original message was deleted

RexNormous•3/27/24, 3:40 PM

This is really awesome - didn't realize this existed.

Picturing my user's sign up flow though, I'd effectively need to pause a user's sign up and go into a "setup phase" for them (when a new deployment kicks off, db is created, etc.), then email them when we've "provisioned" their account. I think this would work if my app was invite-only & in beta, but unfortunately my app is on-demand and self-serve.

Rich•3/27/24, 4:40 PM

Does anybody know if it's possible to read environment variables in a wrangler.toml file?

For context I want to run

wrangler d1 migrations

wrangler d1 migrations

on our CI and would like to read the accountId and db Id from env vars rather than hard coding them in .toml files?

RRich Does anybody know if it's possible to read environment variables in a wrangler.t...

Hello, I’m Allie!•3/27/24, 5:05 PM

You cannot, unless you generate the file dynamically yourself

Mmare arcis In theory would d1 read replicas (when released) be faster than KV coming from a...

Max (@rozenmd)OP•3/28/24, 7:29 AM

KV is architected to support millions of requests per second to a single namespace, D1 is not

Max (@rozenmd)OP•3/28/24, 7:30 AM

(imo) KV would still be faster

MMax (@rozenmd)KV is architected to support millions of requests per second to a single namespa...

Hello, I’m Allie!•3/28/24, 8:16 AM

Could D1 not horizontally replicate in a single location to handle those queries? It wouldn’t be immediate, but it should be possible to scale with demand, no?

Hello, I’m Allie!•3/28/24, 8:21 AM

Or well, actually, can multiple D1 instances connect to a single “stored” DB, assuming they are both in the same colo?

Hello, I’m Allie!•3/28/24, 8:21 AM

If so, spinning up a second read replica should only take a few milliseconds

Hello, I’m Allie!•3/28/24, 8:35 AM

That would be a killer feature against KV, since it would mean the ability to scale to millions/billions of RPS, while having replication time of 1-2 seconds or less(compared with KV's 1 minute+ cacheTtls).

Hello, I’m Allie!•3/28/24, 8:47 AM

And as for the limitations on the size of the DB, if you could shard along the primary key, then you should be able to get a lot more mileage out of it

HHello, I’m Allie!Could D1 not horizontally replicate in a single location to handle those queries...

Max (@rozenmd)OP•3/28/24, 9:02 AM

oh of course - we're just getting started here, I just meant the current state

MMax (@rozenmd)oh of course - we're just getting started here, I just meant the current state

Hello, I’m Allie!•3/28/24, 9:06 AM

Though I'm actually curious, if you are allowed to say, is D1 storage tied to a single instance, or could multiple instances connect to a single stored DB?

HHello, I’m Allie!Though I'm actually curious, if you are allowed to say, is D1 storage tied to a ...

Max (@rozenmd)OP•3/28/24, 9:27 AM

depends what you mean by storage. replicas will work by basically spinning up new DOs with a copy of the db

MMax (@rozenmd)depends what you mean by storage. replicas will work by basically spinning up ne...

Hello, I’m Allie!•3/28/24, 9:31 AM

As in, is the SQLite "file"(I know it is probably not actually a file in the background) tied to a single replica, or can multiple replicas in the same colo open that one "file" without needing to copy it?

Max (@rozenmd)OP•3/28/24, 9:32 AM

err no at the moment I think they need to copy it

Tony Leung•3/28/24, 4:18 PM

hi guys, is there anyway to not cascading other tables when I drop a table?

Tony Leung•3/28/24, 4:19 PM

Eg: I have table A which has a column referenced to table B. When I drop table B, all records from column A are deleted too. Is there any simpler way to not have table A truncated?

TTony Leung Eg: I have table A which has a column referenced to table B. When I drop table B...

Matt Silverlock•3/28/24, 4:32 PM

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

Tony Leung•3/28/24, 4:37 PM

sorry but that does not work... I'm running a migration which requires table B to be modified (need table dropping and recreating). I'm setting

PRAGMA foreign_keys = OFF;

PRAGMA foreign_keys = OFF;

at the beginning and

PRAGMA foreign_keys = ON

PRAGMA foreign_keys = ON

at the end of the migration but it still drops all of my records in table A which reference table B are deleted.

Matt Silverlock•3/28/24, 4:41 PM

You need to share more about what you're doing here. Specifically, see:

ON CASCADE UPDATE/DELETE: https://www.sqlite.org/foreignkeys.html#fk_actions
How foreign keys interact with
```
DROP TABLE
```
```
DROP TABLE
```
commands: https://www.sqlite.org/foreignkeys.html#fk_schemacommands

MMatt Silverlock You need to share more about what you're doing here. Specifically, see: * ON CAS...

Tony Leung•3/28/24, 4:46 PM

Sorry

I forgot to mention my scenario:

Table A has column

b_id

b_id

which reference table B column

id

id

ON DELETE CASCADE ON UPDATE CASCADE

ON DELETE CASCADE ON UPDATE CASCADE

In my migration file, I'm trying to do a workaround like this:

PRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;

but all of my records in table A is cascaded

TTony Leung Sorry 😦 I forgot to mention my scenario: - Table A has column `b_id` which refe...

Matt Silverlock•3/28/24, 4:50 PM

It's not clear to me that disabling fkeys works (and you can't do that in D1 anyway):

If foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point. Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.

Try

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

(I have not tested this) - although I expect it will still fail as you still have violated fkey constraints when the transaction goes to complete.

Original message was deleted

Max (@rozenmd)OP•3/28/24, 4:57 PM

delete and recreate is probably the cheapest

MMatt Silverlock It's not clear to me that disabling fkeys works (and you can't do that in D1 any...

Tony Leung•3/28/24, 4:58 PM

yeah it does not work either

Tony Leung•3/28/24, 4:59 PM

there is an issue which i found which has the same problem as me: https://github.com/launchbadge/sqlx/issues/2085

GitHub

Can't do SQLite migrations involving temporarily disabling foreign ...

I'm trying to do a migration involving adding a constraint to a field within a table that is referenced as a foreign key by other tables in SQLite. As you might know, SQLite's ALTER syntax ...

Tony Leung•3/28/24, 5:00 PM

if it does not work then I guess I have to create a temp table to store data from the child table, which is not ideal for large table

Tony Leung•3/28/24, 5:00 PM

i guess modifying the table on the fly is not the best practice for sqlite

TTony Leung there is an issue which i found which has the same problem as me: https://github...

Matt Silverlock•3/28/24, 5:02 PM

The second comment says: https://github.com/launchbadge/sqlx/issues/2085#issuecomment-1237474188

follow up: it seems like bracketing it with PRAGMA defer_foreign_keys = ON; and PRAGMA defer_foreign_keys = OFF; does make it work. I am not familiar enough with arcane SQLite stuff to give a super informed view on whether this should be automatically applied to every migration, but I suspect it might be a good idea - my understanding is that the foreign key constraints will still be checked, but just at the time that the transaction is committed.

MMatt Silverlock The second comment says: https://github.com/launchbadge/sqlx/issues/2085#issueco...

Tony Leung•3/28/24, 5:06 PM

I've tried bracketing with

PRAGMA defer_foreign_keys = ON

PRAGMA defer_foreign_keys = ON

PRAGMA defer_foreign_keys = OFF

PRAGMA defer_foreign_keys = OFF

but it does not work either...

Tony Leung•3/28/24, 5:08 PM

do i still need PRAGMA foreign_keys = OFF;?

TTony Leung I've tried bracketing with `PRAGMA defer_foreign_keys = ON`/`PRAGMA defer_foreig...

Matt Silverlock•3/28/24, 5:08 PM

What does “not work” mean - it does nothing? Errors?

Tony Leung•3/28/24, 5:10 PM

I'm sorry, I mean after bracketing the migration with

PRAGMA defer_foreign_keys

PRAGMA defer_foreign_keys

, Table B was modified and my table A are cascaded without any errors like before

JoeDoe•3/28/24, 5:16 PM

Is there a visual explorer for local databases?

JoeDoe•3/28/24, 5:17 PM

something like d1-manager

JJoeDoe Is there a visual explorer for local databases?

Tony Leung•3/28/24, 5:17 PM

the database is stored under

.wrangler/state/v3/d1

.wrangler/state/v3/d1

. It's a sqlite file, you could explore or even modify it using your favorite tool

Tony Leung•3/28/24, 5:18 PM

I'm using sqlite viewer extension in vscode (which does not allows modification)

JoeDoe•3/28/24, 5:19 PM

ah ok perfect thank you

Tony Leung•3/28/24, 5:19 PM

you could use any more advanced tool like datagrip

JoeDoe•3/28/24, 5:19 PM

Ill find something in intellij

JoeDoe•3/28/24, 5:19 PM

some plugin

Tony Leung•3/28/24, 5:19 PM

IntelliJ or any Jetbrains IDE has database plugin bundled

Tony Leung•3/28/24, 5:20 PM

it's a mini version of datagrip

JJoeDoe some plugin

Tony Leung•3/28/24, 5:21 PM

Here, if you use intelliJ ultimate then it's bundled
https://www.jetbrains.com/help/idea/relational-databases.html#first-steps

JoeDoe•3/28/24, 5:21 PM

The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate.

The Database Tools and SQL plugin is available only in IntelliJ IDEA Ultimate.

JoeDoe•3/28/24, 5:21 PM

Tony Leung•3/28/24, 5:22 PM

you could use dbeaver, it's opensource

We have folks using it already via a rather clunky `user asks for a db -> create a new d1 db via api

Similar Threads