Is there a way to disable foreign key constraints temporarily?

PPaul Is there a way to disable foreign key constraints temporarily?

M

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

PRAGMA defer_foreign_keys = on

IIsaac McFadyen Sorry 😅

S

slagathor•10/30/23, 9:15 PM

Don't be sorry! You've helped me out so much!

S

slagathor•10/30/23, 9:16 PM

Please don't take that "buggy" comment the wrong way. I totally understand how difficult it is to maintain projects.

S

slagathor•10/30/23, 9:16 PM

You've truly been a lifesaver!

Sslagathor Please don't take that "buggy" comment the wrong way. I totally understand how d...

I

Isaac McFadyen•10/30/23, 9:55 PM

Haha, no it actually is pretty buggy, I understand that lul

I

Isaac McFadyen•10/30/23, 9:55 PM

Thank you though, that means a lot

Ttforster I did, but I didn't see any information on connecting to and/or using the D1 cli...

W

windwp•10/30/23, 11:28 PM

i use my library to unit test on local with better sqlite https://github.com/windwp/kysely-zod-sqlite

GitHub

GitHub - windwp/kysely-zod-sqlite: An flexible api for Cloudflare D...

An flexible api for Cloudflare D1 and sqlite. It has an simple api of Prisma and a powerful query with Kysely. - GitHub - windwp/kysely-zod-sqlite: An flexible api for Cloudflare D1 and sqlite. It ...

D

dinesh_karuppasamy•10/31/23, 12:12 AM

Can anyone help to resolve this ? I can able to migrate and push the changes to DB by using drizzle-orm. Whereas I am getting this error while pulling data through SQL query

D

dinesh_karuppasamy•10/31/23, 12:12 AM

[mf:err] Error: D1_ERROR: no such table: users
at D1Database._sendOrThrow (cloudflare-internal:d1-api:66:19)
at async D1PreparedStatement.raw (cloudflare-internal:d1-api:178:32)

Ddinesh_karuppasamy Can anyone help to resolve this ? I can able to migrate and push the changes to ...

C

Cyb3r-Jak3•10/31/23, 12:26 AM

Did you apply the migrations to either remote or local, depending on your environment

CCyb3r-Jak3 Did you apply the migrations to either remote or local, depending on your enviro...

D

dinesh_karuppasamy•10/31/23, 12:28 AM

Yes I applied remote. After applying migrations, table was created. But querying either select or insert is not working by using await env.DB.prepare or exec

Ddinesh_karuppasamy Yes I applied remote. After applying migrations, table was created. But querying...

C

Cyb3r-Jak3•10/31/23, 12:41 AM

Where are you running the exec via wrangler dev?

CCyb3r-Jak3 Where are you running the exec via wrangler dev?

D

dinesh_karuppasamy•10/31/23, 1:05 AM

yes running via wrangler dev

Ddinesh_karuppasamy yes running via wrangler dev

C

Cyb3r-Jak3•10/31/23, 1:13 AM

Did the you also apply the migrations locally? Wrangler dev by default uses local mode which doesn’t use any remote data.

CCyb3r-Jak3 Did the you also apply the migrations locally? Wrangler dev by default uses loca...

D

dinesh_karuppasamy•10/31/23, 1:16 AM

okay thanks. Let me check again.

P

Paul Shriner•10/31/23, 12:10 PM

D1 question and I think that I know the answer, I would like to sort by similarity of two strings. In a traditional enviornment I think I would use editdist3 which seems like the goto in SQLite, but I don't see it support in D1... Did I miss something?

K

kian•10/31/23, 12:18 PM

The spellfix1 virtual table is not included in the SQLite amalgamation and is not a part of any standard SQLite build. It is a loadable extension.

K

kian•10/31/23, 12:18 PM

It's a non-standard extension so it won't be included in D1 currently.

P

Paul Shriner•10/31/23, 12:24 PM

that is what I figured, but was hoping

P

Paul Shriner•10/31/23, 12:26 PM

Basically what I am trying to do is take a comma delimited list of sorted numbers say [1,2,4,7] and find the record most closely match [1,2,7] .

I think that I can do it by flatteing the column into fields, but It won't be simple to read nor will it be performant.

So I would take ideas

P

Paul Shriner•10/31/23, 12:32 PM

Actually maybe not horrible iwth SQL math

K

kian•10/31/23, 2:38 PM

// TODO(someday): Allow temporary tables. Creating a temporary table actually causes
//   SQLite to open a separate temporary file to place the data in. Currently, our storage
//   engine has no support for this.

// TODO(someday): Allow temporary tables. Creating a temporary table actually causes
//   SQLite to open a separate temporary file to place the data in. Currently, our storage
//   engine has no support for this.

// TODO(someday): Allow temporary tables. Creating a temporary table actually causes
//   SQLite to open a separate temporary file to place the data in. Currently, our storage
//   engine has no support for this.

// TODO(someday): Allow temporary tables. Creating a temporary table actually causes
//   SQLite to open a separate temporary file to place the data in. Currently, our storage
//   engine has no support for this.

