How to Connect a discord.js Bot to MySQL or PostgreSQL

Connecting a discord.js Bot to a Database#

Connecting a discord.js bot to MySQL or PostgreSQL requires installing the correct driver, creating a connection pool, and following security best practices. This guide provides working code examples for both databases.

Prerequisites#

• Database host IP, port, name, username, and password from your Database Hosting dashboard.
• Your bot deployed on Discord Bot Hosting.

Connecting to MySQL with mysql2#

Install the package:

bash
COPY
npm install mysql2 dotenv

Create a db.js module:

javascript
COPY
const mysql = require("mysql2/promise");
require("dotenv").config();

const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

async function getBalance(userId) {
    const [rows] = await pool.execute(
        "SELECT balance FROM users WHERE user_id = ?",
        [userId]
    );
    return rows[0]?.balance ?? 0;
}

async function setBalance(userId, amount) {
    await pool.execute(
        "INSERT INTO users (user_id, balance) VALUES (?, ?) ON DUPLICATE KEY UPDATE balance = ?",
        [userId, amount, amount]
    );
}

module.exports = { getBalance, setBalance };

Connecting to PostgreSQL with pg#

Install the package:

bash
COPY
npm install pg dotenv

Create a db.js module:

javascript
COPY
const { Pool } = require("pg");
require("dotenv").config();

const pool = new Pool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    port: 5432
});

async function getBalance(userId) {
    const res = await pool.query(
        "SELECT balance FROM users WHERE user_id = $1",
        [userId]
    );
    return res.rows[0]?.balance ?? 0;
}

async function setBalance(userId, amount) {
    await pool.query(
        "INSERT INTO users (user_id, balance) VALUES ($1, $2) ON CONFLICT (user_id) DO UPDATE SET balance = $2",
        [userId, amount]
    );
}

module.exports = { getBalance, setBalance };

Security Best Practices#

1. Never hardcode credentials. Always use environment variables via .env files.
2. Always use parameterized queries. The ? (MySQL) and $1 (Postgres) syntax prevents SQL injection.
3. Use connection pools. Never create a new connection per query.
4. Set connectionLimit to 10. This is sufficient for a single bot process.

Deploying#

Add DB_HOST, DB_USER, DB_PASS, and DB_NAME as environment variables in your Pterodactyl Startup tab. They will never appear in your codebase. See Database Hosting to get your credentials.