Creating a beginner-friendly CRUD API using Node.js, Express, and MySQL

Aparna Rathore
3 min readAug 23, 2023

--

Creating a beginner-friendly CRUD (Create, Read, Update, Delete) API using Node.js, Express, and MySQL is a great way to learn the basics of building web APIs. Here’s a step-by-step guide to help you get started:

**1. Setup:**
— Install Node.js and MySQL on your machine if you haven’t already.
— Create a new directory for your project and navigate to it using the terminal.

**2. Initialize the Project:**
— Run the following command to initialize a new Node.js project and create a `package.json` file:
```
npm init -y
```

**3. Install Dependencies:**
— Install required packages (`express`, `mysql2`, `body-parser`):
```
npm install express mysql2 body-parser
```

**4. Create Server File:**
— Create a new file named `server.js` in your project directory.

**5. Setup Express and MySQL:**
— Open `server.js` and set up Express and MySQL connections:

```javascript
const express = require(‘express’);
const mysql = require(‘mysql2’);
const bodyParser = require(‘body-parser’);

const app = express();
const port = 3000;

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

const connection = mysql.createConnection({
host: ‘localhost’,
user: ‘your_mysql_username’,
password: ‘your_mysql_password’,
database: ‘your_database_name’
});

connection.connect((err) => {
if (err) {
console.error(‘Error connecting to MySQL:’, err);
} else {
console.log(‘Connected to MySQL’);
}
});
```

**6. Create CRUD Endpoints:**
— Create routes for each CRUD operation (Create, Read, Update, Delete):

```javascript
// Create
app.post(‘/api/users’, (req, res) => {
const { name, email } = req.body;
const query = ‘INSERT INTO users (name, email) VALUES (?, ?)’;
connection.query(query, [name, email], (err, results) => {
if (err) {
res.status(500).json({ error: err.message });
} else {
res.status(201).json({ id: results.insertId });
}
});
});

// Read (Get all users)
app.get(‘/api/users’, (req, res) => {
const query = ‘SELECT * FROM users’;
connection.query(query, (err, results) => {
if (err) {
res.status(500).json({ error: err.message });
} else {
res.status(200).json(results);
}
});
});

// Update
app.put(‘/api/users/:id’, (req, res) => {
const { name, email } = req.body;
const userId = req.params.id;
const query = ‘UPDATE users SET name = ?, email = ? WHERE id = ?’;
connection.query(query, [name, email, userId], (err) => {
if (err) {
res.status(500).json({ error: err.message });
} else {
res.status(204).send();
}
});
});

// Delete
app.delete(‘/api/users/:id’, (req, res) => {
const userId = req.params.id;
const query = ‘DELETE FROM users WHERE id = ?’;
connection.query(query, [userId], (err) => {
if (err) {
res.status(500).json({ error: err.message });
} else {
res.status(204).send();
}
});
});

// Start the server
app.listen(port, () => {
console.log(`Server is running on port ${port}`);
});
```

**7. Create MySQL Database:**
— Create a MySQL database named `test` (or any other name you prefer) using a MySQL client or command line.

**8. Create Users Table:**
— Create a table named `users` with columns `id`, `name`, and `email` in your MySQL database.

**9. Test the API:**
— Run your Node.js server using the command `node server.js`.
— Use tools like Postman or curl to test the CRUD endpoints.

Congratulations! You’ve created a beginner-friendly CRUD API using Node.js, Express, and MySQL. This example is simplified for learning purposes, and in a real-world scenario, you would need to handle error handling, validation, authentication, and other best practices.

--

--

Aparna Rathore
Aparna Rathore

Written by Aparna Rathore

A Tiny Girl with not so many tiny dreams.

No responses yet