8 ms·

Database Versioning: The ignored aspect of version control

72 points by jlemoine 13y ago

andrewaylett 13y ago
Very interested to see the link to Liquibase[0] in that article. Must check it out. Does anyone have experience with that sort of tool that they'd like to share, either good or bad? [0]: http://www.liquibase.org/ http://www.liquibase.org/
- gizmogwai 13y ago
  I do use it. Primarily because it more widespread in the Java world than other competitors,and more easy, in the syntax, to start with. You might also be interested in DB-Main [0] But database migration has little to do with version control of code. With code, when you switch from a version to another, either you have a bug^H^H feature, either you don't. And you can repeat that as many time as you want. Data is another kind of beast. You cannot simply "DROP" a table or even a column back and forth and rely on your backups (if any...). You do no longer want a table? Do not use it anymore, be let it there. You want to migrate your data? make sure you do no loose information. And if you do, duplicate it somewhere if you have to "rollback", or at least choose sensible defaults that can be applied. Data is there to stay, as complete and detailed as it was originally put in your datastore. [0]: http://www.db-main.eu http://www.db-main.eu
  - collyw 13y ago
    Yes, a database is more like a living, constantly evolving thing. I don't think I have ever rolled back a schema change, and I don't think it would be useful to in most cases. Its far better to work out where you went wrong and repair the data. Logging each statement is more likely to be useful than the ability to roll back.
- radiowave 13y ago
  At this point I can barely imagine working with a relational database without using liquibase. It does take some getting used to, and there's a few ways you can get yourself a little bit stuck during development, like when you realize a change you've just applied is wrong, and you dive into the changelog or source files to fix it BEFORE you've asked liquibase to roll back the bad change. Then, when you finally remember to roll back the bad change, liquibase refuses because you've edited the change's definition, and it no longer corresponds to what was actually applied to the database. It's not a very big deal, and if you have more presence of mind than me you might learn not to trip yourself up in that particular way in the first place. In my job I really struggle with getting meaningful test data, and so having the ability to clone the live database and let liquibase roll forward with new changes from a dev branch, try things out, roll back, tweak things and roll forward again is very handy. Also if you're maintaining a large number of stored procedures, it's very handy to keep each of them in a separate file and reference them from your changelog something like: <sqlFile path="..." runOnChange="true"/> - the runOnChange property means that whenever the contents of the file is changed liquibase will automatically reload the procedure. (Though for example, postgres will complain if the change alters the type signature of a function.) I'd recommend using the XML config format (though JSON and YAML are also supported), and use an editor that understands XML schemas. Probably any of the free Jetbrains ones will, certainly PyCharm does. Load the XML schema definitions into the project settings, and you'll get syntax autocompletion and validation for your liquibase changelog files.
yoodenvranx 13y ago
> Every script must start with a integer which must be the next available integer. Based on experience you should not do 01_script_a.py, 02_script_b.py, ... But rather do 010_, 020_, 030_, ... . This way you can squeeze in some initially unaccounted script in the correct location without renaming the others.
- Negitivefrags 13y ago
  It's like BASIC all over again.
  - anon4 13y ago
    Ahaha! Glad I'm not the only one who thought that!
- troels 13y ago
  Better yet, use timestamps. With a sequential identifier, you'll get problems in a distributed team real quick. Rails switched because of this.
- makmanalp 13y ago
  Check out alembic's neat solution to this: http://alembic.readthedocs.org/en/latest/tutorial.html#working-with-branches http://alembic.readthedocs.org/en/latest/tutorial.html#worki... Basically you can solve the problem by fixing the order manually after merging code. And it won't run until you resolve the ordering.
- kelvin0 13y ago
  That would seem like a good idea at first. However if your product is deployed to hundreds of customers each being at various versions of the DB, what happens? Ex: Customer A is at v10, customer B at v20 and customer C at v30. Then you decide for some reason that patch v11 needs to be deployed. What happens to Customers B and C? Do you create 2 new patch, i.e v11 and v40 which have similar changes?
