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

Question

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?

maybe you put the code with console.log then - to see what runs fast and output accordingly please? — Vao Tsun
– Vao Tsun, Commented Feb 3, 2017 at 8:40
I did, and the console.log() after mysqlConnection.end() came out within a seconds, and then the rest of the app ran for around 29 seconds. Running --prof on the scripts show: 40.3% ___mac_get_pid. — Michael Nielsen
– Michael Nielsen, Commented Feb 3, 2017 at 9:04

vitaly-t · Accepted Answer · 2017-02-04 09:04:25Z

2

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.

answered Feb 4, 2017 at 9:04

vitaly-t

26.1k18 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related