Search code examples
postgresqlgogo-gorm

Incorrect data types for Postgres being passed in to query from Gorm

I am trying to create an endpoint within my API for creating companies. Within the Company model I have a []string for storing allow listed domains related to the emails users are allowed to use to sign up with.

The []string is initially mapped from a JSON POST request from an array and assigned the text[] type within Postgres.

AllowedDomains        []string `gorm:"type:text[];default:NULL" json:"allowedDomains" binding:"required"`

Full model with Create()

// Company is the primary struct type for companies
type Company struct {
    common.Base
    Name                  string   `gorm:"unique;default:not null" json:"name" binding:"required"`
    PrimaryContactName    string   `gorm:"unique;default:not null" json:"primaryContactName" binding:"required"`
    PrimaryContactEmail   string   `gorm:"unique;default:not null" json:"primaryContactEmail" binding:"required"`
    PrimaryContactPhone   string   `gorm:"unique;default:not null" json:"primaryContactPhone" binding:"required"`
    SecondaryContactName  string   `gorm:"unique;default:NULL" json:"secondaryContactName"`
    SecondaryContactEmail string   `gorm:"unique;default:NULL" json:"secondaryContactEmail"`
    SecondaryContactPhone string   `gorm:"unique;default:NULL" json:"secondaryContactPhone"`
    PrimaryDomain         string   `gorm:"unique;default:not null" json:"primaryDomain" binding:"required"`
    AllowedDomains        []string `gorm:"type:text[];default:NULL" json:"allowedDomains" binding:"required"`
    MFAEnabled            bool     `gorm:"not null" json:"mfaEnabled" binding:"required"`
    IsValidated           bool     `gorm:"not null"`
}

func (c *Company) Create() error {
    if result := common.Db.Create(c); result.Error != nil {
        log.Printf("Error creating company: %s", c.Name)
        return result.Error
    } else {
        log.Printf("Successfully created company: %s", c.Name)
        return nil
    }
}

In implementing this I am encountering two problems however.

First when AllowedDomains contains a single string the value is not being written to Postgres as an array, but rather as a single string.

api               | 2023/04/10 19:05:50 /go/src/api/company/model.go:25 ERROR: malformed array literal: "website.co.uk" (SQLSTATE 22P02)
api               | [2.006ms] [rows:0] INSERT INTO "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") VALUES ('2023-04-10 19:05:50.551','2023-04-10 19:05:50.551',NULL,'Foo Company Ltd.','Foo','Bar','00000000000','website.com',true,false,'Foo2','Bar2','11111111111',('website.co.uk')) RETURNING "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"
api               | [GIN] 2023/04/10 - 19:05:50 | 500 |    3.043083ms |      172.21.0.1 | POST     "/api/company/register"
api               | 2023/04/10 19:05:50 Error creating company: Foo Company Ltd.
api               | 2023/04/10 19:05:50 ERROR: malformed array literal: "website.co.uk" (SQLSTATE 22P02)
postgres          | 2023-04-10 19:06:35.523 UTC [19] ERROR:  column "allowed_domains" is of type text[] but expression is of type record at character 336
postgres          | 2023-04-10 19:06:35.523 UTC [19] HINT:  You will need to rewrite or cast the expression.
postgres          | 2023-04-10 19:06:35.523 UTC [19] STATEMENT:  INSERT INTO "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,($14,$15)) RETURNING "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"

Secondly, when the JSOn array contains > 1 values, the type being written to the database is of type record and not text[]

api               | 2023/04/10 19:06:35 /go/src/api/company/model.go:25 ERROR: column "allowed_domains" is of type text[] but expression is of type record (SQLSTATE 42804)
api               | [2.502ms] [rows:0] INSERT INTO "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") VALUES ('2023-04-10 19:06:35.522','2023-04-10 19:06:35.522',NULL,'Foo Company Ltd.','Foo','Bar','00000000000','website.com',true,false,'Foo2','Bar2','11111111111',('website.co.uk','website.net')) RETURNING "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"
api               | [GIN] 2023/04/10 - 19:06:35 | 500 |    3.256334ms |      172.21.0.1 | POST     "/api/company/register"
api               | 2023/04/10 19:06:35 Error creating company: Foo Company Ltd.
api               | 2023/04/10 19:06:35 ERROR: column "allowed_domains" is of type text[] but expression is of type record (SQLSTATE 42804)

When I set a breakpoint and analyse the company type after serialization however, it's clear that AllowedDomains is of the correct type.

enter image description here

Any ideas what I'm missing here or the best way to resolve this?

Solution

  • As per the comment by mkopriva, the solution was to use the pq package as below.

    package company

import (
    "github.com/lib/pq"
    "log"
)

// Company is the primary struct type for companies
type Company struct {
    ...
AllowedDomains        pq.StringArray `gorm:"type:text[];default:NULL" json:"allowedDomains" binding:"required"`
}

func (c *Company) Create() error {
    a := pq.StringArray{}
    if c.AllowedDomains != nil && len(c.AllowedDomains) > 0 {
        for _, v := range c.AllowedDomains {
            a = append(a, v)
        }
        c.AllowedDomains = a
    }
    if result := common.Db.Create(c); result.Error != nil {
        log.Printf("Error creating company: %s", c.Name)
        return result.Error
    } else {
        log.Printf("Successfully created company: %s", c.Name)
        return nil
    }
}