Keep in mind visits != D1 queries but still, D1 can easily handle it, yes 😄

Keep in mind visits != D1 queries but still, D1 can easily handle it, yes

akashra•10/1/24, 12:44 AM

It's not about whether or not it's hard to set up. It's the ability to actually generate reports that give you trends over time and across rolling windows, triggers for events above certain thresholds are extremely difficult (or impossible) by comparison, and even just doing searches on certain criteria. I haven't been in that role that used it for 6 months now so can't really go back and find or even remember specific examples anymore - but do recall I haaaaaated it.

DDeleted User feel free, literally, to use 5 million reads per day

akashra•10/1/24, 12:46 AM

Yeah, for single-table lookups it's good. If you start doing joins, especially on one to manys, it can add up quickly.
I cache a lot of data in json objects to avoid hits on the DB - so I'll do stuff like write a cached object and last-updated time every time I do an update. That allows me to mostly just read the full json data rather than joined data with where clauses that would ordinarily be on those joins, and just return them for the worker (or client) to filter out what is needed (based on security/exposed data).

�

𝓈𝒽𝓎•10/1/24, 11:51 AM

can someone help me, how am I supposed to make code like this work without transactions?

sharedDB.batch([
        sharedDB.insert(usersTable).values({
            publicId: nanoid(),
            name: googleUser.name,
            email: googleUser.email
        }).onConflictDoNothing()
            .returning(),
        sharedDB.insert(oauthAccountsTable).values({
            providerId: "google",
            providerUserId: googleUser.sub,
            userId:// !!! no id
        })
    ])

sharedDB.batch([
        sharedDB.insert(usersTable).values({
            publicId: nanoid(),
            name: googleUser.name,
            email: googleUser.email
        }).onConflictDoNothing()
            .returning(),
        sharedDB.insert(oauthAccountsTable).values({
            providerId: "google",
            providerUserId: googleUser.sub,
            userId:// !!! no id
        })
    ])

senojsitruc•10/1/24, 12:32 PM

Hey. I have a question about row metrics. The metrics showed a lot more writes than I expected, so I started looking more closely at the response meta. This one query tries to do an insert, and if there's a conflict on the primary key, it updates the row instead. It shows one read and eight (?!) writes. Am I interpreting the meta correctly? And is this the expected result? Thank you!

Ssenojsitruc Hey. I have a question about row metrics. The metrics showed a lot more writes t...

Sam•10/1/24, 12:40 PM

Do you have indexes?

SSam Do you have indexes?

senojsitruc•10/1/24, 12:40 PM

Ah! Yes, I do.

Ssenojsitruc Ah! Yes, I do.

Sam•10/1/24, 12:40 PM

Each index should be a write

SSam Each index should be a write

senojsitruc•10/1/24, 12:41 PM

That is obvious and it hadn't occurred to me. Thank you.

�𝓈𝒽𝓎 can someone help me, how am I supposed to make code like this work without trans...

lambrospetrou•10/1/24, 1:22 PM

All queries inside each

batch

batch

call are executed inside a single transaction: https://developers.cloudflare.com/d1/build-with-d1/d1-client-api/#batch-statements

Batched statements are SQL transactions . If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

lucasp•10/1/24, 1:34 PM

hello someone can help me. How i can migrate with diff about my remote d1??

npx prisma migrate diff   --from-url ????here remote d1  --to-schema-datamodel ./prisma/schema.prisma   --script   --output migrations/0002_second.sql

npx prisma migrate diff   --from-url ????here remote d1  --to-schema-datamodel ./prisma/schema.prisma   --script   --output migrations/0002_second.sql

Llambrospetrou All queries inside each `batch` call are executed inside a single transaction: h...

Sam•10/1/24, 1:45 PM

I think he 's asking how he can get the userId from the first insert statement, and then use it in the second insert statement within the same batch/transaction.

Guessing that's not possible, right?

cc @Shy

SSam I think he 's asking how he can get the userId from the first insert statement, ...

lambrospetrou•10/1/24, 1:48 PM

You could maybe make your second query to do a SELECT on the UsersTable based on the

googleUser.email

googleUser.email

and use that in your INSERT.

SSam I think he 's asking how he can get the userId from the first insert statement, ...

�

𝓈𝒽𝓎•10/1/24, 1:53 PM

yeah

�

𝓈𝒽𝓎•10/1/24, 1:55 PM

this is the endpoint that creates the user so I can't create a user outside a transcation then add it to the oauth table, what if the 2 statment fails and i cannot select it before because it might be a new user

�

𝓈𝒽𝓎•10/1/24, 1:55 PM

idk what do in this situation

lambrospetrou•10/1/24, 1:59 PM

I am saying do both INSERTs in the same

batch()

batch()

, but your second INSERT should do a nested SELECT to figure out the user ID that was added in the first insert.

