SQLite Node.js: Inserting Data Into a Table

Summary: in this tutorial, you will learn how to insert a row into a table in SQLite databases in Node.js applications.

Inserting a row into a table from Node.js #

To insert data into an SQLite table from a Node.js app, you can follow these steps:

First, import sqlite3 from the sqlite3 module:

import sqlite3 from "sqlite3";

Second, open a database connection to an SQLite database:

const db = new sqlite3.Database(filename);

Third, execute an INSERT statement statement using the run() method of the Database object:

db.run(sql, params, callback);

Finally, close the database connection:

db.close()

Defining a wrapper function #

Since the run() method uses the traditional callback style, it’ll be challenging to work with.

To make it more convenient, we can define a wrapper function execute that calls the run() method and returns a Promise:

const execute = async (db, sql, params = []) => {
  if (params && params.length > 0) {
    return new Promise((resolve, reject) => {
      db.run(sql, params, (err) => {
        if (err) reject(err);
        resolve();
      });
    });
  }
  return new Promise((resolve, reject) => {
    db.exec(sql, (err) => {
      if (err) reject(err);
      resolve();
    });
  });
};

How it works.

The execute() is an async function that has three parameters:

db is an instance of the Database class.
sql is an SQL statement to execute.
params is the argument that will substitute the placeholders (?) in the sql statement.

The execute() function calls the run() method if the params parameter has items or the exec() method if it does not.

Insert one row into a table #

Step 1. Modify the execute() function from sql.js module:

export const execute = async (db, sql, params = []) => {
  if (params && params.length > 0) {
    return new Promise((resolve, reject) => {
      db.run(sql, params, (err) => {
        if (err) reject(err);
        resolve();
      });
    });
  }
  return new Promise((resolve, reject) => {
    db.exec(sql, (err) => {
      if (err) reject(err);
      resolve();
    });
  });
};

Step 2. Add the insert.js file to insert one row into the products table:

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

const main = async () => {
  const db = new sqlite3.Database("my.db");
  const sql = `INSERT INTO products(name, price) VALUES(?, ?)`;
  try {
    await execute(db, sql, ["iPhone", 899.99]);
  } catch (err) {
    console.log(err);
  } finally {
    db.close();
  }
};

main();

Step 3. Open the terminal and execute the following command to run the Node.js app:

node insert.js

Step 4. Verify the insertion:

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

sqlite3 my.db

Second, format the output:

.header on
.mode column
.nullvalue null

Second, query data from the products table:

SELECT * FROM products;

Output:

id  name    price
--  ------  ------
1   iPhone  899.99

Summary #

Use the run() method of the Database object to execute an INSERT statement to insert a new row into a table.

Was this tutorial helpful?