Simple REST API crud using Express JS with MySQL

I want to share a simple API CRUD using Express Js & Mysql. we use MVC format to create crud. firstly we install node and then install express js & express-generator both are installed globally & install some more packages so follow steps.

Step 1. Install express in the system as Globally

Firstly, you have to install the express framework globally to create a web application using Node terminal. Use the following command to install express framework globally

npm install -g express --save

Step 2. Install express-generator in the system as Globally

Use the application generator tool, express-generator to quickly create an application skeleton.

npm install -g express-generator

Step 3. Make a directory

Start your terminal and locate path where you want to install your project example cd /var/www/html and run the command to create a directory the command is below

mkdir express-rest-api-crudcd express-rest-api-crud

Step 3. Install express

express

Step 4. Install node module dependency & project run

npm install/*run this command for debugging*/
DEBUG=express-rest-api-crud:* npm startnpm start

Step 5. Install more packages

/*nodemoon auto reload when any changes in file*/
npm install nodemon --save/*install mysql*/
npm install mysql/*add migrations for create tables in database using command*/
npm install mysql-migrationsnpm start

Step 6. Setup nodemon

nodemon is a tool that helps develop node.js based applications by automatically restarting the node application when file changes in the directory are detected. nodemon docs link

Open your code editors like sublime, VS Code and etc. and open your project in the code editor & select package.json file and change scripts “start”: “node ./bin/www” to “start”: “nodemon ./bin/www” example here...

Step 6. Create an app directory and in-app create more directory like here

Step 7. Connect to the database and create a migrations

for connecting with Mysql and create migrations we add a file in the root directory of the project & file name is migration.js. In this file we connect to Mysql database and add some functions for query executions.

Step 8. Adding Migrations

Run node migration.js add migration create_table_users. Now open the migrations folder. Locate the newest file with greatest timestamp as it predecessor. The file will have the name which was specified in the command such as 1566208914372_create_table_users.js

In this image, we can see here a 1566208914372_create_table_users.js file in the Up & down Write the query in up key of the json created for the forward migration. As a part of good practice, also write the script to rollback the migration in down key. Ex.

Add migration of user and write a SQL query of the users table

There are few ways to run migrations.
Run node migration.js up. Runs all the pending up migrations.
Run node migration.js up 2. Runs 2 pending up migrations from the last position.
Run node migration.js down. Runs only 1 down migrations.
Run node migration.js refresh. Runs all down migrations followed by all up.