Building a MySQL Database Connection with Node.js and Express

In this tutorial, we’ll create a simple Node.js application with Express that connects to a MySQL database. We’ll cover setting up the project, installing necessary dependencies, configuring the database connection, and implementing basic CRUD operations.

Prerequisites

Ensure you have Node.js and npm (Node Package Manager) installed on your machine. Additionally, make sure you have MySQL installed locally or access to a MySQL database server.

Step 1: Setting Up the Project

Create a new directory for your project and initialize it with a package.json file:

mkdir node-mysql-express
cd node-mysql-express
npm init -y

Step 2: Installing Dependencies

Install the required packages for your project:

npm install express mysql body-parser

• express: A web framework for Node.js.
• mysql: A MySQL client for Node.js.
• body-parser: Middleware to parse incoming request bodies.

Step 3: Creating the Express App

Create an index.js file to set up your Express application and establish the MySQL database connection:

const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');

const app = express();
const PORT = process.env.PORT || 3000;
// Middleware
app.use(bodyParser.json());
// MySQL Connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database_name'
});
// Connect to MySQL
db.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL: ' + err.stack);
    return;
  }
  console.log('Connected to MySQL as ID ' + db.threadId);
});
// Routes
app.get('/api/users', (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) {
      console.error('Error executing query: ' + err.stack);
      res.status(500).send('Error fetching users');
      return;
    }
    res.json(results);
  });
});
// Start the server
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Step 4: Implementing CRUD Operations

You can expand the routes to implement CRUD operations (Create, Read, Update, Delete) using MySQL queries:

// Create a new user
app.post('/api/users', (req, res) => {
  const { username, email } = req.body;
  db.query('INSERT INTO users (username, email) VALUES (?, ?)', [username, email], (err, result) => {
    if (err) {
      console.error('Error executing query: ' + err.stack);
      res.status(400).send('Error creating user');
      return;
    }
    res.status(201).send('User created successfully');
  });
});

// Update an existing user
app.put('/api/users/:id', (req, res) => {
  const { username, email } = req.body;
  const userId = req.params.id;
  db.query('UPDATE users SET username = ?, email = ? WHERE id = ?', [username, email, userId], (err, result) => {
    if (err) {
      console.error('Error executing query: ' + err.stack);
      res.status(400).send('Error updating user');
      return;
    }
    res.send('User updated successfully');
  });
});

// Delete a user
app.delete('/api/users/:id', (req, res) => {
  const userId = req.params.id;
  db.query('DELETE FROM users WHERE id = ?', [userId], (err, result) => {
    if (err) {
      console.error('Error executing query: ' + err.stack);
      res.status(400).send('Error deleting user');
      return;
    }
    res.send('User deleted successfully');
  });
});

Step 5: Testing the API

Run your Node.js application (node index.js) and test the API using tools like Postman. Send HTTP requests to localhost:3000/api/users to interact with the MySQL database using the defined routes.

Conclusion

In this tutorial, we’ve built a simple Node.js application with Express that connects to a MySQL database and implements basic CRUD operations. You can further enhance this project by adding error handling, input validation, and additional routes based on your requirements.