[Solved] Drizzle-Orm: How do you insert in a parent and child table? – Drizzle

The Problem:

Given two tables with a parent-child relationship, how do you insert data into both tables using the Drizzle-ORM framework?

The Solutions:

Solution 1: Use a transaction

To insert data into both the users and tokens tables in a single atomic operation, you can use a transaction. A transaction is a set of database operations that are executed as a single unit. If any of the operations in a transaction fail, the entire transaction is rolled back and none of the changes are committed to the database.

To start a transaction in Drizzle-ORM, use the transaction() method on the Database object. The transaction() method takes a callback function as its argument. The callback function is passed a Transaction object that can be used to execute database operations.

Inside the transaction callback, you can use the insert() method on the Table object to insert data into the users and tokens tables. The insert() method returns a Promise that resolves to an array of the primary key values of the inserted rows.

After you have inserted data into both tables, you can commit the transaction by calling the commit() method on the Transaction object. The commit() method returns a Promise that resolves when the transaction has been committed.

Here is an example of how to use a transaction to insert data into the users and tokens tables:

await db.transaction(async (tx) => {
  const result = await tx.insert(users).values({ name: "Billy"}).returning({ userId: users.id });
  
  await tx.insert(tokens).values({ userId: result[0].userId, token: "123" })
  
});

This code will insert a new row into the users table with the name "Billy". It will then insert a new row into the tokens table with the userId of the newly created user and the token "123". If either of these operations fails, the entire transaction will be rolled back and none of the changes will be committed to the database.