I'm having trouble getting foreign key constraints to work. I know with SQLite you need to run `PRAG

Mmicromashor I'm having trouble getting foreign key constraints to work. I know with SQLite y...

L

Lucian•7/20/23, 5:31 PM

They worked for me. What makes you think they didn't work for you and how were they created? I think in sqlite you can only create them within CREATE TABLE and not with ALTER TABLE ADD CONSTRAINT .

LLucian They worked for me. What makes you think they didn't work for you and how were t...

M

micromashorOP•7/20/23, 5:38 PM

Yes, all of them were created within the CREATE TABLE statements. But the DB happily accepts garbage data which should be caught by foreign key constraints. Like I said, I couldn't even get the pragma turned on to begin with.

Mmicromashor Yes, all of them were created within the CREATE TABLE statements. But the DB hap...

L

Lucian•7/20/23, 5:58 PM

Hmm, to be precise the foreign key constraints screamed at me when trying to drop and recreate a table (referenced by ) but I never really tried to add data violating the constraints. So my anecdotal example might not be relevant to you

LLucian Hmm, to be precise the foreign key constraints screamed at me when trying to dro...

M

micromashorOP•7/20/23, 6:14 PM

Were you using the alpha backend? One difference I just found is that for some reason, the alpha backend defaults to

foreign_keys=1

foreign_keys=1

foreign_keys=1

foreign_keys=1, while the beta backend defaults to

foreign_keys=0

foreign_keys=0

foreign_keys=0

foreign_keys=0.

M

micromashorOP•7/20/23, 6:26 PM

actually that means that foreign keys work exactly as intended on the alpha backend, which means that the only thing left to figure out is how to set

foreign_keys=1

foreign_keys=1

foreign_keys=1

foreign_keys=1 on the beta backend. It's tricky because that's per-SQLite connection, but the D1 API doesn't have a concept of a connection

Mmicromashor Were you using the alpha backend? One difference I just found is that for some r...

L

Lucian•7/20/23, 6:40 PM

Yes, I was on the alpha backend

Mmicromashor actually that means that foreign keys work exactly as intended on the alpha back...

L

Lucian•7/20/23, 6:42 PM

I wonder if you can use a db.batch in your worker and have your pragma foreign key as the first statement

LLucian I wonder if you can use a db.batch in your worker and have your pragma foreign k...

M

micromashorOP•7/20/23, 7:21 PM

I was going to try a workaround like that, but it's still not ideal because I can't use prepared statements that way

M

micromashorOP•7/20/23, 7:22 PM

CF must have somehow set foreign_key default on for the alpha backend, so it'd be really nice if they could expose an option to allow that same behavior for the beta backend

M

micromashorOP•7/20/23, 7:23 PM

If I have some extra time this afternoon I might file an issue on the github repo

K

kian•7/20/23, 7:43 PM

foreign key default is set on the experimental backend meowthinking

K

kian•7/20/23, 7:44 PM

or it might be soon

K

kian•7/20/23, 7:44 PM

it was committed to workerd 2 weeks ago so it'll be in the next release if it hasn't already released

Kkian foreign key default is set on the experimental backend <:meowthinking:3506809418...

M

Matt Silverlock•7/20/23, 8:15 PM

Right, for the experimental backend:

all queries run inside a transaction
You can’t enable foreign keys via the PRAGMA inside that context
We are making foreign key enforcement the default
You can relax it with
```
PRAGMA defer_foreign_keys = on
```
PRAGMA defer_foreign_keys = on
```
PRAGMA defer_foreign_keys = on
```
PRAGMA defer_foreign_keys = on
Docs coming very very soon

MMatt Silverlock Right, for the experimental backend: 1. all queries run inside a transaction 2....

M

micromashorOP•7/20/23, 8:17 PM

Thank you! I'm glad this is on the radar and coming soon.

Mmicromashor Thank you! I'm glad this is on the radar and coming soon.

M

Matt Silverlock•7/20/23, 8:39 PM

WIP docs: https://silverlock-d1-foreign-keys.cloudflare-docs-7ou.pages.dev/d1/learning/foreign-keys/

