Building a CRUD Application with Node.js, Express, and MySQL
Tags: webdev, javascript, beginners, programming
Published: 2024-06-06 08:00:00

In this blog, we'll walk through creating a simple CRUD (Create, Read, Update, Delete) application using Node.js, Express, and MySQL. This tutorial will guide you through setting up the project, configuring the database, and implementing the CRUD operations.

## Project Setup

### Step 1: Initializing the Project

Create a new directory for your project and initialize it with npm:

```bash
mkdir crud-nodejs
cd crud-nodejs
npm init -y
```

Install the necessary dependencies:

```bash
npm install express mysql2 dotenv body-parser
npm install --save-dev nodemon
```

### Step 2: Project Structure

Create the following project structure:

```
crud-nodejs
├── config
│   └── database.js
├── controllers
│   └── todoController.js
├── middleware
│   └── errorMiddleware.js
├── models
│   └── todo.js
├── routes
│   └── todoRoutes.js
├── .env.example
├── index.js
└── package.json
```

### Step 3: Configuring Environment Variables

Create a `.env` file (copy from `.env

.example`):

```bash
cp .env.example .env
```

Fill in your MySQL database credentials in the `.env` file:

```
PORT=3000
DB_HOST=localhost
DB_USER=your_user
DB_PASSWORD=your_password
DB_DATABASE=your_database
```

### Step 4: Connecting to MySQL

In `config/database.js`, we set up the MySQL connection using the `mysql2` package:

```js
const mysql = require('mysql2');

require('dotenv').config();

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE
});

connection.connect((err) => {
    if (err) throw err;
    console.log('Connected to MySQL database');
});

module.exports = connection;
```

### Step 5: Creating the Express Server

In `index.js`, we configure the Express server and set up the routes and error handling middleware:

```js
const express = require('express');
const bodyParser = require('body-parser');
const todoRoutes = require('./routes/todoRoutes');
const errorMiddleware = require('./middleware/errorMiddleware');

require('dotenv').config();

const app = express();
const PORT = process.env.PORT || 3000;

// Middleware
app.use(bodyParser.json());

// Routes
app.use('/todos', todoRoutes);

// Error middleware
app.use(errorMiddleware);

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

### Step 6: Defining the Todo Model

In `models/todo.js`, we define the functions to interact with the MySQL database:

```js
const db = require('../config/database');

exports.getAllTodos = function(callback) {
    db.query('SELECT * FROM todos', callback);
};

exports.getTodoById = function(id, callback) {
    db.query('SELECT * FROM todos WHERE id = ?', [id], callback);
};

exports.createTodo = function(newTodo, callback) {
    db.query('INSERT INTO todos SET ?', newTodo, callback);
};

exports.updateTodo = function(id, updatedTodo, callback) {
    db.query('UPDATE todos SET ? WHERE id = ?', [updatedTodo, id], callback);
};

exports.deleteTodo = function(id, callback) {
    db.query('DELETE FROM todos WHERE id = ?', [id], callback);
};
```

### Step 7: Creating the Controller

In `controllers/todoController.js`, we define the logic for handling CRUD operations:

```js
const Todo = require('../models/todo');

exports.getAllTodos = function(req, res) {
    Todo.getAllTodos((err, todos) => {
        if (err) throw err;
        res.json(todos);
    });
};

exports.getTodoById = function(req, res) {
    Todo.getTodoById(req.params.id, (err, todo) => {
        if (err) throw err;
        res.json(todo);
    });
};

exports.createTodo = function(req, res) {
    const newTodo = {
        title: req.body.title,
        completed: req.body.completed
    };

    Todo.createTodo(newTodo, (err, result) => {
        if (err) throw err;
        res.json({ message: 'Todo created successfully' });
    });
};

exports.updateTodo = function(req, res) {
    const updatedTodo = {
        title: req.body.title,
        completed: req.body.completed
    };

    Todo.updateTodo(req.params.id, updatedTodo, (err, result) => {
        if (err) throw err;
        res.json({ message: 'Todo updated successfully' });
    });
};

exports.deleteTodo = function(req, res) {
    Todo.deleteTodo(req.params.id, (err, result) => {
        if (err) throw err;
        res.json({ message: 'Todo deleted successfully' });
    });
};
```

### Step 8: Defining Routes

In `routes/todoRoutes.js`, we set up the routes for the Todo API:

```js
const express = require('express');
const router = express.Router();
const todoController = require('../controllers/todoController');

// Routes
router.get('/', todoController.getAllTodos);
router.get('/:id', todoController.getTodoById);
router.post('/', todoController.createTodo);
router.put('/:id', todoController.updateTodo);
router.delete('/:id', todoController.deleteTodo);

module.exports = router;
```

### Step 9: Error Handling Middleware

In `middleware/errorMiddleware.js`, we define a simple error handling middleware:

```js
module.exports = function errorHandler(err, req, res, next) {
    console.error(err.stack);
    res.status(500).send('Something broke!');
};
```

### Step 10: Running the Application

Add the following scripts to `package.json`:

```json
"scripts": {
  "test": "echo \"Error: no test specified\" && exit 1",
  "dev": "nodemon index.js",
  "start": "node index.js"
}
```

Start the application in development mode:

```bash
npm run dev
```

## Conclusion

You now have a fully functional CRUD application built with Node.js, Express, and MySQL. This application allows you to create, read, update, and delete Todo items. This basic structure can be expanded and customized to fit more complex requirements. Happy coding!

## Exploring the Code

Visit the [GitHub repository](https://github.com/manthanank/crud-nodejs/tree/mysql) to explore the code in detail.

---