GORM UUID not being created before association insert

Question

I am using UUID as an primaryKey for my models, but when INSERTing or UPDATEing the UUID is nil (or the UUID equivalent). Here is the model code:

// --- base.go
import (
    "github.com/google/uuid"
    "gorm.io/gorm"
)

type Base struct {
    Id string `json:"id" gorm:"unique;default:gen_random_uuid()"`
}

func (b *Base) BeforeCreate(tx *gorm.DB) (err error) {
    b.Id = uuid.New().String()
    return
}

// --- survey.go
type Survey struct {
    Base
    //... truncated for brevity
    Questions []SurveyQuestion
}

// --- surveyQuestion.go
type SurveyQuestion struct {
    SurveyIdentifier string   `gorm:"primaryKey;constraint:onDelete:CASCADE;column:survey_identifier;"`
    Survey           Survey   `gorm:"foreignKey:SurveyIdentifier;references:Identifier"`
    QuestionID       string   `gorm:"primaryKey;constraint:onDelete:RESTRICT;column:question_id;"`
    Question         Question `gorm:"foreignKey:QuestionID;references:Id"`
    QuestionNum      int32    `gorm:"default:1;column:question_num;"`
}

// --- question.go
type Question struct {
    Base
    //... truncated for brevity
    Surveys  []SurveyQuestion
}

A Survey should be able to re-use Questions without creating duplicates in the database. At least until the question text changes, then a new row in the Question table will be created.

So one Survey has many Question and one Question can belong to many Surveys. Originally I did a many-to-many association for this, but could not figure out how to update the question_num column. I need that column because, depending on the Survey, the re-used question could be in a different position. I read an SO post that suggested doing a one to many and using a "join" table in the middle. I thought I'd try it, since I couldn't figure out how to update a column in a join table created by GORM, and it felt a little cleaner.

The code in question, where the Id for either Question or Survey models is not getting set upon Save:

var surveyModel *models.Survey
var surveyQuestions []models.SurveyQuestion

db.Transaction(func(tx *gorm.DB) error {
    // [truncated] ... create human-readable identity

    // [truncated] Create the survey model for the Database

    // Iterate over the payload of Questions
    for i, question := range input.Questions {
        // [truncated] ...

        // Get a question Type ID
        var questionTypeId *models.QuestionType
        err := tx.Select("id").First(&questionTypeId, "type = ?", question.Type).Error

        // [truncated] ... Handle errors related to not finding the question type
        

                // The Question model does not get an ID set and results in an error
        surveyQuestion := models.SurveyQuestion{
            Survey: *surveyModel,
            QuestionNum: int32(i + 1),
        }

        // ... create meta data

        questionModel := &models.Question{
            Text: question.Question,
            SubText: *question.Description,
            Weight: *question.Weight,
            Required: question.Required,
            QuestionTypeId: questionTypeId.Id,
            Options: choices,
            Meta: meta,
        }
        
                // Assign the Question Model to the "join" table
        surveyQuestion.Question = *questionModel
        surveyQuestions = append(surveyQuestions, surveyQuestion)
    }

    tx.Debug().Model(&surveyModel).Association("Questions").Append(surveyQuestions)
    tx.Debug().Model(&surveyQuestions).Association("Surveys").Append(&surveyModel)
    tx.Debug().Save(&surveyModel)

    // return nil wil commit the whole transaction
    return nil
})

return surveyModel, nil;

When it runs, it results in the following errors:

survey-api-1  | 2023-03-01T19:17:50.522058668Z 2023/03/01 19:17:50 /usr/src/service/graph/resolver/createSurvey.go:95 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)
survey-api-1  | 2023-03-01T19:17:50.522063578Z [2.655ms] [rows:0] INSERT INTO "survey_questions" ("survey_identifier","question_id","question_num") VALUES ('SUR-VJqI1kWB1h0gRFTu','',1),('SUR-VJqI1kWB1h0gRFTu','',2),('SUR-VJqI1kWB1h0gRFTu','',3),('SUR-VJqI1kWB1h0gRFTu','',4) ON CONFLICT ("survey_identifier","question_id") DO UPDATE SET "survey_identifier"="excluded"."survey_identifier"
survey-api-1  | 2023-03-01T19:17:50.522066908Z

survey-api-1  | 2023-03-01T19:17:50.522069198Z 2023/03/01 19:17:50 /usr/src/service/graph/resolver/createSurvey.go:95 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)
survey-api-1  | 2023-03-01T19:17:50.522077188Z [3.280ms] [rows:0] UPDATE "surveys" SET "updated_at"='2023-03-01 19:17:50.518' WHERE "identifier" = 'SUR-VJqI1kWB1h0gRFTu'
survey-api-1  | 2023-03-01T19:17:50.522670133Z

