Cloudflare Developers•3y ago

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then download that

Dahoom•10/27/23, 1:51 AM

and he beat me to it

CCyb3r-Jak3 You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup yo...

ChrisBrocklesby•10/27/23, 1:57 AM

Hi, Does that work with databases on the new storage subsystem in D1 ?

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

queries the DB with select and writes it to an sql file to put it simply

ChrisBrocklesby•10/27/23, 2:01 AM

@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-Jak3OP•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

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()

You can use https://github.com/nora-soderlund/cloudflare-d1-backups to backup your database and then

Similar Threads