Hey! As part of the development of a new service, we would like to consider the use of storage servi

Hey! As part of the development of a new service, we would like to consider the use of storage services offered by Cloudflare. Although D1 is in beta, we want to carry out a POC to see if it can meet our needs in the future. I would like informed user feedback to confirm or not the assumptions we make about how D1 works.

Our needs are :

Store the data based on the user locations
We want to ensure availability to avoid down times
We want to be able to scale horizontally

To achieve theses needs, we hesitated between Durable Object and D1. Durable Objects are interesting because they meet our needs in terms of storing data close to the user. Unfortunately, we rule out their use because it would not allow us to obtain the list of all our objects without having to store the ids of each durable object created. Se we want to consider D1 but feel free to contradict me if this approach seems wrong.

Our guesses on what’s possible with Cloudflare D1:

Create one or more D1 databases for each Location Hints -> Our question, are location hints respected for sure?
Our object IDs are random but we voluntary add information related to the database that stores them
For sharing, Is a nice approach planned in the backlog at Cloudflare? As it stands, we would obtain an in-house solution where we add in the id of the objects, the location hints + the number of the db and thus, this allows us to scale up to 50,000 (current limit) Databases D1 worldwide. It requires work on our side and therefore, we were wondering if this kind of features was planned in the future?
How is high availability ensured? Is a D1 database hosted in a single location? In this case, what happens if the data center has anomalies? Can other data centers take over the D1 database or is it impacted and will no longer be accessible?

I am aware that the answer to these questions may evolve over time, but I would be interested in obtaining feedback.

Thanks !

MMathieu Hey! As part of the development of a new service, we would like to consider the ...

GuillaumeLakano•11/7/23, 1:55 PM

About DO, you can uses the login ID of your user to generate the DO uuid => let id = OBJECT_NAMESPACE.idFromName(login)
But, if the login ( sometime an email ) changes, you need a plan to move your old DO data to the new one.
Also, keep in mind the DO will be at one place in the world for R/W, rather than D1 that will be still true for Writes, but the Reads should be sometimes replicated from where it's used ( not yet the case, and subject to changes )

MathieuOP•11/7/23, 2:18 PM

Thank you Guillaume! The problem with durable objects is that it doesn’t allow us to get a reliable list of all existing object ids without having to maintain a list in another storage space.

Concerning D1, if I understand correctly, it is planned for the future to replicate the data for reading? In this case, there may be delays for the cache to be invalidated when data is updated? And therefore, we risk ending up with a situation similar to KV, where the consistency is not strong and where we have to wait for a propagation delay?

MMathieu Thank you Guillaume! The problem with durable objects is that it doesn’t allow u...

GuillaumeLakano•11/7/23, 2:24 PM

You also can uses the API to list all your DO objects ( https://developers.cloudflare.com/api/operations/durable-objects-namespace-list-objects )
It's free usage but usage with limits / second ( if I remember correctly, may be 5 / second )

For D1, I don't think they communicate about how the replication will exactly works, and about delay, so it's only speculation for now, until someone from CF could tell you more

Cloudflare API Documentation

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

Paul•11/7/23, 4:08 PM

does d1 support delete and return record?

PPaul does d1 support delete and return record?

GuillaumeLakano•11/7/23, 4:09 PM

Yes: https://www.sqlite.org/lang_returning.html
Be careful, this cost 2 reads + 2 writes

Paul•11/7/23, 4:10 PM

it's also kinda working half the time

Paul•11/7/23, 4:11 PM

DELETE FROM verification_token where token = ?1 RETURNING *DELETE FROM verification_token where token = ?1 RETURNING *

Paul•11/7/23, 4:11 PM

does that look right?

Paul•11/7/23, 4:11 PM

token is a uuid v4 string

Paul•11/7/23, 4:12 PM

i'm trying to figure out how it could be deleting the token, but at the same time not returning any results

Paul•11/7/23, 4:12 PM

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];

const result = await this.db
            .prepare('DELETE FROM verification_token WHERE token = ?1 RETURNING *')
            .bind(token)
            .run();
        if (result.error) {
            return { data: null, error: Error(`failed to delete from db: ${result.error}`) };
        }
        if (result.results.length <= 0) {
            return { data: null, error: null };
        }
        const vToken = result.results[0];

