Pg: Add support for programmatic migrations

[CrushedPixel]

I'm using go-pg in a web framework of mine, jargo.
The goal of jargo is to have the developer as little as possible about database interactions.
Currently, whenever the Application launches, I call db.Model(myModel).CreateTable(IfNotExists). This does not allow for dynamic updating of the database when the model changes at next Application launch.

It would be amazing to have an UpdateTable or AlterTable method updating existing columns. It would even be enough if it would just do an ALTER TABLE x ADD COLUMN IF NOT EXISTS to be able to add new model fields.

[Fyb3roptik]

https://github.com/jinzhu/gorm

This ORM has a method call db.AutoMigrate() and you pass in the models:

db.AutoMigrate(
  &models.User{}, &models.Address{}
)

This will do all table alters and inserts as needed when you add a column. It doesnt however remove old columns, so adding that would be awesome!

[CrushedPixel]

Thank you, unfortunately my framework is heavily centered around go-pg (it generates the annotated struct models required by go-pg at runtime), and it won't be easy switching to gorm.

[Fyb3roptik]

I was not suggesting to switch. Simply helping the dev for go-pg see how someone else does it! I switched from gorm recently and love this orm!

[CrushedPixel]

I see! From what I can tell, it should not be too difficult to add migrations to go-pg. Most of the code for table generation is already in place.

[Fyb3roptik]

https://github.com/jinzhu/gorm/blob/3a9e91ab372120a0e35b518430255308e3d8d5ea/scope.go#L1181

Here is the way they do it. Essentially create table if not exists and then create the fields. This does not change or delete the fields though

[CrushedPixel]

This is literally what I suggested - amazing! I hope this gets added to go-pg 😄

[Fyb3roptik]

Would be nice to also have one that handles updating or deleting columns too. But if this is all we can get I will take it! lol I need this functionality now. I might fork this repo and do a PR if thats cool

[Fyb3roptik]

@vmihailenco

[vmihailenco]

This probably should be part of github.com/go-pg/migrations.

But TBH I doubt that what gorm does is useful except in development environment when new fields are added/removed rapidly and you don't care about migrating existing data. But then simply DropTable and CreateTable will do the same.

Ideally it should be more like Django migrations, but it would be rather big task. I am not sure when I will have time/motivation to do it.

PS I definitely can show how to port gorm's auto migrate to go-pg if you are interested.

[Fyb3roptik]

I was trying to do that but my attempts at running this library locally are not working lol. Mainly it is migrating the database structure and not the data itself. Just need to be able to add tables/columns when added to the Model. An added bonus would be dropping of tables/columns if they are taken out of the Model or Migration params

[Fyb3roptik]

If you dont mind giving me a GIST I can go from there! Thanks!

[Fyb3roptik]

@vmihailenco This is more for the data structure rather than the data itself

[rhymes]

I'm using goose for migrations https://github.com/steinbacher/goose

[CrushedPixel]

@vmihailenco

PS I definitely can show how to port gorm's auto migrate to go-pg if you are interested.

I am definitely interested in adding this to go-pg - does your offer still stand?

[vmihailenco]

I meant that I can show starting points how to get required information from go-pg. Basically following program prints what PostgreSQL knows about the table and how go-pg expects the table to look (make sure to use latest go-pg version):

package main

import (
    "log"
    "reflect"

    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
)

type pgInfoColumns struct {
    tableName struct{} `sql:"information_schema.columns"`

    ColumnName string
    IsNullable bool
    DataType   string
}

type MyModel struct {
    Id      int
    Name    string
    Balance float64
}

func main() {
    db := pg.Connect(&pg.Options{
        Addr: ":5432",
        User: "postgres",
    })

    err := db.CreateTable((*MyModel)(nil), &orm.CreateTableOptions{
        IfNotExists: true,
    })
    if err != nil {
        panic(err)
    }

    table := orm.GetTable(reflect.TypeOf((*MyModel)(nil)).Elem())

    var columns []pgInfoColumns
    err = db.Model(&columns).
        Where("table_name = ?", table.Name).
        Select()
    if err != nil {
        panic(err)
    }

    for i := range columns {
        log.Println("Postgres", columns[i])
    }

    log.Println("--------")

    for _, f := range table.Fields {
        log.Println("go-pg", f)
    }
}

