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:

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.

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

Create a new Customer using POST /customers Api

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

mysql> SELECT * FROM customers;
+----+--------------------+--------+--------+
| id | email              | name   | active |
+----+--------------------+--------+--------+
|  1 | [e-mail] | zKoder |      1 |
|  2 | [e-mail]  | Jack   |      0 |
|  3 | [e-mail]  | Helen  |      0 |
+----+--------------------+--------+--------+

Retrieve all Customers using GET /customers Api

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

Update a Customer using PUT /customers/:customerId Api

Check customers table after a row was updated:

mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | [e-mail] | zKoder   |      1 |
|  2 | [e-mail]  | Jack     |      0 |
|  3 | [e-mail]  | Dr.Helen |      1 |
+----+--------------------+----------+--------+

Delete a Customer using DELETE /customers/:customerId Api

Customer with id=2 was removed from customers table:

mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | [e-mail] | zKoder   |      1 |
|  3 | [e-mail]  | Dr.Helen |      1 |
+----+--------------------+----------+--------+

Delete all Customers using DELETE /customers Api

Now there are no rows in customers table:

mysql> SELECT * FROM customers;
Empty set (0.00 sec)

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

Further Reading

Related Posts:

• Node.js Rest APIs example with Express & MySQL (including Sequelize)
• Node.js – JWT Authentication & Authorization example
• Node.js: Upload/Import Excel file data into MySQL Database
• Node.js: Upload CSV file data into MySQL Database

Fullstack:

• Vue.js + Node.js + Express + MySQL example
• Vue.js + Node.js + Express + MongoDB example
• Angular 8 + Node.js Express + MySQL example
• Angular 10 + Node.js Express + MySQL example
• Angular 11 + Node.js Express + MySQL example
• Angular 12 + Node.js Express + MySQL example
• React + Node.js + Express + MySQL example

Security: Node.js – JWT Authentication & Authorization example

Deployment:

• Deploying/Hosting Node.js app on Heroku with MySQL database
• Dockerize Node.js Express and MySQL example – Docker Compose

Node.js & MySQL Associations:

• One-to-Many Relationship example
• Many-to-Many Relationship example

Comments

[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?