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";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 DELETE statement:

const sql = 'DELETE FROM table WHERE id = :id';Code language: JavaScript (javascript)

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
@idCode language: TypeScript (typescript)

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 });Code language: TypeScript (typescript)

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);Code language: JavaScript (javascript)

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({...});Code language: TypeScript (typescript)

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

result.changesCode language: CSS (css)

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

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

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();
}Code language: JavaScript (javascript)

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;
}Code language: TypeScript (typescript)

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 {Code language: TypeScript (typescript)

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

const sql = `DELETE FROM authors
               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 DELETE statement:

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

Finally, return the number of rows deleted:

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

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();
}
Code language: JavaScript (javascript)

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

bun delete.tsCode language: JavaScript (javascript)

It’ll show the number of rows deleted:

{
  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: SQL (Structured Query Language) (sql)

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.