Cannot read "one to many" Relation with go-pg

Question

I am trying to implement a little state machine with go and store my states in a postgres db.

i created my database like this:

CREATE TABLE state_machines
(
   id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
   initial_state TEXT NOT NULL,
   "name" TEXT NOT NULL
);

CREATE TABLE state_machine_states
(
   state_machine_id uuid NOT NULL REFERENCES state_machines(id) ON DELETE CASCADE,
   "name" TEXT NOT NULL,
   PRIMARY KEY(state_machine_id, "name")
);

// StateMachine is the DB schema
type StateMachine struct {
    ID           *uuid.UUID                `pg:"id,pk,type:uuid,default:uuid_generate_v4()"`
    Name         string                    `pg:"name"`
    InitialState string                    `pg:"initial_state"`
    States       []*StateMachineState      `pg:"fk:state_machine_id"`
}

// StateMachineState is the DB schema
type StateMachineState struct {
    StateMachineID uuid.UUID `pg:"state_machine_id,fk"`
    Name           string    `pg:"name"`
}

I am using go-pg 9.2 and i am trying to load a state machine and a list of its states from the "States" relation.

My function to load the state machines looks like this:

func (cep *repository) GetStateMachines() ([]*StateMachine, error) {
    stateMachines := []*StateMachine{}

    err := cep.db.Model(&stateMachines).
        Relation("States").
        Relation("Transitions").
        Select()

    return stateMachines, err
}

If I execute it, I always get the error message Error reading state machine: model=StateMachine does not have relation="States"

I have done similar relations before and they worked and now, I cannot get it to work again :(

Answer 1

See if there is .Debug() function in go-pg for a query. I use https://gorm.io/ , and there is a Debug funcion, that returns all of the SQL queries. When i see what queries are send, i log in postgresql and try them manually to see a more detailed error.

Answer 2

Try upgrading to v10 which fully supports relations: https://pg.uptrace.dev/orm/has-many-relation/