Using `.field(.custom(SQLExpression))` to fetch data works fine, but crashes when trying to unwrap the value of the column.

[janigro]

Describe the bug

Using .field(.custom(SQLExpression)) to fetch data works fine, but crashes when trying to unwrap the value of the column.

To Reproduce

The following code has been reduced to its minimal expression. My actual SQLExpression is much more complex than the one below. However, to demonstrate the problem, I chose the simplest expression possible:

The example uses a table roles, with a column name:

let row = try await Role.query(on: database)
       .field(.custom(SQLRaw("name")))
       .first()

This fetches data correctly, as printing the row shows:

print("\(row)") // Role(output: ["name": "Admin"])

However, when trying to access the name column, the unwrap crashes the app:

print("role name = \(row.name)") // FluentKit/Field.swift:23: Fatal error: Cannot access field before it is initialized or fetched: name

Workaround

Alternatively, I use vapor/sql-kit, which works just fine using the same SQLExpression:

let row = try await sqlDB.select()
       .column(SQLRaw("name"))
       .from("roles")
       .first(decoding: Role.self)

print("\(row)") // Role(output: _MySQLSQLRow(row: ["name": "Admin"], decoder: MySQLKit.MySQLDataDecoder(json: Foundation.JSONDecoder)))
print("role name = \(row.name)") // role name = Admin

Environment

framework: 4.77.0
toolbox: 18.7.4
fluent: 4.8.0
fluent-kit: 1.42.2
fluent-mysql-driver: 4.3.0
mysql-kit: 4.7.0
mysql-nio: 1.7.0
sql-kit: 3.27.0
OS version: macOS 14.1.1 (23B81)

[gwynne]

This is not a bug; Fluent model loading assumes a specific naming pattern of column aliases which your custom expression fails to follow. In your example, .field(.custom(SQLAlias(/* your actual SQLExpression here */, as: SQLIdentifier("\(FieldKey.prefix(.string(Role.schema), Role.path(for: \.$name)[0]))"))) would most closely approximate what Fluent actually does to generate column names.

That being said, this is not a supported way to use Fluent in the first place; dropping to SQLKit is the correct approach for this kind of need. (And even then, it's very strongly advised to avoid SQLRaw as much as possible; it's generally only needed when expressing keywords for which no expression already exists, nearly everything else can be expressed by various combinations of SQLIdentifier, SQLFunction, SQLColumn, SQLBind, SQLLiteral, SQLBinaryExpression, SQLGroupExpression, SQLList, and so forth - I admit this tends to be exceptionally verbose, to be sure 😅).

[janigro]

Thanks for the superfast response! I'll keep using SQLKit then!

Btw, I'm not really using SQLRaw as my SQLExpression, I just use it for the simplicity in the example. ;-)