I insert a user, grab the auto incremnet pk, and then update the same table with a snowflake I creat

CCarson I insert a user, grab the auto incremnet pk, and then update the same table with...

O

o7•9/30/24, 3:06 PM

cant u do that in the same query? since its the same table

C

CarsonOP•9/30/24, 3:14 PM

no because the snowflake is created using a function,

user is inseted without snowflake, just the email, hash, firstname, lastname, returning pk
funciton to turn pk to snowflake
then I update the user with the snowflake where pk matches

C

CarsonOP•9/30/24, 3:22 PM

this is the workflow

        const hash = utils.hashPassword(request.json.password);
        const account = await env.DB.prepare("INSERT INTO users (email, password, firstname, lastname) VALUES (?, ?, ?, ?) RETURNING pk;")
            .bind(request.json.email, hash, request.json.firstname, request.json.lastname)
            .run();

        if (!account.success) throw new Error();

        const snowflake = utils.createSnowflake(account.results[0].pk);
        const set_id = await env.DB.prepare("UPDATE users SET id = ? WHERE pk = ?;")
            .bind(snowflake, account.results[0].pk)
            .run();
            
        if (!set_id.success) throw new Error();

        const hash = utils.hashPassword(request.json.password);
        const account = await env.DB.prepare("INSERT INTO users (email, password, firstname, lastname) VALUES (?, ?, ?, ?) RETURNING pk;")
            .bind(request.json.email, hash, request.json.firstname, request.json.lastname)
            .run();

        if (!account.success) throw new Error();

        const snowflake = utils.createSnowflake(account.results[0].pk);
        const set_id = await env.DB.prepare("UPDATE users SET id = ? WHERE pk = ?;")
            .bind(snowflake, account.results[0].pk)
            .run();
            
        if (!set_id.success) throw new Error();

        const hash = utils.hashPassword(request.json.password);
        const account = await env.DB.prepare("INSERT INTO users (email, password, firstname, lastname) VALUES (?, ?, ?, ?) RETURNING pk;")
            .bind(request.json.email, hash, request.json.firstname, request.json.lastname)
            .run();

        if (!account.success) throw new Error();

        const snowflake = utils.createSnowflake(account.results[0].pk);
        const set_id = await env.DB.prepare("UPDATE users SET id = ? WHERE pk = ?;")
            .bind(snowflake, account.results[0].pk)
            .run();
            
        if (!set_id.success) throw new Error();

        const hash = utils.hashPassword(request.json.password);
        const account = await env.DB.prepare("INSERT INTO users (email, password, firstname, lastname) VALUES (?, ?, ?, ?) RETURNING pk;")
            .bind(request.json.email, hash, request.json.firstname, request.json.lastname)
            .run();

        if (!account.success) throw new Error();

        const snowflake = utils.createSnowflake(account.results[0].pk);
        const set_id = await env.DB.prepare("UPDATE users SET id = ? WHERE pk = ?;")
            .bind(snowflake, account.results[0].pk)
            .run();
            
        if (!set_id.success) throw new Error();

