Querying Data in SQLite Database from Node.js Applications

Summary: in this tutorial, you will learn how to query data from the SQLite database from a Node.js application using sqlite3 API.

To query data in SQLite database from a Node.js application, you use these steps:

1. Open a database connection.
2. Execute a SELECT statement and process the result set.
3. Close the database connection.

The sqlite3 module provides you with some methods for querying data such as all(), each() and get().

Querying all rows with all() method

The all() method allows you to execute an SQL query with specified parameters and call a callback to access the rows in the result set.

The following is the signature of the all() method:

db.all(sql,params,(err, rows ) => {
    // process rows here    
});
Code language: JavaScript (javascript)

The err argument stores the error detail in case there was an error occurred during the execution of the query. Otherwise, the err will be null.

If the query is executed successfully, the rows argument contains the result set.

Because the all() method retrieves all rows and places them in the memory, therefore, for the large result set, you should use the each() method.

The following example illustrates how to query data from the playlists table in the sample database using the all() method:

const sqlite3 = require('sqlite3').verbose();

// open the database
let db = new sqlite3.Database('./db/chinook.db');

let sql = `SELECT DISTINCT Name name FROM playlists
           ORDER BY name`;

db.all(sql, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.name);
  });
});

// close the database connection
db.close();
Code language: JavaScript (javascript)

Let’s run the program.

>node all.js
90's Music
Audiobooks
Brazilian Music
Classical
Classical 101 - Deep Cuts
Classical 101 - Next Steps
Classical 101 - The Basics
Grunge
Heavy Metal Classic
Movies
Music
Music Videos
On-The-Go 1
TV Shows
Code language: JavaScript (javascript)

The output shows all playlists as expected.

Query the first row in the result set

When you know that the result set contains zero or one row e.g., querying a row based on the primary key or querying with only one aggregate function such as count, sum, max, min, etc., you can use the get() method of Database object.

db.get(sql, params, (err, row) => {
    // process the row here 
});
Code language: JavaScript (javascript)

The get() method executes an SQL query and calls the callback function on the first result row. In case the result set is empty, the row argument is undefined.

The following get.js program demonstrates how to query a playlist by its id:

const sqlite3 = require('sqlite3').verbose();

// open the database
let db = new sqlite3.Database('./db/chinook.db');

let sql = `SELECT PlaylistId id,
                  Name name
           FROM playlists
           WHERE PlaylistId  = ?`;
let playlistId = 1;

// first row only
db.get(sql, [playlistId], (err, row) => {
  if (err) {
    return console.error(err.message);
  }
  return row
    ? console.log(row.id, row.name)
    : console.log(`No playlist found with the id ${playlistId}`);

});

// close the database connection
db.close();
Code language: JavaScript (javascript)

Let’s run the get.js program.

>node get.js
1 'Music'
Code language: JavaScript (javascript)

The output shows the Music playlist which is correct.

If you change the playlistId to 0 and execute the get.js program again:

>node get.js
No playlist found with the id 0
Code language: JavaScript (javascript)

It showed that no playlist was found with id 0 as expected.

Query rows with each() method

The each() method executes an SQL query with specified parameters and calls a callback for every row in the result set.

The following illustrates the each() method:

db.each(sql,params, (err, result) => {
   // process each row here
});
Code language: JavaScript (javascript)

If the result set is empty, the callback is never called. In case there is an error, the err parameter contains detailed information.

The following each.js program illustrates how to use the each() method to query customers’ data from the customers table.

const sqlite3 = require('sqlite3').verbose();

// open the database
let db = new sqlite3.Database('../db/chinook.db');

let sql = `SELECT FirstName firstName,
                  LastName lastName,
                  Email email
            FROM customers
            WHERE Country = ?
            ORDER BY FirstName`;

db.each(sql, ['USA'], (err, row) => {
  if (err) {
    throw err;
  }
  console.log(`${row.firstName} ${row.lastName} - ${row.email}`);
});

// close the database connection
db.close();
Code language: JavaScript (javascript)

Let’s run the each.js program:

>node each.js
Dan Miller - [email protected]
Frank Harris - [email protected]
Frank Ralston - [email protected]
Heather Leacock - [email protected]
Jack Smith - [email protected]
John Gordon - [email protected]
Julia Barnett - [email protected]
Kathy Chase - [email protected]
Michelle Brooks - [email protected]
Patrick Gray - [email protected]
Richard Cunningham - [email protected]
Tim Goyer - [email protected]
Victor Stevens - [email protected]
Code language: JavaScript (javascript)

As you see, the callback function was called for each row to print out the customer’s information.

In this tutorial, you have learned how to use various methods of the Database object to query data from the SQLite database.