SQL transactions?

[zeronumbers]

Doing the https://lucia-auth.com/tutorials/google-oauth/nextjs I see that we need to insert into two tables, shouldn't this be atomic?

lucia/pages/tutorials/google-oauth/nextjs.md

Line 168 in c8b4192

const user = await createUser(googleUserId, username);

lucia/pages/tutorials/google-oauth/nextjs.md

Line 171 in c8b4192

const session = await createSession(sessionToken, user.id);

https://v3.lucia-auth.com/guides/oauth/multiple-providers
v3 lucia also has segment about multiple oauth providers which suggests to add another table for oauth stuff, and now we have 3 tables which need insert.

Is there a reason why transactions are not used?

[pilcrowonpaper]

The operations doesn't need to be atomic. You can still sign-in again even if it fails to create a new session. It's also much easier to read if we just use a single function call for each query.

[zeronumbers]

To avoid confusion, I assume it is still suggested to use transaction if you have a separate table for oauth account?

like it is done here https://v3.lucia-auth.com/guides/oauth/multiple-providers

await db.beginTransaction();
await db.table("user").insert({
	id: userId,
	username: githubUser.login
});
await db.table("oauth_account").insert({
	provider_id: "github",
	provider_user_id: githubUser.id,
	user_id: userId
});
await db.commit();

also maybe there should be on delete cascade added to foreign key here:

CREATE TABLE oauth_account (
    provider_id TEXT NOT NULL,
    provider_user_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    PRIMARY KEY (provider_id, provider_user_id),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

maybe provider_user_id should be provider_account_id?

also drizzle example of session doesn't have on delete cascade while prisma does:

model Session {
  id        String   @id
  userId    Int
  expiresAt DateTime

  user      User     @relation(references: [id], fields: [userId], onDelete: Cascade)
}

const sessionTable = pgTable("session", {
	id: text("id").primaryKey(),
	userId: integer("user_id")
		.notNull()
		.references(() => userTable.id),
	expiresAt: timestamp("expires_at", {
		withTimezone: true,
		mode: "date"
	}).notNull()
});

[pilcrowonpaper]

If you have a separate table for the user and oauth account, then yes you should use a transaction when creating the user and account