What is the best strategy for using node-postgres in multiuser CRUD application

[sairum]

Sorry if this is not the right place to ask this question, but after reading a lot of documentation on using 'pg' module in express (or any other RDBMS, for that matter), I feel that most examples are tailored to a single DB user, even though they appear to be multiuser environments (like many blog or todo applications, where each 'user' actually uses an hidden login/password pair to commit data to the DB). The documentation of node-postgres is also not clear about the issue I report below...

Let me explain. I'm currently refactoring a LAMP (Apache+PHP+Postgres) CRUD application into a Node+Express+node-postgres one. Most of the business logic is made within the Postgres server (user permissions, stored procedures, materialized views, etc). This application is a sort of a RESTful API. By "sort of", I mean an API that is not totally stateless, as it uses sessions to keep users names/passwords in the server during the whole duration of the session, which are then used in each access to the DB (typical PHP pattern). This works well in a multiuser environment, and the postgres server is responsible for maintaining data integrity, even when conflicting commands are issued by different users simultaneously!

My question is: what is the best strategy to implement a true multiuser CRUD application in node+express+node-postgres, using all the facilities offered by the PostgreSQL engine, like user credentials, permissions, etc?

a) use a global pg instance and change the connectionString in pg.connect (with user:password stored in session) for each request (API endpoint)

b) use a new pgClient for each request (with user:password stored in session) and avoid pg connection pooling altogether (eventually using connection pooling in the server, eg. pgbouncer or pgpool)

c) use a local pg instance in a private thread (app clusters) for each authorized user, maintaining pg's connection pooling

I feel that the last option is the least feasible, as each thread (run on a dedicated CPU) should use a single server, limiting the number of users logged to the number of CPUs. But what about the other two? I've seen a few posts demonstrating both patterns, but they are quite old and maybe not implementing the best practices of node and pg.

Thanks in advance

Antonio

[zacronos]

Thinking about the options you've presented, I think I would suggest going with a). Here is my reasoning:

With option a), you get a separate connection pool in the webserver for each user. You have the benefit that connections from the webserver to the database are maintained, which is good for overhead. If you don't expect each user to make queries in parallel with itself, then you can keep the pool size small (1 or 2), since each user will get their own pool. If you do expect each user to make queries in parallel, and/or if you will have multiple webservers running in a cluster, you might get some additional benefit out of running pgbouncer on the database server.

With option b), you'll have a new connection from the webserver to the database (or to pgbouncer or to pgpool) made each time a query happens. If you use pgbouncer or pgpool, you're still going to have a separate connection pool per user, but it will be maintained between the pooler and the database -- the webserver will need to make a new connection for each query, which is still extra network overheard. This might not be that bad if you expect each user to make queries 1 at a time with long waits in between, and you don't mind the extra delay/overhead of establishing a connection each time (which would be less if you use pgbouncer or pgpool, because then at least the db itself doesn't have to deal with the new connections). It has the advantage of not maintaining idle connections from the webserver, but unless you have a very large number of users (200+), and very sparse usage in time, I don't think this will matter.

With option c), I honestly don't see what you would gain over option a). You still have a separate connection pool for each user, but you also have a separate thread running for each user, and I'm not sure why you'd want that -- it would certainly increase memory usage on your webserver, and doesn't seem like it would scale well past a very small number of users (depending on your server specs and app memory needs). In addition, I'm just not sure it would work. When a new request comes in to the webserver, how do you ensure it gets serviced by the private thread appropriate to that user? This feels like a more complicated, higher-overhead approach that would wind up with no advantages over a). Is there something I'm missing?

[sairum]

