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.
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.
You can download the full source code of this example here: CRUD Operations with SQLite in Express.js
Thx, it’s what i was looking for :D