Node Js Mysql Todo Application Example Javascript Login Updated FREE

Node Js Mysql Todo Application Example Javascript Login

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

Mostly, Node.js is coupled with MongoDB and other NoSQL databases, but Node.js performs well with relational databases similar MySQL, too. If you lot desire to write a new microservice with Node.js for an existing database, it's highly probable that you'll use MySQL, ane of the world's near popular open-source databases.

In this tutorial, we'll learn how to build a REST API using MySQL equally our database and Node.js as our language. We'll as well use the Limited.js framework to make our task easier. Our case REST API will track the virtually popular programming languages.

Prerequisites

To follow along with this article, you should accept the following:

• Understanding of how MySQL and relational databases work in general
• Basic cognition of Node.js and Express.js
• Understanding of what REST (representational country transfer) APIs are and how they function
• Cognition of what CRUD (create, read, update, delete) is and how information technology relates to the HTTP methods Get, POST, PUT, and DELETE

The lawmaking in this tutorial is performed on a Mac with Node 14 LTS installed. If you want, you can try to utilize Node.js, Docker, and Docker Etch to better programmer experience. You lot tin also access the full code at the GitHub repository. Allow's get started!

Table of contents

• What is MySQL?
  • Register on Db4free.net
  • Create the programming languages table
  • Add together demo rows for programming languages
• Setting up Limited.js for our REST API
• REST API project structure
• Get pop 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 i of the most popular databases in the world, if not the most popular. Per the 2020 Stack Overflow survey, MySQL was the most-loved database, with more than than 55 percent of respondents using it. The customs edition is freely bachelor, supported by a large and active customs.

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

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

Annals on db4free.net

To go your costless MySQL 8.0 database up and running, you tin can register on db4free.net. First, go to the db4free signup page, then fill out the required details by choosing your database name and username:

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

Create the programming languages table

Now, we have an empty database. Let'south add the programming_languages table. First, click on the database name on the left; for me, it was restapitest123. And then, click SQL on the top menu, which is the second link after Structure, and put the following code for CREATE Table in the text surface area:

CREATE Table `programming_languages` (   `id`            INT(11) NOT Nil auto_increment ,   `name`          VARCHAR(255) NOT Aught ,   `released_year` INT NOT NULL ,   `githut_rank`   INT Nothing ,   `pypl_rank`     INT Zippo ,   `tiobe_rank`    INT Nothing ,   `created_at`    DATETIME Not NULL DEFAULT CURRENT_TIMESTAMP ,   `updated_at`    DATETIME on UPDATE CURRENT_TIMESTAMP Not Nothing DEFAULT CURRENT_TIMESTAMP ,   PRIMARY KEY (`id`),   UNIQUE `idx_name_unique` (`proper noun`(255)) ) engine = innodb charset=utf8mb4 COLLATE utf8mb4_general_ci;        

Click the Go push button, equally below:

The lawmaking volition come up back with a dark-green cheque box and a bulletin along the lines of MySQL returned an empty result set (i.e. zip rows).

With that, we've created a table called programming_languages with eight columns and a primary cardinal called id, which is an internet and car-increase. The name cavalcade is unique, and nosotros also added the released_year for the programming language. We take three columns to input the rank of the programming linguistic communication, sourced from the following resources:

• GitHut: GitHub linguistic communication 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 together 16 pop programming languages to our programming_languages table. Click the same SQL link on the top of the page and copy and paste the code below:

INSERT INTO programming_languages(id,name,released_year,githut_rank,pypl_rank,tiobe_rank)  VALUES  (1,'JavaScript',1995,1,3,vii), (ii,'Python',1991,two,ane,three), (3,'Coffee',1995,three,2,2), (iv,'TypeScript',2012,7,10,42), (5,'C#',2000,9,4,5), (vi,'PHP',1995,8,half-dozen,eight), (7,'C++',1985,5,5,four), (viii,'C',1972,10,5,i), (9,'Ruby',1995,half dozen,15,15), (10,'R',1993,33,7,nine), (xi,'Objective-C',1984,18,8,eighteen), (12,'Swift',2015,16,ix,thirteen), (13,'Kotlin',2011,fifteen,12,40), (14,'Go',2009,four,13,xiv), (fifteen,'Rust',2010,14,xvi,26), (xvi,'Scala',2004,11,17,34);        

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

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

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

Next, we'll set up Limited.js for our REST API with Node.js and MySQL.

Setting upwards Express.js for our Balance API

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

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

Then, we tin can create a packet.json file with npm init -y as follows:

{   "name": "programming-languages-api",   "version": "1.0.0",   "description": "",   "main": "index.js",   "scripts": {     "test": "repeat \"Mistake: no test specified\" && leave 1"   },   "keywords": [],   "author": "",   "license": "ISC" }        

