Node Js Database With Multiple Apps Reading and Writing

Editor's note: This tutorial was last updated 1 February 2022 to replace tools that had get outdated.

Generally, Node.js is coupled with MongoDB and other NoSQL databases, merely Node.js performs well with relational databases like MySQL, too. If you want to write a new microservice with Node.js for an existing database, it's highly probable that you'll use MySQL, one of the world'south virtually pop open-source databases.

In this tutorial, nosotros'll larn how to build a REST API using MySQL as our database and Node.js as our linguistic communication. We'll as well utilise the Express.js framework to make our task easier. Our instance REST API will rails the most popular programming languages.

Prerequisites

To follow along with this article, y'all should accept the following:

• Agreement of how MySQL and relational databases work in full general
• Basic knowledge of Node.js and Express.js
• Understanding of what REST (representational country transfer) APIs are and how they function
• Knowledge of what Crud (create, read, update, delete) is and how it relates to the HTTP methods Get, Post, PUT, and DELETE

The code in this tutorial is performed on a Mac with Node 14 LTS installed. If you want, you tin can try to utilize Node.js, Docker, and Docker Etch to improve developer feel. You lot tin can also admission the full code at the GitHub repository. Let's get started!

Table of contents

• What is MySQL?
  • Annals on Db4free.net
  • Create the programming languages tabular array
  • Add demo rows for programming languages
• Setting upward Limited.js for our REST API
• REST API projection structure
• GET popular programming languages
• POST a new programming language
• PUT to update an existing programming language
• DELETE a programming language
• Testing our APIs
• Further considerations
• Conclusion

What is MySQL?

MySQL is one of the about popular databases in the earth, if not the about popular. Per the 2020 Stack Overflow survey, MySQL was the nearly-loved database, with more 55 percent of respondents using it. The community edition is freely available, supported by a large and active community.

MySQL is a feature-packed relational database offset released in 1995. MySQL runs on all major operating systems like, Linux, Windows, and macOS. Because of its features and its cost-effectiveness, MySQL is used by big enterprises and new startups alike.

For our example REST API, nosotros'll use a gratuitous MySQL service instead of setting up a local MySQL server. To host our testing MySQL database, we'll use db4free.net.

Annals on db4free.net

To get your free MySQL viii.0 database up and running, you lot can register on db4free.net. Showtime, get to the db4free signup folio, then fill out the required details past choosing your database name and username:

Click on Signup and you should receive a confirmation email. Confirm your account by clicking on the link in the email. Next, on the sidebar, click on phpMyAdmin. In the phpMyAdmin login, enter the username and password y'all chose and click Become:

Create the programming languages table

At present, we have an empty database. Let'south add the programming_languages tabular array. First, click on the database name on the left; for me, it was restapitest123. Then, click SQL on the superlative carte du jour, which is the 2d link after Structure, and put the following code for CREATE TABLE in the text area:

CREATE TABLE `programming_languages` (   `id`            INT(11) NOT Aught auto_increment ,   `name`          VARCHAR(255) Non Goose egg ,   `released_year` INT NOT Nothing ,   `githut_rank`   INT NULL ,   `pypl_rank`     INT NULL ,   `tiobe_rank`    INT Nothing ,   `created_at`    DATETIME Not Zippo DEFAULT CURRENT_TIMESTAMP ,   `updated_at`    DATETIME on UPDATE CURRENT_TIMESTAMP NOT Zilch DEFAULT CURRENT_TIMESTAMP ,   Principal Key (`id`),   UNIQUE `idx_name_unique` (`name`(255)) ) engine = innodb charset=utf8mb4 COLLATE utf8mb4_general_ci;        

Click the Get button, as below:

The lawmaking will come up dorsum with a green check box and a message along the lines of MySQL returned an empty result gear up (i.eastward. zippo rows).

With that, nosotros've created a table called programming_languages with 8 columns and a primary key called id, which is an internet and auto-increment. The name column is unique, and nosotros also added the released_year for the programming language. Nosotros accept three columns to input the rank of the programming language, sourced from the following resource:

