Cloudflare Developers•2y ago

heh, well the problem is "reducing" the size briefly increases the size due to writing all the chang

heh, well the problem is "reducing" the size briefly increases the size due to writing all the changes to the WAL file

Kkenton heh, well the problem is "reducing" the size briefly increases the size due to w...

itsmatteomanf•2/17/24, 8:08 PM

Oh, yeah, true…

Kkenton doh, the scheduling algorithm made a bad call and colocated your database with s...

itsmatteomanf•2/17/24, 8:34 PM

Can confirm it's back! And the DELETE actually was successful before the error :lul:

Original message was deleted

kentonOP•2/17/24, 9:27 PM

arguably a bug, but not something we can easily fix. The sqlite DB contains some hidden metadata tables that we don't want to give applications direct access to, which means we use an authorizer callback to block access. If you try to enumerate all tables and query them all you will likely run afoul of this.

itsmatteomanf•2/17/24, 9:32 PM

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

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

Was literally typing that out :lul:

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

Thanks!

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

Let me try it

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

includes indexes too!

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

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

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

itsmatteomanf•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?

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

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

Oh :NotLikeThis:

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

tut tut

MMax (@rozenmd)that was a useful feature

itsmatteomanf•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_id

database_id

id

id

was fixed though, since the API does return

database_id

database_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

path

path

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

heh, well the problem is "reducing" the size briefly increases the size due to writing all the chang

Similar Threads

Similar Threads

Similar Threads