survey-api-1  | 2023-03-01T19:17:50.522687043Z 2023/03/01 19:17:50 /usr/src/service/graph/resolver/createSurvey.go:97 ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02)
survey-api-1  | 2023-03-01T19:17:50.522690973Z [0.561ms] [rows:0] UPDATE "surveys" SET "id"='',"existing_id"='',"prospective_id"='kddauth|3fb25baedf834002e8208692',"name"='On Boarding Survey',"description"='The Greatest Survey you\'ll ever take',"status"='UNSEEN',"created_at"='0000-00-00 00:00:00',"updated_at"='2023-03-01 19:17:50.521' WHERE "identifier" = 'SUR-VJqI1kWB1h0gRFTu'

The first two reference line 95 which is:

tx.Debug().Model(&surveyModel).Association("Questions").Append(surveyQuestions)

The last one references line 97 which is:

tx.Debug().Save(&surveyModel)

I am using PostgreSQL 15, which has gen_random_uuid() built into it.

At first, I had my Id annotated like this:

import "github.com/google/uuid"
//... other stuff
Id uuid.UUID `json:"id" gorm:"unique;default:gen_random_uuid();type:uuid"`

But when it would get saved, the UUID would be NULL like the following errors:

    survey-api-1  | 2023-03-02T00:25:41.978193303Z [1.070ms] [rows:3] INSERT INTO "options" ("label","value","answer_id","question_id") VALUES ('Lion','lion','','00000000-0000-0000-0000-000000000000'),('Tiger','tiger','','00000000-0000-0000-0000-000000000000'),('Cheetah','cheetah','','00000000-0000-0000-0000-000000000000') ON CONFLICT ("id") DO UPDATE SET "question_id"="excluded"."question_id" RETURNING "id"
    survey-api-1  | 2023-03-02T00:25:41.981812185Z
    
    survey-api-1  | 2023-03-02T00:25:41.981852424Z 2023/03/02 00:25:41  ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503)
    survey-api-1  | 2023-03-02T00:25:41.981858524Z [2.851ms] [rows:0] INSERT INTO "survey_questions" ("survey_identifier","question_id","question_num") VALUES ('SUR-UoFGfwjV7MRk9NKc','00000000-0000-0000-0000-000000000000',1),('SUR-UoFGfwjV7MRk9NKc','00000000-0000-0000-0000-000000000000',1),('SUR-UoFGfwjV7MRk9NKc','00000000-0000-0000-0000-000000000000',1),('SUR-UoFGfwjV7MRk9NKc','00000000-0000-0000-0000-000000000000',1) ON CONFLICT DO NOTHING
    survey-api-1  | 2023-03-02T00:25:41.981862313Z
    
    survey-api-1  | 2023-03-02T00:25:41.981864494Z 2023/03/02 00:25:41 /usr/src/service/graph/resolver/createSurvey.go:94 ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503)
    survey-api-1  | 2023-03-02T00:25:41.981867063Z [3.666ms] [rows:1] INSERT INTO "surveys" ("identifier","existing_id","prospective_id","name","description","status","created_at","updated_at") VALUES ('SUR-UoFGfwjV7MRk9NKc','','kddauth|3fb25baedf834002e8208692','On Boarding Survey','The Greatest Survey you\'ll ever take','UNSEEN','2023-03-02 00:25:41.978','2023-03-02 00:25:41.973') ON CONFLICT DO NOTHING RETURNING "id"
    survey-api-1  | 2023-03-02T00:25:41.981983297Z
    
    survey-api-1  | 2023-03-02T00:25:41.982007526Z 2023/03/02 00:25:41 /usr/src/service/graph/resolver/createSurvey.go:94 ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503); ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503)
    survey-api-1  | 2023-03-02T00:25:41.982038285Z [7.791ms] [rows:4] INSERT INTO "questions" ("text","sub_text","weight","required","created_at","updated_at","deleted_at","question_type_id","meta") VALUES ('What did you have for Breakfast this morning?','This is a question about Nutrition',1,true,'2023-03-02 00:25:41.974','2023-03-02 00:25:41.974',NULL,'c8a4921c-940b-4854-a3dd-08a3795fc44a','{"maxCharacters":251,"name":"essay","placeholder":"Type your response here..."}'),('What are the Websites of your fiercest competitors?','Please use one field for the name and the other for the website.',1,true,'2023-03-02 00:25:41.974','2023-03-02 00:25:41.974',NULL,'c8a4921c-940b-4854-a3dd-08a3795fc44a','{"keyPlaceholder":"Ex: My fiercest Competitor","moreLabel":"Add more competitor Websites","multi":true,"name":"key-value","valuePlaceholder":"Ex: https://fiercecompetitor.com"}'),('Which animal are you most afraid of being killed by?','Use Sub-Saharan animals',1,true,'2023-03-02 00:25:41.974','2023-03-02 00:25:41.974',NULL,'61957446-6b96-4b01-a8fa-a97a6f25bb7c','{"choices":[{},{},{}],"name":"multiple-choice"}'),('How much is your yearly salary?','No commas or decimals please.',1,true,'2023-03-02 00:25:41.974','2023-03-02 00:25:41.974',NULL,'01544a71-ec24-46f2-a45c-b72968c11f72','{"name":"number","placeholder":"Type your response here..."}') ON CONFLICT DO NOTHING RETURNING "id","meta"
    survey-api-1  | 2023-03-02T00:25:41.982045705Z
    
    survey-api-1  | 2023-03-02T00:25:41.982048395Z 2023/03/02 00:25:41 /usr/src/service/graph/resolver/createSurvey.go:94 ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503); ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503); ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503); ERROR: insert or update on table "survey_questions" violates foreign key constraint "fk_survey_questions_question" (SQLSTATE 23503)
    survey-api-1  | 2023-03-02T00:25:41.982051405Z [8.915ms] [rows:0] UPDATE "surveys" SET "id"='00000000-0000-0000-0000-000000000000',"existing_id"='',"prospective_id"='kddauth|3fb25baedf834002e8208692',"name"='On Boarding Survey',"description"='The Greatest Survey you\'ll ever take',"status"='UNSEEN',"created_at"='0000-00-00 00:00:00',"updated_at"='2023-03-02 00:25:41.973' WHERE "identifier" = 'SUR-UoFGfwjV7MRk9NKc

