SQLite is one of the relational databases. All the necessary details which are related to the database are stored in a file. When we are copying a database from one machine to another, then this is nothing but a file with no complex commands or anything which we may need in our software database management systems. We will only need to transfer the file.

SQLite database is very light and easy to understand. It is also really easy to set up the whole installation process and also the use. We do not need a complex data server setup or heavy connection object in this.

This database also has the feature to support concurrent access by multiple users in the system. As the data is stored in a file, we will also need to take care of the security of the database. It does not validate the data types.

Post Graduate Program: Full Stack Web Development

in Collaboration with Caltech CTMEEnroll Now
Post Graduate Program: Full Stack Web Development

Setup and Installation

We are going to start our set and installation process by creating a new npm package using the npm init inside an empty directory which is named as node js-sqlite.

$ npm init

This utility will walk you through creating a package.json file.

It only covers the most common items, and tries to guess sane defaults.

See `npm help json` for definitive documentation on these fields

and exactly what they do.

Use `npm install <pkg> --save` afterwards to install a package and

save it as a dependency in the package.json file.

Press ^C at any time to quit.

name: (app) nodejs-sqlite

version: (0.0.0) 0.1.0

description: Code and tutorial for node js and sqlite

entry point: (index.js) main.js

test command: 

git repository: 

keywords: 

author: Adam McQuistan

license: (BSD) MIT

About to write to /node-sqlite/app/package.json:

{

  "name": "nodejs-sqlite",

  "version": "0.1.0",

  "description": "Code and tutorial for node js and sqlite",

  "main": "main.js",

  "scripts": {

    "test": "echo \"Error: no test specified\" && exit 1"

  },

  "repository": "",

  "author": "John Doe",

  "license": "MIT"

}

Is this ok? (yes) 

Now we will install the sqlite3 package via npm package which is necessary for proceeding in the installation process. Here is the code for that:

$ npm install --save sqlite3

After installing sqlite3 we are going to install Bluebird. It will help us to use the familiar promise functionality in our database management programming.

$ npm install --save bluebird

Now we will create an empty file right next to the package.json file which is called sqldatabase.sqlite3 so that the SQLite will store data in the database.

Full Stack Web Developer Course

To become an expert in MEAN StackView Course
Full Stack Web Developer Course

Designing the Database:

Here, we are building out the data access layer for a project and task tracking application. The basic business rules that needs to be followed for this application's data access layer are as follows:

  • The application has some required projects
  • Each project should have one or more tasks to complete in the application.

With all the business rules that we have stated above, we can now take that information and start designing the necessary tables and their fields for the application. It is totally clear that we will need a projects table as well as a tasks table. For the rest, we will just use a little intuition, some made-up test data, and at the end roll with it (a common work characteristic for most of the developers in the software development industry).

Communication Between SQLite and Nodejs

Now in this section of the article, we will try to elaborate the connection between SQLite and Nodejs. First, we will install sqlite3 in our workspace folder location by running the following command in cmd:

npm install sqlite3

It is also required to import the sqlite3 module to facilitate the connection between them in our code editor. This whole process can be done by running the command given below:

const sqlite3 = require('sqlite3');

This module has methods that can be really helpful to us to communicate with sqlite3 from Nodejs. When we open a database, the database is normally a single file in SQLite, so we just need to pass the path of the database. Here we are going to use the DB file for which the link is given above in the SQLite section.

const db = new sqlite3.Database('./chinook.db');

Retrieving All Rows

Retrieving all the rows in the SQLite database is a really crucial step in database management systems. If we want to get all the information from the database then we should use a query, a query is a statement in database management systems that requests some specific information from the database.

We will use the all() method for getting all the rows from the database. This method will allow us to run a query and call a callback to access the rows in the final set.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Retrieving all Rows from the database of our system

dbase.all("SELECT empId, fName FROM employees", (error, rows) => {

    rows.forEach((row) => {

        console.log(row.empId + " " + row.fName);

    })

});

We will also get an error object which contains all the error details that will tell us that if there is any problem in the execution of the query passed else it will be null after the execution. The rows object contains the result of the query in the program provided above.

Full Stack Java Developer Course

In Partnership with HIRIST and HackerEarthEXPLORE COURSE
Full Stack Java Developer Course

Retrieving a Single Row

There are also some situations that arise where we want to check if our query is working efficiently or not, and we do not want to retrieve all the rows from the database, but just one row would be enough. Also in some other cases, we might need to be sure that our query is going to return one row as it does in querying based on the Primary key.

So to handle the situations mentioned above, we can use the get () method. It helps us to retrieve just a single row from the database in our system.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Retrieving a single Row from the database of our system

db.get("SELECT empId, fName FROM employees", (error, row) => {

    console.log(row.empId + " " + row.fName);

});

Retrieving Data Based on Placeholder

In database management systems, we use queries where we might want to pass a dynamic value to the condition added, else without the dynamic value we have to hard code all the things which may not be the case we always wanted for our systems.