Also, actually in your case above, just move the

nanoid()

nanoid()

into a local variable before you create your statements, and use that in both queries. Isn't that nanoid what you want in the second insert or is it an autoincrement key?

�

𝓈𝒽𝓎•10/1/24, 2:01 PM

the nanoid is just a public_id, i'm using autoincrementing ideas

Llambrospetrou I am saying do both INSERTs in the same `batch()`, but your second INSERT should...

�

𝓈𝒽𝓎•10/1/24, 2:02 PM

but that first suggestion seems solid never did that before I wonder why it was never mentioned anyway, I'll give it a try

�

𝓈𝒽𝓎•10/1/24, 2:17 PM

await sharedDB.batch([
        sharedDB.insert(usersTable).values({
            publicId: nanoid(),
            name: googleUser.name,
            email: googleUser.email
        }).onConflictDoNothing()
            .returning(),
        sharedDB.insert(oauthAccountsTable).values({
            providerId: "google",
            providerUserId: googleUser.sub,
            userId: await sharedDB.select({ id: usersTable.id }).from(usersTable).where(eq(usersTable.email,       googleUser.email)).get().then((res) => res?.id!),
        })
    ])

await sharedDB.batch([
        sharedDB.insert(usersTable).values({
            publicId: nanoid(),
            name: googleUser.name,
            email: googleUser.email
        }).onConflictDoNothing()
            .returning(),
        sharedDB.insert(oauthAccountsTable).values({
            providerId: "google",
            providerUserId: googleUser.sub,
            userId: await sharedDB.select({ id: usersTable.id }).from(usersTable).where(eq(usersTable.email,       googleUser.email)).get().then((res) => res?.id!),
        })
    ])

is that how it is intended to be used?

