Bun SQLite: Inserting Data

Summary: In this tutorial, you will learn how to insert a row into a table using the Bun app.

How to Insert a Row into a Table in the Bun App #

To insert a row into a table in the Bun app, 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 an INSERT statement:

const sql = 'INSERT INTO table...';

In practice, you may want to pass values to the INSERT statement. To do that, you can use binding parameters.

Bun requires binding parameters to include $, :, or @ prefix. For example:

$name
:name
@name

By default, Bun does not throw an error if you don’t pass parameters when executing the INSERT statement.

However, if you 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.

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

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

const stmt = db.prepare(sql);

The prepare() method returns a Statement object.

Finally, call the run() method of the Statement object to execute the INSERT statement.

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

Optionally, you can close the database connection:

db.close()

An error may occur when opening the SQLite database and inserting 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 = "INSERT INTO table...";

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

Inserting data into the Authors table in the Bun App #

Step 1. Create a new file author.ts in the project directory:

import { Database } from "bun:sqlite";

export function insert(db: Database, firstName: string, lastName: string) {
  const sql = `INSERT INTO authors(firstName, lastName) 
                 VALUES(:firstName,:lastName);`;
  const stmt = db.prepare(sql);

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

  return result.lastInsertRowid;
}

How it works.

First, define insert function that inserts a new row into the authors table and returns the inserted id:

export function insert(db: Database, firstName: string, lastName: string) {

Second, construct an INSERT statement that inserts a new row into the authors table:

const sql = `INSERT INTO authors(firstName, lastName) 
             VALUES(:firstName,:lastName);`;

Third, prepare the INSERT statement for execution:

const stmt = db.prepare(sql);

Fourth, execute the INSERT statement:

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

Finally, return the id of the inserted row:

return result.lastInsertRowid;

Step 2. Create a new file insert.ts and add the following code to the insert.ts file:

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

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

  // insert data
  const id = insert(db, "John", "Doe");

  // display the id
  console.log({ id });

} catch (error) {

  // display the error
  console.log(error);
} finally {

  // close the database
  if (db) db.close();
}

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

bun insert.ts

It’ll show the id of the inserted row:

{ 
   id: 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   John       Doe

The output indicates that Bun app has successfully inserted a new row into the authors table.

Summary #

Use binding parameters with a prefix (:, $ or @) to pass values to the INSERT statement when executing it.
Use the prepare() method of the Database object to prepare the INSERT statement for execution.
Call the run() method of the Statement object to run the INSERT statement.

Was this tutorial helpful?