2018/10/26 12:06:25 Postgres {{} id false bigint}
2018/10/26 12:06:25 Postgres {{} name false text}
2018/10/26 12:06:25 Postgres {{} balance false double precision}
2018/10/26 12:06:25 --------
2018/10/26 12:06:25 go-pg &{{Id  int  0 [0] false} int Id id "id" bigserial [0]   1 0x54d120 0x551d90 0x5b3310}
2018/10/26 12:06:25 go-pg &{{Name  string  8 [1] false} string Name name "name" text [1]   0 0x54d6f0 0x5522f0 0x5b3140}
2018/10/26 12:06:25 go-pg &{{Balance  float64  24 [2] false} float64 Balance balance "balance" double precision [2]   0 0x54d3f0 0x552130 0x5b3540}

Having that information implementing AutoMigrate is "just" a matter of comparing 2 data-structures and issuing some SQL commands. That can be quite some work depending on how good you expect AutoMigrate to be :) See Table and Field definition to get an idea about go-pg side of this. \d information_schema.columns should describe you PostgreSQL catalog.

Some notes/quirks:

• Table does not have schema name
• no guarantees that Postgres and go-pg use same type names, e.g. my example shows that Postgres uses bigint but go-pg bigserial. Something must be done about it.

[CrushedPixel]

Thanks, that definitely helps.

[CrushedPixel]

Thanks to your input, I was able to implement a basic migration system in my framework.
When changes in the resource model's database table are detected, it tries to copy all data from the old table into the new resource model's table using a simple INSERT INTO new SELECT * FROM old statement.
If that doesn't work, for example due to incompatible data types or removed fields, the user has to perform migration themselves, to avoid any data loss.

https://github.com/CrushedPixel/jargo/blob/813c1c9729a38009bca964fd43104614fde80736/internal/schema_migration.go#L21-L100

[fi0]

@CrushedPixel any plans to add auto migration to go-pg as well?

[kopaygorodsky]

Automigration is a bad idea, but having some tool/API for getting schema diff between the current version in DB and models in your code sounds much better.
Everyone could create own method to run this diff and see what changed/generate migrations/etc.
I'll try to dive into this, maybe it will be useful.

[Fyb3roptik]

That is what Automigration is...its grabbing the diff in schema and migrating it...We arent talking about the data itself, just the schema

[mispp]

If this issue is closed, should we open another one? I mean, having additive automigrations would be helpful for development.

[tabvn]

i hope this help. i need someone can add more data types to support all postgresql data types.
https://github.com/go-pg/pg/issues/1570

[joeky888]

In the real-life use cases, I use gorm on Master, and go-pg on read-only Slave replications. Both are great. Note that gorm has some handy functions like Automigrate() and automatically updates the updated_at column while go-pg can do some complex SQL selections.

So basically the code looks like this:

type Orm struct {
    MasterGorm *gorm.DB
    SlavePg    *pg.DB
}

type BaseModel struct {
    ID        int64          `pg:",pk" gorm:"primarykey"`
    CreatedAt time.Time      `pg:"default:now()" gorm:"default:now()"`
    UpdatedAt time.Time      `pg:"default:now()" gorm:"default:now()"`
    DeletedAt gorm.DeletedAt `pg:",soft_delete" gorm:"index"`
}

type User struct {
    BaseModel
    Name  string `pg:"type:varchar(50),notnull" gorm:"type:varchar(50);not null"`
    Trees string `pg:"type:ltree,unique,notnull" gorm:"type:ltree;uniqueIndex;not null"`
}

// In case I need to use go-pg on Master, I will need to export go-pg orm to raw SQL query then use gorm.Exec() to use it
// See https://stackoverflow.com/questions/57973171
func (o *Orm) pgQueryString(query *orm.Query) (string, error) {
    queryString, err := query.AppendQuery(orm.NewFormatter(), nil)
    return string(queryString), err
}