SQLite Bun: Selecting Data

Summary: in this tutorial, you will learn how to select data from a table in SQLite using Bun.

How to Select data from a table in Bun #

To select data from a table in Bun, you follow these steps:

First, import the Database class from the bun:sqlite module:

import { Database } from "bun:sqlite";

Next, open a connection to an SQLite database:

const db = new Database(filename);

Then, construct a SELECT statement:

const sql = 'SELECT * FROM table ...';

Typically, you want to filter data by passing value to the SELECT statement. For example, you may want to get row by id or find rows whose columns match a pattern.

To do this, you can use the binding parameters. In Bun, bind parameters use a prefix as $, :, and @. For example:

$id
:id
@id

When executing a query, if you don’t pass values to binding parameters, Bun will not throw any error. This is the default behavior.

If you want Bun to throw an error if you forget to pass parameters, you can connect to the SQLite database using strict mode:

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

Also, the strict mode allows you to use the bind parameters without prefixes.

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

After that, call the prepare() method to prepare the SELECT statement for execution:

const stmt = db.prepare(sql);

The prepare() method returns a Statement object.

Finally, call the get() method of the Statement object to get the first matching row:

const result = stmt.get({id: value});

Or call the all() method of the Statement object to get all matching rows:

const result = stmt.all({param: value});

Optionally, you can close the database connection:

db.close()

An error may occur when opening the SQLite database and selecting data. 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 = "SELECT * FROM table...";

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

Selecting one row #

We’ll select the author by id from the authors table.

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

export function findById(db: Database, id: number) {
  const sql = `SELECT * FROM authors 
               WHERE id = :id`;
  const stmt = db.prepare(sql);
  return stmt.get({ id });
}

The findById() function returns a row with a specified id.

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

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

let db;
try {
  db = new Database("pub.db", { strict: true });
  const author = findById(db, 1);
  console.log(author);
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}

Step 3. Open your terminal and run the fetchOne.ts file:

bun fetchOne.ts

Output:

{
  id: 1,
  firstName: "Alex",
  lastName: "Starling",
}

The output shows the author with id 1.

Selecting all rows from a table #

We’ll select all rows from the authors table.

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

export function findAll(db: Database) {
  const sql = `SELECT * FROM authors 
               ORDER BY firstName`;
  const stmt = db.prepare(sql);
  return stmt.all();
}

The findAll() function returns all rows from the authors table. The result set is sorted by the authors’ first names.

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

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

let db;
try {
  db = new Database("pub.db", { strict: true });
  const authors = findAll(db);
  console.log(authors);
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}

Step 3. Open your terminal and run the fetchAll.ts file:

bun fetchAll.ts

Output

[
  {
    id: 1,
    firstName: "Alex",
    lastName: "Starling",
  }, {
    id: 7,
    firstName: "Avery",
    lastName: "Storm",
  }, {
    id: 3,
    firstName: "Casey",
    lastName: "Rivers",
  }, {
    id: 10,
    firstName: "Dakota",
    lastName: "Ember",
  }, {
    id: 2,
    firstName: "Jamie",
    lastName: "Falcon",
  }, {
    id: 6,
    firstName: "Jordan",
    lastName: "Blaze",
  }, {
    id: 4,
    firstName: "Morgan",
    lastName: "Sky",
  }, {
    id: 9,
    firstName: "Quinn",
    lastName: "Frost",
  }, {
    id: 8,
    firstName: "Riley",
    lastName: "Moon",
  }, {
    id: 5,
    firstName: "Taylor",
    lastName: "Phoenix",
  }
]

The output shows all the rows from the authors table.

Searching for authors by names #

We’ll find authors by matching the first or last names with a search term.

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

export function find(db: Database, name: string) {
  const sql = `SELECT * FROM authors 
              WHERE firstName like :name OR lastName like :name
              ORDER BY firstName`;
  const stmt = db.prepare(sql);
  return stmt.all({ name: `%${name}%` });
}

The find() function returns all authors whose first or last names contain a search term. We use the LIKE operator for pattern matching.

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

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

let db;
try {
  db = new Database("pub.db", { strict: true });
  const authors = find(db, "or");
  console.log(authors);
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}

Step 3. Open your terminal and run the fetch.ts file:

bun fetch.ts

Output

[
  {
    id: 7,
    firstName: "Avery",
    lastName: "Storm",
  }, {
    id: 6,
    firstName: "Jordan",
    lastName: "Blaze",
  }, {
    id: 4,
    firstName: "Morgan",
    lastName: "Sky",
  }, {
    id: 5,
    firstName: "Taylor",
    lastName: "Phoenix",
  }
]

Summary #

Use the get() method of the Statment object to retrieve the first matching row.
Use the all() method of the Statment object to retrieve all the matching rows.

Was this tutorial helpful?