How to Connect to MySQL database in Express.js

In Express.js, you can use various packages to connect to a MySQL database. One popular package is mysql2 which provides a straightforward way to interact with MySQL databases. First, install mysql2

npm install mysql2

Then, you can create a connection to your MySQL database in your Express.js application:

const express = require('express');
const mysql = require('mysql2');
const app = express();

// Create a connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database',
  waitForConnections: true,
  connectionLimit: 10, // Adjust this value as needed
  queueLimit: 0
});

// Test the connection
pool.getConnection((err, connection) => {
  if (err) {
    console.error('Error connecting to database:', err);
    return;
  }
  console.log('Connected to MySQL database!');
  connection.release();
});

// Example query
app.get('/users', (req, res) => {
  pool.query('SELECT * FROM users', (error, results, fields) => {
    if (error) {
      console.error('Error executing query:', error);
      res.status(500).send('Error retrieving data');
      return;
    }
    res.json(results);
  });
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

Replace 'localhost', 'your_username', 'your_password', and 'your_database' with your MySQL server details. The createPool method creates a connection pool, which manages multiple connections to the database.

The getConnection method tests the connection by attempting to connect to the database and then releasing the connection immediately. This is optional but can be helpful for verifying the connection when the server starts.

The /users endpoint is an example of querying the users table from the database. You can create additional routes to perform different database operations as needed.

Remember to handle errors appropriately, especially when executing queries, to provide meaningful feedback to the client.

This is a basic example to get you started with connecting to a MySQL database using Express.js and the mysql2 package. Adjust the code according to your application’s needs and best practices, like handling connection errors, securing credentials, and handling database interactions in a more modular way.

Related tutoirals