Automatic schema generation?

[fr3fou]

Are you planning to implement automatic schema generation / migration?

adapter.Migrate(&app.User{}, &app.Book{}) // etc

[Fs02]

Hi so far I don't have any plan to make automatic schema migration.

The reason is our schema always evolving, and sometimes it's include manual data migration, so having a more control over doing it automatically is better.

Tools like this is suites better for my use case:
https://github.com/soundcloud/lhm
https://github.com/golang-migrate/migrate

[Fs02]

I'm closing this issue for now, if you think you need this feature, and want to propose an implementation for this, please let me know. thanks

[lafriks]

Just find out about this project and really like how it is implemented interface wise but missing database schema migrations like gorm or xorm is really something would be needed

[Fs02]

Hi @lafriks, It might be a good to know the use case where auto schema migrations is very useful? so I can have a better consideration about this feature :)

[fr3fou]

Hi @lafriks, It might be a good to know the use case where auto schema migrations is very useful? so I can have a better consideration about this feature :)

in my case it would be to prevent having to write the same schema multiple times (once in a Go struct and once in an SQL file), having it done automatically helps me build things faster.

[Fs02]

in my case it would be to prevent having to write the same schema multiple times (once in a Go struct and once in an SQL file), having it done automatically helps me build things faster.

Just curious, is the auto migration also running in production? So far how is the experience?

cmiiw, So far I can only think that this feature will excels in embedded/standalone environment.
I doubt this is the way to go when working with multiple instance of server, just imagine how many auto migration will try to migrate the same schema on deployment.

[fr3fou]

when using libraries such as typeorm in node.js, that feature is only enabled for development

[Fs02]

hmm, so I believe it's mainly useful only for development?
In that case, I believe something like https://alembic.sqlalchemy.org/en/latest/autogenerate.html will brings more value, but this will be on separate project.

[lafriks]

So mostly:

• Ease of development (faster to develop and less possibility for errors)
• Support for multiple database engines without separate SQL scripts for each database engine

You can check out how we are using it in Gitea project to support multiple databases using xorm for example https://github.com/go-gitea/gitea/blob/master/models/migrations/v70.go

That includes both struct autosync to database structure and if needed data migrations.

Many projects use this also for production. Gitea does not support scaling to multiple servers so that is not that important but if implementing it would be smart to do this correctly to support multiple parallel instances. Most easiest way is to have special table for migrations with structure:

type Migrations struct {
	ID          string `db:",primary"`
	Description string
	StartedAt   time.Time
	FinishedAt  time.Time
}

This would prevent multiple instances to start migrations in parallel as database server PK index would prevent inserting duplicate records even if transaction is not finished yet and other instances if restarted in same time should wait for migrations to complete on first instance.

As for interface this would need two methods - initial database initialization - creating all tables in correct order provided in code and inserting all migrations as completed without running them. And other method to run migrations that are not still run.

[lafriks]

Some ideas can be taken from:
https://gitea.com/techknowlogick/xormigrate or https://github.com/go-gormigrate/gormigrate

[lafriks]

Also it would be great if it would support automatic FK generation as no other Go ORM/DAL supports that currently to my knowledge

[Fs02]

I see, as I understand, it's not a fully auto migration where we leave the engine to do the migration magic, but more like traditional version based migration.

Developer have control on what needs to be migrated on each new version, It is similar to active record, but instead of using dsl, xorm or gorm relies on built in Sync or AutoMigrate feature.

If that's what you mean, then yes, It's something that I want to support, hopefully before version 1 (if I have time or some helps 😆 ).

---

My design is quite different from xormigrate or gomigrate, because I prefer to use DSL instead of automigrate function because it's more explicit.

This is what I have in my mind right now:

1. As for MVP, I'm thinking to build a dsl which the end result will looks like this:

Migration file:

// db/migrations/20200506191500_create_products.go
type CreateProducts struct{}

func (cp CreateProducts) Version() string {
    retun "20200506191500"
}

func (cp CreateProducts) Up(m *rel.Migration) {
    m.CreateTable("products", func(t rel.Table) {
        t.String("name")
        t.Text("description")
    })
}

func (cp) Down(m *rel.Migration) {
    m.DropTable("products")
}

Custom API to invoke migration by code:

migrator := rel.NewMigrator(adapter)
migrator.Add(CreateProducts{})
migrator.Sync()
// migrator.Rollback()

2. (Nice to have) If the mvp (dsl) works, then I'm thinking that we should have built in command to compile and run migration by reading go files in a specific migration directory (similar to go test command).

3. (Nice to have) Some tooling that allows automatic previewing and generation of migration files after reading source code will be a really nice addition. (something like this: https://alembic.sqlalchemy.org/en/latest/autogenerate.html but it should use go:generate)
   This will saves a lot of time when writing migration.

4. (Also nice to have, but difficult and needs a lot of research) Support online migration, but looks like it'll be a very database specific. (mysql: https://github.com/soundcloud/lhm, postgresql: https://github.com/LendingHome/zero_downtime_migrations)

[fr3fou]

I like that API! Could you give an example for foreign keys?

[Fs02]

I haven't though that much, probably either of:

• t.BigInt("product_id", rel.References("products")) (I think this is simpler and easy to extend)
• t.References("products")
• t.BelongsTo("products")

and more inspiration

I think there's a lot to be considered, so far the inspiration of DSL comes mostly from activerecord, but there's other similar project in other language that have simpler DSL(example: Ecto)

Let me know if you have any idea?

[Fs02]

Hi all,

I have an update for this issue, POC version of schema migration is almost done, and you can find the example usage here: Fs02/go-todo-backend#6

let me know what you think 😄

[fr3fou]

I like it! I've been looking for similar tools for a while now. Is it tightly coupled with rel btw? I'd like to use it independently if possible ^^

unrelated note: keep up the great work on rel, I've been excited for its progress!

[Fs02]

You can use the migration functionality independently 😄

[fr3fou]

You can use the migration functionality independently 😄

but should it be in a separate package / repo?

[Fs02]

did you mean package/repo of your project? You can specify migration programmatically even in the same project: https://github.com/Fs02/rel/blob/master/adapter/specs/migration.go

I personally prefer to put my migration into separate package that's not imported by other runtime package just to keep it cleaner and safer from accidental migration.

[fr3fou]

I meant should the migrations "component" / functionality (the migrator package) of rel be in a separate GitHub repo / project, this way you don't have to clone / depend on the rest of rel if you wanted to use just the migrations feature, combined with some other database package, be it vanilla database/sql or anything else. Assuming the rel-go organization you'd have

rel-go/rel - main repo with db interactions
rel-go/migrator - the migrations feature of rel (but separated)

(I'm suggesting this because I know for a fact some of my friends would like to stick to some more vanilla approach that isn't "orm-ish", but would still like the migrations part)

[Fs02]

My first plan is actually to have a separated package for this. but I decided to merge the ddl for the following reason:

• having rel as package name for ddl is like a brand, feels nice and consistent.
• it has a feature where we can execute a golang function with rel Repository. It may be usefull to perform data migration.
• the actual migration logic is not inside rel package, so only ddl is inside rel.

(I'm suggesting this because I know for a fact some of my friends would like to stick to some more vanilla approach that isn't "orm-ish", but would still like the migrations part)

I'm curious, what they currently using? and why they want to move to something that's not vanilla on migration level?

[fr3fou]

upper.io or sqlx!

[Fs02]

What tool/library they use to manage db migration when they are using sqlx?

[fr3fou]

https://github.com/golang-migrate/migrate