gorm, foreign keys, and embedded structs

Question

Gorm's half-baked, magic-out-the-box support of foreign keys has been an annoyance for years and I'm finally trying to figure it out once and for all. I'm using Postgres 12, gorm 1.23.3 and go 1.18.

I have a base model similar to gorm.Model but with a little bit extra:

type BaseModel struct {
    ID              string          `json:"id" gorm:"type:uuid;primarykey;default:uuid_generate_v4()"`
    InstanceVersion int             `json:"instanceVersion"`
    CreatedAt       time.Time       `json:"createdAt" gorm:"type:timestamp"`
    UpdatedAt       time.Time       `json:"updatedAt" gorm:"type:timestamp"`
    DeletedAt       *time.Time      `json:"deletedAt,omitempty" gorm:"type:timestamp" sql:"index"`
    CreatedBy       string          `json:"createdBy"`
    UpdatedBy       string          `json:"updatedBy"`
    DeletedBy       string          `json:"deletedBy,omitempty"`
    MetaData        json.RawMessage `json:"metadata" gorm:"type:jsonb;default:'{}'"`
}

Every model in my DB uses this BaseModel as follows:

type Profile struct {
    BaseModel

    Name   string `json:"name"`
    UserID string `json:"userId"`
}

It generates the tables as follows (UML generated by DBeaver and double checked to be true):

I'm trying to add a foreign key to the CreatedBy and UpdatedBy columns such that they must point to an existing Profile. So I add the following field to the BaseModel type:

    CreatedByProfile *Profile `json:"-" gorm:"foreignKey:CreatedBy"`

I expected the foreign key to be created for every model that BaseModel is a part of and point back to the Profiles table. However, it only makes the FK on the Profiles table.

Minimal recreation of the problem:

package main

import (
    "encoding/json"
    "time"

    "github.com/lib/pq"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type BaseModel struct {
    ID              string          `json:"id" gorm:"type:uuid;primarykey;default:uuid_generate_v4()"`
    InstanceVersion int             `json:"instanceVersion"`
    CreatedAt       time.Time       `json:"createdAt" gorm:"type:timestamp"`
    UpdatedAt       time.Time       `json:"updatedAt" gorm:"type:timestamp"`
    DeletedAt       *time.Time      `json:"deletedAt,omitempty" gorm:"type:timestamp" sql:"index"`
    CreatedBy       string          `json:"createdBy"`
    UpdatedBy       string          `json:"updatedBy"`
    DeletedBy       *string         `json:"deletedBy,omitempty"`
    MetaData        json.RawMessage `json:"metadata" gorm:"type:jsonb;default:'{}'"`

    CreatedByProfile *Profile `json:"-" gorm:"foreignKey:CreatedBy"`
}

type ActivityType string

type Activity struct {
    Base BaseModel `gorm:"embedded"`

    Type                ActivityType   `json:"type"`
    Message             string         `json:"message"`
    Content             string         `json:"content"`
    ImageUrl            string         `json:"imageUrl"`
    DisplayProfileIds   pq.StringArray `json:"displayProfileIds" gorm:"type:uuid[]"`
    RecipientProfileIds pq.StringArray `json:"recipientProfileIds" gorm:"-"`

    // Preload
    ActivityProfiles []*ActivityProfile `json:"activityProfiles"` // has many
}

type ActivityProfile struct {
    Base BaseModel `gorm:"embedded"`

    ReadAt *time.Time `json:"readAt,omitempty" gorm:"type:timestamp" sql:"index"`
    Route  string     `json:"route"`

    ActivityID string `json:"activityId"`
    ProfileID  string `json:"profileId"`

    // Preload
    Activity *Activity `json:"activity"` // belongs to
    Profile  *Profile  `json:"profile"`  // belongs to
}

type Profile struct {
    BaseModel

    Name   string `json:"name"`
    UserID string `json:"userId"`
}

func main() {
    db, err := gorm.Open(postgres.Open("host=localhost port=5432 user=corey dbname=corey password= sslmode=disable"))
    if err != nil {
        panic(err)
    }

    models := []interface{}{
        &Activity{},
        &ActivityProfile{},
        &Profile{},
    }

    err = db.AutoMigrate(models...)
    if err != nil {
        panic(err)
    }
}

I've also tried using gorm:"embedded" tags instead of nested structs but it didn't help. Nothing in this question helps: DB.Model(...).AddForeignKey(...) doesn't exist anymore, the db.Migrator().CreateConstraint(...) lines don't work (how does it know what column is the FK and which column it matches to of what other type? Do I have to run both lines? How could this ever possibly work?!?), and I don't want OnUpdate or OnDelete constraints, just foreign keys.

If I put the CreatedByProfile field on Activity, then I get a FK where Profile.CreatedBy is an FK to Activity.ID which is 100% backwards.

I can add this field to my Profile model:

Activities []*Activity `json:"-" gorm:"foreignKey:CreatedBy"`

and it creates the FK like I want, but I don't actually want that field present on the model. Plus, I'd have to add this unnecessary boilerplate for every model in my DB (and _ fields don't end up with the FK being made).

How can I make Gorm do simple things like create foreign keys without decorating my models with unused fields?

gorm, foreign keys, and embedded structs

0 Answers