PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Only one child record is being created when trying to create a record with one to many relationship

I am trying to build a REST API unsing golang and GORM specifically. I have four entities: Promo, which contains many PromoUnique entities, one Target entity which also contains many Category entities. Code:

type Promo struct {
    PromoID   string    `json:"promo_id" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    CompanyID string    `json:"company_id" gorm:"not null;foreignKey:ID"`
    CreatedAt time.Time `json:"-"`
    UpdatedAt time.Time `json:"-"`

    Target      Target        `json:"target" gorm:"foreignKey:TargetID;not null"`
    Active      bool          `json:"-" gorm:"default:true"`
    ActiveFrom  time.Time     `json:"active_from"`
    ActiveUntil time.Time     `json:"active_until"`
    Description string        `json:"description" gorm:"not null"`
    ImageURL    string        `json:"image_url"`
    MaxCount    int           `json:"max_count" gorm:"not null"`
    Mode        string        `json:"mode" gorm:"not null"`
    LikeCount   int           `json:"like_count" gorm:"default:0"`
    UsedCount   int           `json:"used_count" gorm:"default:0"`
    PromoCommon string        `json:"promo_common"`
    PromoUnique []PromoUnique `json:"promo_unique" gorm:"foreignKey:PromoUniqueID;"`
}

type PromoUnique struct {
    PromoUniqueID string `json:"-" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    PromoID       string `json:"-" gorm:"not null;foreignKey:PromoID"`
    Body          string `json:"-" gorm:"not null"`
    Activated     bool   `json:"-" gorm:"default:false"`
}

type Target struct {
    TargetID   string                `json:"-" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    PromoID    string                `json:"-" gorm:"not null;foreignKey:PromoID"`
    AgeFrom    int                   `json:"age_from"`
    AgeUntil   int                   `json:"age_until"`
    Country    countries.CountryCode `json:"country"`
    Categories []Category            `json:"categories" gorm:"foreignKey:CategoryID;"`
}

type Category struct {
    CategoryID string `json:"id" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    TargetID   string `json:"-" gorm:"not null"`
    Name       string `json:"name" gorm:"not null"`
}

But when I try to create a promo record in the database, it would have only one PromoUnique and Target would have only one Category, despite me creating it with multiple PromoUniques and categories. Service’s and storage’s code:

func (s *promoStorage) Create(ctx context.Context, promo entity.Promo) (*entity.Promo, error) {
    err := s.db.Model(&promo).WithContext(ctx).Create(&promo).Error

    return &promo, err
}

func (s *promoService) Create(ctx context.Context, fiberCTX fiber.Ctx, promoDTO dto.PromoCreate) (*entity.Promo, error) {

    activeFrom, timeError := time.Parse("2006-01-02", promoDTO.ActiveFrom)
    if timeError != nil {
        return nil, timeError
    }
    activeUntil, timeError := time.Parse("2006-01-02", promoDTO.ActiveUntil)
    if timeError != nil {
        return nil, timeError
    }
    var categories []entity.Category
    var promoUniques []entity.PromoUnique
    for _, category := range promoDTO.Target.Categories {
        categories = append(categories, entity.Category{
            Name: category,
        })
    }
    for _, promoUnique := range promoDTO.PromoUnique {
        promoUniques = append(promoUniques, entity.PromoUnique{
            Body: promoUnique,
        })
    }

    company := fiberCTX.Locals("business").(*entity.Business)

    promo := entity.Promo{
        Target: entity.Target{
            AgeFrom:    promoDTO.Target.AgeFrom,
            AgeUntil:   promoDTO.Target.AgeUntil,
            Country:    countries.ByName(strings.ToUpper(promoDTO.Target.Country)),
            Categories: categories,
        },
        CompanyID:   company.ID,
        Active:      true,
        ActiveFrom:  activeFrom,
        ActiveUntil: activeUntil,
        Description: promoDTO.Description,
        ImageURL:    promoDTO.ImageURL,
        MaxCount:    promoDTO.MaxCount,
        Mode:        promoDTO.Mode,
        PromoCommon: promoDTO.PromoCommon,
        PromoUnique: promoUniques,
    }

    company.Promos = append(company.Promos, promo)
    _, err := s.businessStorage.Update(ctx, company)
    if err != nil {
        return nil, err
    }

    return s.promoStorage.Create(ctx, promo)
}

My request body:

{
  "description": "Повышенный кэшбек 10% для новых клиентов бака!",
  "image_url": "https://cdn2.thecatapi.com/images/3lo.jpg",
  "target": {
    "age_from": 15,
    "age_until": 20,
    "country": "ru",
    "categories": [
      "коты",
      "футбол",
      "учитель"
    ]
  },
  "max_count": 100000000,
  "active_from": "2025-01-18",
  "active_until": "2025-01-20",
  "mode": "COMMON",
  "promo_common": "sale-10",
  "promo_unique": [
    "winter-sale-30-abc28f99qa",
    "winter-sale-30-299faab2c",
    "sale-100-winner"
  ]
}

What should I do? I did research on this topic, but I didn’t find any problems with my code. Appreciate any help!

EDIT: queries, that gorm runs on creation:

backend-1  | 2025/01/19 12:18:10 /opt/internal/adapters/database/postgres/promo.go:25
backend-1  | [0.798ms] [rows:1] INSERT INTO "categories" ("target_id","name","category_id") VALUES ('','коты','8e450f1e-ceb4-4a15-918b-63c91f8d2dbb') ON CONFLICT ("category_id") DO UPDATE SET "category_id"="excluded"."category_id" RETURNING "category_id"                                                      
backend-1  |                                                                                                                                                                                                                                                                                                        
backend-1  | 2025/01/19 12:18:10 /opt/internal/adapters/database/postgres/promo.go:25                                                                                                                                                                                                                               
backend-1  | [1.889ms] [rows:1] INSERT INTO "targets" ("promo_id","age_from","age_until","country","target_id") VALUES ('',15,20,643,'8e450f1e-ceb4-4a15-918b-63c91f8d2dbb') ON CONFLICT ("target_id") DO UPDATE SET "target_id"="excluded"."target_id" RETURNING "target_id"                                       
backend-1  |                                                                                                                                                                                                                                                                                                        
backend-1  | 2025/01/19 12:18:10 /opt/internal/adapters/database/postgres/promo.go:25                                                                                                                                                                                                                               
backend-1  | [0.871ms] [rows:1] INSERT INTO "promo_uniques" ("promo_id","body","activated","promo_unique_id") VALUES ('','winter-sale-30-abc28f99qa',false,'8e450f1e-ceb4-4a15-918b-63c91f8d2dbb') ON CONFLICT ("promo_unique_id") DO UPDATE SET "promo_unique_id"="excluded"."promo_unique_id" RETURNING "promo_unique_id"                                                                                                                                                                                                                                                                                                             
backend-1  | 
backend-1  | 2025/01/19 12:18:10 /opt/internal/adapters/database/postgres/promo.go:25                                                                                                                                                                                                                               
backend-1  | [5.933ms] [rows:1] INSERT INTO "promos" ("company_id","created_at","updated_at","active","active_from","active_until","description","image_url","max_count","mode","like_count","used_count","promo_common") VALUES ('abbe7343-c3a1-4ff2-b18b-1135632bdbf7','2025-01-19 12:18:10.395','2025-01-19 12:18:10.395',true,'2025-01-18 00:00:00','2025-01-20 00:00:00','Повышенный кэшбек 10% для новых клиентов бака!','https://cdn2.thecatapi.com/images/3lo.jpg',100000000,'COMMON',0,0,'sale-10') RETURNING "promo_id"
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solved via using raw SQL and changing my entities:

    type Promo struct {
    PromoID   string    `json:"promo_id" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    CompanyID string    `json:"company_id" gorm:"not null;foreignKey:ID"`
    CreatedAt time.Time `json:"-"`
    UpdatedAt time.Time `json:"-"`

    Active      bool          `json:"-" gorm:"default:true"`
    ActiveFrom  time.Time     `json:"active_from"`
    ActiveUntil time.Time     `json:"active_until"`
    Description string        `json:"description" gorm:"not null"`
    ImageURL    string        `json:"image_url"`
    MaxCount    int           `json:"max_count" gorm:"not null"`
    Mode        string        `json:"mode" gorm:"not null"`
    LikeCount   int           `json:"like_count" gorm:"default:0"`
    UsedCount   int           `json:"used_count" gorm:"default:0"`
    PromoCommon string        `json:"promo_common"`
    PromoUnique []PromoUnique `json:"promo_unique;" gorm:"foreignKey:PromoID"`

    AgeFrom    int                   `json:"age_from"`
    AgeUntil   int                   `json:"age_until"`
    Country    countries.CountryCode `json:"country"`
    Categories []Category            `json:"categories" gorm:"foreignKey:PromoID"`
}

type PromoUnique struct {
    PromoUniqueID string `json:"-" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    PromoID       string `json:"-" gorm:"not null;"`
    Body          string `json:"-" gorm:"not null"`
    Activated     bool   `json:"-" gorm:"default:false"`
}