tiquorsj 13y ago
Everyone who does DB update should understand idempotence and how to undo their changes. I like the approach the author provides, but calling it version control is inaccurate. This is change management and it's a completely different discipline with its own body of knowledge.
- bsaul 13y ago
  I failed to understand how undo script would work for big schema modifications ( changing 1-n to n-n or adding intermediate tables). To me, the safest way is definitely a backup / restore if things go wrong ( and you should definitely have backups anyway), but i'm not an expert. Could you elaborate ? Also, the posts mentions ORMs as a solution. Well, clearly i would never want to let anything automaticaly generate and perform migration scripts apart for simple "new nullable column" types of change.
  - nercury 13y ago
    Some ORMs do a decent job generating a correct sequence of DB queries for quite complex schema changes, including relationship changes. However, the process is a bit more manual than you might think. For example, to change from 1-n to n-n you would first generate n-n for first migration, then a manual migration to move data, and then for the final migration you would remove 1-n. The first and last one can be auto-generated by making changes to entities, including the reverse migration. Of course, manual review is absolutely necessary and we write queries ourselves if they involve moving the data.
  - zorlem 13y ago
    You do it by breaking up the potentially destructive changes so you could rollback your deployment to the previous working version. Typically all destructive modification of the schema is done in several passes: eg. in each release/deploy: # copy the data in a new table and add code to place it in both tables # change the code to reference only the new data but still store it in both tables # rename the old table # remove the old table On top of that you have changes that are supposed to be applied before the new code is deployed (eg. creating a table, renaming a table) and after the code is deployed (eg. dropping unused columns). As somebody else mentions in the comments this is more about Change Control than Versioning, but the two things go together to insure that you have as much idempotent and reversible changes as possible. Of course, if you have the resources you can always adopt a "never delete data" policy, so you're always copying instead of modifying the data, making sure you have a way to retract the changes. Clean-up can be done on regular, distant intervals. Using ORM doesn't prevent you from doing a review on the changes that are about to be implemented. ActiveRecord for example, could provide you with a SQL file that will be representing your schema after the ORM is done. Usually the ORM schema changes are executed automatically by your CI upon deployment on Dev, QA and possibly Staging environments (you do have at least two of those, don't you? :) and need manual intervention (maybe to control performance degradation of your live env - locks, increased disk I/O, etc). This way you get plenty of testing of any DB changes and you should be able to spot any problems as early as possible.
mukundmr 13y ago
flywaydb works fine for Java.
- gizmogwai 13y ago
  flywaydb is not even as good as pure SQL scripts. It is mainly designed to be a forward-only tool. I honestly doubt anyone with some database background would recommend it.
  - ecopoesis 13y ago
    Well, I have DB background. I've designed the schema for a moderately large OTA (tingo.com) where I've used Flyway, and I would recommend and use Flyway. Forward only migrations aren't bad, and work pretty well in the real world. As some else pointed out, there are situations (like drop table) that cannot be reversed. As long as each release is backwards compatible with the previous one, you won't ever need to roll back.
    - mercurial 13y ago
      We use it at work, and it works decently. For our development model, we don't need something more complex, and the possibility to have code-based migrations is important (don't know if you can get that with Liquidbase).
