Hi, I am having major issues uploading a large amount of data from a .sql dump to D1 "136,000 rows"

Hi, I am having major issues uploading a large amount of data from a .sql dump to D1 "136,000 rows" any tips / help you guys can offer ?

CChrisBrocklesby Hi, I am having major issues uploading a large amount of data from a .sql dump t...

Cyb3r-Jak3•10/10/23, 11:00 PM

You could try splitting the sql dump file into multiple files and do each one separately

CCyb3r-Jak3 You could try splitting the sql dump file into multiple files and do each one se...

ChrisBrocklesbyOP•10/10/23, 11:20 PM

Hi, I have tried splitting the 650mb .sql file down into 2mb files with no luck sadly. I get the following error via Wrangler...

Paul•10/11/23, 12:03 AM

anyone using SvelteKit managed to get

npm run dev

npm run dev

working after adding D1? I'm assuming we would need to tell it to run the local db

Fabian•10/11/23, 1:51 AM

How to select the most recent day's data from the database?
The database content is as follows:
id ip UA time
41 139.162.86.143 Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/117.0.0.0 Safari/537.36 2023/10/7 09:40:22

Can this be done?
SELECT * FROM m_table
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Fabian•10/11/23, 1:52 AM

Original message was deleted

MrXO•10/11/23, 2:55 AM

use API

MMax (@rozenmd)If you need performance, write a Worker then talk to it over HTTP from python

MrXO•10/11/23, 2:56 AM

so we will use python -> call HTTP request to worker -> worker handle d1 db logic?

MMrXO use API

Fabian•10/11/23, 2:57 AM

大佬帮看看吧

MrXO•10/11/23, 2:58 AM

like this

curl --request POST \
--url https://api.cloudflare.com/client/v4/accounts/account-identifier/d1/database/database-identifier/query \
--header 'Authorization: Bearer undefined' \
--header 'Content-Type: application/json' \
--data '{
"params": [
"firstParam",
"secondParam"
],
"sql": "SELECT * FROM m_table
WHERE time >= DATE_SUB(NOW(), INTERVAL 1 DAY);"
}'

MrXO•10/11/23, 2:58 AM

change your --header 'Authorization: Bearer undefined' \

DDaniFoldi I recommend drizzle, we recently started migrating all projects to it and it wor...

Paolo•10/11/23, 4:50 AM

I had a look and it does not seem to have models? It seems only to create data rows rather than instantiate classes.

soup•10/11/23, 7:52 AM

Hello!

Does anybody know if there's a way to natively insert a new row and get the auto incremented id from the insertion?

Ssoup Hello! Does anybody know if there's a way to natively insert a new row and get ...

Max (@rozenmd)•10/11/23, 7:59 AM

add RETURNING *RETURNING * to the end of the query

Max (@rozenmd)•10/11/23, 8:00 AM

or RETURNING idRETURNING id just for the id column

andyt•10/11/23, 8:55 AM

I have setup my local D1 using wrangler and --local, I have inserted a row and can retrieve the row using wrangler execute, when I run my function I get Error: D1_ERROR: no such table: companyError: D1_ERROR: no such table: company, I have my D! config in my toml file and when I run my pages I pass

--d1=DB

--d1=DB

which matches the binding name in my toml, what have I done wrong ?

MrXO•10/11/23, 9:02 AM

took 0.2s to insert a row via API, so slow

MrXO•10/11/23, 9:02 AM

maybe I need use worker

MMrXO maybe I need use worker

itsmatteomanf•10/11/23, 9:02 AM

This, yes.

MMrXO took 0.2s to insert a row via API, so slow

itsmatteomanf•10/11/23, 9:03 AM

The insert didn't take 200ms, the full API request did have a 200ms roundtrip+processing time.

MrXO•10/11/23, 9:03 AM

yes

itsmatteomanf•10/11/23, 9:03 AM

The time to insert I presume is returned in the metadata along with the query results (I assume, never used that API myself)

Iitsmatteomanf The time to insert I presume is returned in the metadata along with the query re...

MrXO•10/11/23, 9:04 AM

correct

MrXO•10/11/23, 9:04 AM

I did not work with worker before

MrXO•10/11/23, 9:05 AM

so basically we build worker handler d1 query - and use python call to the worker, is it right?

itsmatteomanf•10/11/23, 9:05 AM

Yeah, exactly

Aandyt I have setup my local D1 using wrangler and --local, I have inserted a row and c...

Paolo•10/11/23, 9:05 AM

did you migrate the local DB?

MrXO•10/11/23, 9:06 AM

ok lets me try

MMrXO ok lets me try

itsmatteomanf•10/11/23, 9:06 AM

Be careful with SQL injections...

MrXO•10/11/23, 9:06 AM

MrXO•10/11/23, 9:06 AM

sure

PPaolo did you migrate the local DB?

andyt•10/11/23, 9:33 AM

no, I have created the same table on both local and remote

andyt•10/11/23, 9:41 AM

also when I create a migration it is empty

Paolo•10/11/23, 9:45 AM

well, you can seed the migration if you want

Paolo•10/11/23, 9:45 AM

beside

Aandyt no, I have created the same table on both local and remote

Paolo•10/11/23, 9:46 AM

what does

wrangler d1 execute DB --local --command "select * from company"

wrangler d1 execute DB --local --command "select * from company"

return?

andyt•10/11/23, 9:46 AM

the row i inserted

Aandyt the row i inserted

Paolo•10/11/23, 9:47 AM

can I have a look at the toml file? also, are you using pages or workers?

andyt•10/11/23, 10:04 AM

Looking at my db on the web site, it says I have two tables but clearly only have 1

Aandyt Looking at my db on the web site, it says I have two tables but clearly only hav...

Paolo•10/11/23, 10:24 AM

that is also a bug I found. it mysteriously vanished on its own after a while

GuillaumeLakano•10/11/23, 10:29 AM

Hi! With the future system of dynamic binding ( without explicit name in wrangler.toml ), will you permit us to connect only with the name of the database, or this will be only with the UUID value ?
I need to known if I need to store somewhere (KV, DO, D1...) an association table between the databases names and their UUID.

roftt•10/11/23, 11:50 AM

When I call D1's REST API directly like: https://api.cloudflare.com/client/v4
/accounts/{account-identifier}/d1/database/{database-identifier}/query
Do I automatically trigger a Worker call? So the pricing counts as Worker request + D1 rows read?