Thanks a lot for such detailed response. From what I have seen in the examples and documentation spread through the net, I was also more inclined to adopt option a). However, I do not understand exactly how connection pooling is done in node-postgres (I still haven't seen the code!)

Connection pooling makes a lot of sense when there is a single user connected. All clients in the pool will share the same credentials (user:password). But if different users are accessing the DB simultaneously, how is pooling done. From your post, it seems that each user gets a separate connection pooling ("you get a separate connection pool in the webserver for each user"). But how is that if only one instance of 'pg' is created per application? Am I missing something?

Consider the code below (highly simplified).

var express = require('express'),
  bodyParser = require('body-parser'),
  cookieParser = require('cookie-parser'),
  session = require('express-session'),
  pg = require('pg');

var app = express();

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(session({
  secret: '12345!', 
  resave: true, 
  saveUninitialized: true
}));

// Middleware to check credentials
app.use(function(req, res, next){
  if (req.session.loggedin) {
    next();
  }
  else {  // if someone tries to login
    if (req.body.user&&req.body.password) {
      var user = req.body.user;
      var password = req.body.password;
      // sanity checking here
      req.session.loggedin = true;
      req.session.user = user;
      req.session.password = password;
      console.log('User ' + req.session.user + ' just logged in');
      next();
    }
    else {
      res.send(
        '<!DOCTYPE html><html><body>'+
        '<form method="POST">'+
        'User    : <input type="text" name="user"><br>'+
        'Password: <input type="password" name="password"><br>'+
        '<input type="submit" value="Submit"></form>'+
        '</body></html>'
       );
    }
  }  
});

// A GET route
app.get('/', function(req, res){
  res.send(
    '<!DOCTYPE html><html><body>'+
    '<form method="POST">'+
    'SQL QUERY    : <input type="text" name="query"><br>'+
    '<input type="submit" value="Submit"></form>'+
    '</body></html>'
  );  
});

// A POST route
app.post('/', function(req, res){
  if (req.session.loggedin) {  
    if (req.body.query) {
      var conString = "postgres://"+req.session.user+":"+req.session.password+"@localhost/dbase";
      pg.connect(conString, function(err, client, done) {
        if(err) {
          res.send('error fetching client from pool', err);
        }
        client.query(req.body.query, function(err, result) {
          done();
          if(err) {
            res.send('error running query', err);
          }
          res.send(result.rows);
        });
      });  

    }
    else {
      res.send(
        '<!DOCTYPE html><html><body>'+
        '<form method="POST">'+
        'SQL QUERY    : <input type="text" name="query"><br>'+
        '<input type="submit" value="Submit"></form>'+
        '</body></html>'
      );
    }  
  } else {
    res.send('Access denied!'); 
  }
});

// If accessed, clear session
app.get('/logout', function(req, res){
  req.session.destroy(function(err) {
    console.log(err);
  })
  res.redirect('/');
});

app.listen(3000);

This code works perfectly with my database by simply pointing to localhost:3000, providing a password (login), and the issuing any SQL query on any table (obviously constrained by postgresql permissions). Note that pg is declared globally, and _pg.connect() _is only used in a POST route, but the connection string is recomputed every time based on the session variables (user and password). Does the connection pooling mechanism in pg recognize which pooled clients should be used based on that?

PS: The application I'm developing will be tailored to manage biological samples from which we extract DNA. A few people (5-6) are using the Apache+PHP version right now, without a single failure in five years! Yet, I plan to adopt its structure, porting it to node+express, and then develop another more comprehensive management application for biological collections. The latter may be used by 10 to 50 users... still not much for postgres, which by default accepts 200 connections if I remember it right.

[zacronos]

But if different users are accessing the DB simultaneously, how is pooling done. From your post, it seems that each user gets a separate connection pooling [...]. But how is that if only one instance of 'pg' is created per application?

[...] the connection string is recomputed every time based on the session variables (user and password). Does the connection pooling mechanism in _pg recognize which pooled clients should be used based on that?

You are correct -- pg will create 1 pool for each unique connection string. From the pg docs:

Note: The first parameter passed to connect, either a string or config object (or nothing), currently functions as the key used in pooling clients; therefore, using two different connection strings will result in two separate pools being created.

Based on the extra details of your app, I would use method a), and try using a pool size of 1 or maybe 2, without using pgbouncer or pgpool.

[sairum]

Thanks a lot!

That explains everything! I've RTFM a dozen times and cannot understand how I have missed that piece of text! This post just made my day!
Sincerely yours

Antonio