How to Connect Express to MySQL: Simple Guide

To connect Express to MySQL, install the mysql2 package and create a connection using mysql.createConnection(). Then use this connection inside your Express app to query the database asynchronously.

Syntax

Here is the basic syntax to connect Express to MySQL using the mysql2 package:

• mysql.createConnection(config): Creates a connection to the MySQL database with host, user, password, and database name.
• connection.connect(): Opens the connection.
• connection.query(sql, callback): Runs SQL queries.
• connection.end(): Closes the connection.

javascript

import mysql from 'mysql2';

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_user',
  password: 'your_password',
  database: 'your_database'
});

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

connection.query('SELECT 1 + 1 AS solution', (err, results) => {
  if (err) throw err;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

Output

Connected to MySQL!
The solution is:  2

Example

This example shows a simple Express server connected to MySQL. It queries a table and sends results as JSON.

javascript

import express from 'express';
import mysql from 'mysql2/promise';

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

// Create a MySQL connection pool for better performance
const pool = mysql.createPool({
  host: 'localhost',
  user: 'your_user',
  password: 'your_password',
  database: 'your_database',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

app.get('/users', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: 'Database query failed' });
  }
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

Output

Server running at http://localhost:3000

When you visit http://localhost:3000/users, it returns JSON data from the users table.

Common Pitfalls

• Not installing mysql2 package or using the older mysql package which is deprecated.
• Forgetting to handle asynchronous queries properly, causing the server to crash or hang.
• Not closing connections or not using connection pools, which can exhaust database connections.
• Hardcoding credentials in code instead of using environment variables.

javascript

/* Wrong way: Using callback without error handling and no connection pool */
import mysql from 'mysql2';

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'pass',
  database: 'db'
});

connection.connect(err => {
  if (err) throw err; // crashes server if error
});

connection.query('SELECT * FROM users', (err, results) => {
  if (err) throw err; // crashes server if error
  console.log(results);
});

connection.end();

/* Right way: Using async/await with connection pool and try/catch */
import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: 'localhost',
  user: 'user',
  password: 'pass',
  database: 'db'
});

async function getUsers() {
  try {
    const [rows] = await pool.query('SELECT * FROM users');
    console.log(rows);
  } catch (error) {
    console.error('Query error:', error);
  }
}

getUsers();

Quick Reference

• Install mysql2: npm install mysql2
• Use mysql2/promise for async/await support.
• Use connection pools for production apps.
• Always handle errors with try/catch.
• Keep database credentials secure using environment variables.

Key Takeaways

Use the mysql2 package with promise support to connect Express to MySQL asynchronously.

Create a connection pool to manage multiple database connections efficiently.

Always handle errors to prevent server crashes during database queries.

Avoid hardcoding credentials; use environment variables for security.

Close connections properly or use pools to avoid exhausting database resources.