How to change the text type from pgtype.Text to string in sqlc golang?

Question

I have the generated code for the given query

INSERT INTO "users" (
    username,
    name,
    surname,
    email,
    hashed_password,
    role
) VALUES (
    $1, $2, $3, $4, $5, $6
) RETURNING "id", "username";

which looks like this

type CreateUserParams struct {
    Username       string      `json:"username"`
    Name           pgtype.Text `json:"name"`
    Surname        pgtype.Text `json:"surname"`
    Email          string      `json:"email"`
    HashedPassword string      `json:"hashed_password"`
    Role           UserRole    `json:"role"`
}

How can I change Name pgtype.Text into golang string? currently my sqlc.yaml file looks like following

version: "2"
sql:
  - schema: "db/migrations/users"
    queries: "db/queries"
    engine: "postgresql"
    gen:
      go:
        package: "internal"
        out: "db/internal/"
        sql_package: "pgx/v5"
        output_files_suffix: "_gen"
        emit_json_tags: true
        emit_interface: true
        emit_empty_slices: true
        overrides:
          - db_type: "timestamptz"
            go_type: "time.Time"
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "text"
            go_type:
              type: "string"

The schema for me looks like following

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100),
    surname VARCHAR(100),
    email VARCHAR(150) UNIQUE NOT NULL CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$'),
    hashed_password VARCHAR(1024) NOT NULL,
    role user_role NOT NULL DEFAULT 'USER',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Here name and surname can be NULL based on schema. Now as @kozmo pointed, because these columns can be null, it is generating pgtype.Text instead of string. But I want to create string in every scenarios. How can I do that?

Daegal

Answer 1

You need to set pointer: true in your overrides configuration or set emit_pointers_for_null_types in your sqlc file. Just updated the docs on this: https://github.com/josethz00/sqlc/commit/9f15faedfaf25a8307d93e27fca4453dceea7e8c

Undercut

You missed spelled emit_pointer_for_null_types, it should be emit_pointers_for_null_types – Fuze

Answer 2

I guess, that your "users" schema file looks like

create table users
(
    id              uuid,
    username        text,
    name            text,
);

a sqlc generate

type User struct {
    ID       pgtype.UUID `json:"id"`
    Username pgtype.Text `json:"username"`
    Name     pgtype.Text `json:"name"`
}

if add constrain not null

create table users
(
    id              uuid,
    username        text,
    name            text not null --- 👈🏻
);

generated code looks like

type User struct {
    ID       pgtype.UUID `json:"id"`
    Username pgtype.Text `json:"username"`
    Name     string      `json:"name"`
}

Mitinger

Yes..for the values I am keeping NOT NULL, it is generating as strings but for those it is not mentioned, pgtype.Text is coming. I want to however make it string for everything. – Daegal

I did not find a way to generate nullable sql column to field with direct (no-poinert) type. Othe way, add pointer: true parameter to generate field with *string type. – Mitinger