Run tests on an isolated, temporary PostgreSQL database
bitbucket/overview | download 2.5 | man page
Get an interactive shell to a new database with a predefined schema
$ uri=$(pg_tmp) $ psql $uri -f schema.sql $ psql $uri
Tagwatch: using pg_tmp to facilitate application development
WEBM (31MB, vp8-vorbis) MP4 (27MB, h264-aac)
pg_tmp
is a compact shell script designed to make unit testing, integration testing with
PostgreSQL easy in any language.
Sometimes SQLite is used as drop-in replacement for PostgreSQL when running tests in order to make tests self-contained and therefore free of side-effects. This technique seems to suffice for simple interactions, but it does so by reducing the set of features that an application can use to the functionality common to both platforms. PostgreSQL is not a heavy weight key-value store it is more like a specialized programming environment.
The engineering genius of unit tests is mainly the ability they have to shape the design of the internal interfaces used to build an application. To ensure that we are testing the module and not the environment we mock APIs to external resources, but never the runtime or the language itself. An application built on Postgres is an application where individual functions span more than process—and more than one runtime. Testing concretely requires verification of methods as complete “units”.
To facilitate the creation of tests that run autonomously and concurrently two libraries have been developed to start up and tear down a database in the test code itself: Test-postgresql (Perl), and testing.postgresql (Python). Both of these are well-maintained and highly configurable, but they have some limitations:
pg_tmp
is less configurable from the command line, is impervious to deadlocks, requires
no external libraries, and is already tuned for the singular task of reducing the
startup time to less than a second. This makes a full-featured PostgreSQL database
useful for unit testing and other tests that require rapid feedback.
| Total Wait Time | Speedup | Strategy |
|---|---|---|
| 7.2 | — | Naive in-process creation and teardown |
| 6.4 | 0.8 |
Disabling runtime fsync (
pg_ctl -F
)
|
| 6.0 | 0.4 |
Spinning to make the first connection (instead of
pg_ctl -w
)
|
| 2.2 | 3.8 |
Background database initialization (
initdb --nosync
)
|
| 1 second | 1.3 | Shut down database asynchronously |
While most PostgreSQL libraries support the URI syntax, some frameworks do not. The following are links to culpability shims that allow you to configure your application to accept a URI-formatted connection string.
| Ruby (pg adapter) | ruby-pg-url |
| Django | dj-database-url |
Last updated on November 20, 2018
Send questions or comments to
ericshane@eradman.com