SQLite supports bytes, so you could store anything if you convert it to bytes, or just serialize the

Llambrospetrou SQLite supports bytes, so you could store anything if you convert it to bytes, o...

H

Hard@Work•2/5/25, 11:10 AM

That would require access to the V8 Serializer(or a custom one) within the runtime, which doesn't exist yet, right?

HHard@Work That would require access to the V8 Serializer(or a custom one) within the runti...

L

lambrospetrouOP•2/5/25, 11:13 AM

I am not sure if the Workers runtime provides that serializer for users, to be honest.

G

geg•2/5/25, 12:41 PM

I asked about it before

mentioned here:
durable-objects

Llambrospetrou SQLite supports bytes, so you could store anything if you convert it to bytes, o...

H

Hard@Work•2/5/25, 12:43 PM

Actually, Kenton has a good point about giving users access to serialisers directly(being that upgradability is nigh-on impossible if the user has to serialize data themselves). I'm not too familiar with SQLite Extensions/Plugins, but would it be possible to somehow add a serialiser to the SQLite API, so it wouldn't require direct access?

Original message was deleted

H

Hello, I’m Allie!•2/5/25, 12:53 PM

Not necessarily? I was thinking that it would be some kind of extension where, if it recognizes an “object” column, then all modifications pass through the serializer, so you can’t ever directly touch the bytes

H

Hard@Work•2/5/25, 12:55 PM

Yeah idk

Original message was deleted

L

lambrospetrouOP•2/5/25, 2:52 PM

Keep in mind this is your database, in your application, and your business logic is really tied to it. I personally wouldn't depend on some abstract serialization for putting data in my SQL database. For the KV API it's fine since that's abstracted from you higher up, you "put" an object and you "get" that object back. You don't have direct access to the underlying bytes.

With SQLite, you do since you say what goes into each row. It's your database tables. In that case I would favor using serialization that I control. You can migrate from one to another if you change your mind, or want better compression in size vs time, etc.

B

Burrito•2/5/25, 4:12 PM

I'm using SQLite DO, one request is counting for 236 row reads, but with logging I've confirmed that there are only 5 SQL queries each with 1 row read. What could the rest be from? Does using the KV API count for more row reads?

BBurrito I'm using SQLite DO, one request is counting for 236 row reads, but with logging...

L

lambrospetrouOP•2/5/25, 4:15 PM

You say 5 SQL queries, but then you mention the KV API. What are you doing within each request?

If you are using SQL, then keep in mind any select you do might access your indices as well, and if you don't have indices a full scan is done reading all your rows.

Llambrospetrou You say 5 SQL queries, but then you mention the KV API. What are you doing withi...

B

Burrito•2/5/25, 4:18 PM

In DO constructor I use the KV API for migration, this should be 1 get operation there to check if the migration is up to date.
Code afterwards uses SQL API only, exposed via a wrapper:

const cursor = sqlStorage.exec(query, ...values)
const results = [...cursor]

if (logSql) {
    console.log({
        query,
        read: cursor.rowsRead,
        written: cursor.rowsWritten,
    })
}

return results

const cursor = sqlStorage.exec(query, ...values)
const results = [...cursor]

if (logSql) {
    console.log({
        query,
        read: cursor.rowsRead,
        written: cursor.rowsWritten,
    })
}

return results

const cursor = sqlStorage.exec(query, ...values)
const results = [...cursor]

if (logSql) {
    console.log({
        query,
        read: cursor.rowsRead,
        written: cursor.rowsWritten,
    })
}

return results

const cursor = sqlStorage.exec(query, ...values)
const results = [...cursor]

if (logSql) {
    console.log({
        query,
        read: cursor.rowsRead,
        written: cursor.rowsWritten,
    })
}

return results

B

Burrito•2/5/25, 4:25 PM

The request results in 236 row reads in DO metrics, but worker logs only show 5 SQL queries with 1 row read each. The only thing unaccounted for is that one KV get at the start, which is why I'm asking if KV API has a different way of counting.

