@Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully this becomes native

@Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully this becomes native option in the Cloudflare Dashboard (or Wrangler CLI) in the future.

CChrisBrocklesby @Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully ...

Cyb3r-Jak3•10/27/23, 2:03 AM

I believe it is planned to be added

captain•10/27/23, 4:52 AM

odd thing occuring...that seems new and I can't explain it

captain•10/27/23, 4:53 AM

i'm inserting data but on the primary key, if i insert a number like: 9440344, the database saves it as 9440344.0

captain•10/27/23, 4:54 AM

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

Dahoom•10/27/23, 4:54 AM

probably an sqlite thing, might be saving int as double for some reason?

Dahoom•10/27/23, 4:55 AM

check the PRAGMA config

DDahoom probably an sqlite thing, might be saving int as double for some reason?

captain•10/27/23, 4:55 AM

whats weird is that i didn't notice

captain•10/27/23, 4:55 AM

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

CREATE TABLE quotes (order_id text PRIMARY KEY,location_id text,status text,type text,vendor text,date integer,date_exp integer,vendor_order_id text,vendor_order_body text);

Dahoom•10/27/23, 4:57 AM

which column do you get the number from?

Dahoom•10/27/23, 4:59 AM

since a lot of it is being saved as text it might be the way js is parsing the values before being inserted into the db

captain•10/27/23, 4:59 AM

i'm checking the number in console before it gets entered and not seeing the .0

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

and i'm also doing parseInt(key)

DDahoom which column do you get the number from?

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

any other ideas?

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

i'm dumbfounded on this

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

wondering if something changed on the cloudflare side

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

order_id is the primary key using text

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

yes

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

https://stackoverflow.com/questions/23157411/using-text-as-a-primary-key-in-sqlite-table-bad discussion says there shouldn't be any performance issues

Stack Overflow

Using text as a primary key in SQLite table bad?

Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true? And will the rowid be used as the actual primary key in such a case?

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

I'm not saying that I'm just wondering if THATS whats causing the issue

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

and dont trust stackoverflow

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

its like citing Wikipedia

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

ha maybe so, but this probably is a bug? maybe? Should I his cloudflare staff on this?

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

most likely a PRAGMA issue to do with how the value is being represented in sqlite

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

try to convert the value to a string before inserting it, at least thats what gippity says might work https://decide.gg/s/pGbt

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

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

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

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

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?

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

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

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.

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

got it

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

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

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

await env.MYDATABASE_DB.prepare(`insert into quotes (order_id, location_name, location_id, status, type, vendor, date, date_exp, vendor_order_id, vendor_order_body) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`).bind(parseInt(key), raw_data_post.orders[0].restaurant_name, customer_id, 'pending', type, best_auto_vendor, JSON.stringify(timestamp1), JSON.stringify(timestamp1), 'notsure', results).run()

@Cyb3r-Jok3 @Dahoom Excellent solution for now, Thank you both... But hopefully this becomes native

Similar Threads