How To Connect SQLite With Node.js

Introduction

 

In this article, we will learn how to use connect SQLite with NodeJS and how to perform the CRUD operations with SQLite and NodeJS.

NodeJS

Node.js® is a JavaScript runtime built on Chrome's V8 JavaScript engine. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient. To know more visit here.

Reference: https://nodejs.org/

SQLITE

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects. It is most widely used for Mobile Apps Development. To know more visit the following link

Reference - https://www.sqlite.org/about.html   

Requisition:

Getting started with node JS, we need to install node JS in our PC or Laptop. We can download Node JS from the following link and install it to your PC.

Reference - https://nodejs.org/en/download/

Steps

I have split this part into two steps as in the following.

Step 1 - Setting up the project with SQLite.

Step 2 - Implementation of CRUD operations.

Step 1 - Setting up the project with SQLite

1. We will create a project named as “nodejs-sqlite” and in that create a package file as “package.json”. Paste the following in it.
   

   {
     "name": "nodejs-sqlite",
     "version": "1.0.0",
     "dependencies": {
       "sqlite3": "^ 4.0.0"
     }
   }

2. Here, we have to download the “SQLite3 with version 4.0.0”. To install the dependencies, open command prompt and redirect the terminal to the root directory of the project.
   
   

3. Then type “npm install” and hit enter. This will install the dependencies to node_modules and make your project ready to work.

Step 2 - Implementation of CRUD operations

In this part, we will see how to create DB with SQLite and how to perform CRUD Operations.

1. The following code will create the SQLite database. We have to create a “JS” file and in my case, I have created the JS File named as js. The file will be run by executing the command “node server.js”.
2. Then we have to import the SQLite dependency to the file.
   

   var sqlite3 = require('sqlite3').verbose();
   var db = new sqlite3.Database('./mydb.db3');

   mydb.db3 is automatically created if the DB does not exist in the path.

3. CREATE TABLE
   The following code shows how to create a table. Normal SQL Query will create the table.
   

   // Create Table
   db.serialize(function() {
       db.run("CREATE TABLE IF NOT EXISTS Users (Name TEXT, Age INTEGER)");
   });

4. INSERT INTO TABLE
   The following code shows how to insert data to table.
   

   // Insert into Table
   db.serialize(function() {
       db.run("INSERT into Users(Name,Age) VALUES ('Mushtaq',24)");
       db.run("INSERT into Users(Name,Age) VALUES ('Fazil',23)");
   }

5. SELECT FROM TABLE
   The following code shows how to select data from table.
   

   // Select All Data
   db.serialize(function() {
       db.all("SELECT * from Users",function(err,rows){
           if(err)
                                {
               console.log(err);
           }
           else{
               console.log(rows);
           }
       });
   });

   The above code will show the inserted data from table like the following figure.
   
   

6. UPDATE TO TABLE
   The update operations will be achieved with Normal Transaction SQL run. The following code will show the Update Operation with SQLite.
   

   //Perform UPDATE operation
   db.run("UPDATE table_name where condition");

7. DELETE FROM TABLE
   The following code will show the delete Operation with SQLite.
   

   //Perform DELETE operation
   db.run("DELETE * from table_name where condition");

Download Code

If you found this article to be informative, do like and star the repo on GitHub. You can download the full sample code from GitHub.