{"id":1251,"date":"2017-06-26T11:34:17","date_gmt":"2017-06-26T04:34:17","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1251"},"modified":"2024-10-27T21:43:42","modified_gmt":"2024-10-27T14:43:42","slug":"querying-data-sqlite-database-node-js-applications","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-nodejs\/query\/","title":{"rendered":"SQLite Node.js: Querying Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to query data from a table in a Node.js app.<\/p>\n\n\n\n

This tutorial begins where the “Delete data from a table in Node.js<\/a>” tutorial left off.<\/p>\n\n\n\n

Querying data from a table<\/h2>\n\n\n\n

To query data from an SQLite table in a Node.js app, you use these steps:<\/p>\n\n\n\n

First, import sqlite3<\/code> from the sqlite3<\/code> module:<\/p>\n\n\n

import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
const<\/span> db = new<\/span> sqlite3.Database(filename);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, execute a SELECT<\/code> statement statement using the all()<\/code> to get all rows or get()<\/code> to retrieve the first matching row from a table:<\/p>\n\n\n
db.all(sql, params, callback);\ndb.get(sql, params, callback);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection:<\/p>\n\n\n
db.close()<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Defining wrapper functions<\/h2>\n\n\n\n
The following defines fetchAll()<\/code> and fetchFirst()<\/code> functions that wrap the all()<\/code> and get()<\/code> methods:<\/p>\n\n\n
export<\/span> const<\/span> fetchAll = async<\/span> (db, sql, params) => {\n  return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n    db.all(sql, params, (err, rows) => {\n      if<\/span> (err) reject(err);\n      resolve(rows);\n    });\n  });\n};\n\nexport<\/span> const<\/span> fetchFirst = async<\/span> (db, sql, params) => {\n  return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n    db.get(sql, params, (err, row) => {\n      if<\/span> (err) reject(err);\n      resolve(row);\n    });\n  });\n};\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Importing CSV data into the products table<\/h2>\n\n\n\n
Step 1. Download the following products.csv file<\/a>.<\/p>\n\n\n\n
Step 2. Import the products.csv file into the products table. You can follow this tutorial to do it.<\/a><\/p>\n\n\n\n
After importing, the products should have the following rows:<\/p>\n\n\n
id  name                price\n--  ------------------  -----\n1   iPhone 14           799\n2   Samsung Galaxy S23  749\n3   Google Pixel 7      599\n4   OnePlus 11          699\n5   Sony Xperia 1 V     949\n6   Xiaomi 13 Pro       899\n7   Oppo Find X5        699\n8   Motorola Edge 40    599\n9   Nokia G60           329\n10  Asus ROG Phone 6    999<\/code><\/span><\/pre>\n\n\n
Querying all rows from a table<\/h2>\n\n\n\n
Step 1. Create fetchAll.js<\/code> file to retrieve all rows from the products<\/code> table:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\nimport<\/span> { fetchAll } from<\/span> \".\/sql.js\"<\/span>;\n\n(async<\/span> (<\/span>) =><\/span> {\n  const<\/span> db = new<\/span> sqlite3.Database(\"my.db\"<\/span>);\n\n  \/\/ fetch all products<\/span>\n  let<\/span> sql = `SELECT * FROM products`<\/span>;\n\n  try<\/span> {\n    const<\/span> products = await<\/span> fetchAll(db, sql);\n    console<\/span>.log(products);\n  } catch<\/span> (err) {\n    console<\/span>.log(err);\n  } finally<\/span> {\n    db.close();\n  }\n})();\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Run the program:<\/p>\n\n\n
node<\/span> fetchAll<\/span>.js<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show the following output:<\/p>\n\n\n
[\n  { id<\/span>: 1<\/span>, name<\/span>: 'iPhone 14'<\/span>, price<\/span>: 799<\/span> },\n  { id<\/span>: 2<\/span>, name<\/span>: 'Samsung Galaxy S23'<\/span>, price<\/span>: 749<\/span> },\n  { id<\/span>: 3<\/span>, name<\/span>: 'Google Pixel 7'<\/span>, price<\/span>: 599<\/span> },\n  { id<\/span>: 4<\/span>, name<\/span>: 'OnePlus 11'<\/span>, price<\/span>: 699<\/span> },\n  { id<\/span>: 5<\/span>, name<\/span>: 'Sony Xperia 1 V'<\/span>, price<\/span>: 949<\/span> },\n  { id<\/span>: 6<\/span>, name<\/span>: 'Xiaomi 13 Pro'<\/span>, price<\/span>: 899<\/span> },\n  { id<\/span>: 7<\/span>, name<\/span>: 'Oppo Find X5'<\/span>, price<\/span>: 699<\/span> },\n  { id<\/span>: 8<\/span>, name<\/span>: 'Motorola Edge 40'<\/span>, price<\/span>: 599<\/span> },\n  { id<\/span>: 9<\/span>, name<\/span>: 'Nokia G60'<\/span>, price<\/span>: 329<\/span> },\n  { id<\/span>: 10<\/span>, name<\/span>: 'Asus ROG Phone 6'<\/span>, price<\/span>: 999<\/span> }\n]<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Query a single row from a table<\/h2>\n\n\n\n
Step 1. Create fetchFirst.js<\/code> file to fetch a row with ID 10 from the products<\/code> table:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\nimport<\/span> { fetchFirst } from<\/span> \".\/sql.js\"<\/span>;\n\n(async<\/span> (<\/span>) =><\/span> {\n  const<\/span> db = new<\/span> sqlite3.Database(\"my.db\"<\/span>);\n\n  let<\/span> sql = `SELECT * FROM products WHERE id = ?`<\/span>;\n\n  try<\/span> {\n    const<\/span> product = await<\/span> fetchFirst(db, sql, [10<\/span>]);\n    console<\/span>.log(product);\n  } catch<\/span> (err) {\n    console<\/span>.log(err);\n  } finally<\/span> {\n    db.close();\n  }\n})();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Run the program:<\/p>\n\n\n
node<\/span> fetchFirst<\/span>.js<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll show the following output:<\/p>\n\n\n
{ id<\/span>: 10<\/span>, name: 'Asus ROG Phone 6'<\/span>, price: 999<\/span> }<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the all()<\/code> method of the Database<\/code> object to get all rows from a table.<\/li>\n\n\n\n
  • Use the get()<\/code> method of the Database<\/code> object to retrieve the first matching row from a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, open a database connection to an SQLite database<\/a>:<\/p>\n\n\n