Home > Software design >  How do I solve SQL Scan error on column in Go?
How do I solve SQL Scan error on column in Go?

Time:09-29

Technologies used

  • Go
  • Gorm
  • PostgreSQL 14.5 (In Docker container)
  • OpenAPI
  • oapi-codegen package v1.11.0

I am building an API for CRUD operations on Personas from the Shin Megami Tensei Persona spin-off series of games. I have an issue when trying to fetch data from my database using the technologies above.

Error message

sql: Scan error on column index 0, name "arcana_id": unsupported Scan, storing driver.Value type string into type *api.ArcanaID

I think the issue is that when retrieving the data it is trying to store a string inside of a *api.ArcanaID.

How can I adjust my data model so that I can pull a UUID from my DB?

I have looked at this question and it did not solve my issue because it is dealing with nil values.

I have tried changing the type of the ArcanaID from string to uuid.UUID with no success. Same error message.

Data Model - openapi.yaml

components:
  schemas:
    P5Arcana:
      type: object
      required:
        - ArcanaID
      properties:
        ArcanaID:
          $ref: "#/components/schemas/ArcanaID"
    ArcanaID:
      description: A universally unique identifier for identifying one of the 22 Major Arcana.
      type: string
      x-go-type: uuid.UUID
      x-go-type-import:
        path:  github.com/google/uuid
      x-oapi-codegen-extra-tags:
        gorm: "primaryKey;unique;type:uuid;default:uuid_generate_v4()"

interface interface.go

packages databases

import (
    "context"
    "github.com/bradleyGamiMarques/PersonaGrimoire/api
  )

type PersonaGrimoire interface {
    GetPersona5ArcanaByUUID(ctx context.Context, arcanaUUID api.ArcanaID) (arcana api.P5Arcana, err error)
}

interfaceimpl interfaceimpl.go

packages databases
import (
    "context"
    "errors"
    "fmt"

    "github.com/bradleyGamiMarques/PersonaGrimoire/api"
    "github.com/sirupsen/logrus"
    "gorm.io/gorm"
)

type PersonaGrimoireImpl struct {
    Gorm   *gorm.DB
    Logger *logrus.Logger
}

func (p *PersonaGrimoireImpl) GetPersona5ArcanaByUUID(ctx context.Context, arcanaUUID api.ArcanaID) (arcana api.P5Arcana, err error) {
    err = p.Gorm.WithContext(ctx).Model(&api.P5Arcana{ArcanaID: arcana.ArcanaID}).Where(&api.P5Arcana{ArcanaID: arcanaUUID}).First(&arcana).Error
    if err != nil {
        if errors.Is(err, gorm.ErrRecordNotFound) {
            p.Logger.Warnf("Attempted to get Persona 5 Arcana by ID that does not exist. Error: %s", err.Error())
            return api.P5Arcana{}, fmt.Errorf("attempted to get Persona 5 Arcana by ID that does not exist Error: %w", err)
        }
    }
    return arcana, nil
}

Implementation code

// Check if ID exists
// Calls GetPersona5ArcanaByUUID()
// Return result

CodePudding user response:

Thank you to Jamie Tanna at https://www.jvt.me/posts/2022/07/12/go-openapi-server/.

Their solution involved not using the github.com/google/uuid package and instead used the openapi_types.UUID type.

This was done by defining the schema as such.

    ArcanaID:
      description: A universally unique identifier for identifying one of the 22 Major Arcana.
      type: string
      format: uuid
      pattern: "[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-4[a-fA-F0-9]{3}-[89abAB][a-fA-F0-9]{3}-[a-fA-F0-9]{12}"
      x-oapi-codegen-extra-tags:
        gorm: "type:uuid;primaryKey"

This results in generated code that looks like

// ArcanaID A universally unique identifier for identifying one of the 22 Major Arcana.
type ArcanaID = openapi_types.UUID
  • Related