6 ms·

Sqitch - Sane database change management

92 points by dmmalam 11y ago

eddd 11y ago
I tried to use it once. It looked promising, but i remember I had some problems with testing migrations.
glogla 11y ago
Squitch looks interesting, but I can't figure out how to use it to make production deployments. In some environments, you can play with your dev and test environment, but the production is someone else's playground, and you just have to give them set of sql scripts to deploy. Can something like that be done with squitch?
- peterwwillis 11y ago
  I might be missing something, but all the code being executed just seems to be running some .sql files on the database (deploy/.sql, revert/.sql, verify/*.sql). It seems to just be an interface to loading these sql files, making it easier to (for example) revert a set of changes in order. So, you could just use this tool to manage and test your chances, and then manually go through and give instructions to prod engineers on which files to deploy and how. Git does seem to be complicating things, though.
ljoshua 11y ago
Nice, it feel similar to how Yii does database migrations but with a lot more packed in. What I'd really like to see is a open source or free tool accomplishing similar things to what RedGate SQL Compare does. Now that's a powerful tool and certainly worth its cost, but in some situations a limited option due to its support for only SQL Server or where funds are lacking.
- zamalek 11y ago
  What I'd like to see is a portable Data Dude (a.k.a. Visual Studio Data Projects). You basically write your database as a whole bunch of CREATE scripts, Data Dude then does a full parse over that (with all the static compilation bells and whistles - including some static analysis), diffs it against your actual DB (or a previous version, if you keep the 'libs' around) and then spits out the relevant diff script. Obviously you'll sometimes you need to manually pre-script/post-script to upgrade data. It basically makes database dev behave exactly like your average imperative language. The current mainstream DB landscape is ridiculously absurd. We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? We the heck are we committing history (migrations) into a stores that is already historical (SCM)?
  - kyllo 11y ago
    Persistent (a Haskell database library) does something like this, in a highly declarative way. You just declare your tables and their columns as Haskell data types, and when it compiles your application it automatically migrates your database if it can do so non-destructively. If your change would destroy data or requires a default value for a new non-null column or something, it will give you an error message that tells you what you need to do. It's by far the best experience I've had working with a database in a programming language. Only problem is that it's Haskell specific, not portable to other languages.
  - jacques_chester 11y ago
    > We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? In-memory data structures are ephemeral. You "migrate" by restarting or replacing code entirely. If you have an on-disk format that basically acts as a memory snapshot, someone writes a migration script, which is usually not recognised as such. Databases seem weird and icky and mismatched, but it's because of the D in ACID. Durable data has momentum, more force is required to change its vector than the mist that lives in memory.
  - VLM 11y ago
    If I read you correctly (quite possibly not) you're proposing something like the output of "mysqldump -d" be compared to some golden schema dump and then implement something like RANCID for routers such that changes in the DB schema get emailed or otherwise loudly announced and tracked vs the golden schema. I do that. A very small shell script can take care of irrelevant diff outputs (timestamps and default minimum autoincrement numbers the like) On a side issue (kind of), something not covered in the squitch tutorial is if you git-flow then you probably should lay down some discipline that the exact name of your feature branch is the squitch change name, just to keep insanity levels down. Also I've done things like a git-flow feature branch results in a completely new test table name being created and there needs to be discipline that the test table name is "normalTableName-gitflowFeatureBranchName" or some kind of site standard. Its only kind of a side issue, in that your proposed Data Dude app probably should cooperate with git-flow workflows. > so why the heck are we writing migrations for our DBs Sometimes dumb indexing decisions can take hours / days to apply and kill the NAS, run the system out of storage, etc. I've done that and its not entertaining, so its less painful to do "real stuff" by hand, at least to production machines. Tend to find some hilarious scaling mistakes. Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes.
mrbig4545 11y ago
I used to use sqitch. It drive me mad, it wants to do too much. I have got for version control, I don't need sqitch to do it as well. And it doesn't play nice with other developers. If you add migration a in one branch, and someone adds migration b in another, then they merge theirs before yours, you're in for a world of pain whne you try to delpoy yours. In the end I wrote a replacement that did was I needed in under 100 lines of perl. DB management should be simple. No point over complicating it Not forgetting the guy who writes it is not that friendly, we sent a patch to change the default answer to revert to no, instead of yes. First off he said it was useless, then after some prodding over several months he copied it in and committed it in his name, no mention of where it came from. Real classy
- eli 11y ago
  Are there other database migration systems that make dealing with migrations in conflicting branches really easy?
  - mrbig4545 11y ago
    I don't know about conflicting branches, but sqitch didn't like out of order migrations, even if they have nothing to do with each other. The authors response is for the developer with the issue to revert back to a sane point and then redeploy, which is not suitable if you've spent a hour filling that table with data!
  - jcoby 11y ago
    It's been my experience that simpler is better. At least when it comes to teams up to around 12-15 people. After that politics will dictate how you migrate. Adopting three rules has pretty much made migrations a non issue: 1. migrations should be timestamped, tracked, and applied in time order (rails-style migrations; this allows for the migrator to determine which migrations have not been applied regardless of when they get added to the run list) 2. migrations should be committed separate from logic changes (this way you can bring in migration a if migration b depends on it even when feature a isn't ready to be merged) 3) migrations are always forward. It's great to be able to revert during development but production is always forward. If a migration fails it always requires investigation; there is no automatic recovery. Using the above rules you can put together a migration system in any language in about an hour by simply storing files that issue DDL/SQL directly. With a few hours more work you can abstract it out and be cross-database but that's rarely worth it. I've tried sequentially versioned migrations and they are a major bear to work with when branching and multiple developers are involved. You end up having one guy be the "database migration guy" and responsible for keeping everything in order. The intelligent migrators that do diffs of the schema vs the db always have issues plus the very real potential to lose data.
UseStrict 11y ago
So far Sqitch has been an absolute nightmare for production deployments, particularly if you have to change something in history before a tag occurs. Not to mention merging, which invariably always generates a conflict in the plan file.
chrismaeda 11y ago
We're in the midst of adopting Liquibase to support our schema on 3 different db's (Oracle, SQL Server, MySQL). Interested in hearing about any experiences or gotchas with this tool.
- fusiongyro 11y ago
  I have been using it for the last five or so years and absolutely love it. I would recommend that you: - Make sure every code feature gets its own Liquibase migration file - Use the XML format—stronger IDE integration, and it's been around longer - If possible, use their migrations instead of raw SQL, since you will get the rollback functionality "for free." - Integrate it with the "deploy" phase of your Maven build, if applicable - Use the "reverse engineer" feature when you get close to deploying it the first time, to ensure that you have a from-scratch workflow that works and produces identical schemas You will get merge conflicts on the file that lists the migrations to apply, but they are obvious and trivial to fix. Pitfalls. The only one I've noticed is that it can be confused about Postgres types, but I have been able to the "rewrite" functionality to "fix them in post." I tried the YAML format and found that I missed the completion in the IDE that the XML format has, thanks to the schema. Otherwise, it works fine, but I like the validity angle. You can set up some migrations to be run every time. I usually set up the GRANTs that way, so I know the permissions are good right before a code deploy. Also, on those migrations, use an empty <rollback> clause so it doesn't prevent you from rolling back (you can do that on any migration to make Liquibase ignore it during a rollback operation, so all my data-manipulation migrations get one too).
dalacv 11y ago
I tried to use it. But it was too cumbersome to manage my migrations this way. I love the way that Rails handles database migrations.
shoq 11y ago
We're currently looking at http://flywaydb.org/ http://flywaydb.org/. Does anyone care to share some expiriences?
- enricopulatzo 11y ago
  Especially if your build involves Maven, FlywayDB is a very good choice if you want to use native SQL to manage your schema. I was originally quite bullish on FlywayDB until I started looking into Liquibase by way of Dropwizard. I'm personally not quite convinced of the need for native SQL scripting for schema management, as I often like to work in H2 for dev and something else for production. However, if you primarily want to use JDBC + native SQL to manage your schema, Flyway is quite powerful and easy to fold into your build process.
- hackerboos 11y ago
  Coming from Django and Rails Flyway was missing support for rollbacks. Everything else worked well mind...
frugalmail 11y ago
I really prefer git + Liquibase. I personally like the database abstraction and the refactorings.
ahachete 11y ago
In the PostgreSQL world, there are extensions (http://www.postgresql.org/docs/9.4/static/extend-extensions.html http://www.postgresql.org/docs/9.4/static/extend-extensions....). Since extensions are self-contained groups of database objects (i.e., code), are versioned and have some (basic) form of dependency management, they can be used for simple deployment mechanisms. They also support "SQL diffs" as "migration scripts" from version to version, and the extension mechanism applies them as needed. With all this, ot is not difficult to build a simple infrastructure for code packaging and deployment. That's what we do in our company.
- dmmalam 11y ago
  Would love to see a blog post detailing this.
  - ahachete 11y ago
    Hmmmmmmm ok, good idea. Adding to our TODO list, hope to get it out soon ;P

92 points by dmmalam 11y ago

eddd 11y ago

I tried to use it once. It looked promising, but i remember I had some problems with testing migrations.

Squitch looks interesting, but I can't figure out how to use it to make production deployments. In some environments, you can play with your dev and test environment, but the production is someone else's playground, and you just have to give them set of sql scripts to deploy. Can something like that be done with squitch?

peterwwillis 11y ago
I might be missing something, but all the code being executed just seems to be running some .sql files on the database (deploy/.sql, revert/.sql, verify/*.sql). It seems to just be an interface to loading these sql files, making it easier to (for example) revert a set of changes in order. So, you could just use this tool to manage and test your chances, and then manually go through and give instructions to prod engineers on which files to deploy and how. Git does seem to be complicating things, though.

ljoshua 11y ago

Nice, it feel similar to how Yii does database migrations but with a lot more packed in. What I'd really like to see is a open source or free tool accomplishing similar things to what RedGate SQL Compare does. Now that's a powerful tool and certainly worth its cost, but in some situations a limited option due to its support for only SQL Server or where funds are lacking.

zamalek 11y ago
What I'd like to see is a portable Data Dude (a.k.a. Visual Studio Data Projects). You basically write your database as a whole bunch of CREATE scripts, Data Dude then does a full parse over that (with all the static compilation bells and whistles - including some static analysis), diffs it against your actual DB (or a previous version, if you keep the 'libs' around) and then spits out the relevant diff script. Obviously you'll sometimes you need to manually pre-script/post-script to upgrade data. It basically makes database dev behave exactly like your average imperative language. The current mainstream DB landscape is ridiculously absurd. We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? We the heck are we committing history (migrations) into a stores that is already historical (SCM)?
- kyllo 11y ago
  Persistent (a Haskell database library) does something like this, in a highly declarative way. You just declare your tables and their columns as Haskell data types, and when it compiles your application it automatically migrates your database if it can do so non-destructively. If your change would destroy data or requires a default value for a new non-null column or something, it will give you an error message that tells you what you need to do. It's by far the best experience I've had working with a database in a programming language. Only problem is that it's Haskell specific, not portable to other languages.
- jacques_chester 11y ago
  > We don't write migrations for our C++, so why the heck are we writing migrations for our DBs? In-memory data structures are ephemeral. You "migrate" by restarting or replacing code entirely. If you have an on-disk format that basically acts as a memory snapshot, someone writes a migration script, which is usually not recognised as such. Databases seem weird and icky and mismatched, but it's because of the D in ACID. Durable data has momentum, more force is required to change its vector than the mist that lives in memory.
- VLM 11y ago
  If I read you correctly (quite possibly not) you're proposing something like the output of "mysqldump -d" be compared to some golden schema dump and then implement something like RANCID for routers such that changes in the DB schema get emailed or otherwise loudly announced and tracked vs the golden schema. I do that. A very small shell script can take care of irrelevant diff outputs (timestamps and default minimum autoincrement numbers the like) On a side issue (kind of), something not covered in the squitch tutorial is if you git-flow then you probably should lay down some discipline that the exact name of your feature branch is the squitch change name, just to keep insanity levels down. Also I've done things like a git-flow feature branch results in a completely new test table name being created and there needs to be discipline that the test table name is "normalTableName-gitflowFeatureBranchName" or some kind of site standard. Its only kind of a side issue, in that your proposed Data Dude app probably should cooperate with git-flow workflows. > so why the heck are we writing migrations for our DBs Sometimes dumb indexing decisions can take hours / days to apply and kill the NAS, run the system out of storage, etc. I've done that and its not entertaining, so its less painful to do "real stuff" by hand, at least to production machines. Tend to find some hilarious scaling mistakes. Very few source code diffs scale the source code length itself exponentially or something equally naughty so its always safe to commit/pull source code changes and automerge them, not so much schema changes.

mrbig4545 11y ago

I used to use sqitch. It drive me mad, it wants to do too much. I have got for version control, I don't need sqitch to do it as well. And it doesn't play nice with other developers. If you add migration a in one branch, and someone adds migration b in another, then they merge theirs before yours, you're in for a world of pain whne you try to delpoy yours. In the end I wrote a replacement that did was I needed in under 100 lines of perl. DB management should be simple. No point over complicating it Not forgetting the guy who writes it is not that friendly, we sent a patch to change the default answer to revert to no, instead of yes. First off he said it was useless, then after some prodding over several months he copied it in and committed it in his name, no mention of where it came from. Real classy

eli 11y ago
Are there other database migration systems that make dealing with migrations in conflicting branches really easy?
- mrbig4545 11y ago
  I don't know about conflicting branches, but sqitch didn't like out of order migrations, even if they have nothing to do with each other. The authors response is for the developer with the issue to revert back to a sane point and then redeploy, which is not suitable if you've spent a hour filling that table with data!
- jcoby 11y ago
  It's been my experience that simpler is better. At least when it comes to teams up to around 12-15 people. After that politics will dictate how you migrate. Adopting three rules has pretty much made migrations a non issue: 1. migrations should be timestamped, tracked, and applied in time order (rails-style migrations; this allows for the migrator to determine which migrations have not been applied regardless of when they get added to the run list) 2. migrations should be committed separate from logic changes (this way you can bring in migration a if migration b depends on it even when feature a isn't ready to be merged) 3) migrations are always forward. It's great to be able to revert during development but production is always forward. If a migration fails it always requires investigation; there is no automatic recovery. Using the above rules you can put together a migration system in any language in about an hour by simply storing files that issue DDL/SQL directly. With a few hours more work you can abstract it out and be cross-database but that's rarely worth it. I've tried sequentially versioned migrations and they are a major bear to work with when branching and multiple developers are involved. You end up having one guy be the "database migration guy" and responsible for keeping everything in order. The intelligent migrators that do diffs of the schema vs the db always have issues plus the very real potential to lose data.

UseStrict 11y ago

So far Sqitch has been an absolute nightmare for production deployments, particularly if you have to change something in history before a tag occurs. Not to mention merging, which invariably always generates a conflict in the plan file.

chrismaeda 11y ago

We're in the midst of adopting Liquibase to support our schema on 3 different db's (Oracle, SQL Server, MySQL). Interested in hearing about any experiences or gotchas with this tool.

fusiongyro 11y ago
I have been using it for the last five or so years and absolutely love it. I would recommend that you: - Make sure every code feature gets its own Liquibase migration file - Use the XML format—stronger IDE integration, and it's been around longer - If possible, use their migrations instead of raw SQL, since you will get the rollback functionality "for free." - Integrate it with the "deploy" phase of your Maven build, if applicable - Use the "reverse engineer" feature when you get close to deploying it the first time, to ensure that you have a from-scratch workflow that works and produces identical schemas You will get merge conflicts on the file that lists the migrations to apply, but they are obvious and trivial to fix. Pitfalls. The only one I've noticed is that it can be confused about Postgres types, but I have been able to the "rewrite" functionality to "fix them in post." I tried the YAML format and found that I missed the completion in the IDE that the XML format has, thanks to the schema. Otherwise, it works fine, but I like the validity angle. You can set up some migrations to be run every time. I usually set up the GRANTs that way, so I know the permissions are good right before a code deploy. Also, on those migrations, use an empty <rollback> clause so it doesn't prevent you from rolling back (you can do that on any migration to make Liquibase ignore it during a rollback operation, so all my data-manipulation migrations get one too).

dalacv 11y ago

I tried to use it. But it was too cumbersome to manage my migrations this way. I love the way that Rails handles database migrations.

shoq 11y ago

We're currently looking at http://flywaydb.org/ http://flywaydb.org/. Does anyone care to share some expiriences?

enricopulatzo 11y ago
Especially if your build involves Maven, FlywayDB is a very good choice if you want to use native SQL to manage your schema. I was originally quite bullish on FlywayDB until I started looking into Liquibase by way of Dropwizard. I'm personally not quite convinced of the need for native SQL scripting for schema management, as I often like to work in H2 for dev and something else for production. However, if you primarily want to use JDBC + native SQL to manage your schema, Flyway is quite powerful and easy to fold into your build process.
hackerboos 11y ago
Coming from Django and Rails Flyway was missing support for rollbacks. Everything else worked well mind...

frugalmail 11y ago

I really prefer git + Liquibase. I personally like the database abstraction and the refactorings.

ahachete 11y ago

In the PostgreSQL world, there are extensions (http://www.postgresql.org/docs/9.4/static/extend-extensions.html http://www.postgresql.org/docs/9.4/static/extend-extensions....). Since extensions are self-contained groups of database objects (i.e., code), are versioned and have some (basic) form of dependency management, they can be used for simple deployment mechanisms. They also support "SQL diffs" as "migration scripts" from version to version, and the extension mechanism applies them as needed. With all this, ot is not difficult to build a simple infrastructure for code packaging and deployment. That's what we do in our company.

dmmalam 11y ago
Would love to see a blog post detailing this.
- ahachete 11y ago
  Hmmmmmmm ok, good idea. Adding to our TODO list, hope to get it out soon ;P