• GitHut: GitHub language stats for Q4 2020
• PYPL: The PopularitY of Programming Language Index
• TIOBE index

The created_at and updated_at columns store dates to keep a track of when the rows were created and updated.

Add demo rows for programming languages

Next, we'll add sixteen popular programming languages to our programming_languages table. Click the same SQL link on the top of the page and copy and paste the lawmaking below:

INSERT INTO programming_languages(id,name,released_year,githut_rank,pypl_rank,tiobe_rank)  VALUES  (i,'JavaScript',1995,1,3,7), (ii,'Python',1991,two,1,3), (iii,'Java',1995,iii,ii,ii), (4,'TypeScript',2012,7,10,42), (five,'C#',2000,ix,4,5), (6,'PHP',1995,8,6,8), (7,'C++',1985,5,5,iv), (8,'C',1972,10,5,ane), (9,'Ruby',1995,6,15,xv), (10,'R',1993,33,vii,9), (11,'Objective-C',1984,18,8,eighteen), (12,'Swift',2015,sixteen,9,13), (13,'Kotlin',2011,15,12,xl), (14,'Get',2009,4,thirteen,fourteen), (15,'Rust',2010,14,xvi,26), (sixteen,'Scala',2004,11,17,34);        

You should receive a message that reads something like "16 rows inserted".

The data collected from our three sources is collected and added to the table in majority past the INSERT argument, creating 16 rows, one for each programming language. We'll return to this later when nosotros fetch data for the GET API endpoint.

If we click on the programming_languages table, visible on the left, nosotros'll meet the rows that nosotros just added:

Next, nosotros'll gear up Express.js for our Balance API with Node.js and MySQL.

Setting up Express.js for our Residuum API

To ready a Node.js app with an Express.js server, we'll beginning create a directory for our project to reside in:

mkdir programming-languages-api && cd programming-languages-api        

Then, we can create a package.json file with npm init -y every bit follows:

{   "name": "programming-languages-api",   "version": "i.0.0",   "description": "",   "principal": "alphabetize.js",   "scripts": {     "test": "echo \"Error: no test specified\" && exit 1"   },   "keywords": [],   "author": "",   "license": "ISC" }        

To install Express, we'll run npm i express, adding Express every bit a dependency in the parcel.json file.Adjacent, we'll create a slim server in the alphabetize.js file. Information technology will print an ok message on the main path /:

const limited = crave("limited"); const app = limited(); const port = 3000; app.employ(express.json()); app.utilise(   express.urlencoded({     extended: true,   }) ); app.get("/", (req, res) => {   res.json({ message: "ok" }); }); app.listen(port, () => {   panel.log(`Example app listening at http://localhost:${port}`); });        

There are a few important things to note in the code above. For one, nosotros'll using the built-in Express JSON parser middleware to parse JSON in the next steps. Nosotros'll also utilize the express.urlencoded() middleware to parse the URL encoded body.

If the PORT is not provided as an environment variable, our app volition run on port 3000. We tin run the server with node index.js and hit http://localhost:3000 to see {bulletin: "ok"} as the output.

Rest API project construction

We'll structure our project in the following manner to arrange our files logically in folders:

config.js will contain configuration for information like the database credentials and the rows we want to testify per page when we paginate results. helper.js is the dwelling for whatsoever helper functions, like calculating offset for pagination.

The routes/programmingLanguages.js file will human activity as the glue between the URI and the corresponding part in the services/programmingLanguages.js service. The services folder will house all our services. One of them is db.js, which we utilize to talk with the MySQL database.

Another service is programmingLanguages.js, which volition have methods like getMultiple, create, etc. to get and create the programming linguistic communication resource. Basic mapping of the URI and the related service office will expect like the lawmaking beneath:

GET /programming-languages → getMultiple() POST /programming-languages → create() PUT /programming-languages/:id → update() DELETE /programming-languages/:id → remove()        