pjmlp 13y ago
If I remember correctly there were a few vendors that used to sell such tooling in the 90's.
flurdy 13y ago
Have to admit I use simpler tools such as DbDeploy, Flyway or Play!'s evolutions. They do the trick for smaller databases, but are difficult to retroactively fit in as the OP mentions. A great tip I also received once was to split migrations across releases when they are destructive. ie. if a column is to be removed, first release code that don't use that column, if all okay, rename to column in the next release db migration, and if still ok, in a release later on actually delete the column. If this was done in one release and didn't work your rollback would be difficult. Other defensive actions e.g. when deleting rows of data, is to copy the affected data to a temporary table first before deleting it in the original table(s), and only in a later release when its proven you did not need to rollback should you delete the temporary table. And if for some reason you don't have any automated database versioning, at least create a versions table to manually keep track of changes and dates for some sort of sane auditing.
sandGorgon 13y ago
Does anyone know what is the production-worthy mechanism of working with database versioning in Django, Node and Go ? It always feels strange to me (coming from Rails) that other frameworks treat versioning as optional - I mean Django is getting migrations (not as South, but as part of the default toolkit) only now ? Does that mean there is another way to collaborate and work on database changes ?
- stdbrouw 13y ago
  South has existed and has been in wide use for a very long time, so whether or not it's built into Django doesn't really matter that much. As for Node and Go, I don't see how they could have One System To Rule Them All – this feels like something you take care of at the ORM or framework level. Now, as to your "is there another way?" – during development of an app that hasn't yet reached production, I still prefer extensively using fixtures and just nuking the database on every change, then loading the fixtures back in. (In Django this is a one-liner on the shell, I suppose it's probably similar in other frameworks.) Faster and really helps with unit testing too.
  - ollysb 13y ago
    How do you do deployments to production?
deleted 13y ago
[deleted]
catmanjan 13y ago
Microsoft's MVC database migration system has worked well for me. The package manager handles making the "code behind" the database changes (which is committed to the CVS), and you can revert and update as needs be.
- qntmfred 13y ago
  for .NET work I prefer https://github.com/schambers/fluentmigrator/wiki https://github.com/schambers/fluentmigrator/wiki
nercury 13y ago
Solved by Doctrine2 project way ago. Using a much better timestamp versioning. I actually miss its schema diff and migration tools when I go back to such languages like C# or C++...
- e1g 13y ago
  Agreed. Doctrine Migrations[1] is excellent for managing SQL schema differences and migrations across all major RDBMs (MySQL/PGSQL/MSSQL/Oracle/etc). Both upgrading and downgrading is supported, as well as custom scripts and adjustments. Conceptually there is no reason why it couldn't be integrated into non-PHP environments and be used only as an SQL versioning tool. [1] https://github.com/doctrine/migrations https://github.com/doctrine/migrations
pornel 13y ago
Schema rollback doesn't really work, because once you have data in the database DROP TABLE is not the inverse of CREATE TABLE (going back and forth deletes the data). I've never been in situation where losing production data is acceptable. I've stopped bothering with rollback statements that nobody ever cares to test and nobody will ever use in production. Schema changes are forward-only.
- toxicFork 13y ago
  Assuming you have lots of storage space, instead of drop you could just copy the table across to the archive. Similarly with dropped fields' previous versions, and created fields' current versions in the case of a rollback. But then you will also need to have the logic to create those fields for new rows. It's just easier to have forwards-only changes.
- mediascreen 13y ago
  I don't do rollback statements for non reversible operations either, but for some things like adding/renaming columns or adding/dropping indices it usually works fine.
- radiowave 13y ago
  That's true, but I still test the rollback on all my liquibase change sets. This helps to ensure that I have a straightforward option to back out of my changes solely in the case that something goes badly wrong immediately during deployment into the live environment. But I agree that any regression after that point requires independent consideration, for the reasons you give.
- share_and_enjoy 13y ago
  Agree with the production use case. For me rollback scripts are more useful in development and branching than production rollbacks. Say I'm working on one feature in one branch and I've split a column, then I need to work on a hotfix on another branch which doesn't have that split column. It's really useful being able to switch to that branch and quickly get my database rolled back into a state to match
ibotty 13y ago
every article about db version control should mention sqitch. it's a great tool that works similar to and in conjunction with git. http://sqitch.org/ http://sqitch.org/
- chrisfarms 13y ago
  sqitch is the only approach to version controlled migrations I've found that actually works long term. I wish more people would invest time into learning it so that it would become more commonplace.
- girvo 13y ago
  Oh wow, that's really neat (although their main website won't load for me, I'm checking out the github repo). It's exactly what I've been looking for, cheers!
- vdm 13y ago
  This is why I checked these comments. Thank you.
bni 13y ago
Didn't like the article. Has your typical handwaving about "using an ORM" that will resolve all your complexities. Object Relational Mapping is a problem completely orthogonal to database migrations. I like flywaydb. For "rolling back" you need to use your regular disaster recovery, as migrations will modify data aswell as the schema. Use transaction logging.
dsugarman 13y ago
I would love to see Github come out with some decent postgres versioning
fendale 13y ago
This is an area I have experimented with in large enterprise environments, usually with Oracle databases. In one job I created a process where we used Rails style migrations with some Ruby code I put together. As well as schema migrations, I had it handle applying stored procedures to the database too. Since then I created a little application called dbgeni (database generic installer) and have been using it successfully to create Dev and Test environments in another job. It changes the schema with migrations and applies changed stored procedures too. One difference with dbgeni and other tools, is that dbgeni uses plain SQL files and applies them using the database CLI - ie sqlplus for Oracle, the mysql command for MySql and isql for Sybase. This means that if you take the tool away, the SQL scripts are still useful - that allows me to use the tool for applying changes to dev and test environments, but since the production DBAs don't want to use it, I can just bundle up the same scripts and pass them over. The project is on github https://github.com/sodonnel/dbgeni https://github.com/sodonnel/dbgeni and it installs as a Ruby gem. I have supporting website http://dbgeni.appsintheopen.com http://dbgeni.appsintheopen.com with more details.
danbruc 13y ago
While all theory sounds nice in theory in practice it just fails. If you are only concerned with empty databases during development there is not much to be gained by keeping deltas of different database schema versions - just having one evolving database create script will do the job. Then just grab the version belonging to your application version and you are good to go. Once you have a non-empty database and have to provide data migration scripts everything becomes a one-way street in all but the most trivial cases because updates are destructive. My favorite toy example are firstname and lastname. If you decide to merge them into a single name column (without separator between firstname and lastname) you can not automatically go back because there is ambiguity when ever either firstname or lastname is missing or when at least one of firstname or lastname consists of two or more words. If you do the opposite - split the name into firstname and lastname - and somehow manage to come up with a satisfying heuristic how to perform the split, it is easy to go back by just merging firstname and lastname again. But then going forward again will not return you to the initial state in all the cases where your heuristic fails. Currently I think the way to go may be using a highly normalized database schema - like 5th or 6th normal form - and perform only non-destructive schema evolution. I am not yet sure how this will turn out in practice with respect to limitations imposed on the possibilities to evolve the schema and how bad it may become because of abandoned database schema artifacts and the contained data. Anyway I think it looks promising and after doing some more research, thinking and prototyping I will most likely give it a try in an upcoming project.
- collyw 13y ago
  Or you could just use a schema-less database like MongoDB </irony>
  - zaidf 13y ago
    And introduce yourself to a new set of problems? Like the possibility of some part of the program saving first name and last name as one field and other parts as one field.
    - collyw 13y ago
derekp7 13y ago
I've been struggling with how to get an Oracle database contents under Git control. Our developers use an older rapid development environment, in which the front end tool stores its form layout, triggers, and source code all in the database. It doesn't provide an option to develop out of regular text files. So there are a couple approaches I could take: 1) Use triggers so that every time a GIT command is run, it either imports or exports the given database table. But this takes simple, fast operations and makes them expensive. 2) Write a Fuse plugin that can expose a database schema as a filesystem object, and have a git checkout trigger that mount it in the appropriate place. Has anyone already done either of these two?
binarymax 13y ago
As others have said and the author mentioned with the 3Tb example - it is the data that is the tricky part. Rather, the tricky part is state. Code (and schema) versioning is fundamentally easy because it is internally stateless. Once you add external state to the mix all bets are off. I really wish there was a silver bullet but there is not. If you can solve the issue of versioned persistence of large amounts of state then you will solve a lot of problems in computer science.
- aragot 13y ago
  Exact: Stateless! Sad that we still can't dump a database to a code file, unique and canonical. Maybe one way would be if the deployment script could say "We're missing column X and Y" from comparing a description of the db to the actual one. If you deploy an older version, you still get the message but choose not to apply the change if it's destructive.
