Given a CSV file containing data bound for a D1 table, what is the most optimal way to update said t

H

I don’t know ahead of time what has changed, nor even if anything has changed

T

TigersWay•7/31/23, 11:46 PM

Following some previous comments, should we conclude that the API

dump

dump

dump

dump is not working yet/anymore on the new/default/experimental backend... Am I right?

C

chientrm.com•7/31/23, 11:56 PM

using D1, how to get

rowsEffected

rowsEffected

rowsEffected

rowsEffected?

HHello, I’m Allie!Given a CSV file containing data bound for a D1 table, what is the most optimal ...

M

Matt Silverlock•8/1/23, 12:20 AM

You’d have to read it to know what changed. Sounds like premature optimization? How many rows is the CSV that this matters?

TTigersWay Following some previous comments, should we conclude that the API `dump` is not ...

M

Matt Silverlock•8/1/23, 12:21 AM

Correct, per the docs: https://developers.cloudflare.com/d1/learning/backups/

there are not near term plans to support dump.

Backups (Legacy) · Cloudflare D1 docs

D1 has built-in support for creating and restoring backups of your databases, including support for scheduled automatic backups and manual backup …

TTigersWay Following some previous comments, should we conclude that the API `dump` is not ...

M

Matt Silverlock•8/1/23, 12:21 AM

d1-database

Time Travel is much more powerful.

MMatt Silverlock https://discord.com/channels/595317990191398933/992060581832032316/1135238676264...

T

TigersWay•8/1/23, 12:23 AM

But right now I have no way to "recover" a DB full of real data!
I guess I'm gonna start and write an export API

MMatt Silverlock You’d have to read it to know what changed. Sounds like premature optimization? ...

H

Hello, I’m Allie!OP•8/1/23, 12:52 AM

One of the files in question has around ~68k rows. And I could probably just run it as a single/few large

UPDATE

UPDATE

UPDATE

UPDATEs, but that seems pretty slow...

H

Hello, I’m Allie!OP•8/1/23, 12:54 AM

And in case I do need to run each separately, would it be more efficient to run it as a few large statements, or each row as a single statement in a batch?

H

Hello, I’m Allie!OP•8/1/23, 12:57 AM

To be clear, I'm not expecting to be able to do it within a single read/write, I'm just wondering if there is anything I could do that is faster/more efficient than ~70k+ inserts every day...

H

Hello, I’m Allie!OP•8/1/23, 1:00 AM

If it matters, my dataset is a GTFS dump from BART. In this case, the number of rows is comparatively small, but it can scale to a fair bit large with larger transit systems, so I'm just going for best practices here

H

Hello, I’m Allie!OP•8/1/23, 1:03 AM

And it shouldn't

be outside the workable range of D1, as the CSV-form dataset is currently 6.5 MB

HHello, I’m Allie!One of the files in question has around ~68k rows. And I could probably just run...

M

Matt Silverlock•8/1/23, 1:06 AM

Or https://www.sqlite.org/lang_UPSERT.html

?

MMatt Silverlock Or https://www.sqlite.org/lang_UPSERT.html - ?

H

Hello, I’m Allie!OP•8/1/23, 3:08 AM

Would there also be a handy way to drop rows that aren’t in the CSV dataset?

C

chientrm.com•8/1/23, 3:21 AM

how can I check

rowsAffected

rowsAffected

rowsAffected

rowsAffected?

HHello, I’m Allie!Would there also be a handy way to drop rows that **aren’t** in the CSV dataset?

I

itsmatteomanf•8/1/23, 7:22 AM

At that point just create a new table...?

M

Matt Silverlock•8/1/23, 9:05 AM

So as of a few hours ago, every D1 database on the new backend now has a limit of 500MB.

That’s 500MB per database, and you each have 10 databases. This is just a stop on the way to even larger databases, which we’ll enable for those of you on the paid Workers plan in the near-ish future. Happy querying!

Changelog entry: https://developers.cloudflare.com/d1/changelog/#per-database-limit-now-500-mb

MMatt Silverlock So as of a few hours ago, **every D1 database on the new backend now has a limit...

E

Erisa•8/1/23, 9:37 AM

@D1

MMatt Silverlock So as of a few hours ago, **every D1 database on the new backend now has a limit...

I

itsmatteomanf•8/1/23, 1:16 PM

Ohhh, I was just considering moving a 227MB database, but couldn't. Can now

I

itsmatteomanf•8/1/23, 1:17 PM