Now, let's code our GET programming languages API with pagination.

GET pop programming languages

To create our GET programming languages API, we'll need to link our Node.js server with MySQL. To do and then, we'll use the mysql2 package from npm, which nosotros can install with the npm i mysql2 control on the project root.

Next, we'll create the config file on the root of the project with the post-obit contents:

const config = {   db: {     /* don't betrayal password or whatever sensitive info, done but for demo */     host: "db4free.cyberspace",     user: "restapitest123",     password: "restapitest123",     database: "restapitest123",   },   listPerPage: 10, }; module.exports = config;        

Consequently, we'll create the helper.js file with the code below:

role getOffset(currentPage = one, listPerPage) {   render (currentPage - 1) * [listPerPage]; }  function emptyOrRows(rows) {   if (!rows) {     return [];   }   return rows; }  module.exports = {   getOffset,   emptyOrRows }        

For the fun part, we'll add the road and link information technology to the services. First, we'll connect to the database and enable running queries on the database in the services/db.js file:

const mysql = crave('mysql2/promise'); const config = require('../config');  async function query(sql, params) {   const connection = wait mysql.createConnection(config.db);   const [results, ] = await connection.execute(sql, params);    return results; }  module.exports = {   query }        

Now, we'll write up the services/programmingLanguage.js file that acts equally the span between the road and the database:

const db = require('./db'); const helper = require('../helper'); const config = crave('../config');  async function getMultiple(folio = 1){   const offset = helper.getOffset(page, config.listPerPage);   const rows = await db.query(     `SELECT id, name, released_year, githut_rank, pypl_rank, tiobe_rank      FROM programming_languages LIMIT ${start},${config.listPerPage}`   );   const information = helper.emptyOrRows(rows);   const meta = {folio};    return {     data,     meta   } }  module.exports = {   getMultiple }        

After that, we'll create the routes file in routes/programmingLanguages.js, which looks similar the following:

const express = require('express'); const router = limited.Router(); const programmingLanguages = require('../services/programmingLanguages');  /* GET programming languages. */ router.get('/', async role(req, res, next) {   effort {     res.json(look programmingLanguages.getMultiple(req.query.page));   } take hold of (err) {     panel.error(`Error while getting programming languages `, err.message);     adjacent(err);   } });  module.exports = router;        

For the terminal piece of our Become endpoint, we need to wire up the route in the index.js file as follows:

const express = require("limited"); const app = limited(); const port = 3000; const programmingLanguagesRouter = require("./routes/programmingLanguages"); app.use(express.json()); app.utilise(   express.urlencoded({     extended: true,   }) ); app.become("/", (req, res) => {   res.json({ message: "ok" }); }); app.employ("/programming-languages", programmingLanguagesRouter); /* Error handler middleware */ app.utilize((err, req, res, next) => {   const statusCode = err.statusCode || 500;   panel.error(err.message, err.stack);   res.status(statusCode).json({ message: err.message });   return; }); app.listen(port, () => {   console.log(`Instance app listening at http://localhost:${port}`); });        

Nosotros made 2 important changes in our entrypoint index.js file. For one, nosotros added the code below:

const programmingLanguagesRouter = require('./routes/programmingLanguages');        

Secondly, we link upward the /programming-languages route to the router nosotros just created every bit follows:

app.use('/programming-languages', programmingLanguagesRouter);        

Nosotros've also added an error handler middleware to handle any errors and provide a proper condition code and bulletin.

After adding the GET endpoint, when we run our app again with node index.js and striking the browser with http://localhost:3000/programming-languages, we'll see an output like the post-obit:

Depending on the extensions you take installed on your browser, your output might look a petty dissimilar.

Note that we've already implemented pagination for our GET API, which is possible because of the getOffset role in helper.js and the style we run the SELECT query in services/programmingLanguage.js. Endeavour http://localhost:3000/programming-languages?page=2 to come across languages 11–16.

POST a new programming language

Our POST API will allow us to create a new programming language in our table.

