AutoMigrate foreign keys

[nkovacs]

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]

I'm also interested in the answer for this.

[codepushr]

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

[tp]

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]

previous issue: https://github.com/jinzhu/gorm/issues/349

[aacanakin]

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]

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]

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]

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]

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]

+1

[JeanLebrument]

+1

[jrletosa]

+1

[mraxus]

+1

[robvdl]

+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]

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

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

[robvdl]

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.

[nkovacs]

@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]

+1

[logie17]

+1

[yoshiya0503]

+1

[dylanninin]

+1

[tourist-py]

+1

[stanisdev]

For example:

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

[nmabhinandan]

+1

[SilverCory]

+2

[roobre]

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.

[netoax]

+1

[mabana]

+1

[ezk84]

+1

[carrollgt91]

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]

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.

[zignd]

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]

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]

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.

[bassrock]

@MOZGIII Something like https://symfony.com/doc/master/bundles/DoctrineMigrationsBundle/index.html for GORM would be fantastic.

[dongshimou]

+1

[MrMatten]

+1

[JYama321]

I have also same problem on gorm with mysql:5.7.

type User struct {
    gorm.Model
    UserName string
    Email string
}

type Subscription struct {
    gorm.Model
    User User `gorm:"ForeignKey:TargetUserID;AssociationForeignKey:ID"`
    UserID uint
    TargetUserID uint
}

This does not create foreign key.

I could associate with UserID in Subscription Table to ID in User Table like this document, but I want to associate two foreign keys, UserID and TargetUserID, from Subscription table to User Table. So I tried to do this in another way but I couldn't.

[fabrol]

@carrollgt91 Did you ever get a response / update on your patch ? Many years later, but seems like this is still a problem yes ?

[inliquid]

Problem is that it's not documented, so it seems almost everyone expect GORM to create constrains in DB while its actual meaning of "foreign key" tags is just internal relations between structs.

[robvdl]

I would go as far as saying the entire feature for creating tables from models is completely useless if it doesn't create foreign keys, agreed that it should be documented at least because currently it's extremely misleading.

[bartmika]

+1

[MOZGIII]

The solution I eventually came up with is just writing SQL manually. There are other tools that help with loading the query rows into structs, and with Go it seems to just be more idiomatic to take control over the SQL itself and the mapping between the structs and the database. Gorm can help here, but I'd avoid using it for table creation for example - simply because the complexity trade-off it introduces is not worth it. I mean, this issue, and a bunch of other cause problem when doing serious work with the database, and, while most of them can be solved or fixed, it takes the time to deal with - on the contrary, with just raw SQL (or SQL with builders) the time to write SQL is explicit, and you have way more control over the optimizations without using an ORM. And you don't have to deal with the bugs that ORMs introduce. Writing trivial stuff over and over again may become boring, thankfully there are ready-made SQL builders to help with that. Not saying that there are no use cases for ORMs and Gorm in particular - again, in my experience, it just seems that working with lower-level SQL builders is easier than with ORM in Go.

[rodolfomartinez]

+1

[wedi]

[rant]_Please stop saying you are developer friendly, immediately. It's ok when this does not work, but having examples which pretend this is working* is really, really unfriendly. Seriously._[/rant]

To emphasize: it is ok this is not resolved! We all have a limited time budget. But offering examples which seem to do something that is not working takes all lot from a new user's time budget. This issue with many duplicates with a lot of comments proofs this. So: Please mention this issue in the docs, at least.

* If someone would be nitpicking, he could say these examples do not pretent this is working as they are incomplete snippets but combined with the prominent quick start example on the front page new users are bound to hit this.

[Really stopping to rant now. This is my first rant @ github. Thanks for listening. I feel better now.]

[vlttnv]

Thank you @wedi for saying that these examples do not work. I cannot believe that there are code snippets in the documentation, of all places, that are just wishful things rather than actual examples.

[sadegh-shahbazi]

at last... does gorm create foreignkey or not? because work with gorm and check the database and i realize that gorm didn't make any foreignkey...

[robvdl]

No it doesn't automigrate still does not create foreign keys properly. I think most of the complaints in this long thread are about that and the fact the documentation doesn't clearly state it doesn't create foreign keys properly therefore misleading so many people. Basically people have moved on and used other migration libraries.

[mikepc]

And the foreignkey and associated_foreignkey defined on models do not work either. Since we're using postgres for this, I'm going to try the REFERENCES solution, we'll see if that works.

[wedi]

From what I read, gorm looks like a great tool with great potential to become a developer friendly ORM library. Having recovered from my frustration I'd be happy to review documentation pull requests that are targeted on getting new users going.

Working with single tables works like a charm and is userfriendly, indeed. From my first experience the problems arise as soon as one tries to tackle real life applications that need anything from the section Associations.

My suggestions First and foremost: add a note that AutoMigrate() is not supposed to add any keys to the database. I'd be happy make such a pull request myself if I get an approving response, @jinzhu. Next steps:

2. Embed the examples in a working context that actually creates the keys in the database. Just like the great front page example just works.
3. Align the examples contentwise. Choose one application and extend it step by step. I had the impression the docs jump arbitrarily between users/profiles/toys and credit cards so I got kicked out of the flow of understanding when moving ahead to the next one.
4. Expand on the examples themselves by showcasing the differences between the types of associations. Printing the resulting tables might help.
5. I put my focus on examples as we are developers and used to read code but finally some text work might help. I had a hard time to grasp the idea behind Belongs To and Has One.

This is a lot of work to do and I respect the time you and all contributors are already putting into a project this big. I am certainly not demanding these changes. What I would like to say is: the project is commiting itself by saying "The fantastic ORM library for Golang, aims to be developer friendly". Please, look at all those issues relating to this and respect other dev's time budget and add a note about the examples not actually creating keys in the database when used with AutoMigrate().