11984 Ford Laser Learnt this one the hard way after I'd already determined a KV schema I liked. W...

I

iLearn•2/5/25, 4:40 PM

Brilliant Answer. Effort (and Money

) saver for Architects.
I was about to fall in the same trap as yours, but somehow thought to reach community to validate my schema and pricing. And bingo, your answer saved

@lambrospetrou @Hard@Work | R2 (Please pardon for tagging, if i am breaking community rules): In case you are Cloudflare employees, may you please update the documentation with the above answer (as an example) in "Pricing" for DO-KV storage. This will be "money"-saver for many, as the Pricing page has no such "explicit" example showing the clear "distinction" in pricing approach for "list" and "get" (though for list(), it is very clearly mentioned, but NOT for get() / put() ). Thanks.
Pricing page link : https://developers.cloudflare.com/durable-objects/platform/pricing/#key-value-storage-backend

Llambrospetrou Handling expiration after a TTL is quite trivial though if you need the single t...

I

iLearn•2/5/25, 4:50 PM

Appreciate the suggestion.
expirationTTL, imho, is of more value as ready-made-infra for "cleaning" the stale data (store and forget). There are many keys, which one "wants to forget" after creating.
example use-case : 2-minute expiration OTPs. i shall be relieved that i can keep on storing multiple such OTPs and they will be auto-cleaned up by infra. (NOTE : validation of OTP based on expiration time can be acheived in same way as you suggested in worker and hence, validation is not much of a concern). Concern will be a user creates multiple OTPs and "DOES-NOT" use them, in this scenario, expirationTTL keep my KV-store cleaning up and make developer life worry-free.

BBurrito The request results in 236 row reads in DO metrics, but worker logs only show 5 ...

J

James•2/5/25, 4:54 PM

I find it easier to debug row counting / query optimization when I log the return values of the rows read / written per query in real-time, rather than deduce from less granular logs.

JJames I find it easier to debug row counting / query optimization when I log the retur...

B

Burrito•2/5/25, 4:57 PM

Well

cursor.rowsRead

cursor.rowsRead

cursor.rowsRead

cursor.rowsRead should be accurate, the amount of returned rows isn't a good representation of amount of rows read because query can read more than it returns.

B

Burrito•2/5/25, 4:57 PM

But the query in question is just look up row by primary key, so 1 row read is accurate regardless.

IiLearn Brilliant Answer. Effort (and Money 🙂 ) saver for Architects. I was about to f...

L

lambrospetrouOP•2/5/25, 5:12 PM

What exactly is it not mentioned in the pricing docs?

A request unit is defined as 4 KB of data read or written. A request that writes or reads more than 4 KB will consume multiple units, for example, a 9 KB write will consume 3 write request units.

This is an example on the pricing page explaining how reads (e.g. gets) work. List is the same, but it needs clarification to make sure people know that it's across all items examined/returned.

Llambrospetrou What exactly is it not mentioned in the pricing docs? > A request unit is def...

I

iLearn•2/5/25, 5:18 PM

Just to be clear :
fact#1 : "A request unit is defined as 4 KB of data read" (as mentioned in Pricing page)

My Q#1 : Assume, "keys" is an array of 10-Keys, if i make get(keys) (single operation), and cumulative size of all 10-K-V returned values is less than 4KB AND size of each K-V is also less than 4KB .. then what will be the pricing ?
Answer Option 1 : 10 x Price of each RRU
Answer Option 2 : 1 x Price of each RRU

Link of usage of get(keys) .. NOTE : keys (in PLURAL) :
[ get(keys) is NOT available for Workers-KV, it is ONLY for DO-KV ]
https://developers.cloudflare.com/durable-objects/api/storage-api/#get

Cloudflare Docs

Durable Object Storage · Cloudflare Durable Objects docs

The Durable Object Storage API allows Durable Objects to access transactional and strongly consistent storage. A Durable Object's attached storage is private to its unique instance and cannot be accessed by other objects.

B

