brianc · December 22, 2023 00:47 · Oct 22, 2014 · Oct 22, 2014 · Oct 22, 2014 · Oct 22, 2014
diff --git a/gistfile1.md b/gistfile1.md
@@ -16,7 +16,9 @@ So if you have N servers running M processes with the cluster module make sure (
 
 ### Code Organization:
 
-I usually make a single file in the application with a 'query' method exported.  This method uses the pool, checks out a client, runs the query, returns the client to the pool, and then calls the callback with the results.  I do this because it's much easier to audit SQL or put logging in one file than 3 months into a project going "hmm...can we log every sql statement?" but you've already sprinkled `pg.connect` calls throughout 50 route handlers and 20 model files across the application.  usually the file looks something like this:
+I usually make a single file in the application with a 'query' method exported.  This method uses the pool, checks out a client, runs the query, returns the client to the pool, and then calls the callback with the results.  I do this because it's much easier to audit SQL or put logging in one file than 3 months into a project going "hmm...can we log every sql statement?" but you've already sprinkled `pg.connect` calls throughout 50 route handlers and 20 model files across the application. Also, it's easy to forget to _release_ your client when you're finished with it.  I've helped on apps which had a client leak, and just like a memory leak, the app stops responding some time in the future as the pool gets exhausted.  Hard to track down if you're checking out clients throughout a 100+ file code base.  
+
+usually the file looks something like this:
 
 ```js
 

diff --git a/gistfile1.md b/gistfile1.md
@@ -1,5 +1,9 @@
 Some thoughts on using node-postgres in a web application
 
+
+
+This is the approach I've been using for the past year or so.  I'm sure I'll change and it will change as I grow & am exposed to more ideas, but it's worked alright for me so far.
+
 ### Pooling:
 
 I would definitely use a single pool of clients throughout the application. node-postgres ships with a pool implementation that has always met my needs, but it's also fine to just use the `require('pg').Client` prototype and implement your own pool if you know what you're doing & have some custom requirements on the pool.  
@@ -99,8 +103,6 @@ router.post('/user', function(req, res, next) {
 
 ```
 
-This is just where I'm at right now in my journey.  I'm sure I'll change my approach as I grow & am exposed to more ideas, but it's worked alright for me so far.
-
 ### Transactions:
 
 Funnily enough the amount of times I actually use multi-query transactions is pretty low, and so I haven't had the need to look at modules that help out with this or work on any of my own.  In the handful of places I've done a transaction I've just rolled it out by hand...it's quite a bit of code and you absolutely __cannot__ use the aforementioned `./lib/query` helper because you __must use the same client for all steps of the transaction__.  This is one of the places where I still reference `require('pg').connect` directly.  This is (unfortunately) usually how it looks:

diff --git a/gistfile1.md b/gistfile1.md
@@ -156,7 +156,6 @@ var transferFunds = function(fromId, toId, amount, cb) {
 
 That's actually pretty horrible looking & there's probably a better way - I just haven't needed to find one yet!
 
-I hope this helps...
 
 
 ### Testing
@@ -202,4 +201,7 @@ describe('test with database access', function() {
     }));
   });
 });
-```
+```
+
+
+I banged this out quickly so it has some holes in it for sure, but I hope this helps...
diff --git a/gistfile1.md b/gistfile1.md
@@ -156,4 +156,50 @@ var transferFunds = function(fromId, toId, amount, cb) {
 
 That's actually pretty horrible looking & there's probably a better way - I just haven't needed to find one yet!
 
-I hope this helps...
+I hope this helps...
+
+
+### Testing
+
+I use mocha for testing usually, and I have a pretty simple way to integration test routes, library functions, or whatever that hit the database.  This assumes everything is using the query method in `./lib/query` we talked about..
+
+```js
+
+//some-test.js
+
+//set the pool size to ONE in tests
+//this means we will always get the same client...
+//this lets us use a BEGIN/ROLLBACK in our tests so the database
+//is never left in a bad state
+
+require('pg').defaults.poolSize = 1;
+var query = require('../lib/query');
+var ok = require('okay');
+var assert = require('assert');
+
+describe('test with database access', function() {
+  beforeEach(function(done) {
+    query('BEGIN', done);
+  });
+  
+  afterEach(function(done) {
+    query('ROLLBACK', done);
+  });
+  
+  it('does something awesome but does not disturb the database', function(done) {
+    query('INSERT INTO "user"(email) VALUES($1)', ['[email protected]'], ok(done, function(rows) {
+      query('SELECT COUNT(*) as user_count FROM "user"', ok(done, function(rows) {
+        assert.equal(rows[0].user_count, 1);
+        done();
+      }));
+    }));
+  });
+  
+  it('has clean database state', function(done) {
+    query('SELECT COUNT(*) as user_count FROM "user"', ok(done, function(rows) {
+      assert.equal(rows[0].user_count, 1);
+      done();
+    }));
+  });
+});
+```
diff --git a/gistfile1.md b/gistfile1.md
@@ -0,0 +1,159 @@
+Some thoughts on using node-postgres in a web application
+
+### Pooling:
+
+I would definitely use a single pool of clients throughout the application. node-postgres ships with a pool implementation that has always met my needs, but it's also fine to just use the `require('pg').Client` prototype and implement your own pool if you know what you're doing & have some custom requirements on the pool.  
+
+The drawback to using a pool for each piece of middleware or using multiple pools in your application is you need to control how many open clients you have connected to the backend, it's more code to maintain, and likely wont improve performance over using a single pool.  If you find requests often waiting on available clients from the pool you can increase the size of the built in pool with `pg.defaults.poolSize = 100` or something.  I've set the default at 20 which is a sane default I think.  If you have long running queries during web requests you probably have bigger problems than increasing your pool size is going to solve. :wink: 
+
+Each client connection takes a small but non-trivial amount of ram on the PostgreSQL server backend. Once we opened 1,500 clients to our server & the server went unresponsive - all the resources were consumed servicing clients & queries wouldn't run.  I've heard a good soft limit to stick to is 1,000. 
+
+So if you have N servers running M processes with the cluster module make sure (pg.defaults.poolSize * N * M) < 1000. [here is a more in-depth guide about connection count](https://blog.heroku.com/archives/2013/11/22/connection_limit_guidance)
+
+### Code Organization:
+
+I usually make a single file in the application with a 'query' method exported.  This method uses the pool, checks out a client, runs the query, returns the client to the pool, and then calls the callback with the results.  I do this because it's much easier to audit SQL or put logging in one file than 3 months into a project going "hmm...can we log every sql statement?" but you've already sprinkled `pg.connect` calls throughout 50 route handlers and 20 model files across the application.  usually the file looks something like this:
+
+```js
+
+//./lib/query.js
+
+//I have omitted logging, but my function is usually sprinkled with logs
+module.exports = function(queryText, queryValues, cb) {
+   pg.connect(function(err, client, release) {
+     //connection failure
+     //we don't need to release anything
+     //because we were never handed a client in this case
+     if(err) return cb(err);
+
+     client.query(queryText, queryValues, function(err, result) {
+       //always release the client
+       release();
+
+       if(err) return cb(err);
+
+       //i like to return the rows directly since 99% of the time
+       //I don't care about the other properties on the result object
+       return cb(null, result.rows, result);
+     });
+   });
+};
+```
+
+This pattern for me is so common I created [pg-query](https://github.com/brianc/node-pg-query) to basically abstract it into a module I drop in on most projects.  I still generally create a `./lib/query` file that exports the `pg-query` module anyway in case I want to replace the module with a bespoke implementation.
+
+I also like to keep longer, non-dynamic queries in separate `.sql` files because it's hard to do multi-line strings in JavaScript & I like to separate them out to keep the code a bit more readable. I know in web-apps a lot of times you need to dynamically build queries and for that I still build them dynamically, but I try for more static queries, and when I have them, I like to use [querybox](https://github.com/brianc/node-querybox).  I usually have a folder at the application root level with sql files in it, and an `index.js` file that looks like this:
+
+```js
+// ./sql/index.js
+
+//our custom 'query' helper
+var query = require('../lib/query');
+module.exports = require('querybox')(__dirname, query);
+```
+
+So we end up with a tree structure that looks like this...
+
+```
+| app.js
+| routes/
+|-- data.js
+|-- photos.js
+| lib/
+|-- query.js
+| sql/
+|-- index.js
+|-- some-big-query.sql
+|-- get-user-by-email.sql
+```
+
+So a very simplified version of a route handler with no input validation and no logging would look something like this:
+
+```js
+// routes/data.js
+var sql = require('../sql');
+var query = require('../lib/query');
+var router = new require('express').Router();
+
+module.exports = router;
+
+router.get('/user/:email', function(req, res, next) {
+  var email = req.params.email;
+  sql.run('get-user-by-email', [email], function(err, rows) {
+    if(err) return next(err);
+    if(!rows.length) return next(404);
+
+    return res.json(rows[0]);
+  });
+});
+
+router.post('/user', function(req, res, next) {
+  var queryText = 'INSERT INTO "user"(email) VALUES ($1')';
+  var queryValues = [req.body.email]
+  query(queryText, queryValues, function(err, rows, res) {
+    if(err) return next(err);
+    
+    res.status(204).end();
+  });
+});
+
+```
+
+This is just where I'm at right now in my journey.  I'm sure I'll change my approach as I grow & am exposed to more ideas, but it's worked alright for me so far.
+
+### Transactions:
+
+Funnily enough the amount of times I actually use multi-query transactions is pretty low, and so I haven't had the need to look at modules that help out with this or work on any of my own.  In the handful of places I've done a transaction I've just rolled it out by hand...it's quite a bit of code and you absolutely __cannot__ use the aforementioned `./lib/query` helper because you __must use the same client for all steps of the transaction__.  This is one of the places where I still reference `require('pg').connect` directly.  This is (unfortunately) usually how it looks:
+
+```js
+
+var transferFunds = function(fromId, toId, amount, cb) {
+
+  pg.connect(function(err, client, release) {
+    if(err) return cb(err);
+    
+    var curryError = function(dispose, next) {
+      return function(err, res) {
+        if(err) {
+          //if we pass 'truthy' to release it will
+          //close & throw away the client & replace it with a new one
+          //in the pool - i do this for 'critical' query errors
+          release(dispose);
+          return cb(err);
+        }
+        return next(res.rows, res);
+      }
+    }
+    
+    client.query('BEGIN', curryError(true, function() {
+    
+      var queryText = 'SELECT sum(money) as "availableFunds" FROM account WHERE id = $1'
+      client.query(queryText, [fromId], curryError(function(rows) {
+        if(rows[0].availableFunds < amount) {
+          release();
+          return cb(new Error('not enough money to transfer'));
+        }
+        
+        var updateFunds = 'INSERT INTO account(money) VALUES ($2) WHERE id = $1';
+        
+        //debit & credit accounts
+        client.query(updateFunds, [fromId, -amount], curryError(true, function() {
+        
+          client.query(updateFunds, [toId, amount], curryError(true, function() {
+            
+            //finally commit our transaction
+            client.query('COMMIT', curryError(true, function() {
+              cb(null);
+            }));
+          }));
+        }));
+      }));
+    }));
+  }); 
+  
+}
+```
+
+That's actually pretty horrible looking & there's probably a better way - I just haven't needed to find one yet!
+
+I hope this helps...
No results found