Invalid UUID type is generated when db column is declared with REFERENCES

[dandee]

Version

1.21.0

What happened?

sqlc with pgx/v5 and uuid type overriden by github.com/gofrs/uuid.UUID:

Structures and functions are generated with incorrect pgtype.UUID type for uuid columns annotated with REFERENCES keyword.

I expect the type to be uuid.UUID as requested:

func (q *Queries) GetDistributor(ctx context.Context, userID uuid.UUID, iD uuid.UUID) (Distributor, error) {}

Relevant log output

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.21.0

package db

import (
	"github.com/gofrs/uuid"
	"github.com/jackc/pgx/v5/pgtype"
)

type Distributor struct {
	ID        uuid.UUID          `json:"id"`
	DeletedAt pgtype.Timestamptz `json:"deletedAt"`
	UserID    pgtype.UUID        `json:"-"`
}

type User struct {
	ID uuid.UUID `json:"-"`
}

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.21.0
// source: query.sql

package db

import (
	"context"

	"github.com/gofrs/uuid"
	"github.com/jackc/pgx/v5/pgtype"
)

const getDistributor = `-- name: GetDistributor :one
SELECT id, user_id
FROM distributors d
WHERE d.user_id = $1 AND id = $2 AND d.deleted_at IS NULL
`

func (q *Queries) GetDistributor(ctx context.Context, userID pgtype.UUID, iD uuid.UUID) (Distributor, error) {
	row := q.db.QueryRow(ctx, getDistributor, userID, iD)
	var i Distributor
	err := row.Scan(&i.ID, &i.UserID)
	return i, err
}

Database schema

CREATE TABLE users (
  id uuid PRIMARY KEY
);

CREATE TABLE distributors (
  id uuid PRIMARY KEY,
  deleted_at timestamp with time zone,
  user_id uuid REFERENCES users
);

SQL queries

-- Example queries for sqlc
CREATE TABLE users (
  id uuid PRIMARY KEY
);

CREATE TABLE distributors (
  id uuid PRIMARY KEY,
  deleted_at timestamp with time zone,
  user_id uuid REFERENCES users
);

-- name: GetDistributor :one
SELECT *
FROM distributors d
WHERE d.user_id = $1 AND id = $2 AND d.deleted_at IS NULL;

Configuration

version: "2"
sql:
- engine: "postgresql"
  schema: "/"
  queries: "/"
  gen:
    go:
      sql_package: "pgx/v5"
      package: "db"
      out: "./"
      emit_json_tags: true
      emit_pointers_for_null_types: true
      json_tags_case_style: camel
      query_parameter_limit: 5
      overrides:
      - db_type: "uuid"
        go_type: "github.com/gofrs/uuid.UUID"

Playground URL

https://play.sqlc.dev/p/9e6006530d91b4c02abb280f1683215ee1927049124a21e616399489f4353d98

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrewmbenton]

The issue here is not related to the REFERENCES constraint, but rather that sqlc treats nullable fields differently from not-nullable fields when considering overrides. You can either mark the user_id field as NOT NULL or you can add an additional override for the nullable type:

version: "2"
sql:
- engine: "postgresql"
  schema: "/"
  queries: "/"
  gen:
    go:
      sql_package: "pgx/v5"
      package: "db"
      out: "./"
      emit_json_tags: true
      emit_pointers_for_null_types: true
      json_tags_case_style: camel
      query_parameter_limit: 5
      overrides:
      - db_type: "uuid"
        go_type: "github.com/gofrs/uuid.UUID"
      - db_type: "uuid"
        go_type: "github.com/gofrs/uuid.UUID"
        nullable: true

We should document this better in https://docs.sqlc.dev/en/stable/reference/config.html#type-overriding

[dandee]

I see. So it's a different bug, since I've requested "emit_pointers_for_null_types: true" and got "pgtype.UUID" instead of "*uuid.UUID"

see: #2630

[illiafox]

The issue here is not related to the REFERENCES constraint, but rather that sqlc treats nullable fields differently from not-nullable fields when considering overrides. You can either mark the user_id field as NOT NULL or you can add an additional override for the nullable type:

I have the same issue with version v1.25.0

https://play.sqlc.dev/p/c0783e2fdd1ba1dfeca0b396bb9f0cbe3ce79a22a552aecd7565bda94ff68cec

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "repository"
        out: "."
        sql_package: "pgx/v5"
        emit_all_enum_values: true
        emit_db_tags: true
        emit_interface: true
        emit_prepared_queries: true
        emit_enum_valid_method: true
        emit_empty_slices: true
        emit_params_struct_pointers: true
        emit_result_struct_pointers: true
        emit_pointers_for_null_types: true
        query_parameter_limit: 2
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
            nullable: true

schema.sql

CREATE TABLE IF NOT EXISTS items
(
    id         UUID PRIMARY KEY           DEFAULT uuid_generate_v4(),
    parent_id  UUID REFERENCES items (id) NULL,
    name TEXT NOT NULL
);

query.sql

-- name: CreateItem :exec
INSERT INTO items (id, parent_id, name)
VALUES (@id,
        @parent_id,
        @name);

Generated models.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.25.0

package repository

import (
	"github.com/google/uuid"
)

type Item struct {
	ID       uuid.UUID `db:"id"`
	ParentID uuid.UUID `db:"parent_id"`
	Name     string    `db:"name"`
}

Generated query.sql.go

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.25.0
// source: query.sql

package repository

import (
	"context"

	"github.com/google/uuid"
)

const createItem = `-- name: CreateItem :exec
INSERT INTO items (id, parent_id, name)
VALUES ($1,
        $2,
        $3)
`

type CreateItemParams struct {
	ID       uuid.UUID `db:"id"`
	ParentID uuid.UUID `db:"parent_id"`
	Name     string    `db:"name"`
}

func (q *Queries) CreateItem(ctx context.Context, arg *CreateItemParams) error {
	_, err := q.db.Exec(ctx, createItem, arg.ID, arg.ParentID, arg.Name)
	return err
}

---

I expect sqlc to generate ParentID *uuid.UUID