CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
R3uK
R3uK

Reputation: 14537

pool.query is not a function - Using npm mysql package to use connection pool

I'm using npm mysql package, to create a connection pool to my DataBase with dbHandler.js :

var mysql = require("mysql");

var pool = mysql.createPool({
    connectionLimit : 10,
    host: "path.to.host",
    user: "username",
    password: "password",
    database: "dbname",
    debug: false
});

exports.pool = pool;

Then I call it inside routesHandler.js, to handle my SQL request :

var mysql = require("mysql");
var pool = require("./dbHandler");

exports.login = function(req, res) {
    var email = req.body.email;
    pool.query('SELECT * FROM users WHERE users.email = ?', [email], function(
        error,
        results,
        fields
    ) {
        if (error) {
            res.send({
                code: 400,
                failed: "Error ocurred"
            });
        } else {
            if (results.length > 0) {
                //Mail authorized
                res.send({
                    code: 200,
                    success: "Sucessfull login"
                });
            } else {
                //Mail unauthorized
                res.send({
                    code: 403,
                    failed: "Unauthorized mail"
                });
            }
        }
    });
};

I'm not sure it's the best way to do it, but I can't seem to found a proper example... :/
I would gladly :) accept any suggestions or links about the way to handle the whole :

But I'm getting that basic error message but I can't figure out how to get rid of it :

TypeError: pool.query is not a function

Any pointers will be welcome!

Upvotes: 7

Views: 14898

Answers (4)

Ibukun King-David Bello
Ibukun King-David Bello

Reputation: 1

You have to create a client from the pool, call something like this

const { client } = pool();

await pool.query('SELECT * FROM users WHERE users.email = ?', [email], function(error, results, fields) {
    if (error) {
        res.send({
            code: 400,
            failed: "Error ocurred"
        });
    } else {
        if (results.length > 0) {
            //Mail authorized
            res.send({
                code: 200,
                success: "Sucessfull login"
            });
        } else {
            //Mail unauthorized
            res.send({
                code: 403,
                failed: "Unauthorized mail"
            });
        }
    }
});

Upvotes: 0

Pooya Panahandeh
Pooya Panahandeh

Reputation: 638

I had a similar problem and I tried most of the available solution but none of them are working and finally, I got the answer to my problem.

dbHandler.js file:

var Pool = require('pg').Pool;

var pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'database_name',
    password: '#############',
    port: 5432,
});


module.exports = {pool};

anotherfile.js:

let {pool: pool} = require('dbHandler');
// by this calling method I was able to use pool

Upvotes: 6

Amit Wagner
Amit Wagner

Reputation: 3264

You do exports.pool = pool;, so you must call the pool propriety like this var pool = require("./dbHandler");.

If you use module.exports=pool in your dbHandler then your code will work as is.

If not just use my answer :

var pool = require("./dbHandler").pool;

exports.login = function(req, res) {
    var email = req.body.email;
    pool.query('SELECT * FROM users WHERE users.email = ?', [email], function(
        error,
        results,
        fields
    ) {
        if (error) {
            res.send({
                code: 400,
                failed: "Error ocurred"
            });
        } else {
            if (results.length > 0) {
                //Mail authorized
                res.send({
                    code: 200,
                    success: "Sucessfull login"
                });
            } else {
                //Mail unauthorized
                res.send({
                    code: 403,
                    failed: "Unauthorized mail"
                });
            }
        }
    });
};

Upvotes: 9

shahabvshahabi
shahabvshahabi

Reputation: 955

i think you must use async/await before the function like this : 

exports.login = async function(req, res) {
    var email = req.body.email;
   await pool.query('SELECT * FROM users WHERE users.email = ?', [email], function(
        error,
        results,
        fields
    ) {
        if (error) {
            res.send({
                code: 400,
                failed: "Error ocurred"
            });
        } else {
            if (results.length > 0) {
                //Mail authorized
                res.send({
                    code: 200,
                    success: "Sucessfull login"
                });
            } else {
                //Mail unauthorized
                res.send({
                    code: 403,
                    failed: "Unauthorized mail"
                });
            }
        }
    });
};

Upvotes: 0

Related Questions