Moment For Technology

Use Sequelize to quickly build CRUD operations on PostgreSQL data

Posted on Dec. 3, 2022, 9:09 a.m. by Melissa Evans
Category: The back-end Tag: The back-end restful postgresql

[This is the fourth day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.]

I've written a column titled "Preaching APIS" about THE REST style of apis and recommended practices. Today, to start building an API service for a management system, the first thing you need to deal with is data storage. This article will summarize the CRUD operations for building PostgreSQL data quickly using Sequelize under NodeJS.

Project source code:


Sequelize is a Promise-based Node.js ORM tool with strong transaction support, association, prefetch and lazy load, read replication, and other features. Sequelize supports the following databases: PostgreSQL, MySQL, MariaDB, SQLite and MSSQL.

Sequelize is a top-level object that references the Sequlize module to create an instance of Sequlize. It can also be used to obtain references to other objects in the module, such as the Utils utility class, Transaction class, and so on. Once an instance is created, you can use the instance to create or define models, perform queries, synchronize database structures, and so on.

Official website:

Add and Configure

Before installing the modules, first install the development tool sequelize-cli

sudo npm install -g sequelize-cli
Copy the code

Next, install the modules related to the data store in the project directory.

npm install  sequelize --save
npm install pg pg-hstore  --save
Copy the code

Now create the file. Sequelizerc in the project root directory.

const path = require('path');

module.exports = {
  "config": path.resolve('./config', 'db.json'),
  "models-path": path.resolve('./models'),
  'seeders-path': path.resolve('./seeders'),
  'migrations-path': path.resolve('./migrations')
Copy the code

This file tells Sequelize to initialize to generate the Config and models to a specific directory. Next, enter the command to initialize Sequelize.

sequelize init
Copy the code

This command will create the config/db.json, models/index.js, migrations, and seeders directories and files. After executing the command, open and edit config/db.json to configure the database connection information.

{ "development": { "username": "dbusername", "password": "dbpassword", "database": "crayon-admin", "host": "", "dialect": "postgres", "options": {"operatorsAliases": false}, "logging": false}, "test": {"username": "Dbusername ", "password":" dbPassword ", "database": "crayon-admin", "host": "", "dialect": "postgres" }, "production": { "username": "dbusername", "password": "dbpassword", "database": "crayon-admin", "host": "", "dialect": "postgres"}}Copy the code

Directory description:

  • migrations: All files to be migrated are approvedsequelize db:migrateCreate the corresponding data table
  • seeders: Seed file, which initializes the data needed to be inserted into the database, runsequelize db:seed:all

Create Models and Migrations

Use the CLI tool Sequelize-CLI to create the Administrators table

sequelize model:create --name administrators --attributes id:integer,add_time:integer,last_login:integer,username:string,email:string,login_ip:string
Copy the code

Two files are generated

  • /src/migrations/20210803095520-create-administrators.js: Creates a database table script for database initialization.
"use strict"; module.exports = { up: async (queryInterface, Sequelize) = { await queryInterface.createTable("administrators", { id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER, }, id: { type: Sequelize.INTEGER, }, add_time: { type: Sequelize.INTEGER, }, last_login: { type: Sequelize.INTEGER, }, username: { type: Sequelize.STRING, }, password: { type: Sequelize.STRING, }, email: { type: Sequelize.STRING, }, login_ip: { type: Sequelize.STRING, }, }); }, down: async (queryInterface, Sequelize) = { await queryInterface.dropTable("administrators"); }};Copy the code
  • /src/models/administrators.js: Generated model file
"use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) = { class administrators extends Model {} administrators.init( { id: { type: DataTypes.INTEGER, primaryKey: true, }, add_time: DataTypes.INTEGER, last_login: DataTypes.INTEGER, username: DataTypes.STRING, password: DataTypes.STRING, email: DataTypes.STRING, login_ip: DataTypes.STRING, }, { sequelize, indexes: [ { unique: true, fields: ["id"], }, ], freezeTableName: true, timestamps: False, // Whether to automatically add a timestamp createAt, updateAt modelName: "Administrators ",}); return administrators; };Copy the code

Now execute the command:

sequelize db:migrate
Copy the code

After the command is successfully executed, the table Administrators is created in the connected database.

Create a seed

The SEED is used to initialize the insert data. Administrators, for example, need to create a default account before the system runs, and these default account information is written in the seed file. The seed command is as follows:

sequelize seed:create --name administrator
Copy the code

This will create a file in the Seeders folder and change the code as follows:

