nodejs sqlite tutorial
Connecting To SQLite Database Using Node.js
Summary: in this tutorial, you will learn how to connect to an SQLite database from Node.js applications.
Installing sqlite3 module
To interact with the SQLite database, you need to download and install sqlite3 module. You can use npm to do so using the following command:
> npm install sqlite3
After installing the sqlite3 module, you are ready to connect to an SQLite database from a Node.js application.
To connect to an SQLite database, you need to:
First, import the sqlite3 module
Second, call the Database() function of the sqlite3 module and pass the database information such as database file, opening mode, and a callback function.
Connecting to the in-memory database
To open a database connection to an in-memory database, you use the following steps.
First, import the sqlite3 module:
const sqlite3 = require('sqlite3').verbose();
Notice that the execution mode is set to verbose to produce long stack traces.
Second, create a Database object:
let db = new sqlite3.Database(':memory:');
The sqlite3.Database() returns a Database object and opens the database connection automatically.
The sqlite3.Database() accepts a callback function that will be called when the database opened successfully or when an error occurred.
The callback function has the error object as the first parameter. If an error occurred, the error object is not null, otherwise, it is null.
If you don’t provide the callback function and an error occurred during opening the database, an error event will be emitted. In case the database is opened successfully, the open event is emitted regardless of whether a callback is provided or not.
So you now can open an SQLite database and provide the detailed information if an error occurred as follows:
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQlite database.');
});
It is a good practice to close a database connection when you are done with it. To close a database connection, you call the close() method of the Database object as follows:
db.close();
The close() method will wait for all pending queries completed before actually closing the database.
Similar to the Database(), the close() method also accepts a callback that indicates whether an error occurred during closing the database connection.
db.close((err) => {
if (err) {
return console.error(err.message);
}
console.log('Close the database connection.');
});
The following illustrates the complete code for opening and closing an in-memory SQLite database:
const sqlite3 = require('sqlite3').verbose();
// open database in memory
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
return console.error(err.message);
}
console.log('Connected to the in-memory SQlite database.');
});
// close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
console.log('Close the database connection.');
});
Let’s run the program to see how it works.
> node connect.js
Connected to the in-memory SQlite database.
Close the database connection.
As you can see, it works perfectly as expected.
Connecting to a disk file database
To connect to a disk file database, instead of passing the ':memory:' string, you pass the path to the database file.
For example, to connect to the chinook database file stored in the db folder, you use the following statement:
let db = new sqlite3.Database('./db/chinook.db', (err) => {
if (err) {
console.error(err.message);
}
console.log('Connected to the chinook database.');
});
There are three opening modes:
sqlite3.OPEN_READONLY: open the database for read-only.
sqlite3.OPEN_READWRITE : open the database for reading and writting.
sqlite3.OPEN_CREATE: open the database, if the database does not exist, create a new database.
The sqlite3.Database() accepts one or more mode as the second argument. By default, it uses the OPEN_READWRITE | OPEN_CREATE mode. It means that if the database does not exist, the new database will be created and is ready for read and write.
To open the chinook sample database for read and write, you can do it as follows:
let db = new sqlite3.Database('./db/chinook.db', sqlite3.OPEN_READWRITE, (err) => {
if (err) {
console.error(err.message);
}
console.log('Connected to the chinook database.');
});
The following example shows the complete code for opening the chinook database, querying data from the playlists table, and closing the database connection.
const sqlite3 = require('sqlite3').verbose();
// open the database
let db = new sqlite3.Database('./db/chinook.db', sqlite3.OPEN_READWRITE, (err) => {
if (err) {
console.error(err.message);
}
console.log('Connected to the chinook database.');
});
db.serialize(() => {
db.each(`SELECT PlaylistId as id,
Name as name
FROM playlists`, (err, row) => {
if (err) {
console.error(err.message);
}
console.log(row.id + "\t" + row.name);
});
});
db.close((err) => {
if (err) {
console.error(err.message);
}
console.log('Close the database connection.');
});
Note that you will learn how to query data in the next tutorial.
In this tutorial, you have learned how to connect to an SQLite database either in-memory or disk file based database.
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:
Open a database connection.
Execute a SELECT statement and process the result set.
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
});
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();
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
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
});
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();
Let’s run the get.js program.
>node get.js
1 'Music'
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
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
});
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();
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]
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.
Controlling the Execution Flow of Statements
Summary: in this tutorial, you will learn how to control the execution flow of statements.
The sqlite3 module provides you with two methods for controlling the execution flow of statements. The serialize() method allows you to execute statements in serialized mode, while the parallelize() method executes the statements in parallel.
Let’s look into each method in detail to understand how it works.
Executing statement in serialized mode with Database.serialize
The serialize() method puts the execution mode into serialized mode. It means that only one statement can execute at a time. Other statements will wait in a queue until all the previous statements are executed.
After the serialize() method returns, the execution mode is set to the original mode again.
It’s safe to nest the serialize() method as follows:
db.serialize(() => {
// queries will execute in serialized mode
db.serialize(() => {
// queries will execute in serialized mode
});
// queries will execute in serialized mode
});
Suppose, you want to execute the following three statements in sequence:
Create a new table.
Insert data into the table.
Query data from the table.
To do this, you place these statements in the serialize() method as follows:
const sqlite3 = require('sqlite3').verbose();
// open the database connection
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
console.error(err.message);
}
});
db.serialize(() => {
// Queries scheduled here will be serialized.
db.run('CREATE TABLE greetings(message text)')
.run(`INSERT INTO greetings(message)
VALUES('Hi'),
('Hello'),
('Welcome')`)
.each(`SELECT message FROM greetings`, (err, row) => {
if (err){
throw err;
}
console.log(row.message);
});
});
// close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
});
Because the run() method returns a Database object so that we could chain the method calls.
Let’s run the program to see how it works.
> node serialize.js
Hi
Hello
Welcome
It works as expected.
Notice that if you don’t place three statements in the serialize() method, all the three statements may execute in parallel which would cause an error.
Executing statements in parallel with Database.parallelize
If you want the scheduled queries to execute in parallel, you place them in the parallelize() method.
Similar to the serialize() method, it is safe to nest the parallelize() method as follows:
db.parallelize(() => {
// queries will execute in parallel mode
db.parallelize(() => {
// queries will execute in parallel mode
});
// queries will execute in parallel mode
});
For the demonstration, we will create a new function that calculates the sum of two numbers using SQLite database and place the function calls in the parallelize() method as shown in the following example:
const sqlite3 = require('sqlite3').verbose();
// open a database connection
let db = new sqlite3.Database(':memory:', (err) => {
if (err) {
console.error(err.message);
}
});
db.parallelize(() => {
dbSum(1, 1, db);
dbSum(2, 2, db);
dbSum(3, 3, db);
dbSum(4, 4, db);
dbSum(5, 5, db);
});
// close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
});
function dbSum(a, b, db) {
db.get('SELECT (? + ?) sum', [a, b], (err, row) => {
if (err) {
console.error(err.message);
}
console.log(`The sum of ${a} and ${b} is ${row.sum}`);
});
}
Let’s run the parallelize.js program.
>node parallelize.js
The sum of 5 and 5 is 10
The sum of 1 and 1 is 2
The sum of 4 and 4 is 8
The sum of 3 and 3 is 6
The sum of 2 and 2 is 4
As you see in the output, the order of execution is not the same as it was called in the program.
Notice that the statements execute in parallel, therefore, each time you run the program, the order of execution may be different.
In this tutorial, you have learned how to control the execution flow of the statements.
Inserting Data Into an SQLite Table from a Node.js Application
Summary: in this tutorial, you will learn how to insert one or more row into an SQLite table from a Node.js application.
To insert data into an SQLite table from a Node.js application, you follow these steps:
Open a database connection.
Execute an INSERT statement.
Close the database connection.
For the demonstration, we will create a new database named sample.db in the db folder.
When you open a database connection in the default mode, the database is created if it does not exist.
let db = new sqlite3.Database('./db/sample.db');
In the sample.db database, we create a table called langs for storing programming languages:
db.run('CREATE TABLE langs(name text)');
You can run the program to create the sample.db database and langs table as follows:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('../db/sample.db');
db.run('CREATE TABLE langs(name text)');
db.close();
Now, we are ready to insert data into the langs table.
Insert one row into a table
To execute an INSERT statement, you use the run() method of the Database object:
db.run(sql, params, function(err){
//
});
The run() method executes an INSERT statement with specified parameters and calls a callback afterwards.
If an error occurred, you can find the detailed information in the err argument of the callback function.
In case the statement is executed successfully, the this object of the callback function will contain two properties:
lastID property stores the value of the last inserted row ID.
changes property stores the rows affected by the query.
The following insert.js program illustrates how to insert a row into the langs table:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./db/sample.db');
// insert one row into the langs table
db.run(`INSERT INTO langs(name) VALUES(?)`, ['C'], function(err) {
if (err) {
return console.log(err.message);
}
// get the last insert id
console.log(`A row has been inserted with rowid ${this.lastID}`);
});
// close the database connection
db.close();
Let’s run the insert.js program:
>node insert.js
A row has been inserted with rowid 1
It worked as expected.
Insert multiple rows into a table at a time
To insert multiple rows at a time into a table, you use the following form of the INSERT statement:
INSERT INTO table_name(column_name)
VALUES(value_1), (value_2), (value_3),...
To simulate this in the Node.js application, we first need to construct the INSERT statement with multiple placeholders:
INSERT INTO table_name(column_name)
VALUES(?), (?), (?),...
Suppose, you want to insert rows into the langs table with the data from the following languages array:
let languages = ['C++', 'Python', 'Java', 'C#', 'Go'];
To construct the INSERT statement, we use the map() method to map each element in the languages array into (?) and then join all placeholders together.
let placeholders = languages.map((language) => '(?)').join(',');
let sql = 'INSERT INTO langs(name) VALUES ' + placeholders;
The following insert-many.js program illustrates how to insert multiple rows into the langs table:
const sqlite3 = require('sqlite3').verbose();
// open the database connection
let db = new sqlite3.Database('../db/sample.db');
let languages = ['C++', 'Python', 'Java', 'C#', 'Go'];
// construct the insert statement with multiple placeholders
// based on the number of rows
let placeholders = languages.map((language) => '(?)').join(',');
let sql = 'INSERT INTO langs(name) VALUES ' + placeholders;
// output the INSERT statement
console.log(sql);
db.run(sql, languages, function(err) {
if (err) {
return console.error(err.message);
}
console.log(`Rows inserted ${this.changes}`);
});
// close the database connection
db.close();
Let’s run the insert-many.js program to see how it works.
> node insert-many.js
INSERT INTO langs(name) VALUES (?),(?),(?),(?),(?)
Rows inserted 5
It inserted 5 rows into the langs table which is what we expected.
In this tutorial, you have learned how to insert one or more rows into an SQLite table from a Node.js application.
Updating Data in SQLite Database from a Node.js Application
Summary: this tutorial shows you how to update data in the SQLite database from a Node.js application.
To update data in the SQLite database from a Node.js application, you use these steps:
Open a database connection.
Execute an UPDATE statement.
Close the database connection.
For the demonstration, we will use the langs table in the sample.db database that we created in the previous tutorial.
Updating data example
To update data in a table, you use the UPDATE statement as follows:
UPDATE table_name
SET column_name = value_1
WHERE id = id_value;
To execute the UPDATE statement in the Node.js application, you call the run() method of the Database object:
db.run(sql, params, function(err){
//
});
The run() method executes an UPDATE statement with specified parameters and calls a callback afterwards.
The err argument of the callback stores the error detail in case the execution has any problem e.g., syntax error, locking, etc.
If the UPDATE statement is executed successfully, the this object of the callback function will contain the changes property that stores the number of rows updated.
The following update.js program illustrates how to update a row in the langs table from C to Ansi C:
const sqlite3 = require('sqlite3').verbose();
// open a database connection
let db = new sqlite3.Database('./db/sample.db');
//
let data = ['Ansi C', 'C'];
let sql = `UPDATE langs
SET name = ?
WHERE name = ?`;
db.run(sql, data, function(err) {
if (err) {
return console.error(err.message);
}
console.log(`Row(s) updated: ${this.changes}`);
});
// close the database connection
db.close();
Let’s test the update.js program.
>node update.js
Row(s) updated: 1
The output showed that one row has been updated which is correct.
In this tutorial, you have learned how to update data in the SQLite database from a Node.js application.
SQLite Node.js – Deleting Data
Summary: in this tutorial, you will learn how to delete data in the SQLite database from a Node.js application.
To delete data in the SQLite database from a Node.js application, you use the following steps:
Open a database connection.
Execute a DELETE statement.
Close the database connection.
For the demonstration, we will use the langs table in the sample.db database that we created in the previous tutorial.
Deleting data example
To delete data from a table, you use the DELETE statement as follows:
DELETE FROM table_name
WHERE column_name = value;
To execute the DELETE statement from a Node.js application, you call the run() method of the Database object as follows:
db.run(sql, params, function(err) {
//
});
The run() method allows you to execute a DELETE statement with specified parameters and calls a callback function afterward.
If there was any error during the execution of DELETE statement, the err argument of the callback function will provide the details.
If the DELETE statement executed successfully, the this object of the callback function will contain the changes property that stores the number of rows deleted.
The following delete.js program illustrates how to delete a row from the langs table:
const sqlite3 = require('sqlite3').verbose();
// open a database connection
let db = new sqlite3.Database('./db/sample.db', (err) => {
if (err) {
console.error(err.message);
}
});
let id = 1;
// delete a row based on id
db.run(`DELETE FROM langs WHERE rowid=?`, id, function(err) {
if (err) {
return console.error(err.message);
}
console.log(`Row(s) deleted ${this.changes}`);
});
// close the database connection
db.close((err) => {
if (err) {
return console.error(err.message);
}
});
Let’s test the update.js program.
>node delete.js
Row(s) deleted: 1
The output showed that one row had been deleted successfully.
In this tutorial, you have learned how to delete data in the SQLite database from a Node.js application.