I close the connection, and can no longer connect Nodejs Mysql

Question

I need to close the connections after the query return success or error, the first time I do the query ok, but then it closes and no longer queries... I have tried to close in several parts of the code without success.

    try {
        connection.query(sql, [empresaID], (error, results) => {
            if (error) return res.status(400).json(error);

            if (results.length === 0) {
                res.status(401).json({ msg: 'Empresa não encontrada' })
                //connection.end()
            } else {
                res.status(200).json(results);
                //connection.end()
            }
            //connection.end()
        })
    } catch(err) {
        res.status(500).json(err);
    }

Answer 1

Your code is in the wrong stream, because it works like this, if any error has not results and if you have results has not error need to put a if to make this decision, following his example:

try {
    connection.query(sql, [empresaID], (error, results) => {
        if (error) {
            return res.status(400).json(error);
        } else {
           res.status(200).json(results);               
        }
        connection.end()
    })
} catch(err) {
    res.status(500).json(err);
}

that is, this code above was only to understand the flow that must be done. If the empresaID no exists no error is also returned a default value that can be better worked on that if.

Another thing, the connection should be created whenever it will use and closed after its use, I would create a js thus:

const mysql = require('mysql');

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

Then in the location that will use the connection import:

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

and use like this:

conn.executeSQLQuery('SELECT * FROM todos', (error, results, fields) => {
    if (error) {
        res.json(error);
    } else {
        res.json(results);
    }
});

you won’t be worrying about this problem anymore, because I reported this connection should always be created in the.