Server/modules/database.js

const mariadb = require("mariadb")
const logger = require("./logger")
const fs = require("fs/promises")
const path = require("path")

const dbConfig = {
    host: process.env.DATABASE_HOST,
    user: process.env.DATABASE_USER,
    password: process.env.DATABASE_PASSWORD,
    database: process.env.DATABASE_NAME,
    acquireTimeout: 20000,
    connectTimeout: 20000,
    bigIntAsNumber: true,
    insertIdAsNumber: true,
    decimalAsNumber: true
}

const pool = mariadb.createPool({
    ...dbConfig,
    connectionLimit: Number(process.env.DATABASE_CONNECTION_LIMIT) || 10
})

async function runDDL() {
    let conn
    try {
        conn = await mariadb.createConnection({
            ...dbConfig,
            multipleStatements: true
        })

        const ddlDir = path.join(process.cwd(), "data", "ddl")
        const files = await fs.readdir(ddlDir)
        
        const sqlFiles = files
            .filter(file => file.endsWith(".sql"))
            .sort()

        for (const file of sqlFiles) {
            const filePath = path.join(ddlDir, file)
            const sql = await fs.readFile(filePath, "utf-8")

            if (sql.trim()) {
                logger.log(`Execution of ${file.bold}`, ["DDL", "yellow"])
                await conn.query(sql)
            }
        }
        logger.log("Initialization of tables done.", ["DDL", "yellow"])
    } catch (error) {
        logger.error("Error while executing scripts :", ["DDL", "yellow"])
        logger.error(error)
        throw error
    } finally {
        if (conn) {
            await conn.end()
        }
    }
}

module.exports = {
    pool,
    runDDL
}