Create a common DB migration story about go HOT 4 CLOSED

I think that a wrapper around github.com/rubenv/sql-migrate in Horizon looks good. What I would change is:

• Remove latest.sql file. It seems that it has to be updated after adding a new migration file. Why not just run migrations?
• I personally don't like the go-bindata compilation step. It's commited to the Git repo and causes merge conflicts, it's impossible to check during a review (compiles human-readable text to hex) and it's not part of our build process (so it's easy to forget). What about simply using multiline strings to import SQL into Go code?

Any thoughts @nullstyle? I would like to use the final system in #378 but I don't want to start coding before talking to you.

from go.

Remove latest.sql file. It seems that it has to be updated after adding a new migration file. Why not just run migrations?

They serve different purposes, especially when you get to a large number of migrations. The latest schema allows a new database to go from empty to the correct schema without all of the intermediary steps. This becomes important when you hit 50+ migrations.

It's commited to the Git repo and causes merge conflicts, it's impossible to check during a review (compiles human-readable text to hex) and it's not part of our build process (so it's easy to forget).

Automate it. The only reason it is in the current state is because the current setup works for me (I don't forget to run go generate) and no one else has done any work to improve it.

What about simply using multiline strings to import SQL into Go code?

Editting SQL in a multiline string sucks hardcore IMO

from go.

They serve different purposes, especially when you get to a large number of migrations. The latest schema allows a new database to go from empty to the correct schema without all of the intermediary steps. This becomes important when you hit 50+ migrations.

I think it's not big deal even with a larger number of migrations. These are usually simple schema changes so applying them shouldn't take that long. Going from empty DB to the latest schema is only done once, so even if the process takes 1-2 minute (which shouldn't be that long even for hundreds schema migrations given that creating tables, adding indexes on empty tables or removing/adding fields are operations that take miliseconds) I think it's fine.

Automate it. The only reason it is in the current state is because the current setup works for me (I don't forget to run go generate) and no one else has done any work to improve it.

I agree that automating this can solve "forgot to run go generate" issues but other issues: hard to read diffs and conflicts are still there. What about this:

• Let's continue compiling migration files.
• Remove the compiled files from Git and add them to .gitignore.
• Add go generate step to CI process and update README build instructions.

Now, compiled files are not part of Git (so do not appear in diffs and can't cause conflicts).

from go.

I think it's not big deal even with a larger number of migrations. These are usually simple schema changes so applying them shouldn't take that long. Going from empty DB to the latest schema is only done once, so even if the process takes 1-2 minute (which shouldn't be that long even for hundreds schema migrations given that creating tables, adding indexes on empty tables or removing/adding fields are operations that take miliseconds) I think it's fine.

It's not just about runtime performance. It's also about simplicity of action at runtime. One set of SQL statements (that you can inspect in a single text editor) is much easier to debug for an end user than "migration 22 of 50" failed. This lesson is not a new on, it comes from years and years of working with migrations in the RoR world: http://edgeguides.rubyonrails.org/active_record_migrations.html#schema-dumping-and-you

Now, compiled files are not part of Git (so do not appear in diffs and can't cause conflicts).

Two notes:

• The hard to read diffs aren't really due to migrations... it's due to the test scenarios. Each migration causes three diff hunks: the new migration file, the patch to latest.sql and the updated bindata.go. I'm fine with bindata.go being in .gitignore. Nikhil is working on a system to clean up the diffs caused by the test scenarios.
• We want latest.sql in the commit history. It is the system of record for the latest horizon db schema and having a history of how it changes is valuable.

from go.