Connect middleware to manage Postgres connections.
_______ _______ _ _ _______ _______ _________( ____ \( ___ )( ( /|( ( /|( ____ \( ____ \\__ __/| ( \/| ( ) || \ ( || \ ( || ( \/| ( \/ ) (| | | | | || \ | || \ | || (__ | | | || | | | | || (\ \) || (\ \) || __) | | | || | | | | || | \ || | \ || ( | | | || (____/\| (___) || ) \ || ) \ || (____/\| (____/\ | |(_______/(_______)|/ )_)|/ )_)(_______/(_______/ )_(_______ _______ _______ _ _________ _______ _ _________( ____ )( ____ \( ____ \( \ \__ __/( ____ \( ( /|\__ __/| ( )|| ( \/| ( \/| ( ) ( | ( \/| \ ( | ) (_____ | (____)|| | | | | | | | | (__ | \ | | | |(_____)| _____)| | ____ | | | | | | | __) | (\ \) | | || ( | | \_ )| | | | | | | ( | | \ | | || ) | (___) || (____/\| (____/\___) (___| (____/\| ) \ | | ||/ (_______)(_______/(_______/\_______/(_______/|/ )_) )_(
This module is aimed at taking the pain away from managing your Postgres connections from within a connect/express
app. Too many times has
res.redirect() been used and left the DB connection hanging.
npm install connect-pgclient
connect-pgclient helps you manage (and free) your Postgres database connections so you don't have to. It
automatically gets a client from
node-pg at the start of the request and calls
done() at the end of the
request to automatically return the client back to pg's pool. This way you'll never lose any clients by accidentally
var pgclient = require'connect-pgclient';var dbMiddleware = pgclientconfig :database : 'dbname'user : 'me'host : 'dbserver.internal';appget'/'dbMiddleware// here you can use req.db.client to perform queriesnext;ressend'Ok';// req.db.done is automatically called to release the client;
If you are trying to do your Pg clients manually, then there are various cases which you might forget about where you
done(). Here is an example when you have a client but call
res.redirect() and forget to release
appget'/'connectToDbselectSomethingFromDbif somethingWasntFoundreturn resredirect'/';// bang, you just lost a DB clientnext;// must remember to release here, otherwise you'll lose another DB clientdisconnectFromDbressend'Ok';;
connect-pgclient you'll be able to do this:
appget'/'// middleware you created using connect-pgclientdbMiddlewareselectSomethingFromDbif somethingWasntFoundreturn resredirect'/';// client is automatically releasednext;ressend'Ok';// client is automatically released;
Once you have setup and called your
connect-pgclient middleware, you Postgres client is available on the
req object as follows:
// the node-pg clientreqdbclient// the done function which node-pg needs to return the client to the poolreqdbdone// boolean to show us whether we are in the middle of a transactionreqdbtransaction
In general, you should only ever use the
req.db.client property of
- config - the database connection params as defined in node-postgres
- transaction - (default: false) States whether to BEGIN and COMMIT a transaction for you.
- log - (default: no-op) a function to call with log messages to help with debugging (usually dev only)
You may or may not want to do this, but it shows a good example for starters:
var pgclient = require'connect-pgclient';appusepgclientconfig :database : 'dbname'user : 'me'host : 'dbserver.internal';
If you would like
connect-pgclient to BEGIN and COMMIT a transaction for you, then just pass the
transaction param as
true into the options.
var pgclient = require'connect-pgclient';appusepgclientconfig :database : 'dbname'user : 'me'host : 'dbserver.internal'transaction : true;
The database clients are released back to
node-pg even if the request ends in
res.render() or even in error
// Postgres middleware which gets a Pg client and releases it after// the request has been fulfilled.var connectToDb = pgclientconfig :database : 'dbname'user : 'me'host : 'dbserver.internal';// Postgres middleware which gets a Pg client, starts a transaction// and commits and releases it after the request has been fulfilled.var connectToDbWithTransaction = pgclientconfig :database : 'dbname'user : 'me'host : 'dbserver.internal'transaction : true;// a route which always succeeds - Pg client is released okappget'/'connectToDbressend'My Homepage - Under Construction!';;// a route which redirects 50% of the time - Pg client is released okappget'/random'connectToDbif Mathrandom < 0.5// even though we're not calling node-pg's done(), connect-pgclient does it for us even hereresredirect'/';elseressend'My Homepage - Under Construction!';;// a route which dies 50% of the time - Pg client is released okappget'/roulette'connectToDbWithTransaction// this is how you get your freshly minted Postgres clientreqdbclient"SELECT now()" next;if Mathrandom < 0.5// even though we're not calling node-pg's done(), connect-pgclient does it for us even herenext"Die die die!";elseressend'My Homepage - Under Construction!';;
Using brianc's excellent pg library, we connect to the
database and store both the
client and the
done function onto the
req so that we can use the client
in our routes, but also automatically call
done when the request has finished.
connect-pgclient works much like connect's
session middleware in that it wraps
res.end() so that we
can get control both before and after the request has been fulfilled, which allows us to give the client back to pg's
pool automatically no matter what happened during the request.
When you use
connect-pgclient to give you a client and automatically start a transaction, if the request ends up
in error, the transaction still has
COMMIT performed. In this error case, I think
ROLLBACK should be called
instead but I'm not yet sure how to detect if the request is in the error state.
(Note: remember that this is after the request has been fulfilled, which is after any error middleware has been run.)