Pyrseas is a Database Version Control for PostgreSQL with a very interesting approach and with an easy way to script a database (database to a YAML file) and to apply changes to a database (YAML file to a database). It’s develped in Python by Joe Abbate (thank you Joe for it!)

Currently I’m using dbtoyaml to script the database in every release and I commit it together with application code, but in the future I plan to use it together with yamltodb for continuous integration.

Brief introduction

Joe describes it as follows:

Pyrseas provides a framework and utilities to upgrade and maintain a PostgreSQL database.
Features:

• Outputs a YAML/JSON description of a PostgreSQL database’s tables and other objects (metadata), suitable for storing in a version control repository.
• Generates SQL statements to modify a database so that it will match an input YAML/JSON specification.

• (planned) Generates a flexible web application to update PostgreSQL tables.
  Requirements:

• PostgreSQL 8.4 or higher

• Python 2.6 or higher

• (planned) Werkzeug

• (planned) Jinja2

License:

• Pyrseas is free (libre) software and is distributed under the BSD license.

You can download it from GitHub and the most current documentation is available here.

Installing required packages

I suppose that in Linux its installation is quite straight forward, but in Windows Server 2008 R2 you need 64bits packages and not all required packages have their official 64bits version. So, I had to search for some alternative to install Pyrseas pre-requisites.

I installed these packages in the following order:

• [Python 2.7.3 Windows X86-64 Installer[(http://python.org/ftp/python/2.7.3/python-2.7.3.amd64.msi) (Windows AMD64 / Intel 64 / X86-64 binary — does not include source). I suggest to add python in PATH environment variable.
• From an unofficial website, where they advice you with “The files are unofficial(meaning: informal, unrecognized, personal, unsupported) and made available for testing and evaluation purposes.” You can download and install:
  • distribute-0.6.26.win-amd64-py2.7.exe (it’s a replacement for setuptools).
  • PyYAML-3.10.win-amd64-py2.7.exe.
• psycopg2-2.4.5.win-amd64-py2.7-pg9.1.3-release (official website).

Installing Pyrseas

After downloading it from GitHub, unzip the file and into the directory execute:

python setup.py install

Example:

E:\Temp\pyrseas-0.5.0>python setup.py install
running install
running bdist_egg
running egg_info
[…]
Using e:\python27\lib\site-packages
Searching for psycopg2==2.4.5
Best match: psycopg2 2.4.5
Adding psycopg2 2.4.5 to easy-install.pth file
Using e:\python27\lib\site-packages
Searching for distribute==0.6.26
Best match: distribute 0.6.26
Adding distribute 0.6.26 to easy-install.pth file
Installing easy_install-script.py script to E:\Python27\Scripts
Installing easy_install.exe script to E:\Python27\Scripts
Installing easy_install-2.7-script.py script to E:\Python27\Scripts
Installing easy_install-2.7.exe script to E:\Python27\Scripts
Using e:\python27\lib\site-packages
Finished processing dependencies for Pyrseas==0.5.0

Using it

Once installed, you can execute it. For example:

cd C:\Python27\Lib\site-packages\Pyrseas-0.5.0-py2.7.egg\pyrseas
python dbtoyaml.py –host=”127.0.0.1″ -U myuser mydatabase > E:\temp\mydatabase.yaml

If everything goes fine, you should have the file E:\temp\dbagn.yaml that describes your database. Now you can test easily yamltodb by deleting or adding a column in a table in YAML file and run it:

python yamltodb.py –host=”127.0.0.1″ –user=postgres –output=”deltascript.sql” mydatabase mydatabase.yaml

Then, you’ll apply the changes made in the YAML file to the database!

Conclusion

It’s interesting, isn’t it? I like to script out the database schema and nowadays with ORM products it’s even more important: you never now what’s happening behind the scenes…