An `id` of `null` throws violates not-null constraint.

Given the following User table schema:

postgres=> CREATE TABLE "User"(id SERIAL PRIMARY KEY, email Text NOT NULL, hash TEXT NOT NULL, role role NOT NULL);
CREATE TABLE

I get the following output when I create the table in terminal:

postgres=> \d "User";
                             Table "public.User"
 Column |  Type   | Collation | Nullable |              Default               
--------+---------+-----------+----------+------------------------------------
 id     | integer |           | not null | nextval('"User_id_seq"'::regclass)
 email  | text    |           | not null | 
 hash   | text    |           | not null | 
 role   | role    |           | not null | 
Indexes:
    "User_pkey" PRIMARY KEY, btree (id)

Running the following command in psql gives me the same error:

postgres=> INSERT INTO "User" (id, email, hash, role)
postgres-> VALUES (null, 'xxxx@xxxxxxx.com', 'xxxxxxxx', 'admin');
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, xxxx@xxxxxxx.com, xxxxxxx, admin).

However running the same command with id removed gives me a successful input:

postgres=> INSERT INTO "User" (email, hash, role)
postgres-> VALUES (xxxx@xxxxxxx.com', 'xxxxxxxx', 'admin');
INSERT 0 1 // Success!

Now with the following PostgreSQLModel:

import Vapor
import FluentPostgreSQL

struct User: PostgreSQLModel {

    // MARK: - Definitions

    typealias ID = Int

    // MARK: - Attributes

    var id: ID?
    let email: String
    let hash: String
    let role: UserRole

    // MARK: - Init

    public init(email: String,
                hash: String,
                role: UserRole) {
        self.email = email
        self.hash = hash
        self.role = role
    }

}

// MARK: - Equatable / Hashable

extension User: Hashable {

    public static func ==(lhs: User,
                          rhs: User) -> Bool {
        if lhs.id != rhs.id {
            return false 
        }
        if lhs.email != rhs.email {
            return false
        }
        if lhs.hash != rhs.hash {
            return false
        }
        if lhs.role != rhs.role {
            return false
        }
        return true
    }

    public func hash(into hasher: inout Hasher) {
        hasher.combine(self.id)
        hasher.combine(self.email)
        hasher.combine(self.hash)
        hasher.combine(self.role)
    }

}

// MARK: - Codable

extension User: Codable {

    private enum RootKeys: String, CodingKey {
        case id
        case email
        case hash
        case role
    }

    init(from decoder: Decoder) throws {
        let container = try decoder.container(keyedBy: RootKeys.self)
        if let id = try? container.decode(ID.self, forKey: .id) {
            self.id = id
        } else {
            self.id = nil
        }
        self.email = try container.decode(String.self, forKey: .email)
        self.hash = try container.decode(String.self, forKey: .hash)
        self.role = try container.decode(UserRole.self, forKey: .role)
    }

    func encode(to encoder: Encoder) throws {
        var container = encoder.container(keyedBy: RootKeys.self)
        try container.encode(self.id, forKey: .id)
        try container.encode(self.email, forKey: .email)
        try container.encode(self.hash, forKey: .hash)
        try container.encode(self.role, forKey: .role)
    }

}

// MARK: - PostgreSQL

/// Allows `User` to be used as a dynamic migration
extension User: Migration {

    static func prepare(on connection: PostgreSQLConnection) -> EventLoopFuture<Void> {
        return Database.create(User.self, on: connection) { builder in
            /**
                v1:
                    + id
                    + email
                    + hash
                    + role
             */
            builder.field(for: \.id, isIdentifier: true)
            builder.field(for: \.email)
            builder.field(for: \.hash)
            builder.field(for: \.role)
        }
    }

}

/// Allows `User` to be encoded to and decoded from HTTP messages.
extension User: Content {}

/// Allows `User` to be used as a dynamic parameter in route definitions.
extension User: Parameter {}

When I run the following:

let user = User(id: null, email: "xxxx@xxxxxxx.com", hash: "xxxxxxx", role: .admin) 
user.create(on: request)

I get the same error as when I passed in (null, ...) above:

[ ERROR ] PostgreSQLError.server.error.ExecConstraints: null value in column "id" violates not-null constraint (ErrorMiddleware.swift:26)
[ DEBUG ] Possible causes for PostgreSQLError.server.error.ExecConstraints: Failing row contains (null, xxxx@xxxxxxx.com, xxxxxxx, admin). (ErrorMiddleware.swift:26)

This is my Package.swift:

let package = Package(
    name: "Users",
    products: [
        .library(name: "Users", targets: ["App"]),
    ],
    dependencies: [
        // 💧 A server-side Swift web framework.
        .package(url: "https://github.com/vapor/vapor.git", from: "3.0.0"),

        // 🔵 Swift ORM (queries, models, relations, etc) built on SQLite 3.
        .package(url: "https://github.com/vapor/fluent-postgres-driver.git", from: "1.0.0"),

    ],
    targets: [
        .target(name: "App", dependencies: ["FluentPostgreSQL", "Vapor"]),
        .target(name: "Run", dependencies: ["App"]),
        .testTarget(name: "AppTests", dependencies: ["App"])
    ]
)

Solution

When you've specified a nullable id, such as:

struct User: PostgreSQLModel {

    var id: ID?
    let column1: String 
    let column2: String
}

Fluent should automatically pass a request of the following if the ID passed is null:

INSERT INTO table (column1, column2) VALUES ('xxxxx', 'xxxxx');

Instead of:

INSERT INTO table (id, column1, column2) VALUES (null, 'xxxxx', 'xxxxx');

Update

Tracking down the error it seems to stem from here:

public func query(_ query: PostgreSQLQuery, resultFormat: PostgreSQLResultFormat, _ onRow: @escaping ([PostgreSQLColumn: PostgreSQLData]) throws -> ()) -> Future<Void> {
    var binds: [Encodable] = []
    let sql = query.serialize(&binds)
    return operation {
        do {
            return try self._query(sql, binds.map { try PostgreSQLDataEncoder().encode($0) }, resultFormat: resultFormat, onRow)
        } catch {
            return self.eventLoop.newFailedFuture(error: error)
        }
    }
}

Where the serialized query is:

"INSERT INTO "User" ("role", "id", "email", "hash") VALUES ('admin', $1, $2, $3) RETURNING *"

Which stems from PostgreSQLDatabase+QuerySupporting

Seeing as in the file PostgreSQLDatabase+QuerySupporting doesn't even exist anymore my guess is that this was fixed in a later version, but that version is still either in beta or RC?

Update 2

case ._insert:
    var insert: PostgreSQLInsert = .insert(fluent.table)
    var values: [PostgreSQLExpression] = []
    fluent.values.forEach { row in
        // filter out all `NULL` values, no need to insert them since
        // they could override default values that we want to keep
        switch row.value {
        case ._literal(let literal):
            switch literal {
            case ._null: return
            default: break
            }
        case ._bind(let bindValue):
            // EXTREMELY HACKY CODE
            switch (bindValue.value) {
            case .encodable(let encodable):
                if (row.key == "id" && Optional<Int>.self == type(of: encodable)) { return }
            default: break 
            }
            break
        default: break
        }
        insert.columns.append(.column(nil, .identifier(row.key)))
        values.append(row.value)
    }
    insert.values.append(values)
    insert.upsert = fluent.upsert
    insert.returning.append(.all)
    query = .insert(insert)

The following extremely hacky code fixed the issue, however it is super hacky and obviously not production code. Is there a more permanent solution here?

vapor / fluent-postgres-driver