@Enum array

[gperdomor]

I have this enum

enum Permission: String, PostgreSQLEnum, PostgreSQLMigration {
    ...
    case asset_create = "asset:create"
    case asset_delete = "asset:delete"
    case asset_read = "asset:read"
    case asset_update = "asset:update"
    ...
}

this Entity:

final class Role: PostgreSQLUUIDModel, Migration, Content {
    static var entity = "roles"
    
    var id: UUID?
    var name: String
    var permissions: [Permission]

    init(name: String, permissions: [Permission]) {
        self.name = name
        self.permissions = permissions
    }
}

this migration:

struct RoleDemo: Migration {
    typealias Database = PostgreSQLDatabase
    
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        let role = Role(
            name: "Demo",
            permissions: [.asset_create, .item_create]
        )
        
        return role.save(on: connection).transform(to: ())
    }
    
    static func revert(on connection: PostgreSQLConnection) -> Future<Void> {
        return .done(on: connection)
    }
}

And finally... This error:

Thread 1: Fatal error: Error raised at top level: ⚠️ PostgreSQL Error: column "permissions" is of type permission[] but expression is of type text[]
- id: PostgreSQLError.server.error.transformAssignedExpr

These suggestions could address the issue: 
- You will need to rewrite or cast the expression.

[grundoon]

Have you added Permission as a migration in your configure.swift, prior to its use within the Role migration?

var migrations = MigrationConfig()
migrations.add(migration: Permission.self, database: .psql)
migrations.add(model: Role.self, database: .psql)
// ...
services.register(migrations)

[gperdomor]

@grundoon yes... The enum was added to the migrations... Seems to be a serialization problem... https://stackoverflow.com/questions/18234946/postgresql-insert-into-an-array-of-enums

[obrhoff]

@gperdomor Did you find a workaround for this issue? In general I wonder why it defaults to text[] and not to PostgreSQLEnum.postgreSQLDataType + "[]"

[gperdomor]

