Jack Downson

Jack Downson


RESTful API with Node.js and Mysql

APIs are the way applications communicate with each other. And APIs are the way to make versatile applications.

One API and multiple frontends or multiple micro APIs and multiple front ends, there is no doubt that API development is the core of scalable and manageable applications.

In this post, we will talk about creating a REST API on Node.js with Express and MySQL.

First things first; important prerequisites for this article to follow up are:

First let’s start with project initialization i.e. git, npm, etc.

npm i -S express

HTTP Server

Now we need to add express and related dependencies to get up and running with the HTTP server.

npm i -S express body-parser

Express is a framework for API development on Node.js; similar tools in this area are:

Now we set up the basic express app server with following server.js file:

const express = require('express')
const app = express()
const port = 3000

app.get('/', (req, res) => res.send('Hello World!'))

app.listen(port, () => console.log(`Example app listening on port ${port}!`));

As you can see, like with the function call app.get

  • We are defining a Route on our server
  • The server will respond to GET calls for this Route
  • The Callback will handle it in the following way: (req, res) => res.send('Hello World!'); And for this route, the response will be Hello World String.

And if we write the same line as follows:

app.post('/', (req, res) => res.send('Hello POST World!'))

This is the same as GET; except with the .post method, the server with .post method will respond to POST requests on these Routes with string Hello POST World

You can send the same response to all HTTP methods with the .all method as:

app.all('/ping', (req, res) => res.send(new Date()))

Database Access & CRUD

As we are using MySQL Database here, we need to set up the database access for Node.js.

For that, we will need mysql package for Node.js. Let’s go ahead and install the package as the main dependency

npm i -S mysql

After installing mysql, we can write our basic code to connect to the database and then get some data from a table. It can be written as follows:

const mysql = require('mysql');

// Get the Host from Environment or use default
const host = process.env.DB_HOST || 'localhost';

// Get the User for DB from Environment or use default
const user = process.env.DB_USER || 'root';

// Get the Password for DB from Environment or use default
const password = process.env.DB_PASS || '';

// Get the Database from Environment or use default
const database = process.env.DB_DATABASE || 'twitter_clone';

