{"id":4104,"date":"2024-10-30T10:21:46","date_gmt":"2024-10-30T03:21:46","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=4104"},"modified":"2024-10-30T10:43:23","modified_gmt":"2024-10-30T03:43:23","slug":"transaction","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-bun\/transaction\/","title":{"rendered":"SQLite Bun: Transaction"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to perform a transaction in an SQLite database using Bun.<\/p>\n\n\n\n

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

Creating tables<\/h2>\n\n\n\n

Step 1. Open your terminal and navigate to the project directory.<\/p>\n\n\n\n

Step 2. Connect to the pub.db<\/code> database using the sqlite3<\/code> shell.<\/p>\n\n\n\n

Step 3. Create the books<\/code> and book_authors<\/code> tables:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> books (\n   id<\/span> INTEGER<\/span> PRIMARY KEY<\/span>,\n   title TEXT<\/span> NOT<\/span> NULL<\/span>,\n   isbn TEXT<\/span> NOT<\/span> NULL<\/span>,\n   publishedYear DATE<\/span> NOT<\/span> NULL<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> book_authors(\n   bookId INTEGER<\/span>,\n   authorId INTEGER<\/span>,\n   PRIMARY KEY<\/span>(bookId, authorId),\n   FOREIGN<\/span> KEY<\/span>(bookId) REFERENCES<\/span> books(id<\/span>) ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>,\n   FOREIGN<\/span> KEY<\/span>(authorId) REFERENCES<\/span> authors<\/span>(id<\/span>) ON<\/span> DELETE<\/span> CASCADE<\/span> ON<\/span> UPDATE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the updated database diagram:<\/p>\n\n\n\n
\"sqlite<\/figure>\n\n\n\n
Step 4. List all the tables<\/p>\n\n\n
authors       book_authors  books<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Performing a transaction<\/h2>\n\n\n\n
We’ll insert a new book and assign an author to the book within the same transaction<\/em>.<\/p>\n\n\n\n
Step 1. Create a book.ts<\/code> file.<\/p>\n\n\n\n
Step 2. Define type ID<\/code> which can be number<\/code> or bigint<\/code> in the book.ts<\/code>:<\/p>\n\n\n
type<\/span> ID = number<\/span> | bigint;<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Define insertBook<\/code> function that inserts a new row<\/a> into the books<\/code> table:<\/p>\n\n\n
function<\/span> insertBook<\/span>(\n  db: Database,\n  title: string<\/span>,\n  isbn: string<\/span>,\n  publishedYear: string<\/span>\n<\/span>): ID<\/span> <\/span>{\n  const<\/span> sql = `INSERT INTO books(title, isbn, publishedYear) \n               VALUES(:title,:isbn,:publishedYear);`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  const<\/span> result = stmt.run({\n    title,\n    isbn,\n    publishedYear,\n  });\n\n  return<\/span> result.lastInsertRowid;\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Define assignBookToAuthor<\/code> function that inserts a new row into the book_authors<\/code> table:<\/p>\n\n\n
function<\/span> assignBookToAuthor<\/span>(db: Database, bookId: ID, authorId: ID<\/span>) <\/span>{\n  console<\/span>.log(`Assigning book ${bookId}<\/span> to author ${authorId}<\/span>`<\/span>);\n\n  const<\/span> sql = `INSERT INTO book_authors(bookId, authorId) \n               VALUES(:bookId,:authorId);`<\/span>;\n  const<\/span> stmt = db.prepare(sql);\n  stmt.run({\n    bookId,\n    authorId,\n  });\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Define the addBook<\/code> function that calls the insertBook<\/code> and assignBookToAuthor<\/code> functions within the same transaction:<\/p>\n\n\n
export<\/span> function<\/span> addBook<\/span>(\n  db: Database,\n  title: string<\/span>,\n  isbn: string<\/span>,\n  publishedYear: string<\/span>,\n  authorId: ID\n<\/span>) <\/span>{\n  \/\/ create a new transaction<\/span>\n  const<\/span> start = db.transaction(()<\/span> =><\/span> {\n    const<\/span> bookId = insertBook(db, title, isbn, publishedYear);\n    assignBookToAuthor(db, bookId, authorId);\n  });\n\n  \/\/ start the transaction<\/span>\n  start();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, call the transaction method of the Database<\/code> object that wraps a transaction. It returns a function that runs queries within the same transaction:<\/p>\n\n\n
const<\/span> start = db.transaction(()<\/span> =><\/span> {<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Inside the returning function, call the insertBook<\/code> function to insert a new row into the books<\/code> table and assignBookToAuthor<\/code> function to insert a row into the book_authors<\/code> table.<\/p>\n\n\n
const<\/span> bookId = insertBook(db, title, isbn, publishedYear);\nassignBookToAuthor(db, bookId, authorId);<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the add<\/code> function to start the transaction:<\/p>\n\n\n
start();<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
If the function returns, the transaction is automatically committed. If an exception occurs, the transaction is rolled back. <\/p>\n\n\n\n
Note that the exception will not be caught but propagate as usual.<\/p>\n\n\n\n
Step 6. Create a new file transaction.ts<\/code> with the following code:<\/p>\n\n\n
import<\/span> { Database } from<\/span> \"bun:sqlite\"<\/span>;\nimport<\/span> { addBook } from<\/span> \".\/book\"<\/span>;\n\nlet<\/span> db;\n\ntry<\/span> {\n  \/\/ connect to SQLite<\/span>\n  db = new<\/span> Database(\"pub.db\"<\/span>, { strict: true<\/span> });\n\n  \/\/ enable foreign key support<\/span>\n  db.exec(\"PRAGMA foreign_keys = ON\"<\/span>);\n\n  \/\/ insert a new book<\/span>\n  addBook(\n    db,\n    \"SQLite Secret: Adventures in Queryland\"<\/span>,\n    \"978-1-67890-123-4\"<\/span>,\n    \"2024\"<\/span>,\n    100<\/span>\n  );\n} catch<\/span> (error) {\n  console<\/span>.log(error);\n} finally<\/span> {\n  if<\/span> (db) db.close();\n}<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, connect to the SQLite database:<\/p>\n\n\n
db = new<\/span> Database(\"pub.db\"<\/span>, { strict<\/span>: true<\/span> });<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, enable foreign key support:<\/p>\n\n\n
db.exec(\"PRAGMA foreign_keys = ON\"<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, call the addBook()<\/code> function to add a book and assign an author with id 1:<\/p>\n\n\n
addBook(\n    db,\n    \"SQLite Secret: Adventures in Queryland\"<\/span>,\n    \"978-1-67890-123-4\"<\/span>,\n    \"2024\"<\/span>,\n    100<\/span>\n  );<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection:<\/p>\n\n\n
if<\/span> (db<\/span>) db<\/span>.close<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 7. Run the transaction.ts<\/code> file:<\/p>\n\n\n
bun transaction.ts<\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 8. Verify the data in the books<\/code> and book_authors<\/code> tables:<\/p>\n\n\n
select<\/span> * from<\/span> books;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id  title                                   isbn               publishedYear\n--  --------------------------------------  -----------------  -------------\n1<\/span>   SQLite Secret: Adventures in<\/span> Queryland  978<\/span>-1<\/span>-67890<\/span>-123<\/span>-4<\/span>  2024<\/span><\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n
select<\/span> * from<\/span> book_authors;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
bookId  authorId\n------  --------\n1<\/span>       1<\/span><\/code><\/span>Code language:<\/span> TypeScript<\/span> (<\/span>typescript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the transaction has been completed successfully.<\/p>\n\n\n\n
The transaction will fail if you change the author ID to a non-existing one such as 100<\/code>. The app will not insert any row into the books<\/code> or book_authors<\/code> tables.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the transaction()<\/code> method of the Database<\/code> object to return a function that wraps multiple queries executing within a transaction.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t