We're looking to add a column to the table user called admin (boolean) in an existing database.
migrations
001-admin.sql
test
001-admin.sql
index.js
.travis.yml
package.json
replay.js
-- New users should not be admin
BEGIN; DO $$ <<fn>>
DECLARE
uid int;
a boolean;
BEGIN
uid := (SELECT create_user('[email protected]', repeat('X', 64)));
RAISE NOTICE 'user % created', uid;
a := (SELECT admin FROM "user" WHERE user_id = uid);
IF a IS NULL THEN
RAISE 'admin is null';
END IF;
IF a = TRUE THEN
RAISE 'admin is true by default';
END IF;
END; $$; ROLLBACK;BEGIN; DO $$ <<fn>>
We're doing a few things here. The BEGIN; starts a transaction explicitly. The DO-keyword executes an anonymous code block. (When a language is not specified, plpgsql is used) $$ marks the beginning of a string. Notice how there is a matching $$ at the end of the script. <<fn>> is optional. It lets you qualify your function variables (the ones under DECLARE) with fn.varname, for example fn.uid. In this test, there are no name ambiguities.
The rest is pretty straight forward plpgsql. We create a user, in this case with the function create_user. If admin is NULL by default, an exception is RAISE'd.
END; marks the end of the function and $$; marks the end of the anonymous code block and concludes the DO statement. Finally, we ROLLBACK;, which only occurs in the case of a success. In the case of a failure, an exception is raised and the transaction is aborted.
-- admin can not be null
BEGIN; DO $$ <<fn>>
DECLARE
uid int;
a boolean;
BEGIN
uid := (SELECT create_user('[email protected]', repeat('X', 64)));
BEGIN
UPDATE "user" SET admin = NULL WHERE user_id = uid;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END;
RAISE 'fail';
END; $$; ROLLBACK;
In this test, we want the test to fail. The admin column should be NOT NULL.
To run the tests, I use mocha (because I use it for everything else) and expect.js for assertions.
var fs = require('fs')
, path = require('path')
, Client = require('pg').Client
, client = new Client(process.env.DB)
, files = fs.readdirSync(__dirname).filter(function(fn) {
return fn.match((/\.sql$/))
})
describe('database', function() {
client.connect()
files.forEach(function(fn) {
it(fn, function(done) {
var q = fs.readFileSync(path.join(__dirname, fn), 'utf8')
client.query(q, done)
})
})
after(client.end.bind(client))
})This script reads every file in its own directory that has the extension .sql and describes a test for it using the it-function exposed by mocha. Each test is simply to run the query against the PostgreSQL client.
Let's try:
PS C:\git\snow\db> mocha -b
....
× 1 of 1 tests failed:
1) database 001-admin.sql:
error: column "admin" does not exist
at Connection.parseE (C:\git\snow\db\node_modules\pg\lib\connection.js:537:11)
at Connection.parseMessage (C:\git\snow\db\node_modules\pg\lib\connection.js:382:17)
at Socket.<anonymous> (C:\git\snow\db\node_modules\pg\lib\connection.js:101:18)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:710:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:382:10)
at emitReadable (_stream_readable.js:378:5)
at readableAddChunk (_stream_readable.js:143:7)
at Socket.Readable.push (_stream_readable.js:113:10)
at TCP.onread (net.js:511:21)
That's what we expected. Next, we'll write a migration script that adds this column.
We have now written a test and a script that runs it. The next step is to describe the migration script to add the admin column to the database.
ALTER TABLE "user"
ADD COLUMN admin BOOLEAN NOT NULL DEFAULT(FALSE);The migration runner is a small script that runs migration scripts on our database.
PS C:\git\snow\db> node .\replay.js
Run migration scripts.
Usage: C:\Program Files\nodejs\node.exe C:\git\snow\db\replay.js
Options:
-d, --db database uri [required]
-f, --from from migration index [default: 0]
-t, --to to migration index [default: 1000]
Missing required arguments: d
var fs = require('fs')
, colors = require('colors')
, path = require('path')
, Client = require('pg').Client
, argv = require('optimist')
.usage('Run migration scripts.\nUsage: $0')
.describe('d', 'database uri').demand('d').alias('d', 'db')
.describe('f', 'from migration index').alias('f', 'from').default('f', 0)
.describe('t', 'to migration index').alias('t', 'to').default('t', 1000)
.argv
var client = new Client(argv.db)
client.connect()
var dir = path.join(__dirname, './migrations')
, files = fs.readdirSync(dir).sort()
function nextFile(cb) {
var fn = files.shift()
if (!fn) return cb()
var n = +fn.substr(0, 3)
if (n < argv.from) return nextFile(cb)
if (n > argv.to) return cb()
var q = fs.readFileSync(path.join(dir, fn), 'utf8')
process.stdout.write(fn.substr(0, 3) + '... ')
client.query(q, function(err) {
if (err) {
console.error('ERROR: %s\n'.red, err.message)
throw err
}
console.log('OK'.green)
nextFile(cb)
})
}
if (argv.f === 0 && argv.t == 1000) console.log('running all migrations')
else console.log('running migrations %s to %s', argv.f, argv.t)
nextFile(function() {
process.exit()
})Let's give it a shot:
PS C:\git\snow\db> node .\replay.js -d postgres://postgres@localhost/test
running all migrations
001... OK
To see what happens if a migration fails, try to run it again:
PS C:\git\snow\db> node .\replay.js -d postgres://postgres@localhost/test -f 1
running migrations 1 to 1000
001... ERROR: column "admin" of relation "user" already exists
C:\git\snow\db\replay.js:31
throw err
^
error: column "admin" of relation "user" already exists
at Connection.parseE (C:\git\snow\db\node_modules\pg\lib\connection.js:537:11)
at Connection.parseMessage (C:\git\snow\db\node_modules\pg\lib\connection.js:382:17)
at Socket.<anonymous> (C:\git\snow\db\node_modules\pg\lib\connection.js:97:20)
at Socket.EventEmitter.emit (events.js:95:17)
at Socket.<anonymous> (_stream_readable.js:710:14)
at Socket.EventEmitter.emit (events.js:92:17)
at emitReadable_ (_stream_readable.js:382:10)
at emitReadable (_stream_readable.js:378:5)
at readableAddChunk (_stream_readable.js:143:7)
at Socket.Readable.push (_stream_readable.js:113:10
We've now peformed the migration and the test should pass.
PS C:\git\snow\db> mocha -R spec
database
V 001-admin.sql
1 test complete (31 ms)
Perfect!
Let's add some continuous integration:
.travis.yml
language: node_js
env:
- DB=postgres://postgres@localhost/test
node_js:
- 0.8
before_script:
- psql -c 'create database test;' -U postgres
- node ./replay.js -d postgres://postgres@localhost/test
Push to Github, which triggers Travis-CI:
Using worker: worker-linux-6-1.bb.travis-ci.org:travis-linux-18
$ export DB=postgres://postgres@localhost/test
git.1
$ git clone --depth=50 --branch=master git://github.com/justcoin/snow-db.git justcoin/snow-db
Cloning into 'justcoin/snow-db'...
remote: Counting objects: 265, done.
remote: Compressing objects: 100% (124/124), done.
remote: Total 265 (delta 143), reused 256 (delta 138)
Receiving objects: 100% (265/265), 46.56 KiB, done.
Resolving deltas: 100% (143/143), done.
$ cd justcoin/snow-db
git.2
$ git checkout -qf 9bcb57b8841f681b1df4339dafb259a43d8f8a30
$ nvm use 0.8
Now using node v0.8.22
$ node --version
v0.8.22
$ npm --version
1.2.14
install
$ npm install
npm http GET https://registry.npmjs.org/pg
npm http GET https://registry.npmjs.org/optimist
...
[email protected] node_modules/colors
[email protected] node_modules/optimist
└── [email protected]
...
└── [email protected] ([email protected], [email protected])
before_script.1
$ psql -c 'create database test;' -U postgres
CREATE DATABASE
before_script.2
$ node ./replay.js -d postgres://postgres@localhost/test
running all migrations
001... OK
$ npm test
> [email protected] test /home/travis/build/justcoin/snow-db
> mocha -b
․․․․․․․․․․․․․
1 test complete (78 ms)
The command "npm test" exited with 0.
Done. Your build exited with 0.