This small tutorial shows how to connect and query Postgres using Node.

For this project we assume you have a Mac or Linux machine, know git, know node.js, and some SQL.

Contents

Setup Project

Clone Example Project

git clone git@github.com:wlaurance/node-postgresql-example.git

The example code is available at the Github repo above.

Install PostgreSQL

If you need PostgreSQL, I recommend Postgres.app for mac. I have a detailed post on how to install PostgreSQL for Mac for Node apps here.

Step 1 Create and Populate Database

We are using an example database from the PostgreSQL wiki. It is the database of the fictional thriving bookstore Booktown.

Original Link

cd node-postgresql-example #if not already in directory
psql < booktown.sql

Running this command will read the booktown.sql file. It contains commands PostgreSQL knows. The commands make the database, tables, and sample data for booktown.

Step 2 Connect to Database

npm install #if you are following along
#Installs the 'pg' and 'underscore' node module
//First we 'require' the 'pg' module
var pg = require('pg');
//Next we define a connection string. The string contains the
//connection protocol, username, password, host, post, and database name.

//A localhost PostgreSQL database's connection string is simple.
var connectionString = 'postgres://localhost/booktown';

//Step 2

//We access a PostgreSQL client

//We use the 'pg' module's recommended client pooling API
//We pass the connect function the database connection string, and a callback function
//'onConnect'. We define that function.
pg.connect(connectionString, onConnect);

function onConnect(err, client, done) {
  //Err - This means something went wrong connecting to the database.
  if (err) {
    console.error(err);
    process.exit(1);
  }

  //For now let's end client
  client.end();
}

Step 3 Build a reusable client query API

We build our API into functional units that handle one concern. Our concerns are connecting, querying, handling errors, and handling results.

For this step let's work on one collection, booktown 'transactions'.


//Step 3
//An API for selecting all or some members of a collection.

var _ = require('underscore');
//Now we'll use the _.partial function to make a utility function called connectWithConnectionString.
var connectWithConnectionString =  _.bind(_.partial(pg.connect, connectionString), pg);
//connectWithConnectionString is still using pg.connect underneath. It automatically applies the
//connection string each time we call connectWithConnectionString. So now the API is
//connectWithConnectionString(function(err, client, done) { /*your code here*/ })

//OK Cool! So now when we want a PostgreSQL client we worry only about the callback
//function. Can we do better? Yes, let's handle the connection 'err' object the same for
//every callback

function buildSelectQuery(tableName) {
  return ['select * from', tableName].join(' ');
}

function buildQueryClient(query) {
  return function(onQueryReturn) {
    connectWithConnectionString(function(err, client, done) {
      if (err) {
        return onQueryReturn(new Error(['Database connection failed with error', err.toString()].join(' ')));
      } else {
        client.query(query, function(err, results) {
          done(err);
          onQueryReturn(err, results);
        });
      }
    });
  }
}

//Selects all of the supplied tableName
function selectAll(tableName) {
  return function(onSelectReturn) {
    var sql = buildSelectQuery(tableName);
    var queryClient = buildQueryClient(sql);
    queryClient(function(err, tableValues) {
      if (err) {
        return onSelectReturn(new Error(['Select all failed on', tableName, 'with error', err.toString()].join(' ')));
      } else {
        return onSelectReturn(null, tableValues);
      }
    });
  }
}

//Utility function to handle errors in callback functions.
var errorCheck = function(cb) {
  return function(err, result) {
    if (err) {
      console.error(err);
      throw err;
    } else {
      cb(result);
    }
  }
}

//Handles callback errors using `errorCheck` and printRows with
//optional text.
var printRows = function(text) {
  return errorCheck(function(results) {
    console.log(results.rows);
    if (text) console.log(text);
  });
}

var selectAllShipments = selectAll('shipments');
selectAllShipments(printRows());

Step 4 Creating new APIs from Step 3

We did a lot of functional programming in step 3. It was a lot of work just to query all the transactions.

In step 4, I want to show your hard work paying off. See now, how simple it is to select all books and authors.

