Cloudflare Developers•2y ago

Is there any specific way, or plans for a way, to see storage used by each table and, not sure if po

Is there any specific way, or plans for a way, to see storage used by each table and, not sure if possible, each index?

Iitsmatteomanf Is there any specific way, or plans for a way, to see storage used by each table...

Max (@rozenmd)•2/17/24, 9:52 PM

SELECT SUM("pgsize") FROM "dbstat" WHERE name='TABLENAME';

Max (@rozenmd)•2/17/24, 9:52 PM

apparently

Max (@rozenmd)•2/17/24, 9:54 PM

SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name;

for all tables

MMax (@rozenmd)SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name; for all tables

itsmatteomanfOP•2/17/24, 9:54 PM

Was literally typing that out :lul:

itsmatteomanfOP•2/17/24, 9:54 PM

Thanks!

itsmatteomanfOP•2/17/24, 9:54 PM

Let me try it

Max (@rozenmd)•2/17/24, 9:54 PM

includes indexes too!

itsmatteomanfOP•2/17/24, 9:54 PM

Uhm... no such table: dbstatno such table: dbstat

kian•2/17/24, 9:55 PM

proobably isn't enabled in D1

itsmatteomanfOP•2/17/24, 9:55 PM

I'm not using any storage! :party_yay:

kian•2/17/24, 9:55 PM

it's a compile-time flag (SQLITE_ENABLE_DBSTAT_VTABSQLITE_ENABLE_DBSTAT_VTAB)

Kkian proobably isn't enabled in D1

itsmatteomanfOP•2/17/24, 9:55 PM

Yeah, I figured...

kian•2/17/24, 9:56 PM

https://github.com/cloudflare/workerd/blob/4d32cdc6252fc4d93147638243f476d810393611/build/BUILD.sqlite3#L10-L18 :^(

Kkian <https://github.com/cloudflare/workerd/blob/4d32cdc6252fc4d93147638243f476d81039...

itsmatteomanfOP•2/17/24, 9:57 PM

That's sad

Max (@rozenmd)•2/17/24, 10:14 PM

copy paste it?

MMax (@rozenmd)copy paste it?

itsmatteomanfOP•2/17/24, 10:14 PM

I did

Max (@rozenmd)•2/17/24, 10:14 PM

oh boo i was testing on an alpha db

Max (@rozenmd)•2/17/24, 10:14 PM

that was a useful feature

MMax (@rozenmd)oh boo i was testing on an alpha db

itsmatteomanfOP•2/17/24, 10:14 PM

Oh :NotLikeThis:

kian•2/17/24, 10:14 PM

tut tut

MMax (@rozenmd)that was a useful feature

itsmatteomanfOP•2/17/24, 10:15 PM

It really was!

MMax (@rozenmd)that was a useful feature

Isaac McFadyen•2/17/24, 11:16 PM

You should bring up adding it for discussion in

workerd

workerd

Isaac McFadyen•2/17/24, 11:17 PM

So that at least it's considered in terms of any security issues vs the extra usability

Dylan Jones•2/18/24, 1:40 AM

Trying to add a D1 database to a worker through the API:

https://developers.cloudflare.com/api/operations/worker-script-patch-settings

getting error:

"code": 10202,
"message": "workers.api.error.invalid_script_config"

The docs say for the binding type

Allowed value:
kv_namespace

Is D1 bindings not allowed to be added through the API?

Trying a POC where I dynamically create a D1 for each user, and bind it a worker so I can query the database.

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

kian•2/18/24, 2:26 AM

What’s your full request body?

Dylan Jones•2/18/24, 2:32 AM

Using insomia to test

settings: {"bindings":[ {"database_id": "ID", "name": "DB1","type": "d1"}]}

Dylan Jones•2/18/24, 2:32 AM

but I think I got it working... just randomly tried id instead of database_id

Dylan Jones•2/18/24, 2:42 AM

ah, that seems to work!! that should be in the docs some where. When you get the worker settings it has "database_id", but to add you have to just use "id"

Dylan Jones•2/18/24, 2:43 AM

the docs: https://developers.cloudflare.com/api/operations/worker-script-get-settings say it returns as namespace_id

Cloudflare API Documentation

Interact with Cloudflare's products and services via the Cloudflare API

kian•2/18/24, 2:44 AM

The API docs render it very poorly, you need to click on the array (oneOf) [objects]array (oneOf) [objects] next to the bindings until you find the D1 one.

