New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AutoMigrate foreign keys #450

Open
nkovacs opened this Issue Apr 13, 2015 · 38 comments

Comments

Projects
None yet
@nkovacs
Contributor

nkovacs commented Apr 13, 2015

How do I create foreign keys with AutoMigrate?

type TestRun struct {
    ID        uint64
    StartedAt int64
    EndedAt   int64
    Suites    []Suite
}

type Suite struct {
    ID        uint64
    TestRunID uint64
    Name      string
}

db.AutoMigrate(&TestRun{}, &Suite{})
@czivar

This comment has been minimized.

Show comment
Hide comment
@czivar

czivar Apr 15, 2015

I'm also interested in the answer for this.

czivar commented Apr 15, 2015

I'm also interested in the answer for this.

@codepushr

This comment has been minimized.

Show comment
Hide comment
@codepushr

codepushr May 10, 2015

Me too! Right now gorm is not creating FK constraints, right?

codepushr commented May 10, 2015

Me too! Right now gorm is not creating FK constraints, right?

@tp

This comment has been minimized.

Show comment
Hide comment
@tp

tp May 18, 2015

Also ran into this after a few minutes when trying out GORM.

Is there any recommended way to add FK relations to an existing table (other than manually)?

tp commented May 18, 2015

Also ran into this after a few minutes when trying out GORM.

Is there any recommended way to add FK relations to an existing table (other than manually)?

@tp

This comment has been minimized.

Show comment
Hide comment
@tp

tp May 18, 2015

previous issue: #349

tp commented May 18, 2015

previous issue: #349

@aacanakin

This comment has been minimized.

Show comment
Hide comment
@aacanakin

aacanakin Jul 1, 2015

I looked a lot about that. I tried nearly everything but it doesn't work.

type Model struct {
    Id        string    `sql:"size:36" gorm:"primary_key;"`
    CreatedAt time.Time `sql:"DEFAULT:current_timestamp"`
    UpdatedAt time.Time `sql:"DEFAULT:current_timestamp"`
    DeletedAt time.Time
}

type Domain struct {
    Model
    Extension string `sql:"size:255;"`
}

type User struct {
    Model
    Name     string `sql:"not null; size:255"`
    Email    string `sql:"not null; unique; size:255"`
    Password string `sql:"not null; size:255"`
    Birthday time.Time
    // Domain   Domain `gorm:"foreignkey:domain_id; foreigntype:Domain"`
    // Domain   Domain `gorm:"associationforeignkey:Domain"`
    // DomainId string `gorm:"ForeignKey:domain_id"`
    Role Role
}

In this example, I'm using mysql driver. The trials I made are based from old issues. I think there's no way to define has one relationship currently.

Also, one suggestion;
There's a great documentation website called http://readme.io. @jinzhu you can easily create great documentation using markdown.

aacanakin commented Jul 1, 2015

I looked a lot about that. I tried nearly everything but it doesn't work.

type Model struct {
    Id        string    `sql:"size:36" gorm:"primary_key;"`
    CreatedAt time.Time `sql:"DEFAULT:current_timestamp"`
    UpdatedAt time.Time `sql:"DEFAULT:current_timestamp"`
    DeletedAt time.Time
}

type Domain struct {
    Model
    Extension string `sql:"size:255;"`
}

type User struct {
    Model
    Name     string `sql:"not null; size:255"`
    Email    string `sql:"not null; unique; size:255"`
    Password string `sql:"not null; size:255"`
    Birthday time.Time
    // Domain   Domain `gorm:"foreignkey:domain_id; foreigntype:Domain"`
    // Domain   Domain `gorm:"associationforeignkey:Domain"`
    // DomainId string `gorm:"ForeignKey:domain_id"`
    Role Role
}

In this example, I'm using mysql driver. The trials I made are based from old issues. I think there's no way to define has one relationship currently.

Also, one suggestion;
There's a great documentation website called http://readme.io. @jinzhu you can easily create great documentation using markdown.

@nkovacs

This comment has been minimized.

Show comment
Hide comment
@nkovacs

