Cloudflare Developers•3y ago

oh just realized free dbs are limited to 500mb, so may have to come up with another solution unless

oh just realized free dbs are limited to 500mb, so may have to come up with another solution unless I want to pay

switzOP•10/26/23, 9:52 PM

this is not mission critical, will have to re-think

Sswitz oh just realized free dbs are limited to 500mb, so may have to come up with anot...

omar4289•10/26/23, 10:12 PM

Yeah, also if you have indexes, that might take some space too, and now we are only limited to 2gb per db, so not even sure you could migrate it all

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

Its really important to be able to download a D1 database, with this new TimeTravel feature the ability has seemed to disappear... This seems like vendor lock-in... Any tricks or workarounds to download a large D1 database ?

DDahoom nora's project should be linked in the docs

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

CChrisBrocklesby Its really important to be able to download a D1 database, with this new TimeTra...

Cyb3r-Jak3•10/27/23, 1:50 AM

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

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

oh just realized free dbs are limited to 500mb, so may have to come up with another solution unless

Similar Threads