//Step 4 - Building on this API
//Now when you want to select all rows from other collections you can build
//a function using selectAll.
var selectAllBooks = selectAll('books')
selectAllBooks(printRows())
var selectAllAuthors = selectAll('authors')
selectAllAuthors(printRows());
//You get the idea. We use the selectAll function generating function to build
//named functions. On objects, these functions become methods.

//Author Function
function AuthorCtrl() {

}
AuthorCtrl.prototype.selectAll = selectAllAuthors;
//Try using the AuthorCtrl.selectAllMethod
var authorCtrl = new AuthorCtrl();
authorCtrl.selectAll(printRows('Im from the Author Controller'));

Step 5 Building a select all with limit API

You might say yes, we can build select all APIs but how do I build insert, delete, where, and joins?

Glad you asked. By building functions that handle one concern we can reuse the code. Let's first add a select with limit API.

//Step 5 - Building a limit clause API
//If you take a look at our 'functional' functions and function returning
//functions, you'll notice that it's specific to select all a particular
//table. Let's build our functional API a little different now.

//In this API we want to build a Query that returns at most N books
var selectAtMostNBooks = buildDynamicQuery([
  'select * from books',
  'limit $1'
]);

//Now we call this function with the limit parameter.
var selectAtMost5Books = selectAtMostNBooks(5);
selectAtMost5Books(printRows('Select at most 5 books'));


function buildDynamicQuery(statements) {
  //We build a function that accepts SQL statements and returns
  //a function that accepts query parameters.
  return function () {
    var parameters = _.toArray(arguments)
    return function (onQueryReturn) {
      // It returns a function
      //to query. It accepts a callback function to handle query return values.
      //Closure properties capture the variables. The SQL string isn't
      //constructed until query execution.
      var reg = new RegExp(/\$\d+/);
      var sql = statements.join(' ');
      _.each(parameters, function(p, i) {
        sql = sql.replace('$' + (i + 1), p);
      });
      var queryClient = buildQueryClient(sql);
      return queryClient(onQueryReturn);
    }
  }
}

Step 6 Building Advanced Queries

First we build a query to calculate the total cost of our current book supply.

Let's build a function get the author for a particular book title.

The SQL command will look something like this.

select concat(authors.first_name, ' ', authors.last_name) from authors join books on books.author_id = authors.id where books.title like 'The Velveteen Rabbit';
//Step 6 building advanced functions
var getOurCostOfCurrentInventory = buildDynamicQuery([
  'select sum(cost * stock) from stock'
])(/* No parameters */);
getOurCostOfCurrentInventory(printRows('cost of inventory'))


var getAuthorNameByBookTitle = buildDynamicQuery([
  "select concat(authors.first_name, ' ', authors.last_name) as author from authors",
  "join books on books.author_id = authors.id",
  "where books.title like '$1'"
]);

var getVelveteenRabbitAuthor = getAuthorNameByBookTitle('The Velveteen Rabbit');
//You don't have to be so specific with function names.
getAuthorNameByBookTitle('The Velveteen Rabbit')(errorCheck(function(result){
  console.log(result.rows[0]);
}));


//This code ends the 'pg' module's pool after five seconds.
//The process exits because there are no more event listeners.
setTimeout(pg.end.apply(pg), 5000);

Summary

The tutorial provides connection, querying, and API design for using Node.js and PostgreSQL.

What is the example bad at?

The biggest failure is it doesn't do anything for you in relation to your data models changing. Please use a data modeling library if you need this level of abstraction.

Another problem I didn't address is control flow. Sure, we made an API that is simple to piece together. But the API provides no control flow for making many queries.

Why is the functional approach a good one?

Functional code is more readable if you write description function names. Testing functional code is easier because each concern's input and output you can simulate. You can handle more synchronous errors using try and catch. The functional approach consolidates all asynchronous errors into the final callback. This results in reduced error handling responsibility on the caller.

Related Posts

Node.js PostgreSQL Transactions