nkovacs Aug 7, 2015

Contributor

I ended up just calling AddForeignKey every time I call AutoMigrate on startup. It throws an error if the key already exists, which is annoying, but it works. Unfortunately there doesn't seem to be a way to check if the foreign key already exists, since you don't know the name of the foreign key (that's hidden inside gorm, you could hardcode it, but then your code will break if it's changed).

Contributor

nkovacs commented Aug 7, 2015

I ended up just calling AddForeignKey every time I call AutoMigrate on startup. It throws an error if the key already exists, which is annoying, but it works. Unfortunately there doesn't seem to be a way to check if the foreign key already exists, since you don't know the name of the foreign key (that's hidden inside gorm, you could hardcode it, but then your code will break if it's changed).

@aacanakin

This comment has been minimized.

Show comment
Hide comment
@aacanakin

aacanakin Aug 7, 2015

I'm still waiting for this issue to be solved. The problem of AddForeignKey is; we don't write auto migration code at the same file with model definition code. Foreign keys are properties of models and I think it should be solved with tagging.

I ended up writing a python script which has sqlalchemy models and a sync command line script. I use database/sql package in go part.

aacanakin commented Aug 7, 2015

I'm still waiting for this issue to be solved. The problem of AddForeignKey is; we don't write auto migration code at the same file with model definition code. Foreign keys are properties of models and I think it should be solved with tagging.

I ended up writing a python script which has sqlalchemy models and a sync command line script. I use database/sql package in go part.

@WnP

This comment has been minimized.

Show comment
Hide comment
@WnP

WnP Aug 12, 2015

the documentation part on foreign keys and relations realy need to be improved/updated/wrote, most opened and closed issues are related to the lack of documentation, for example there's nothing related to many2one relation… well I guess you've got the point

Also the foreign keys should -imho- be created and managed by automigrate.

@jinzhu, hope you understand

WnP commented Aug 12, 2015

the documentation part on foreign keys and relations realy need to be improved/updated/wrote, most opened and closed issues are related to the lack of documentation, for example there's nothing related to many2one relation… well I guess you've got the point

Also the foreign keys should -imho- be created and managed by automigrate.

@jinzhu, hope you understand

@blachniet

This comment has been minimized.

Show comment
Hide comment
@blachniet

blachniet Oct 26, 2015

I just tripped up on this one too. If AutoMigrating foreign keys is not going to be supported, I think there should at least be a clean way to AddForeignKeyIfNotExists.

blachniet commented Oct 26, 2015

I just tripped up on this one too. If AutoMigrating foreign keys is not going to be supported, I think there should at least be a clean way to AddForeignKeyIfNotExists.

@Quentin-M

This comment has been minimized.

Show comment
Hide comment
@Quentin-M

Quentin-M commented Nov 10, 2015

+1

@JeanLebrument

This comment has been minimized.

Show comment
Hide comment
@JeanLebrument

JeanLebrument commented Nov 26, 2015

+1

@jrletosa

This comment has been minimized.

Show comment
Hide comment
@jrletosa

jrletosa commented Dec 4, 2015

+1

@mraxus

This comment has been minimized.

Show comment
Hide comment
@mraxus

mraxus commented Dec 4, 2015

+1

@robvdl

This comment has been minimized.

Show comment
Hide comment
@robvdl

robvdl Jan 3, 2016

+1

The explanation on the previous issue #349 about some databases not supporting foreign keys, so we won't support foreign keys in AutoMigrate at all is a bit stupid, and I am really sorry to say so. I don't want to be rude, but what databases don't support foreign keys these days? probably only MySQL with MyISAM and that's about it? But even MySQL is defaulting to InnoDB now, it only used to default to MyISAM but not anymore.

I agree with others that this feature really is a MUST, what's the point of bridge tables that have no foreign keys on them? You don't get any referential integrity checking and that is dangerous and leads to bad data. Is it really that difficult to only add foreign keys on DB systems that support it? It doesn't sound that hard. I imagine most of us would be on PostgreSQL these days anyway, so we really want proper bridge tables to be created.

