MySQL Connection in Node.js

Step 1: Install Dependencies

First, initialize a Node.js project and install the mysql2 package:

# Initialize project
npm init -y

# Install mysql2 (recommended over mysql)
npm install mysql2

Step 2: Simple Connection

Create a file called db-simple.js:

// db-simple.js
const mysql = require('mysql2');

// Create a single connection
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school'
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err.message);
        return;
    }
    console.log('Connected to MySQL database!');
    console.log('Connection ID:', connection.threadId);
});

// Run a simple query
connection.query('SELECT 1 + 1 AS result', (err, results) => {
    if (err) {
        console.error('Query error:', err);
        return;
    }
    console.log('Test query result:', results[0].result);
});

// Close connection when done
connection.end((err) => {
    if (err) {
        console.error('Error closing connection:', err);
        return;
    }
    console.log('Connection closed.');
});

Step 3: Connection Pool (Recommended)

For production applications, use a connection pool. Create db-pool.js:

// db-pool.js
const mysql = require('mysql2');

// Create a connection pool
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school',

    // Pool specific settings
    waitForConnections: true,  // Wait if no connections available
    connectionLimit: 10,        // Maximum connections in pool
    queueLimit: 0,             // Unlimited waiting requests
    enableKeepAlive: true,     // Keep connections alive
    keepAliveInitialDelay: 0   // Start keepalive immediately
});

// Test the pool
pool.query('SELECT NOW() as current_time', (err, results) => {
    if (err) {
        console.error('Pool query error:', err);
        return;
    }
    console.log('Current database time:', results[0].current_time);
});

// Export for use in other files
module.exports = pool;

Step 4: Promise-based Connection (Modern Approach)

Use async/await with promises. Create db-promise.js:

// db-promise.js
const mysql = require('mysql2/promise');

// Configuration object
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'school',
    waitForConnections: true,
    connectionLimit: 10
};

// Create promise-based pool
const pool = mysql.createPool(dbConfig);

// Example usage with async/await
async function testConnection() {
    try {
        // Get a connection from pool
        const connection = await pool.getConnection();
        console.log('Connected successfully!');

        // Run a query
        const [rows] = await connection.execute('SELECT * FROM students LIMIT 5');
        console.log('Students:', rows);

        // Release connection back to pool
        connection.release();

    } catch (error) {
        console.error('Database error:', error.message);
    }
}

// Run the test
testConnection();

// Export pool for other modules
module.exports = pool;

Step 5: Environment Variables for Security

Never hardcode credentials! Create .env file:

# .env file (add to .gitignore!)
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=school
DB_CONNECTION_LIMIT=10

Install dotenv and use environment variables:

npm install dotenv

// db-secure.js
require('dotenv').config();
const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    connectionLimit: parseInt(process.env.DB_CONNECTION_LIMIT) || 10
});

module.exports = pool;

Complete Example: Database Module

A reusable database module for your projects. Create database.js:

// database.js - Reusable database module
require('dotenv').config();
const mysql = require('mysql2/promise');

class Database {
    constructor() {
        this.pool = null;
    }

    // Initialize the connection pool
    async connect() {
        if (this.pool) return this.pool;

        this.pool = mysql.createPool({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASSWORD || '',
            database: process.env.DB_NAME || 'test',
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });

        // Test connection
        try {
            const connection = await this.pool.getConnection();
            console.log('Database connected successfully');
            connection.release();
        } catch (error) {
            console.error('Database connection failed:', error.message);
            throw error;
        }

        return this.pool;
    }

    // Execute a query with parameters
    async query(sql, params = []) {
        const pool = await this.connect();
        const [results] = await pool.execute(sql, params);
        return results;
    }

    // Get a single row
    async getOne(sql, params = []) {
        const results = await this.query(sql, params);
        return results[0] || null;
    }

    // Close all connections
    async close() {
        if (this.pool) {
            await this.pool.end();
            this.pool = null;
            console.log('Database connections closed');
        }
    }
}

// Export singleton instance
module.exports = new Database();

Usage in your application:

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

async function main() {
    try {
        // Query all students
        const students = await db.query('SELECT * FROM students');
        console.log('All students:', students);

        // Get single student
        const student = await db.getOne(
            'SELECT * FROM students WHERE id = ?',
            [1]
        );
        console.log('Student #1:', student);

        // Insert new student
        const result = await db.query(
            'INSERT INTO students (name, email, age) VALUES (?, ?, ?)',
            ['John Doe', 'john@example.com', 22]
        );
        console.log('Inserted ID:', result.insertId);

    } catch (error) {
        console.error('Error:', error.message);
    } finally {
        await db.close();
    }
}

main();