�𝓈𝒽𝓎 ``` await sharedDB.batch([ sharedDB.insert(usersTable).values({ ...

lambrospetrou•10/1/24, 2:36 PM

I don't think this is right. The

.get().then((res) => res?.id!)

.get().then((res) => res?.id!)

part needs the query to actually run I imagine. I am not familiar with this framework you are using for constructing your queries.

Oo7 5000 requests per day is light work for D1

Juan•10/1/24, 3:26 PM

Thank you!

IIsaac McFadyen Keep in mind visits != D1 queries but still, D1 can easily handle it, yes 😄

Juan•10/1/24, 3:27 PM

Yeah haha, thank you!

Original message was deleted

o7•10/2/24, 5:56 AM

dynamic d1 database bindings are being worked on. but this functionality currently does exist with SQLite on DO (with lower storage limits for now before they match d1 limits)

�

😈 Donkey 💫•10/2/24, 9:15 AM

I wonder if CF has any plan for Document Database (no-sql) ?

�😈 Donkey 💫I wonder if CF has any plan for Document Database (no-sql) ?

lambrospetrou•10/2/24, 9:35 AM

Out of curiosity, with Durable Objects having a KV API, and now an SQL API, what would you get out of a document API? I guess a library could provide this ontop of the SQLite API, but I am wondering if you have something else in mind that could be provided except the API itself.

Llambrospetrou Out of curiosity, with Durable Objects having a KV API, and now an SQL API, what...

�

😈 Donkey 💫•10/2/24, 10:25 AM

After doing quick research, SQL api is promising

�

😈 Donkey 💫•10/2/24, 10:25 AM

I'm thinking about Document DB because of native ORM, UI dashboard (expect), manually set index,..

dangoldin•10/2/24, 2:51 PM

probably a dumb question but is there a way for me to query a D1 database that's running locally/dev mode? i set up a standard sqlite driver in datagrip with the path in .wrangler and the queries work and tables have the right structure but no data is returned. the app is reading the data just fine. i wonder if it's due to the wal/shm files not being compacted into the main sqlite file?

Ddangoldin probably a dumb question but is there a way for me to query a D1 database that's...

lambrospetrou•10/2/24, 3:58 PM

You can get to the raw sqlite files under

.wrangler

.wrangler

, but don't mess them up outside of your worker code to avoid unexpected behavior

If things break, you can clean up and reset by deleting those files.

Llambrospetrou You can get to the raw sqlite files under `.wrangler`, but don't mess them up ou...

dangoldin•10/2/24, 4:07 PM

well that's what i'm trying to do but they show up as empty when i query them. for example:

dangoldin•10/2/24, 4:08 PM

in this case datagrip is configured to point to

199187a5c9948d6a9eaef3ee19490fb5eec30c2012c4b4101aaeb362f07f1d3e.sqlite

199187a5c9948d6a9eaef3ee19490fb5eec30c2012c4b4101aaeb362f07f1d3e.sqlite

dangoldin•10/2/24, 4:09 PM

i think i need to get wrangler/miniflare to force it to flush and then i'll be able to access via non-cloudflare tooling

Cyb3r-Jak3•10/2/24, 4:10 PM

You should be able to do

wrangler d1 execute —local <query>

wrangler d1 execute —local <query>

https://developers.cloudflare.com/workers/wrangler/commands/#execute

Cloudflare Docs

Commands | Cloudflare Workers docs

Create, develop, and deploy your Cloudflare Workers with Wrangler commands.

dangoldin•10/2/24, 4:12 PM

cool - will explore more!

dangoldin•10/2/24, 4:13 PM

weird. the command also returns nothing but if i query it within the app (remix), it works perfectly

dangoldin•10/2/24, 4:13 PM

must be pointing to a different sqlite ha

dangoldin•10/2/24, 4:14 PM

ah that's what it is i think - the app with prisma is writing to a different sqlite file. urg i'm dumb

mallets•10/3/24, 7:35 AM

Now I don't what's easier for CF to implement, but I wouldn't mind the 10GB/database limit if we can have 1 DB per user. These DBs could end up with 100KB max or a couple GB.
But that runs into the 50K limit. Will CF increase this limit easily upon request or should I just fragment tables over multiple DBs?
Feels like managing a "managed" DB service.

Original message was deleted

mallets•10/3/24, 8:23 AM

Good to know.
Unrelated, but I am confused by the pricing per read request. An old 2023 blog post mentions read units (4KB), while the current pricing is simply per row read.
Hope that means reading a "heavy" 512KB blob row still counts as a single read now?

mallets•10/3/24, 8:30 AM

Also a bit odd that it's "cheaper" to store small files on D1, if they need to be accessed a lot. R2 is 360x more costly per read but 50x cheaper GB/month.

mallets•10/3/24, 8:34 AM

Thinking of storing small emails completely on D1 and a path to R2 for the bigger ones.

Mmallets Also a bit odd that it's "cheaper" to store small files on D1, if they need to b...

boop•10/3/24, 10:42 AM

Don't forget about the cache in front of R2. You wont be billed a ~~read request~~ Class B if it hits the cache

Zane Shannon•10/4/24, 2:42 AM

hey crew, i'm using d1 with prisma and getting abysmal performance on workers but totally reasonable on localhost. i don't think it's the location bit because i'm in san francisco and the database is located in WNAM. any hints about where to look?

ZZane Shannon hey crew, i'm using d1 with prisma and getting abysmal performance on workers bu...

mallets•10/4/24, 9:36 AM

If this is about query latency, there is a good chance the worker was invoked on the other side of the globe. Something about routing priorities based on account plan. A CF node exists in the same city as me but connections to any free plan resource is 8000kms away.

https://cloudflare-test.judge.sh/

mallets•10/4/24, 9:46 AM

Check the http response headers, the airport code is the location of the serving worker.

MMax (@rozenmd)aight i'll cut a PR

divby0•10/4/24, 10:03 AM

what happened to that pr? Asking because newest wrangler (3.79) still makes this mistake and doesn't apply in the right order

If there's any help needed, I'd be happy to try writing the PR if you didn't have time yet

Kyle•10/4/24, 2:56 PM

Hi, is this blog post still relevant? It says the D1 API is in beta testing, but this was written 2 years ago. So, would the procedures call be available? Couldn't get it work the other day in typescript or javascript.

https://blog.cloudflare.com/whats-new-with-d1/

The Cloudflare Blog

D1: our quest to simplify databases

Get an inside look on the D1 experience today, what the team is currently working on and what’s coming up!

KKyle Hi, is this blog post still relevant? It says the D1 API is in beta testing, but...

Larry•10/4/24, 6:32 PM

I also read that again recently and was surprised by the stored procedure reference. That was an earlier discussion of the possibilities but instead of getting implemented inside of D1, my best guess is that this is now implemented by using DOs with the

sql.exec()

sql.exec()

interface.

sharedDB.batch([ sharedDB.insert(usersTable).values({ publicId: nanoid(), name: googleUser.name, email: googleUser.email }).onConflictDoNothing() .returning(), sharedDB.insert(oauthAccountsTable).values({ providerId: "google", providerUserId: googleUser.sub, userId:// !!! no id }) ])

await sharedDB.batch([ sharedDB.insert(usersTable).values({ publicId: nanoid(), name: googleUser.name, email: googleUser.email }).onConflictDoNothing() .returning(), sharedDB.insert(oauthAccountsTable).values({ providerId: "google", providerUserId: googleUser.sub, userId: await sharedDB.select({ id: usersTable.id }).from(usersTable).where(eq(usersTable.email, googleUser.email)).get().then((res) => res?.id!), }) ])

Keep in mind visits != D1 queries but still, D1 can easily handle it, yes 😄

Similar Threads

Similar Threads

Similar Threads