CODEWITHSUNDEEP
mysqlnode.jspostgresqlsqlite
Michael Nielsen
Michael Nielsen

Reputation: 1242

NodeJS, storing data in a SQLite from both a MySQL and a PostgreSQL

I've created a script which is using both SQLite, Mysql and PostgreSQL. I'm getting values from a PostgreSQL, which I use to call a MySQL, which I finally store in a SQLite. The SQLite is then used for other applications. I'm thinking of running the node.js script every half hour as a cron job.

This is my code: https://jsfiddle.net/bjo93syg/

var pgPromise = require('pg-promise')();
var mysql     = require('mysql');
var sqlite3   = require('sqlite3').verbose();

var pgConnection = pgPromise('postgres://my-user@localhost/my-db');

var mysqlConnection = mysql.createConnection({
  host     : 'localhost',
  user     : 'my-user',
  password : 'my-pass',
  database : 'my-database'
});

var sqliteConnection = new sqlite3.Database('test.db');

sqliteConnection.serialize(function () {
    sqliteConnection.run("BEGIN TRANSACTION");
    sqliteConnection.run("DROP TABLE IF EXISTS numbers");
    sqliteConnection.run("CREATE TABLE numbers (xnumber BIGINT)");
    var stmt = sqliteConnection.prepare("INSERT INTO numbers VALUES (?)");

    pgConnection.any('SELECT ...')
        .then(function(data) {
            for (var i = 0; i < data.length; i++) {
                mysqlConnection.query('SELECT ...?', [my_value], function (error, results, fields) {
                    if (error) console.log(error);
                    for (var j = 0; j < results.length; j++) {
                        stmt.run(results[j].x);
                    }
                    stmt.finalize();
                    sqliteConnection.run("COMMIT");

                    sqliteConnection.close();
                    mysqlConnection.end();
                    // pgConnection is a lazy connection, i.e. only the actual query methods acquire and release the connection
                });
            }
        })
        .catch(function(err) {
            console.log(err);
        });
});

The code actually seems to work, but is taking around 30 seconds to run. When I add console.logs into the code to see what takes up time, it seems as if all the database handling actually runs within less then a second, but the entire node server.js takes around 30 seconds.

Do I need to close something, or how can I debug what is taking up time?

Upvotes: 2

Views: 823

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

As per the Library de-initialization notes of pg-promise, and as shown in all examples...

In the end of processing, invoke pgPromise.end(), to shut down the connection pool, so the process can finish without any delay.

Upvotes: 2

Related Questions