DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’» is a community of 963,673 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Create account Log in
Cover image for Node.js CRUD Operation with MySQL example
Tien Nguyen
Tien Nguyen

Posted on • Updated on

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:

    Top comments (1)

    Collapse
     
    fransafu profile image
    Francisco Javier SΓ‘nchez Fuentes

    Great example! Do you plan to write about "best practice in REST API" or "Soft delete" With Node.js? Or maybe complement the post with that? What do you think about it?

    This post blew up on DEV in 2020:

    js visualized

    πŸš€βš™οΈ JavaScript Visualized: the JavaScript Engine

    As JavaScript devs, we usually don't have to deal with compilers ourselves. However, it's definitely good to know the basics of the JavaScript engine and see how it handles our human-friendly JS code, and turns it into something machines understand! πŸ₯³

    Happy coding!