Node.js + MySQL (Connecton Pool + Prepared Statement + Transaction)

Jan 28, 2024 · 5 min read · Node JavaScript ·

Overview

Requirement

I'm tring to write some Node.js functions to wrap some basic database operations and my requirement is to support these features.

Connection pool
Prepared statement
Transaction
Throw exception: when there is any exception, it can throw the exception to outside.

Node.js MySQL drivers

First, I find there are 4 drivers for Node.js to connect to MySQL/MariaDB, this is really ...

This is an old project and it's original driver is mysql. Because it doesn't support Prepared statement, I decide to migrate it to mysql2. These are MySQL drivers for Node.js:

Code sample

The folowing code will declare the driver and create the connection pool.

 1'use strict'
 2
 3const fs = require('fs/promises');  // Node.js built-in module for file operations
 4const mysql = require('mysql2');    // Database client driver
 5const winston = require('winston'); // Log framework
 6const winstonConfig = require('./winston-config.js'); // Load the JSON config
 7
 8const logger = winston.createLogger(winstonConfig);
 9
10let pool;
11
12async function initDatabase() {
13    logger.info("Start to connect to database...");
14    try {
15        pool = require('mysql2/promise').createPool({
16            decimalNumbers: true,   // This is important, check https://github.com/sidorares/node-mysql2/tree/master/documentation/en
17            host: process.env.DB_HOST,
18            port: process.env.DB_PORT,
19            user: process.env.DB_USER,
20            password: process.env.DB_PASSWORD,
21            database: process.env.DB_DATABASE,
22            waitForConnections: true,
23            connectionLimit: 10,
24            maxIdle: 10, // max idle connections, the default value is the same as `connectionLimit`
25            idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
26            queueLimit: 0,
27            enableKeepAlive: true,
28            keepAliveInitialDelay: 0,
29            timezone: "Z"
30        });
31
32    } catch (err) {
33        logger.error("Cannot connect to database: " + err);
34        process.exit(1)
35    }
36}

Query sample

 1async function getTable1(value) {
 2    logger.info("Start to get data from table1");
 3    let result = [];
 4    let sql = `SELECT * FROM table1 WHERE key = ?' : ''}`;
 5    logger.info(`SQL: ${sql}, bind var: ${value}`);
 6
 7    let conn = null;
 8    try {
 9        conn = await pool.getConnection();
10        const [response] = await conn.execute(sql, [value]);
11        for (let row of response) {
12            let row1 = {
13                    "key1": row.VALUE1,
14                    "key2": row.VALUE2,
15                    "key3": row.VALUE3,
16            };
17            result.push(row1);
18        }
19    } catch (err) {
20        logger.error("Get data error: ", err);
21        throw err;
22    } finally {
23        if (conn) await conn.release();     // Release connection
24    }
25    return result;
26}

(Do NOT use) Query sample

The following code can query data, but if there is any exception, it will NOT raise. I'm not sure the reason, it seems due to promise?

 1async function getTable1(value) {
 2    logger.info("Start to get data from table1");
 3    let result = [];
 4    let sql = `SELECT * FROM table1 WHERE key = ?' : ''}`;
 5    logger.info(`SQL: ${sql}, bind var: ${value}`);
 6
 7    await pool.getConnection()
 8        .then(conn => {
 9            const res = conn.query(sql, [value]);
10            conn.release();
11            return res;
12        }).then(result => {
13            for (let row of result[0]) {
14                let row1 = {
15                    "key1": row.VALUE1,
16                    "key2": row.VALUE2,
17                    "key3": row.VALUE3,
18                };
19                result.push(row1);
20            }
21        }).catch(err => {
22            logger.error("Get data error: ", err);
23            throw err;
24        });
25    return result;
26}

