5

We're multiple users working on multiple apps using one same database. The database schema is used in production, preproduction and development environments. Right now, we're keeping it updated using migrations across all environments, but this leaves us with hundreds of migration files and it's incredibly messy.

Is there a way to keep a database schema synced across environments, without having the schema split up in dozens of files?

5
  • How frequently do you update your production environment? Do you update production only after an official release (say every X weeks), or do you use a Continuous Deployment strategy. Sep 26, 2016 at 11:09
  • @BartvanIngenSchenau I update the production environment after an official release. Sep 26, 2016 at 13:22
  • What exactly is the problem with having the schema split up in files? I work on an application where the entire database is now built automatically from scratch using a base script to create some tables and >1000 SQL scripts written over the years as we develop the application that add, remove or update tables, columns, indexes...
    – JDT
    Sep 27, 2016 at 8:00
  • @JDT The problem with this is that you can't have a quick glance at how your schema look. You have no idea of how it looks until you apply every migrations to create the database and browse it in a database visualization tool. Also, when creating a new database, you need to start from your initial schema up to your latest migration. Not that it's really painful but it's just not a really good design. Sep 27, 2016 at 8:08
  • It might be personal preference but I'd MUCH rather browse my database in a visualisation tool instead of a huge SQL script, but YMMV.
    – JDT
    Sep 27, 2016 at 9:36

2 Answers 2

2

If you want to minimize the amount of files you need to perform your database changes and prefer a single big file, you could use the following workflow:

  1. Write a 'baseline' file to create your database tables as they are in production
  2. Develop your application's next version, using one or more files for your changes to the database in that version
  3. Deploy from scratch to development by running the baseline file, along with the change files
  4. Deploy to preproduction by running the files containing the changes on the preproduction database, which is the same schema version as your baseline file
  5. Deploy to production by running the files containing the changes, which is the same schema version as your baseline file
  6. Once the changes make it to production, create a new 'baseline' file by running the existing baseline, applying the changes and creating a new baseline from that database.
  7. Repeat from step 2

This is not something I'd personally do. There is nothing inherently wrong with having a lot of small files for database changes because:

  • Version control can easily tell you why a change occurred because you commit the change script, which can be very helpful
  • Reading a baseline script for a big application is painful, a good database visualisation tool makes it a lot easier
  • The impact performance-wise in running the small script should be negligible
  • If you ever have an old database version updating to the latest version is easy because you still have the individual change scripts
  • Tooling exists to help you run those files and track which ones still need to run as part of the deployment, which helps if you bump an environment to a version that skips some versions inbetween

To give you an idea, one of the applications I work on has more than 14.000 lines of SQL across 34 files to create a baseline database and uses more than 1.000 change scripts from that baseline. Good luck reading that baseline script.

5
  • Well the thing with migrations is that it's basically a "file version" of a VCS. Instead of everything being managed internally, you have all your commits right there in "your same folder". It's like instead of versioning your source code files with git, you create a new file with a patch for every modifications. And at the end, if you want to read one whole file at its latest revision, you need to apply every patches one by one. It's more this concept that bothers me rather than not being able to read my whole raw SQL file. Sep 27, 2016 at 15:23
  • 1
    By that logic you might as well put all of your sources inside a single file, no?
    – JDT
    Sep 27, 2016 at 18:14
  • One important think I failed to mention: every application, if it lives long enough, ends up with migration scripts in databases that are more than 'add this column'. The moment you need to do transformations in order to migrate no automatic tool can help you.
    – JDT
    Sep 27, 2016 at 18:20
  • Well I don't think you get my point. I was not referring to having all my schema in a single file, but having my whole latest revision in a single "folder". Just like you have your whole latest commit in a single place and not split up in multiple patches. Sep 28, 2016 at 1:59
  • So what is it that you want to achieve, then? You want everything in a single file, but to me it seems for no reason other than having it all in a single file? In that case, write checks around every single statement and put everything in a one big file. That way you can run the file on any environment to upgrade it to a specific version and the checks make sure that no statement that was already run is run again.
    – JDT
    Sep 28, 2016 at 7:14
1

If you can, have one group or person manage the model or schema. From the model or schema the DDL is generated and applied to the database instances. That way you don't maintain scripts. Instead just the model of the database and generate the script(s) when needed. This should include tables, indexes, triggers, etc. There are several tools on the market to make this manageable.

The application teams will need to maintain the stored procedures or any data that needs to be inserted/updated for the application. They can store these in source control to store latest and make them re-runnable so they can be run again and again to the same effect.

One time loads are more difficult. A strategy here would be to store those under source control. One could have a table in the database called ONETIME or similar which would store all the one time scripts that have been run previously. When the scripts are run, it merely checks the ONETIME table to see whether the script has already been run against the instance.

This should make things more manageable.

2
  • I'm trying to find a technique where I don't need an application team to upgrade the database.. Here's what I'm trying to do: have one schema described in a version controlled file. A developer can update it, and when we want to update the production database, we just run a command that would compare the current database and the new schema and apply the modification automatically. Sep 27, 2016 at 2:34
  • This is an apples to oranges comparison. One could use the schema and create a new database from it and then compare that database with the current one in production to see the differences. But I think you could make everything re-runnable. If Column_Exists(ColumnName) Alter table.... etc.
    – Jon Raynor
    Sep 27, 2016 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.