2

New to SQL... how does one insert into a parent table and a child one?

Assuming the following tables

import { integer, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const users = pgTable('user', {
    id: serial('id').primaryKey(),
    name: text('name'),
});

export const tokens = pgTable('token', {
    id: serial('id').primaryKey(),
        userId: text("userId").notNull().references(() => users.id, { onDelete: "cascade" }),
        token: string("token"),
});

To create a new user with a token... I think manually looks like this...

const newUser = await db.insert(users).values({name: "Billy"}).returning();
const token = await db.insert(token).values({userId: newUser.id, token: "123"}).returning();

Is this the proper way or is this transaction supposed to be using a view or transactions?

wongz
  • 3,255
  • 2
  • 28
  • 55

1 Answers1

3

Please note that I am also pretty new to drizzle and CRUD SQL stuff, so there may be a "better" way to do this, but I believe you need to wrap this in a transaction.

Basically, you'll return the id of the new user you created and use it to insert a new record in your tokens table (which is what you were already doing), but if an error occurs than the db "rolls back" and nothing happens.

await db.transaction(async (tx) => {
  const result = await db.insert(users).values({ name: 'billy' }).returning({ userId: users.id });
  
  await db.insert(tokens).values({ userId: result[0].userId, token: '123' })
  
});
Stephen ODea
  • 46
  • 1
  • 1