Thanks I'll look into it. This was an example, the table has 15k rows and it reads only 50 each time

Thanks I'll look into it. This was an example, the table has 15k rows and it reads only 50 each time..

NNikolas Thanks I'll look into it. This was an example, the table has 15k rows and it rea...

Note that this is the same for any row-based relational database: you have to scan the table to find the rows that match the condition.

forslunds•10/23/23, 2:31 PM

Hmm, might save som rows when doing one db per user then

forslunds•10/23/23, 2:31 PM

(Millions per user)

Kevin•10/23/23, 5:56 PM

How do you add and manage foreign key relationships in d1?

NNikolas Thanks I'll look into it. This was an example, the table has 15k rows and it rea...

Michał•10/23/23, 6:49 PM

You can add indices to reduce the number of sequential scans

MMichał You can add indices to reduce the number of sequential scans

NikolasOP•10/23/23, 7:20 PM

Yes I did that and the reads got way down. Thanks

Deleted User 7f983ae1•10/23/23, 7:27 PM

I found out that

auto_increment

auto_increment

in D1 doesn't work

DDeleted User 7f983ae1 I found out that `auto_increment` in D1 doesn't work

Matt Silverlock•10/23/23, 7:30 PM

auto_increment

auto_increment

is not valid and is ignored.

DDeleted User 7f983ae1 I found out that `auto_increment` in D1 doesn't work

Matt Silverlock•10/23/23, 7:30 PM

https://www.sqlite.org/autoinc.html

Deleted User 7f983ae1•10/23/23, 7:31 PM

is it only mysql support it? ok

kian•10/23/23, 7:35 PM

No, it's just that

auto_increment

auto_increment

is the wrong spelling. It should be AUTOINCREMENTAUTOINCREMENT, and is only valid on a INTEGER PRIMARY KEYINTEGER PRIMARY KEY column.

Since INTEGER PRIMARY KEYINTEGER PRIMARY KEY will become an alias for the rowidrowid column by default, it usually isn't needed.

Deleted User 7f983ae1•10/23/23, 7:35 PM

Deleted User 7f983ae1•10/23/23, 7:36 PM

Really? I always learnt auto_increment when I using MySQL

Deleted User 7f983ae1•10/23/23, 7:36 PM

And it work

kian•10/23/23, 7:36 PM

D1 uses SQLite under the hood, so there will be syntax/grammar differences here and there. Different database engines typically have different extensions to the SQL standard, so what works in one place might not work somewhere else.

Deleted User 7f983ae1•10/23/23, 7:36 PM

Deleted User 7f983ae1•10/23/23, 7:37 PM

I will check that specifically and fix it

Deleted User 7f983ae1•10/23/23, 7:37 PM

thank you

Deleted User 7f983ae1•10/23/23, 7:39 PM

(It seem like I have to reset the table to fix it since I set it as pk, so I might just not fix it lol)

KKevin How do you add and manage foreign key relationships in d1?

Kevin•10/23/23, 9:03 PM

Anyone have info on this by chance?

KKevin Anyone have info on this by chance?

Matt Silverlock•10/23/23, 9:06 PM

https://www.sqlite.org/foreignkeys.html

KKevin Anyone have info on this by chance?

joeljuca•10/23/23, 9:14 PM

You might be interested in the docs for the ALTER TABLEALTER TABLE statement in SQLite.

joeljuca•10/23/23, 9:15 PM

The TLDR for FKs and/or table constraints in general in SQLite is: they are pretty much set on table creation. IIUC, if you need to seriously change your table constraints, you'll probably need to recreate the table (eg: CREATE TABLE my_table2CREATE TABLE my_table2, copy all my_tablemy_table. data to my_table2my_table2, and then renaming it to my_tablemy_table)

Kevin•10/23/23, 9:49 PM

Thank you very much!

Zero•10/24/23, 1:22 AM

Are there plans for CDC for D1? stream changes to queues? this will help greatly with integrating systems together

Deleted User 7f983ae1•10/24/23, 8:26 AM

Is it possible to read the D1 with software like DataGrip?

Deleted User 7f983ae1•10/24/23, 9:39 AM

Or possible workaround if it isn't possible then reading it directly on dashboard which only show 10 row per page

Cyb3r-Jak3•10/24/23, 11:49 AM

You can use the REST API to query your database.

EEisenbergEffect Is it possible to connect a local tool like SQLiteStudio to a remote D1 database...

Cyb3r-Jak3•10/24/23, 12:28 PM

Not unless someone has added support for D1 API to the tool.

EEisenbergEffect Another question I have...there seems to be conflicting guidance in the docs on ...

Dahoom•10/24/23, 12:40 PM

both work

Dahoom•10/24/23, 12:41 PM

both do the same thing

Dahoom•10/24/23, 12:42 PM

i think its that way in the docs because the --d1 param was added after the toml method so they used that as an example

Dahoom•10/24/23, 12:42 PM

since d1 was in alpha

Dahoom•10/24/23, 12:44 PM

through a cloudflare worker

Dahoom•10/24/23, 12:45 PM

I run my projects in cloudflare pages so its just as easy as adding 1 parameter, but for you, you might need to create a worker that takes the api calls from your server and routes them to d1 and returns the answer

Dahoom•10/24/23, 12:45 PM

make sure to secure it with an api key or something with the secret variable

Dahoom•10/24/23, 12:48 PM

its already built you'll find it somewhere in the d1 docs

Dahoom•10/24/23, 12:49 PM

if not just look for a public repo, overall the worker should only be like ~5 lines if you dont want to add any validation or anything

CCyb3r-Jak3 You can use the REST API to query your database.

Deleted User 7f983ae1•10/24/23, 2:39 PM

are there any method currently to download the database file? I only saw it in the doc in legacy which only work in alpha release

Deleted User 7f983ae1•10/24/23, 2:39 PM

but not sure for now

GGuillaumeLakano Hi ! The LIMIT in a UPDATE/DELETE request doesn't seems to be supported, it's de...

GuillaumeLakano•10/24/23, 2:44 PM

Is it possible to enable this SQLite configuration option please?
Permit to uses LIMIT on UPDATE and DELETE requests are useful, especially for security reasons

Thanks I'll look into it. This was an example, the table has 15k rows and it reads only 50 each time

Similar Threads