MySQL with NodeJS: insertion of records

Question

MySQL with NodeJS: insertion of records

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I created a script called 'test.js' just to demonstrate a simple insertion of log records into a MySQL database, using NodeJS, as below. I will generate something around 5 logs per second, which makes me assume that it will require a certain processing of the machine to be running this script my every log generated. My question is: is there a way to leave an open connection so logs are being inserted, without having to open and close the connection to each log? I do not know ... if there is a totally different way, I'd like to know about any alternative.

const mysql = require('mysql');

const con = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '123456',
    database: 'meu_db_teste'
});

con.connect((err) => {
    if(err){
        console.log('Erro de conexão do DB');
        return;
    }
    console.log('Conexão estabilizada com threadId: ', con.threadId);
});

// Estes dados do "reg_log" virão de um outro script
const reg_log = { info: 'abcde...xyz', dttime: 'xx:xx:xx' };
con.query('INSERT INTO logs SET ?', reg_log, (err, res, fields) => {
    if(err) throw err;

    console.log('Last insert ID:', res.insertId);
});

con.end((err) => {
    console.log('Connection closed');
});

mysql node.js

asked by anonymous 17.07.2017 / 20:53

2 answers

Optimization with MongoDB Error "bash: ruby: command not found"

score 0 · Answer 1

dbconnection file:

var mysql = require('mysql');

var connMysql = () => {
    return mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '1234',
        database: 'banco'
    });
}

module.exports = () => {
    console.log('Conexao estabelecida com banco de dados');
    return connMysql;
}

using models:

function NoticiasDao(connection) {
    this._connection = connection;
}


NoticiasDao.prototype.getNoticias =  function(callback){
    this._connection.query('select * from noticias', callback);
}


NoticiasDao.prototype.getNoticia = function(callback) {
    this._connection.query('select * from noticias where idNoticias = 2', callback);
}


NoticiasDao.prototype.salvarNoticia = function(noticia, callback) {
    this._connection.query('insert into noticias set ?', noticia, callback);
}



module.exports = function() {

    return NoticiasDao;
}

Controller:

module.exports.formulario = function(app, req, res) {
    response.render("admin/form_add_noticia", { validation: '',noticia:noticia});
}

module.exports.salvarNoticia = function(app, req, res) {
    var noticia = request.body;
    var connection = app.config.dbconnection();
    var noticiaDao = new app.app.models.NoticiasDao(connection);


            noticiaDao.salvarNoticia(noticia, function(error, result) {
                response.redirect('/noticias');
            });

    });
}

using consign you initialize connection, models, controller when starting server

var consign = require('consign');
consign()
    .include('app/routes')
    .then('config/dbconnection.js')
    .then('app/models')
    .then('app/controllers')
    .into(app);

score 0 · Answer 2

The ideal scenario is to separate the database connection file to implement some kind of singleton , something like this:

connection.js

const mysql = require('mysql');
var single_connection;
module.exports = function() {
    if (!single_connection) {
        single_connection = mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: '123456',
            database: 'meu_db_teste'
        });
    }
    return single_connection;
}

teste.js

const db = require('connection);
...