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

Better Database Migrations in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

41–50 of 89 posts

Re: Better Database Migrations in Postgres

#41

Post by barnabee »

gregmac wrote: post #25

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…

I've found Liquibase [1] to be really good for this, although the docs don't give much of a hint about how to structure your change sets.

If you get it right, it's really easy to manage the DB schema structure and commits to your source repository are very legible (because there's one file per entity being modified), much better than interleaving all changes in a single stream IMO.

Particular highlights:

- Their annotated SQL format allows you to create a file per table that contains a first "changeset" to create the table and subsequent changesets for each alteration

- The "runOnChange" option allows idempotent, non-data-destroying parts of your schema such as view and function definitions to be kept as individual files and modified in place (Liquibase uses a hash of the definition to decide whether to rerun it when migrating)

- The "includeAll" tag lets you collect together the scripts in directories ("/views", "/tables", "/functions", etc.) and have them automatically picked up and run from a single "schema.xml" file in the root.

[1] http://www.liquibase.org

Re: Better Database Migrations in Postgres

#42

Post by 0XAFFE »

rawrmaan wrote: post #3

What's everyone favorite library for doing Postgres migrations using node? I'm using knex.js and still doing migrations mostly by hand.

We use umzug[1] a migrations framework which uses sequelize[2]. We use umzug/sequelize with TypeScript and its quite pleasant to work with.

[1] https://github.com/sequelize/umzug

[2] https://github.com/sequelize/sequelize

Re: Better Database Migrations in Postgres

#43

Post by [deleted] »

vog wrote: post #36
gregmac wrote: post #25

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…

(We should coin a term for this. I propose: "idempotent database updates".) I'm also a strong proponent of idempotent database updates, and prefer those over classic migrations wherever possible. Some experience from PostgreSQL (with several years of experience in various applications): While this approach works pretty well for idempotent changes such as "add column if not exists", it is more tricky when data content…

[deleted]

Re: Better Database Migrations in Postgres

#45

Post by robertlagrant »

trampi wrote: post #28
oneweekwonder wrote: post #16

Earlier quoted context omitted.

Another options is liquibase[0]. I use the diff[1] tool to create a changeset and then convert it to sql before applying it to my db. liquibase keeps a log and lock table in your db so you can always review the changeset you applied to the db at a later time. [0] http://www.liquibase.org/ [1] http://www.liquibase.org/documentation/diff.html

Liquibase is definitifely a good recommendation. We use it to update Oracle, postgreSQL, MariaDB as well as exporting the current schema as a hsqldb-script. Some migrations are only executed for some of our customers.

Also a big fan of Liquibase here, on SQL Server. I've used Redgate in the past, which is even graphical etc, so simpler to use for slightly less technical DBAs.

Re: Better Database Migrations in Postgres

#46

Post by robertlagrant »

sandGorgon wrote: post #14
misframer wrote: post #2

strong_migrations looks like a really useful tool. Are there similar ones for languages/frameworks besides Ruby/Rails?

Alembic is a standalone tool written in Python. Having spent years on rails migrations and then using Alembic, I can say that alembic is really brilliant. It behaves like git - it has a branching and merge model for Migrations (in case multiple people work on it simultaneously).

Alembic is amazing, but AFAIK only works with its author's (world-beating) ORM, SQLAlchemy. I didn't think it was a general-purpose database change tool.

Re: Better Database Migrations in Postgres

#48

Post by sandGorgon »

robertlagrant wrote: post #46
sandGorgon wrote: post #14

Earlier quoted context omitted.

Alembic is a standalone tool written in Python. Having spent years on rails migrations and then using Alembic, I can say that alembic is really brilliant. It behaves like git - it has a branching and merge model for Migrations (in case multiple people work on it simultaneously).

Alembic is amazing, but AFAIK only works with its author's (world-beating) ORM, SQLAlchemy. I didn't think it was a general-purpose database change tool.

We use it standalone for our nodejs code. It does pull-in SQL alchemy, but who cares. I think what you are really asking is this - We don't autogenerate migrations from models, we write them explicitly.

There is nothing even close enough in the nodejs world and we wanted to manage our dB properly.

Incidentally, three other nodejs startups that I told this to, also started using Alembic for their migrations.

Re: Better Database Migrations in Postgres

#49

Post by brightball »

gregmac wrote: post #25

I really just want a deterministic (or sync-based) migration tool. The only two I'm aware of are Innovartis DBGhost and RedGate SQL Compare (though RedGate requires a license everywhere it runs, whereas DBGhost only requires a license to compile the package). This stems from my work years ago with databases in on-premise products. Customers would modify the database schema, causing migration "up" scripts to fail, and…

I think Navicat will do that as well

Re: Better Database Migrations in Postgres

#50

Post by YorickPeterse »

For GitLab we have a somewhat special approach to allow us to perform zero downtime migrations. Basically we have two types of migrations:

1. Regular migrations (located in db/migrate)

2. Post-deployment migrations (located in db/post_migrate)

Post-deployment migrations are mostly used for removing things (e.g. columns) and correcting data (e.g. bad data caused by a bug for which we first need to deploy the code fix). The code for this is also super simple:

    # Just dump this in config/initializers
    unless ENV['SKIP_POST_DEPLOYMENT_MIGRATIONS']
      path = Rails.root.join('db', 'post_migrate').to_s

      Rails.application.config.paths['db/migrate']
By default Rails will include both directories so running `rake db:migrate` will result in all migrations being executed as usual. By setting an environment flag you can opt-out of the post-deployment migrations. This allows us to deploy GitLab.com essentially as follows:

1. Deploy code to a deployment host (not used for requests and such)

2. Run migrations excluding the post-deployment ones

3. Deploy code everywhere

4. Re-run migrations, this time including the post-deployment migrations

We don't use anything like strong migrations, instead we just documented what requires downtime or not, how to work around that (and what methods to use), etc. Some more info on this can be found here:

* https://docs.gitlab.com/ee/development/what_requires_downtim...

* https://docs.gitlab.com/ee/update/README.html#upgrading-with...

* https://docs.gitlab.com/ee/development/post_deployment_migra...

Post reply on HN