emmelaich 13y ago
Here you go. It's easy with a bit of Category Theory. Just implement something along the lines of David Spivak's "On The Relational Foundations Of Functorial Data Migration". http://arxiv.org/abs/1212.5303 Original motivation, from v1 of the paper: I asked whether the difficulty in changing data types to allow for longer integers was simply a storage issue or something more fundamental. They said that it was more fundamental, and in fact that my question showed how little I knew about the horrors of using databases in the real world!
jdc0589 13y ago
This is something EVERYONE struggles with at some point, and everyone has a different solution that works. It's still alpha (I'm introducing breaking changes soon), but I have been working on Mite (https://github.com/jdc0589/mite-node https://github.com/jdc0589/mite-node) for a few weeks now; its a re-implementation of an internal tool we used in the past. The documentation is incomplete/out-of date though; it will be getting some love soon. Mite's goal is kind of similar to sqitch, but it aims to be a little simpler; very little configurations and migrations are plain old sql
dgrnbrg 13y ago
Another interesting solution is to use a functional database, like Datomic. Since the database looks like a giant timeseries, you have an implicit versioning built into the database.
share_and_enjoy 13y ago
I've used the manual migration script approaches before (Active Record, DBDeploy), which are really reliable providing you use them religiously to perform all changes. However it's a lot of work and it's easy to accidentally make a change directly to the local dev DB and then either forget to commit a change script or commit a change that isn't quite what you did. At the other end the automated comparison/migration tools are much more convenient. However they just can't work in 100% of cases, for example thinking a rename is a drop and a create, or splitting data. I've always ended up with a hybrid approach in the past
languagehacker 13y ago
I wrote about this idea few years ago, from a non-ORM perspective: http://robertelwell.info/blog/future-proof-db-changelog/ http://robertelwell.info/blog/future-proof-db-changelog/
sethreno 13y ago
I prefer to think of db version control and db migrations as two separate things. Version control achieved by keeping the SQL scripts to create the database in your VCS along with the code that depends on it. Migration scripts are created later as a pre-deployment step. I prefer not to store these scripts in VCS because in my experience they are typically run once and never used again. There are several tools that can generate the migration scripts by comparing two schemas e.g. dev-->prod. I wrote an open source command line tool that can be used to create VCS friendly create scripts from a SQL Server database. It's on github at https://github.com/sethreno/schemazen#schema-zen---script-and-create-sql-server-objects-quickly https://github.com/sethreno/schemazen#schema-zen---script-an... Also, here are a few tools that I've used to generate migrations scripts: * http://opendbiff.codeplex.com/ http://opendbiff.codeplex.com/ * http://www.sqldbtools.com/Tools.aspx?ProductId=1 http://www.sqldbtools.com/Tools.aspx?ProductId=1 * http://www.red-gate.com/products/sql-development/sql-compare/ http://www.red-gate.com/products/sql-development/sql-compare...

72 points by jlemoine 13y ago

Very interested to see the link to Liquibase[0] in that article. Must check it out. Does anyone have experience with that sort of tool that they'd like to share, either good or bad? [0]: http://www.liquibase.org/ http://www.liquibase.org/

gizmogwai 13y ago
I do use it. Primarily because it more widespread in the Java world than other competitors,and more easy, in the syntax, to start with. You might also be interested in DB-Main [0] But database migration has little to do with version control of code. With code, when you switch from a version to another, either you have a bug^H^H feature, either you don't. And you can repeat that as many time as you want. Data is another kind of beast. You cannot simply "DROP" a table or even a column back and forth and rely on your backups (if any...). You do no longer want a table? Do not use it anymore, be let it there. You want to migrate your data? make sure you do no loose information. And if you do, duplicate it somewhere if you have to "rollback", or at least choose sensible defaults that can be applied. Data is there to stay, as complete and detailed as it was originally put in your datastore. [0]: http://www.db-main.eu http://www.db-main.eu
- collyw 13y ago
  Yes, a database is more like a living, constantly evolving thing. I don't think I have ever rolled back a schema change, and I don't think it would be useful to in most cases. Its far better to work out where you went wrong and repair the data. Logging each statement is more likely to be useful than the ability to roll back.
radiowave 13y ago
At this point I can barely imagine working with a relational database without using liquibase. It does take some getting used to, and there's a few ways you can get yourself a little bit stuck during development, like when you realize a change you've just applied is wrong, and you dive into the changelog or source files to fix it BEFORE you've asked liquibase to roll back the bad change. Then, when you finally remember to roll back the bad change, liquibase refuses because you've edited the change's definition, and it no longer corresponds to what was actually applied to the database. It's not a very big deal, and if you have more presence of mind than me you might learn not to trip yourself up in that particular way in the first place. In my job I really struggle with getting meaningful test data, and so having the ability to clone the live database and let liquibase roll forward with new changes from a dev branch, try things out, roll back, tweak things and roll forward again is very handy. Also if you're maintaining a large number of stored procedures, it's very handy to keep each of them in a separate file and reference them from your changelog something like: <sqlFile path="..." runOnChange="true"/> - the runOnChange property means that whenever the contents of the file is changed liquibase will automatically reload the procedure. (Though for example, postgres will complain if the change alters the type signature of a function.) I'd recommend using the XML config format (though JSON and YAML are also supported), and use an editor that understands XML schemas. Probably any of the free Jetbrains ones will, certainly PyCharm does. Load the XML schema definitions into the project settings, and you'll get syntax autocompletion and validation for your liquibase changelog files.

yoodenvranx 13y ago

> Every script must start with a integer which must be the next available integer. Based on experience you should not do 01_script_a.py, 02_script_b.py, ... But rather do 010_, 020_, 030_, ... . This way you can squeeze in some initially unaccounted script in the correct location without renaming the others.

Negitivefrags 13y ago
It's like BASIC all over again.
- anon4 13y ago
  Ahaha! Glad I'm not the only one who thought that!
troels 13y ago
Better yet, use timestamps. With a sequential identifier, you'll get problems in a distributed team real quick. Rails switched because of this.
makmanalp 13y ago
Check out alembic's neat solution to this: http://alembic.readthedocs.org/en/latest/tutorial.html#working-with-branches http://alembic.readthedocs.org/en/latest/tutorial.html#worki... Basically you can solve the problem by fixing the order manually after merging code. And it won't run until you resolve the ordering.
kelvin0 13y ago
That would seem like a good idea at first. However if your product is deployed to hundreds of customers each being at various versions of the DB, what happens? Ex: Customer A is at v10, customer B at v20 and customer C at v30. Then you decide for some reason that patch v11 needs to be deployed. What happens to Customers B and C? Do you create 2 new patch, i.e v11 and v40 which have similar changes?

tiquorsj 13y ago

Everyone who does DB update should understand idempotence and how to undo their changes. I like the approach the author provides, but calling it version control is inaccurate. This is change management and it's a completely different discipline with its own body of knowledge.

bsaul 13y ago
I failed to understand how undo script would work for big schema modifications ( changing 1-n to n-n or adding intermediate tables). To me, the safest way is definitely a backup / restore if things go wrong ( and you should definitely have backups anyway), but i'm not an expert. Could you elaborate ? Also, the posts mentions ORMs as a solution. Well, clearly i would never want to let anything automaticaly generate and perform migration scripts apart for simple "new nullable column" types of change.
- nercury 13y ago
  Some ORMs do a decent job generating a correct sequence of DB queries for quite complex schema changes, including relationship changes. However, the process is a bit more manual than you might think. For example, to change from 1-n to n-n you would first generate n-n for first migration, then a manual migration to move data, and then for the final migration you would remove 1-n. The first and last one can be auto-generated by making changes to entities, including the reverse migration. Of course, manual review is absolutely necessary and we write queries ourselves if they involve moving the data.
- zorlem 13y ago
  You do it by breaking up the potentially destructive changes so you could rollback your deployment to the previous working version. Typically all destructive modification of the schema is done in several passes: eg. in each release/deploy: # copy the data in a new table and add code to place it in both tables # change the code to reference only the new data but still store it in both tables # rename the old table # remove the old table On top of that you have changes that are supposed to be applied before the new code is deployed (eg. creating a table, renaming a table) and after the code is deployed (eg. dropping unused columns). As somebody else mentions in the comments this is more about Change Control than Versioning, but the two things go together to insure that you have as much idempotent and reversible changes as possible. Of course, if you have the resources you can always adopt a "never delete data" policy, so you're always copying instead of modifying the data, making sure you have a way to retract the changes. Clean-up can be done on regular, distant intervals. Using ORM doesn't prevent you from doing a review on the changes that are about to be implemented. ActiveRecord for example, could provide you with a SQL file that will be representing your schema after the ORM is done. Usually the ORM schema changes are executed automatically by your CI upon deployment on Dev, QA and possibly Staging environments (you do have at least two of those, don't you? :) and need manual intervention (maybe to control performance degradation of your live env - locks, increased disk I/O, etc). This way you get plenty of testing of any DB changes and you should be able to spot any problems as early as possible.

mukundmr 13y ago

flywaydb works fine for Java.

gizmogwai 13y ago
flywaydb is not even as good as pure SQL scripts. It is mainly designed to be a forward-only tool. I honestly doubt anyone with some database background would recommend it.
- ecopoesis 13y ago
  Well, I have DB background. I've designed the schema for a moderately large OTA (tingo.com) where I've used Flyway, and I would recommend and use Flyway. Forward only migrations aren't bad, and work pretty well in the real world. As some else pointed out, there are situations (like drop table) that cannot be reversed. As long as each release is backwards compatible with the previous one, you won't ever need to roll back.
  - mercurial 13y ago
    We use it at work, and it works decently. For our development model, we don't need something more complex, and the possibility to have code-based migrations is important (don't know if you can get that with Liquidbase).