kian•2/18/24, 2:44 AM

kian•2/18/24, 2:44 AM

I was under the impression that database_iddatabase_id vs

id

id

was fixed though, since the API does return database_iddatabase_id in a few places, so I'll ask the team on Monday.

Dylan Jones•2/18/24, 2:49 AM

oh wow, I had no idea you could cycle through them like that. That would have made it more clear, I completely guessed

Bblurrrb Do we have read replicas enabled for d1?

Isaac McFadyen•2/18/24, 3:56 AM

D1 does not currently have read replicas, no. Read replica support is planned but is not here yet and no timeline has been given.

Dylan Jones•2/18/24, 4:25 AM

Does that mean each D1 database is only in a single region?

Isaac McFadyen•2/18/24, 4:28 AM

That's correct, yes.

Isaac McFadyen•2/18/24, 4:29 AM

Your DB will either be created in the nearest region to you when you create it, or you can optionally provide a "region hint" to request a specific region (and the DB placer will try to place it as close as possible to your requested region).

Dylan Jones•2/18/24, 4:31 AM

okay thanks! thats good to know

Jakob Norlin•2/18/24, 11:32 AM

It doesn't seem possible to use wrangler to apply migrations to a local DB created with miniflare

When I set up a local DB with miniflare it is persisted to .mf/d1/miniflare-D1DatabaseObject/{hash}.sqlite.mf/d1/miniflare-D1DatabaseObject/{hash}.sqlite. But if I run wrangler d1 migrations apply DB --local --persist-to ./.mfwrangler d1 migrations apply DB --local --persist-to ./.mf it creates a new db at .mf/v3/d1/miniflare-D1DatabaseObject/{hash}.sqlite.mf/v3/d1/miniflare-D1DatabaseObject/{hash}.sqlite instead (note the extra /v3//v3/).

I don't see a way in which I can get miniflare to work against the same DB file as the wrangler CLI with the current conventions.

Could I get som guidance on this?

Original message was deleted

Jakob Norlin•2/18/24, 7:40 PM

No that doesn't work either. Then it creates a new db file at .wrangler/state/v3/d1/miniflare-D1DatabaseObject/{hash}.sqlite.wrangler/state/v3/d1/miniflare-D1DatabaseObject/{hash}.sqlite

Original message was deleted

Jakob Norlin•2/18/24, 7:56 PM

Are you maybe on an older version of wrangler? I'm on the latest v3.28.3v3.28.3, and if I add pathpath to

wrangler.toml

wrangler.toml

like you have it does nothing. The migration script still targets the .wrangler.wrangler folder

Original message was deleted

Jakob Norlin•2/18/24, 7:57 PM

Phew, that means I'm not going mad at least

Jakob Norlin•2/18/24, 8:02 PM

So maybe your miniflare is somehow set to read from the .wrangler folder by SvelteKit then? I've got this, and it reads the DB from the .mf folder.

import { Miniflare } from 'miniflare'

const mf = new Miniflare({
  d1Databases: ["DB"],
  modules: true,
  script: "",
  d1Persist: true,
})

import { Miniflare } from 'miniflare'

const mf = new Miniflare({
  d1Databases: ["DB"],
  modules: true,
  script: "",
  d1Persist: true,
})

Original message was deleted

Jakob Norlin•2/18/24, 8:13 PM

Hmm yeah that makes more sense, but not getting it to work yet. It doesn't seem to find the DB

kehitys.•2/19/24, 10:56 AM

Good afternoon.

I have been interested about D1 but I could need help to understand some parts.

In the official website of D1 (https://www.cloudflare.com/developer-platform/d1/) it is told "Infinite scalability with cost effective pricing". How well does the db scale? What are max write numbers/s as SQLite has 1 concurrent write as of my understanding?

I was looking information about the high availability of D1. Are there replicas or some kind of failover protocol that if the write-read instance would fail another will be automatically be started resulting in to in low and no downtime for the db?

Also where is the data located? Only to a specific location of the db that is chosen or through read replicas to everywhere basicly?

Than you very much for helping a new SQL learner

mllnd•2/19/24, 12:35 PM

Is there a way to bind D1 remote database during local development?

kian•2/19/24, 12:44 PM

Workers, add —remote to the dev command

Pages, no

Unsmart•2/19/24, 5:42 PM

You put an api token.

Bearer <token>

Bearer <token>

Is there any specific way, or plans for a way, to see storage used by each table and, not sure if po

Similar Threads