Cloudflare Developers•3y ago

Whilst I’m not at my computer to test, the meta object returned should have rows_read and rows_writt

Whilst I’m not at my computer to test, the meta object returned should have rows_read and rows_written that tell you

Kkian Whilst I’m not at my computer to test, the meta object returned should have rows...

GuillaumeLakano•11/5/23, 7:10 PM

Oh good point, I'll try, thank you!

GuillaumeLakano•11/5/23, 8:08 PM

From my tests, I'm surprised because the rows_read / rows_written are not what I thought:

1) An INSERT is indeed 1 write, but also a read cost
2) More surprising, an « INSERT ... RETURNING * » is 2 writes + 2 reads
3) A DROP TABLE is 3 read and 0 write
4) A CREATE TABLE is 1 read + 2 writes
5) A SELECT from an empty table is 1 read

Do you known if it's the correct expected billing please ?

Frank Walsh•11/5/23, 10:06 PM

Has anyone successfully applied migration in cloudflare CI/CD or GitHub?

In cloudlflare it seems like the command executes in my build.sh file but nothing happens.

In GitHub I get a generic bash exits with error code 1

Frank Walsh•11/5/23, 10:11 PM

The interesting thing about the GitHub error is initially I got an error stating that CLOUDFLARE_API_TOKEN had to be defined as an environment variable. After defining that is when I get the generic error

Original message was deleted

GuillaumeLakano•11/5/23, 11:23 PM

This could explains the read cost, but not the multiples writes from a CREATE TABLE or a INSERT/RETURNING

GGuillaumeLakano From my tests, I'm surprised because the rows_read / rows_written are not what I...

Unsmart•11/6/23, 12:16 AM

The metadata there is correct but also an INSERT will not always be only 1 write... it depends on the number of indexes because it has to write to each index. Not sure why insert returning would add another write there though

kw•11/6/23, 12:53 AM

how can we use transaction in D1 to avoid race condition? my use case is I'll read some data and write based on the data I read. how to avoid dirty read in this case? db.batch() seems not useful here

kw•11/6/23, 1:06 AM

something like

SELECT FOR UPDATE

SELECT FOR UPDATE

GGuillaumeLakano From my tests, I'm surprised because the rows_read / rows_written are not what I...

Matt Silverlock•11/6/23, 1:12 AM

These all seem reasonable but it will also depend on your index configuration. Also keep in mind the first 25 billion reads are included.

MMatt Silverlock These all seem reasonable but it will also depend on your index configuration. A...

GuillaumeLakano•11/6/23, 7:51 AM

I totally agree that the pricing includes a generous number of free reads. There is also free writes operations, it's really appreciated.

As the write cost is 1,000 time bigger than read cost, we trying to estimate our bill after the free part. It's why I've done theses checks of rows_reads/rows_written on some SQL operations and I'm surprised on some costs.

For example we'll remove all our RETURNING keywords from our SQL statements because we had stupidly thought that this will cause only an additional read, and not an additional write.

Pascale•11/6/23, 11:20 AM

I'm struggling to use any of the two D1 exporters due to the error: "D1 Error: Isolate Has exceeded Memory Size". Anyone have a pointer to get a whole backup from large databases? Tuning the respective body size limits etc. didnt help

kianOP•11/6/23, 11:24 AM

Which exporter are you using, https://github.com/nora-soderlund/cloudflare-d1-backups?

Pascale•11/6/23, 11:24 AM

yeah, I tried this one and the fork

Pascale•11/6/23, 11:26 AM

the reasoning behind that is that the app is currently structured in a way to only interact with one database, but I hit the 2GB limit pretty fast, so now I try to pool the data over multiple databases to work around that.

the backup should help me migrate my data

__tweak^prone Does `wrangler dev` connect to my external Cloudflare D1 if i specify it in the ...

JustinNoel•11/6/23, 12:10 PM

PSA:

You can access your local D1sqlite data base by going into the follow in your source directory:

.wrangler -> state -> v3 -> d1 -> miniflare-D1DatabaseObject -> xxxxxxxxxx.sqlite

.wrangler -> state -> v3 -> d1 -> miniflare-D1DatabaseObject -> xxxxxxxxxx.sqlite

Just use any SQLite GUI tool and and open that file.

