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 \
  libpq-dev

Test Connection with Commandline Client

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

Show tables

\d

Describe a table

\d <<table name>>

Connect with Node.js Client

Install

sudo apt-get install -y \
  libpq-dev

npm install --save \
  pg \
  knex \
  bookshelf

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) {
  console.log(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 )



Published

2014-5-6



Buy me a coffeeBuy me a coffee


  73% of Goal Reached


Want more like this?