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

21–30 of 39 posts

Re: Sqitch - Sane database change management

#21

Post by jcoby »

sandGorgon wrote: post #14
jcoby wrote: post #12

Earlier quoted context omitted.

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.

That's a great idea. I never would have considered that.

Do you create a full rails project to handle migrations or what? How do you integrate that in with your other code?

Re: Sqitch - Sane database change management

#22

Post by gghh »

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…

We don't write migrations for our C++, so why the heck are we writing migrations for our DBs

The reason is that your database is much more "stateful" than you binary executables. A db migration (DDL, i.e. when you change the schema of your db) can take days to apply (eg: create an index in a large table), and you don't want to re-do it every time you deploy. That's why database changes are much more "diff oriented" than your code: when you deploy code you just compile the whole thing and replace the old binary with a new one.

Re: Sqitch - Sane database change management

#23

Post by idunno246 »

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?

We've been using alembic in Python. It makes a dag, and has a concept of merge points so you Don't grow leaves indefinitely. Handles branching well, our history mostly just looks like a line of diamonds.

You're sol if the branches touch the same column or directly conflict, but that's never happened to us. Rarely dev Dbs end up hosed, if someone commits a bad change for instance and you pull it in before fixed, but since the latest alembic bug fixes and solid pr ci t hasn't happened.

Re: Sqitch - Sane database change management

#25

Post by dragonwriter »

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…

> We don't write migrations for our C++, so why the heck are we writing migrations for our DBs?

Because your DB is a datastore as well as a store of code and structure, and when you apply changes to your production DB, they generally have to preserve the data that is already in the DB.

That means that the transition is the important thing to describe and test. Inferring it from create scripts is possible, but it doesn't really gain you anything. And doing it by some process that infers some of the changes but also leaves you to directly script others seems to be strictly inferior to describing the transitions explicitly and not leaving something to infer them partially with you filling in the gaps.

It sounds like you are describing a process that leaves you more work to do so that you can be less close to the important part of what the is actually be done, which seems like losing all around -- though it certainly seems like the kind of losing that is inexplicably popular in the enterprise world.

Re: Sqitch - Sane database change management

#26

Post by stevepike »

jcoby wrote: post #21
sandGorgon wrote: post #14

Earlier quoted context omitted.

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.

That's a great idea. I never would have considered that. Do you create a full rails project to handle migrations or what? How do you integrate that in with your other code?

I've used https://github.com/thuss/standalone-migrations with success before. It's the rails migration system as a standalone gem.

Re: Sqitch - Sane database change management

#28

Post by enricopulatzo »

shoq wrote: post #24

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

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.

Re: Sqitch - Sane database change management

#30

Post by sandGorgon »

jcoby wrote: post #21
sandGorgon wrote: post #14

Earlier quoted context omitted.

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.

That's a great idea. I never would have considered that. Do you create a full rails project to handle migrations or what? How do you integrate that in with your other code?

A full rails project is just a few KB. All you need to do each time is run "bundle exec rails generate migration AddNameToUsers"

And then follow the migration syntax to modify your schema. Rails will take care if the timesramping, rollbacks (in most cases),etc. You can manage your indexes, primary,etc everything here. You don't need to write any other rails code.

Check this whole directory as a part of your main source repo.

Another commenter pointed out a standalone gem for migrations. That might be OK, but I'd rather use mainline rails in this case.

Post reply on HN