This lab demonstrates basic database programming in Node.js using a file-based database sqlite.
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine (from https://www.sqlite.org/). The whole database is stored in a file, and an application can open this database directly without connecting to any server.
A popular module to access SQLite in Node is sqlite3. However, this module only provides a callback-based API. Therefore, we will also use the package sqlite which is a wrapper library on sqlite3 that provides a promise-based API. (online reference)
The package.json in the example code already include the following dependencies. You can install both packages using npm install.
"dependencies": {
"sqlite": "^4.0.23",
"sqlite3": "^5.0.2"
},
The example code zip file also includes a sample database my-northwind.sqlite, which is based on a sample database for Microsoft SQL server. I’ve converted it into SQLite format. You can read the database schema here.
To browse the content of the database, install and use the GUI tool DB browser for SQLite.
SQLite supports both basic and advanced features of SELECT statements. The following demonstrates the steps to read data from a database.
Most functions of sqlite are asynchronous. In other words, they return a promise which may either resolve to the result (e.g. result set returned by SELECT), or reject with an error. For brevity, we use await on the top-level in the following example.
// p141.mjs
// import sqlite3 driver
import sqlite3 from 'sqlite3';
// import database open function
import { open } from 'sqlite';
// connect to the database system, open database file
const db = await open({
// the database file
filename: './my-northwind.sqlite',
// tell sqlite to use the standard 'sqlite3' driver
driver: sqlite3.Database
});
const query = "SELECT * FROM Product WHERE Id=14";
// get a single row from the result set of the query
const tofu = await db.get(query);
console.log(tofu);
// close the database connection
await db.close();
db.get(query) returns one row from the result set of running the query. The row is formatted as a JavaScript object. (What if the SQL returns 0 or more than 1 rows?)
If you want to fetch all rows of the result set, you can either
This example demonstrates the first approach. db.all() returns a promise which resolves to an array of objects, in which each object corresponds to one row in the result set. This example also illustrates placeholders in SQL queries, column renaming, and using template strings to write multi-line SQL queries.
// p142.mjs
// The total sales quantity of products in category 7 - Produce
// ...
const query =
`SELECT P.id as id, P.ProductName as productName, SalesQuantity
FROM Product as P
JOIN (SELECT ProductId, SUM(Quantity) as SalesQuantity
FROM OrderDetail GROUP BY ProductId)
as D ON P.id = D.ProductId
WHERE P.CategoryID = $categoryId
ORDER BY SalesQuantity DESC`;
const productTotalSales =
await db.all(query, { $categoryId: 7 });
console.log(productTotalSales);
// ...
The next example p143.mjs retrieves the orders (from Order table) made during December of 2012, and also calculates the total order amount from data in OrderDetail table for each order. We perform this task with two SQL select queries: one for retrieving orders, and one for calculating the total order amount for each order.
// p143.mjs
// ...
async function getXmasOrders () {
const queryOrders =
`SELECT Id as id, OrderDate as orDate
FROM 'Order'
WHERE orDate BETWEEN $startDate AND $endDate`;
const xmasOrders = await db.all(queryOrders, {
$startDate: "2012-12-01",
$endDate: "2012-12-31"
});
for (let ord of xmasOrders) {
ord.amount = await getOrderAmount(ord.id);
}
return xmasOrders;
}
const xmasOrders = await getXmasOrders();
console.table(xmasOrders);
When we refactor the statements to a function to get the xmas orders, we have to make the function a async function because we’re using await inside. This function returns a promise. If the function returns a result (return xmasOrders), the promise resolves to that result (i.e. an array of order records). On the other hand, if any exception is thrown inside the function, including a rejection from await getOrderAmount(), the promise rejects with the error object of the exception.
Getting the whole record into an array is convenient with db.all(). However, if the number of records is very large, and you only need to process the records one by one, you can avoid wasting system memory by using db.each(). This method calls a callback to process each row in the result set, and the returned promise resolves to the number of rows after all rows are processed.
// p144.mjs
async function exportOrderDetail () {
const queryOrderDetail =
`SELECT * FROM OrderDetail LIMIT 500 OFFSET 1000`;
const prom = db.each(queryOrderDetail, {} , (err, row) => {
if (err) throw err;
console.log(row);
});
// returns number of records in result set
return await prom;
}
const recordCount = await exportOrderDetail();
console.log("count =", recordCount);
Use the method db.run() to execute INSERT, UPDATE and DELETE statements. In the callback to this method, the property this.changes indicates the number of rows affected by the query, and the property this.lastID contains the value of the last inserted row ID.
// p145.mjs
async function createCustomer (cust) {
const query =
`INSERT INTO Customer (CompanyName, ContactName, City, Phone)
VALUES ($companyName, $contactName, $city, $phone)`;
const result = await db.run(query, {
$companyName: cust.companyName,
$contactName: cust.contactName,
$city: cust.city,
$phone: cust.phone
});
return result;
}
const insResult = await createCustomer({
companyName: 'MPI', contactName: 'Peter Chan',
phone: '(853) 85996123', city: 'Macao'
});
// insResult.lastID gives the primary key 'id' auto-generated by sqlite
console.log('A new customer is created by SQL INSERT');
console.log('insResult:', insResult);
The example source files are available here.