Sqitch - Sane database change management • Hacker News Board

Re: Sqitch - Sane database change management

#31

Post by shkkmo » Mon, Aug 31, 2015, 5:29 PM UTC

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've found the doctrine migrations library to work nicely, if you are already using doctrine as an ORM. In fact, I find the simplicity of using doctrine migrations one the my favorite features of doctrine. I find myself wishing that the other parts of doctrine were as well designed and implemented.

1) It identifies migrations based on the timestamp when the migration was created. This means that it checks for any missing migrations regardless of migration 'order' while still running all needed migrations in order. It also checks for any migrations that have been run on the DB that are not in your current code base and warns you about them.

2) The migrations are simple PHP scripts that run SQL. This means that you can put whatever you want in them, easily edit them, adjust the timestamp, and manage them in your version control of choice.

3) Merging two branches with different migrations is not a problem, as long as the migrations themselves don't conflict (i.e. one changes a table or column name that the other also wants to modify). You will however have to identify the conflicts by testing the migrations together rather than just relying on your merge tool.

I'd have a hard time moving to any other migration tool that didn't offer this level of simplicity. The auto-generation of migrations based on comparing the XML schema and the DB is nice as well, but I could live without it as long as I have the other features.

Re: Sqitch - Sane database change management

#32

Post by joesb » Mon, Aug 31, 2015, 5:32 PM UTC

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.

I could not agree more.

Rails's migration is probably the only one that works in any project condition we've been through. No problem working with legacy DB. No problem using it in the DB where other team change unrelated table. You always know what change is in each migration.

Re: Sqitch - Sane database change management

#33

Post by subleq » Mon, Aug 31, 2015, 5:54 PM UTC

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?

Django. Migrations are modeled as a DAG instead of a linear sequence, so you can create migrations in conflicting branches as much as you want, and just create a 'merge' migration (that does nothing but combine the two branches) when you merge the branches.

Re: Sqitch - Sane database change management

#34

Post by ahachete » Mon, Aug 31, 2015, 6:13 PM UTC

In the PostgreSQL world, there are extensions (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.

Re: Sqitch - Sane database change management

#35

Post by zimbatm » Mon, Aug 31, 2015, 6:23 PM UTC

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…

Something I understood only recently is that a DB is an API. You can add fields/tables to it easily but changes should always be backwards-compatible (if you care about availability). Only when all the clients have been upgraded can the old fields/tables be removed.

Most deploys should be:

1. Adding fields/tables

2. Replace all clients

3. Remove old fields/tables

In the case of a rename triggers should be used to duplicate data between step 1 and 3.

Re: Sqitch - Sane database change management

#36

Post by dmmalam » Mon, Aug 31, 2015, 7:59 PM UTC

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?

Currently using pg-migrator [1]. Seems to have the right balance between power and simplicity. Though numbered migrations can be a little annoying to manage cross team.

[1] https://github.com/aphel-bilisim-hizmetleri/pg-migrator

Re: Sqitch - Sane database change management

#37

Post by dmmalam » Mon, Aug 31, 2015, 8:00 PM UTC

ahachete wrote: post #34

In the PostgreSQL world, there are extensions ( 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 appl…

Would love to see a blog post detailing this.

Re: Sqitch - Sane database change management

#38

Post by ahachete » Mon, Aug 31, 2015, 10:45 PM UTC

dmmalam wrote: post #37

ahachete wrote: post #34

In the PostgreSQL world, there are extensions ( 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 appl…

Would love to see a blog post detailing this.

Hmmmmmmm ok, good idea. Adding to our TODO list, hope to get it out soon ;P

Re: Sqitch - Sane database change management

#39

Post by fusiongyro » Tue, Sep 01, 2015, 5:37 AM UTC

chrismaeda wrote: post #18

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.

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