Insert data sample

 1async function postTable1(data) {
 2    logger.info("Start to insert data into table1");
 3    let sql = "INSERT INTO table1 (...) VALUES (?,?,?, ...);";
 4    let bindVar = [data.value1, data.value2, data.value3, ...];
 5    logger.info(`SQL: ${sql}, bind var: ${bindVar}`);
 6
 7    let insert_id = -1;
 8    let conn = null;
 9    try {
10        conn = await pool.getConnection();
11        const [response] = await conn.execute(sql, bindVar);
12        insert_id += response.insertId;
13        logger.info(`Insert to table1, the new id is ${insert_id}`);
14    } catch (err) {
15        logger.error("Insert table1 error: ", err);
16        throw err;
17    } finally {
18        if (conn) await conn.release();     // Release connection
19    }
20    return insert_id;
21}

Update sample

 1async function updateTable1(data) {
 2    logger.info("Start to update name of table1");
 3    let sql = "UPDATE table1 SET name = ? WHERE id = ? "
 4    let bindVar = [data.name, data.id];
 5    logger.info(`SQL: ${sql}, bind var: ${JSON.stringify(bindVar)}`);
 6    let affectedRows = 0;
 7
 8    let conn = null;
 9    try {
10        conn = await pool.getConnection();
11        const [response] = await conn.execute(sql, bindVar);
12        affectedRows += response.affectedRows;
13        logger.info(`Update ${affectedRows} row(s) in table1.`);
14    } catch (err) {
15        logger.error("Update name of table1 error: ", err);
16        throw err;
17    } finally {
18        if (conn) await conn.release();     // Release connection
19    }
20    return affectedRows;
21}

Delete sample

 1async function deleteTable(data) {
 2    logger.info(`Start to delete record of table1, input is ${JSON.stringify(data)}`);
 3    let sql = "DELETE FROM table1 WHERE id = ?; "
 4    let bindVar = [data.id];
 5    logger.info(`SQL: ${sql}, bind var: ${JSON.stringify(bindVar)}`);
 6    let affectedRows = 0;
 7
 8    let conn = null;
 9    try {
10        conn = await pool.getConnection();
11        const [responseTestResult] = await conn.execute(sql, bindVar);
12        affectedRows += responseTestResult.affectedRows;
13        logger.info(`Delete ${affectedRows} row(s) in table1.)`);
14    } catch (err) {
15        logger.error("Delete table1 error: ", err);
16        throw err;
17    } finally {
18        if (conn) await conn.release();     // Release connection
19    }
20    return t_affectedRows;
21}

Transaction

 1async function updateMultipleTable(data) {
 2    logger.info(`Start to update table1 and table2, input is ${JSON.stringify(data)}`);
 3    let affectedRows = 0;
 4    let sql1 = "UPDATE table1 SET name = ? WHERE id = ? "
 5    let bindVar1 = [data.name1, data.id1];
 6    let sql2 = "UPDATE table2 SET name = ? WHERE id = ? "
 7    let bindVar2 = [data.name2, data.id2];
 8
 9    let conn = null;
10    try {
11        conn = await pool.getConnection();
12        await conn.beginTransaction();
13
14        // Update Table1 
15        const [response] = await conn.execute(sql1, bindVar1);
16        affectedRows += response.affectedRows;
17        logger.info(`Update ${affectedRows} row(s) in table1.`);
18
19        // Update Table2
20        const [response] = await conn.execute(sql2, bindVar2);
21        affectedRows += response.affectedRows;
22        logger.info(`Update ${affectedRows} row(s) in table2.`);
23
24        // Commit
25        await conn.commit();
26    } catch (error) {
27        logger.error("Update table1 error: ", err);
28        if (conn) await conn.rollback();    // Rollback for any error
29        throw error;
30    } finally {
31        if (conn) await conn.release();     // Release connection
32    }
33    return affectedRows;
34}

Node.js + MySQL (Connecton Pool + Prepared Statement + Transaction)

Overview

Requirement

Node.js MySQL drivers

Code sample

Posts in this Series