Postgresql with Knex.js

Getting Started:

# Install dependencies
$ npm install --save pg knex

# Create knexfile.js
$ knex init

Open up the generated knexfile.js. I recommend commenting out the staging and production configuration (for now). Configure your development environment to look something like this:

module.exports = {

  development: {
    client: 'postgresql',
    connection: {
      database: 'my_appname_dev'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

  test: {
    client: 'postgresql',
    connection: {
      database: 'my_appname_test'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: 'knex_migrations'
    }
  },

};

DB-Model Pattern

Here is a useful pattern to get started with interacting with your database. It involves 3 parts:

1. The db.js file. This is the file that makes the connection to the database.
2. The create-model.js file. This is the file that contains all your common model methods.
3. The models themselves - user.js, comment.js, etc. Each of these models will mixin the common methods from create-model.js

db.js

First create the folder server/lib/ if it does not exist.

Write the following in server/lib/db.js:

// Read configuration file
var config = require('../knexfile.js')

// Configure knex with the correct environment configuration
var env = process.env.NODE_ENV || 'development'
var db = require('knex')(config[env])

// Export the db object, which will be able to make database connections
module.exports = db

// Function for your testing suite
db.deleteEverything = function () {
  if (env !== 'test') return Promise.reject();
  // TODO: Delete data from all tables (useful for testing)
  // return db('users').truncate()
}

create-model.js

Write the following in server/lib/create-model.js:

"use strict"
var db = require('./db.js')
var Promise = require('bluebird')


module.exports = function (modelName, tablename) {

  // Initialize with methods common across all models
  var Model = {

    all: function () {
      return db(tablename).select('*')
    },

    // Finds a single record by id
    find: function (id) {
      return Model.findBy({ id: id })
    },

    // Finds a single record
    findBy: function (attrs) {
      return db(tablename).select('*').where(attrs).limit(1)
        .then(function(rows) {
          return (rows.length === 0) ? Promise.reject(new Model.NotFound) : rows[0]
        })
    },

    save: function (attrs) {
      return attrs.id ? Model.updateOne(attrs) : Model.create(attrs)
    },

    create: function (attrs) {
      attrs.created_at = new Date()
      return db(tablename).insert(attrs).returning('id')
        .then(function (rows) {
          return Object.assign({ id: rows[0] }, attrs)
        })
    },

    // Updates a specific record by its id
    updateOne: function (attrs) {
      if (! attrs.id) {
        return Promise.reject(new Model.InvalidArgument('id_is_required'))
      }

      attrs.updated_at = new Date()
      return db(tablename).update(attrs).where({ id: attrs.id })
        .then(function(affectedCount) {
          return (affectedCount === 0) ? Promise.reject(new Model.NotFound) : attrs
        })
    },

    destroy: function (id) {
      return db(tablename).where({ id: id }).delete()
    }
  }


  // Custom Errors (useful for handling via Promise#catch)
  Model.NotFound = class NotFound extends Error {
    constructor() {
      super(`${modelName}: not found.`)
      this.name = 'InvalidSignup'
    }
  }
  Model.InvalidArgument = class InvalidArgument extends Error {
    constructor(message) {
      super(`${modelName}: ${message}`)
      this.name = 'InvalidArgument'
    }
  }

  // Return an object with Model as its prototype to make overriding easier
  var finalModel = Object.create(Model)
  finalModel.methods = Model
  return finalModel
}

These methods are re-used for every new model you create. Feel free to add or remove methods as you work on your project.

The models themselves

First create the folder server/models/ if it does not exist.

As an example, let's create a server/models/user.js file:

var db = require('../lib/db')
var Promise = require('bluebird')
var createModel = require('../lib/create-model')


var User = module.exports = createModel('User', 'users')

User.signIn = function (username, password) {
  return User.findBy({ username: username })
    .then(function(user) {
      // In a real app the user's password would be encrypted
      if (user.password === password) {
        return user
      }
      else {
        return Promise.reject(new User.InvalidCredentials())
      }
    })
}

// Another custom error
User.InvalidCredentials = class InvalidCredentials extends Error {
  constructor() {
    super(`invalid_credentials`)
    this.name = 'InvalidCredentials'
  }
}

Now when you require this file, you can use .signIn, as well as all the other common model methods you define (.create, .all, etc.).

Example Use (with express.js)

var User = require('../models/user')

app.post('/sign-in', function (req, res) {
  User.signIn(req.body.username, req.body.password)
    .then(function(user) {
      // Set session, etc.
      res.send({ signedIn: true })
    })
    .catch(User.NotFound, function() {
      res.status(400).send({ signedIn: false, error: 'username_not_found' })
    })
    .catch(User.InvalidCredentials, function() {
      res.status(401).send({ signedIn: false, error: 'invalid_password' })
    })
})

In practice you may want to combine both NotFound and InvalidCredentials into one response for better security.