Object-Relational Mapper For SQLite3 In Node.js

Node.js is getting more popular day-by-day, and many frameworks and platforms are supporting Node.js for its high-performance web server development. In this post, you will learn the database development in Node.js with the popular ORM library- Sequelize.

Sequelize is the library that lets you write and manage your database as a JavaScript object, with properties and functions. If you have ever programmed in .NET environment, then you would know what Entity Framework is. Entity Framework is an ORM for .NET projects, letting you write extensive database projects with C# and .NET types. In .NET environments, such as ASP.NET, you can consume those classes.

Although Node.js community is huge, you will still find yourself searching for the projects and libraries for NPM. I did the same. There was a project of mine, where I needed to consume SQLite 3 database and writing the SQL code was prone to errors mostly and on top of everything else, it was difficult. So I needed to find an ORM. Sequelize is what I found, and I want to share the same thoughts with you so that you can also utilize the library in your own projects.

You need to have the basic understanding of how Node.js runtime works, and how your projects are set up for programming. Database know-how is needed, but not a must. We will not be using any extensive SQL queries; instead, I will show you how to write everything as a pure JavaScript function.

Sequelize overview

In this tutorial, I will demonstrate the use of Sequelize, an object-relational mapper for Node.js projects. Sequelize is the best ORM library that I have found on the NPM, plus it offers a wide range of options. You can integrate the same library with many database providers and work with them altogether.



Sequelize is an NPM package, available online, for your Node.js projects.

To learn Sequelize, you can read their documentation on their official website, Sequelize. You can also check the stats for this package on the NPM package manager repository, where you will learn the package itself as well as the overview of who developed and managed the package. Note that, NPM contains thousands of packages, but every package has a default name. This helps keep the packages separate, so the name of the package takes the URL part and that is where you can find the packages. Some very basic information about the package, its owners, and the community contributions is shared on the NPM page, such as,



As you can see, this package is a popular one as it contains several important database engines' support for the development and is currently being developed actively in the community. Thus you can trust the package.

Lastly, the package uses pure JavaScript types to configure and program the databases. Instead of having to write any query, you create the objects and execute functions to do that. You will learn some basics of these functions in the later modules below.

Node.js app development

I will talk about SQLite 3, only. Thus, that is what you need to install. The required packages for your project are,

• SQLite3
• Sequelize
• Optionals
  
  
  • Express
  • Pug

The optional packages are only shared in case you want to add some visuals. The database programming does not rely on these packages, otherwise on dependencies. Sequelize is installed through NPM, the package manager for Node.js projects. First of, you need to add a dependency in your package.json file, and then pull the packages from the server. Here you can do that,

"dependencies": {
    "express": "^4.16.2",
    "pug": "^2.0.0-rc.4",
    "sequelize": "^4.26.0",
    "sqlite3": "^3.1.13"
}

To update the packages, open your NPM command prompt (or any GUI based package manager) and update the packages from the package.json file. This will download and resolve the dependencies for your project.

$ npm update

Once this command finishes, you will have almost everything set up. Now that you have everything in place, you can write the application’s server part to listen to the requests by the users and provide them with the data. Note one thing that to keep tutorial database-only I am not going to create a templated website, just merely a simple res.send would do the trick for me. In my project, I had the file named “app.js” which was used as the server file, you would have your own. In any case, just open the file and add the server-logic. You are suggested to have your own code, my code is not necessary to be used in the project.

The following is my code to make the server run and provide us with a hello world sort of response.

const express = require("express");
let app = new express();

app.get("/", function(req, res) {
    res.send("You are on the home page.");
});

app.get("*", function(req, res) {
    res.send("You're lost in the woods.");
});

let port = process.env.PORT || 12345;
app.listen(port, function() {
    console.log("Server is now listening at localhost:" + port);
});

After this, you should know that your Node.js app is running and ready to accept the database content. This web app will only say one thing, “You are on the home page.” and the lost message will be shown for any other URL that the user might enter. I left the part out because I wanted to configure the database in the next section.

Database programming

The database programming part is the part where only database related content will be shared. Instead of hardcoding everything right in the main file, we can create a separate module for the database file and expose the connection. Even a better practice is to leave everything inside the package, and only execute functions. However, to keep things simpler, here I will expose an object that contains the connection and the models for our database. I created a “database.js” file in the project, to store everything related to the database in the project. I recommend doing this because this way everything will be in their own pages and you will be able to manage everything separately.

