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";

Step 2. Open a connection to an SQLite database:

const db = new Database(filename);

Step 3. Construct an UPDATE statement:

const sql = 'UPDATE table SET ...';

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
@name

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 });

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);

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({...});

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

db.close();

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();
}

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;
}

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 {
// ...

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;`;

Third, prepare the UPDATE statement for execution:

const stmt = db.prepare(sql);

Fourth, execute the UPDATE statement:

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

Finally, return the number of rows updated:

return result.changes;

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();
}

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

bun update.ts

It’ll show the number of rows updated:

{
  rowUpdated: 1,
}

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.db

Step 3. Format the query output:

.mode column
.header on 
.nullvalue null

Step 4. Retrieve data from the authors table:

select * from authors;

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.

Was this tutorial helpful?