Did some research and switched to using a hook to return a UUID from a uuid.UUID package from Google, but that resulted in an error about how the UUID was too long, something about how it was 20 characters. I don't have the message anymore.

Then found (this SO post) which advises using a text-based type and generating the UUID. This is where I'm currently at in my progress.

I have checked the GORM documentation so many times my eyes are glazing over, and Googling examples is not super helpful, as they seem to favor the Query Builder, or use overly-contrived examples. I'm new to Golang but not new to development or ORMs. I think I need another set of eyes to tell me if I'm doing something egregious from a GORM (or Golang) standpoint. I'm migrating this code from NodeJS Prisma to Golang GORM, and it was working great as a many-to-many relationship using Prisma, so it feels like I'm doing something wrong in GORM.

I get the same errors trying this answer https://stackoverflow.com/a/43858941/4233452.

This answer seems close (possibly?) to what I need https://stackoverflow.com/a/57261270/4233452 but I don't know how to apply it to my situation.

Any help is greatly appreciated!

score 0 · Answer 1 · answered Mar 02 '23 at 04:32

There were a few things I had to do to get the associations to save correctly. Instead of using the one to many, I went back to many-to-many association with the join table.

base.go stayed the same.

Changed Surveys from []*SurveyQuestions to []*Survey:

// --- question.go
type Question struct {
    Base
    //... truncated for brevity
    Surveys  []*Survey `gorm:"many2many:survey_questions;"`
}

Changed QuestionId from uuid.UUID to string.

// --- surveyQuestion.go
type SurveyQuestion struct {
    SurveyIdentifier string   `gorm:"primaryKey;constraint:onDelete:CASCADE;column:survey_identifier;"`
    QuestionId       string   `gorm:"primaryKey;constraint:onDelete:RESTRICT;column:question_id;"`
    QuestionNum      int32    `gorm:"default:1;column:question_num;"`
}

Changed Questions from []*SurveyQuestions to []*Question:

// --- survey.go
type Survey struct {
    Base
    //... truncated for brevity
    Questions []*Question `gorm:"many2many:survey_questions;"`
}

Now when the implementation is called, the hook for the Base struct will be called, and the UUID will be generated. I think the length error went away because I changed it to a string instead of a proper UUID type.

Then in my implementation code I relied on Auto Create/Update for the associations. So it ended up looking like this:

var surveyModel *models.Survey
var questions []*models.Question

db.Transaction(func(tx *gorm.DB) error {
    // [truncated] ... create human-readable identity
    // [truncated] Create the survey model for the Database

    // Iterate over the payload of Questions
    for i, question := range input.Questions {
        // [truncated] ...

        // Get a question Type ID
        var questionTypeId *models.QuestionType
        err := tx.Select("id").First(&questionTypeId, "type = ?", question.Type).Error

        // [truncated] ... Handle errors related to not finding the question type
        // ... create meta data

        questionModel := &models.Question{
            Text: question.Question,
            SubText: *question.Description,
            Weight: *question.Weight,
            Required: question.Required,
            QuestionTypeId: questionTypeId.Id,
            Options: choices,
            Meta: meta,
            Surveys: []*models.Survey{
                surveyModel,
            },
        }
        
        questions = append(questions, &questionModel)
    }

    surveyModel.Questions = questions

    tx.Create(&surveyModel)

    for i, question := range questions {
        tx.Model(&models.SurveyQuestion{}).Where("survey_identifier = ?", surveyModel.Identifier).Where("question_id = ?", question.Id).Update("question_num", i + 1)
    }

    // return nil wil commit the whole transaction
    return nil
})

return surveyModel, nil;

Before when I was trying to save associations this way, I wasn't using the tx reference inside the transaction. I was using the value outside the function of db. That's what bit me! Classic mistake.

GORM UUID not being created before association insert

1 Answers1