Node js Rest Apis Country State City List From MySQL Database

Node js Rest Apis Country State City List From MySQL Database

Apis for country state city list in node js with MySQL database; in this tutorial, you will learn how to create country state city list APIs from MySQL database in node js express app.

And as well as; You can also use Country State City list API in node js MySQL with angular, Vue, react, any android app and ios app.

How to Build Country State City List REST Apis in Node js Express With MySQL Database

  • Step 1 – Create Node Express js App
  • Step 2 – Create Database and Country State City Tables
  • Step 3 – Install express ejs body-parser mysql Modules
  • Step 4 – Connect App to MySQL DB
  • Step 5 – Create Server.js File And Import Modules
  • Step 6 – Create Country State City List Routes
  • Step 7 – Start App Server
  • Step 8 – Test Country State City Apis with Postman App

Step 1 – Create Node Express js App

Execute the following command on terminal to create node js app:

mkdir my-app
cd my-app
npm init -y

Step 2 – Create Database and Country State City Tables

Create database and tables; so execute the following sql query to create database and table:

CREATE DATABASE my-node;


CREATE TABLE `countries` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `states` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `country_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cities` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `state_id` int(11) NOT NULL,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `countries` VALUES (1, 'USA', 1);
INSERT INTO `countries` VALUES (2, 'Canada', 1);


INSERT INTO `states` VALUES (1, 1, 'New York', 1);
INSERT INTO `states` VALUES (2, 1, 'Los Angeles', 1);
INSERT INTO `states` VALUES (3, 2, 'British Columbia', 1);
INSERT INTO `states` VALUES (4, 2, 'Torentu', 1);


INSERT INTO `cities` VALUES (1, 2, 'Los Angales', 1);
INSERT INTO `cities` VALUES (2, 1, 'New York', 1);
INSERT INTO `cities` VALUES (3, 4, 'Toranto', 1);
INSERT INTO `cities` VALUES (4, 3, 'Vancovour', 1);

Step 3 – Install express ejs body-parser mysql Modules

Execute the following command on the terminal to install express ejs body-parser MySQL modules:

npm install express ejs mysql body-parser --save

body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and make it available under req.body property. In other words, it simplifies the incoming request.

Express-EJS– EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages.

Mysql an open-source relational database management system (RDBMS).

Step 4 – Connect App to MySQL DB

Create database.js file into your app root directory and add the following code into it to connect your app to the mongodb database:

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost', // Replace with your host name
  user: 'root',      // Replace with your database username
  password: '',      // Replace with your database password
  database: 'my-node' // // Replace with your database Name
}); 

conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

Step 5 – Create Server.js File And Import Modules

Create server.js file; so visit your app root directory and create server.js file; Then import above installed modules into it:

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var bodyParser = require('body-parser');
var db = require('./database');

var app = express();

Step 6 – Create Country State City List Routes

Create country state city route for apis; so visit routes open server.js file; Then add the following routes into it:

var createError = require('http-errors');
var http = require('http');
var express = require('express');
var path = require('path');
var bodyParser = require('body-parser');
var db = require('./database');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, '/'));
app.set('view engine', 'ejs');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: false
}));

app.get('/countries-list', function(req, res) {
    db.query('SELECT * FROM countries ORDER BY id desc', function(err, rows) {

        if (err) {
            res.json({
                msg: 'error'
            });
        } else {
            res.json({
                msg: 'success',
                countries: rows
            });
        }
    });
});

app.post('/get-states-by-country', function(req, res) {


    db.query('SELECT * FROM states WHERE country_id = "' + req.body.country_id + '"',
        function(err, rows, fields) {

            if (err) {
                res.json({
                    msg: 'error'
                });
            } else {
                res.json({
                    msg: 'success',
                    states: rows
                });
            }

        });
});

app.post('/get-cities-by-state', function(req, res) {


    db.query('SELECT * FROM cities WHERE state_id = "' + req.body.state_id + '"',
        function(err, rows, fields) {

            if (err) {
                res.json({
                    msg: 'error'
                });
            } else {
                res.json({
                    msg: 'success',
                    cities: rows
                });
            }

        });
});

// port must be set to 8080 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function() {
    console.log('Node app is running on port 3000');
});

module.exports = app;

Step 7 – Start App Server

You can use the following command to start node js app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/

Step 8 – Test Country State City Apis with Postman App

Open your postman app and call country state city list apis; as shown below:

Countries List Api

URL :- http://localhost:3000/countries-list
Method :- GET

States List Api

URL :- http://localhost:3000/get-states-by-country
Method :- POST
Parameters :- country_id

Cities List Api

URL :- http://localhost:3000/get-cities-by-state
Method :- POST
Parameters :- state_id

Note that, call the above apis using x www form urlencoded mehtod on postman.


The “application/x-www-form-urlencoded” is the default encoding (in HTTP terms Content-Type) a web form uses to transfer data, not multipart/form-data. To send an HTTP post request on form submission with a Content Type of multipart/form-data, one must explicitly specify this as the enctype value. 

Conclusion

Country state city list apis in node js with MySQL database; in this tutorial, you have learned how to create country state city list APIs from MySQL database in node js express app.

Recommended Node JS Tutorials

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *