Theon is a Schema Version Management system for PostgreSQL databases. So the first question to ask yourself is why you would want to bother with such a thing at all.
Why use Theon?
- your data model is in constant flux, needs to be adapted frequently and responsively to meet new requirements
- your data model is large and complex
- you need to manage different instances of your data model for production and development servers, for handling continuous release/testing cycles, for deployment at multiple organisations
- many different people are concurrently working on making changes to your data model
- your data model needs to be kept in alignment with other outside systems, you need it to be automatically maintained based on the requirements of those systems
- you want to develop your data model at a higher abstracted layer than physical database tables, columns and relationships
- you or others need to maintain/upgrade external applications so that they remain in alignment with specific versions of your data model
- others maintain independent systems/applications using your data model and need a robust/automatic upgrade path
If you want/need to do any/all the above then Theon may help you out. If not then adopting a Schema Version Management system might still be the right thing to do – after all it is considered sensible to version control source code so why not databases, see Database Refactoring) – but you will probably get less immediate benefit.
There are other Schema Version Management systems that support PostgreSQL: Liquibase, Flyway, sqlHawk, MIGRATEdb, schema-evolution-manager, Alembic, Sqitch, DBSteward. All these vary in functionality, complexity and price. Follow the links and make your evaluation of the pros and cons of each depending on your own specific needs. Architecturally Theon is probably closest to DBSteward.
Why choose Theon?
- open source under GPLv3
- unix command line scriptable tool chain
- extensible
- change sets are just SQL DDL
- built in graphical schema editor
- make changes by direct DDL manipulation on development database instances
- import and go
Why not?
- immature
- evolving
- only supports a subset of the PostrgreSQL feature set
The second part of this article examines what you need to get started using Theon. The final part will demonstrate managing a database in Theon.
Thanks for this series of articles, Tim. This is a helpful overview of what Theon is. I’ve never heard of it (but I have heard of the others). I agree that database schema management can be hard but it’s very beneficial.
My team at work uses a different tool, but I’m learning more about Postgres and I’ll consider Theon for a side project in Postgres.
Is it something you’re still using?
Hi Ben. Thanks for your comments. We still use Theon a lot and find it very useful, in particular we can throw together new prototype services in a matter of days that can then be quickly changed as users work with them. It still has limitations (re-installing a database from a new schema remains easier than trying to directly upgrade it from a differential schema for anything beyond basic additive change). It suits our local development and production environment, but probably isn’t for everyone.