A Database Upgrade Script in Node

If you’re starting a database project, one of the first things you need is the ability to recreate and upgrade the database.  Tarantino has features to do this in the .NET world, but I just needed something quick and dirty.  You can also use this code as an introduction to Brian Carlson’s Node Progres library (connection pooling coming soon…).  Note that, in common with most Node projects, it’s almost impossible to get work done without underscore.js and async.js.

# pg = require 'postgres-js/lib/postgres-pure'
pg = require './node-postgres/lib'
eyes = require 'eyes'
_ = require './underscore'
async = require './async'
fs = require 'fs'

process.on 'uncaughtException', (d) ->
  if d.stack?
    console.log "Message #{d.message}"
    console.log d.stack
  else
    eyes.inspect d

getConnection = (d) ->
  conn = new pg.Client d
  conn.connect()

  conn.on 'error', (err) ->
    eyes.inspect err

  _.extend conn,
    execute : (text, c) ->
      q = @query2 text, c
      q.on 'end', c
    singleValue : (text, c) ->
      q = @query2 text, c
      q.once 'row', (r) ->
        for k,v of r
          c null, v
          q.removeAllListeners 'end'
          return
      q.on 'end', c
    query2 : (text, c) ->
      console.log "Executingn#{text}"
      q = @query text
      q.on 'error', c
      q
   conn

conn = getConnection
  database : 'db_name'
  user : 'postgres'
  password : 'password'

getId = (f) ->
  m = /d+/.exec f
  return 0 if m == null
  parseInt m[0]

passErrorsUp = (c) -> (e) ->
  if e != null
    c e

async.waterfall [
    (c) ->
      @foundSchemaVersion = false
      q = conn.query2 "select tablename from pg_tables;", c
      q.on 'row', (r) =>
        @foundSchemaVersion ||= 'schema_version' == r.tablename
      q.on 'end', => c null, @foundSchemaVersion
    (d, c) ->
      console.log "Found table: #{foundSchemaVersion}"
      if d
        c()
      else
        conn.execute "create table schema_version ( version integer );", c
    (c) -> conn.singleValue "select version from schema_version", c
    (d, c) ->
      if d == null
        conn.execute "insert into schema_version values (0);", -> c null, 0
      else
        c null, d
    (v, c) ->
      console.log "Current Version #{v}"
      fs.readdir './sql', (e, d) -> c e, v, d
    (v, files, c) ->
      files = (_ files).chain()
        .select((f) -> v < getId f)
        .sortBy(getId)
        .value()
      if files.length == 0
        console.log "No files to read!"
        c()
      else
        commands = _.flatten _.map files, (f) -> [
          (c) -> fs.readFile "sql/#{f}", 'utf8', c
          (text, c) ->
            console.log "Read file #{f}"
            text = "#{text};update schema_version set version = #{getId f};"
            conn.execute text, c
          (d, c) ->
            console.log "Executed file #{f}"
            c()
        ]
        async.waterfall commands, passErrorsUp c
], (e) ->
  if e == null || !e?
    process.exit 0
  else
    eyes.inspect e
    process.exit 1

Some notes for Node newbies like me:

• It’s really important to ensure that the callback (c) gets called exactly once for every step in an async waterfall.  This is particularly messy when you kick off a waterfall within the other waterfall.
• The node_postgres API missed some convenience functions I’m used to.  So I added them.  This is JavaScript, after all.