Learning Nodejs # 12: Using SQLite Databases on Nodejs

In the previous tutorial, we tried using the MySQL database on Nodejs .

Now we will try using SQLite on Nodejs.

What's the difference between SQLite and MySQL?

SQLite is an RDBMS database (Relational Database Management System)that does not require a server.

While MySQL requires a server.

SQLite stores the database on file .dbor .sqlite3.

SQLite is usually used on desktop and mobile applications. While MySQL is usually used in web applications.

If you are just hearing SQLite, I suggest reading the "SQLite tutorial for beginners" first.

After that, please go to this tutorial.

Okay, we just start ...

Required Tools and Modules

First , make sure sqlite3it's installed on your computer. Because sqlite3 we will use it to create a database.

Please install with the following command:

sudo apt install sqlite3

Press and yhold Enter, then wait until the process is complete.

Second , we need a module sqlite3on nodejs to connect SQLite with Nodejs.

Please create a new project by creating a directory named nodejs-sqlite.

mkdir nodejs-sqlite
cd nodejs-sqlite

After that, run the following command to install the module sqlite3.

npm install sqlite3

Wait until the process is complete ...

Next we will create a new database.

Creating a SQLite Database

The SQLite database is stored in a file .db, we must make this file use the command sqlite3.

Before doing that, please create a new directory named db in the directory nodejs-sqlite.

mkdir db

After that, type the following command to create a new database:

sqlite3 db/playlist.db

We will create a database named playlist.dbwhich will contain a table favorite_songsfor storing a list of favorite songs.

Display above the meaning: Currently we are inside the SQLite shell .

Please exit the shell , by pressing the Ctrl+ button d.

After that, to make sure the database file is successfully created, type the following command.

tree db

If there is a new file named playlis.db, then it means: making the database successful.

Next, we will connect SQLite with Node.

Make Nodejs Connections with SQLite

Please create a new file named db_config.js, then fill in the following code:

const sqlite3 = require('sqlite3').verbose();
const dbFile = __dirname + "/db/playlist.db";

let db = new sqlite3.Database(dbFile, sqlite3.OPEN_READWRITE, (err) => {
    if(err) throw err;
    console.log("Koneksi ke database berhasil!");
});

module.exports = db;

Explanation:

First we import the module sqlite3with the mode verbose() so that it can display an error message.

const sqlite3 = require('sqlite3').verbose();

Then we specify the database file address:

const dbFile = __dirname + "/db/playlist.db";

After that, we try to connect to the database by creating objects dbfrom the class sqlite3.Database().

There are three parameters that we give to this class:

1. Address the path of the database file ( dbFile);
2. Mode used ( sqlite3.OPEN_READWRITE);
3. Callback function to be executed.

There are three types of modes that can be provided for the second parameter:

1. sqlite3.OPEN_READONLY: open the database to read (only) .
2. sqlite3.OPEN_READWRITE: open the database to read and write.
3. sqlite3.OPEN_CREATE: open the database and create a new database if it doesn't already exist.

Then in the last line, we export the object dbso that later it can be imported from other scripts.

module.exports = db;

Okay, now try execution db_config.js.

This means that we have successfully connected to the database playlist.db.

What if it fails?

Usually if it fails it will look like this:

This fails because the name of the database is incorrect or the database does not yet exist.

Next we will create a table, previously please delete this line:

console.log("Koneksi ke database berhasil!");

So as not to interfere.

Create SQLite Tables

We will create a table through Nodejs.

Please create a new file named create_table.js, then fill in the following code:

const db = require('./db_config');

db.serialize(function(){

    let sql = `CREATE TABLE IF NOT EXISTS favorite_songs(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title VARCHAR(64),
        artist VARCHAR(64)
    );`;
    db.run(sql, (err) => {
        if(err) throw err;
        console.log("Table created");
    });

});

db.close();

Explanation:

Before we can execute a query , we must call the function db.serialize()first.

