× logo Home HTML CSS Javascript React-App Angular.js logo
logo

SQL Server Architecture

React-Frontend, Node.js and Express-Backend


We're gonna build the application with following architecture:
Fronend:
React Client sends HTTP Requests and retrieves HTTP Responses using Axios,consume data on the components.
React Router is used for navigating to pages.

Backend:
Node.js Express exports REST APIs & interacts with SQL Database using Sequelize Object-relational mapping (ORM)

Finally, we're gonna test the Rest Apis using Postman.
This is our project structure:


We need to install necessary modules: express, sequelize, tedious
Run the command:
npm install express sequelize tedious cors

The package.json file should look like this:




Yeah, the first step is done. We're gonna work with Sequelize in the next section.

First five parameters are for SQL Server / MSSQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:
max: maximum number of connection in pool
min: minimum number of connection in pool
idle: maximum time, in milliseconds, that a connection can be idle before being released
acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error


In development, you may need to drop existing tables and re-sync database. Just use force: true as following code:


We can easily modify db.config.js to change anytime with SQL Server.


Create the Controller
Inside app/controllers folder, let's create tutorial.controller.js with these CRUD functions:
create
findAll
findOne
update
delete
deleteAll
findAllPublished




Let’s implement these functions.



We use req.query.title to get query string from the Request and consider it as condition for findAll() method.








This controller can be modified a little to return pagination response:





Conclusion

Today, we've learned how to create Node.js CRUD example with SQL Server (MSSQL) using Express Rest Apis web server. We also know way to add configuration for MSSQL database & Sequelize, create a Sequelize Model, write a controller and define routes for handling all CRUD operations.


SQL Automation Tools..... Contributed by Jama Hassan
My current Solutions Architect position has afforded me the opportunity to organize and develop the entire solutions process from start to finish. logo