How to save mysql query in a global array in Node.js

Question

I'm new to Node.js and Express. I understand that the functions are executed asynchronously, but I don't get how to use global variables. Here I connect to mysql database, retrieve the query results and render them on my test.html page. Then in the mysql.js file, I want to save those results in an array called entry, so that the other get() can use those same results. The only alternative I can think of with my limited understanding of Node.js is to just query the database again in the other get(). How can I use this global to save my query results from the anonymous function? Also what event and function do I call so that I can execute my connection.end() correctly?

//mysql.js
var express = require('./express');
var mysql = require('mysql');
var hbs = require('./hbs');
var app = express();

app.set('view engine', 'html');
app.engine('html', hbs.__express);

var connection = mysql.createConnection({
    host: '127.0.0.1',
    user: 'root',
    password: 'password',
    database: 'db',
    port: 3306
});

connection.connect();

entry=[];

app.get('/', function(req, res){
    connection.query(
    'SELECT * FROM table where ID < 10;',
    function(err, result, fields)
    {
        if(err) throw err;
        res.render('test',{title:"title", entries:result});
        entry = result;
    });
});

app.get('/article/:id', function(req, res) {
    res.render('article',{title:entry.title, blog:entry});
});

app.listen(3000);
//connection.end();

<h1>HTML Test</h1>
{{#each entries}}
    <p>
        <a href="/article/{{ID}}">{{Name}}</a><br/>
        Name: {{Name}}
    </p>
{{/each}}

Nate · Accepted Answer · 2014-01-03T19:12:09.653

The easy way is to run a query to retrieve each article when it’s requested. Your first route (for '/') looks fine. Just add a similar query for your second route:

connection.query('SELECT * FROM table WHERE ID=?', [req.params.id],
  function(err, rows, fields)
{
  if (err) { throw err; }
  if (!rows.length) { return res.send(404); }
  res.render('article', { title: rows[0].title, blog: rows[0] });
})

But if you really want to cache entries:

Use redis or memcached if you want real caching. E.g. if you’re concerned about being slashdotted or whatever it’s called now. This needs a Redis or Memcache daemon and is a bit more involved.
In your code, the SQL query doesn’t impose an ORDER. The only way to get an article with a particular ID is to loop over the global entry array looking at each one until you find the right one.
Adding an ORDER BY clause doesn’t help because you could still skip an ID (or later delete an entry with a particular ID). You still have to loop over the entire array to find the one you want.

If you are done using the database, you can call connection.end. But if your app runs “forever” and you plan to access the DB from time to time, there’s no need to call it. The connection will be killed if/when your app is killed. A cleaner way is to let the mysql module manage connections for you. See the documentation section “Pooling connections”.

How to save mysql query in a global array in Node.js

1 Answers1