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

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

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

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

there some plugins Ill try

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

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

is there any better solution rather than creating a temp table to hold my data since I'm having multiple tables referencing table B and they holds a lot of records. I've tried another solution as mentioned in: https://github.com/launchbadge/sqlx/issues/2085#issuecomment-1499859906 but it raises an error like this:

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

  D1 runs your SQL in a transaction for you.
  Please export an SQL file from your SQLite database and try again.

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions.

  D1 runs your SQL in a transaction for you.
  Please export an SQL file from your SQLite database and try again.

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

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

This is quite incorrect, I can confirm that the bundled SQLite plugin is available in all Jetbrains IDEs. Not sure why they have this listed in their docs.

IIsaac McFadyen This is quite incorrect, I can confirm that the bundled SQLite plugin is availab...

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

that misleading line is noted in IntelliJ document.

✘ [ERROR] Wrangler could not process the provided SQL file, as it contains several transactions. D1 runs your SQL in a transaction for you. Please export an SQL file from your SQLite database and try again.

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

Similar Threads

Similar Threads

Similar Threads