Reuse mysql connection function where

Question

Hello!

Analyzing my source code I realized that within the project there is a part that repeats many times.

I would like to reuse repetitive code in my mysql project

 const pool = mysql.createPool(config)

return new Promise((resolve, reject)=>{
            const queryString = `SELECT ...`
            pool.getConnection((error, connection)=>{
                if(error){
                    return
                }
                connection.query(queryString, (err, result)=>{
                    connection.release()
                    if(err) {                
                        return false
                    }
                    resolve(result)
                })
            })
        })

Where in the query will be used other querys, such as INSERTS, UPDATE, SELECTS

I saw that you can use callbacks `

const conn = pool.getConnection((error, connection)=>{
   callback(error,connection)
})

But when using the query, it says it is Undefined

const con = conn.connection
con.connection.query( 'SELECT ...', err, result )
con.release();

Answer 1

Well I have a code and I will share how I would do this execution planning of SQL that returns records or not, example:

mysql-connection.js

const mysql = require('mysql');

module.exports = {
    connection: function () {
        return mysql.createConnection({
            host     : 'localhost',
            port     : 3306,
            user     : 'root',
            password : 'senha',
            database : 'db'
        });
    },    
    executeSQLQueryParams: function (sql, params, callback) {
        const conn = this.connection();
        conn.query(sql,params, function(error, results, fields){        
            callback(error, results, fields);
            conn.end();
        });       
    },    
    executeSQLQuery: function (sql, callback) {
        const conn = this.connection();    
        conn.query(sql, function(error, results, fields){
            callback(error, results, fields)
        });    
    }
}

Como utilizar:

const conn = require('./mysql-connection');

Return all records from the table todos:

router.get('/todos', (req, res) => {
    conn.executeSQLQuery('SELECT * FROM todos ORDER BY description', 
      (error, results, fields) => {
        if (error) {
            res.json(error);
        } else {
            res.json(messages.return_data(200,messages.select_all,results));
        }
    });
}

---

Save new record to table todos:

router.post('/todo', (req, res) => {
    const description = req.body.description;
    const done = req.body.done;
    const params = [description, done];            
    conn.executeSQLQueryParams('INSERT INTO todos(description, done) VALUES(?,?)', 
        params, 
        (error, results, fields) => 
    {
        if (error) {
            res.json(error);
        } else {
            const id = results.insertId;
            if (id) {
                conn.executeSQLQueryParams('SELECT * FROM todos WHERE id=?',
                 [id], 
                 (error, results, fields) => 
                 {
                    if (error) {
                        res.json(error);
                    } else {
                        res.json(messages.return_data(200,messages.insert,results[0]));
                    }
                });
            }
        }
    });
}

this example is with node, mysql and express.