I don't see the option to use new D1 backend, is it the default now? 🤔

J

James•7/31/23, 10:46 PM

It's default now, yes!

wrangler d1 list

wrangler d1 list

should give you a list back of your D1 databases and what backend they're running on. If you see

version: beta

version: beta

, they're on the new backend

Cchientrm.com I don't see the option to use new D1 backend, is it the default now? 🤔

M

Matt Silverlock•7/31/23, 10:50 PM

https://developers.cloudflare.com/d1/changelog/#new-default-storage-subsystem

Changelog · Cloudflare D1 docs

Databases created via the Cloudflare dashboard and Wrangler (as of v3.4.0) now use D1’s new storage subsystem by default. The new backend can be 6 - …

H

Hello, I’m Allie!•7/31/23, 11:17 PM

Given a CSV file containing data bound for a D1 table, what is the most optimal way to update said table, reducing reads/writes?

H

Hello, I’m Allie!•7/31/23, 11:18 PM

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

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

C

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

using D1, how to get

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

s, but that seems pretty slow...

H

Hello, I’m Allie!•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!•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!•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!•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!•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.comOP•8/1/23, 3:21 AM

how can I check

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?