SQLite Node.js: Creating Tables

Summary: in this tutorial, you will learn how to create new tables in SQLite from Node.js apps.

This tutorial begins where the “Connecting to SQLite Databases from Node.js” tutorial left off.

Creating a table from the Node.js app

To create a new table in an SQLite database from a Node.js app, you follow these steps:

First, import sqlite3 from sqlite3 module:

import sqlite3 from "sqlite3";Code language: JavaScript (javascript)

Second, open a new database connection to the SQLite database:

const db = new sqlite3.Database("my.db");Code language: JavaScript (javascript)

Third, call the exec() method of the Database object to execute a CREATE TABLE statement to create a table:

db.exec(sql, (err) => {
    // handle error here
});Code language: JavaScript (javascript)

Finally, close the database connection:

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

Since the exec() method uses the traditional callback style which is quite challenging to work with. To make it easier, we can define a function execute() that wraps the exec() method and returns a Promise:

const execute = async (db, sql) => {
  return new Promise((resolve, reject) => {
    db.exec(sql, (err) => {
      if (err) reject(err);
      resolve();
    });
  });
};Code language: JavaScript (javascript)

And we can use the execute() function using async/await syntax:

try {
  await execute(sql);
} catch(error) {
  console.log(error);
}Code language: JavaScript (javascript)

Modifying the Node.js app

Step 1. Create sql.js and export the execute function:

export const execute = async (db, sql) => {
  return new Promise((resolve, reject) => {
    db.exec(sql, (err) => {
      if (err) reject(err);
      resolve();
    });
  });
};
Code language: JavaScript (javascript)

Step 2. Add the createTable.js to the project:

import sqlite3 from "sqlite3";
import { execute } from "./sql.js";

const main = async () => {
  const db = new sqlite3.Database("my.db");
  try {
    await execute(
      db,
      `CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price DECIMAL(10, 2) NOT NULL)`
    );
  } catch (error) {
    console.log(error);
  } finally {
    db.close();
  }
};

main();
Code language: JavaScript (javascript)

Step 3. Open the terminal and run the app:

npm startCode language: JavaScript (javascript)

Step 4. Verify the table creation.

First, open a new terminal and use the sqlite3 tool to connect to the my.db database:

sqlite3 my.dbCode language: JavaScript (javascript)

Second, list all the tables in the my.db database:

.tablesCode language: JavaScript (javascript)

Output:

productsCode language: JavaScript (javascript)

The output indicates that the products table was created.

Third, examine the structure of the products table:

.schema productsCode language: JavaScript (javascript)

Output:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);Code language: JavaScript (javascript)

Finally, quit the sqlite3 tool:

.quitCode language: JavaScript (javascript)

Summary

• Use the exec() method of the Database object to execute a CREATE TABLE statement and create a new table in an SQLite database from Node.js.