SQLite Node.js: Inserting Data Into a Table

[Savigo]

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone 

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

Second, open a database connection to an SQLite database:

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

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

db.run(sql, params, callback);Code language: CSS (css)

Finally, close the database connection:

db.close()Code language: CSS (css)

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

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

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

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

node insert.jsCode language: CSS (css)

Step 4. Verify the insertion:

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

sqlite3 my.dbCode language: JavaScript (javascript)

Second, format the output:

.header on
.mode column
.nullvalue nullCode language: CSS (css)

Second, query data from the products table:

SELECT * FROM products;Code language: JavaScript (javascript)

Output:

id  name    price
--  ------  ------
1   iPhone  899.99Code language: JavaScript (javascript)

Summary

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