CRUD operations with SQLite in Express.js – Java Code Geeks

I show You how To Make Huge Profits In A Short Time With Cryptos!

Hello. In this tutorial, we are going to 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 light in terms of configuration, database administration and required resources. It is stand-alone, serverless, configuration-free, transactional.

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

1.1 Configuring Node.js

To install Node.js on Windows, you will need to download the installer from this connect. Click the installer (also include NPM package manager) for your platform and run the installer to get started with the Node.js setup wizard. Follow the steps of the wizard and click Finish when you are finished. If all goes well, you can go to the command prompt to check if the installation was successful, as shown in Figure 1.

Fig. 1: Verifying the installation of the node and npm

2. CRUD operations with SQLite in Express.js

To configure the application, we will need to navigate to a path where our project will reside. To program stuff I use Visual Studio code like my favorite IDE. You are free to choose the IDE of your choice.

2.1 Establishment of the implementation

Let’s write the different files that will be needed for practical learning.

2.1.1 Configuring dependencies

Go to the project directory and run npm init -y to create a package.json to file. This to file contains the relevant metadata for the project and is used to manage the project dependencies, script, version, etc. Add the following code to the file where 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 path of the directory containing the file and use the npm install order. If all goes well the dependencies will be loaded into the node_modules folder and you’re ready to go to the next steps.

2.1.2 Creating a database configuration

Create the database configuration in the model folder that will be responsible for configuring the details of the table.

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 template file

Create a model class model folder that will be responsible for creating the structure of the table and mapping the entity. The table structure will be created as soon as the application is successfully launched using the sequel library. Sequencing is a promise-based orm nodejs for databases. It provides support for transactions, relationships, fast and lazy loading, read replication, and 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 file that will be responsible for processing the customer’s incoming request and providing 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 for mapping the incoming request from the client to the method of the controller.

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 which serves as a home point for our app and will also be responsible for loading the default profile data into the table once the app 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.

Order

$ npm start

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

Console output

[email protected] MXXXXX4 ~/mycode/frontend/Node/nodejs-sqlite (main)
$ npm start

> [email protected] start C:UsersyatinmycodefrontendNodenodejs-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 factor or any other tool of your choice to make the HTTP request to the application endpoints.

End points

// 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’s it for this tutorial and I hope the article has served you with everything you were looking for. Happy learning and don’t forget to share!

5. Summary

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

6. Download the project

This was a tutorial for performing raw operations with SQLite in a nodejs application.



Source link