To create a POST programming linguistic communication API in the /programming-languages endpoint, we'll add code to the service and the routes files. In the service method, nosotros'll get the name, the release year, and other ranks from the request body, then insert them into the programming_languages table.

Append the following code to the services/programmingLanguages.js file:

async function create(programmingLanguage){   const issue = await db.query(     `INSERT INTO programming_languages      (name, released_year, githut_rank, pypl_rank, tiobe_rank)      VALUES      (${programmingLanguage.name}, ${programmingLanguage.released_year}, ${programmingLanguage.githut_rank}, ${programmingLanguage.pypl_rank}, ${programmingLanguage.tiobe_rank})`   );    permit bulletin = 'Error in creating programming language';    if (result.affectedRows) {     message = 'Programming language created successfully';   }    return {message}; }        

Make sure you export the following role likewise:

module.exports = {   getMultiple,   create }        

For the role above to be attainable, we demand to add a road to link it up in the routes/programmingLanguages.js file as follows:

/* POST programming language */ router.post('/', async function(req, res, adjacent) {   effort {     res.json(await programmingLanguages.create(req.trunk));   } catch (err) {     console.fault(`Error while creating programming language`, err.message);     next(err);   } });        

PUT to update an existing programming language

To update an existing programming language, we'll use the /programming-languages/:id endpoint, where we'll get the data to update the language. To update a programming language, nosotros'll run the UPDATE query based on the data we got in the asking.

PUT is an idempotent activity, pregnant if the same call is made over and over over again, information technology volition produce the exact aforementioned results. To enable updating existing records, we'll add the following code to the programming language service:

async office update(id, programmingLanguage){   const result = expect db.query(     `UPDATE programming_languages      Fix proper noun="${programmingLanguage.proper name}", released_year=${programmingLanguage.released_year}, githut_rank=${programmingLanguage.githut_rank},      pypl_rank=${programmingLanguage.pypl_rank}, tiobe_rank=${programmingLanguage.tiobe_rank}      WHERE id=${id}`    );    let bulletin = 'Mistake in updating programming language';    if (result.affectedRows) {     message = 'Programming language updated successfully';   }    return {message}; }        

Make sure you consign this function as well as we did earlier:

module.exports = {   getMultiple,   create,   update, };        

To wire upwardly the code with the PUT endpoint, we'll add together the code below to the programming languages route file, just above module.exports = router;:

/* PUT programming linguistic communication */ router.put('/:id', async function(req, res, next) {   try {     res.json(look programmingLanguages.update(req.params.id, req.trunk));   } take hold of (err) {     console.error(`Error while updating programming language`, err.message);     next(err);   } });        

Now, we have the power to update any existing programming language. For example, we can update a language'southward proper noun if nosotros see a typo.

DELETE a programming language

Nosotros'll utilise the /programming-languages/:id path with the HTTP DELETE method to add the functionality to delete a programming language. Become ahead and run the code below:

async part remove(id){   const upshot = look db.query(     `DELETE FROM programming_languages WHERE id=${id}`   );    allow message = 'Error in deleting programming language';    if (result.affectedRows) {     message = 'Programming linguistic communication deleted successfully';   }    return {bulletin}; }        

Don't forget to export this function also. Once again, to link upwards the service with the road, nosotros'll add together the following lawmaking to the routes/programmingLanguages.js file:

/* DELETE programming linguistic communication */ router.delete('/:id', async function(req, res, adjacent) {   try {     res.json(await programmingLanguages.remove(req.params.id));   } grab (err) {     console.mistake(`Error while deleting programming language`, err.message);     side by side(err);   } });        

Testing our APIs

After yous have the Node.js Limited server running with node index.js, you can exam all the API endpoints. To create a new programming language, allow's go with Dart, run the post-obit cURLcommand. Alternately, yous can use Postman or whatsoever other HTTP client:

curl -i -X POST -H 'Accept: awarding/json' \     -H 'Content-blazon: application/json' http://localhost:3000/programming-languages \     --data '{"name":"sprint", "released_year": 2011, "githut_rank": xiii, "pypl_rank": 20, "tiobe_rank": 25}'        

The code above volition effect in the following output:

HTTP/1.1 200 OK X-Powered-Past: Express Content-Type: application/json; charset=utf-viii Content-Length: 55 ETag: Westward/"37-3mETlnRrtfrms6wlAjdgAXKq9GE" Date: Monday, 01 Feb 2021 11:20:07 GMT Connection: keep-alive  {"message":"Programming language created successfully"}        

You can remove the X-Powered-By header and add other security response headers using Express.js Helmet, which will be a great addition to improve the API's security. For at present, allow's update the GitHut rank of Dart from 13 to 12:

gyre -i -X PUT -H 'Have: awarding/json' \     -H 'Content-type: application/json' http://localhost:3000/programming-languages/17 \     --data '{"proper name":"dart", "released_year": 2011, "githut_rank": 12, "pypl_rank": 20, "tiobe_rank": 25}'        

The code above will generate an output like below:

HTTP/1.1 200 OK 10-Powered-Past: Limited Content-Type: application/json; charset=utf-eight Content-Length: 55 ETag: Westward/"37-0QPAQsRHsm23S9CNV3rPa+AFuXo" Date: Mon, 01 Feb 2021 11:40:03 GMT Connection: proceed-alive  {"bulletin":"Programming linguistic communication updated successfully"}        

To test out the DELETE API, you tin apply the following cURL to delete Sprint with ID 17:

curl -i -X DELETE -H 'Accept: awarding/json' \     -H 'Content-type: awarding/json' http://localhost:3000/programming-languages/17        

The code higher up will result in the post-obit output:

HTTP/1.ane 200 OK X-Powered-By: Limited Content-Blazon: application/json; charset=utf-8 Content-Length: 55 ETag: W/"37-aMzd+8NpWQ09igvHbNLorsXxGFo" Date: Mon, 01 Feb 2021 11:fifty:17 GMT Connection: proceed-live  {"message":"Programming language deleted successfully"}        

If yous're more used to a visual interface for testing, for instance, Postman, you can import the cURL commands into Postman.

Farther considerations

For the sake of simplicity in this tutorial, we kept our instance fairly simple. Still, if this was a real-life API, and not a demo, I'd highly recommend the following:

• Utilize a robust validation library like Joi to precisely validate the input, for case, to ensure the name of the programming linguistic communication is required and doesn't already exist in the database.
• Meliorate security by adding Helmet.js to Limited.js
• Streamline logs in a more manageable manner using a Node.js logging library like Winston
• Use Docker for the Node.js application

Conclusion

Nosotros now accept a functioning API server that uses Node.js and MySQL. In this tutorial, nosotros learned how to prepare MySQL on a costless service. Nosotros then created an Express.js server that can handle various HTTP methods in connectedness to how it translates to SQL queries.

The case Residuum API in this tutorial serves as a proficient starting point and foundation for building real-world, product-ready Residual APIs, wherein you tin can practice the additional considerations describes above. I hope y'all enjoyed this article, happy coding!

200's merely Monitor failed and slow network requests in production

Deploying a Node-based web app or website is the piece of cake function. Making sure your Node instance continues to serve resources to your app is where things get tougher. If you're interested in ensuring requests to the backend or third political party services are successful, endeavor LogRocket. https://logrocket.com/signup/

LogRocket is similar a DVR for web and mobile apps, recording literally everything that happens while a user interacts with your app. Instead of guessing why problems happen, you can aggregate and report on problematic network requests to quickly empathise the root crusade.

LogRocket instruments your app to record baseline performance timings such every bit page load fourth dimension, time to get-go byte, slow network requests, and likewise logs Redux, NgRx, and Vuex actions/state. Start monitoring for free.

campbellaunuentid.blogspot.com

Source: https://blog.logrocket.com/build-rest-api-node-express-mysql/