// Create the connection with required details
const con = mysql.createConnection({
  host, user, password, database,

const query = "SELECT * FROM tweets";

// make to connection to the database.
con.connect(function(err) {
  if (err) throw err;

  // if connection is successful
  con.query(query, (err, result, fields) => {
    // if any error while executing above query, throw error
    if (err) throw err;

    // if there is no error, you have the result

Here we are making a connection to the database with mysql.createConnection function and then using the connection reference to fire a Select query to the Database with connection.query function.

In both functions, we are using callbacks with first error parameter which will tell us if any error occurred while executing the provided command. We can convert the above code to async/await or Promise style with the help of the following functions:

Connect as a Promise

// connect
const mysql = require('mysql');

module.exports = async (params) => new Promise(
(resolve, reject) => {
    const connection = mysql.createConnection(params);
  connection.connect(error => {
      if (error) {

Query as a Promise

// query
module.exports = async (conn, q, params) => new Promise(
(resolve, reject) => {
  const handler = (error, result) => {
      if (error) {
  conn.query(q, params, handler);

Now we merge the basic express app, promise based mysql connection & query; the code to connect and the query should look like this:

const express = require('express')

const dbConfig = require('./dbConfig');
// ↑ exports = {user, password, host, databse}

const connection = require('./helpers/connection');
const query = require('./helpers/query');

const app = express()
const port = 3000;

app.get('/', (req, res) => res.send('Hello World!'))

app.get('/list', async (req, res) => {
  const conn = await connection(dbConfig).catch(e => {}) 
  const results = await query(conn, 'SELECT * FROM tweets').catch(console.log);
  res.json({ results });

app.listen(port, () => console.log(`Example app listening on port ${port}!`))

The above code will be following the following DB schema:

    username varchar(15) NOT NULL,
    password varchar(32) NOT NULL,
    followers int DEFAULT 0,
    following int DEFAULT 0,
    tweets int DEFAULT 0,
    PRIMARY KEY (id)

CREATE TABLE following(
    PRIMARY KEY (id)

    username varchar(15) NOT NULL,
    tweet varchar(140) NOT NULL,
    PRIMARY KEY (id)
# Some Random Data
INSERT INTO USERS(username, password) VALUE('pankaj', MD5('pankaj'));

INSERT INTO TWEETS(username, user_id, tweet) VALUE('pankaj', 1, 'Hello World!');

INSERT INTO TWEETS(username, user_id, tweet) VALUE('pankaj', 1, 'Hello World Again!');

Till now we have seen the basic arrangement of MySQL Functions. Let’s take a brief look at how we can arrange our Express app efficiently.


Middleware Functions are the functions that transform the server app functionalities in terms of extending the request and/or response and passing them on to the next function or middleware function in the chain.

  → Middleware-1
  → next()
  → Middleware-2
  → Route Handler 1
  → Route handler 2
  → Send Response
  → End

By calling next() inside a middleware function; the processing is handed over to the next function or middleware function

Middleware functions can perform the following tasks:

  • Execute any code.
  • Make changes to the request and the response objects.
  • End the request-response cycle.
  • Call the next middleware function in the stack.

What does a middleware function look like? As following:

+ app.use(function (req, res, next) {
+  console.log('Time:', Date.now())
+  next()
+ })

The above middleware will log the time on each request received

Or you can run it on a very specific route (for example /users ) in the following way:

- app.use(function (req, res, next) {
+ app.use('/users', function (req, res, next) {
    console.log('Time:', Date.now());

Or multiple middleware functions on the GET call of some route like /users/pankaj

- app.use('/users', function (req, res, next) {
+ app.get('/user/:username', function (req, res, next) {
    console.log('Time:', Date.now());
+ }, function (req, res, next) {
+   // send an entry to system log
+   next();

Some common middlewares for an Express app are:

  • Body Parser - Parses the request body and transforms it into a JavaScript Object for easy operation
  • Logger - Logs the requests to console or any other specific log
  • CORS - Enables CORS for requests; it depends on how the middleware is used.

Now how do we use middleware to make a modular application?

We will be doing so by separating functionalities with respect to route and then attaching them as middleware function on some route base.

  • We will create an instance of router from express
  • Then we will add basic CRUD routes to the router instance
  • Then we will add this route instance to another route-base in the main Express app

Let’s create one basic route based middleware for Tweets:

// file-name: app-middlewares/tweets.js
const express = require('express');
const router = express.Router();

router.get('/:id', (req, res) => {
  const { id } = req.params;
  res.send({ id });

router.get('/feed', (req, res) => {
  res.send('Here get the feed');

module.exports = router;

Here we created two routes:

/feed → for the tweets feed
/:id → for the tweet of matching id

As you notice, we did not put anything related to the base route except for how it should behave on the mount point.

For now, these routes are not accessible as we have not mounted them in our app.

Not let’s mount the route middleware on to the route /tweets and see how it behaves in the app:

  const express = require('express')

+ const appRouter = require('./app-middlewares/tweets');
  const app = express();
  const port = 3000;

+ app.use('/tweets', appRouter);
  app.listen(port, () => {
    console.log(`Example app listening on port ${port}!`)

After this when you restart your node server, you will see that /feed is available as a child of /tweets making the final route into /tweets/feed Similarly, for /:id, it will become /tweets/:id.

Till now we understood the following:

  • How to create basic Express server
  • How to use MySQL in Express
  • What is Middleware
  • How to arrange our application with middleware functions

Now let’s create resolver functions to provide data to the feed and the tweet endpoint.

Here we are trying to keep the data-layer and controller separate. This way any future changes in controller or data-layer are very minimally affecting each other.


For the feed, we need to provide items in a paginated fashion. But before, we need to think about what kind of pagination we should go for. That means we will have two ways to choose from for the pagination: -Limit and Offset Based -Pivot Based

Limit and Offset based pagination

Limit and Offset based pagination is good for systems where the paginated data is not realtime or frequently changing. If we do so, we will run into problems of duplicated entries in the feed.

Pivot based pagination

In a Pivot based pagination system, the items before or after the pivot are requested. It is very similar to the Limit and Offset based pagination; except that the data is sorted with the pivoted information as well: generally by creation timestamp.

For a social media feed, the data is very dynamic or let’s say real-time. So here we will go for the Pivot based pagination system.

Now here in our DB design, we can go for Row ID or Timestamp for our pivot, as both are sortable entities in our DB design; this might change for NoSQL based design or if we choose to use UUID or strings hash for our IDs.

We will go ahead with ID column as our pivot, and the following will be our SQL query to get a feed with 50 items:

// query-builders/feed-query.js
module.exports = (user, pivot) => `
  SELECT username, tweet, timestamp
  FROM tweets WHERE ( user_id = ${user}
   OR ( user_id IN (
    SELECT user2_id FROM following WHERE user1_id = ${user}
   ) )
  ) ${pivot ? `AND id < ${pivot}` : ''}
  LIMIT 0, 50`;

Here special to notice that, if this is the first load, we don’t need pivot; so we will send the first 50 from the Tweets table sorted in descending order by ID.

Ad when we will pass the ID pivot, it will give us the 50 records from the specified ID, omitting that specific ID.

So using the above query; our /feed endpoint looks like the following:

const connection = require('../helpers/connection');
const query = require('../helpers/query');
const feedQuery = require('../query-builders/feed-query');
router.get('/feed', async (req, res) => {
  const user = 1;
  const pivot = null;
  const conn = await connection(dbConfig).catch(e => {});
  const feed = await query(conn, feedQuery(user, pivot))

And our rote for specific tweet itself:

const connection = require('../helpers/connection');
const query = require('../helpers/query');
router.get('/:id', async (req, res) => {
  const { id } = req.params;
  const conn = await connection(dbConfig).catch(e => {});
  const tweet = await query(conn, `SELECT * FROM TWEETS
    WHERE id = ?`, [id])

With the above things in place we need a few more endpoints like the following:

GET /user => logged in user
GET /user/followers => followers of the user
GET /user/following => all the friends user is following
GET, PUT /user/settings => to update things like password,name etc
POST /user/follow -> to follow some friend


POST /auth/register -> register as new user
POST /auth/login -> login for users
GET /auth/confirm -> confirm the email for user
GET, POST /auth/forgot -> forgot password

For authentication, we will use third party authentication scripts such as Firebase or Auth0

The following code will take care of auth and set the session or JWT for logged in user.

This logged in user will be the basic initial information for all the other routes like feed or user-related info

Let’s implement the user-related routes considering that we have information about currently logged-in user.

User Profile

// GET
// /user
router.get('/', async (req, res) => {
  const user = 1;
  const conn = await connection(dbConfig).catch(e => {});
  const currentUser = await query(conn, `SELECT * FROM USERS
    WHERE id = ?`, [user])

User’s Followers

// GET
// /user/followers
router.get('/followers', async (req, res) => {
  const user = 1;
  const conn = await connection(dbConfig).catch(e => {});
  const followers = await query(conn, `SELECT
    USER_INFO.*, username as user1_username
    user1_id, user2_id, username as user2_username
    FROM FOLLOWING LEFT JOIN USERS ON user2_id = users.id
    WHERE user1_id = ?) as USER_INFO
    LEFT JOIN USERS ON user1_id = users.id`, [user])

User’s friends whom User is Following

// GET
// /user/following
router.get('/following', async (req, res) => {
  const user = 1;
  const conn = await connection(dbConfig).catch(e => {});
  const followers = await query(conn, `SELECT
    USER_INFO.*, username as user1_username
    user1_id, user2_id, username as user2_username
    FROM FOLLOWING LEFT JOIN USERS ON user2_id = users.id
    WHERE user2_id = ?) as USER_INFO
    LEFT JOIN USERS ON user1_id = users.id`, [user])

Following a new Friend by User

// /user/follow
router.post('/following', async (req, res) => {
  const user = 1;
  const { id } = req.params;
  const conn = await connection(dbConfig).catch(e => {});
  const follow = await query(conn, `INSERT INTO FOLLOWING
    (user1_id, user2_id)
    VALUE (?, ?)`, [user, id])

User’s settings

// /user/settings 
router.get('/settings', async (req, res) => {
  const user = 1;
  const conn = await connection(dbConfig).catch(e => {});
  const settings = await query(conn, `SELECT * FROM SETTINGS WHERE user_id = ?`, [user])
router.put('/settings', async (req, res) => {
  const user = 1;
  const vals = req.body;
  const values = Object.keys(vals).map(k => `${k}=${vals[k]}`);
  const conn = await connection(dbConfig).catch(e => {});
  const status = await query(conn, `UPDATE SETTINGS
        SET ? WHERE user_id = ?`, [values, user])

Attaching User Routes to App

As we created all above routes middleware for a user; let’s mount this middleware on the /user route base:

- const appRouter = require('./app-middlewares/tweets');
+ const tweetsRouter = require('./app-middlewares/tweets');
+ const userRouter = require('./app-middlewares/user');
- app.use('/tweets', appRouter);
+ app.use('/tweets', tweetsRouter);
+ app.use('/user', userRouter);

And this how we have created:

  • REST(ish) API with Node.js and Express
  • Twitter-like application API with Node.js, Express, and MySQL

You can download following postman collection to browse through the API

Run in Postman

Download Collection

View/Download the above code from this repo: Github: express-mysql


So in this article, we saw how to create APIs with Node.js, Express, and MySQL and how to develop the application in a maintainable and modular way.

Let me know what do you think about this article through comments 💬 or on Twitter at @patel_pankaj_ and @time2hack

If you find this article helpful, please share it with others 🗣; subscribe to the blog for new posts and see you the next time.

#nodejs #mysql #programming #javascript

What is GEEK

Buddha Community

RESTful API with Node.js and Mysql
Wilford  Pagac

Wilford Pagac


What is REST API? An Overview | Liquid Web

What is REST?

The REST acronym is defined as a “REpresentational State Transfer” and is designed to take advantage of existing HTTP protocols when used for Web APIs. It is very flexible in that it is not tied to resources or methods and has the ability to handle different calls and data formats. Because REST API is not constrained to an XML format like SOAP, it can return multiple other formats depending on what is needed. If a service adheres to this style, it is considered a “RESTful” application. REST allows components to access and manage functions within another application.

REST was initially defined in a dissertation by Roy Fielding’s twenty years ago. He proposed these standards as an alternative to SOAP (The Simple Object Access Protocol is a simple standard for accessing objects and exchanging structured messages within a distributed computing environment). REST (or RESTful) defines the general rules used to regulate the interactions between web apps utilizing the HTTP protocol for CRUD (create, retrieve, update, delete) operations.

What is an API?

An API (or Application Programming Interface) provides a method of interaction between two systems.

What is a RESTful API?

A RESTful API (or application program interface) uses HTTP requests to GET, PUT, POST, and DELETE data following the REST standards. This allows two pieces of software to communicate with each other. In essence, REST API is a set of remote calls using standard methods to return data in a specific format.

The systems that interact in this manner can be very different. Each app may use a unique programming language, operating system, database, etc. So, how do we create a system that can easily communicate and understand other apps?? This is where the Rest API is used as an interaction system.

When using a RESTful API, we should determine in advance what resources we want to expose to the outside world. Typically, the RESTful API service is implemented, keeping the following ideas in mind:

  • Format: There should be no restrictions on the data exchange format
  • Implementation: REST is based entirely on HTTP
  • Service Definition: Because REST is very flexible, API can be modified to ensure the application understands the request/response format.
  • The RESTful API focuses on resources and how efficiently you perform operations with it using HTTP.

The features of the REST API design style state:

  • Each entity must have a unique identifier.
  • Standard methods should be used to read and modify data.
  • It should provide support for different types of resources.
  • The interactions should be stateless.

For REST to fit this model, we must adhere to the following rules:

  • Client-Server Architecture: The interface is separate from the server-side data repository. This affords flexibility and the development of components independently of each other.
  • Detachment: The client connections are not stored on the server between requests.
  • Cacheability: It must be explicitly stated whether the client can store responses.
  • Multi-level: The API should work whether it interacts directly with a server or through an additional layer, like a load balancer.

#tutorials #api #application #application programming interface #crud #http #json #programming #protocols #representational state transfer #rest #rest api #rest api graphql #rest api json #rest api xml #restful #soap #xml #yaml

An API-First Approach For Designing Restful APIs | Hacker Noon

I’ve been working with Restful APIs for some time now and one thing that I love to do is to talk about APIs.

So, today I will show you how to build an API using the API-First approach and Design First with OpenAPI Specification.

First thing first, if you don’t know what’s an API-First approach means, it would be nice you stop reading this and check the blog post that I wrote to the Farfetchs blog where I explain everything that you need to know to start an API using API-First.

Preparing the ground

Before you get your hands dirty, let’s prepare the ground and understand the use case that will be developed.


If you desire to reproduce the examples that will be shown here, you will need some of those items below.

  • NodeJS
  • OpenAPI Specification
  • Text Editor (I’ll use VSCode)
  • Command Line

Use Case

To keep easy to understand, let’s use the Todo List App, it is a very common concept beyond the software development community.

#api #rest-api #openai #api-first-development #api-design #apis #restful-apis #restful-api

Hire Dedicated Node.js Developers - Hire Node.js Developers

If you look at the backend technology used by today’s most popular apps there is one thing you would find common among them and that is the use of NodeJS Framework. Yes, the NodeJS framework is that effective and successful.

If you wish to have a strong backend for efficient app performance then have NodeJS at the backend.

WebClues Infotech offers different levels of experienced and expert professionals for your app development needs. So hire a dedicated NodeJS developer from WebClues Infotech with your experience requirement and expertise.

So what are you waiting for? Get your app developed with strong performance parameters from WebClues Infotech

For inquiry click here: https://www.webcluesinfotech.com/hire-nodejs-developer/

Book Free Interview: https://bit.ly/3dDShFg

#hire dedicated node.js developers #hire node.js developers #hire top dedicated node.js developers #hire node.js developers in usa & india #hire node js development company #hire the best node.js developers & programmers

Aria Barnes

Aria Barnes


Why use Node.js for Web Development? Benefits and Examples of Apps

Front-end web development has been overwhelmed by JavaScript highlights for quite a long time. Google, Facebook, Wikipedia, and most of all online pages use JS for customer side activities. As of late, it additionally made a shift to cross-platform mobile development as a main technology in React Native, Nativescript, Apache Cordova, and other crossover devices. 

Throughout the most recent couple of years, Node.js moved to backend development as well. Designers need to utilize a similar tech stack for the whole web project without learning another language for server-side development. Node.js is a device that adjusts JS usefulness and syntax to the backend. 

What is Node.js? 

Node.js isn’t a language, or library, or system. It’s a runtime situation: commonly JavaScript needs a program to work, however Node.js makes appropriate settings for JS to run outside of the program. It’s based on a JavaScript V8 motor that can run in Chrome, different programs, or independently. 

The extent of V8 is to change JS program situated code into machine code — so JS turns into a broadly useful language and can be perceived by servers. This is one of the advantages of utilizing Node.js in web application development: it expands the usefulness of JavaScript, permitting designers to coordinate the language with APIs, different languages, and outside libraries.

What Are the Advantages of Node.js Web Application Development? 

Of late, organizations have been effectively changing from their backend tech stacks to Node.js. LinkedIn picked Node.js over Ruby on Rails since it took care of expanding responsibility better and decreased the quantity of servers by multiple times. PayPal and Netflix did something comparative, just they had a goal to change their design to microservices. We should investigate the motivations to pick Node.JS for web application development and when we are planning to hire node js developers. 

Amazing Tech Stack for Web Development 

The principal thing that makes Node.js a go-to environment for web development is its JavaScript legacy. It’s the most well known language right now with a great many free devices and a functioning local area. Node.js, because of its association with JS, immediately rose in ubiquity — presently it has in excess of 368 million downloads and a great many free tools in the bundle module. 

Alongside prevalence, Node.js additionally acquired the fundamental JS benefits: 

  • quick execution and information preparing; 
  • exceptionally reusable code; 
  • the code is not difficult to learn, compose, read, and keep up; 
  • tremendous asset library, a huge number of free aides, and a functioning local area. 

In addition, it’s a piece of a well known MEAN tech stack (the blend of MongoDB, Express.js, Angular, and Node.js — four tools that handle all vital parts of web application development). 

Designers Can Utilize JavaScript for the Whole Undertaking 

This is perhaps the most clear advantage of Node.js web application development. JavaScript is an unquestionable requirement for web development. Regardless of whether you construct a multi-page or single-page application, you need to know JS well. On the off chance that you are now OK with JavaScript, learning Node.js won’t be an issue. Grammar, fundamental usefulness, primary standards — every one of these things are comparable. 

In the event that you have JS designers in your group, it will be simpler for them to learn JS-based Node than a totally new dialect. What’s more, the front-end and back-end codebase will be basically the same, simple to peruse, and keep up — in light of the fact that they are both JS-based. 

A Quick Environment for Microservice Development 

There’s another motivation behind why Node.js got famous so rapidly. The environment suits well the idea of microservice development (spilling stone monument usefulness into handfuls or many more modest administrations). 

Microservices need to speak with one another rapidly — and Node.js is probably the quickest device in information handling. Among the fundamental Node.js benefits for programming development are its non-obstructing algorithms.

Node.js measures a few demands all at once without trusting that the first will be concluded. Many microservices can send messages to one another, and they will be gotten and addressed all the while. 

Versatile Web Application Development 

Node.js was worked in view of adaptability — its name really says it. The environment permits numerous hubs to run all the while and speak with one another. Here’s the reason Node.js adaptability is better than other web backend development arrangements. 

Node.js has a module that is liable for load adjusting for each running CPU center. This is one of numerous Node.js module benefits: you can run various hubs all at once, and the environment will naturally adjust the responsibility. 

Node.js permits even apportioning: you can part your application into various situations. You show various forms of the application to different clients, in light of their age, interests, area, language, and so on. This builds personalization and diminishes responsibility. Hub accomplishes this with kid measures — tasks that rapidly speak with one another and share a similar root. 

What’s more, Node’s non-hindering solicitation handling framework adds to fast, letting applications measure a great many solicitations. 

Control Stream Highlights

Numerous designers consider nonconcurrent to be one of the two impediments and benefits of Node.js web application development. In Node, at whatever point the capacity is executed, the code consequently sends a callback. As the quantity of capacities develops, so does the number of callbacks — and you end up in a circumstance known as the callback damnation. 

In any case, Node.js offers an exit plan. You can utilize systems that will plan capacities and sort through callbacks. Systems will associate comparable capacities consequently — so you can track down an essential component via search or in an envelope. At that point, there’s no compelling reason to look through callbacks.


Final Words

So, these are some of the top benefits of Nodejs in web application development. This is how Nodejs is contributing a lot to the field of web application development. 

I hope now you are totally aware of the whole process of how Nodejs is really important for your web project. If you are looking to hire a node js development company in India then I would suggest that you take a little consultancy too whenever you call. 

Good Luck!

Original Source

#node.js development company in india #node js development company #hire node js developers #hire node.js developers in india #node.js development services #node.js development

Node JS Development Company| Node JS Web Developers-SISGAIN

Top organizations and start-ups hire Node.js developers from SISGAIN for their strategic software development projects in Illinois, USA. On the off chance that you are searching for a first rate innovation to assemble a constant Node.js web application development or a module, Node.js applications are the most appropriate alternative to pick. As Leading Node.js development company, we leverage our profound information on its segments and convey solutions that bring noteworthy business results. For more information email us at hello@sisgain.com

#node.js development services #hire node.js developers #node.js web application development #node.js development company #node js application