robvdl commented Jan 3, 2016

+1

The explanation on the previous issue #349 about some databases not supporting foreign keys, so we won't support foreign keys in AutoMigrate at all is a bit stupid, and I am really sorry to say so. I don't want to be rude, but what databases don't support foreign keys these days? probably only MySQL with MyISAM and that's about it? But even MySQL is defaulting to InnoDB now, it only used to default to MyISAM but not anymore.

I agree with others that this feature really is a MUST, what's the point of bridge tables that have no foreign keys on them? You don't get any referential integrity checking and that is dangerous and leads to bad data. Is it really that difficult to only add foreign keys on DB systems that support it? It doesn't sound that hard. I imagine most of us would be on PostgreSQL these days anyway, so we really want proper bridge tables to be created.

@jinzhu

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Jan 3, 2016

Owner

https://github.com/jinzhu/gorm/blob/master/CONTRIBUTING.md

I could only develop feature request when I have time, pull request is welcome.

Owner

jinzhu commented Jan 3, 2016

https://github.com/jinzhu/gorm/blob/master/CONTRIBUTING.md

I could only develop feature request when I have time, pull request is welcome.

@robvdl

This comment has been minimized.

Show comment
Hide comment
@robvdl

robvdl Jan 3, 2016

oh cool @jinzhu, my apologies I thought the issue was closed completely, but this is cool as I have about 2 more weeks at home before I go to back work, plenty of time to have a go at this.

robvdl commented Jan 3, 2016

oh cool @jinzhu, my apologies I thought the issue was closed completely, but this is cool as I have about 2 more weeks at home before I go to back work, plenty of time to have a go at this.

@jinzhu jinzhu added type:feature and removed #relation labels Jan 4, 2016

@nkovacs

This comment has been minimized.

Show comment
Hide comment
@nkovacs

nkovacs Mar 22, 2016

Contributor

@jinzhu What is the correct way to get an arbitrary model's table name and an arbitrary field's db name?
I'm currently using db.NewScope(FooModel{}).TableName() and field, ok := db.NewScope(FooModel{}).FieldByName("SomeField"); field.DBName. Is this right?

Contributor

nkovacs commented Mar 22, 2016

@jinzhu What is the correct way to get an arbitrary model's table name and an arbitrary field's db name?
I'm currently using db.NewScope(FooModel{}).TableName() and field, ok := db.NewScope(FooModel{}).FieldByName("SomeField"); field.DBName. Is this right?

@enmanuelr

This comment has been minimized.

Show comment
Hide comment
@enmanuelr

enmanuelr commented Apr 16, 2016

+1

@logie17

This comment has been minimized.

Show comment
Hide comment
@logie17

logie17 commented May 26, 2016

+1

@yoshiya0503

This comment has been minimized.

Show comment
Hide comment
@yoshiya0503

yoshiya0503 commented May 30, 2016

+1

@dylanninin

This comment has been minimized.

Show comment
Hide comment
@dylanninin

dylanninin commented Sep 2, 2016

+1

@tourist-py

This comment has been minimized.

Show comment
Hide comment
@tourist-py

tourist-py commented Sep 5, 2016

+1

@stanisdev

This comment has been minimized.

Show comment
Hide comment
@stanisdev

stanisdev Oct 29, 2016

For example:

db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")

stanisdev commented Oct 29, 2016

For example:

db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")

@nmabhinandan

This comment has been minimized.

Show comment
Hide comment
@nmabhinandan

nmabhinandan commented Nov 20, 2016

+1

@SilverCory

This comment has been minimized.

Show comment
Hide comment
@SilverCory

SilverCory commented May 26, 2017

+2

@roobre

This comment has been minimized.

Show comment
Hide comment
@roobre

roobre May 30, 2017

Hello all,

Correct me if I'm wrong, but doesn't this issue also affect CreateTable() too? Foreign keys dont seem to be generated through any means other than manually (through Model().AddForeignKey()).