GuillaumeLakano•11/7/23, 4:13 PM

This seems to be the good syntax yes

Paul•11/7/23, 4:14 PM

Weird how result.results.length <= 0result.results.length <= 0 is TRUE on production

Paul•11/7/23, 4:14 PM

even tho it deletes the token

kian•11/7/23, 4:14 PM

Because run()run() does not return resultsresults

kian•11/7/23, 4:14 PM

https://developers.cloudflare.com/d1/platform/client-api/#await-stmtrun

Cretezy•11/7/23, 4:15 PM

Would it be possible to add my and @nora's exporters to https://developers.cloudflare.com/d1/platform/community-projects/ ?

https://github.com/Cretezy/cloudflare-d1-backup
https://github.com/nora-soderlund/cloudflare-d1-backups

Kkian Because `run()` does not return `results`

Paul•11/7/23, 4:16 PM

How are you getting your results from queries?

PPaul How are you getting your results from queries?

kian•11/7/23, 4:17 PM

Use another method, like all()all() or first()first().

Paul•11/7/23, 4:18 PM

wow, thats insane. so my intellisense is just wrong

Paul•11/7/23, 4:18 PM

or was this a recent change?

Paul•11/7/23, 4:19 PM

and how the heck are all my other queries working then

Paul•11/7/23, 4:19 PM

haha

Paul•11/7/23, 4:19 PM

i use run() everywhere

Paul•11/7/23, 4:19 PM

maybe because they are simple SELECT statements?

kian•11/7/23, 4:32 PM

run()run() should throw an error if it returns results.

kian•11/7/23, 4:33 PM

Well, it did in Wrangler 3.1.1. Seems like it doesn't in 3.15.0.

kian•11/7/23, 4:34 PM

@rozenmd is the documentation wrong about run()run()? It currently does exactly the same as all()all().

Paul•11/7/23, 4:36 PM

yep that exactly. It looks like my problem is unrelated to D1. It seems the worker function is running twice, so the first one is eating the verification_token, and the second run fails.

Max (@rozenmd)•11/7/23, 4:56 PM

i don't recall us changing run()run()'s behaviour tbh

johnjoz•11/7/23, 6:24 PM

D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with wrangler fails with mysterious node errors, and I’ve followed the sundry blog examples. I can rclone to r2 with impunity. Is there any facility to load a d1 sqlite db from r2?

MMax (@rozenmd)i don't recall us changing `run()`'s behaviour tbh

kian•11/7/23, 6:28 PM

I've tried it in local & remote development and run()run() returns resultsresults.

kian•11/7/23, 6:28 PM

In Wrangler 3.1.1, you'd get an error thrown saying "Execute returned results" or something similar

kian•11/7/23, 6:29 PM

d1-database

kian•11/7/23, 6:29 PM

kian•11/7/23, 6:33 PM

Miniflare used to check on it's own and throw (hence why I had the issue on an old Wrangler version) but now the API seems to send results rather than an error.

Jjohnjoz D1 workers paid noob possible: trying to upload a roughly 1.5 gb database with w...

Max (@rozenmd)•11/7/23, 8:16 PM

you might be overloading the db - what exactly are the errors?

johnjoz•11/7/23, 8:57 PM

% wrangler d1 execute ipbistrosql --file ./searchtree.sql
FATAL ERROR: v8::ToLocalChecked Empty MaybeLocal

johnjoz•11/7/23, 8:57 PM

Plus many more nondescript lines from v8 and node

johnjoz•11/7/23, 8:59 PM

If i copy the sql file and truncate at a hundred lines that works as in blogs. But of course is useless. The sql file (generated by dump from sqlite3 file tested locally) is about 21 million lines. The db is about 1.5 gb.

johnjoz•11/7/23, 9:06 PM

Perhaps I should rclone the sql file to r2 (i can do that no problem) then write a worker which loads from r2 into d1, but it seems i hit some unintended unchecked limit inside a node dependency or v8 itself (on which i was long ago a contributor via a qualcomm google joint effort)

Jjohnjoz % wrangler d1 execute ipbistrosql --file ./searchtree.sql FATAL ERROR: v8::ToLoc...