Gorm: 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.

[Bleser]

Is there any progress?

[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:

1. Embed the examples in a working context that actually creates the keys in the database. Just like the great front page example just works.
2. 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.
3. Expand on the examples themselves by showcasing the differences between the types of associations. Printing the resulting tables might help.
4. 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()._

[robvdl]

If you plan to review the documentation, another real "gotcha" moment you get from using gorm is soft deletes. The docs go on to say you can use the Model base class for your models as I recall (using struct embedding), but it doesn't go on to say that this adds both a created_at and deleted_at column to each table. I don't mind a created_at column as it's useful for debugging, but deleted_at is a pretty bad default to have as it does soft deletes. e.g. marking the item as deleted but not really deleting it.

Soft deletes make querying harder as now in each query you have to filter your table queries as SELECT where NOT deleted_at etc... which is just another thing to remember.

I don't like soft deletes one bit, but mostly I think it needs to be extremely clear in the documentation that the author has made this decision to use soft deletes, and how to get out of using them.

Personally, when I was using gorm, I created my own model base class (struct) that only has the created_at field, therefore it won't use the soft delete functionality I dislike so much.

[goldyfruit]

I spent 3 hours looking for why my foreign key was not created using AutoMigrate().

As mentioned few times above, just a warning in the documentation saying that foreign keys are not supported and should be created with AddForeignKey() will be enough so far.

It's also possible to do this to add foreign key during the AutoMigrate():

db.AutoMigrate(Event{}).AddForeignKey("user_id", "users(id)", "RESTRICT", "RESTRICT")

Thanks for the hard work @jinzhu, it's really appreciate !

[muety]

I spent an hour to find out why FKs are not getting created until I found this issue.
Thanks @goldyfruit, that solution works.

[ghost]

thanks @goldyfruit for the solution, initially proposed by @nkovacs and thanks @jinzhu for the amazing work you have done till now.
I would like to contribute, starting with this issue, but I'm still a newbie with GoLang (i used to be a Django backend developer)

[sentriz]

hmm
AddForeignKey() uses ALTER TABLE _ ADD CONSTRAINT which is not supported by sqlite

EDIT
I found this comment helpful

[AhmedMKamal]

Okay, solution mentioned by @goldyfruit works but I'm getting an error every time I call AddForeignKey since the foreign key already exists and that's VERY annoying, is there any updates or upcoming updates?

[TianDaGe]

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

// File represents file
type File struct {
    Csurrogat int `gorm:"primary_key"`
    Name      string
    Positions []Position `gorm:"foreignkey:cfilesur;association_foreignkey:csurrogat"`
}

// Position represents position
type Position struct {
    Csurrogat int `gorm:"primary_key"`
    Cfilesur  int `sql:"type:integer REFERENCES files(csurrogat)"`
    Name      string
}

func main() {
    var err error
    var db *gorm.DB

    db, err = gorm.Open("sqlite3", "sample.db")
    if err != nil {
        panic("failed to connect sqlite database")
    }
    defer db.Close()

    // Migrate the schema
    db.AutoMigrate(&File{})
    db.AutoMigrate(&Position{})

    db.Create(&File{Csurrogat: 1, Name: "sample"})
    db.Create(&Position{Csurrogat: 1, Cfilesur: 1, Name: "file1"})
    db.Create(&Position{Csurrogat: 2, Cfilesur: 1, Name: "file2"})
    db.Create(&Position{Csurrogat: 3, Cfilesur: 1, Name: "file3"})

    files := []*File{}
    if err := db.Find(&files).Error; err != nil {
        panic(err)
    }

    for _, file := range files {
        db.Model(&file).Related(&file.Positions)
        fmt.Println(file.Positions)
    }

    fmt.Println(files[0])
}

This is not working at all. it is showing empty array for positions.

[]
&{1 sample []}

What is wrong?

[rvarner]

This issue has been open for 4 years 9 months 3 days 17 hours 36 minutes. Can we get an update please?

[jessequinn]

lol. another issue is pointed here and yet it isnt resolved.

Any progress on this basic fundamental aspect?

[jinzhu]

@rvarner @jessequinn done in v2

[LaysDragon]

hi @jinzhu did you mean AutoMigrate can manage foreign keys in gorm v2?

[jinzhu]

@LaysDragon yes.

[pkaramol]

How do we explicitly use v2?

When importing

"github.com/jinzhu/gorm/v2"

I get the error message

| main.go:9:2: module github.com/jinzhu/gorm@latest found (v1.9.12), but does not contain package github.com/jinzhu/gorm/v2

[GrigoriyMikhalkin]

@pkaramol
Just install it with go get github.com/jinzhu/gorm@v2_dev.

Also, it's currently in development, and as you can see, keeps same module name(i.e. without /v2 suffix). So you need to import it as github.com/jinzhu/gorm (or use replace directive in your local go.mod if, still, you want to import it as github.com/jinzhu/gorm/v2)