不能在Gormigrate中使用txdb

Question

I'm trying to use txdb for migrating my database inside an isolated transaction, for the purpose of testing code that interact with the database and also for tests with the migrations themselves. But every time I run my code an error is returned and some warnings are yell by Gorm:

$ DATABASE_URL="postgres://postgres@localhost:5432/postgres?sslmode=disable" ./txdb_ejemplo
`txdb_postgres` is not officially supported, running under compatibility mode.

(/home/jorge/go/pkg/mod/gopkg.in/gormigrate.v1@v1.6.0/gormigrate.go:381)
[2019-08-12 19:46:28]  pq: current transaction is aborted, commands ignored until end of transaction block
panic: migration failed: pq: current transaction is aborted, commands ignored until end of transaction block

goroutine 1 [running]:
main.main()
        /home/jorge/proyectos/kue/fero/txdb_ejemplo/main.go:76 +0x3a1

And this is the output of the docker service running offering the database:

db_1  | 2019-08-13 01:46:08.788 UTC [1] LOG:  database system is ready to accept connections
db_1  | 2019-08-13 01:46:28.564 UTC [48] ERROR:  function database() does not exist at character 8
db_1  | 2019-08-13 01:46:28.564 UTC [48] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
db_1  | 2019-08-13 01:46:28.564 UTC [48] STATEMENT:  SELECT DATABASE()
db_1  | 2019-08-13 01:46:28.564 UTC [48] ERROR:  syntax error at or near "AND" at character 71
db_1  | 2019-08-13 01:46:28.564 UTC [48] STATEMENT:  SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = ? AND table_name = ?
db_1  | 2019-08-13 01:46:28.564 UTC [48] ERROR:  current transaction is aborted, commands ignored until end of transaction block
db_1  | 2019-08-13 01:46:28.564 UTC [48] STATEMENT:  CREATE TABLE migrations (id VARCHAR(255) PRIMARY KEY)

I don't understand what I'm doing wrong, but I offer a minimal functional example that reproduces the issue I'm having, here is the repository with the code and there is a primer of the Go code:

package main

import (
    "fmt"
    "os"
    "time"

    "github.com/DATA-DOG/go-txdb"
    "github.com/jinzhu/gorm"
    _ "github.com/lib/pq"
    gormigrate "gopkg.in/gormigrate.v1"
)

var (
    migration_1 = func(tx *gorm.DB) error {
        type Person struct {
            gorm.Model
            Name string
        }

        return tx.CreateTable(&Person{}).Error
    }
    migration_2 = func(tx *gorm.DB) error {
        type Person struct {
            Age int
        }
        return tx.AutoMigrate(&Person{}).Error
    }
    return_nil = func(tx *gorm.DB) error {
        return nil
    }
)

type Person struct {
    gorm.Model
    Name string
    Age  int
}

func MigrateAll(gdb *gorm.DB) error {
    m := gormigrate.New(gdb, gormigrate.DefaultOptions, []*gormigrate.Migration{
        {
            ID:       "first",
            Migrate:  migration_1,
            Rollback: return_nil,
        },
        {
            ID:       "second",
            Migrate:  migration_2,
            Rollback: return_nil,
        },
    })
    return m.Migrate()
}

func main() {
    url := os.Getenv("DATABASE_URL")
    txdb.Register("txdb_postgres", "postgres", url)
    var db *gorm.DB
    var err error
    for i := 0; i < 3; i++ {
        db, err = gorm.Open("txdb_postgres", "tx_1")
        if err == nil {
            break
        }
        fmt.Printf("connection failed, retrying in 10 seconds. Reason: %s
", err)
        time.Sleep(10 * time.Second)
    }
    if err != nil {
        panic(fmt.Sprintf("connection failed: %s", err))
    }

    defer db.Close()
    err = MigrateAll(db)
    if err != nil {
        panic(fmt.Sprintf("migration failed: %s", err))
    }
}

Answer 1

The problem seams to be that gorm uses the first gorm.Open argument as a dialect name (it has a set of predefined dialects) and not as a database/sql driver name.

So you need to use a gorm known dialect name as the first argument, in this case you want "postgres" since you will be using postgresql as the underlying database hence you need gorm using the corresponding sql dialect.

Then you tell gorm to use the "txdb" driver and "tx_1" as the connection url.

This seem to work:

package main

import (
    "fmt"
    "os"
    "time"

    "github.com/DATA-DOG/go-txdb"
    "github.com/jinzhu/gorm"
    _ "github.com/lib/pq"
    gormigrate "gopkg.in/gormigrate.v1"
)

var (
    migration_1 = func(tx *gorm.DB) error {
        type Person struct {
            gorm.Model
            Name string
        }

        return tx.CreateTable(&Person{}).Error
    }
    migration_2 = func(tx *gorm.DB) error {
        type Person struct {
            Age int
        }
        return tx.AutoMigrate(&Person{}).Error
    }
    return_nil = func(tx *gorm.DB) error {
        return nil
    }
)

type Person struct {
    gorm.Model
    Name string
    Age  int
}

func MigrateAll(gdb *gorm.DB) error {
    m := gormigrate.New(gdb, gormigrate.DefaultOptions, []*gormigrate.Migration{
        {
            ID:       "first",
            Migrate:  migration_1,
            Rollback: return_nil,
        },
        {
            ID:       "second",
            Migrate:  migration_2,
            Rollback: return_nil,
        },
    })
    return m.Migrate()
}

func main() {
    url := os.Getenv("DATABASE_URL")
    txdb.Register("txdb", "postgres", url)
    var db *gorm.DB
    var err error
    for i := 0; i < 3; i++ {
        db, err = gorm.Open("postgres", "txdb", "tx_1")
        if err == nil {
            break
        }
        fmt.Printf("connection failed, retrying in 10 seconds. Reason: %s
", err)
        time.Sleep(10 * time.Second)
    }
    if err != nil {
        panic(fmt.Sprintf("connection failed: %s", err))
    }

    defer db.Close()
    err = MigrateAll(db)
    if err != nil {
        panic(fmt.Sprintf("migration failed: %s", err))
    }
}

Alternatively you could pass gorm.Open directly the *sql.Db, the relevant part would be like:

        s, err := sql.Open("txdb", "tx_1")
        // handle err
        db, err := gorm.Open("postgres", s)
        // handle err

And that would work too.