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

Next, open a connection to an SQLite database:

const db = new Database(filename);Code language: TypeScript (typescript)

Then, construct a SELECT statement:

const sql = 'SELECT * FROM table ...';Code language: TypeScript (typescript)

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

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

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

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

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

const result = stmt.all({param: value});Code language: TypeScript (typescript)

Optionally, you can close the database connection:

db.close()Code language: TypeScript (typescript)

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

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

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

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

bun fetchOne.tsCode language: TypeScript (typescript)

Output:

{
  id: 1,
  firstName: "Alex",
  lastName: "Starling",
}Code language: TypeScript (typescript)

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

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

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

bun fetchAll.tsCode language: TypeScript (typescript)

[
  {
    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",
  }
]Code language: TypeScript (typescript)

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

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

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

bun fetch.tsCode language: TypeScript (typescript)

[
  {
    id: 7,
    firstName: "Avery",
    lastName: "Storm",
  }, {
    id: 6,
    firstName: "Jordan",
    lastName: "Blaze",
  }, {
    id: 4,
    firstName: "Morgan",
    lastName: "Sky",
  }, {
    id: 5,
    firstName: "Taylor",
    lastName: "Phoenix",
  }
]Code language: TypeScript (typescript)

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.