Need to write up FK actions, add basic schema examples (how to define fkeys between tables), and how to handle errors/resolve FK issues - before I publish it proper.

Original message was deleted

M

Max (@rozenmd)•7/21/23, 2:54 PM

just realised we pretty much never actually want to set a default value in the form based on the field's sql default value, since it needs to be NULL for sqlite to use the default value

B

Braden•7/22/23, 12:14 AM

Looking to integrate honeycomb.io with d1 to get traces of db queries in my logs. Anyone already done something like this?

T

Tony Leung•7/22/23, 1:27 PM

hi guys, I'm having two table

document

document

and

document_type

document_type

document_type

document_type and

document

document

has a foreign key of

document_type

document_type

document_type

document_type.

document

document

is a relatively large table and

document_type

document_type

document_type

document_type has 10 or 20 records. What should I do when I want to get data from both tables, using JOIN or SELECT two times and use code to get the final data?

TTony Leung hi guys, I'm having two table `document` and `document_type` and `document` has ...

L

Lucian•7/22/23, 3:24 PM

I say use a JOIN, that is what it's intended for. No need to JOIN datasets "in code", databases engines are optimized to handle joins efficiently.

LLucian I say use a JOIN, that is what it's intended for. No need to JOIN datasets "in ...

T

Tony Leung•7/22/23, 3:58 PM

Thank you! Is this applied to SQLite? I'm using D1 btw

T

Tony Leung•7/22/23, 4:48 PM

Thank you! I'm kinda new with SQLite

A

Ashley•7/22/23, 9:36 PM

I know read replicas are coming soon, so assume currently there is just a leader being created, but what happens if the DC the leader is in goes down? I assume right now the DB would be inaccessible... but in future, would a new leader be elected in the event a region/DC goes down where a D1 database's leader is?

AAshley I know read replicas are coming soon, so assume currently there is just a leader...

M

Matt Silverlock•7/23/23, 7:02 AM

This is already the case. We run the leader from another data center in the same region implicitly.

MMatt Silverlock This is already the case. We run the leader from another data center in the same...

A

Ashley•7/23/23, 8:51 AM

Awesome, thank you!

VViper How do we connect to D1 using pages or A Nodejs framework like Nextjs?

M

Matt Silverlock•7/23/23, 9:30 AM

https://developers.cloudflare.com/pages/framework-guides/ and https://developers.cloudflare.com/d1/examples/

D1 is just like KV

Framework guides · Cloudflare Pages docs

Documentation for Cloudflare Pages, the best way to deploy your static and JAMstack sites

Examples · Cloudflare D1 docs

Query your D1 database from a Remix application.

VViper I don't get it, how would you access the database without the wrangler.toml file...

M

Matt Silverlock•7/23/23, 10:40 AM

The guides show you how to bind a database using Pages.

VViper I don't get it, how would you access the database without the wrangler.toml file...

M

Matt Silverlock•7/23/23, 10:40 AM

(I strongly suggest following one of the Pages tutorials first)

VViper I don't think it works the same way with Nextjs I tried setting my database in t...

C

Cyb3r-Jak3•7/23/23, 1:10 PM

Are you passing a D1 binding with your pages dev command?

C

chientrm.com•7/23/23, 2:39 PM

should I switch to new D1 backend?

C

chientrm.com•7/23/23, 2:39 PM

it doesn't have back up yet

M

Mozzy•7/23/23, 3:06 PM

Screw backups, live on the Edge (of your seat)

Cchientrm.com it doesn't have back up yet

C

Cyb3r-Jak3•7/23/23, 3:26 PM

Time travel is available d1-database

H

Hyper•7/23/23, 7:58 PM

i switched my production database to d1 and were finally live on the edge (of my seat)

M

MrJeeves•7/23/23, 10:45 PM

hmm... this doesn't seem right...

trying to use db within a pages function works locally but dead in prod for me. anything im missing?

M

MrJeeves•7/23/23, 10:46 PM

i did forget to add the binding, but also did a new deploy after adding it to preview env

W

Walshy•7/23/23, 10:49 PM

what does "dead" mean in this context?

