Consider generating enums for check constraints

[mfridman]

What do you want to change?

Consider generating enums for check constraints (in a similar way as if postgres enums were defined). For example, given this table:

CREATE TABLE books (
    name text NOT NULL,
    status text NOT NULL CHECK (status in ('available', 'unavailable', 'lost'))
);

The generated code would be:

type Book struct {
	Name   string
	Status string
}

But if it were

CREATE TYPE book_status AS ENUM (
  'available',
  'unavailable',
  'lost'
);

CREATE TABLE books (
    name text NOT NULL,
    status book_status
);

the generated code now contains a Go constant type

type BookStatus string

const (
	BookStatusAvailable   BookStatus = "available"
	BookStatusUnavailable BookStatus = "unavailable"
	BookStatusLost        BookStatus = "lost"
)

[...]

Could sqlc also generate these enums based on check constraints?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[baabcantcode]

not sure if i should piggy back here, but using the same check constraint in sqlite is generating an interface{} type.

[baabcantcode]

changing the data type from char(1) to text helped get rid of the interface{} at least.

ex:

create table if not exists x (
    id integer primary key,
    constrains text not null,
    applies_to_user char(1) not null check(applies_to_user in ('y', 'n')) default 'y',
    default_weight integer not null default 1
);

[baabcantcode]

it also doesnt appear to be parsing the affinities into the model correctly, for e.g. boolean.
https://www.sqlite.org/datatype3.html - specifically 3.1 & 3.1.1

[krhubert]

Personally, I don't mind if enum setup is very explicit, as long as there's a way to keep the enum definition in the same pkg as the rest of the generated code, which is great value added.

Config example:

version: "2"
sql:
  - engine: "postgresql"
    queries: "queries"
    schema: "schema"
    gen:
      go:
        sql_package: "pgx/v5"
        ....

        # list of enums to generate based on the column and constraint name 
        enums:
            - column: "books.status"
              constraint: "books_status_check"
              

[krhubert]

Anyone who's looking for a workaround here's my approach. This keeps the enum generated without using them as a column type. The downside is double enum definition, but without using an enum directly as column name it is easy to both add and delete values.

-- this enum is defined here so sqlc can generate the code for it.
-- do not use this enum directly for book table, use a check constraint instead
CREATE TYPE book_status AS ENUM (
  'available',
  'unavailable',
  'lost'
);

CREATE TABLE book (
    name text NOT NULL,
    status text NOT NULL CHECK (status in ('available', 'unavailable', 'lost'))
);

version: "2"
sql:
  - engine: "postgresql"
    queries: "queries"
    schema: "schema"
    gen:
      go:
        sql_package: "pgx/v5"
        ....
              
        overrides:
          - column: "book.status"
            go_type:
              type: "BookStatusType"