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?

Payment

you may need to handle the conversion between Go types and pgtype types manually in your code and it means you would need to convert the Go types to the corresponding pgtype types before passing them to the generated functions, and vice versa when retrieving values from the database! – Dylan

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!

Dylan

Thanks, I understand, but overriding is what I want to avoid. I have no issues using pgtypes as long I am doing it the right way. However it seems weird to me having to define numeric values the way I described before. Is that the only way? Is there a better, commonly-used way that I am missing? I am asking all these questions because I cannot find any reference online. – Payment

@Payment I updated my answer, and unfortunately, there is no commonly used alternative way to handle these pgtype types,the pgtype package provides the necessary types and methods to work with PostgreSQL-specific data types in a type-safe manner, and if you find the usage of pgtype types to be cumbersome, one possible approach is to write helper functions or methods to simplify the process of creating and working with pgtype types,for example, you could create a function that takes a string and returns a pgtype.Text type with the Valid field set to true! – Dylan

Ok, I guess that's what I'll do. Thanks. – Payment