CCarson this is the workflow ```js const hash = utils.hashPassword(request.json....

L

lambrospetrou•9/30/24, 7:35 PM

Since this is 2 remote calls, they are not connected, and cannot rollback. Is Snowflake just random IDs? If yes, can you create them ahead of time without the exact autoincrement and send them together in one query? Or use some other mechanism to generate your ID like UUID, ULID, Nanoid, etc.

Llambrospetrou Since this is 2 remote calls, they are not connected, and cannot rollback. Is Sn...

C

CarsonOP•9/30/24, 7:47 PM

Ah alright thats what I was afraid of. Snowflake is just random ids but they are calculated using the pk just so that there arent 2 of the same ids ever. But ill prob just switch over to uuid or something for simplicity sake

C

CarsonOP•9/30/24, 7:48 PM

Thank you for your help

CCarson Ah alright thats what I was afraid of. Snowflake is just random ids but they are...

L

lambrospetrou•9/30/24, 7:49 PM

No problem. I personally use Nanoid with 32+ characters, or any UUIDv4/v7 should be OK too.

CCarson Ah alright thats what I was afraid of. Snowflake is just random ids but they are...

O

o7•9/30/24, 7:49 PM

u can make the snowflake a primary key, or unique, which means sqlite will deny creating another row of the same snowflake

C

CarsonOP•9/30/24, 7:52 PM

I have it that way curently for my mysql database. My curent codebase uses transactions and the snowflake is unique. The reason I did this was because I didnt want it to fail in the first place. if I just use the auto increment id in my creation of the snowflake then I know that it will be random.

C

CarsonOP•9/30/24, 7:53 PM

I know its a bit overkill and will prob never happen but I wanted to account for it

CCarson I have it that way curently for my mysql database. My curent codebase uses trans...

L

lambrospetrou•9/30/24, 7:55 PM

If you are going to use an autoincrement key to generate another primary key you already did the job of guaranteeing uniqueness in the first query, so why use a snowflake afterwards anyway. It's extra overhead really. Either generate it and send with the first query and retry in case a collision happens in a few years, or keep the autoincrement key.

C

CarsonOP•9/30/24, 7:57 PM

because I want that seccond primary key to be a set length, if I just give each user the id of the auto increment, then users 1-9 will have a id length of 1, 10-99 would be 2, and so on

CCarson because I want that seccond primary key to be a set length, if I just give each ...

L

lambrospetrou•9/30/24, 7:59 PM

My suggestion is to then to add a PRIMARY KEY on the column of your ID, generate a random ID with UUIDv4 or Nanoid (or UUIDV7 if you need time-ordered IDs), and add a simple retry wrapper around your calls. The collisions will be extremely rare to worry about the 2nd roundtrip that you anyway do right now for all calls, and you get what you want.

N

naourass•9/30/24, 10:13 PM

Is there currently some workaround to have a

db.batch

db.batch

db.batch

db.batch operation where a step requires the result of the previous one (the

id

id

of an inserted element)?

N

naourass•9/30/24, 10:15 PM

Or some similar workaround that would allow such a transaction?

J

Juan•9/30/24, 10:22 PM

Hey! I wanted to ask if D1 would be suitable for this scenario. I’m migrating an e-commerce webapp from a VPS to Cloudflare Workers, so that it can scale easier. We were already using R2 for images. Now the problem comes on choosing where to host the database. At the moment we were using a self hosted PostgreSQL server. We first thought on moving to Neondb. However we also thought on D1 as it would be easier to have all our services in Cloudflare.

Would D1 be suitable for an e-commerce website with around 1000 orders per month and around 5000 visits per day considering it should be able to scale ~x10 times that?

J

Juan•9/30/24, 10:26 PM

I’m not sure how D1 performs considering it’s based on SQlite, which is typically for small or local use cases

JJuan Hey! I wanted to ask if D1 would be suitable for this scenario. I’m migrating an...

O

o7•9/30/24, 11:15 PM

5000 requests per day is light work for D1

Oo7 5000 requests per day is light work for D1

I

Isaac McFadyen•9/30/24, 11:16 PM

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

Original message was deleted

A

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

A

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
        })
    ])

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
        })
    ])

S

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

S

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

Do you have indexes?

SSam Do you have indexes?

S

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

Ah! Yes, I do.

Ssenojsitruc Ah! Yes, I do.

S

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

Each index should be a write

SSam Each index should be a write

S

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

L

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

All queries inside each

batch

batch

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.

L

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

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

S

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, ...

L

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

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

L

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

I am saying do both INSERTs in the same

batch()

batch()

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()

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!),
        })
    ])

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({ ...

L

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!)

.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

J

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 😄

J

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

Yeah haha, thank you!

Original message was deleted

O

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) ?

L

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,..

D

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

L

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

You can get to the raw sqlite files under

.wrangler

.wrangler

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

I insert a user, grab the auto incremnet pk, and then update the same table with a snowflake I creat

Similar Threads

Similar Threads

Similar Threads