pjmlp 13y ago

If I remember correctly there were a few vendors that used to sell such tooling in the 90's.

flurdy 13y ago

Have to admit I use simpler tools such as DbDeploy, Flyway or Play!'s evolutions. They do the trick for smaller databases, but are difficult to retroactively fit in as the OP mentions. A great tip I also received once was to split migrations across releases when they are destructive. ie. if a column is to be removed, first release code that don't use that column, if all okay, rename to column in the next release db migration, and if still ok, in a release later on actually delete the column. If this was done in one release and didn't work your rollback would be difficult. Other defensive actions e.g. when deleting rows of data, is to copy the affected data to a temporary table first before deleting it in the original table(s), and only in a later release when its proven you did not need to rollback should you delete the temporary table. And if for some reason you don't have any automated database versioning, at least create a versions table to manually keep track of changes and dates for some sort of sane auditing.

sandGorgon 13y ago

Does anyone know what is the production-worthy mechanism of working with database versioning in Django, Node and Go ? It always feels strange to me (coming from Rails) that other frameworks treat versioning as optional - I mean Django is getting migrations (not as South, but as part of the default toolkit) only now ? Does that mean there is another way to collaborate and work on database changes ?

stdbrouw 13y ago
South has existed and has been in wide use for a very long time, so whether or not it's built into Django doesn't really matter that much. As for Node and Go, I don't see how they could have One System To Rule Them All – this feels like something you take care of at the ORM or framework level. Now, as to your "is there another way?" – during development of an app that hasn't yet reached production, I still prefer extensively using fixtures and just nuking the database on every change, then loading the fixtures back in. (In Django this is a one-liner on the shell, I suppose it's probably similar in other frameworks.) Faster and really helps with unit testing too.
- ollysb 13y ago
  How do you do deployments to production?

deleted 13y ago

[deleted]

catmanjan 13y ago

Microsoft's MVC database migration system has worked well for me. The package manager handles making the "code behind" the database changes (which is committed to the CVS), and you can revert and update as needs be.

qntmfred 13y ago
for .NET work I prefer https://github.com/schambers/fluentmigrator/wiki https://github.com/schambers/fluentmigrator/wiki

nercury 13y ago

Solved by Doctrine2 project way ago. Using a much better timestamp versioning. I actually miss its schema diff and migration tools when I go back to such languages like C# or C++...

e1g 13y ago
Agreed. Doctrine Migrations[1] is excellent for managing SQL schema differences and migrations across all major RDBMs (MySQL/PGSQL/MSSQL/Oracle/etc). Both upgrading and downgrading is supported, as well as custom scripts and adjustments. Conceptually there is no reason why it couldn't be integrated into non-PHP environments and be used only as an SQL versioning tool. [1] https://github.com/doctrine/migrations https://github.com/doctrine/migrations

pornel 13y ago

Schema rollback doesn't really work, because once you have data in the database DROP TABLE is not the inverse of CREATE TABLE (going back and forth deletes the data). I've never been in situation where losing production data is acceptable. I've stopped bothering with rollback statements that nobody ever cares to test and nobody will ever use in production. Schema changes are forward-only.

toxicFork 13y ago
Assuming you have lots of storage space, instead of drop you could just copy the table across to the archive. Similarly with dropped fields' previous versions, and created fields' current versions in the case of a rollback. But then you will also need to have the logic to create those fields for new rows. It's just easier to have forwards-only changes.
mediascreen 13y ago
I don't do rollback statements for non reversible operations either, but for some things like adding/renaming columns or adding/dropping indices it usually works fine.
radiowave 13y ago
That's true, but I still test the rollback on all my liquibase change sets. This helps to ensure that I have a straightforward option to back out of my changes solely in the case that something goes badly wrong immediately during deployment into the live environment. But I agree that any regression after that point requires independent consideration, for the reasons you give.
share_and_enjoy 13y ago
Agree with the production use case. For me rollback scripts are more useful in development and branching than production rollbacks. Say I'm working on one feature in one branch and I've split a column, then I need to work on a hotfix on another branch which doesn't have that split column. It's really useful being able to switch to that branch and quickly get my database rolled back into a state to match

ibotty 13y ago

every article about db version control should mention sqitch. it's a great tool that works similar to and in conjunction with git. http://sqitch.org/ http://sqitch.org/

chrisfarms 13y ago
sqitch is the only approach to version controlled migrations I've found that actually works long term. I wish more people would invest time into learning it so that it would become more commonplace.
girvo 13y ago
Oh wow, that's really neat (although their main website won't load for me, I'm checking out the github repo). It's exactly what I've been looking for, cheers!
vdm 13y ago
This is why I checked these comments. Thank you.

bni 13y ago

Didn't like the article. Has your typical handwaving about "using an ORM" that will resolve all your complexities. Object Relational Mapping is a problem completely orthogonal to database migrations. I like flywaydb. For "rolling back" you need to use your regular disaster recovery, as migrations will modify data aswell as the schema. Use transaction logging.

dsugarman 13y ago

I would love to see Github come out with some decent postgres versioning

fendale 13y ago

This is an area I have experimented with in large enterprise environments, usually with Oracle databases. In one job I created a process where we used Rails style migrations with some Ruby code I put together. As well as schema migrations, I had it handle applying stored procedures to the database too. Since then I created a little application called dbgeni (database generic installer) and have been using it successfully to create Dev and Test environments in another job. It changes the schema with migrations and applies changed stored procedures too. One difference with dbgeni and other tools, is that dbgeni uses plain SQL files and applies them using the database CLI - ie sqlplus for Oracle, the mysql command for MySql and isql for Sybase. This means that if you take the tool away, the SQL scripts are still useful - that allows me to use the tool for applying changes to dev and test environments, but since the production DBAs don't want to use it, I can just bundle up the same scripts and pass them over. The project is on github https://github.com/sodonnel/dbgeni https://github.com/sodonnel/dbgeni and it installs as a Ruby gem. I have supporting website http://dbgeni.appsintheopen.com http://dbgeni.appsintheopen.com with more details.

danbruc 13y ago

While all theory sounds nice in theory in practice it just fails. If you are only concerned with empty databases during development there is not much to be gained by keeping deltas of different database schema versions - just having one evolving database create script will do the job. Then just grab the version belonging to your application version and you are good to go. Once you have a non-empty database and have to provide data migration scripts everything becomes a one-way street in all but the most trivial cases because updates are destructive. My favorite toy example are firstname and lastname. If you decide to merge them into a single name column (without separator between firstname and lastname) you can not automatically go back because there is ambiguity when ever either firstname or lastname is missing or when at least one of firstname or lastname consists of two or more words. If you do the opposite - split the name into firstname and lastname - and somehow manage to come up with a satisfying heuristic how to perform the split, it is easy to go back by just merging firstname and lastname again. But then going forward again will not return you to the initial state in all the cases where your heuristic fails. Currently I think the way to go may be using a highly normalized database schema - like 5th or 6th normal form - and perform only non-destructive schema evolution. I am not yet sure how this will turn out in practice with respect to limitations imposed on the possibilities to evolve the schema and how bad it may become because of abandoned database schema artifacts and the contained data. Anyway I think it looks promising and after doing some more research, thinking and prototyping I will most likely give it a try in an upcoming project.

collyw 13y ago
Or you could just use a schema-less database like MongoDB </irony>
- zaidf 13y ago
  And introduce yourself to a new set of problems? Like the possibility of some part of the program saving first name and last name as one field and other parts as one field.
  - collyw 13y ago

derekp7 13y ago

I've been struggling with how to get an Oracle database contents under Git control. Our developers use an older rapid development environment, in which the front end tool stores its form layout, triggers, and source code all in the database. It doesn't provide an option to develop out of regular text files. So there are a couple approaches I could take: 1) Use triggers so that every time a GIT command is run, it either imports or exports the given database table. But this takes simple, fast operations and makes them expensive. 2) Write a Fuse plugin that can expose a database schema as a filesystem object, and have a git checkout trigger that mount it in the appropriate place. Has anyone already done either of these two?