@docterd no :(

[MihaelIsaev]

Same here

enum UserRole: String, PostgreSQLEnum, PostgreSQLMigration {
    case admin, manager, promoter, hipster
}

trying to create a User

final class User: Content {
    var id: UUID?
    var phone: String
    var roles: [UserRole]
    var createdAt, updatedAt, deletedAt: Date?
}

but getting error

⚠️ PostgreSQL Error: column "roles" is of type userrole[] but expression is of type text[]

here what I see in logs

[psql] [2019-01-19 20:56:45 +0000] INSERT INTO "User" ("updatedAt", "roles", "phone", "id", "createdAt") VALUES ($1, $2, $3, $4, $5) RETURNING * [0x000222d4a2561e46 (TIMESTAMP), ["admin"] (TEXT[]), "‭+15555235555" (TEXT), A5AFFA4E-019C-45AC-AEB2-9E13239E847A (UUID), 0x000222d4a2561e46 (TIMESTAMP)]

[tanner0101]

Hmm I'll have to look into this. Enums were never tested inside of arrays, so not a huge surprise it doesn't work.

As a workaround, you should be able to use just enum UserRole: String { ... } and do the enum validation in Swift instead of the DB.

[Janoz94]

Any workaround or maybe is there already solution for it?

[tanner0101]

@Janoz94 to clarify my idea for a workaround, you could store the enum values as [String], and then have a mutable computed property on the model that converts to / from [MyEnum]. No solution has been found yet, but I'll make sure it gets fixed by the next major release at the latest.

[mcritz]

I have this exact same issue. Happy to see it’s got a healthy discussion. I’d love to either see this feature work as intended or have a best practices documentation so other devs don’t step on the same landmine.

[tanner0101]

This is fixed in Fluent 4. The workarounds mentioned here are the best option for Fluent 3.

[bdrelling]

@tanner0101 I'm not sure if this is related but thought I'd add some context in case it is helpful for testing as I saw you reopened the issue and I'm running into a similar problem right now on Vapor 4.

Below is the transcript of a post I made in Discord, with some additional context at the bottom.

Let me know if this is off-topic for this issue and I re-open in another issue.

---

Another Fluent question: What's the right way to put an array of enums in a schema and in the model? I'm doing this right now, my enum is just called Color as an example:

public enum Color: String, Codable {
    case red
    case blue
    case green
}

database.enum("color").read().flatMap { colors
    ...
    .field("colors", .array(of: colors), .required)
    ...
}

@Field(key: "colors")
public var colors: [Color]

The database (Postgres) looks fine. It blocks me from storing invalid values, it requires the default value of {} instead of NULL, and I can store my array as {red,blue,green} -- all good. But when I try to access it from the model, it throws an error at me about an invalid unexpected data type.

I'm not sure if I should be using another property wrapper or what. Trying to go by the guides.

The exact error is this:

invalid field: colors type: Array<Color> error: Unexpected data type: UNKNOWN 4106478. Expected array."

I have tried @Enum (doesn't work of course), dropping .required to test alternate values and using OptionalField just to test.

---

For additional context, when I try to create a new type and I set colors to [], I get the following error:

[ ERROR ] column "colors" is of type colorful_color[] but expression is of type jsonb[] (transformAssignedExpr)
[ ERROR ] server: column "colors" is of type colorful_color[] but expression is of type jsonb[] (transformAssignedExpr) [request-id: 507FD685-BC28-498E-9456-17DA1C67ABB7]

but if I set colors to [.blue], for example, I get a slightly different error:

[ ERROR ] column "colors" is of type colorful_color[] but expression is of type text[] (transformAssignedExpr)
[ ERROR ] server: column "colors" is of type colorful_color[] but expression is of type text[] (transformAssignedExpr) [request-id: 278B95BA-228F-4BE5-AF43-D1EE100F9D33]

[tonyarnold]

@tanner0101 I'm still seeing quite a similar issue to this in the latest release of FluentKit:

enum Role: Int16, Content, CaseIterable {
  case one = 1
  case two = 2
  case three = 3
}

The field on the model:

@Field(key: "roles")
var roles: [Role]

My migration for this field:

.field("roles", .array(of: .int16))

When I try to save an instance of the model, I get this error:

failed: caught error: "invalid field: roles type: Array<Role> error: Unexpected data type: SMALLINT[]. Expected array."

[43n79w]

@tanner0101 With postgresql 12 I'm seeing the same behaviour @bdrelling is seeing. I've found a workaround that's not ideal but could be acceptable to some people.

Instead of doing something like this...

database.enum("color").read().flatMap { colors
    ...
    .field("colors", .array(of: colors), .required)
    ...
}

You can do...

database.enum("color").read().flatMap { colors
    ...
    .field("colors", .sql(raw: 'text[]'), .required)
    ...
}

You won't get the benefit of the column type in postgres being an array of enums. But it does allow everything to work as one would expect otherwise. For people who might be starting from scratch, this would give them a relatively straightforward way to migrate to a proper solution once available.

The underlying issue may be that with postgres, certain array syntax require a cast to the enum array type, otherwise postgres will treat the value like a text array.

For example, this won't work given @bdrelling's example.

INSERT INTO things_with_colors ('colors') VALUES (ARRAY['red', 'blue', 'green'])

If the column type is set correctly, the above will cause the error message [ ERROR ] column "colors" is of type color[] but expression is of type text[]

However, this should work

INSERT INTO things_with_colors ('colors') VALUES (ARRAY['red', 'blue', 'green']::color[])

[bdrelling]

@tanner0101 curious to get your thoughts on this issue -- I had the embarrassing moment of repeating history with this issue, straight down to stumbling across the same Discord posts, GitHub issues, and so on.

It seems like it should be such a simple issue, which of course means there's more this than meets the eye, but I don't have a solid understanding of what's preventing this from moving forward, or even any recommendations on how to work around this or any mentions in the documentation about this -- if they are there, then I missed them after pouring over documentation repeatedly for the past day or so...

Thanks in advance!

[0xTim]

@gwynne so should this be supported or is this something that would need to be planned for Fluent 5?

[gwynne]

The underlying problem is the same one that makes handling enums so awkward in Fluent to begin with - Fluent doesn't really have any facility for correctly dealing with database-typed enums like PostgreSQL's. Even with scalar @Enum properties, Fluent is cheating - it's not encoding such properties as bound parameters at all; it's just embedding the raw enum case as a string directly in the query (in other words, it's doing INSERT INTO "foo" VALUES ('enumCase'), which works because PostgreSQL defaults to allowing automatic conversion between simple strings and matching enum cases). Since there's no @EnumArray property type and no .enumCaseArray() case on DatabaseQuery.Value, Fluent goes back to using bound parameters and binds a value like ['red', 'green'] - and since in PostgreSQL's wire protocol, bound parameters are always explicitly typed, the value is effectively ['red'::text, 'green'::text]::text[], for which there is, of course, no conversion.

The truly correct solution here would be for Fluent to look up the Postgres OID for custom enum types and use bound parameters for enum types which specify the correct OID, but I've tried to add this support more than once in Fluent 4 and failed every time; there are design limitations which make it effectively impossible without severely breaking the existing API.

It's possible to force Fluent to use the correct encoding for singular enum case values by manually overriding the PostgresDataConvertible conformance of the Swift type and performing lookup queries at startup, but even then it won't do the right thing for arrays - to_regtype('colors') and to_regtype('colors[]') don't return the same OID, and it's not possible to override the OID used for array encoding because of missing logic in PostgresDataType's arrayType accessor. And TBH, I'm not inclined to add the missing logic because using it would be extremely painful. For example, here's what I had to do just to make single enum cases "work":

// Todo.swift
enum Color: String, Codable, PostgresDataConvertible {
    case red, blue, green
    
    public static var postgresDataType: PostgresDataType = .text
    
    public var postgresData: PostgresData? {
        var data = PostgresData(string: self.rawValue)
        data.type = Self.postgresDataType
        return data
    }
    
    public init?(postgresData: PostgresData) {
        guard let stringValue = postgresData.string else { return nil }
        guard let realValue = Self.init(rawValue: stringValue) else { return nil }
        self = realValue
    }
}

// configure.swift
public func configure(_ app: Application) throws {
    // the usual configuration stuff
    
    try app.autoMigrate().wait()
    
    struct TypeOIDResult: Codable { let oid: Int32 }
    guard let colorsOid = try (app.db as! SQLDatabase).raw("SELECT to_regtype('colors')::integer AS oid").first(decoding: TypeOIDResult.self).wait() else {
        throw Abort(.internalServerError, reason: "can't lookup DB enum type")
    }
    Color.postgresDataType = .init(UInt32(colorsOid.oid))
}

This would have to be repeated for every single enum type to be used in the database, plus even if arrays could be made to work, there'd have to be even more logic to look up the corresponding array type for each enum type as well.

In short, Fluent 4 just can't architecturally handle what's needed, unfortunately - and it annoys me enough that this is the case that every time the question comes up, I go through all of the above again myself just to make extra double sure there's no way to even just tack on support with duct tape and chewing gum (like I did for the @CompositeID feature, for example).

Rest assured that Fluent 5 will handle all of this correctly and cleanly 🤦‍♀️.

(P.S.: Meanwhile, MySQL doesn't have to deal with any of this 'cause enums in MySQL are just strings with the allowed cases listed in the column's type definition - I reserve judgement on whether this is "better" (*cough*Itisn't*cough*) than Postgres's way.)

[bdrelling]

@gwynne thank you so much for the detailed write-up, and so sorry to make you repeat history with this again as well!

Would it make sense to add this into the documentation? I don't know our current guidelines around pointing out all these caveats and stuff, but I think including (a) information about Enum arrays and (b) information about Postgres' handling (where applicable) would be super helpful!

I'd be happy to propose some documentation updates myself but honestly not confident I would know what to write -- but if it saves time, I could prep a PR and ping for copy tweaks or whatever.

[0xTim]

@bdrelling that would be great!