How To Use SQLite With Node JS (JavaScript,ES)

February 13, 2020

How to Use SQLite With Node JS (JavaScript,ES)

How  To Use SQLite With Node.js  (JavaScript,ES)

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, server less, 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.

    or

    You can use npmto do so using the following command: npm install sqlite3

    {  
     "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.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.run("INSERT into Users(Name,Age) VALUES ('Frit',23)");  
  5. SELECT FROM TABLE
    The following code shows how to select data from table.

    // Select All Data  
      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 in logs

  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");  

Written by Manoj Bhardwaj who lives and works in Mohali Punjab (India). My stackoverflow