How to use a stringified UUID as Primary Key in FluentProvider for Vapor - Swift

Question

I am working on a Vapor API and while using FluentProvider, I am trying to create a Users table based off of a Users Model in App/Models/Users.swift:

final class Users: Model {
    let storage = Storage()

    // Properties
    let UserUUID:  String
    let FirstName: String
    let LastName:  String
    let EMail:     String
    let Password:  String

    // Initializer
    init( userUUID:  String,
          firstName: String, lastName: String, 
          email:     String, password: String ) 
    {
        self.UserUUID  = userUUID
        self.FirstName = firstName
        self.LastName  = lastName
        self.EMail     = email
        self.Password  = password
    }

    init(row: Row) throws {
        UserUUID  = try row.get("UserUUID" )
        FirstName = try row.get("FirstName")
        LastName  = try row.get("LastName" )
        EMail     = try row.get("EMail"    )
        Password  = try row.get("Password" )
    }

    func makeRow() throws -> Row {
        var row = Row()

        try row.set("UserUUID",  UserUUID )
        try row.set("FirstName", FirstName)
        try row.set("LastName",  LastName )
        try row.set("EMail",     EMail    )
        try row.set("Password",  Password )

        return row
    }
}

This is associating with the SQLite provider by default. I would like to use a UUID in String format as the Primary Key for this table.

My issue is that when writing the Preparation for this model, it only wants the primary key to be named "id" created with builder.id(). If I try to create a custom column instead and use it as the Primary Key/Identifier, it throws an error. The Preparation code is as follows:

extension Users: Preparation {
    static func prepare(_ database: Database) throws {
        try database.create(self) { builder in

            /* builder.id()    <- intentionally omitted. */
            builder.custom( // <- Attempted in place of builder.id()
                "UserUUID",  
                 type:"VARCHAR(255) PRIMARY KEY", 
                 optional: false, 
                 unique: true
            ) 
            builder.string("FirstName", optional: false, unique: false)
            builder.string("LastName",  optional: false, unique: false)
            builder.string("EMail",     optional: false, unique: true)
            builder.string("Password",  optional: false, unique: false)
        }
    }

    static func revert(_ database: Database) throws {
        try database.delete(self)
    }
}

The resulting error when calling the associated route from the API:

POST /service/users/new
[SQLite.StatusError: error("table userss has no column named id")]
Conform 'SQLite.StatusError' to Debugging.Debuggable to provide more 
debug information.

If I am understanding correctly, it will only accept an ID column named "id", which apparently can only be generated by using the builder.id() method, which only assigns an auto-incremented integer to the ID.

So my questions are:

1. Is it possible to use a specific column as the Primary Key/Identifier column, and if so, how?

2. Would renaming "UserUUID" to "id" but keeping the rest of it's properties the same also work as a solution?

Answer 1

It looks like Fluent knows how to create the ID by using static variables on Entity which is a protocol your User model conforms to. Specifically, it looks like you'll need to override the following two variables:

final class Users: Model {
    static var idKey: String {
        return "UserUUID"
    }
    static var idType: IdentifierType {
        return .uuid
    }
}

Now, if you uncomment your builder.id() line, the id should be created correctly.