Burrito•2/5/25, 6:03 PM

Is there a faster way to debug DO storage metrics than waiting tens of minutes for dashboard to update? Is there a way to do it locally?

BBurrito Is there a faster way to debug DO storage metrics than waiting tens of minutes f...

H

Hard@Work•2/5/25, 6:05 PM

I don't think so? IIRC the dashboard is powered by GraphQL, which you can query directly, however it would still have delays

B

Burrito•2/5/25, 6:06 PM

Well that's problematic.

BBurrito Well that's problematic.

H

Hard@Work•2/5/25, 6:07 PM

It's a difficult thing because while seeing storage metrics for a single DO might be easy, seeing aggregated metrics for millions/billions of DOs fast is very difficult

B

Burrito•2/5/25, 6:08 PM

Yeah that's understandable, but I would've thought there's a way to do it locally, since the runtime is now open source and all that.

BBurrito The request results in 236 row reads in DO metrics, but worker logs only show 5 ...

B

Burrito•2/5/25, 6:09 PM

I'm trying to debug this issue and it's really not helping that I need to wait a long time before I can observe what had happened.

BBurrito Yeah that's understandable, but I would've thought there's a way to do it locall...

H

Hard@Work•2/5/25, 6:09 PM

Ooh, ok. I'm not entirely sure, is the DO storage engine actually open source?

H

Hard@Work•2/5/25, 6:10 PM

I don't remember if it uses a stub within Miniflare

B

Burrito•2/5/25, 6:10 PM

Ah not sure then.

B

Burrito•2/5/25, 6:19 PM

Well I've tried a reproduction, starting from

create cloudflare

create cloudflare

create cloudflare

create cloudflare template with modified code, SQLite backend:

import { DurableObject } from 'cloudflare:workers';

export class MyDurableObject extends DurableObject<Env> {
    constructor(ctx: DurableObjectState, env: Env) {
        super(ctx, env);

        ctx.blockConcurrencyWhile(async () => {
            await ctx.storage.transaction(async () => {
                const version = await ctx.storage.get('migrationVersion');
                if (version === 1) return;

                ctx.storage.sql.exec(`
                    CREATE TABLE Configs (
                        key TEXT NOT NULL,
                        value TEXT NOT NULL,
                        PRIMARY KEY (key)
                    )
                    STRICT;
                `);
                ctx.storage.sql.exec(`INSERT INTO Configs (key, value) VALUES (?, ?);`, 'foo', 'bar');

                await ctx.storage.put('migrationVersion', 1);
            });
        });
    }

    async sayHello(key: string): Promise<string> {
        const { value } = this.ctx.storage.sql.exec(`SELECT value FROM Configs WHERE key = ?;`, key).one();

        return `Hello, ${value}!`;
    }
}

export default {
    async fetch(request, env, ctx): Promise<Response> {
        const url = new URL(request.url);
        if (url.pathname !== '/hello-world') return new Response(null, { status: 404 });

        let id: DurableObjectId = env.MY_DURABLE_OBJECT.idFromName('hello-world');
        let stub = env.MY_DURABLE_OBJECT.get(id);
        let greeting = await stub.sayHello('foo');
        return new Response(greeting);
    },
} satisfies ExportedHandler<Env>;

import { DurableObject } from 'cloudflare:workers';

export class MyDurableObject extends DurableObject<Env> {
    constructor(ctx: DurableObjectState, env: Env) {
        super(ctx, env);

        ctx.blockConcurrencyWhile(async () => {
            await ctx.storage.transaction(async () => {
                const version = await ctx.storage.get('migrationVersion');
                if (version === 1) return;

                ctx.storage.sql.exec(`
                    CREATE TABLE Configs (
                        key TEXT NOT NULL,
                        value TEXT NOT NULL,
                        PRIMARY KEY (key)
                    )
                    STRICT;
                `);
                ctx.storage.sql.exec(`INSERT INTO Configs (key, value) VALUES (?, ?);`, 'foo', 'bar');

                await ctx.storage.put('migrationVersion', 1);
            });
        });
    }

