Node.js CRUD Operation with MySQL example
Tags: webdev, javascript, node, mysql
Published: 2021-08-11 11:37:52

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](https://www.bezkoder.com/node-js-rest-api-express-mysql/)

<h2>Application overview</h2>
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 <code>Customer</code> model, write the controller. Then we define routes for handling all CRUD operations:
<div class="table-responsive">
<table class="table table-hover  table table-hover" width="100%">
<thead>
<tr>
<th>Methods</th>
<th>Urls</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<tr>
<td>GET</td>
<td>/customers</td>
<td>get all Customers</td>
</tr>
<tr>
<td>GET</td>
<td>/customers/42</td>
<td>get Customer with id=42</td>
</tr>
<tr>
<td>POST</td>
<td>/customers</td>
<td>add new Customer</td>
</tr>
<tr>
<td>PUT</td>
<td>/customers/42</td>
<td>update Customer with id=42</td>
</tr>
<tr>
<td>DELETE</td>
<td>/customers/42</td>
<td>remove Customer with id=42</td>
</tr>
<tr>
<td>DELETE</td>
<td>/customers</td>
<td>remove all Customers</td>
</tr>
</tbody>
</table>
</div>
Finally, we're gonna test the Rest Apis using Postman.

Our project structure will be like:

<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-project-structure.png" alt="nodejs-rest-api-express-mysql-project-structure" width="241" height="344" class="alignnone size-full wp-image-577" />

<h2>Test the APIs</h2>
Run our Node.js application with command: <code>node server.js</code>.
The console shows:
```
Server is running on port 3000.
Successfully connected to the database.
```
Using Postman, we're gonna test all the Apis above.

<strong><li>Create a new Customer using <code>POST /customers</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-create.png" alt="nodejs-rest-api-express-mysql-test-create" width="732" height="452" class="alignnone size-full wp-image-592" />

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 |
+----+--------------------+--------+--------+
```

<strong><li>Retrieve all Customers using <code>GET /customers</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-retrieve-all.png" alt="nodejs-rest-api-express-mysql-test-retrieve-all" width="732" height="620" class="alignnone size-full wp-image-594" />

<strong><li>Retrieve a single Customer by id using <code>GET /customers/:customerId</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-retrieve-one.png" alt="nodejs-rest-api-express-mysql-test-retrieve-one" width="731" height="399" class="alignnone size-full wp-image-596" />

<strong><li>Update a Customer using <code>PUT /customers/:customerId</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-update.png" alt="nodejs-rest-api-express-mysql-test-update" width="733" height="443" class="alignnone size-full wp-image-599" />

Check <code>customers</code> 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 |
+----+--------------------+----------+--------+
```

<strong><li>Delete a Customer using <code>DELETE /customers/:customerId</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-delete-one.png" alt="nodejs-rest-api-express-mysql-test-delete-one" width="724" height="353" class="alignnone size-full wp-image-600" />

Customer with id=2 was removed from <code>customers</code> table:
```
mysql> SELECT * FROM customers;
+----+--------------------+----------+--------+
| id | email              | name     | active |
+----+--------------------+----------+--------+
|  1 | bezkoder@gmail.com | zKoder   |      1 |
|  3 | drhelen@gmail.com  | Dr.Helen |      1 |
+----+--------------------+----------+--------+
```

<strong><li>Delete all Customers using <code>DELETE /customers</code> Api</li></strong>
<img src="https://bezkoder.com/wp-content/uploads/2019/09/nodejs-rest-api-express-mysql-test-delete-all.png" alt="nodejs-rest-api-express-mysql-test-delete-all" width="729" height="360" class="alignnone size-full wp-image-603" />

Now there are no rows in <code>customers</code> 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](https://www.bezkoder.com/node-js-rest-api-express-mysql/)

## Further Reading
Related Posts:
- <a href="https://bezkoder.com/node-js-express-sequelize-mysql/">Node.js Rest APIs example with Express & MySQL (including Sequelize)</a>
- <a href="https://bezkoder.com/node-js-jwt-authentication-mysql/">Node.js – JWT Authentication & Authorization example</a>
- <a href="https://bezkoder.com/node-js-upload-excel-file-database/">Node.js: Upload/Import Excel file data into MySQL Database</a>
- <a href="https://bezkoder.com/node-js-upload-csv-file-database/">Node.js: Upload CSV file data into MySQL Database</a>

Fullstack:
- <a href="https://bezkoder.com/vue-js-node-js-express-mysql-crud-example/">Vue.js + Node.js + Express + MySQL example</a>
- <a href="https://bezkoder.com/vue-node-express-mongodb-mevn-crud/">Vue.js + Node.js + Express + MongoDB example</a>
- <a href="https://bezkoder.com/angular-node-express-mysql/">Angular 8 + Node.js Express + MySQL example</a>
- <a href="https://bezkoder.com/angular-10-node-js-express-mysql/">Angular 10 + Node.js Express + MySQL example</a>
- <a href="https://bezkoder.com/angular-11-node-js-express-mysql/">Angular 11 + Node.js Express + MySQL example</a>
- <a href="https://bezkoder.com/angular-12-node-js-express-mysql/">Angular 12 + Node.js Express + MySQL example</a>
- <a href="https://bezkoder.com/react-node-express-mysql/">React + Node.js + Express + MySQL example</a>

Security: <a href="https://bezkoder.com/node-js-jwt-authentication-mysql/">Node.js – JWT Authentication & Authorization example</a>

Deployment:
- <a href="https://bezkoder.com/deploy-node-js-app-heroku-cleardb-mysql/">Deploying/Hosting Node.js app on Heroku with MySQL database</a>
- [Dockerize Node.js Express and MySQL example – Docker Compose](https://www.bezkoder.com/docker-compose-nodejs-mysql/)

Node.js & MySQL Associations:
- <a href="https://bezkoder.com/sequelize-associate-one-to-many/">One-to-Many Relationship example</a>
- <a href="https://bezkoder.com/sequelize-associate-many-to-many/">Many-to-Many Relationship example</a>