Cloudflare Developers•3y ago

A more pressing question is why you would use the text format when saving only integers?

DDahoom try to convert the value to a string before inserting it, at least thats what gi...

captain•10/27/23, 5:21 AM

that worked..i feel foolish. Probably still should be considered a bug with D1, but the workaround worked!

DDahoom A more pressing question is why you would use the text format when saving only i...

captain•10/27/23, 5:21 AM

sometimes it might not be an integer

DahoomOP•10/27/23, 5:22 AM

Trust me I had the same idea, maybe later i would want to switch to nanoid's or something but honestly this thought process is more destructive than helpful

captain•10/27/23, 5:23 AM

in this scenario, i'm converting stuff over from a KV model

captain•10/27/23, 5:23 AM

and so the key might be a number from one source, but another source sometimes puts in a letter combo

Ccaptain that worked..i feel foolish. Probably still should be considered a bug with D1, ...

DahoomOP•10/27/23, 5:23 AM

Not a bug, not even an issue with D1. Its just how sqlite works when inserting int as text

Ccaptain and so the key might be a number from one source, but another source sometimes p...

DahoomOP•10/27/23, 5:27 AM

you are working with primary keys so they have to be unique for each row. what you need is a foriegn key in another column that points to ids from other tables/sources and this way you can easily create constraints and save yourself a lot of headache

DahoomOP•10/27/23, 5:28 AM

I recommend using gippity to create a sqlite schema for you, since it seems like you are used to regular SQL and the whole sqlite difference might be confusing you a little

DDahoom you are working with primary keys so they have to be unique for each row. what y...

captain•10/27/23, 5:40 AM

if i ensure the key is always a string and unique, what other issues could i run into?

DahoomOP•10/27/23, 5:40 AM

The issue is that you would have to make sure its unqiue on your end

DahoomOP•10/27/23, 5:41 AM

its like 1 layer and if that layer fails you could break the whole thing without even noticing

captain•10/27/23, 5:41 AM

it will always be unique coming from the two different sources

captain•10/27/23, 5:41 AM

source 1 always unique and source 2 unique with letters

captain•10/27/23, 5:41 AM

so they could never overlap

DahoomOP•10/27/23, 5:42 AM

a person could sent a request twice and this will could break a lot of things

DahoomOP•10/27/23, 5:43 AM

there are many ways it could fail, my point is with this approach you will run into MANY unforseen consequences.

captain•10/27/23, 5:44 AM

got it

captain•10/27/23, 5:44 AM

well thats a good point

DahoomOP•10/27/23, 5:45 AM

Do it the way you want honestly but I'm just trying to save you some headache

captain•10/27/23, 5:45 AM

appreciate the incite

DahoomOP•10/27/23, 5:45 AM

np, good luck on your project

DDahoom np, good luck on your project

captain•10/27/23, 5:46 AM

thank you for taking the time, it was very helpful!

Adi•10/27/23, 9:14 AM

To the D1 team, not sure if this is already on the cards but I have a feature request.
When making queries on the Cloudflare dashboard for D1, it would be great if we could get back query metrics similar to how the client API gives back metrics.
Would make testing query performance a breeze.

Adi•10/27/23, 9:16 AM

Also not sure if there is a better place to make this request (e.g github), if so, please let me know.

kian•10/27/23, 9:16 AM

Here should be fine - in the interim, if you open your browser’s devtools, you’ll have the metrics in the response under the same meta object.

Not ideal, but if you just want to quickly check a query then it’s there

Kkian Here should be fine - in the interim, if you open your browser’s devtools, you’l...

Adi•10/27/23, 9:59 AM

Oh my lord you're absolutely correct. Thank you!!
This helps so much in the interim XD

charl.dev•10/27/23, 1:37 PM

im trying to seed my dbs but keep getting

The request is malformed: failed to decode id

The request is malformed: failed to decode id

charl.dev•10/27/23, 1:38 PM

"code": 7400,

charl.dev•10/27/23, 1:38 PM

just basic queries, nothing fancy

DahoomOP•10/27/23, 1:38 PM

the issue is with the request, just telling us its basic doesnt really help :)

