Getting Started with NodeJS SQLite

Getting Started with Node.js and SQLite

SQLite makes a nice stand-alone database for applications that do not require a full client-server environment. Using SQLite3 with Node.js is easy. It can provide all the benefits of a SQL database persistence layer without needing a DBA or DevOps team.

For a demonstration of the general process, you can read the documentation of the SQLite3 API.

Prerequisite: To get started with Node.js and SQLite3, you must have installed Node.js and Node Package Manager (npm) on your machine. If you have not, install using the below commands:

sudo apt install npm
sudo apt install nodejs

Having installed Node.js, SQLite is now ready to be installed using npm for SQLite3.

Install SQLite

Install SQLite support into Node.js using npm on your local development environment.

sudo npm install sqlite3

Create a Database

Now you can create an SQLite database with Node.js. This example uses a simple database application to track superheroes from the Marvel Cinematic Universe.

1. First, create a file called sample.js and import the sqlite3 module into Node.js:

   File: sample.js

   1	var sqlite3 = require('sqlite3');

2. The following line creates a database, mcu.db, in the current working directory. The sqlite3.Database() call can take one, two, or three arguments. The second argument is SQLite database flags, from the set of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE, and sqlite3.OPEN_CREATE.

   File: sample.js

   1 2 3

   ... new sqlite3.Database('./mcu.db', sqlite3.OPEN_READWRITE, (err) ...);

   Note

   The following are the different SQLite flag combinations:

   • OPEN_READONLY: The database is opened in read-only mode. If the database does not already exist, an error is returned.

   • OPEN_READWRITE: The database is opened for reading and writing where the database must already exist, otherwise an error is returned.

   • OPEN_CREATE: The database is opened for reading and writing, and if the database does not exist, it is created.

   The default SQLite database flag is sqlite3.OPEN_READWRITE and sqlite3.OPEN_CREATE.

3. The third argument in the sqlite3.Database() is a callback function that is called when the database is opened successfully or when an error occurred. This callback function has the error object, err as the first parameter. If an error occurred, the error object is not null, otherwise, it is null.

   File: sample.js

   1 2 3 4 5 6 7 8 9 10 11 12

   var sqlite3 = require('sqlite3'); var db; new sqlite3.Database('./mcu.db', sqlite3.OPEN_READWRITE, (err) => { if (err && err.code == "SQLITE_CANTOPEN") { createDatabase(); return; } else if (err) { console.log("Getting error " + err); exit(1); } runQueries(db); });

4. If the database exists, the runQueries() is executed. Now you need to create the createDatabase() function as shown below:

   File: sample.js

   1 2 3 4 5 6 7 8 9

   function createDatabase() { var newdb = new sqlite3.Database('mcu.db', (err) => { if (err) { console.log("Getting error " + err); exit(1); } createTables(newdb); }); }

The above code is similar to that of creating the database. However, this time the flags are missing; that means that the database is created if it does not exist yet. If it succeeds, the createTables() is executed to create the tables. If we get an error again, something more serious is going on, so the code exits.

Create Tables and Insert Data

The following code illustrates SQLite’s exec() method to create the tables and populate them. The exec() method runs all the queries in the specified string. After the tables are created and insertions are made, the runQueries() method is executed. The following code creates a table for popular Marvel superheroes such X-Men, Thanos, and others. It also creates a table for their superpowers.

File: sample.js

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

function createTables(newdb) { newdb.exec(` create table hero ( hero_id int primary key not null, hero_name text not null, is_xman text not null, was_snapped text not null ); insert into hero (hero_id, hero_name, is_xman, was_snapped) values (1, 'Spiderman', 'N', 'Y'), (2, 'Tony Stark', 'N', 'N'), (3, 'Jean Grey', 'Y', 'N'); create table hero_power ( hero_id int not null, hero_power text not null ); insert into hero_power (hero_id, hero_power) values (1, 'Web Slinging'), (1, 'Super Strength'), (1, 'Total Nerd'), (2, 'Total Nerd'), (3, 'Telepathic Manipulation'), (3, 'Astral Projection'); `, () => { runQueries(newdb); }); }

Query the Database

You can use one of several methods to fetch rows from the database. The data can be fetched row by row, looped over, or returned in a single array. In this case, the latter method is used. The following code returns characters whose superpowers are being “Total Nerds,” and whether they are X-Men or were snapped by Thanos.

File: sample.js

1 2 3 4 5 6 7 8 9 10 11 12

function runQueries(db) { db.all(` select hero_name, is_xman, was_snapped from hero h inner join hero_power hp on h.hero_id = hp.hero_id where hero_power = ?`, "Total Nerd", (err, rows) => { rows.forEach(row => { console.log(row.hero_name + "\t" + row.is_xman + "\t" + row.was_snapped); }); }); }

The all() method of the sqlite3 returns an array of rows on success, or an error on failure.

Note

It is good practice to parameterize the query by providing a list of substation values or an object with properties. Because it can be substituted using $properyname syntax. This avoids SQL injection hacks.

Below is the complete sample.js file:

File: sample.js

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

var sqlite3 = require('sqlite3'); let db= new sqlite3.Database('./mcu.db', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, (err) => { if (err && err.code == "SQLITE_CANTOPEN") { createDatabase(); return; } else if (err) { console.log("Getting error " + err); exit(1); } runQueries(db); }); function createDatabase() { var newdb = new sqlite3.Database('mcu.db', (err) => { if (err) { console.log("Getting error " + err); exit(1); } createTables(newdb); }); } function createTables(newdb) { newdb.exec(` create table hero ( hero_id int primary key not null, hero_name text not null, is_xman text not null, was_snapped text not null ); insert into hero (hero_id, hero_name, is_xman, was_snapped) values (1, 'Spiderman', 'N', 'Y'), (2, 'Tony Stark', 'N', 'N'), (3, 'Jean Grey', 'Y', 'N'); create table hero_power ( hero_id int not null, hero_power text not null ); insert into hero_power (hero_id, hero_power) values (1, 'Web Slinging'), (1, 'Super Strength'), (1, 'Total Nerd'), (2, 'Total Nerd'), (3, 'Telepathic Manipulation'), (3, 'Astral Projection'); `, () => { runQueries(newdb); }); } function runQueries(db) { db.all(`select hero_name, is_xman, was_snapped from hero h inner join hero_power hp on h.hero_id = hp.hero_id where hero_power = ?`, "Total Nerd", (err, rows) => { rows.forEach(row => { console.log(row.hero_name + "\t" +row.is_xman + "\t" +row.was_snapped); }); }); }

When you execute sample.js file, the following result is generated:

username@localhost:~$ node sample.js
Spiderman	N	Y
Tony Stark	N	N

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• SQLite3 API
• Marvel Cinematic Universe