TheLazy.Dev
Portfolio GitHub

Blog

webdev |javascript |node |mysql

Node.js CRUD Operation with MySQL example

tienbku

Tien Nguyen

Posted on August 11, 2021

Node.js CRUD Operation with MySQL example

This tutorial will guide you through the steps of building a simple Node.js CRUD Operation with MySQL database using Expressjs for Rest API.

Full Article: Build Node.js Rest APIs with Express & MySQL

Application overview

We will build Node.js CRUD Operation with MySQL - Rest Apis for creating, retrieving, updating & deleting Customers.

First, we start with an Express web server. Next, we add configuration for MySQL database, create Customer model, write the controller. Then we define routes for handling all CRUD operations:

Methods Urls Actions
GET /customers get all Customers
GET /customers/42 get Customer with id=42
POST /customers add new Customer
PUT /customers/42 update Customer with id=42
DELETE /customers/42 remove Customer with id=42
DELETE /customers remove all Customers

Finally, we're gonna test the Rest Apis using Postman.

Our project structure will be like:

nodejs-rest-api-express-mysql-project-structure

Test the APIs

Run our Node.js application with command: node server.js.
The console shows:

Server is running on port 3000.
Successfully connected to the database.
Enter fullscreen mode Exit fullscreen mode

Using Postman, we're gonna test all the Apis above.

  • Create a new Customer using POST /customers Api

    • nodejs-rest-api-express-mysql-test-create

    After creating some new Customers, we can check MySQL table:

    mysql> SELECT * FROM customers;
+----+--------------------+--------+--------+
| id | email              | name   | active |
+----+--------------------+--------+--------+
|  1 | bezkoder@gmail.com | zKoder |      1 |
|  2 | jack123@gmail.com  | Jack   |      0 |
|  3 | drhelen@gmail.com  | Helen  |      0 |
+----+--------------------+--------+--------+
    Enter fullscreen mode Exit fullscreen mode

  • Retrieve all Customers using GET /customers Api

    • nodejs-rest-api-express-mysql-test-retrieve-all

  • Retrieve a single Customer by id using GET /customers/:customerId Api

    • nodejs-rest-api-express-mysql-test-retrieve-one

  • Update a Customer using PUT /customers/:customerId Api

    • nodejs-rest-api-express-mysql-test-update

    Check customers table after a row was updated:

    mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | bezkoder@gmail.com | zKoder   |      1 |
|  2 | jack123@gmail.com  | Jack     |      0 |
|  3 | drhelen@gmail.com  | Dr.Helen |      1 |
+----+--------------------+----------+--------+
    Enter fullscreen mode Exit fullscreen mode

  • Delete a Customer using DELETE /customers/:customerId Api

    • nodejs-rest-api-express-mysql-test-delete-one

    Customer with id=2 was removed from customers table:

    mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | bezkoder@gmail.com | zKoder   |      1 |
|  3 | drhelen@gmail.com  | Dr.Helen |      1 |
+----+--------------------+----------+--------+
    Enter fullscreen mode Exit fullscreen mode

  • Delete all Customers using DELETE /customers Api

    • nodejs-rest-api-express-mysql-test-delete-all

    Now there are no rows in customers table:

    mysql> SELECT * FROM customers;
Empty set (0.00 sec)
    Enter fullscreen mode Exit fullscreen mode

    For step by step instruction and Github source code, please visit:
    Build Node.js Rest APIs with Express & MySQL

    Further Reading

    Related Posts:

    Fullstack:

    Security: Node.js – JWT Authentication & Authorization example

    Deployment:

    Node.js & MySQL Associations:

    💖 💪 🙅 🚩
    tienbku
    Tien Nguyen

    Posted on August 11, 2021

    Join Our Newsletter. No Spam, Only the good stuff.

    Sign up to receive the latest update from our blog.

    Related

    Node.js CRUD Operation with MySQL example
    webdev Node.js CRUD Operation with MySQL example

    August 11, 2021