First, the connection thing, the connection has to be created. Start off with the package and then check if this is the first time to create the connection or the second time. The code would say what I want to mention here,

const Sequelize = require("sequelize");
var conn;
(async function() {
    if(!conn) {
        try {
            // Initialize the connection on the first run
            conn = new Sequelize("dbName", null, null, {
                dialect: "sqlite",
                storage: "db.sqlite"
            });
            console.log("Connected to the database.");

            // Create the tables
            try {
                await conn.sync({force: true});
                console.log("Created the tables successfully.");
            } catch(error) {
                console.log("Cannot create the tables.\n" + error);
            }
        } catch (error) {
            console.log("There was a problem with connection.\n" + error);
        }
    }
})();

This will make sure we only create the connection object one time. Inside the conditional block, we create the connection and provide the required values, such as,

• storage property
• dialect (it is a must in the Sequelize)

Moving onwards, we create the tables. The “sync” function creates the functions for the models. The “force” property suggests that you need to drop every table before creating it. In SQLite, we create the tables and drop them, which I will show you in a minute.

The models are the tables in our database. I only have created one table, “Employee”, this table contains the data for employees that we are about to create in next sections. Anyways, the code to do that is,

// create the models
const Person = conn.define("Employee", {
    Id: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true },
    Name: Sequelize.STRING,
    Location: Sequelize.STRING,
    Department: Sequelize.STRING,
    Salary: Sequelize.INTEGER
});

module.exports = {
    connection: conn,
    models: {
        personTable: Person
    }
}

A bit of explanation of the code above is, that you create the model by creating a definition in the connection. That tells Sequelize connection which database to create the table in. This way, you will get a model inside one connection, and you can have multiple connections and multiple models. This will let you manage,

• Employees in Office database
• Departments in Office database
• Opportunities in the Sales database
• Salary in Accounts database

You can argue that they can be a table in one database, but just as an example. You can include other types of tables (objects) in a main large database. Lastly, just include the package inside your app.js file to support the database in the server itself,

let db = require("./src/database");

Now, we can continue reading and writing the objects.

Reading the objects

Now that our basic workflow has been implemented, we can continue to write the code to read the objects from the database. Open the app.js file once again, and write the following code over there.

// Routers
app.get("/getPeople", async function(req, res) {
    let people = await db.models.personTable.findAll();

    if(people.length == 0) {
        res.send("No record exists in the database right now for Person table.");
    } else {
        var buffer = "";
        var index = 1;
        for (person of people) {
            buffer += `#${index++}: ${person.Name} lives in ${person.Location}.<br />`;
        }

        res.send(buffer);
    }
});

The code above is the simple router, that would listen to the requests coming at “getPeople” URL. We are trying to capture the people from the table. To understand how this works, please check the module.exports line in the code above. You will see how this works. Later on, the code is only to check whether there are any people in the database or not.

Currently, we do not have any data in the database, this means that it is going to show the message that there is no record in the database. In the next section, we will add some data and then refresh the page to see how this works. Upon running the app, you see the following logs in the console,

Connected to the database.Server is now listening to localhost:12345

Executing (default): DROP TABLE IF EXISTS `Employees`;
Executing (default): DROP TABLE IF EXISTS `Employees`;
Executing (default): CREATE TABLE IF NOT EXISTS `Employees` (`Id` INTEGER PRIMARY KEY AUTOINCREMENT, `Name` VARCHAR(255), `Location` VARCHAR(255), `Department` VARCHAR(255), `Salary` INTEGER, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`Employees`)

Created the tables successfully.

This means that it tries to remove and create the database tables for the model. Now we can navigate to the page where we expect to find the content,


This demonstrates, how the objects are selected. Sequelize only needs us to do one single thing,

let people = await db.models.personTable.findAll();

And all by itself, selects the objects using SQL query for the database engine we have used. Note that we did not need to write any SQL command, whereas this is the command that got executed in the background.

Executing (default): SELECT `Id`, `Name`, `Location`, `Department`, `Salary`, `createdAt`, `updatedAt` FROM `Employees` AS `Employee`;

This is just one of the areas where an ORM can help us, we no longer need to maintain a record of writing SQL, and managing the protection of the tables. Moreover, you can see there are some extra columns in the table,

• createdAt
• updatedAt

These columns, are created by Sequelize to manage and maintain the record creation in the database. They can help you understand when a specific column of data was inserted in the table, for logging purposes and debugging or management purposes.