K

kian•10/31/23, 2:38 PM

The new storage backend doesn't support temporary tables.

K

kian•10/31/23, 2:58 PM

They fall under the same comment as far as I can see

K

kian•10/31/23, 3:07 PM

sqlite3CreateView

sqlite3CreateView

sqlite3CreateView

sqlite3CreateView calls

sqlite3StartTable

sqlite3StartTable

sqlite3StartTable

sqlite3StartTable so it would follow the same path of attempting to create a temporary file - that's my understanding

K

kian•10/31/23, 3:14 PM

Creating a temporary view w/

PRAGMA query_only = 1

PRAGMA query_only = 1

PRAGMA query_only = 1

PRAGMA query_only = 1 results in

attempt to write a readonly database (8)

attempt to write a readonly database (8)

attempt to write a readonly database (8)

attempt to write a readonly database (8) but you might be right, I can't say I've ever really used them before.

K

kian•10/31/23, 3:16 PM

Omitting

TEMP

TEMP

TEMP

TEMP from

CREATE VIEW

CREATE VIEW

CREATE VIEW

CREATE VIEW just moves it from the

main

main

database to

temp

temp

temp

temp as far as I can see, so I assume it's still trying to write something at the least - of which, would be on a temporary database file.

K

kian•10/31/23, 3:18 PM

The edge runtime supposedly sets

temp_store

temp_store

temp_store

temp_store to

MEMORY

MEMORY

MEMORY

MEMORY, which conflicts with the comment - so there might be a quirk in SQLite or D1.

J

JustinNoel•10/31/23, 3:21 PM

I just migrated my project from a Planetscale database to D1 yesterday.

It was super easy and improved my page loads times thanks to lower latency.

Thanks so much to everyone on the CF D1 team!

P

PaulOP•10/31/23, 3:22 PM

The docs for time travel are a bit confusing.
Does it create a new snapshot when I run this:

wrangler d1 time-travel info YOUR_DATABASE

wrangler d1 time-travel info YOUR_DATABASE

wrangler d1 time-travel info YOUR_DATABASE

wrangler d1 time-travel info YOUR_DATABASE ?

Also, how do I get a list of previous snapshots so I can restore to one?

P

PaulOP•10/31/23, 3:24 PM

Ah I think i get it. Because it's happening every minute for the last 30 days, i can just supply it with any timestamp?

PPaul Ah I think i get it. Because it's happening every minute for the last 30 days, i...

H

Hello, I’m Allie!•10/31/23, 3:25 PM

You need the ID of the backup first:

$ wrangler d1 backup download example-db 123a81a2-ab91-4c2e-8ebc-64d69633faf1

$ wrangler d1 backup download example-db 123a81a2-ab91-4c2e-8ebc-64d69633faf1

$ wrangler d1 backup download example-db 123a81a2-ab91-4c2e-8ebc-64d69633faf1

$ wrangler d1 backup download example-db 123a81a2-ab91-4c2e-8ebc-64d69633faf1

P

PaulOP•10/31/23, 3:26 PM

I don't think the

backup

backup

backup

backup works anymore on beta?

H

Hello, I’m Allie!•10/31/23, 3:26 PM

Oh wait

H

Hello, I’m Allie!•10/31/23, 3:26 PM

Ur right

PPaul I don't think the `backup` works anymore on beta?

H

Hello, I’m Allie!•10/31/23, 3:27 PM

Yeah, then what you said should work fine

P

PaulOP•10/31/23, 3:27 PM

which is honestly a bit concering, i'd like to be able to easily export my entire database as standard sql

P

PaulOP•10/31/23, 3:27 PM

i feel like time-travel is a form of lock-in

P

PaulOP•10/31/23, 3:28 PM

it's an excellent feature for sure, but it's still very important for me to be able to down the entirety of my database, so I can load it up in a local client and play with it

PPaul it's an excellent feature for sure, but it's still very important for me to be a...

H

Hello, I’m Allie!•10/31/23, 3:28 PM

I think this falls under the

beta

beta

beta

beta banner. Afaik they do still intend to have a way to dump the DB at some point

HHello, I’m Allie!I think this falls under the `beta` banner. Afaik they do still intend to have a...

P

PaulOP•10/31/23, 3:29 PM

Oh excellent.

PPaul Oh excellent.

D

Dahoom•10/31/23, 5:17 PM

https://github.com/nora-soderlund/cloudflare-d1-backups in the meantime you can use this

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. - GitHub - nora-soderlund/cloudflare-d1-backups: A package to dump a copy of your experimental D1 database through a worker.

D

Dahoom•10/31/23, 5:19 PM

There should be a discord bot that scans for keywords like "export" and "dump" in this channel and automatically posts this url

Original message was deleted

M

Max (@rozenmd)•10/31/23, 8:52 PM

was on holiday, will get on it

Is there a way to disable foreign key constraints temporarily?

Similar Threads

Similar Threads

Similar Threads