Support go migrations

[denismakogon]

SQL migrations is a good thing, but likely useless in many cases. Migrate must support go migrations, i.e a function that accepts an instance of the database or a transaction object (in case of SQL datastores). Something along the lines of this to up the version:

func up(tx *sql.Tx) error {
         _, err := tx.Exec("ALTER TABLE calls ADD stats text;")
    return err
}

down also looks the same:

func down(tx *sql.Tx) error {
    _, err := tx.Exec("ALTER TABLE calls DROP COLUMN stats;")
    return err
}

[rubens21]

Hey @denismakogon, could you please describe a case where the SQL is useless. My team is comparing migrations tools, and I would like to consider your comment.

Thanks.

[denismakogon]

@rubens21 so, here's the thing, if i have a system with a lot of migrations applied and i have to apply changes to multiple tables in one query (image bunch of resources tight down with a lot cross-references), so, in order to make things work i need to update one table with an appropriate field with a value that, imagine, comes from 3rd-party service.

So, most of the complex migrations can't fit into one *.sql file, so i need to write some go code in the migrations, that is more than necessary. We ended up having our own simple SQL migrations framework: https://github.com/fnproject/fn/tree/master/api/datastore/sql/migratex

[denismakogon]

Moreover, you can't write SQL migration with altering the column and make it work on sqlite3, you need to create a new table with modifications applied, copy the data from an old table, delete old table, etc.

[rubens21]

Thank so much for such fast and detailed response. That's a very specific case, but I do agree we have more common cases where a programmatic migration would be much easier than SQL statements. Thank, I will check that project out.