SQLite Node.js: Querying Data

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

Finally, close the database connection:

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

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

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    999

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

Step 2. Run the program:

node fetchAll.jsCode language: CSS (css)

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

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

Step 2. Run the program:

node fetchFirst.jsCode language: CSS (css)

It’ll show the following output:

{ id: 10, name: 'Asus ROG Phone 6', price: 999 }Code language: CSS (css)

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.