CODEWITHSUNDEEP
expressconnection-poolingnode-mysql2
CodeMHT
CodeMHT

Reputation: 1

MySQL2 Connection Pooling

I am trying to connect to my database using a connection pool. The rest api uses node and express and I'm using mysql/promise for the connection pool but when I run the code I get an error

this is my code

import express from 'express';
import mysql2 from 'mysql2';
import multer from 'multer';
import path from 'path';
import dbPool from './db.js';
import dotenv from 'dotenv';
dotenv.config()

const route = express.Router();

route.get("/", (req, res) => {
    dbPool.query("SELECT * FROM vehicles", (err, result) => {
        if (err) {
            console.error("Loading Failure:", err);
            res.status(500).send("Loading Failure");
        } else {
            res.send(result);
        }
    });
})

this is my connection pool

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

dotenv.config();

const dbPool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 20, // Adjust as per your requirements
    queueLimit: 0,

})

dbPool.getConnection((err, connect) => {
    if (err) {
        console.log(err)
    } else if (connect) {
        console.log("Pool Connected")
    }
    connect.release()
})


export default dbPool;

i expect to get information about the vehicles stored in the vehicles table but i keep getting the error

Error: Callback function is not available with promise clients.
    at PromisePool.query (C:\Users\gwala\carRental\rental_api\node_modules\mysql2\promise.js:358:13)
    at file:///C:/Users/gwala/carRental/rental_api/routes/vehicles.js:12:12
    at Layer.handle [as handle_request] (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\layer.js:95:5)
    at next (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\route.js:144:13)
    at Route.dispatch (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\route.js:114:3)
    at Layer.handle [as handle_request] (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\layer.js:95:5)
    at C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\index.js:284:15
    at Function.process_params (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\index.js:346:12)  
    at next (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\index.js:280:10)
    at Function.handle (C:\Users\gwala\carRental\rental_api\node_modules\express\lib\router\index.js:175:3)

Upvotes: 0

Views: 121

Answers (1)

VeeBee
VeeBee

Reputation: 55

The issue is you're using "mysql2/promise" and then using a callback function in getConnection.

dbPool.getConnection((err, connect) => {
    if (err) {
        console.log(err)
    } else if (connect) {
        console.log("Pool Connected")
    }
    connect.release()
})

The documentation: https://sidorares.github.io/node-mysql2/docs shows you're supposed to use

const conn = await pool.getConnection();

// Do something with the connection
await conn.query(/* ... */);

// Don't forget to release the connection when finished!
pool.releaseConnection(conn);

Since you're also checking for an error, I suppose a simple trycatch block should do the trick.

Upvotes: 0

Related Questions