DahoomOP•10/27/23, 1:39 PM

if you want you can just show the id column and what you tried to insert into it, incase its sensitive data

charl.dev•10/27/23, 1:41 PM

the db is emtpy, im trying to create the tables via query

charl.dev•10/27/23, 1:41 PM

e.g.

https://api.cloudflare.com/client/v4/accounts/account_id/d1/database/db_id/query

{
  "params": [],
  "sql": "DROP TABLE IF EXISTS comments; CREATE TABLE IF NOT EXISTS comments ( id integer PRIMARY KEY AUTOINCREMENT, author text NOT NULL, body text NOT NULL, post_slug text NOT NULL ); CREATE INDEX idx_comments_post_slug ON comments (post_slug);"
}

https://api.cloudflare.com/client/v4/accounts/account_id/d1/database/db_id/query

{
  "params": [],
  "sql": "DROP TABLE IF EXISTS comments; CREATE TABLE IF NOT EXISTS comments ( id integer PRIMARY KEY AUTOINCREMENT, author text NOT NULL, body text NOT NULL, post_slug text NOT NULL ); CREATE INDEX idx_comments_post_slug ON comments (post_slug);"
}

DahoomOP•10/27/23, 1:42 PM

try

CREATE TABLE IF NOT EXISTS comments ( id integer PRIMARY KEY, author text NOT NULL, body text NOT NULL, post_slug text NOT NULL ); CREATE INDEX idx_comments_post_slug ON comments (post_slug)

CREATE TABLE IF NOT EXISTS comments ( id integer PRIMARY KEY, author text NOT NULL, body text NOT NULL, post_slug text NOT NULL ); CREATE INDEX idx_comments_post_slug ON comments (post_slug)

(removed autoincrement)

charl.dev•10/27/23, 1:42 PM

my bad

charl.dev•10/27/23, 1:42 PM

was using db name instead of id

DahoomOP•10/27/23, 1:43 PM

ah no problem

DahoomOP•10/27/23, 1:43 PM

happens sometimes

slagathor•10/27/23, 8:46 PM

Does anyone have a good way to visualize D1 data?

slagathor•10/27/23, 8:47 PM

Right now I'm using DataGrip with https://github.com/isaac-mcfadyen/d1-jdbc-driver, but it's a bit buggy

slagathor•10/27/23, 8:48 PM

(Honestly it's been very helpful so far. I'm just curious what other people are using.)

Sslagathor Right now I'm using DataGrip with https://github.com/isaac-mcfadyen/d1-jdbc-driv...

Paul•10/28/23, 2:45 PM

this is exactly what i've been looking for, thanks. What's buggy about it?

Paul•10/28/23, 3:02 PM

i'm going to try and use that with DBeaver

Paul•10/28/23, 3:02 PM

or is that a bad idea?

Paul•10/28/23, 3:24 PM

How can we export the sqlite backup file for our beta D1?

PPaul How can we export the sqlite backup file for our beta D1?

Cyb3r-Jak3•10/28/23, 3:32 PM

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then download that. There is currently no built in way

Onik•10/28/23, 5:54 PM

Hey guys, has anyone tried to access their database using a client? or is it only via packages they provided or API?
Client being like HeidiSQL, or Visual Studio Code Database Extension

DahoomOP•10/28/23, 6:13 PM

I think the only way to access it is through workers/cloudflare REST api

OOnik Hey guys, has anyone tried to access their database using a client? or is it onl...

DahoomOP•10/28/23, 6:16 PM

You could create your own worker that routes your sql queries or you can use https://github.com/elithrar/http-api-d1-example (I have not used it for a while so I'm unsure if it still works)

https://api.cloudflare.com/client/v4/accounts/account_id/d1/database/db_id/query { "params": [], "sql": "DROP TABLE IF EXISTS comments; CREATE TABLE IF NOT EXISTS comments ( id integer PRIMARY KEY AUTOINCREMENT, author text NOT NULL, body text NOT NULL, post_slug text NOT NULL ); CREATE INDEX idx_comments_post_slug ON comments (post_slug);" }

A more pressing question is why you would use the text format when saving only integers?

Similar Threads