Sorry 😦 I forgot to mention my scenario: - Table A has column `b_id` which reference table B column

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;

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 LeungOP•3/28/24, 4:58 PM

yeah it does not work either

Tony LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•3/28/24, 5:19 PM

IntelliJ or any Jetbrains IDE has database plugin bundled

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

it's a mini version of datagrip

JJoeDoe some plugin

Tony LeungOP•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 LeungOP•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 LeungOP•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 LeungOP•3/28/24, 5:33 PM

that misleading line is noted in IntelliJ document.

TTony Leung that misleading line is noted in IntelliJ document.

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

Yeah I know. I'm unsure why it's noted in there.

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

The Datagrip integration is available in all IDEs so not sure why they noted it.

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

Maybe it's for a more advanced version? As far as I know they're all identical though...

IIsaac McFadyen Yeah I know. I'm unsure why it's noted in there.

Tony LeungOP•3/28/24, 5:36 PM

to boost intelliJ selling I guess? A sql console is more than enough for casual use to me. Maybe the more advanced database like dynamo, snowflake is not supported?

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

Possibly? Unsure.

Max (@rozenmd)•3/28/24, 5:44 PM

Early release, but if you want to try an unoptimized

wrangler d1 export

wrangler d1 export

: https://github.com/cloudflare/workers-sdk/pull/5425

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

idk if I'm doing it wrong, but the most popular plugin on intellij didn't work for me

JJoeDoe idk if I'm doing it wrong, but the most popular plugin on intellij didn't work f...

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

It shouldn't be a plugin.

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

It should be built-in to the IDE itself.

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

webstorm, not ultimate

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

For example, in RustRover it's the little Database icon.

JJoeDoe webstorm, not ultimate

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

Yeah. All IDEs have it I think

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

I don't have WebStorm downloaded right now but I remember seeing it in there too.

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

It might be a hidden button that you need to find in the More tool windows section.

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

its ok

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

Ill manage for now

JoeDoe•3/28/24, 6:10 PM

Is there a way to run a script that will still have access/context of the cloudflare bindings

JoeDoe•3/28/24, 6:11 PM

because right now, the only way I can see to do this is to trigger the exection via fetch()

JJoeDoe Is there a way to run a script that will still have access/context of the cloudf...

Max (@rozenmd)•3/28/24, 6:13 PM

outside of Cloudflare Workers, you mean?

Max (@rozenmd)•3/28/24, 6:14 PM

there's https://developers.cloudflare.com/api/operations/cloudflare-d1-query-database

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

IIsaac McFadyen I don't have WebStorm downloaded right now but I remember seeing it in there too...

kian•3/28/24, 6:16 PM

WebStorm is the 1 IDE that doesn’t

✘ [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.

Sorry 😦 I forgot to mention my scenario: - Table A has column `b_id` which reference table B column

Similar Threads

Similar Threads

Similar Threads