Go relationship error when migrating models using gorm

Question

I am new to go/golang and I have been learning it by trying to create a small project. I have written several models in my go project corresponding to the db tables I want in my db. I have opted to use PostgreSQL, when migrating the models I get the following error

ERROR: relation "personal_informations" does not exist (SQLSTATE 42P01)
[1.657ms] [rows:0] CREATE TABLE "employee_leaves_requests" 
("id" bigserial,"created_at" timestamptz,"updated_at" timestamptz,"deleted_at" timestamptz,
"emp_no" bigint NOT NULL,"reason" text NOT NULL,"leave_type_id" bigint NOT NULL,
"days_remaining" bigint NOT NULL,"leave_limit" text NOT NULL,"from_date" timestamptz NOT NULL,
"to_date" timestamptz NOT NULL,"leave_status" text NOT NULL,"status" text NOT NULL,"document_url" text NOT NULL,
PRIMARY KEY ("id"),CONSTRAINT "fk_personal_informations_employee_leaves_request" FOREIGN KEY ("emp_no") 
REFERENCES "personal_informations"("id"),CONSTRAINT "fk_leave_types_employee_leaves_request" 
FOREIGN KEY ("leave_type_id") REFERENCES "leave_types"("id"))

These are the models that are not being migrated

type PersonalInformation struct {
    gorm.Model
    EmpNo         string `gorm:"not null"`
    Gender        string `gorm:"not null"`
    Surname       string `gorm:"not null"`
    FirstName     string `gorm:"not null"`
    MiddleName    string
    Dob           time.Time `gorm:"not null"`
    RoleID        uint      `gorm:"not null"`
    Town          string
    Country       string
    Location      string
    PhoneNumber   string `gorm:"not null"`
    MaritalStatus bool   `gorm:"not null"`
    ImageURL      string `gorm:"not null"`
    IsInsured     bool   `gorm:"not null"`
    Status        string

    Role                  Roles                    `gorm:"foreignKey:RoleID"`
    EmployeeLeavesRequest []EmployeeLeavesRequests `gorm:"foreignKey:EmpNo"`
    EmployeeLeave         []EmployeeLeaves         `gorm:"foreignKey:EmpNo"`
}

type EmployeeLeaves struct {
    gorm.Model
    EmpNo           string `gorm:"not null"`
    LeaveTypeId     uint   `gorm:"not null"`
    LeaveLimit      string `gorm:"not null"`
    AllocatedLeaves string `gorm:"not null"`
    Status          string `gorm:"not null"`

    PersonalInfo PersonalInformation `gorm:"foreignKey:EmpNo"`
    LeaveType    LeaveTypes          `gorm:"foreignKey:LeaveTypeId"`
}

type EmployeeLeavesRequests struct {
    gorm.Model
    EmpNo         string    `gorm:"not null"`
    Reason        string    `gorm:"not null"`
    LeaveTypeId   string    `gorm:"not null"`
    DaysRemaining int       `gorm:"not null"`
    LeaveLimit    string    `gorm:"not null"`
    FromDate      time.Time `gorm:"not null"`
    ToDate        time.Time `gorm:"not null"`
    LeaveStatus   string    `gorm:"not null"`
    Status        string    `gorm:"not null"`
    DocumentUrl   string    `gorm:"not null"`

    PersonalInfo PersonalInformation `gorm:"foreignKey:EmpNo"`
    LeaveType    LeaveTypes          `gorm:"foreignKey:LeaveTypeId"`
}

This is the method used to migrate

func MigrateModels() {
    dbName := "testdb"
    dbUser := "user"
    dbPassword := ""
    dbHost := "localhost"
    dbPort := "5432"

    connStr := fmt.Sprintf("user=%s password=%s dbname=%s host=%s port=%s sslmode=disable", dbUser, dbPassword, dbName, dbHost, dbPort)

    db, err := gorm.Open(postgres.New(postgres.Config{
        DSN: connStr,
    }), &gorm.Config{})

    if err != nil {
        panic("failed to connect database")
    }

    db.AutoMigrate(
        &Users{},
        &PersonalData{},
        &Roles{},
        &Teams{},
        &Departments{},
        &Holidays{},
        &PersonalInformation{},
        &LeaveTypes{},
        &EmployeeLeaves{},
        &EmployeeLeavesRequests{},
    )

    if err != nil {
        fmt.Println("Error migrating the models:", err)
        return
    }

    fmt.Println("Database migration completed successfully!")
}

Where could the error be coming from?

score 0 · Answer 1 · answered May 21 '23 at 16:40

It looks the issue happening here is due to the circular dependancy between the models.

PersonalInformation model has a relationship with EmployeeLeavesRequest and EmployeeLeave. But again you defined a relationship in EmployeeLeave and EmployeeLeavesRequest to PersonalInformation as well.

type PersonalInformation struct {
    ...
    Role                  Roles                    `gorm:"foreignKey:RoleID"`
    EmployeeLeavesRequest []EmployeeLeavesRequests `gorm:"foreignKey:EmpNo"`
    EmployeeLeave         []EmployeeLeaves         `gorm:"foreignKey:EmpNo"`
}

type EmployeeLeaves struct {
    ...
    PersonalInfo PersonalInformation `gorm:"foreignKey:EmpNo"`
}

type EmployeeLeavesRequests struct {
    ...
    PersonalInfo PersonalInformation `gorm:"foreignKey:EmpNo"`
}

As a result, the dependency becomes circular.

The order of GORM Migration

In the absence of dependencies between models, GORM will migrate them in the order that they are defined in code.
As long as there are dependencies between models, it will generate tables in the correct order to comply with foreign key constraints.

Therefore, the GORM tries to create employee_leaves before creating personal_informations, and it fails.

Solution

Make sure your model structs are defined in an order that respects their dependencies for the correct migration order.

You can remove the dependency with PersonalInformation from EmployeeLeaves and EmployeeLeavesRequests.

Enable logging to see the logs, and DryRun to generate SQL without running it, so that you can see the migration SQL order.

    db, err := gorm.Open(postgres.New(postgres.Config{
        DSN: connStr,
    }), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info), // Enable logging mode
        DryRun: true, // DryRun generate sql without execute
    })

Go relationship error when migrating models using gorm

1 Answers1