Whenever I am writing a project & using node-postgres I like to create a file within it and make all interactions with the database go through this file. This serves a few purposes:
- Allows my project to adjust to any changes to the node-postgres API without having to trace down all the places I directly use node-postgres in my application.
- Allows me to have a single place to put logging and diagnostics around my database.
- Allows me to make custom extensions to my database access code & share it throughout the project.
- Allows a single place to bootstrap & configure the database.
example
The location doesn’t really matter - I’ve found it usually ends up being somewhat app specific and in line with whatever folder structure conventions you’re using. For this example I’ll use an express app structured like so:
Typically I’ll start out my file like so:
That’s it. But now everywhere else in my application instead of requiring directly, I’ll require this file. Here’s an example of a route within :
Imagine we have lots of routes scattered throughout many files under our directory. We now want to go back and log every single query that’s executed, how long it took, and the number of rows it returned. If we had required node-postgres directly in every route file we’d have to go edit every single route - that would take forever & be really error prone! But thankfully we put our data access into . Let’s go add some logging:
That was pretty quick! And now all of our queries everywhere in our application are being logged.
note: I didn’t log the query parameters. Depending on your application you might be storing encrypted passwords or other sensitive information in your database. If you log your query parameters you might accidentally log sensitive information. Every app is different though so do what suits you best!
Now what if we need to check out a client from the pool to run several queries in a row in a transaction? We can add another method to our file when we need to do this:
Okay. Great - the simplest thing that could possibly work. It seems like one of our routes that checks out a client to run a transaction is forgetting to call in some situation! Oh no! We are leaking a client & have hundreds of these routes to go audit. Good thing we have all our client access going through this single file. Lets add some deeper diagnostic information here to help us track down where the client leak is happening.
That should hopefully give us enough diagnostic information to track down any leaks.