SQLite Bun: Transaction

Summary: in this tutorial, you will learn how to perform a transaction in an SQLite database using Bun.

Transactions allow you to execute multiple queries such that either all of them succeed or none do. In Bun, you use transaction() method of the Database object to create a transaction.

Creating tables

Step 1. Open your terminal and navigate to the project directory.

Step 2. Connect to the pub.db database using the sqlite3 shell.

Step 3. Create the books and book_authors tables:

CREATE TABLE books (
   id INTEGER PRIMARY KEY,
   title TEXT NOT NULL,
   isbn TEXT NOT NULL,
   publishedYear DATE NOT NULL
);

CREATE TABLE book_authors(
   bookId INTEGER,
   authorId INTEGER,
   PRIMARY KEY(bookId, authorId),
   FOREIGN KEY(bookId) REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY(authorId) REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE
);Code language: SQL (Structured Query Language) (sql)

Here’s the updated database diagram:

Step 4. List all the tables

authors       book_authors  booksCode language: TypeScript (typescript)

Performing a transaction

We’ll insert a new book and assign an author to the book within the same transaction.

Step 1. Create a book.ts file.

Step 2. Define type ID which can be number or bigint in the book.ts:

type ID = number | bigint;Code language: TypeScript (typescript)

Step 3. Define insertBook function that inserts a new row into the books table:

function insertBook(
  db: Database,
  title: string,
  isbn: string,
  publishedYear: string
): ID {
  const sql = `INSERT INTO books(title, isbn, publishedYear) 
               VALUES(:title,:isbn,:publishedYear);`;
  const stmt = db.prepare(sql);
  const result = stmt.run({
    title,
    isbn,
    publishedYear,
  });

  return result.lastInsertRowid;
}Code language: TypeScript (typescript)

Step 4. Define assignBookToAuthor function that inserts a new row into the book_authors table:

function assignBookToAuthor(db: Database, bookId: ID, authorId: ID) {
  console.log(`Assigning book ${bookId} to author ${authorId}`);

  const sql = `INSERT INTO book_authors(bookId, authorId) 
               VALUES(:bookId,:authorId);`;
  const stmt = db.prepare(sql);
  stmt.run({
    bookId,
    authorId,
  });
}Code language: TypeScript (typescript)

Step 5. Define the addBook function that calls the insertBook and assignBookToAuthor functions within the same transaction:

export function addBook(
  db: Database,
  title: string,
  isbn: string,
  publishedYear: string,
  authorId: ID
) {
  // create a new transaction
  const start = db.transaction(() => {
    const bookId = insertBook(db, title, isbn, publishedYear);
    assignBookToAuthor(db, bookId, authorId);
  });

  // start the transaction
  start();
}Code language: TypeScript (typescript)

How it works.

First, call the transaction method of the Database object that wraps a transaction. It returns a function that runs queries within the same transaction:

const start = db.transaction(() => {Code language: TypeScript (typescript)

Inside the returning function, call the insertBook function to insert a new row into the books table and assignBookToAuthor function to insert a row into the book_authors table.

const bookId = insertBook(db, title, isbn, publishedYear);
assignBookToAuthor(db, bookId, authorId);Code language: TypeScript (typescript)

Second, call the add function to start the transaction:

start();Code language: TypeScript (typescript)

If the function returns, the transaction is automatically committed. If an exception occurs, the transaction is rolled back.

Note that the exception will not be caught but propagate as usual.

Step 6. Create a new file transaction.ts with the following code:

import { Database } from "bun:sqlite";
import { addBook } from "./book";

let db;

try {
  // connect to SQLite
  db = new Database("pub.db", { strict: true });

  // enable foreign key support
  db.exec("PRAGMA foreign_keys = ON");

  // insert a new book
  addBook(
    db,
    "SQLite Secret: Adventures in Queryland",
    "978-1-67890-123-4",
    "2024",
    100
  );
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}Code language: TypeScript (typescript)

How it works.

First, connect to the SQLite database:

db = new Database("pub.db", { strict: true });Code language: JavaScript (javascript)

Second, enable foreign key support:

db.exec("PRAGMA foreign_keys = ON");Code language: JavaScript (javascript)

Third, call the addBook() function to add a book and assign an author with id 1:

addBook(
    db,
    "SQLite Secret: Adventures in Queryland",
    "978-1-67890-123-4",
    "2024",
    100
  );Code language: JavaScript (javascript)

Finally, close the database connection:

if (db) db.close();Code language: CSS (css)

Step 7. Run the transaction.ts file:

bun transaction.tsCode language: TypeScript (typescript)

Step 8. Verify the data in the books and book_authors tables:

select * from books;Code language: SQL (Structured Query Language) (sql)

Output:

id  title                                   isbn               publishedYear
--  --------------------------------------  -----------------  -------------
1   SQLite Secret: Adventures in Queryland  978-1-67890-123-4  2024Code language: TypeScript (typescript)

select * from book_authors;Code language: SQL (Structured Query Language) (sql)

Output:

bookId  authorId
------  --------
1       1Code language: TypeScript (typescript)

The output indicates that the transaction has been completed successfully.

The transaction will fail if you change the author ID to a non-existing one such as 100. The app will not insert any row into the books or book_authors tables.

Summary

• Call the transaction() method of the Database object to return a function that wraps multiple queries executing within a transaction.