How do I release a pool connection from a model?

Question

I want to read a big JSON file (this to be precise), iterate over it, check which are new entries and save or update as necessary.

Doing this with sails, and sails mysql I found out that the whole process slows down the first time and if I try to run it a second time it dies at some point. My mysql server seems to handle that for around 24k requests and dies.

Here is the code with which I made it worked, but it completely ignores sails, or sails-mysql and I use mysql directly to achieve this.

This is the working code:

var path = require('path')
var Promise = require('bluebird')
var fs = Promise.promisifyAll(require('fs'))
var rootPath = process.cwd()
var filePath = path.join(rootPath, 'assets/systems.json')
var mysql = require("mysql");
Promise.promisifyAll(mysql);
Promise.promisifyAll(require("mysql/lib/Connection").prototype);
Promise.promisifyAll(require("mysql/lib/Pool").prototype);
var pool  = mysql.createPool({
  connectionLimit: 10,
  host: 'localhost',
  user: 'user',
  password: 'pass',
  database: 'db'
});

function getSqlConnection() {
  return pool.getConnectionAsync().disposer(function(connection) {
    connection.release();
  });
}

fs.readFileAsync(filePath, 'utf8').then(JSON.parse).then(function(systems) {
  total = systems.length
  return systems
})
.map(function(item, index, value) {
  Promise.using(getSqlConnection(), function (conn) {
    return conn.queryAsync('SELECT * FROM system WHERE name = "' + item.name + '"')
  })
  .then(function(have_system) {
    // do something with the info
    return the_result_of_doing_something
  })
  .caught(function(err) {
    console.log(err)
  })
}, {concurency: 5})
.caught(SyntaxError, function(e) {
  console.log("Invalid JSON in file " + e.fileName + ": " + e.message);
})
.lastly(function() {
  broadcast(["All DONE!!!", updated_systems, new_systems, current])
})

How can I achieve this without requiring mysql again and creating my own connection? So this:

function getSqlConnection() {
  return pool.getConnectionAsync().disposer(function(connection) {
    connection.release();
  });
}

Promise.using(getSqlConnection(), function (conn) {
  return conn.queryAsync('SELECT * FROM system WHERE name = "' + item.name + '"')
})

Would become something like:

function getSailsConnection() {
  return sails.pool.getConnectionAsync().disposer(function(connection) {
    connection.release();
  });
}

Promise.using(getSailsConnection(), function (conn) {
  return conn.System.findOne({name: item.name})
})

If I can properly release I don't even have to use Promise.using() since the waterline model can use promises.

System.findOne({name: item.name}).then(function(have_system) {
  // do something
  System.releaseConection() // or some other API call to achieve this
})
.caught(function(err) {
  System.releaseConection() // or some other API call to achieve this
})

PS: I tried to add a sails-mysql tag too but I'm not allowed.

Why not just overwrite the entire file every time? seems faster than checking for changed entries. — Alex Hill, Jan 06 '15 at 23:41
@AlexHill I don't want to write the file. I want to read from it and import it into the database. Then when I do have entries update the database instead of wiping it and import again. Also users could modify entries in the database which I want to compare with the file and submit pull requests to that repository with the added or corrected information. — tic2000, Jan 06 '15 at 23:45

score 1 · Answer 1 · answered Jan 07 '15 at 01:49

1

You are not returning any promise from .map callback so not only will the promise fulfill prematurely, concurrency will not do anything.

You could also use a single connection for the whole thing:

using(getSailsConnection(),
      fs.readFileAsync(filePath, 'utf8').then(JSON.parse), function(conn, systems) {
    return systems.map(function(item) {
        return conn.System.findOne({name: item.name});
          .then(function() {

          })
    }, {concurrency: 5});
})
.lastly(function() {
  broadcast(["All DONE!!!", updated_systems, new_systems, current])
})

answered Jan 07 '15 at 01:49

Esailija

138,174
23
272
326

Isn't the return in the last then what is returned from the whole chain inside map? – tic2000 Jan 07 '15 at 02:06
@tic2000 return statement in javascript doesn't magically return something for other functions, only the function the return statement is in – Esailija Jan 07 '15 at 02:08
OK. I added a return. But unfortunately this doesn't answer my main question. How can I achieve this in sails, cause `conn.System.findOne({name: item.name})` doesn't work and I still have to use `conn.queryAsync('SELECT * FROM system WHERE name = "' + item.name + '"')` – tic2000 Jan 07 '15 at 03:06
@tic2000 you need to implement getSailsConnection – Esailija Jan 07 '15 at 03:52
But how? That was my question. My getSailsConnection was pseudo code. I don't know how to hook in the pool sails-mysql already created and get a connection from there. – tic2000 Jan 07 '15 at 04:50
@tic2000 does sails mysql have documentation? – Esailija Jan 07 '15 at 05:51
@tic2000 I think you can just run `System.findOne` without having to create connection yourself – Esailija Jan 07 '15 at 12:34

How do I release a pool connection from a model?

1 Answers1