Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Sqitch - Sane database change management

sqitch.org

Post reply on HN
Print view View original

11–20 of 39 posts

Re: Sqitch - Sane database change management

#11

Post by mrbig4545 »

eli wrote: post #9
mrbig4545 wrote: post #6

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 unde…

Are there other database migration systems that make dealing with migrations in conflicting branches really easy?

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!

Re: Sqitch - Sane database change management

#12

Post by jcoby »

eli wrote: post #9
mrbig4545 wrote: post #6

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 unde…

Are there other database migration systems that make dealing with migrations in conflicting branches really easy?

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.

Re: Sqitch - Sane database change management

#13

Post by peterwwillis »

glogla wrote: post #3

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?

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.

Re: Sqitch - Sane database change management

#14

Post by sandGorgon »

jcoby wrote: post #12
eli wrote: post #9

Earlier quoted context omitted.

Are there other database migration systems that make dealing with migrations in conflicting branches really easy?

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…

We actually use rails just to manage our db. Rails migrations are so good and sane (including the rake generator tasks) that I highly recommend it...even if you are using nodejs or something else as your actual stack.

Re: Sqitch - Sane database change management

#15

Post by VLM »

zamalek wrote: post #5
ljoshua wrote: post #4

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.

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. Obvio…

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.

Re: Sqitch - Sane database change management

#16

Post by dpb000 »

zamalek wrote: post #5
ljoshua wrote: post #4

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.

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. Obvio…

SCM solves the easy problem of maintaining the history of the DDL required to build an empty database from scratch (and you can even have DML under source control to populate the database with test data once it is built).

When version 1.2 of a table's DDL adds a new, not nullable column to the version 1.1 definition and there are a billion rows in production, you are looking at a one-off migration event.

Re: Sqitch - Sane database change management

#17

Post by zamalek »

dpb000 wrote: post #16
zamalek wrote: post #5

Earlier quoted context omitted.

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. Obvio…

SCM solves the easy problem of maintaining the history of the DDL required to build an empty database from scratch (and you can even have DML under source control to populate the database with test data once it is built). When version 1.2 of a table's DDL adds a new, not nullable column to the version 1.1 definition and there are a billion rows in production, you are looking at a one-off migration event.

> you are looking at a one-off migration event.

Right, and that's where you roll up your sleeves and describe how to upgrade to that new column; just like you'd handle an out-of-date wire format with C++. No matter how you look at it, you'll always have to manually deal with the most complex upgrade scenarios no matter what you are targeting (RDBMS/CPU/car/washing machine).

The question remains: why are we doing that all the time?

Re: Sqitch - Sane database change management

#20

Post by zamalek »

VLM wrote: post #15
zamalek wrote: post #5

Earlier quoted context omitted.

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. Obvio…

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 a…

> 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.

Awesome counterargument. However, in the same breath: what's the story with migrations? In RoR they're ordered by date, so what happens if two developers make concurrent and conflicting schema changes in their respective branches? That merge will silently fail, a false positive. At least a merge conflict throws up red flags, causing some human has to look at what is going on.

Post reply on HN