To install Express, we'll run npm i limited, adding Limited as a dependency in the bundle.json file.Next, nosotros'll create a slim server in the index.js file. It will print an ok message on the master path /:

const express = require("express"); const app = express(); const port = 3000; app.use(express.json()); app.use(   express.urlencoded({     extended: truthful,   }) ); app.go("/", (req, res) => {   res.json({ message: "ok" }); }); app.listen(port, () => {   console.log(`Example app listening at http://localhost:${port}`); });        

There are a few of import things to notation in the lawmaking above. For ane, we'll using the built-in Limited JSON parser middleware to parse JSON in the adjacent steps. We'll also utilize the express.urlencoded() middleware to parse the URL encoded trunk.

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

Balance API project construction

We'll structure our projection in the following fashion to arrange our files logically in folders:

config.js will comprise configuration for information like the database credentials and the rows nosotros want to evidence per page when nosotros paginate results. helper.js is the home for any helper functions, similar computing outset for pagination.

The routes/programmingLanguages.js file volition human activity as the gum between the URI and the respective function in the services/programmingLanguages.js service. The services folder will firm all our services. One of them is db.js, which nosotros utilise to talk with the MySQL database.

Another service is programmingLanguages.js, which will have methods similar getMultiple, create, etc. to become and create the programming language resource. Basic mapping of the URI and the related service function will look like the code beneath:

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

Now, permit's lawmaking our Go programming languages API with pagination.

Go popular programming languages

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

Side by side, we'll create the config file on the root of the project with the following contents:

const config = {   db: {     /* don't expose password or any sensitive info, done only for demo */     host: "db4free.cyberspace",     user: "restapitest123",     countersign: "restapitest123",     database: "restapitest123",   },   listPerPage: ten, }; module.exports = config;        

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

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

For the fun part, we'll add the route and link it to the services. Starting time, we'll connect to the database and enable running queries on the database in the services/db.js file:

const mysql = require('mysql2/promise'); const config = require('../config');  async part query(sql, params) {   const connectedness = await 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 every bit the bridge between the road and the database:

const db = require('./db'); const helper = crave('../helper'); const config = require('../config');  async function getMultiple(folio = 1){   const offset = helper.getOffset(folio, config.listPerPage);   const rows = expect db.query(     `SELECT id, proper noun, released_year, githut_rank, pypl_rank, tiobe_rank      FROM programming_languages LIMIT ${beginning},${config.listPerPage}`   );   const data = helper.emptyOrRows(rows);   const meta = {folio};    render {     data,     meta   } }  module.exports = {   getMultiple }        

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

const express = crave('express'); const router = express.Router(); const programmingLanguages = crave('../services/programmingLanguages');  /* Get programming languages. */ router.go('/', async function(req, res, side by side) {   attempt {     res.json(wait programmingLanguages.getMultiple(req.query.page));   } catch (err) {     console.error(`Error while getting programming languages `, err.bulletin);     adjacent(err);   } });  module.exports = router;        

For the final piece of our Become endpoint, we need to wire upwards the route in the index.js file equally follows:

const limited = require("express"); const app = express(); const port = 3000; const programmingLanguagesRouter = require("./routes/programmingLanguages"); app.use(express.json()); app.apply(   express.urlencoded({     extended: truthful,   }) ); app.get("/", (req, res) => {   res.json({ message: "ok" }); }); app.use("/programming-languages", programmingLanguagesRouter); /* Mistake handler middleware */ app.apply((err, req, res, next) => {   const statusCode = err.statusCode || 500;   console.fault(err.message, err.stack);   res.status(statusCode).json({ bulletin: err.message });   render; }); app.listen(port, () => {   console.log(`Example app listening at http://localhost:${port}`); });        

We made two important changes in our entrypoint index.js file. For one, we added the code below:

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

Secondly, nosotros link upward the /programming-languages route to the router we simply created as follows:

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

We've too added an mistake handler middleware to handle any errors and provide a proper status code and message.

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 following:

Depending on the extensions you have installed on your browser, your output might look a little dissimilar.

Note that we've already implemented pagination for our GET API, which is possible considering of the getOffset function in helper.js and the way nosotros run the SELECT query in services/programmingLanguage.js. Try http://localhost:3000/programming-languages?page=2 to encounter languages 11–16.

POST a new programming linguistic communication

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

To create a Mail service programming linguistic communication API in the /programming-languages endpoint, we'll add code to the service and the routes files. In the service method, we'll get the proper name, the release year, and other ranks from the asking torso, then insert them into the programming_languages tabular array.

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

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

Make sure y'all export the following function as well:

module.exports = {   getMultiple,   create }        

For the role to a higher place to exist attainable, we need to add a route to link information technology upwardly in the routes/programmingLanguages.js file as follows:

/* POST programming linguistic communication */ router.mail('/', async function(req, res, next) {   effort {     res.json(await programmingLanguages.create(req.trunk));   } grab (err) {     panel.error(`Error while creating programming language`, err.message);     next(err);   } });        

PUT to update an existing programming linguistic communication

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, we'll run the UPDATE query based on the information we got in the request.

PUT is an idempotent activeness, meaning if the same telephone call is made over and over once again, it will produce the exact same results. To enable updating existing records, we'll add the post-obit code to the programming language service:

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

Make sure you export this role likewise every bit we did before:

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

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

/* PUT programming language */ router.put('/:id', async office(req, res, next) {   try {     res.json(look programmingLanguages.update(req.params.id, req.body));   } catch (err) {     console.mistake(`Mistake while updating programming linguistic communication`, err.message);     side by side(err);   } });        

At present, we have the ability to update any existing programming language. For instance, we can update a language's name if we see a typo.

DELETE a programming language

We'll use the /programming-languages/:id path with the HTTP DELETE method to add together the functionality to delete a programming language. Go alee and run the code beneath:

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

Don't forget to export this function as well. Once again, to link up the service with the route, we'll add the following code to the routes/programmingLanguages.js file:

/* DELETE programming language */ router.delete('/:id', async function(req, res, next) {   endeavor {     res.json(await programmingLanguages.remove(req.params.id));   } catch (err) {     panel.fault(`Error while deleting programming language`, err.message);     adjacent(err);   } });        

Testing our APIs

Later you accept the Node.js Express server running with node index.js, you lot can test all the API endpoints. To create a new programming language, permit's go with Dart, run the post-obit cURLcommand. Alternately, you can use Postman or any other HTTP client:

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

The code to a higher place will event in the following output:

HTTP/1.1 200 OK X-Powered-By: Express Content-Type: awarding/json; charset=utf-8 Content-Length: 55 ETag: W/"37-3mETlnRrtfrms6wlAjdgAXKq9GE" Engagement: Mon, 01 Feb 2021 11:20:07 GMT Connection: keep-alive  {"message":"Programming language created successfully"}        

Yous tin can remove the X-Powered-By header and add other security response headers using Limited.js Helmet, which volition be a great addition to improve the API'south security. For now, let's update the GitHut rank of Dart from thirteen to 12:

curlicue -i -X PUT -H 'Take: application/json' \     -H 'Content-blazon: awarding/json' http://localhost:3000/programming-languages/17 \     --data '{"name":"dart", "released_year": 2011, "githut_rank": 12, "pypl_rank": xx, "tiobe_rank": 25}'        

The code above will generate an output similar below:

HTTP/i.ane 200 OK X-Powered-By: Limited Content-Blazon: application/json; charset=utf-8 Content-Length: 55 ETag: W/"37-0QPAQsRHsm23S9CNV3rPa+AFuXo" Date: Mon, 01 Feb 2021 xi:40:03 GMT Connection: keep-alive  {"message":"Programming linguistic communication updated successfully"}        

To exam out the DELETE API, y'all can use the post-obit cURL to delete Dart with ID 17:

scroll -i -10 DELETE -H 'Accept: application/json' \     -H 'Content-type: application/json' http://localhost:3000/programming-languages/17        

The code in a higher place will effect in the following output:

HTTP/1.1 200 OK X-Powered-By: Limited Content-Blazon: awarding/json; charset=utf-viii Content-Length: 55 ETag: W/"37-aMzd+8NpWQ09igvHbNLorsXxGFo" Date: Mon, 01 February 2021 xi:50:17 GMT Connexion: keep-alive  {"message":"Programming language deleted successfully"}        

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

Further considerations

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

• Utilize a robust validation library like Joi to precisely validate the input, for example, to ensure the name of the programming language is required and doesn't already be in the database.
• Improve security by adding Helmet.js to Express.js
• Streamline logs in a more manageable way using a Node.js logging library similar Winston
• Use Docker for the Node.js application

Conclusion

We at present have a functioning API server that uses Node.js and MySQL. In this tutorial, we learned how to set up MySQL on a free service. Nosotros then created an Express.js server that can handle various HTTP methods in connection to how it translates to SQL queries.

The example Rest API in this tutorial serves every bit a good starting point and foundation for building real-world, production-ready REST APIs, wherein you can practice the additional considerations describes above. I hope you lot enjoyed this article, happy coding!

200's only Monitor failed and slow network requests in product

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

LogRocket is like a DVR for spider web and mobile apps, recording literally everything that happens while a user interacts with your app. Instead of guessing why problems happen, y'all can aggregate and written report on problematic network requests to quickly understand the root cause.

LogRocket instruments your app to record baseline operation timings such as page load time, time to beginning byte, tedious network requests, and likewise logs Redux, NgRx, and Vuex deportment/country. Starting time monitoring for free.

Node Js Mysql Todo Application Example Javascript Login

DOWNLOAD HERE

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

Posted by: mcfallschart.blogspot.com