SQLite Bun: Deleting Data

Summary: In this tutorial, you will learn how to delete a row from a table in an SQLite database in a Bun app.

How to Delete Data from a Table in the Bun App #

To delete data from a table of an SQLite database in 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 DELETE statement:

const sql = 'DELETE FROM table WHERE id = :id';

Typically, you need to pass values to the DELETE statement such as an id to delete the row by id. To do that, you can use binding parameters.

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

$id
:id
@id

By default, Bun does not throw an error if you skip passing parameters when executing the DELETE statement.

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

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

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

For example, if you have :id parameter, you can pass an object with the id property { id: 1 } , instead of { :id : 1 }

Step 4. Call the prepare() method to prepare the DELETE 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 DELETE statement to delete data:

const result = stmt.run({...});

The changes property of the result object stores the number of rows deleted:

result.changes

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

db.close();

When you open a SQLite database connection and delete data from a table, 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";

const sql = "DELETE FROM table ...";

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

Delete Data from the authors Table in the Bun App #

Step 1. Add a new function called remove() to file author.ts file:

// ...

export function remove(db: Database, id: number): number {
  const sql = `DELETE FROM authors
               WHERE id = :id;`;
  const stmt = db.prepare(sql);
  const result = stmt.run({ id });
  return result.changes;
}

How it works.

First, define remove function that deletes a row from the authors table and returns the number of rows deleted:

export function remove(db: Database, id: number): number {

Second, construct an DELETE statement that deletes a row from the authors table:

const sql = `DELETE FROM authors
               WHERE id = :id;`;

Third, prepare the UPDATE statement for execution:

const stmt = db.prepare(sql);

Fourth, execute the DELETE statement:

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

Finally, return the number of rows deleted:

return result.changes;

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

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

let db;

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

  // remove an author with id 1
  const rowDeleted = remove(db, 1);

  // display the number of rows deleted
  console.log({ rowDeleted });
} catch (error) {
  // display the error
  console.log(error);
} finally {
  // close the database
  if (db) db.close();
}

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

bun delete.ts

It’ll show the number of rows deleted:

{
  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?