As someone has already pointed out, doing it that way requires to refer to model field names outside the struct definition. This is not intuitive, and can cause code maintenance problems.

Unfortunately, I'm just getting started with go, so my skills aren't good enough to dive into some as complex as an orm. I'm sorry I can't write a PR myself :(. Any updates regarding this would be greatly appreciated tho.

roobre commented May 30, 2017

Hello all,

Correct me if I'm wrong, but doesn't this issue also affect CreateTable() too? Foreign keys dont seem to be generated through any means other than manually (through Model().AddForeignKey()).

As someone has already pointed out, doing it that way requires to refer to model field names outside the struct definition. This is not intuitive, and can cause code maintenance problems.

Unfortunately, I'm just getting started with go, so my skills aren't good enough to dive into some as complex as an orm. I'm sorry I can't write a PR myself :(. Any updates regarding this would be greatly appreciated tho.

@joaoaneto

This comment has been minimized.

Show comment
Hide comment
@joaoaneto

joaoaneto commented Aug 9, 2017

+1

@mabana

This comment has been minimized.

Show comment
Hide comment
@mabana

mabana commented Sep 13, 2017

+1

@ezk84

This comment has been minimized.

Show comment
Hide comment
@ezk84

ezk84 Sep 24, 2017

Contributor

+1

Contributor

ezk84 commented Sep 24, 2017

+1

@carrollgt91

This comment has been minimized.

Show comment
Hide comment
@carrollgt91

carrollgt91 Oct 3, 2017

I've gotten started on an implementation here: https://github.com/carrollgt91/gorm/tree/auto-foreign-key

Still very much a WIP, but it does correctly handle belongs_to, has_one, and has_many relationships during the table creation process. It also will AutoMigrate dependent tables to ensure that the addition of the foreign key constraints is possible without requiring the user to be concerned about the order of their AutoMigrate calls.

I have a few concerns, though -

  1. Error propagation - I'm not sure I have a very good grasp on how these migration errors make their way back up to the initial db.Model(&Model{}).AutoMigrate call
  2. sqlite - this will break AutoMigrate with sqlite wherever you have relationships specified in your models. I have not seen anywhere within the scope.go file in which you change the implementation based on dialect - maybe I should push this implementation down to the dialect level so that it will not execute for sqlite?
  3. onDelete and onUpdate - currently, there's no way to specify these in the struct tags for foreign keys; might be nice to allow users to specify them so as not to hardcode this value into the autoForeignKey functionality.

@jinzhu I would love to discuss strategies for implementing this in such a way that you'd find acceptable. I'm quite happy to put in the time to get it working.

carrollgt91 commented Oct 3, 2017

I've gotten started on an implementation here: https://github.com/carrollgt91/gorm/tree/auto-foreign-key

Still very much a WIP, but it does correctly handle belongs_to, has_one, and has_many relationships during the table creation process. It also will AutoMigrate dependent tables to ensure that the addition of the foreign key constraints is possible without requiring the user to be concerned about the order of their AutoMigrate calls.

I have a few concerns, though -

  1. Error propagation - I'm not sure I have a very good grasp on how these migration errors make their way back up to the initial db.Model(&Model{}).AutoMigrate call
  2. sqlite - this will break AutoMigrate with sqlite wherever you have relationships specified in your models. I have not seen anywhere within the scope.go file in which you change the implementation based on dialect - maybe I should push this implementation down to the dialect level so that it will not execute for sqlite?
  3. onDelete and onUpdate - currently, there's no way to specify these in the struct tags for foreign keys; might be nice to allow users to specify them so as not to hardcode this value into the autoForeignKey functionality.

@jinzhu I would love to discuss strategies for implementing this in such a way that you'd find acceptable. I'm quite happy to put in the time to get it working.

@MOZGIII

This comment has been minimized.

Show comment
Hide comment
@MOZGIII

MOZGIII Dec 2, 2017

Maybe it's not relevant, but you can create foreign keys easily if you're using postgres database like so:

type Source struct {
	ID int64
	Name string
}

type File struct {
	ID int64
	SourceID int64 `gorm:"type:bigint REFERENCES sources(id)"`
}

So, again, just a note that you already have it on PostgreSQL.

Of course, making gorm know more about foreign keys is general is much better approach.

MOZGIII commented Dec 2, 2017

Maybe it's not relevant, but you can create foreign keys easily if you're using postgres database like so:

type Source struct {
	ID int64
	Name string
}

type File struct {
	ID int64
	SourceID int64 `gorm:"type:bigint REFERENCES sources(id)"`
}

So, again, just a note that you already have it on PostgreSQL.

Of course, making gorm know more about foreign keys is general is much better approach.

@Bleser92

This comment has been minimized.

Show comment
Hide comment
@Bleser92

Bleser92 Feb 11, 2018

Is there any progress?

Bleser92 commented Feb 11, 2018

Is there any progress?

@zignd

This comment has been minimized.

Show comment
Hide comment
@zignd

zignd Feb 11, 2018

For now github.com/mattes/migrate is working fine for me. I'm just mentioning it here for those of you looking for an alternative solution, as GORM still doesn't have this feature built-in.

zignd commented Feb 11, 2018

For now github.com/mattes/migrate is working fine for me. I'm just mentioning it here for those of you looking for an alternative solution, as GORM still doesn't have this feature built-in.

@jinzhu

This comment has been minimized.

Show comment
Hide comment
@jinzhu

jinzhu Feb 11, 2018

Owner

Also you could check out https://github.com/go-gormigrate/gormigrate

Btw, I will look into this feature in v2, hopefully, we could have it then.

Owner

jinzhu commented Feb 11, 2018

Also you could check out https://github.com/go-gormigrate/gormigrate

Btw, I will look into this feature in v2, hopefully, we could have it then.

@MOZGIII

This comment has been minimized.

Show comment
Hide comment
@MOZGIII

MOZGIII Feb 11, 2018

I have implemented a pretty cool in-house migration system, that main feature is when generating new migraion it can run gorm automigrate and diff the database schema of the last version to the automigrated version. It then pre-populates the migration file with the right SQL statements.
It's been a huge time-saver for me, unfortunately I cannot share the code. I can help with building something similar in the open though, contact me if you're interested.

MOZGIII commented Feb 11, 2018

I have implemented a pretty cool in-house migration system, that main feature is when generating new migraion it can run gorm automigrate and diff the database schema of the last version to the automigrated version. It then pre-populates the migration file with the right SQL statements.
It's been a huge time-saver for me, unfortunately I cannot share the code. I can help with building something similar in the open though, contact me if you're interested.

@jinzhu jinzhu added this to the v2.0 milestone Feb 13, 2018

@bassrock

This comment has been minimized.

Show comment
Hide comment
@bassrock

bassrock commented Mar 4, 2018

@dongshimou

This comment has been minimized.

Show comment
Hide comment
@dongshimou

dongshimou commented Mar 30, 2018

+1

@dalu

This comment has been minimized.

Show comment
Hide comment
@dalu

dalu Jul 11, 2018

Someone could make good money creating a "Golang ORM" kickstarter.
gorm is the defacto standard in Go's ORM landscape and it's missing essential features.
There's also my Google's 1st result https://github.com/go-pg/pg but it's even worse for documentation and only works with postgresql.
Google has the manpower but wants to promote their own infrastructure "Rent our DB product we're providing all the necessary tools".
We need a Google for open source software with it's open source search engine and ads and this would fund open source project creation with dedicated teams who are paid for their work so it's pushed forward.

dalu commented Jul 11, 2018

Someone could make good money creating a "Golang ORM" kickstarter.
gorm is the defacto standard in Go's ORM landscape and it's missing essential features.
There's also my Google's 1st result https://github.com/go-pg/pg but it's even worse for documentation and only works with postgresql.
Google has the manpower but wants to promote their own infrastructure "Rent our DB product we're providing all the necessary tools".
We need a Google for open source software with it's open source search engine and ads and this would fund open source project creation with dedicated teams who are paid for their work so it's pushed forward.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment