Ionic App with NodeJS, Express, MySQL, Sequelize?—?Taxi App [Part 1]

In this series of tutorials, we will make a complete architecture with the Node JS &?Express -based Server with MySQL database. We will use the?Sequelize ?ORM to work with SQL queries. Initially, we will start with the setup of the SQL server and Node JS in your local machine

So lets start and make our Awesome Application.

SQL Server Installation (Windows)

In this part we will start with installing?SQL server?in your local machine, If you have that you can skip this part and start with Node JS server setup

1. Go to?https://dev.mysql.com/downloads/mysql/ ?, select your operating system and get the MySQL installer
2. Then go to the download location and install the MySQL Community Server(8.0.21 at the time of this blog)

This process will take some time, remember to create the password in MySQL which you can remember, we will need it later. And after that, we have to install?MySQL workbench?where we can display all our database tables and relations.

1. Go to?https://dev.mysql.com/downloads/workbench/ ?, choose the operating system, and download the installer.
2. Go to downloads, install the workbench and you can search workbench in your pc (if you are using windows 8 you can go to right bottom and search for workbench)

Go to MySQL workbench and you will see an interface like —

Our MySQL installation is done and we are good to go, Now we start with Node JS setup

If you like any other SQL tool other then workbench, you are free to do that as we are not dependent with Workbench.

Node Express Project?Setup

In this part we will start to code our server and integrate it with?sequelize?ORM which will help in writing complex queries in MySQL, It will make our work easier and more JS oriented.

First, For NodeJs, You can install Node from the official?Node Site — get the Stable LTS version.

To check if the node is working, you can run the following command in your terminal / CMD — this will let you know if this is working properly. We will need?npm?installer for the most part of this series

$ npm --version

$ node --version
        

Now, Go to the project directory and run?

$ npm init
        

This will create a?package.json?file in your project, Which will help us to maintain our dependencies.

Now we have to add?index.js?(main script of Node JS project) and also add this code to?package.json?like this —

"scripts": {
  "start": "node index"
}
        

Now we will install some of the dependencies that will help in construct our API server easily.

$ npm install express cors morgan sequelize cookie-parser mysql2

$ npm install sequelize-cli
        

express — Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications.

cors — Cross-origin resource sharing (CORS) is a mechanism that allows restricted resources on a web page to be requested from another domain outside the domain from which the first resource was served

morgan — HTTP request logger middleware for node.js, Named after Dexter, a show you should not watch until completion.

cookie-parser — Parse?Cookie?header and populate?req.cookies?with an object keyed by the cookie names.

sequelize — Sequelize is a promise-based ORM for Node.js and io.js. It supports the dialects PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL and features solid transaction support, relations, read replication, and more.

mysql2 — MySQL client for Node.js with a focus on performance. Supports prepared statements, non-utf8 encodings, binary log protocol, compression, SSL. This is mandatory for you to install if you are using MySQL you can change this connector if you are using other SQL versions.?Read here

Sequlize CLI?Setup

In this integration we will use the?sequelize-cli?,?this is different from sequelize NPM module. This is a command-line tool to help you setup Database changes without actually changing the database from Native SQL commands.?

You can install the CLI using one of many commands, one of the methods is using NPX

$ npx sequelize-cli init
        

This will add these folders in your project structure —

• config, contains a configuration file, which tells CLI how to connect with database
• models, contains all models for your project (e.g. User Model)
• migrations, contains all migration files ( maintaining DB change history)
• seeders, contains all seed files ( if you need some seed data to be fed to the system)

Now we have to configure and create a new Database in our MySQL server, For that, we have to add our credentials to the?config/config.json

{	  
   "development": {
	 "username": "root", <--------
	 "password": null, <--------
	  "database": "database_development", <-------
	  "host": "127.0.0.1", <---------
	  "dialect": "mysql"
   },
   "test": {
	 "username": "root",
	 "password": null,
	 "database": "database_test",
	 "host": "127.0.0.1",
	 "dialect": "mysql"
   },
   "production": {
	 "username": "root",
	 "password": null,
	 "database": "database_production",
	 "host": "127.0.0.1",
	 "dialect": "mysql"
   }
}
        

In the above file add your credentials that you have used while setting up the MySQL server in your local machine. And you can change the database name is required. There are 3 environment — we will use development first in our case

Initialization of?Database

As we are using the?sequelize-cli, it does the database connection part automatically and we are ready to use the exported DB object in other places. If you are using your own connection code for Sequelize — you can check it?here . We will make more use of this exported sequelize object in the next parts of our series.

Now its time you can add the models in your database using the?sequelize-cli

$ npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string
        

1. — name?flag is used to give the name to the Model (Table)
2. — attributes?are used to define Table fields

It adds the user.js file in?models?folder and adds a migration file in?migrations?folder. Basically, migrations are meant to keep track of changes to the database. With migrations you can transfer your existing database into another state and vice versa: Those state transitions are saved in migration files, which describe how to get to the new state and how to revert the changes in order to get back to the old state.

So whenever you change any model — you will need to make changes in the Model file, Migration file (both are done automatically by CLI — if you use CLI commands). For Migrating these changed you have to run the below commands. These commands may differ in the case of other OS where you can directly call?sequelize?commands instead of?node_moules?path

node_modules\.bin\sequelize db:create

node_modules\.bin\sequelize db:migrate
        

If you are using Mac OS/ Linux — then you can just replace \ with /, After this, we can check out our MySQL workbench

You can see here that a new table has been created, That means we have successfully integrated Node JS and MySQL

Conclusion?

So in this part of the series, we have learned how to setup MySQL server, integrating Node- Express with the?sequelize ORM, And how to make some models using?sequelize-cli?and?migrate?the changes to MySQL Tables.

Now in the next part, we will go through the?API endpoints?in Express JS and how to perform MySQL queries using?sequelize.?Later we will use this system with our Ionic App.