We need to satisfy the above scenario, so for that, we use a placeholder. Whatever value we need to pass to the placeholder will get replaced in the query thus giving it a dynamic nature that we always wanted.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Retrieving all the data based on Placeholder in our database system

db.all("SELECT EmpId, fName FROM employees where title=$title", {

        $title: 'Customer Support Officer'

    },

    (error, rows) => {rows.forEach((row) => {

        console.log(row.EmpId + " " + row.fName);

    })

});

In the program given above, we have included a placeholder for the title of the database to search based on ‘Customer Support Officer’, replacing this value will change the query very easily with no need to touch it.

Executing run() Method:

All of the above methods that we have discussed return some rows as a result, but some queries do not return any result for example: creating a table, dropping a table, inserting a row.

So, in this scenario, for cases like these, we normally use the run() method in our database. It will not just return any result but it may also attach an additional property to this keyword within the scope of the callback of this method. If we take an example of inserting a row, then it will attach lastID to this database, it’s a property that is used to store the value of the last inserted row ID from the provided data.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Executing run() Method in our database system

db.run(`Insert into playlists(Name) values(?)`, 

    ['Indie'],

    function(error){

        console.log("New playlist has been added with id " + this.lastID);

    }

);

Free Course: Getting Started with NodeJS

Master the Basics of NodeJSEnroll Now
Free Course: Getting Started with NodeJS

Using SQLite each() Method Instead of forEach():

As we have already described, there might be a case where we will have to perform a similar operation on all the returned rows from the query of the database. If we consider our db.all() method example, then we use javascript forEach() method to iterate over each row from the query.

So in this situation, here we can use each() method. This is a really useful method that can play a big role in these scenarios. This method mainly takes the query and a callback function as an argument for your system, and then it will run the callback on each row of the result from your database.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Using the SQLite each() method instead of forEach() for our database

db.each("SELECT EmpId, fName FROM employees limit 10",

    (error, row) => {

        console.log(row.EmpId + " " + row.fName);

    }

);

Running Queries Synchronously

If we assume a requirement where we need to drop a table, then we should create a table, and then insert some rows in it. If we put all the queries separately and execute them, they all will be running parallel in our database system. It could also occur that the drop query will get executed and before executing create query insert query gets executed which will create an error for that system.

When we face the above scenario, then that can also be solved by writing some queries in the callback. Like when we create a query then it can easily go into the callback of drop query again, and insert query can go into the callback of creating table query for the required condition of an application.

const sqlite3 = require('sqlite3');

const dbase = new sqlite3.Database('./chinook.db');

//Running queries synchronously without serialize method of the system

db.run("DROP TABLE all the playlists", function(error){

    db.run("CREATE TABLE playlists([playId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[name] NVARCHAR(120))", function(error){

        db.run("INSERT INTO playlists (name) VALUES  ('TV shows'), ('Movies'), ('Web series')");

    });

});

However, in this case, we have to keep indenting the next query in the callback of our database of the previous one at some point, it may become complex to handle in our database management systems.

So, if we want to avoid the above kind of scenario we will need to run them in a synchronized manner of our database. We use the serialize() method for the execution of this method.

const sqlite3 = require('sqlite3

const dbase = new sqlite3.Database('./chinook.db');

//with serialize method

db.serialize(() => {

    db.run("DROP TABLE all the playlists");

    db.run("CREATE TABLE playlists([playId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,[Name] NVARCHAR(120))");

    db.run("INSERT INTO the playlists (name) VALUES  ('TV Shows'), ('Movies'), ('Web Series')");

});

Master front-end and back-end technologies and advanced aspects in our Post Graduate Program in Full Stack Web Development. Unleash your career as an expert full stack developer. Get in touch with us NOW!

Conclusion

In this article on  Node Js Sqlite, we have illustrated almost all the Node Js Sqlite with proper examples. The article starts with a brief introduction to SQLite and then we directly start elaborating the various types of setup and installation in SQLite. We have provided a brief overview of how to design the database and the communication between SQLite and Node js. We have explained retrieving all rows, retrieving a single row, and retrieving data based on placeholders and also their examples. After the explanation, we have also illustrated the topics like executing the run method, using SQLite each method instead of forEach(), and running queries synchronously with an example for a better understanding of the topic. We hope that this article was able to give you a thorough knowledge of Node Js Sqlite and how we can use it in our software development projects.

To know more about the Node Js Sqlite, you can enroll in the Post-Graduate Program In Full-Stack Web Development offered by Simplilearn in collaboration with Caltech CTME. This Web Development course is a descriptive online bootcamp that includes 25 projects, a capstone project, and interactive online classes. In addition to the  Node Js Sqlite and other related concepts, the course also details everything you need to become a full-stack technologist and accelerate your career as a software developer.

Simplilearn also offers free online skill-up courses in several domains, from data science and business analytics to software development, AI, and machine learning. You can take up any of these free courses to upgrade your skills and advance your career.

If you have any questions, feel free to post them in the comments section below. Our team will get in touch with you at the earliest.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors