Background

Using Postgres with Node.js has been one of my favorite experiences in professional software development. Working with the database has been, for the most part, a seamless experience. It's especially nice that Postgres supports native JSON (a topic for another post).

Querying with node-postgres

The node-postgres module is sufficient for most purposes except when injecting data into a query. We've found that it's error-prone to provide parameters via the $1, $2 syntax because you have to match up items by index in an array. The majority of our queries take no less than 4 parameters and some more than 10. Imagine keeping the order of items in the array in sync with the parameter placeholders in a query as your project evolves. For your consideration, here's how to execute a prepared statement with node-postgres.

client.query({  
   name: 'search_customers',
   text: `SELECT * 
      FROM customer
      WHERE first_name = $1 
         AND last_name = $2;`,
   values: ['Steve', 'Jobs'],
})

As you can see, there is no easy way to modify the query without shuffling in or out items of the values array. What if you wanted to add another parameter to this query? Logically, you would tack on another value to the end of the values array and place a $3 where it belongs in the query. However, what if that parameter goes somewhere in the SQL statement before all other values? This wouldn't cause an error, but it definitely makes my skin crawl thinking about parameter placeholders being out of order. For example consider the revised query where we've decided to place the address.state filter before the customer filters:

SELECT *  
FROM customer  
   INNER JOIN address ON address.customer_id = customer.customer_id
WHERE address.state = $3  
   AND customer.first_name = $1
   AND customer.last_name = $2;

Does it hurt your brain to see $3 BEFORE $1 and $2 like mine? I know it's ultimately inconsequential, but nevertheless it drives me insane.

Continuing on with this example, the next step is to add the new filter parameter to the end of the values array.

   ...
   values: ['Steve', 'Jobs', 'California'],
   ...

This process isn't too bad but over time it could become quite a burden as values are added and removed. Removing a value, for example, would require running through the entire query and decrementing every index AFTER the index you've removed. Oh, and value placeholders are 1-based (1, 2, 3, ...n), good luck not mixing that up occasionally! What was $2 again?

Note: this isn't a limitation of node-postgres. The index placeholder parameter format is used by Postgres.

Improving parameter input

It would be much more convenient to specify the parameters by name e.g., :first_name instead of $1. Assuming this functionality is available the above query can be improved:

client.query({  
   name: 'search_customers',
   text: `SELECT * 
      FROM customer
         INNER JOIN address ON address.customer_id =       customer.customer_id
      WHERE address.state = :state
         AND customer.first_name = :first_name
         AND customer.last_name = :last_name;`, 
   values: {
      first_name: 'Steve',
      last_name: 'Jobs',
      state: 'California',
   },
})

This is nice isn't it? Unfortunately, this isn't a feature of the node-postgres library.

Introducing TinyPg

TinyPg Project on GitHub

TinyPg makes it possible to use objects as the source for parameters in a query. For example:

db.query(`  
SELECT *  
FROM customer  
   INNER JOIN address ON address.customer_id = customer.customer_id
WHERE address.state = :state  
   AND customer.first_name = :first_name
   AND customer.last_name = :last_name;
`, {
   first_name: 'Steve',
   last_name: 'Jobs',
   state: 'California', 
})

SQL files over embedded strings

Now that we're past the mess of managing parameters by index the next step is organizing our SQL statements. It's kind of ugly to embed SQL in your JavaScript syntax. TinyPg allows for specifying a directory for which it will load all files with the .sql extension. The path to the file will be normalized into a key for which you can look up and execute the SQL as a prepared statement. For our projects we have hundreds of different SQL queries. It's worth noting that we don't subscribe to tools that generate SQL on your behalf. Many of our queries require use of Postgres features that no SQL generator can provide. Therefore, we don't bother trying to force these libraries to be smarter and instead stick to the language that's best suited for retrieving data from relational data stores: SQL.

Consider the following directory structure:

/app
   /db
      /address
         create.sql
      /customer
         create.sql
         fetch.sql
         search.sql

