Home » JavaScript » CRUD Operations with SQLite in Express.js

About Yatin Batra

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

CRUD Operations with SQLite in Express.js

Hello. In this tutorial, we will perform CRUD operations in an Express.js application with SQLite.

1. Introduction

SQLite is a software library that provides a relational database management system. It is lightweight in terms of setup, database administration, and required resources. It is self-contained, serverless, zero-configuration, transactional.

  • Self-contained means that it require minimal support from the operating system or any external library
  • Zero-configuration means that no external installation is required before using it
  • Transactional means it is fully ACID-compliant i.e. all queries and changes are atomic, consistent, isolated, and durable

1.1 Setting up Node.js

To set up Node.js on windows you will need to download the installer from this link. Click on the installer (also include the NPM package manager) for your platform and run the installer to start with the Node.js setup wizard. Follow the wizard steps and click on Finish when it is done. If everything goes well you can navigate to the command prompt to verify if the installation was successful as shown in Fig. 1.

crud sqlite Express.js - npm installation
Fig. 1: Verifying node and npm installation

2. CRUD Operations with SQLite in Express.js

To set up the application, we will need to navigate to a path where our project will reside. For programming stuff, I am using Visual Studio Code as my preferred IDE. You’re free to choose the IDE of your choice.

2.1 Setting up the implementation

Let us write the different files which will be required for practical learning.

2.1.1 Setting up dependencies

Navigate to the project directory and run npm init -y to create a package.json file. This file holds the metadata relevant to the project and is used for managing the project dependencies, script, version, etc. Add the following code to the file wherein we will specify the required dependencies.

package.json

{
  "name": "nodejs-sqllite",
  "version": "1.0.0",
  "description": "crud operations with nodejs and sqlite",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon index.js"
  },
  "keywords": [
    "nodejs",
    "sequelize",
    "express",
    "sqlite3"
  ],
  "author": "javacodegeeks",
  "license": "ISC",
  "dependencies": {
    "express": "4.17.1",
    "sequelize": "6.12.0-alpha.1",
    "sqlite3": "5.0.2"
  },
  "devDependencies": {
    "nodemon": "2.0.15"
  }
}

To download the dependencies navigate to the directory path containing the file and use the npm install command. If everything goes well the dependencies will be loaded inside the node_modules folder and you are good to go with the further steps.

2.1.2 Creating a database config

Create the database config in the model folder that will be responsible to set up the table details.

dbconfig.js

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize("test-db", "user", "pass", {
  dialect: "sqlite",
  host: "./dev.sqlite"
});

module.exports = sequelize;

2.1.3 Creating a model file

Create a model class model folder that will be responsible to create the table structure and mapping the entity. The table structure will be created as soon as the application is started successfully with the help of the sequelize library. Sequelize is a promise-based nodejs orm for databases. It provides transaction support, relations, eager and lazy loading, read replication, and much more.

profile.js

// represents the  model
const { Model, DataTypes } = require("sequelize");
const sequelize = require("./dbconfig");

class Profile extends Model {}

Profile.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING
    },
    email: {
      type: DataTypes.STRING
    },
    phone: {
      type: DataTypes.STRING
    }
  },
  {
    sequelize,
    modelName: "profile",
    timestamps: false
  }
);

module.exports = Profile;

2.1.4 Creating a controller file

Create a controller file in the controller folder that will be responsible to handle the incoming request from the client and provide with a response. The model class object will interact with the table using the orm capability provided by the Sequelize library.

profileController.js

// represents the jpa layer to fetch data from db
const Profile = require("../model/profile");

const getAllProfiles = async (req, res) => {
  const profiles = await Profile.findAndCountAll();
  res.send({
    context: profiles.rows,
    total: profiles.count
  });
};

const getProfile = async (req, res) => {
  const id = req.params.id;
  await Profile.findOne({ where: { id: id } }).then((item) => {
    if (item != null) {
      res.send(item);
    } else {
      res.sendStatus(404);
    }
  });
};

const saveProfile = async (req, res) => {
  const profile = {
    name: req.body.name,
    email: req.body.email,
    phone: req.body.phone
  };
  await Profile.create(profile).then(() => {
    res.sendStatus(201);
  });
};

const updateProfile = async (req, res) => {
  const id = req.params.id;
  await Profile.findByPk(id).then((item) => {
    if (item != null) {
      item
        .update({
          name: req.body.name,
          email: req.body.email,
          phone: req.body.phone
        })
        .then(() => {
          res.sendStatus(204);
        });
    } else {
      res.sendStatus(404);
    }
  });
};

const deleteProfile = async (req, res) => {
  const id = req.params.id;
  await Profile.findByPk(id).then((item) => {
    if (item != null) {
      item.destroy();
      res.sendStatus(200);
    } else {
      res.sendStatus(404);
    }
  });
};

module.exports = {
  getAllProfiles,
  getProfile,
  saveProfile,
  updateProfile,
  deleteProfile
};