Note: If you use any database change commands to alter the structure of the database, you will not see those changes in your current GUI. You have to close the GUI connection and open the file again.

Arda Acar•11/6/23, 12:43 PM

How can I export d1 database as .sql with wrangler?

AArda Acar How can I export d1 database as .sql with wrangler?

Max (@rozenmd)•11/6/23, 12:57 PM

with wrangler, you can't -

alpha

alpha

databases only let you download .sqlite backups, and

beta

beta

databases don't let you download backups yet

MMax (@rozenmd)with wrangler, you can't - `alpha` databases only let you download .sqlite backu...

Arda Acar•11/6/23, 1:45 PM

Thanks for the answer. Will it be possible to back up the .sql database when it comes out of beta?

AArda Acar Thanks for the answer. Will it be possible to back up the .sql database when it ...

Max (@rozenmd)•11/6/23, 1:49 PM

Probably not as a means of backup, since we have point-in-time recovery now: https://developers.cloudflare.com/d1/learning/time-travel/

but we're aware folks want to be able to export their databases at any time, and it's in our backlog to make that possible

MMax (@rozenmd)Probably not as a means of backup, since we have point-in-time recovery now: htt...

Arda Acar•11/6/23, 1:50 PM

thanks for your reply and your interest

Cretezy•11/6/23, 3:37 PM

@nora8008 @examo I've fixed the

LIMIT

LIMIT

issue with v0.2.0 of my exporter: https://github.com/Cretezy/cloudflare-d1-backup/commit/dffcd9fef0224593029add123244acef6b1afd3c

GitHub

v0.2.0: Add --limit option to add LIMIT to each SELECT · Cretezy/cl...

Also add formatting/Prettier.

Pascale•11/6/23, 4:25 PM

you are amazing

wadefletch•11/6/23, 7:43 PM

Can I write tests like this, using local D1 test db with Hono? https://developers.cloudflare.com/d1/learning/local-development/#usage-example

Local development · Cloudflare D1 docs

D1 has fully-featured support for local development, running the same version of D1 as Cloudflare runs globally. Local development uses Wrangler, the …

kianOP•11/6/23, 8:01 PM

Yes.

Original message was deleted

Matt Silverlock•11/6/23, 8:15 PM

What about an ordered index on the column you want sorted?

kianOP•11/6/23, 8:26 PM

better D1 execute errors, coming soon to a Wrangler near you

Kkian better D1 execute errors, coming soon to a Wrangler near you

Hello, I’m Allie!•11/6/23, 8:34 PM

Need to make WorkerLLaMa parse the errors and return a readable fix

kianOP•11/6/23, 8:35 PM

Village (Josef)•11/7/23, 12:44 AM

Hey, what alter table functionality does D1 support? I can't find any docs on it.

Village (Josef)•11/7/23, 12:44 AM

I'm hoping all the alter/drop/rename/add column stuff in a Function and everything?

VVillage (Josef)I'm hoping all the alter/drop/rename/add column stuff in a Function and everythi...

Isaac McFadyen•11/7/23, 1:02 AM

Any SQL that can be executed from Wrangler or the dashboard can be executed from a Function as well, yeah.

Isaac McFadyen•11/7/23, 1:03 AM

Any DDL or DML - so CREATE/DROP/ALTER TABLE, any inserts, deletes, querying, etc will work from them.

Isaac McFadyen•11/7/23, 1:05 AM

D1 should support most (not all - no transactions, only bulk statements) statements that SQLite supports

Village (Josef)•11/7/23, 1:32 AM

Sweet, thank you!

Village (Josef)•11/7/23, 1:33 AM

Couldn’t find any docs anywhere with what it actually supports.

Bamboo•11/7/23, 6:17 AM

Billing Doubt
I binded my d1 database to a worker function. And whenever I fetch worker url, it provides me with some data, like an api.

So now, the doubt is, whenever i fetch data, will I be charged for both worker and d1 usage or just d1 usage?

BBamboo **Billing Doubt** I binded my d1 database to a worker function. And whenever I f...

Max (@rozenmd)•11/7/23, 8:37 AM

you're billed for the tiny fraction of time it takes to serialise the d1 response in the Worker + whatever the d1 usage was

Mathieu•11/7/23, 12:57 PM

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 )

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

Whilst I’m not at my computer to test, the meta object returned should have rows_read and rows_writt

Similar Threads