Custom Queries in Vapor Fluent

While the QueryBuilder interface is pretty neat, it’s still missing some things. Recently, I needed a GROUP BY clause in something, and was rather unsurprised to find that Fluent doesn’t support it.1

Fortunately, it’s still possible to write custom SQL and read in the results. Make yourself a convenience struct to unpack the results:

struct MyQueryResult: Codable { let parentID: Parent.IDValue let sum: Double }
Code language: Swift (swift)

(Strictly speaking, it can be Decodable instead of Codable, but as long as the Parent.IDValue (generated for free by making Parent conform to Model, I believe) is Codable, Swift generates the conformance for us.)

Now, in your controller, import SQLKit, and then get your database instance as an SQL database instance:

guard let sqlDatabase = req.db as? SQLDatabase else { // throw or return something here }
Code language: Swift (swift)

After that, write your request:

let requestString = "SELECT ParentID, SUM(Value) FROM child GROUP BY ParentID"
Code language: Swift (swift)

Note – your syntax may vary; I found that, using Postgres, you need to wrap column names in quotes, so I used a neat Swift feature to make that less painful:

let requestString = #"SELECT "ParentID", SUM("Value") FROM child GROUP BY "ParentID""#
Code language: Swift (swift)

If you want to use string interpolation, swap out \() for \#().

Finally, make the query:

return sqlDatabase.raw(SQLQueryString(requestString)).all(decoding: MyQueryResult.self)
Code language: Swift (swift)
  1. Entity Framework Core, which is an incredibly robust, full-featured ORM, only barely supports GROUP BY, so seeing this rather young ORM not support it isn’t all that shocking.

Serving ‘files’ in Vapor

In my experience, dynamically generating a file, serving it immediately, and not persisting it on the server is a pretty common use case. In general, this is one of two things – either a PDF download, or a CSV. While my Vapor tinkering hasn’t yet given me an opportunity to generate PDFs on the server, I have had an occasion to create a CSV, and wrote up a little helper for doing so.

import Vapor struct TextFileResponse { enum ResponseType { case inline, attachment(filename: String) } var body: String var type: ResponseType var contentType: String } extension TextFileResponse: ResponseEncodable { public func encodeResponse(for request: Request) -> EventLoopFuture<Response> { var headers = HTTPHeaders() headers.add(name: .contentType, value: contentType) switch type { case .inline: headers.add(name: .contentDisposition, value: "inline") case .attachment(let filename): headers.add(name: .contentDisposition, value: "attachment; filename=\"\(filename)\"") } return request.eventLoop.makeSucceededFuture(.init(status: .ok, headers: headers, body: .init(string: body))) } }
Code language: Swift (swift)

That’ll work for any file you can assemble as text; CSV just struck me as being the most useful example. Use ResponseType.inline for a file you want displayed in a browser tab, and .attachment if it’s for downloading.

And if you’re doing a lot of CSVs, give yourself a nice little helper:

extension TextFileResponse { static func csv(body: String, name: String) -> TextFileResponse { .init(body: body, type: .attachment(filename: name), contentType: "text/csv") } }
Code language: Swift (swift)

Default Values in Vapor Fluent

My recent tinkering has been with Vapor, and while I mostly like their Fluent ORM, it has some rough edges and semi-undocumented behavior. At some point, I’ll feel confident enough in what I’ve learned through trial and error (combined with reading the source code – open source!) to actually make some contributions to the documentation, but for now, I’m going to throw some of the things I struggled with up here.

If you’re using a migration to add a column, and specifically want it to be non-null, you’ll need a default value. My first approach was to do a three-step migration, adding the column as nullable, then filling the default value on all rows, and then setting the column to be non-null, but that didn’t feel right. Eventually, though, I figured out how to express a DEFAULT constraint in Fluent:

let defaultValueConstraint = SQLColumnConstraintAlgorithm.default(/* your default value here */)
Code language: Swift (swift)

Then, in your actual schema builder call:

.field("column_name", /* your type */, .sql(defaultValueConstraint), .required)
Code language: Swift (swift)

Note that SQLColumnConstraintAlgorithm isn’t available from the Fluent module, you’ll need to import SQLKit first.

And here, a full worked example:

import Vapor import Fluent import SQLKit struct DemoMigration: Migration { func prepare(on database: Database) -> EventLoopFuture<Void> { let defaultValueConstraint = SQLColumnConstraintAlgorithm.default(false) return database.schema(DemoModel.schema) .field(DemoModel.FieldKeys.hasBeenTouched, .bool, .sql(defaultValueConstraint), .required) .update() } func revert(on database: Database) -> EventLoopFuture<Void> { database.schema(DemoModel.schema) .deleteField(DemoModel.FieldKeys.hasBeenTouched) .update() } }
Code language: Swift (swift)

(For context, I’m in the habit of having a static var schema: String { "demo_model" } and a struct FieldKeys { static var hasBeenTouched: FieldKey { "has_been_touched" } } within each of my Fluent models – it keeps everything nice and organized, and avoids having stringly-typed issues all over the place.)


Reversi: A Postmortem

Spring quarter consisted of two things: beginning the internship, and an “intro to programming” course. Which, at first glance, seems like it would’ve been a “coast to an easy A” kind of thing for me, but that wasn’t my goal. And, to quote the Dean of UCI’s Graduate Division, “grad school is for you.”

So, at the start of the quarter, I sat down to figure out what my goals for this class would be, and came up with two things. The first, which I won’t be writing about, was to get a bit more teaching experience – in the vein of “guiding people to asking the right questions,” rather than just showing them the answers.

Second, and the topic of this post, was that I wanted to learn Vapor. The professor was kind enough to let me do this – instead of doing the course project (an online game of Reversi) in Node, I did it in Vapor.

As a learning exercise, I’d say it was… okay.

What Went Well

I love Swift as a language. The type system just fits in my head, it aligns incredibly well with how I think.

In this case, that meant representing all the events to the server, and the responses from the server, as enums.

It also meant that I could have a solid Game class that represented the whole game board, with some neat logic, like getters that calculate the current score and if the game has ended. Pair those with a custom Codable implementation, and you’ve moved the majority of the logic to the server.

… and What Didn’t

The fact that I’m representing events to and from the server as enums, instead of using Vapor’s routing system, was a result of tacking on another thing I wanted to learn about, and trying to loosely hew to the nominal course objectives. The official version of the project used WebSockets for all the communication. Vapor supports WebSockets. Great combo, right?

Well, sure, but it meant I did almost nothing with the actual routing. Instead I re-implemented a lot of it by hand, and not in a very clean way. Vapor doesn’t scope things the way I expected – based on some experimentation, it instantiates a single copy of your controller class and reuses it, rather than having one per connection. So instead of having nice class-level storage of variables, and splitting everything up into functions with the main one handling routing, it all wound up crammed into the main function. Just so I could maintain the proper scope on variables. I’m still not happy about it.

What’s Next

I’d like to keep tinkering with Vapor. When I’ve got the time, I have a project in mind where it seems like a good fit.

In the meantime, I hope their documentation improves a lot. The docs they have are good tutorials, and cover their material well; they also, it feels like, leave out the lion’s share of the actual framework. By the end of the project, I’d given up on the docs and was just skimming through the source code on GitHub, trying to find the implementation of whatever I was trying to work with. (This, by the way, doesn’t work with Leaf, the templating engine – the docs are basically nonexistent, and the code is abstracted enough that you can’t really skim it, either.)

Complaints aside, I still like Vapor. I picked up a book on the framework, which seems like a pretty good reference on the topic.

And hey, it was a neat little project. (The JavaScript is a disorganized mess, but it’s also aggressively vanilla – while the rest of the class was learning about NPM, I decided to see how far I could get with no JS dependencies whatsoever. Answer: very.) Check it out, if you’d like: