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

Next, open a connection to an SQLite database:

const db = new Database(filename);Code language: JavaScript (javascript)

Then, construct an INSERT statement:

const sql = 'INSERT INTO table...';Code language: JavaScript (javascript)

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

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

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

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

Optionally, you can close the database connection:

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

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

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

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

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

const sql = `INSERT INTO authors(firstName, lastName) 
             VALUES(:firstName,:lastName);`;Code language: TypeScript (typescript)

Third, prepare the INSERT statement for execution:

const stmt = db.prepare(sql);Code language: TypeScript (typescript)

Fourth, execute the INSERT statement:

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

Finally, return the id of the inserted row:

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

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

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

bun insert.tsCode language: JavaScript (javascript)

It’ll show the id of the inserted row:

{ 
   id: 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: JavaScript (javascript)

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.