Cloudflare Developers•3y ago

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

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, ...

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

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

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

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

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

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

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

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

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

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

it will always be unique coming from the two different sources

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

source 1 always unique and source 2 unique with letters

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

so they could never overlap

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

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

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

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

got it

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

well thats a good point

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

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

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

appreciate the incite

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

np, good luck on your project

DDahoom np, good luck on your project

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

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

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

Dahoom•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);"
}

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

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

ah no problem

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

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

Dahoom•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)

Onik•10/28/23, 6:17 PM

sounds good, thanks

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

Old Man Maker•10/28/23, 6:21 PM

That's really awesome

Rex•10/28/23, 7:21 PM

Is there a way to upload sql to d1?

Rex•10/28/23, 7:29 PM

or run sql

RRex or run sql

Dahoom•10/28/23, 7:30 PM

https://developers.cloudflare.com/workers/wrangler/commands/#execute

Commands - Wrangler · Cloudflare Workers docs

Create, develop, and deploy your Cloudflare Workers with Wrangler commands.

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);" }

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

Similar Threads