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

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 SilverlockOP•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 SilverlockOP•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.

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

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

if I do this

    async scheduled(event, env, ctx) {
        ctx.waitUntil(doSomeTaskOnASchedule(env, ctx));
                ctx.waitUntil(theOtherTask(env, ctx));
    }

    async scheduled(event, env, ctx) {
        ctx.waitUntil(doSomeTaskOnASchedule(env, ctx));
                ctx.waitUntil(theOtherTask(env, ctx));
    }

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

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

Similar Threads

Similar Threads

Similar Threads