The Scenario

I'm wanting to connect to a PostgreSQL database using node.js and Bookshelf.

My client's DB admin has already set up the database, but because we're working on different parts of the application in different languages, I need to connect to it from node.js.

Side note: I'd recommend that you not have two applications accessing a database (too much duplication of business logic constraints that can't fit into the database itself) and that if you have to, you layer your application so that the storage service has complete control of the database and the secondary layer interacts with that layer.

Install PostgreSQL Client with Headers & Development Files

sudo apt-get install -y \
  postgresql-client \

Test Connection with Commandline Client

psql -U <<username>> -h <<hostname||ip>> <<db_name>> -p <<port>>

Show tables


Describe a table

\d <<table name>>

Connect with Node.js Client


sudo apt-get install -y \

npm install --save \
  pg \
  knex \

Test Connection

'use strict';

var Bookshelf = require('bookshelf')

var PostgreSql = Bookshelf.initialize({
  client: 'postgresql',
  connection: {
    host     : 'localhost',
    port     : '5432',
    user     : 'foouser',
    password : 'foosecret',
    database : 'foo_database',
    charset  : 'UTF8_GENERAL_CI'

var Customers = PostgreSql.Model.extend({
  tableName: 'customer'

Customers.collection().fetch().then(function (collection) {

By AJ ONeal

If you loved this and want more like it, sign up!

Did I make your day?
Buy me a coffeeBuy me a coffee  

(you can learn about the bigger picture I'm working towards on my patreon page )