Using ENUM type

[kstarzyk]

Hi,

At first thanks for such great tool! The workflow combined with both goose and sqlboiler works very well and conveys the impression of doing things in elegant manner :)

I have encountered a problem when ENUM type is used, for example:

CREATE TYPE workday AS ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
CREATE TABLE event (
  id     SERIAL,
  name   VARCHAR(255),
  day    workday NOT NULL
);

ALTER TABLE event ADD CONSTRAINT event_key PRIMARY KEY(id);

Executing sqlboiler output warning:
Warning: Incompatible data type detected: workday

Thus the models directory is created, running tests
go test models/
results in massive fail log:

--- FAIL: TestDelete (0.00s)
    --- FAIL: TestDelete/Events (0.01s)
    	event_test.go:35: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:39: models: unable to delete from event: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:44: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestQueryDeleteAll (0.00s)
    --- FAIL: TestQueryDeleteAll/Events (0.00s)
    	event_test.go:65: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:69: models: unable to delete all from event: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:74: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestSliceDeleteAll (0.00s)
    --- FAIL: TestSliceDeleteAll/Events (0.00s)
    	event_test.go:95: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:101: models: unable to delete all from event slice: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:106: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestExists (0.00s)
    --- FAIL: TestExists/Events (0.00s)
    	event_test.go:126: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:131: Unable to check if Event exists: models: unable to check if event exists: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:134: Expected EventExistsG to return true, but got false.
--- FAIL: TestFind (0.00s)
    --- FAIL: TestFind/Events (0.00s)
    	event_test.go:150: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:155: models: unable to select from event: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:159: want a record, got nil
--- FAIL: TestBind (0.00s)
    --- FAIL: TestBind/Events (0.00s)
    	event_test.go:175: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:179: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestOne (0.00s)
    --- FAIL: TestOne/Events (0.00s)
    	event_test.go:196: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:200: models: failed to execute a one query for event: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestAll (0.00s)
    --- FAIL: TestAll/Events (0.00s)
    	event_test.go:223: models: unable to insert into event: pq: invalid input value for enum workday: "a"
    	event_test.go:226: models: unable to insert into event: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:231: models: failed to assign all query results to Event slice: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:235: want 2 records, got: 0
--- FAIL: TestCount (0.00s)
    --- FAIL: TestCount/Events (0.00s)
    	event_test.go:256: models: unable to insert into event: pq: invalid input value for enum workday: "a"
    	event_test.go:259: models: unable to insert into event: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:264: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:268: want 2 records, got: 0
--- FAIL: TestInsert (0.00s)
    --- FAIL: TestInsert/Events (0.00s)
    	event_test.go:423: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:428: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:432: want one record, got: 0
    --- FAIL: TestInsert/Events#01 (0.01s)
    	event_test.go:449: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:454: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:458: want one record, got: 0
--- FAIL: TestReload (0.00s)
    --- FAIL: TestReload/Events (0.00s)
    	event_test.go:475: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:479: models: unable to select from event: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestReloadAll (0.00s)
    --- FAIL: TestReloadAll/Events (0.00s)
    	event_test.go:496: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:502: models: unable to reload all in EventSlice: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestSelect (0.00s)
    --- FAIL: TestSelect/Events (0.00s)
    	event_test.go:518: models: unable to insert into event: pq: invalid input value for enum workday: "b"
    	event_test.go:523: models: failed to assign all query results to Event slice: bind failed to execute query: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:527: want one record, got: 0
--- FAIL: TestUpdate (0.00s)
    --- FAIL: TestUpdate/Events (0.00s)
    	event_test.go:553: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:558: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:562: want one record, got: 0
    	event_test.go:570: models: unable to update event row: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestSliceUpdateAll (0.00s)
    --- FAIL: TestSliceUpdateAll/Events (0.00s)
    	event_test.go:591: models: unable to insert into event: pq: invalid input value for enum workday: ""
    	event_test.go:596: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:600: want one record, got: 0
    	event_test.go:626: models: unable to update all in event slice: pq: current transaction is aborted, commands ignored until end of transaction block
--- FAIL: TestUpsert (0.00s)
    --- FAIL: TestUpsert/Events (0.00s)
    	event_test.go:647: Unable to upsert Event: models: unable to upsert for event: pq: syntax error at or near "ON"
    	event_test.go:652: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:655: want one record, got: 0
    	event_test.go:664: Unable to upsert Event: models: unable to upsert for event: pq: syntax error at or near "ON"
    	event_test.go:669: models: failed to count event rows: pq: current transaction is aborted, commands ignored until end of transaction block
    	event_test.go:672: want one record, got: 0

models/event.go

type Event struct {
  ID   int         `boil:"id" json:"id" toml:"id" yaml:"id"`
  Name null.String `boil:"name" json:"name,omitempty" toml:"name" yaml:"name,omitempty"`
  Day  string      `boil:"day" json:"day,omitempty" toml:"day" yaml:"day,omitempty"`

  R *eventR `boil:"-" json:"-" toml:"-" yaml:"-"`
  L eventL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

If combine the warning, test output and generated code the answer is simple: enum type is not handled properly and it is treated as string type.

Are there any workarounds (other than editing generated files) for such case? (or maybe I have done something wrong?).

Thanks for your help!
Have a nice day

EDIT:
Of course the easiest workaround:

create table workday (
name VARCHAR(20) PRIMARY KEY
);

[aarondl]

Hey there,

So first off let me explain that we don't have proper enum support currently. The next thing to say is that you can still use sqlboiler and enums as strings, since this is how the driver works internally anyway, the only problem is that you can't run the compatibility tests since the randomize package doesn't understand the valid values for your enum type.

What we're going to do is the following:

• Support enum types in the bdb/drivers package by encoding enums like so: enum('val1','val2')
• Generate constants (if all enum values conform to a Go identifier-safe regex) in the model that:
  • For postgresql will look like: DatatypeValue (eg. WorkdayMonday)
  • For mysql will look like: TablenameColumnnameValue (eg. EventDayMonday)
  • Will be camelcased if and only if the value is all lowercase in the database, otherwise casing is untouched
• Support enum types in the randomize package by choosing a random value from the above encoding

If this sounds okay to you then we can start work on this.

One more thing, the TestUpsert is failing in a way I don't expect, is it possible that you can run that test in isolation along with debug mode? We may have a bug here.

go test -v -run 'TestUpsert/Events' ./models -test.debug

[aarondl]

https://github.com/vattle/sqlboiler/releases/tag/v2.1.0

[aarondl]

Check docs for more information on this new feature.