Migrate doesn't work with CockroachDB

[crosslogic]

I've compiled the CLI with cockroachdb tag and run this command on CocrkoachDB v1.1:

migrate \
            -database cockroachdb://test:test@localhost:26257/test_cl?sslmode=disable     \
            -source file://sqlfiles                                                     \
            -verbose up

This is the log:

2017/10/17 16:12:38 Start buffering 1/u persona
2017/10/17 16:12:38 Start buffering 2/u centros
2017/10/17 16:12:38 Start buffering 3/u conceptos
2017/10/17 16:12:38 Start buffering 4/u contratos
2017/10/17 16:12:38 Start buffering 5/u cuentas
2017/10/17 16:12:38 Start buffering 6/u sucursales
2017/10/17 16:12:38 Start buffering 7/u unicidades
2017/10/17 16:12:38 Start buffering 8/u ubicaciones
2017/10/17 16:12:38 Start buffering 9/u productos
2017/10/17 16:12:38 Start buffering 10/u skus
2017/10/17 16:12:38 Start buffering 11/u usuarios
2017/10/17 16:12:38 Start buffering 30/u ops
2017/10/17 16:12:39 error: pq: relation "schema_migrations" does not exist

Am I missing something?

[nickjackson]

I think there is an issue here where the table_name = $1 value needs double quoting.

I believe this is due to some changes made in v1.1.0 around case sensitivity of table names. https://www.cockroachlabs.com/docs/releases/v1.1.0.html#backwards-incompatible-changes

[zikes]

This is a result of using TRUNCATE to clear the migrations table within a transaction at https://github.com/mattes/migrate/blob/master/database/cockroachdb/cockroachdb.go#L224

One of the current limitations of CRDB is that schema changes can't be in the same transaction as data manipulation: https://www.cockroachlabs.com/docs/stable/known-limitations.html#schema-changes-within-transactions

Unfortunately, it seems that TRUNCATE is considered a schema change. My guess is that it drops and recreates the table behind the scenes for efficiency's sake, rather than deleting individual records. I changed that line to DELETE FROM and this error was resolved for me.