If you provide /app/db as a root directory to TinyPg it will load and parse all sql files beneath that directory. These files are keyed using the path to the file e.g. address.create or customer.fetch. Here's an example usage:

db.sql('customer.search', {  
   first_name: 'Steve',
   last_name: 'Jobs',
   state: 'California',
}) 

Ultimately TinyPg uses the node-postgres library the same way we started out this article. TinyPg provides additional benefit of increasing maintainability and decreasing surface area for bugs. Bugs are reduced by checking for the existence of required parameters when each query is executed. Ideally, when your queries are run in your test suite these errors will get you on the right track faster. TinyPg does not assume you mean to specify null if there's no key present in the parameter object, instead it will fail with an error message describing the missing parameter. I highly recommend using an object literal to specify parameters rather than a variable for reasons we'll get to later in this article.

Transaction support

Running transactions requires the use of the same Postgres client for all queries within the transaction. The library makes this easy to do whilst providing a uniform interface to subsequent callers. A call to .transaction provides a new instance of TinyPg that maintains the same client for every subsequent query against that instance (normally each query would request a client from a pool). Here's how to create a customer and associate an address in the same transaction.

db.transaction(transaction_db => { // BEGIN  
   return transaction_db.sql('customer.create', { // INSERT
      first_name: 'Steve',
      last_name: 'Jobs',
   })
   .then(result => {
      const customer = result.rows[0]

      return transaction_db.sql('address.create', { // INSERT
         customer_id: customer.customer_id,
         street: '123 W 10th St',
         city: 'Palo Alto',
         state: 'California',
         zip: 94301,
      })
   .then(() => customer.customer_id)
   })
}) // COMMIT
.then(customer_id => {
   return db.sql('customer.fetch', { // SELECT
      customer_id: customer_id,
   })
   .then(result => result.rows[0])
})

Whenever your promise succeeds it'll automatically execute COMMIT and in the event of failure it will execute ROLLBACK. Calling transaction on a Tiny instance that's already in a transaction will be a no-op. It is important that the lambda given to the transaction function return a promise. Otherwise COMMIT may be called at an unexpected time. Synchronous errors thrown in a transaction lambda will be caught and result in a ROLLBACK.

Here's the sequence of SQL statements that would be executed:

BEGIN  
INSERT INTO customer ...  
INSERT INTO address ...  
COMMIT  
SELECT * FROM customer ...  

Notice the select AFTER the transaction has been committed. This is very important in order to return data that's actually persisted in the database.

Events

Events are emitted at the beginning and end of every query. This has been helpful for us to diagnose slow running queries. Our practice is to create a single instance of TinyPg per process and attach handlers to the query and result events to log all database queries.

Sometimes you need to associate several database calls with some context e.g., a web request. TinyPg provides a way to create a brand new event emitter that can emit events separately from the global handlers. This functionality isn't thoroughly flushed out and may not fit all use cases but works great for us so far. Here's an example:

const db = new TinyPg(options)

function ApiRequestHandler(request, reply) {  
   const isolated_db = db.isolatedEmitter()

   isolated_db.events.on('query', context => {
      console.log(request.request_id, context.name)
   })

   return new UserService(isolated_db).list()
   .then(users => reply(users))
}

In the above example isolated_db is the same instance of TinyPg except with an overridden events property and dispose method to remove all listeners. The UserService can create other services and pass its reference to isolated_db to other services. In doing so, you can track all database queries executed as the result of every API request.

Static analysis with VS Code Plugin and TSLint Rules

If you're using TypeScript in your project (which I highly recommend) you can get an extra level of validation and editor integration by using the TinyPg VS Code plugin. This plugin can statically analyze (why I suggest using object literals) your code to ensure you've referenced sql files that exist and have provided all required parameters. This plugin has significantly sped up our development process because we no longer need to compile and run our tests before we encounter and fix silly errors. The VS Code plugin also supports jumping to the sql file directly and previewing the source on hover. If you're interested in using TinyPg please consider using it along with TypeScript to take advantage of this nifty plugin.

TinyPg VS Code Plugin

TinyPg TSLint Rules

See an example project

Example Project on GitHub