SQLite Bun: Updating Data

Summary: In this tutorial, you will learn how to update data in a table from a Bun app.

How to Update Data in a Table in the Bun App

To update data in a table of an SQLite database from a Bun app, you follow these steps:

Step 1. Import the Database class from the bun:sqlite module:

import { Database } from "bun:sqlite";Code language: JavaScript (javascript)

Step 2. Open a connection to an SQLite database:

const db = new Database(filename);Code language: JavaScript (javascript)

Step 3. Construct an UPDATE statement:

const sql = 'UPDATE table SET ...';Code language: JavaScript (javascript)

Typically, you need to pass values to the UPDATE statement. To do that, you can use binding parameters.

In Bun, binding parameters have one of the prefixes: $, :, or @. For example:

$name
:name
@nameCode language: TypeScript (typescript)

By default, Bun does not throw an error if you don’t pass parameters when executing the UPDATE statement.

But you can set the strict mode to true when connecting to a SQLite database:

const db = new Database("mydb.sqlite", { strict : true });Code language: TypeScript (typescript)

Bun will throw an error if you don’t pass the parameters in this case. Additionally, Bun will let you bind parameters without using prefixes.

For example, if you have $name parameter, you can pass an object with the name property { name: "Joe" } , instead of { $name : "Joe"}

Step 4. Call the prepare() method to prepare the UPDATE statement for execution:

const stmt = db.prepare(sql);Code language: JavaScript (javascript)

The prepare() method returns a Statement object.

Step 5. Call the run() method of the Statement object to execute the UPDATE statement to update data.

const result = stmt.run({...});Code language: TypeScript (typescript)

Step 6. Optionally, you can explicitly close the database connection:

db.close();Code language: JavaScript (javascript)

When you open a new SQLite database connection and update data, an error may occur. To handle it properly, you can use the try-catch statement.

Here’s the complete code:

import { Database } from "bun:sqlite";

let db;
const sql = "UPDATE table SET ...";

try {
  db = new Database("<pathToDbFile>", { strict : true});
  const stmt = db.prepare(sql);
  stmt.run({...})
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}Code language: JavaScript (javascript)

Updating Data in the authors Table in the Bun App

Step 1. Add a new function to file author.ts file:

// ...

export function update(
  db: Database,
  id: number,
  firstName: string,
  lastName: string
): number {
  const sql = `UPDATE authors
               SET firstName = :firstName, 
                   lastName = :lastName
                WHERE id = :id;`;
  const stmt = db.prepare(sql);

  const result = stmt.run({
    id,
    firstName,
    lastName,
  });

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

How it works.

First, define update function that updates the first and last names of an author specified by the author id and returns the number of rows updated:

export function update(
  db: Database,
  id: number,
  firstName: string,
  lastName: string
): number {
// ...Code language: TypeScript (typescript)

Second, construct an INSERT statement that inserts a new row into the authors table:

const sql = `UPDATE authors
               SET firstName = :firstName, 
                   lastName = :lastName
                WHERE id = :id;`;Code language: TypeScript (typescript)

Third, prepare the UPDATE statement for execution:

const stmt = db.prepare(sql);Code language: TypeScript (typescript)

Fourth, execute the UPDATE statement:

const result = stmt.run({
    id,
    firstName,
    lastName,
});Code language: TypeScript (typescript)

Finally, return the number of rows updated:

return result.changes;Code language: TypeScript (typescript)

Step 2. Create a new file update.ts with the following code:

import { Database } from "bun:sqlite";
import { update } from "./author";

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

  // insert data
  const rowUpdated = update(db, 1, "Johnathan", "Smith");

  // display the number of rows updated
  console.log({ rowUpdated });
} catch (error) {
  // display the error
  console.log(error);
} finally {
  // close the database
  if (db) db.close();
}
Code language: JavaScript (javascript)

Step 3. Open the terminal and run the update.ts file:

bun update.tsCode language: JavaScript (javascript)

It’ll show the number of rows updated:

{
  rowUpdated: 1,
}Code language: TypeScript (typescript)

Verifying data

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

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

sqlite3 pub.dbCode language: CSS (css)

Step 3. Format the query output:

.mode column
.header on 
.nullvalue nullCode language: CSS (css)

Step 4. Retrieve data from the authors table:

select * from authors;Code language: JavaScript (javascript)

Output:

id  firstName  lastName
--  ---------  --------
1   Johnathan  Smith

The output indicates that the Bun app has successfully updated the author with id 1.

Summary

• Use the prepare() method of the Database object to prepare the UPDATE statement for execution.
• Call the run() method of the Statement object to run the UPDATE statement.