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
    eyes.inspect d

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

  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'
      q.on 'end', c
    query2 : (text, c) ->
      console.log "Executingn#{text}"
      q = @query text
      q.on 'error', c

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
        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
        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)
      if files.length == 0
        console.log "No files to read!"
        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}"
        async.waterfall commands, passErrorsUp c
], (e) ->
  if e == null || !e?
    process.exit 0
    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.

Published by

Julian Birch

Full time dad, does a bit of coding on the side.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s