What are some strategies for maintaining a common database schema with a team of developers and no DBA?

Question

I'm curious about how others have approached the problem of maintaining and synchronizing database changes across many (10+) developers without a DBA? What I mean, basically, is that if someone wants to make a change to the database, what are some strategies to doing that? (i.e. I've created a 'Car' model and now I want to apply the appropriate DDL to the database, etc..)

We're primarily a Python shop and our ORM is SQLAlchemy. Previously, we had written our models in such a way to create the models using our ORM, but we recently ditched this because:

• We couldn't track changes using the ORM
• The state of the ORM wasn't in sync with the database (e.g. lots of differences primarily related to indexes and unique constraints)
• There was no way to audit database changes unless the developer documented the database change via email to the team.

Our solution to this problem was to basically have a "gatekeeper" individual who checks every change into the database and applies all accepted database changes to an accepted_db_changes.sql file, whereby the developers who need to make any database changes put their requests into a proposed_db_changes.sql file. We check this file in, and, when it's updated, we all apply the change to our personal database on our development machine. We don't create indexes or constraints on the models, they are applied explicitly on the database.

I would like to know what are some strategies to maintain database schemas and if ours seems reasonable.

Thanks!

Answer 1

The solution is rather administrative then technical :)

The general rule is easy, there should only be tree-like dependencies in the project: - There should always be a single master source of schema, stored together with the project source code in the version control - Everything affected by the change in the master source should be automatically re-generated every time the master source is updated, no manual intervention allowed never, if automatic generation does not work -- fix either master source or generator, don't manually update the source code - All re-generations should be performed by the same person who updated the master source and all changes including the master source change should be considered a single transaction (single source control commit, single build/deployment for every affected environment including DBs update)

Being enforced, this gives 100% reliable result.

There are essentially 3 possible choices of the master source 1) DB metadata, sources are generated after DB update by some tool connecting to the live DB 2) Source code, some tool is generating SQL scheme from the sources, annotated in a special way and then SQL is run on the DB 3) DDL, both SQL schema and source code are generated by some tool 4) some other description is used (say a text file read by a special Perl script generating both SQL schema and the source code)

1,2,3 are equally good, providing that the tool you need exists and is not over expensive 4 is a universal approach, but it should be applied from the very beginning of the project and has an overhead of couple thousands lines of code in a strange language to maintain

Answer 2

Have you tried the SQLalchemy Migrate tools?

They are specifically designed to auto-migrate your database design changes.