db.serialize(function(){
    // ...
}

This function has a parameter in the form of a callback function . Later in this callback function we will create and execute the query.

In SQL code, we will create a table with names favorite_songs, then in it we create three columns:

1. idto accommodate ID. This column will be PRIMARY KEYand will be automatically entered with AUTOINCREMENT.
2. title to save the song title, 64 characters in length with string type or varchar.
3. artist to save the artist's name, the length is 64 characters with the string type or varchar.

Then we execute sqlto create tables with functions db.run().

Later the message "Table create"will be displayed, if the query execution is successful.

Finally ...

We close the database with a function db.close(), because the connection is no longer used.

Okay, then let's try running the program create_table.js.

To be sure, try entering the SQLite shell .

Follow the following command:

sqlite3 db/playlis.db # masuk ke shell
.tables
.schema favorite_songs

If the results are like this ...

... then we have succeeded in creating a table.

Next we will fill in the data to the table.

Add Data to SQLite

Insert data into SQLite tables, the method is not much different from creating tables.

The steps:

1. Execution db.serialize();
2. then in it make a query;
3. query execution with db.run().

Let's try ...

Please create a new file with the name insert.js and contents as follows:

const db = require('./db_config');

db.serialize(function(){

    let sql = "INSERT INTO favorite_songs (title, artist) VALUES ('First Love', 'Utada Hikaru')";
    db.run(sql, (err) => {
        if(err) throw err;
        console.log("1 record inserted");
    });

});

db.close();

After that, try execution insert.js.

It works!

We only add one data. Next we will try to add more data.

How to?

The method is almost the same.

But when executing a query, we must use looping. Because there is a lot of data to be added.

Let's try ...

Make a new file named insert_multi.js, then fill in the following code:

let db = require('./db_config');

db.serialize(function(){
    let sql = `INSERT INTO favorite_songs (title, artist) VALUES (?,?)`;
    let stmt = db.prepare(sql);
    var songs = [
        ["Dear God", "Avenged Sevenfold"],
        ["No Matter", "Akon"],
        ["No Wonem No Cry", "Bob Marley"],
        ["Umbrella", "Rihana"]
    ];

    songs.forEach((song) => {
        stmt.run(song, (err) => {
            if (err) throw err;
        });
    });
    console.log(`${songs.length} record inserted`);
    stmt.finalize();
    
});

db.close();

Try execution insert_multi.js.

It works!

Pay attention to the program code above ...

Because we will execute many queries, we use statements stmt.

let stmt = db.prepare(sql);

This is usually used for transactions .

Then after that, we make a loop based on the length of the array songsby using the method forEach().

songs.forEach((song) => {
    stmt.run(song, (err) => {
        if (err) throw err;
    });
});

In looping, we execute the query by calling the function run()of the object stmt.

Then we give the parameters songso that the question marks in SQL are filled with data in the array.

Finally ...

We close the transaction with the function stmt.finalize() and close the database with db.close().

Read Data from SQLite

Especially for reading data, the method or function used is different from the others.

There are two methods that can be used:

1. db.all() to retrieve all data from query results;
2. db.get() to retrieve one data from the query results;

The method db.all()will produce an array containing objects from each rowor data.

While the method db.get()will produce an object from the data.

For more details…

... let's try.

Make a new file named select.js, then fill in the following code:

const db = require('./db_config');

db.serialize(function(){

    let sql = "SELECT * FROM favorite_songs";
    db.all(sql, (err, rows) => {
        if (err) throw err;

        if(rows){
            // cetak isi rows
            rows.forEach(song => {
                console.log(`[${song.id}] ${song.artist} - ${song.title}`);
            });
        } else {
            console.log("tidak ada data/hasil");
        }
    });

});

db.close();

The result:

Display the following code ...

db.all(sql, (err, rows) => {
    if (err) throw err;

    if(rows.length > 1){
        // cetak isi rows
        rows.forEach(song => {
            console.log(`[${song.id}] ${song.artist} - ${song.title}`);
        });
    } else {
        console.log("tidak ada data/hasil");
    }
});

The query results will be stored in a variable rows, this variable will contain an array.

Therefore, we use methods forEach()to make repetitions and display contents.

Next we will try to take just one data ...

How to?

The method is the same, we just need to change SQL and the functiondb.all()becomes db.get().

Let's try ...

Make a new file named select_one.js, then fill in the following code:

const db = require('./db_config');

db.serialize(function(){

    let sql = "SELECT * FROM favorite_songs WHERE title=?";
    songTitle = "Umbrella";

    db.get(sql, [songTitle], (err, row) => {
        if (err) throw err;

        if(row){
            // cetak isi row
            console.log(`[${row.id}] ${row.artist} - ${row.title}`);
        } else {
            console.log("Tidak ada data/hasil");
        }
    });

});

db.close();

The result:

Update SQLite Data

There was an error in the title of the song we made. In Akon - No Mattershould true: Akon - Don't Matter.

We have to change it.

How?

The method is the same as data insert, but the SQL is different.

Let's try ...

Make a new file with the name update.js, then fill in the following code:

const db = require('./db_config');

db.serialize(function(){

    let sql = `UPDATE favorite_songs 
        SET title="Don't Matter"
        WHERE id=?`;

    let songId = '43';

    db.run(sql, [songId], (err) => {
        if (!err) console.log("1 record updated");
    });

});

db.close();

After that, try executing:

Pay attention to the code above ...

We give a value '43'for a variable songId, because the id of the data we are editing is 43.

Please adjust the iddata you want to change.

Delete SQLite Data with Nodejs

Just like changing data, deleting data also requires iddetermining which data to delete.

Let's try ...

Make a new file with the name delete.js, then fill in the following code:

const db = require('./db_config');

db.serialize(function(){

    let sql = `DELETE FROM favorite_songs WHERE id=?`;
    let songId = "59";

    db.run(sql, [songId], (err) => {
        if (!err) console.log("Data deleted");
    });

});

db.close();

The result:

In the above example, we remove the data ber- id 59, namely: Rihanna - Umbrella.

Then ... How do I delete all data from the table?

The trick is easy.

Just delete it WHEREin SQL, then all the data in the table will be deleted.

Example: delete_all.js

const db = require('./db_config');

db.serialize(function(){

    let sql = `DELETE FROM favorite_songs`;

    db.run(sql, (err) => {
        if (!err) console.log("All data deleted");
    });

});

db.close();

Then the result:

Reference: https://www.petanikode.com/nodejs-sqlite/