如何在Go中解决SQL扫描错误的列问题?

huangapple go评论82阅读模式
英文:

How do I solve SQL Scan error on column in Go?

问题

使用的技术

  • Go
  • Gorm
  • PostgreSQL 14.5(在Docker容器中)
  • OpenAPI
  • oapi-codegen包v1.11.0

我正在为Shin Megami Tensei Persona系列游戏的角色建立一个CRUD操作的API。在使用上述技术时,我遇到了从数据库获取数据的问题。

错误信息
sql: 在列索引0上扫描错误,名称“arcana_id”:不支持的扫描,将driver.Value类型的字符串存储到类型*api.ArcanaID中

我认为问题在于在检索数据时,它试图将字符串存储在*api.ArcanaID中。

我该如何调整我的数据模型,以便从数据库中获取UUID?

我查看了这个问题,但它没有解决我的问题,因为它处理的是空值。

我尝试将ArcanaID的类型从字符串更改为uuid.UUID,但没有成功。仍然显示相同的错误信息。

数据模型 - openapi.yaml

components:
  schemas:
    P5Arcana:
      type: object
      required:
        - ArcanaID
      properties:
        ArcanaID:
          $ref: "#/components/schemas/ArcanaID"
    ArcanaID:
      description: 用于标识22个主要大秘卡之一的通用唯一标识符。
      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.go

package databases

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

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

接口实现 interfaceimpl.go

package 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("尝试通过不存在的ID获取Persona 5 Arcana。错误:%s", err.Error())
			return api.P5Arcana{}, fmt.Errorf("尝试通过不存在的ID获取Persona 5 Arcana。错误:%w", err)
		}
	}
	return arcana, nil
}

实现代码

// 检查ID是否存在
// 调用GetPersona5ArcanaByUUID()
// 返回结果
英文:

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

答案1

得分: 1

感谢Jamie Tanna在https://www.jvt.me/posts/2022/07/12/go-openapi-server/上的贡献。

他们的解决方案不使用github.com/google/uuid包,而是使用openapi_types.UUID类型。

这是通过以下方式定义模式实现的。

    ArcanaID:
      description: 用于标识22张主要奥秘牌之一的通用唯一标识符。
      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"

这将生成以下代码

// ArcanaID 用于标识22张主要奥秘牌之一的通用唯一标识符。
type ArcanaID = openapi_types.UUID
英文:

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

huangapple
  • 本文由 发表于 2022年9月25日 04:32:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/73840401.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定