binarymax 13y ago

As others have said and the author mentioned with the 3Tb example - it is the data that is the tricky part. Rather, the tricky part is state. Code (and schema) versioning is fundamentally easy because it is internally stateless. Once you add external state to the mix all bets are off. I really wish there was a silver bullet but there is not. If you can solve the issue of versioned persistence of large amounts of state then you will solve a lot of problems in computer science.

aragot 13y ago
Exact: Stateless! Sad that we still can't dump a database to a code file, unique and canonical. Maybe one way would be if the deployment script could say "We're missing column X and Y" from comparing a description of the db to the actual one. If you deploy an older version, you still get the message but choose not to apply the change if it's destructive.

emmelaich 13y ago

Here you go. It's easy with a bit of Category Theory. Just implement something along the lines of David Spivak's "On The Relational Foundations Of Functorial Data Migration". http://arxiv.org/abs/1212.5303 Original motivation, from v1 of the paper: I asked whether the difficulty in changing data types to allow for longer integers was simply a storage issue or something more fundamental. They said that it was more fundamental, and in fact that my question showed how little I knew about the horrors of using databases in the real world!

jdc0589 13y ago

This is something EVERYONE struggles with at some point, and everyone has a different solution that works. It's still alpha (I'm introducing breaking changes soon), but I have been working on Mite (https://github.com/jdc0589/mite-node https://github.com/jdc0589/mite-node) for a few weeks now; its a re-implementation of an internal tool we used in the past. The documentation is incomplete/out-of date though; it will be getting some love soon. Mite's goal is kind of similar to sqitch, but it aims to be a little simpler; very little configurations and migrations are plain old sql

dgrnbrg 13y ago

Another interesting solution is to use a functional database, like Datomic. Since the database looks like a giant timeseries, you have an implicit versioning built into the database.

share_and_enjoy 13y ago

I've used the manual migration script approaches before (Active Record, DBDeploy), which are really reliable providing you use them religiously to perform all changes. However it's a lot of work and it's easy to accidentally make a change directly to the local dev DB and then either forget to commit a change script or commit a change that isn't quite what you did. At the other end the automated comparison/migration tools are much more convenient. However they just can't work in 100% of cases, for example thinking a rename is a drop and a create, or splitting data. I've always ended up with a hybrid approach in the past

languagehacker 13y ago

I wrote about this idea few years ago, from a non-ORM perspective: http://robertelwell.info/blog/future-proof-db-changelog/ http://robertelwell.info/blog/future-proof-db-changelog/

sethreno 13y ago

I prefer to think of db version control and db migrations as two separate things. Version control achieved by keeping the SQL scripts to create the database in your VCS along with the code that depends on it. Migration scripts are created later as a pre-deployment step. I prefer not to store these scripts in VCS because in my experience they are typically run once and never used again. There are several tools that can generate the migration scripts by comparing two schemas e.g. dev-->prod. I wrote an open source command line tool that can be used to create VCS friendly create scripts from a SQL Server database. It's on github at https://github.com/sethreno/schemazen#schema-zen---script-and-create-sql-server-objects-quickly https://github.com/sethreno/schemazen#schema-zen---script-an... Also, here are a few tools that I've used to generate migrations scripts: * http://opendbiff.codeplex.com/ http://opendbiff.codeplex.com/ * http://www.sqldbtools.com/Tools.aspx?ProductId=1 http://www.sqldbtools.com/Tools.aspx?ProductId=1 * http://www.red-gate.com/products/sql-development/sql-compare/ http://www.red-gate.com/products/sql-development/sql-compare...