SQLite Node.js: Querying Data

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 query data from a table in a Node.js app.

This tutorial begins where the “Delete data from a table in Node.js” tutorial left off.

Querying data from a table #

To query data from an SQLite table in a Node.js app, you use these steps:

First, import sqlite3 from the sqlite3 module:

import sqlite3 from "sqlite3";Copy

Second, open a database connection to an SQLite database:

const db = new sqlite3.Database(filename);Copy

Third, execute a SELECT statement statement using the all() to get all rows or get() to retrieve the first matching row from a table:

db.all(sql, params, callback);
db.get(sql, params, callback);Copy

Finally, close the database connection:

db.close()Copy

Defining wrapper functions #

The following defines fetchAll() and fetchFirst() functions that wrap the all() and get() methods:

export const fetchAll = async (db, sql, params) => {
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      if (err) reject(err);
      resolve(rows);
    });
  });
};

export const fetchFirst = async (db, sql, params) => {
  return new Promise((resolve, reject) => {
    db.get(sql, params, (err, row) => {
      if (err) reject(err);
      resolve(row);
    });
  });
};Copy

Importing CSV data into the products table #

Step 1. Download the following products.csv file.

Step 2. Import the products.csv file into the products table. You can follow this tutorial to do it.

After importing, the products should have the following rows:

id  name                price
--  ------------------  -----
1   iPhone 14           799
2   Samsung Galaxy S23  749
3   Google Pixel 7      599
4   OnePlus 11          699
5   Sony Xperia 1 V     949
6   Xiaomi 13 Pro       899
7   Oppo Find X5        699
8   Motorola Edge 40    599
9   Nokia G60           329
10  Asus ROG Phone 6    999Copy

Querying all rows from a table #

Step 1. Create fetchAll.js file to retrieve all rows from the products table:

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

(async () => {
  const db = new sqlite3.Database("my.db");

  // fetch all products
  let sql = `SELECT * FROM products`;

  try {
    const products = await fetchAll(db, sql);
    console.log(products);
  } catch (err) {
    console.log(err);
  } finally {
    db.close();
  }
})();Copy

Step 2. Run the program:

node fetchAll.jsCopy

It’ll show the following output:

[
  { id: 1, name: 'iPhone 14', price: 799 },
  { id: 2, name: 'Samsung Galaxy S23', price: 749 },
  { id: 3, name: 'Google Pixel 7', price: 599 },
  { id: 4, name: 'OnePlus 11', price: 699 },
  { id: 5, name: 'Sony Xperia 1 V', price: 949 },
  { id: 6, name: 'Xiaomi 13 Pro', price: 899 },
  { id: 7, name: 'Oppo Find X5', price: 699 },
  { id: 8, name: 'Motorola Edge 40', price: 599 },
  { id: 9, name: 'Nokia G60', price: 329 },
  { id: 10, name: 'Asus ROG Phone 6', price: 999 }
]Copy

Query a single row from a table #

Step 1. Create fetchFirst.js file to fetch a row with ID 10 from the products table:

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

(async () => {
  const db = new sqlite3.Database("my.db");

  let sql = `SELECT * FROM products WHERE id = ?`;

  try {
    const product = await fetchFirst(db, sql, [10]);
    console.log(product);
  } catch (err) {
    console.log(err);
  } finally {
    db.close();
  }
})();Copy

Step 2. Run the program:

node fetchFirst.jsCopy

It’ll show the following output:

{ id: 10, name: 'Asus ROG Phone 6', price: 999 }Copy

Summary #

• Use the all() method of the Database object to get all rows from a table.
• Use the get() method of the Database object to retrieve the first matching row from a table.