2.1.5 Creating a routing file

Create a routing file in the routes folder that will be responsible to map the incoming request from the client with the controller method.

profileRoutes.js

// represents the router class
const express = require("express");
const {
  getAllProfiles,
  getProfile,
  saveProfile,
  deleteProfile,
  updateProfile
} = require("../controller/profileController");

const router = express.Router();

// http://localhost:3005/api/profiles
router.get("/profiles", getAllProfiles);

// http://localhost:3005/api/profile/id
router.get("/profile/:id", getProfile);

// http://localhost:3005/api/profile
/*
{
    "name": "{{$randomFullName}}",
    "email": "{{$randomEmail}}",
    "phone": "{{$randomPhoneNumber}}"
}
*/
router.post("/profile", saveProfile);

// http://localhost:3005/api/profile/id
/*
{
    "name": "{{$randomFullName}}",
    "email": "{{$randomEmail}}",
    "phone": "{{$randomPhoneNumber}}"
}
*/
router.put("/profile/:id", updateProfile);

// http://localhost:3005/api/profile/id
router.delete("/profile/:id", deleteProfile);

module.exports = {
  routes: router
};

2.1.6 Creating an index file

Create an index file that serves as the welcome point for our application and will also be responsible to load the default profile data in the table once the application is started successfully.

index.js

// automatically creating table on startup and inserting data
const sequelize = require("./model/dbconfig");
const Profile = require("./model/profile");

// default loading data
sequelize.sync({ force: true }).then(async () => {
  console.log("db is ready... inserting sample data...");
  for (let i = 1; i < 11; i++) {
    let num = Math.floor(Math.random() * 9000000000) + 1000000000;
    const profile = {
      name: `profile${i}`,
      email: `profile${i}@automation.com`,
      phone: num.toString()
    };
    await Profile.create(profile);
  }
  console.log("sample data inserted...");
});

// application
const express = require("express");
const profileRoutes = require("./routes/profileRoutes");

const app = express();
app.use(express.json());

// application routes
app.get("/", (req, resp) => resp.send("application is up and running"));

app.use("/api", profileRoutes.routes);

const PORT = process.env.PORT || 3005;
app.listen(PORT, () => {
  console.log(`Service endpoint = http://localhost:${PORT}`);
});

3. Run the Application

To run the application navigate to the project directory and enter the following command as shown below.

Command

$ npm start

If everything goes well the application will be started successfully on a port number read from the .env file. In this case, the application will be started successfully on a port number – 3005.

Console output

yatin@XX-XXXXXXX MXXXXX4 ~/mycode/frontend/Node/nodejs-sqlite (main)
$ npm start

> nodejs-sqllite@1.0.0 start C:\Users\yatin\mycode\frontend\Node\nodejs-sqlite
> nodemon index.js

[nodemon] 2.0.15
[nodemon] to restart at any time, enter `rs`
[nodemon] watching path(s): *.*
[nodemon] watching extensions: js,mjs,json  
[nodemon] starting `node index.js`

Service endpoint = http://localhost:3005
Executing (default): DROP TABLE IF EXISTS `profiles`;
Executing (default): DROP TABLE IF EXISTS `profiles`;
Executing (default): CREATE TABLE IF NOT EXISTS `profiles` (`id` INTEGER PRIMARY KEY, `name` VARCHAR(255), `email` VARCHAR(255), `phone` VARCHAR(255));
Executing (default): PRAGMA INDEX_LIST(`profiles`)
db is ready... inserting sample data...
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
Executing (default): INSERT INTO `profiles` (`id`,`name`,`email`,`phone`) VALUES ($1,$2,$3,$4);
sample data inserted...

4. Demo

You are free to use postman or any other tool of your choice to make the HTTP request to the application endpoints.

Endpoints

// HTTP GET - Get all profiles
// http://localhost:3005/api/profiles

// HTTP GET - Get profile by id
// http://localhost:3005/api/profile/id

// HTTP POST - Create a new profile
// http://localhost:3005/api/profile
/*
{
    "name": "{{$randomFullName}}",
    "email": "{{$randomEmail}}",
    "phone": "{{$randomPhoneNumber}}"
}
*/

// HTTP PUT - Update a profile
// http://localhost:3005/api/profile/id
/*
{
    "name": "{{$randomFullName}}",
    "email": "{{$randomEmail}}",
    "phone": "{{$randomPhoneNumber}}"
}
*/

// HTTP DELETE - Delete a profile
// http://localhost:3005/api/profile/id

That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

5. Summary

In this tutorial, we learned how to perform crud operations with SQLite in a nodejs application via express and Sequelize. You can download the source code and the postman collection from the Downloads section.

6. Download the Project

This was a tutorial to perform crud operations with SQLite in a nodejs application.

Download
You can download the full source code of this example here: CRUD Operations with SQLite in Express.js

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Adria
Adria
28 days ago

Thx, it’s what i was looking for :D