    async sayHello(key: string): Promise<string> {
        const { value } = this.ctx.storage.sql.exec(`SELECT value FROM Configs WHERE key = ?;`, key).one();

        return `Hello, ${value}!`;
    }
}

export default {
    async fetch(request, env, ctx): Promise<Response> {
        const url = new URL(request.url);
        if (url.pathname !== '/hello-world') return new Response(null, { status: 404 });

        let id: DurableObjectId = env.MY_DURABLE_OBJECT.idFromName('hello-world');
        let stub = env.MY_DURABLE_OBJECT.get(id);
        let greeting = await stub.sayHello('foo');
        return new Response(greeting);
    },
} satisfies ExportedHandler<Env>;

import { DurableObject } from 'cloudflare:workers';

export class MyDurableObject extends DurableObject<Env> {
    constructor(ctx: DurableObjectState, env: Env) {
        super(ctx, env);

        ctx.blockConcurrencyWhile(async () => {
            await ctx.storage.transaction(async () => {
                const version = await ctx.storage.get('migrationVersion');
                if (version === 1) return;

                ctx.storage.sql.exec(`
                    CREATE TABLE Configs (
                        key TEXT NOT NULL,
                        value TEXT NOT NULL,
                        PRIMARY KEY (key)
                    )
                    STRICT;
                `);
                ctx.storage.sql.exec(`INSERT INTO Configs (key, value) VALUES (?, ?);`, 'foo', 'bar');

                await ctx.storage.put('migrationVersion', 1);
            });
        });
    }

    async sayHello(key: string): Promise<string> {
        const { value } = this.ctx.storage.sql.exec(`SELECT value FROM Configs WHERE key = ?;`, key).one();

        return `Hello, ${value}!`;
    }
}

export default {
    async fetch(request, env, ctx): Promise<Response> {
        const url = new URL(request.url);
        if (url.pathname !== '/hello-world') return new Response(null, { status: 404 });

        let id: DurableObjectId = env.MY_DURABLE_OBJECT.idFromName('hello-world');
        let stub = env.MY_DURABLE_OBJECT.get(id);
        let greeting = await stub.sayHello('foo');
        return new Response(greeting);
    },
} satisfies ExportedHandler<Env>;

import { DurableObject } from 'cloudflare:workers';

export class MyDurableObject extends DurableObject<Env> {
    constructor(ctx: DurableObjectState, env: Env) {
        super(ctx, env);

        ctx.blockConcurrencyWhile(async () => {
            await ctx.storage.transaction(async () => {
                const version = await ctx.storage.get('migrationVersion');
                if (version === 1) return;

                ctx.storage.sql.exec(`
                    CREATE TABLE Configs (
                        key TEXT NOT NULL,
                        value TEXT NOT NULL,
                        PRIMARY KEY (key)
                    )
                    STRICT;
                `);
                ctx.storage.sql.exec(`INSERT INTO Configs (key, value) VALUES (?, ?);`, 'foo', 'bar');

                await ctx.storage.put('migrationVersion', 1);
            });
        });
    }

    async sayHello(key: string): Promise<string> {
        const { value } = this.ctx.storage.sql.exec(`SELECT value FROM Configs WHERE key = ?;`, key).one();

        return `Hello, ${value}!`;
    }
}

export default {
    async fetch(request, env, ctx): Promise<Response> {
        const url = new URL(request.url);
        if (url.pathname !== '/hello-world') return new Response(null, { status: 404 });

        let id: DurableObjectId = env.MY_DURABLE_OBJECT.idFromName('hello-world');
        let stub = env.MY_DURABLE_OBJECT.get(id);
        let greeting = await stub.sayHello('foo');
        return new Response(greeting);
    },
} satisfies ExportedHandler<Env>;

I've sent two requests far enough apart to make sure they show up separately on dashboard, and first request shows 5 rows read + 8 rows written, second request shows 8 rows read + 0 row written.

B

