I am interested in how others have contacted the issue of maintaining and syncing database changes across many (10+) designers with no DBA? What i'm saying, essentially, is when someone wants to create a switch to the database, what exactly are some methods to doing that? (i.e. I have produced a 'Car' model and today I wish to apply the right DDL towards the database, etc..)

We are mainly a Python shop and our ORM is SQLAlchemy. Formerly, we'd written our models in a way to produce the models using our ORM, but we lately cast off this because:

  • We could not track changes while using ORM
  • The condition from the ORM wasn't synchronized using the database (e.g. plenty of variations mainly associated with indexes and different constraints)
  • There is not a way to audit database changes unless of course the developer recorded the database change via email towards the team.

Our fix for your problem ended up being to essentially possess a "gatekeeper" individual that inspections every become the database and is applicable all recognized database changes for an accepted_db_changes.sql file, whereby the designers who desire to make any database changes take their demands right into a proposed_db_changes.sql file. We take a look file in, and, when it is up-to-date, all of us apply the modification to the personal database on our development machine. We do not create indexes or constraints around the models, they're applied clearly around the database.

I must understand what are a handful of methods to keep database schemas and when ours appears reasonable.


The answer is quite administrative then technical :)

The overall rule is simple, there should simply be tree-like dependencies within the project: - There ought to always be just one master supply of schema, saved with the project source code within the version control - Everything impacted by the modification within the master source ought to be instantly re-produced each time the actual source is up-to-date, no manual intervention permitted never, if automatic generation doesn't work -- fix either master source or generator, don't by hand update the origin code - All re-decades ought to be carried out through the same individual who up-to-date the actual source and all sorts of changes such as the master source change should be thought about just one transaction (single source control commit, single build/deployment for each affected atmosphere including DBs update)

Being enforced, this provides 100% reliable result.

You will find basically 3 possible options from the master source 1) DB metadata, sources are produced after DB update by some tool hooking up towards the live DB 2) Source code, some tool is producing SQL plan in the sources, annotated inside a special way after which SQL operates around the DB 3) DDL, both SQL schema and source code are produced by some tool 4) another description can be used (say a text file read with a special Perl script producing both SQL schema and also the source code)

1,2,3 are equally good, supplying the tool you'll need is available and isn't over costly 4 is really a universal approach, but it ought to be applied in the very start of the project and it has an overhead of couple 1000's lines of code inside a strange language to keep

Perhaps you have attempted the SQLalchemy Migrate tools?

They're particularly made to auto-migrate your database design changes.