Are you trying to use it in a prod deploy? If so, you'll want it in the Production tab too

M

MrJeeves•7/23/23, 10:50 PM

umm no was just missing the binding in context, but turns out i was still using the last deploy's url

W

Walshy•7/23/23, 10:50 PM

haha that'll do it

M

MrJeeves•7/23/23, 10:50 PM

completely user error

A

ajgeiss0702•7/24/23, 3:04 AM

I've been experimenting with D1 on and off now for a few months. A few months ago I had set up a database, and it was working perfectly fine. I took a few months off of that project and worked on some other things. I've now come back to that project with D1 and now this request to D1 is failing and im not sure why. Any ideas?

  (error) Error: D1_ERROR
    at D1Database._send (functionsWorker-0.5813307969025092.js:11813:13)
    at async D1PreparedStatement.first (functionsWorker-0.5813307969025092.js:11858:7)
    at async default (functionsWorker-0.5813307969025092.js:6789:45)
    at async handle_action_json_request (functionsWorker-0.5813307969025092.js:8923:19)
    at async resolve (functionsWorker-0.5813307969025092.js:11347:22)
    at async Object.handle (functionsWorker-0.5813307969025092.js:389:24)
    at async respond (functionsWorker-0.5813307969025092.js:11243:22)
    at async Object.fetch (functionsWorker-0.5813307969025092.js:11715:13)

  (error) Error: D1_ERROR
    at D1Database._send (functionsWorker-0.5813307969025092.js:11813:13)
    at async D1PreparedStatement.first (functionsWorker-0.5813307969025092.js:11858:7)
    at async default (functionsWorker-0.5813307969025092.js:6789:45)
    at async handle_action_json_request (functionsWorker-0.5813307969025092.js:8923:19)
    at async resolve (functionsWorker-0.5813307969025092.js:11347:22)
    at async Object.handle (functionsWorker-0.5813307969025092.js:389:24)
    at async respond (functionsWorker-0.5813307969025092.js:11243:22)
    at async Object.fetch (functionsWorker-0.5813307969025092.js:11715:13)

  (error) Error: D1_ERROR
    at D1Database._send (functionsWorker-0.5813307969025092.js:11813:13)
    at async D1PreparedStatement.first (functionsWorker-0.5813307969025092.js:11858:7)
    at async default (functionsWorker-0.5813307969025092.js:6789:45)
    at async handle_action_json_request (functionsWorker-0.5813307969025092.js:8923:19)
    at async resolve (functionsWorker-0.5813307969025092.js:11347:22)
    at async Object.handle (functionsWorker-0.5813307969025092.js:389:24)
    at async respond (functionsWorker-0.5813307969025092.js:11243:22)
    at async Object.fetch (functionsWorker-0.5813307969025092.js:11715:13)

  (error) Error: D1_ERROR
    at D1Database._send (functionsWorker-0.5813307969025092.js:11813:13)
    at async D1PreparedStatement.first (functionsWorker-0.5813307969025092.js:11858:7)
    at async default (functionsWorker-0.5813307969025092.js:6789:45)
    at async handle_action_json_request (functionsWorker-0.5813307969025092.js:8923:19)
    at async resolve (functionsWorker-0.5813307969025092.js:11347:22)
    at async Object.handle (functionsWorker-0.5813307969025092.js:389:24)
    at async respond (functionsWorker-0.5813307969025092.js:11243:22)
    at async Object.fetch (functionsWorker-0.5813307969025092.js:11715:13)

A

ajgeiss0702•7/24/23, 3:06 AM

nevermind. turned out to be a structure migration that replaced something that it shouldnt have, causing an invalid SQL statement. Would be nice to have better errors though (at least an "invalid SQL statement" would have turned this from a 5 minute investigation to about 30 seconds

)

VViper No I set it thru the site

C

Cyb3r-Jak3•7/24/23, 12:09 PM

That only works in production. From the docs

Pass the --d1=BINDING_NAME flag when developing locally. BINDING_NAME should match what call in your code

I'm having trouble getting foreign key constraints to work. I know with SQLite you need to run `PRAG

Similar Threads

Similar Threads

Similar Threads