Though, to be fair, it now works very well with Grafana, I'd need to reconfigure everything.

I

itsmatteomanf•8/1/23, 1:17 PM

But I could get fancy and use e-mail workers to add the logs to the DB from a single worker

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

M

Matt Silverlock•8/1/23, 3:32 PM

Are you using the new backend? Executed as-is for me.

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

~/repos/d1 (main) ✔
➜  wrangler d1 execute db-wnam --file=$HOME/Downloads/schema.sql

🌀 Mapping SQL input into an array of statements
🌀 Parsing 9 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 9 commands in 0.6782660111784935ms

Ooxygencraft I'm trying to setup the schema on my D1 database but when I run the wrangler com...

M

Matt Silverlock•8/1/23, 3:33 PM

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

➜  wrangler d1 execute db-wnam --command "PRAGMA table_xinfo(scores)"

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on db-wnam (6f2478dc-789b-4f31-95ff-d6001dc617d2):
🚣 Executed 1 commands in 0.225492000579834ms
┌─────┬───────────────┬─────────┬─────────┬────────────┬────┬────────┐
│ cid │ name          │ type    │ notnull │ dflt_value │ pk │ hidden │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 0   │ scoreId       │ INTEGER │ 0       │            │ 1  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 1   │ leaderboardId │ INTEGER │ 0       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 2   │ userId        │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 3   │ value         │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 4   │ gameVersion   │ TEXT    │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 5   │ timestamp     │ INTEGER │ 1       │            │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 6   │ hasReplay     │ INTEGER │ 0       │ 0          │ 0  │ 0      │
├─────┼───────────────┼─────────┼─────────┼────────────┼────┼────────┤
│ 7   │ verified      │ INTEGER │ 0       │ 0          │ 0  │ 0      │
└─────┴───────────────┴─────────┴─────────┴────────────┴────┴────────┘

ZZebra Hello! I have a stupid question but how do I view my table entries in D1 ? I c...

D

divby0•8/1/23, 8:27 PM

Seeing the same for all tables on my project, worked fine before. I guess this is a temporary thing

Ddivby0 Seeing the same for all tables on my project, worked fine before. I guess this i...

M

Matt Silverlock•8/1/23, 8:32 PM

Specifically after 9? Or just in general.

MMatt Silverlock Specifically after 9? Or just in general.

D

divby0•8/1/23, 8:34 PM

Definitely independant of the number cause it didn't change

D

divby0•8/1/23, 8:35 PM

Some tables have 1 row, some have 4, some 11, none of them are showing up, but the console tab is still working fine so in case this is temporary it's totally okay for me at least

Ddivby0 Some tables have 1 row, some have 4, some 11, none of them are showing up, but t...

M

Matt Silverlock•8/1/23, 8:37 PM

I suspect a bug (we didn’t push any code today, but the dashboard team may have). Let me take a look shortly.

MMatt Silverlock I suspect a bug (we didn’t push any code today, but the dashboard team may have)...

D

divby0•8/1/23, 8:37 PM

no rush - I appreciate it

M

Matt Silverlock•8/1/23, 8:41 PM

I can't immediately repro: one of my DBs seems fine?

M

Matt Silverlock•8/1/23, 8:41 PM

M

Matt Silverlock•8/1/23, 8:41 PM

ZZebra And sorry for spamming with images here, just thought it might help with bug fix...

M

Matt Silverlock•8/1/23, 8:56 PM

No, that's helpful. But also weird — there's no paid tier at all (yet). Zero changes there.

ZZebra And sorry for spamming with images here, just thought it might help with bug fix...

M

Matt Silverlock•8/1/23, 8:56 PM

cc @geelen when he's in shortly

A

AFSANEH ABBAS•8/1/23, 9:18 PM

Why Web
https://api.sandbox-transgerwise-tech
Can not work with email

U

Unsmart•8/1/23, 9:21 PM

Not sure what you mean

I

itsmatteomanf•8/1/23, 9:30 PM

I saw 61 instances of this error in the span of a few seconds, seems fine now...

D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.
D1_ERROR: Error: Internal error while starting up D1 storage caused object to be reset.

I

itsmatteomanf•8/1/23, 9:30 PM

New backend, Database ID:

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2

546787dd-35d5-4822-9b50-36fa0a4310c2.

Given a CSV file containing data bound for a D1 table, what is the most optimal way to update said t

Similar Threads

Similar Threads

Similar Threads