Wiki: TestDrivenDatabaseDevelopment

Test Driven Database Development tutorial

Monday, July 19
Portland 252

Questions? Please use the comment box on the tutorial description page.


Attendees should have PostgreSQL 8.4 + contribs installed. Contribs are usually included with binary distributions.

Attendees should also have Perl (Windows users grab Strawberry Perl) and Test::Harness 3.0 or higher: To install from CPAN:

cpan Test::Harness

And finally, attendees should download and install pgTAP 0.24. Be sure to install it into a schema named tap>

make TAPSCHEMA=tap
make install
make installcheck

See below for detailed instructions for installing pgTAP on Windows or for building without a compiler.

Creating the Database

Now, assuming that PostgreSQL is running, create the database and add pgTAP to it:

createdb flipr
createlang plpgsql flipr
psql -d flipr -f pgtap.sql

Note that you might have to use -U postgres to connect to PostgreSQL as the super user:

createdb -U postgres flipr
createlang -U postgres plpgsql flipr
psql -U postgres -d flipr -f pgtap.sql

If such is the case for you, you will also need to use -U postgres with pg_prove in the exercises during the presentation.

Installing pgTAP on Windows

PGXS (the PostgreSQL extension build-system) is mainly a Unix build environment. Fortunately, pgTAP requires no C building on 8.4, so installing it should be pretty easy if you’ve already got Perl installed. So if you know where your PostgreSQL install is (probably something like "%ProgramFiles%\PostgreSQL-8.4), you should be able to install pgTAP like this (updated by David Hancock to get the DOS batch syntax working; using Strawberry Perl):

cd c:\wherever\you\put\tap24
copy pgtap.sql
perl.exe -pi.bak -e "s/TAPSCHEMA/tap/g" pgtap.sql
perl.exe -pi.bak -e "s/__OS__/win32/g" pgtap.sql
perl.exe -pi.bak -e "s/__VERSION__/0.24/g" pgtap.sql
perl.exe -pi.bak -e "s/^-- ## //g" pgtap.sql
copy pgtap.sql "%ProgramFiles%\PostgreSQL\8.4\share" 
call pl2bat bin/pg_prove
copy pg_prove.bat "%ProgramFiles%\PostgreSQL\8.4\bin\"

Essentially, we’re just copying to pgtap.sql, and then changing “TAPSCHEMA” to “tap”, __OS__ to “win32”, and __VERSION__ to “0.24”, and then removing the all instances of ”—## ” from the beginning of lines. Then it gets copied to the share directory under you PostgreSQL install.

After that, we use the pl2bat.bat program that should be included with your install of Perl to convert pg_prove to a .bat file, and then install that. If you want, you can put it somewhere else so that it’s in your %PATH%, or else add the PostgreSQL bin directory to your %PATH%—or just use the full path to it to execute it.

Please update this entry with any improvements. It will be used to create permanent permanent instructions to include in the README. Your feedback will really help with that!

Installing pgTAP without a compiler

If you’re installing pgTAP on a box with no compiler (for example, if you you’re on Mac OS X and don’t have Xcode installed), you can build and install pgTAP like so:

cd pgtap-0.24
cp pgtap.sql
perl -pi.bak -e 's/TAPSCHEMA/tap/g' pgtap.sql
perl -pi.bak -e "s/__OS__/`./`/g" pgtap.sql
perl -pi.bak -e 's/__VERSION__/0.24/g' pgtap.sql
perl -pi.bak -e 's/^-- ## //g' pgtap.sql
cp pgtap.sql /path/to/pgsql/share/contrib/
cp bin/pg_prove /path/to/pgsql/bin/


The entire presentation, including the Keynote slide deck and all source code examples, are in the presentation GitHub repository. You can clone it with

git clone git://

The files in the can also be fetched from the local network file server:

Alternative git repo

A shallow git clone is available at:

git clone git://
  • Intel
  • Microsoft
  • Google
  • Facebook
  • Rackspace Hosting
  • (mt) Media Temple, Inc.
  • ActiveState
  • CommonPlaces
  • DB Relay
  • FireHost
  • GoDaddy
  • HP
  • HTSQL by Prometheus Research
  • Impetus Technologies Inc.
  • Infobright, Inc
  • JasperSoft
  • Kaltura
  • Marvell
  • Mashery
  • NorthScale, Inc.
  • Open Invention Network
  • OpSource
  • Oracle
  • Parallels
  • PayPal
  • Percona
  • Qualcomm Innovation Center, Inc.
  • Rhomobile
  • Schooner Information Technology
  • Silicon Mechanics
  • SourceGear
  • Symbian
  • VoltDB
  • WSO2
  • Linux Pro Magazine

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at

Download the OSCON Sponsor/Exhibitor Prospectus

Media Partner Opportunities

Download the Media & Promotional Partner Brochure (PDF) for information on trade opportunities with O'Reilly conferences or contact mediapartners@

Press and Media

For media-related inquiries, contact Maureen Jennings at

OSCON Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the OSCON Newsletter (login required)

OSCON 2.0 Ideas

Have an idea for OSCON to share?

Contact Us

View a complete list of OSCON contacts