"use strict"; module.exports = { up: async (queryInterface, Sequelize) = { /** * Add seed commands here. * * Example: * await queryInterface.bulkInsert('People', [{ * name: 'John Doe', * isBetaMember: false * }], {}); */ await queryInterface.bulkInsert( "administrators", [ { id:1, username: "administrators", password: "devpoint", email: "[email protected]", add_time:1627828617, last_time:1627828617 }, ], {} ); }, down: async (queryInterface, Sequelize) = { /** * Add commands to revert seed here. * * Example: * await queryInterface.bulkDelete('People', null, {}); * /}};Copy the code

To insert the data from the SEED into the database, execute the following command:

sequelize db:seed:all
Copy the code

Create Services

Create folder services. The code in the folder encapsulates the method of interacting with the model, including all CRUD (create, read, update and delete) operations. Create administrators.

const AdministratorsModel = require(".. /models").administrators; class AdministratorsService { constructor() {} async get(username) { try { const userinfo = await AdministratorsModel.findOne({ where: { username }, }); return userinfo; } catch (error) { throw error; } } async add(newData) { try { return await AdministratorsModel.create(newData); } catch (error) { throw error; } } async del(id) { try { const isExist = await AdministratorsModel.findOne({ where: { id: Number(id) }, }); if (isExist) { const deleted = await AdministratorsModel.destroy({ where: { id: Number(id) }, }); return deleted; } return null; } catch (error) { throw error; } } async update(id, updateData) { try { const isExist = await AdministratorsModel.findOne({ where: { id: Number(id) }, }); if (isExist) { await AdministratorsModel.update(updateData, { where: { id: Number(id) }, }); return updateData; } return null; } catch (error) { throw error; } } } module.exports = new AdministratorsService();Copy the code

Create Controllers

The services file created above is used for the controller. Create a file named administrators. Js in the controller folder with the following code:

const administratorsService = require(".. /services/administrators"); const util = require(".. /utils"); class AdministratorsController { constructor() {} async login(req, res) { const { username, passowrd } = req.body; try { const userinfo = await administratorsService.get(username); console.log(userinfo); if (! Userinfo) {util.seterror (200, 30004, 'username does not exist: ${username}'); } else {util.setSuccess(200, "login successful ", userinfo); } return util.send(res); } catch (error) { util.setError(404, error); return util.send(res); } } } module.exports = new AdministratorsController();Copy the code

Create the Routers

Create the administrators. Js file in the directory routers. The code is as follows:

const Router = require("express"); const administratorController = require(".. /controllers/administrators"); const administratorsRouter = Router();"/login", administratorController.login); module.exports = administratorsRouter;Copy the code

Create the entry

Now create the interface for the service. Create the file app.js in the project root directory with the following code:

"use strict"; const administratorsRouter = require("./src/routers/administrators"); require("./src/utils/logger.js")(2); const pjson = require("./package.json"); const os = require("os"); const express = require("express"); const app = express(); const bodyParser = require("body-parser"); const CONFIG = require("./config"); const cookieParser = require("cookie-parser"); function _version(serviceUrl) { const serviceInfo = { name: os.hostname(), os: os.platform(), os_v: os.release(), version: "v" + pjson.version, };" ");" ",; console.success(" ", serviceInfo.version); console.success(" ", serviceUrl);" ");" "); } function _isAuth(req) { if (req.cookies) { return req.cookies.auth; } else { return false; } } function _setAuth(res, userinfo) { res.cookie("auth", userinfo); } function _formatResponse(code, message, data) { return Object.assign( { code: code, message: message, }, data ); } const allowDomains = "*"; All (allowDomains, (req, res, next) = {res.header(" access-control-allow-origin ", "*"); Res. The header (" origin ", ""); res.header( "Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept,application/x-www-form-urlencoded" ); res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS"); res.header("Content-Type", "application/json; charset=utf-8"); const noCheckPaths = ["/api/v1/auth/login"]; if (req.method == "OPTIONS") { res.send(200); } else { if (noCheckPaths.includes(req.path)) { next(); } else { const authInfo = _isAuth(req); if (authInfo { next(); } else { res.send(401); }}}}); app.use(cookieParser()); app.use(bodyParser.json()); app.use( bodyParser.urlencoded({ extended: true, }) ); app.use("/api/v1/auth", administratorsRouter); / / start running const port = process. The env. The port | | CONFIG. The port; _version (` running at${port} `); app.listen(port);Copy the code

Now run the node app.js command to start the service. You will see the terminal effect as follows:

At this point, a basic API login service has yet to be completed and will be perfected in subsequent iterations. The code for the article is on GitHub.

About (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.