SQLite Bun: Create Tables

Summary: in this tutorial, you will learn how to create a new table in SQLite from the Bun app.

To create a new table, 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 a CREATE TABLE statement:

const sql = 'CREATE TABLE ...';Code language: JavaScript (javascript)

After that, call the exec() method of the db instance to execute the CREATE TABLE statement to create a new table in the SQLite database:

db.exec(sql)Code language: JavaScript (javascript)

Finally, close the database connection:

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

An error may occur when opening the SQLite database and creating a table. 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 = "CREATE TABLE ...";
try {
  db = new Database("<pathToDbFile>");
  db.exec(sql);
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}Code language: JavaScript (javascript)

Creating table example

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

Step 2. Add the following code to the createTables.ts file:

import { Database } from "bun:sqlite";
const sql = `
    CREATE TABLE IF NOT EXISTS authors(
        id  INTEGER PRIMARY KEY,
        firstName TEXT NOT NULL,
        lastName TEXT NOT NULL
    )`;
let db;
try {
  db = new Database("pub.db");
  db.exec(sql);
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}Code language: JavaScript (javascript)

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

bun createTables.tsCode language: JavaScript (javascript)

Verifying tables

Step 1. Open a terminal and navigate to the project directory.

Step 2. Connect to the pub.db database using the sqlite3 shell:

sqlite3 pub.dbCode language: JavaScript (javascript)

Step 3. List all the tables in the pub.db database:

.tablesCode language: JavaScript (javascript)

Output:

authorsCode language: JavaScript (javascript)

The output indicates that the authors table has been created successfully.

Step 3. Check the schema of the authors table:

.schema authorsCode language: JavaScript (javascript)

Output:

CREATE TABLE authors(
        id  INTEGER PRIMARY KEY,
        firstName TEXT NOT NULL,
        lastName TEXT NOT NULL
    );Code language: SQL (Structured Query Language) (sql)

Summary

• Call the exec() method of the Database instance to execute a CREATE TABLE statement.