Using vapor-fluent to upsert models

Question

I am currently struggling with doing an upsert with vapor/fluent. I have a model something like this:

struct DeviceToken: PostgreSQLModel {
    var id: Int?
    var token: String
    var updatedAt: Date = Date()

    init(id: Int? = nil, token: String, updatedAt: Date = Date()) {
        self.id = id
        self.token = token
        self.updatedAt = updatedAt
    }
}

struct Account: PostgreSQLModel {
    var id: Int?
    let username: String
    let service: String
    ...
    let deviceTokenId: DeviceToken.ID

    init(id: Int? = nil, service: String, username: String, ..., deviceTokenId: DeviceToken.ID) {
        self.id = id
        self.username = username
        ....
        self.deviceTokenId = deviceTokenId
    }
}

From the client something like

{
    "deviceToken": {
        "token": "ab123",
        "updatedAt": "01-01-2019 10:10:10"
    },
    "account": {
        "username": "user1",
        "service": "some service"
    }
}

is send.

What I'd like to do is to insert the new models if they do not exist else update them. I saw the create(orUpdate:) method however this will only update if the id is the same (in my understanding). Since the client does not send the id i am not quite sure how to handle this.

Also I can't decode the model since the account is send without the deviceTokenId and therefore the decoding will fail. I guess I can address the latter problem by overriding NodeCovertible or by using two different models (one for decoding the json without the id and the actual model from above). However the first problem still remains.

What I exactly want to do is:

Update a DeviceToken if an entry with token already exists else create it
If an account with the combination of username and service already exists update its username, service and deviceTokenId else create it. DeviceTokenId is the id returned from 1.

Any chance you can help me out here ?

dehlen dehlen · Accepted Answer · 2019-01-28T18:44:35

For everyone who is interested: I solved it by writing an extension on PostgreSQLModel to supply an upsert method. I added a gist for you to have a look at: here.

Since these kind of links sometimes are broken when you need the information here a quick overview:

Actual upsert implementation:

extension QueryBuilder
where Result: PostgreSQLModel, Result.Database == Database {

    /// Creates the model or updates it depending on whether a model
    /// with the same ID already exists.
    internal func upsert(_ model: Result,
                         columns: [PostgreSQLColumnIdentifier]) -> Future<Result> {

        let row = SQLQueryEncoder(PostgreSQLExpression.self).encode(model)

        /// remove id from row if not available
        /// otherwise the not-null constraint will break
        row = row.filter { (key, value) -> Bool in
            if key == "id" && value.isNull { return false }
            return true
        }

        let values = row
            .map { row -> (PostgreSQLIdentifier, PostgreSQLExpression) in
                return (.identifier(row.key), row.value)
        }

        self.query.upsert = .upsert(columns, values)
        return create(model)
    }

}

Convenience methods

extension PostgreSQLModel {

    /// Creates the model or updates it depending on whether a model
    /// with the same ID already exists.
    internal func upsert(on connection: DatabaseConnectable) -> Future<Self> {
        return Self
            .query(on: connection)
            .upsert(self, columns: [.keyPath(Self.idKey)])
    }

    internal func upsert<U>(on connection: DatabaseConnectable,
                        onConflict keyPath: KeyPath<Self, U>) -> Future<Self> {
        return Self
            .query(on: connection)
            .upsert(self, columns: [.keyPath(keyPath)])
    }

    ....
}

I solved the other problem I had that my database model could not be decoded since the id was not send from the client, by using a inner struct which would hold only the properties the client would send. The id and other database generated properties are in the outer struct. Something like:

struct DatabaseModel: PostgreSQLModel {

    var id: Int?
    var someProperty: String

    init(id: Int? = nil, form: DatabaseModelForm) {

        self.id = id
        self.someProperty = form.someProperty
    }

    struct DatabaseModelForm: Content {
        let someProperty: String
    }
}

Using vapor-fluent to upsert models

1 Answers