Burrito•2/5/25, 6:21 PM

It's not as outlandish as my actual project, but these numbers still don't make any sense. I fail to see how this code can result in that many reads or writes.

J

James•2/5/25, 6:32 PM

i bet its something goofy w the psuedo KV api on Sqlite since its abstracting the real SQL away for you

B

Burrito•2/5/25, 6:59 PM

Okay we are getting somewhere, if you replace the migration with:

CREATE TABLE Configs (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config1 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config2 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

-- Config3, Config4, Config5

CREATE TABLE Configs (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config1 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config2 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

-- Config3, Config4, Config5

CREATE TABLE Configs (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config1 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config2 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

-- Config3, Config4, Config5

CREATE TABLE Configs (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config1 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

CREATE TABLE Config2 (
    key TEXT NOT NULL,
    value TEXT NOT NULL,
    PRIMARY KEY (key)
)
STRICT;

-- Config3, Config4, Config5

Where

Config1

Config1

Config1

Config1 to

Config5

Config5

Config5

Config5 are all useless tables, and keeping rest of the code the same.
Now repeat the same 2 requests:

First request: 8 written, 5 read.
Second request: 0 written, 28 read.

B

Burrito•2/5/25, 7:00 PM

@lambrospetrou ^ Is the above to be expected?

I

iLearn•2/5/25, 7:09 PM

Need help (in my design) :

Facts :
F#1 : I shall have 1 Class, say UserOTPStore. This will will store 6-digit OTP as Key and Timestamp (of expiration) as Value.
F#2 : User shall be identified by "mobile number"

Implementation :

User requests OTP. Create DO with "mobile_number" and store OTP and Timestamp inside respective DO.

Please confirm my understanding for following Qs :

Q#1 : How to fetch ALL user for whom DOs has been created (with persistent data) ?
A#1 : Use DO-API : "/accounts/{account_id}/workers/durable_objects/namespaces/{id}/objects"

Q#2 : How to fetch data stored for ALL Users :
A#2 : Loop through the list of DO-IDs received from A#1, and call stub for each DO-ID (assume a function dump_stored_data() is part of the UserOTPStore Class)

Q#3 : How to clean-up / delete ALL Users data ? (basically, remove everything related to DO UserOTPStore Class)
A#3 : NO simple way. Need to use approach for A#2.
PLEASE suggest a simple O(1) operation, (as approach taken for delete based on A#2 will be really costly)

Thanks in advance for kind consideration in answering/confirming my Qs.

BBurrito Okay we are getting somewhere, if you replace the migration with: ```sql CREATE ...

J

James•2/5/25, 7:37 PM

Thats so bizarre, what if you vary this one more time, and take out the migration get/put.

IiLearn Need help (in my design) : Facts : F#1 : I shall have 1 Class, say UserOTPStor...

J

James•2/5/25, 7:39 PM

you're better off storing a reference to the stubs centrally in another store/D1/DO/whatever rather than using the DO-API.

JJames you're better off storing a reference to the stubs centrally in another store/D1...

I

iLearn•2/5/25, 7:44 PM

Ok. Thanks for suggestion.
Any reason for not encouraging to use DO-API ? (actually want to offload as much as possible to platform , instead of house-keeping self)

J

James•2/5/25, 7:48 PM

lots of reasons, the DO API is weak if you want to actually find specific DOs and not iterate over all the things, you're better of keeping central ref plus useful business data, in your case like, i dont know, lastSMSTs or something

I generally keep DO data contained in the DO if it supports the DO user/client directly in thier specific isolated tasks, and anything system related, gets kept a level above the DO for central management, cleanup, aggregation etc.

JJames lots of reasons, the DO API is weak if you want to actually find specific DOs an...

I

iLearn•2/5/25, 7:52 PM

Ahh. Nice Point and makes sense.
QQ : (my Q#3 above) How to clean-up / delete ALL Users data ? (basically, remove everything related to DO UserOTPStore Class) ?

JJames Thats so bizarre, what if you vary this one more time, and take out the migratio...

B

Burrito•2/5/25, 7:56 PM

I don't think that matters anyways, since the migration code won't run after the first request.

B

Burrito•2/5/25, 7:58 PM

The more bizarre part is that row read somehow scales with the amount of tables, in the first reproduction there were 2 tables (

Configs

Configs

Configs

Configs, and the hidden KV API table) and second request costs 8 reads, in the second reproduction there were 7 tables (

Configs

Configs

Configs

Configs,

Config1

Config1

Config1

Config1 to

Config5

Config5

Config5

Config5, and hidden KV API table) and the second request costs 28 reads.

B

Burrito•2/5/25, 7:58 PM

This seems like a bug to me.

BBurrito I don't think that matters anyways, since the migration code won't run after the...

J

James•2/5/25, 8:01 PM

id still try it, there is 1 get, and who knows if the get is doing something funny w schema reads

B

Burrito•2/5/25, 8:03 PM

Sure I'll try it after this. It'd be much easier to test these things if it didn't take half an hour to do one test because things show up so slowly on dashboard.

JJames id still try it, there is 1 get, and who knows if the get is doing something fun...

B

Burrito•2/5/25, 8:52 PM

Nope, removing the migration code afterwards drops the rows read from 28 to 27.

B

Burrito•2/5/25, 8:53 PM

There's something weird going on.

B

Burrito•2/5/25, 10:42 PM

I've completely removed KV API from my SQLite DO, and it's still showing 234 row reads in dashboard metrics, despite logging shows the 6 queries only have 1 row read each. There is something very wrong with either the metrics, or how row read/written are counted.

IiLearn Ok. Thanks for suggestion. Any reason for not encouraging to use DO-API ? (actua...

L

lambrospetrouOP•2/5/25, 11:04 PM

If you use DOs, you should not need often to iterate all of them. It defeats the purpose of using DOs in the first place.
For the OTP example, if you want to offload OTP expiration to the platform, and deletion, etc, why not just use Workers KV and be done with it?
Set your expiration and let everything be deleted. OTP codes are immutable, so you write a key like

<mobile-phone>-<OTP>

<mobile-phone>-<OTP>

<mobile-phone>-<OTP>

<mobile-phone>-<OTP> and value being the timestamp or whatever info you want. When the request comes in you can check the specific OTP if it exists or not indicating validity, and search by prefix the phone number.

If that satisfies your needs, don't use DOs, since your access patterns requiring ALL DOs data is very bad for cost and performance.

Also, as others mentioned before, usually it's good to do some bookkeeping of DOs created in some other DOs so that you can do faster search/listing of them. Read https://developers.cloudflare.com/reference-architecture/diagrams/storage/durable-object-control-data-plane-pattern/ for more details on that.

B

Burrito•2/6/25, 12:44 AM

@lambrospetrou Apologize for pinging, regarding the issue with SQLite storage operations being overcounted, do you have any idea what could be the cause? How should I get this issue to be looked at by Cloudflare? Developers getting charged with these inaccurate counts (over 10x more) is not exactly a light issue.

You can reproduce it by using

npm create cloudflare@latest

npm create cloudflare@latest

npm create cloudflare@latest

npm create cloudflare@latest hello world template with DO, change to SQLite backend, with the following code: https://gist.github.com/NonSpicyBurrito/f4938faa7a779574d9af945d012f6703. Deploy, send two requests far enough apart so they can show up separately on dashboard, and observe DO metrics:

First request where migration occurs: 5 rows read, 10 rows written.
Second request: 26 rows read, 0 row written.
The second request should never result in 26 rows read, there are only 2 rows in the entire database. And the more irrelevant tables you add, the more row reads it counts.

1

1984 Ford Laser•2/6/25, 7:00 AM

Do SQL triggers work in DOs? I swear someone posted that they'd gotten it working recently

SQLite supports bytes, so you could store anything if you convert it to bytes, or just serialize the

Similar Threads

Similar Threads

Similar Threads