Hey! I'm available for hire (and I appreciate tips)  



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

Was this useful to you? Share it!

Also, you can give me a tip or hire me.


Published

2014-5-6

Categories


Tags