Inserting the objects

Now let us try to insert the objects into the database and then see how our program works. Again, just to keep the tutorial tidy, I will be trying to save the records myself in a special request path, where. This will save some time of mine, as well as yours when you will not need to read a lot of code, which is irrelevant to the article itself. Just created the route,

app.get("/createPeople", async function(req, res) {
    // Try creating some objects
    let list = [
        { Name: "Person 1", Location: "Area 1", Department: "Unknown", Salary: 10000 },
        { Name: "Person 2", Location: "Area 2", Department: "Less Known", Salary: 55000 },
        { Name: "Person 3", Location: "Area 3", Department: "Commonly Known", Salary: 50500 },
        { Name: "Person 4", Location: "Area 4", Department: "Reknown", Salary: 90500 }
    ];

    // Save in the database.
    try {
        for (item of list) {
            await db.models.personTable.create(item);
        }

        res.redirect("/getPeople");
    } catch (error) {
        console.log("There was an error saving the person.\n" + error);
        res.redirect("/lost");
    }
});

This router will automatically redirect me to the page where we print the people. One thing that you need to pay attention to is, the fact that to create the objects, we need to pass a JavaScript object that contains the attributes needed to create the objects. After executing this code, our page shows the people that were selected.


This way, we can capture everything from the database. Of course there are other ways of selecting the objects, such as with the WHERE clause but I skipped that for the sake of a shorter tutorial. Even the result above, was fetched from the database after the insertion of the data, all of that was managed by the SQLite wrapper and the SQL queries were parsed by the ORM itself,

Executing (default): INSERT INTO `Employees` (`Id`,`Name`,`Location`,`Department`,`Salary`,`createdAt`,`updatedAt`) VALUES (NULL,'Person 1','Area 1','Unknown',10000,'2017-12-05 18:39:58.925 +00:00','2017-12-05 18:39:58.925 +00:00');
Executing (default): INSERT INTO `Employees` (`Id`,`Name`,`Location`,`Department`,`Salary`,`createdAt`,`updatedAt`) VALUES (NULL,'Person 2','Area 2','Less Known',55000,'2017-12-05 18:39:59.260 +00:00','2017-12-05 18:39:59.260 +00:00');
Executing (default): INSERT INTO `Employees` (`Id`,`Name`,`Location`,`Department`,`Salary`,`createdAt`,`updatedAt`) VALUES (NULL,'Person 3','Area 3','Commonly Known',50500,'2017-12-05 18:39:59.646 +00:00','2017-12-05 18:39:59.646 +00:00');
Executing (default): INSERT INTO `Employees` (`Id`,`Name`,`Location`,`Department`,`Salary`,`createdAt`,`updatedAt`) VALUES (NULL,'Person 4','Area 4','Reknown',90500,'2017-12-05 18:39:59.882 +00:00','2017-12-05 18:39:59.882 +00:00');
Executing (default): SELECT `Id`, `Name`, `Location`, `Department`, `Salary`, `createdAt`, `updatedAt` FROM `Employees` AS `Employee`;

In the code above, you can see how the time was managed by the Sequelize and the update and create times were stored in the database. Once you update the values, your updated values would be in the database telling you when the particular object was updated in the database.

Onwards

In this post, we merely uncovered the vast topic of database programming and how Node.js helps us in that. We saw how to install the packages for Node.js database programming, how to create the models and establish the connection with the database.

We then moved onwards to create the routing for the web app, where we will access the resources. These routings can be configured in other ways and formats too. But we did not focus on any of those because that will make the tutorial a huge mini-ebook. Now that you know how to write database projects in Node.js, you can consider writing the code to update the objects, and delete them as needed.

You also need to understand how the Sequelize was working in the background to port the same project to other database types; such as MongoDB or PostgreSQL. Mostly, those databases are used for the projects instead of SQLite. Since SQLite was a lightweight database, I used it in the tutorial — you can surely use other types.

Moreover, you need to configure a few things,

• Pug templates
• Asynchronous programming
• URL routing

In your own enterprise apps with Node.js, you would need to manage how your ORM communicates with these plugins and pipeline objects. Managing all this would let you create a full powered web app in Node.js, and this would wrap up the tutorial for you. Although I was unable to cover everything since the API of Sequelize was huge, yet I tried to uncover most of the aspects of this framework to get you started.