Gorm: Please considering support 'using tag to generate forerign keys'

[cjun714]

This is first time I use Gorm, it's really amazing and very easy to use.
But it cost me lots of time to figure out how to make 'foreign key' work, which I thought should already be perfect for a long time.
I searched most articles on StackOverflow, Reddit, GitHub and even Qiita, but no satisfied way to support foreign key .

Here are the issues:

1. You can use gorm:"foreignkey:", but it doesn't create any foreign key, not like primary_key, that's quite counter-intuitive.
2. You need use gorm:"type:int REFERENCES user(id) ON DELETE CASCADE" for PostgteSQL#450.
3. You need use db.Model(Email{}).AddForeignKey("user_id", "user(id)", "CASCADE", "CASCADE") for MySQL, because above tag doesn't work in MySQL.
4. You need use gorm:"type:int REFERENCES user(id) ON DELETE CASCADE" + db.Exec("PRAGMA foreign_keys = ON") for SQLite because AddForeignKey() doesn't work for SQLite and 'foreign_keys = off' is default setting in SQLite#390.
5. None of above things are mentioned in document.
6. I am still confusing what's the usage of tag gorm:"foreignkey:XXX", it doesn't create any foreign key or contribute cascade delete/insert/update/retrieve.

It's complex if you want to use foreign key and support several databases.

The biggest problem is:
We can't find a way to support MySQL/SQLite/PostgreSQL simultaneously if we want to use foreign key.
If you change DB, you also need change code to define model, because Gorm has not universal definition to create foreign key.
But in fact, most databases support using same SQL and syntax to create foreign keys(ref).

Does supporting 'tag generating foreign key' impact migrate and loop reference case?#258
No, I am not sure.
I think providing a new choice wouldn't make anything worse, since people can still fallback to use old way to create foreign key.

Not supporting 'using tag generating foreign key' has caused many issues:

1. Incompatible code: if you switch DB, you need change both model definition and application code.
2. Unmaintainable code:
   Refer other table/field name in a model definition is worse than referring other model name.
   Should avoid using AddForeignKey() as possible because it's not compatible, unless you need handle loop reference or other cornner case.
3. Lead to counter-intuitive usage, lots of questions are about why foreign key doesn't work as expect.
4. Difficult to document.
5. Need more educate effort.

This issue has been reported again and again since 2013, lots of time wasted to figure out why foreign key doesn't work and how make it work, but no essential improvement was made.

Foreign key is still much more important and common used everywhere, against loop reference(if I'm wrong, please correct me).

Support 'tag generating foreign key' can resolve most above issues, without making anything worse.
People can still use AddForeignKey() to handle cornner case like 'loop reference in PostgreSQL'.
If they don't need 'loop reference', they can use tag to generate most tables and foreign keys, that would be great.

My suggestions:

1. Gorm can provide an universal way(tag) to define most models and foreign keys.
2. Just use AddForeignKey() to handle cornner case(like loop reference in PostgreSQL)#258
3. Document these conner cases, let user to choose using tag or AddForeignKey().

I understand balancing features is quit difficult than I thought, lots of thing need considering, please consider it.
Great thanks for your amazing work.

[cjun714]

@dalu I haven't reach ManyToMany yet, but soon I will. If I meet similar problems, I will update this thread.

[cryptix]

@cjun714 have you been able to continue working on this?

I myself use postgress so I'm not that much blocked by it but the whole thing is very confusing and irritating...

[apocelipes]

I ran into the same problem. When using sqlite3, the "foreignkey" tag does not add a foreign key to the table. The document does not explicitly indicate the method of creating the foreign key. Foreign keys are useful features for many people. If you can't provide good support for foreign keys, it will greatly affect the use of orm.

[Adirio]

Adding a tag (or actually three tags) would be the best option in my opinion too.

It also adds another benefit. If the Model knows about foreign keys, soft-deletes couls also behave appropiately, setting DeletedAt of children fields to the same time that the parents.

Proposed syntax:

type User struct {
    gorm.Model
    Name    string
    Surname string
}

type CreditCard struct {
    gorm.Model
    Number         string
    ExpirationDate time.Time
    Owner          uint `gorm:"ForeignKey:User.ID;OnUpdate:Cascade;OnDelete:Cascade"`
}

That would create the following foreign key:

• Name of the column: "owner", taken from the struct's field after applying the usual transformation.
• Destiny table: "users", taken from the first part of the ForeignKey tag (before .). If we already have a model that represents this table, why should we have to write the table's name again? That would mean that the code will break if we change the default table name of User and would be harder to debug. Get it from the model and avoid these problems. Additionally a golang programmer would not need to know which are the rules that parse table names as inside Go it will always use the same names.
• Destiny column: "id", taken from thesecond part of the ForeignKey tag (after.). The tag ForeignKey:User without any second part should default to ForeignKey:User.ID so that it defaults to gorm.Model's behavior. As in the previous case, the golang field name "ID" has been used instead of the column name "id" as that would make debuging easier and avoiding to require knowledge of column name parsing rules.
• ON UPDATE behavior: "CASCADE", taken from "OnUpdate" tag.
• ON DELETE behavior: "CASCADE", taken from "OnDelete" tag.

As for how to solve the PostgreSQL issue with non-already-existing tables, I would probably add a method to the Dialect interface DelayForeignKeyCreation() bool and if it returns true, instead of creating the foreign keys with a TABLE CREATE command you will wait for all the tables to be migrated and then issue the ALTER TABLE commands mas with AddForeignKey.

EDIT: my previous proposal didn't take into account the case where you don't want to load all references, so the answer has been edited to reflect that.

[fr3fou]

any news?

[camathieu]

Really useful summary thanks @cjun714

Would you consider adding a page that recap how to setup foreign keys on the different databases supported to the wiki until this is addressed in v2 ?

[cjun714]

@camathieu sorry for late response, I am not familiar with GitHub wiki feature and I haven't coding db related code anymore, above content is all I found, I haven't dig it any more. I hope author or maintainers can add related content into doc, that will be more helpful, thanks.

[aclowkey]

I'm using SQLite for testing and MySQL for production.
Anyway to have it support both?