Using Vapor-Fluent to Upsert Models

Using vapor-fluent to upsert models

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
    }
}

Vapor, fluent save/create complex model with PostgreSQL

The save in your create route will not save any children objects in todo. This is your responsibility. I haven't changed to async methods yet, but the way to do it originally in vapor 4 is to save the parent first and then capture the id and update the children objects so that they have the correct foreign key value. Something like:

func create(req: Request) async throws -> CollectionResponse {
    let todo = try req.content.decode(CollectionResponse.self)
    return todo.save(on: req.db).flatMap { _ in
        // todo has been updated with the primary key field value
        todo.childrenArray.map{ $0.$modelA.$id = todo.$id }
        todo.childrenArray.save(on: req.db).flatMap { _ in
            return todo
        }
    }
}

Following the guidelines in https://docs.vapor.codes/4.0/fluent/model/ for naming fields and properties consistently would be a good idea. See this page for details of how the array save works on the childrenArray, it is essentially doing it as a single transaction.

How to correctly handle simplified model in Fluent/Vapor?

The default situation is that a query on the User model will not include any Children fields in the result. To include them, you need .with(\.$products) in the query.

You can limit the fields returned by modifying your query as in the example:

User.query(on: request.db).field(\.$name).field(\.$email).all()

This only brings those fields into the model and leaves unwanted fields in an uninitialised state. See here for more information.

Create record only if parent exists in Vapor using Fluent

You can do something like

LastName.query(on: req.db).filter(\.$name == "Smith")
  .first()
  .unwrap(or: Abort(.notFound))
  .flatMap { lastName in
    let middlenameQuery = MiddleName.query(on: req.db).filter(\.$name == "Jane").first().flatMap { middlenameFound in
      if let middleName = middlenameFound {
        return req.eventLoop.future(middleName)
      } else {
        let newMiddlename = Middlename(name: "Jane")
        return newMiddlename.create(on: req.db).transform(to: newMiddlename)
      }
    }
    return middlenameQuery.flatMap { middlename in
      // Use last name and middle name here
    }

}

request, that save array of models in vapor 3

So, I solved my problem without transactions:

func update(_ req: Request, todos: [CreateTodoRequest]) throws -> Future<HTTPStatus> {
    let user = try req.requireAuthenticated(User.self)
    // 1
    return try todos.map { try Todo(todo: $0, userID: user.requireID()).create(on: req) }
        // 2
        .flatten(on: req)
        // 3
        .transform(to: .ok)
}

.create(on: req) returns an EventLoopFuture<Todo>. So todos.map will return an array of [EventLoopFuture<Todo>]. The second step is to transform it to EventLoopFuture<[Todo]>. And the third step is to transform it to HttpStatus.ok response

Vapor 4 Fluent create Model doesn't save it to the database

Try saving the product instead of the input:

return product.create(on: req.db).map { print("Product saved") }
       .transform(to: .ok)

Find all items with empty many to many relationship in vapor fluent

I haven't checked this out but, intuitively, this should work if you want to make use of the Sibling relationship:

Planet.query(on:req.db).with(\.$tags).all().map { allPlanets in
    let planetWithoutTags = allPlanets.filter { $0.tags.isEmpty }
    // continue processing
}

This is a bit wasteful, since you are fetching (potentially) lots of records that you don't want. A better approach is to get the planets that do have tags and then exclude those:

PlanetTag.query(on:req.db).unique().all(\.$planet).flatMap { planetTags in
    return Planet.query(on:req.db).filter(\.$id !~ planetTags.map { $0.$planet.$id }).all().flatMap { planetsWithoutTags in 
        // continue processing
    }
}

---

Related Topics