Why Linode
Get to Know Us Better
View All Products
Compute
Storage
Databases
Services
Networking
Developer Tools
Delivery
Security
View All Solutions
Solutions
Pricing
Community
Engage With Us

Getting Started with NodeJS SQLite

Updated , by James Turner
Traducciones al Español
Estamos traduciendo nuestros guías y tutoriales al Español. Es posible que usted esté viendo una traducción generada automáticamente. Estamos trabajando con traductores profesionales para verificar las traducciones de nuestro sitio web. Este proyecto es un trabajo en curso.
Create a Linode account to try this guide with a $ credit.
This credit will be applied to any valid services used during your first  days.

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. See our guide SQL Injection Attack: What It Is and How to Prevent It to learn more about this type security vulnerability.

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.

This page was originally published on

nodejs sqlite database

Your Feedback Is Important

Let us know if this guide was helpful to you.

Join the conversation.
Read other comments or post your own below. Comments must be respectful, constructive, and relevant to the topic of the guide. Do not post external links or advertisements. Before posting, consider if your comment would be better addressed by contacting our Support team or asking on our Community Site.