Go - Correct way of using pgtypes

Question

I am developing a simple Go service to connect to a database for basic querying. I am using sqlc to generate the Go functions to interact with the DB. when switching the driver from lib/pq to pgx/v5 now the types for the DB fields are pgtypes instead of Go types. Here's an example:

Instead of this:

    type ListAccountsParams struct {
        Owner  string `json:"owner"`
        Limit  int32  `json:"limit"`
        Offset int32  `json:"offset"`
    }

I now get this:

    type ListAccountsParams struct {
        Owner  pgtype.Text `json:"owner"`
        Limit  pgtype.Int4 `json:"limit"`
        Offset pgtype.Int4 `json:"offset"`
    }

However the only way I find of using pgtypes is this one:

    owner := pgtype.Text{
        String: "Craigs List",
        Valid: true,
    }

Instead of just doing owner := "Craigs List". For numeric types is even more overkill, all implementations I find are like this:

    pgtype.Numeric{
    Int: big.NewInt(-543), 
    Exp: 3, 
    Status: pgtype.Present
    }

Using sqlc config file I can just override these types in favor of Go standard types, but it makes no sense to me having to override a postgres text type to string and so on...

It seems to me that this is not the best way of using these types, it is counter intuitive for me. So my question is, am I doing it right? Is there a different way? Ultimately, is there a way to configure sqlc to use Go types instead of pgtypes while still using the pgx/v5 driver?

Answer 1

you can edit sqlc configuration file to specify custom types for the fields you want to use Go types instead of pgtype types and override the types by specifying the desired Go type in the sqlc.yaml file

types:
  - name: ListAccountsParams
    fields:
      - name: Owner
        goType: string
      - name: Limit
        goType: int32
      - name: Offset
        goType: int32

and please note that using Go types directly may result in potential type mismatches or other issues when interacting with the database,and pgtype types are specifically designed to handle PostgreSQL-specific data types and provide a level of type safety and compatibility!

UPDATE

according to your comment,when using pgx/v5 driver with sqlc, the generated code uses pgtype types to handle PostgreSQL-specific data type and it is the default behavior of sqlc when working with the pgx/v5 driver, and the way you described using pgtype types, such as pgtype.Text and pgtype.Int4, is the correct way of working with those types, for example, when dealing with the pgtype.Text type, you need to set the String field to the desired value and set the Valid field to true.

and regarding numeric types, the pgtype.Numeric type requires the Int, Exp, and Status fields to be set,it is because the pgtype.Numeric type represents a numeric value as an arbitrary-precision integer (Int), an exponent (Exp), and a status (Status) indicating if the value is present or null!