type Category struct {
    CategoryID string `json:"id" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    PromoID    string `json:"-" gorm:"not null;"`
    Name       string `json:"name" gorm:"not null"`
}

    func (s *promoStorage) Create(ctx context.Context, promo entity.Promo) (*entity.Promo, error) {
    // Insert a promo (parent)'s entity
    insertPromoQuery := s.db.WithContext(ctx).Raw(
        "INSERT INTO promos (company_id, created_at, updated_at, active_from, active_until, description, image_url, max_count, mode, promo_common, age_from, age_unti, country) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING promo_id;",
        promo.CompanyID, promo.CreatedAt, promo.UpdatedAt, promo.ActiveFrom, promo.ActiveUntil, promo.Description, promo.ImageURL, promo.MaxCount, promo.Mode, promo.PromoCommon, promo.AgeFrom, promo.AgeUntil, promo.Country).Scan(&promo.PromoID)
    if err := insertPromoQuery.Error; err != nil {
        return nil, err
    }

    // Insert categories
    for _, category := range promo.Categories {
        insertCategoryQuery := s.db.WithContext(ctx).Exec("INSERT INTO categories (promo_id, name) VALUES (?, ?);", promo.PromoID, category.Name)
        if err := insertCategoryQuery.Error; err != nil {
            return nil, err
        }
    }

    // Insert promo_uniques
    for _, promoUnique := range promo.PromoUnique {
        insertPromoUniqueQuery := s.db.WithContext(ctx).Exec("INSERT INTO promo_uniques (promo_id, body, activated) VALUES (?, ?, ?);", promo.PromoID, promoUnique.Body, promoUnique.Activated)
        if err := insertPromoUniqueQuery.Error; err != nil {
            return nil, err
        }
    }

    return &promo, nil
}

  2. 0

    Shouldn’t your foreignKey be "PromoID" as well?

    type Promo struct {
    PromoID   string    `json:"promo_id" gorm:"primaryKey;not null;type:uuid;default:gen_random_uuid()"`
    CompanyID string    `json:"company_id" gorm:"not null;foreignKey:ID"`
    CreatedAt time.Time `json:"-"`
    UpdatedAt time.Time `json:"-"`

    Target      Target        `json:"target" gorm:"foreignKey:TargetID;not null"`
    Active      bool          `json:"-" gorm:"default:true"`
    ActiveFrom  time.Time     `json:"active_from"`
    ActiveUntil time.Time     `json:"active_until"`
    Description string        `json:"description" gorm:"not null"`
    ImageURL    string        `json:"image_url"`
    MaxCount    int           `json:"max_count" gorm:"not null"`
    Mode        string        `json:"mode" gorm:"not null"`
    LikeCount   int           `json:"like_count" gorm:"default:0"`
    UsedCount   int           `json:"used_count" gorm:"default:0"`
    PromoCommon string        `json:"promo_common"`
    PromoUnique []PromoUnique `json:"promo_unique" gorm:"foreignKey:PromoID;"`
}

    When inserting to PromoUnique, promo_id is empty and only promoUniqueId is being filled up. I’m assuming that the value being set on promoUniqueId is Promo.PromoId. And since promoUniqueId is the primary key, only one would be saved.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search