Database connections in Vapor 4

Version 4 of the Swift web framework Vapor was released a while ago. Vapor emphasizes their ORM, Fluent, and it seems that version 4 has changed how a database connection can be acquired if you prefer to write the SQL yourself. They've also skipped documenting it, so getting things working requires some digging. In this post I'll explain how to do it. I'm using PostgreSQL.

You need a connection pool. The right place to set it up is your app's configure(_:Application) method. Use an environment variable to feed a database URL to your app:

import Vapor

public func configure(_ app: Application) throws {
    guard let dbUrlString = Environment.get("DBURL") else {
        preconditionFailure("Missing DBURL")
    }
    /* … */

    // register routes
    try routes(app)
}

Now that you have a URL, import PostgresKit and set up the pool:

import PostgresKit
import Vapor

public func configure(_ app: Application) throws {
    guard let dbUrlString = Environment.get("DBURL") else {
        preconditionFailure("Missing DBURL")
    }
    
    let postgresConfiguration = PostgresConfiguration(url: dbUrlString)!
    let pool = EventLoopGroupConnectionPool(
        source: PostgresConnectionSource(configuration: postgresConfiguration),
        on: app.eventLoopGroup
    )
    /* … */

    // register routes
    try routes(app)
}

Next we need to make the pool available to our request handlers and make sure it's shut down correctly. Making it available to request handlers happens by inserting it into the Application object's storage. Shutdown requires implementing Vapor's LifecycleHandler and registering it with the Application.

First define a struct that wraps the pool:

struct DatabaseService {
    let pool: EventLoopGroupConnectionPool<PostgresConnectionSource>
}

To keep the service in Application.storage, we need a key type:

struct DatabaseServiceKey: StorageKey {
    typealias Value = DatabaseService
}

Add an Application extension property to make it easier to access the service in the storage:

extension Application {
    var databaseService: DatabaseService? {
        get { self.storage[DatabaseServiceKey.self] }
        set { self.storage[DatabaseServiceKey.self] = newValue }
    }
}

The lifecycle implementation looks like this:

extension DatabaseService: LifecycleHandler {
    func shutdown(_ application: Application) {
        self.pool.shutdown()
    }
}

Now you just have slot these pieces in place in configure:

import PostgresKit
import Vapor

public func configure(_ app: Application) throws {
    guard let dbUrlString = Environment.get("DBURL") else {
        preconditionFailure("Missing DBURL")
    }
    
    let postgresConfiguration = PostgresConfiguration(url: dbUrlString)!
    let pool = EventLoopGroupConnectionPool(
        source: PostgresConnectionSource(configuration: postgresConfiguration),
        on: app.eventLoopGroup
    )

    let dbService = DatabaseService(pool: pool)
    app.databaseService = dbService
    app.lifecycle.use(dbService)

    // register routes
    try routes(app)
}

Now you have a working database setup. If you want to run migrations, the configure method is probably a good place to do it, before you set up the routes. I keep the DB code in a domain specific DBClient type; you can use any division of responsibilities you like.

    app.lifecycle.use(dbService)

    let db = dbService.pool.database(logger: app.logger)
    let dbClient = DBClient(database: db)
    app.logger.info("Will run migrate on DB")
    _ = try dbClient.migrate().wait()
    app.logger.info("DB migration done")

    // register routes

When handling requests, you'll just have to get the database service from Request.application. I like to create a struct called RequestEnvironment that encapsulates acquisition of the service and creation of domain logic services. Something like this:

struct RequestEnvironment {
    var makeFooService: () -> FooService

    static func makeDefault(req: Request) -> RequestEnvironment {
        guard let dbService = req.application.databaseService else {
            fatalError("Missing DatabaseService")
        }
        let db = dbService.pool.database(logger: req.logger)
        let dbClient = DBClient(database: db)
        return RequestEnvironment(
            makeFooService: { FooService(dbClient: dbClient, request: req) }
        )
    }    
}

Now when your controller handles a request, create the RequestEnvironment object and use it to call your services with the database client:

struct FooController {
    func create(_ req: Request) -> EventLoopFuture<FooExternal> {
        let newFoo = try req.content.decode(NewFooIncoming.self)
        let env = RequestEnvironment.makeDefault(req: req)
        return env.makeFooService().makeFoo(newFoo)
    }
}

That's it! Go forth and SQL, swiftly.

Alfred Script Filter with find and jq

Looks like this is a jq blog now, so here's another one.

I work on an iOS repository that's used to create a large number of apps and a few frameworks. Each app has a directory with configuration and a script that regenerates the associated Xcode project with XcodeGen.

You can run the script from the shell, or from Finder. Both of these require that you navigate to the appropriate directory or find a window that's already there. Both approaches work, and both are unsatisfactory.

I use Alfred for launching apps and all sorts of other things on macOS. One of the things it allows is workflows, a sort of Automator-like thing where after typing in a keyword Alfred will prompt you for input and execute things and so on. I built a workflow for helping with launching those regenerate scripts. Alfred's workflow sharing thing isn't great, as it creates hard to inspect zip files, and besides my specific circumstances probably aren't relevant to many people. I'll explain here in prose how it works. Adapt it to your needs as necessary.

The repository contains publisher folders. Inside the publisher folders are app folders. In each app folder is a script called regenerate-project.command. The hierarchy looks something like this:

├── publisher1
│   ├── app1
│   │   └── regenerate-project.command
│   └── app2
│       └── regenerate-project.command
└── publisher2
    └── app1
        └── regenerate-project.command

We want Alfred to ask us which one of the scripts to run after we've typed a keyword.

Let's see how we can make it happen. First, to get a list of those files we can run find in the terminal:

find . -maxdepth 3 -mindepth 3 -name regenerate-project.command -print

This gives us a list of files, one per line, like:

./publisher1/app1/regenerate-project.command
./publisher1/app2/regenerate-project.command
./publisher2/app1/regenerate-project.command

etc1.

Now, looking at Alfred's documentation, looks like we need to create a document in the Script Filter JSON Format. It should look like this:

{
    "items": [
        {
            "uid": "publisher1/app1",
            "type": "file",
            "title": "publisher1/app1",
            "arg": "publisher1/app1",
            "match": "publisher1: app1"
        }
    ]
}

And so on. The one thing that breaks the monotony of identical keys is the match value. Its purpose there is to make Alfred give better completions. Alfred has a "word boundary" matching logic, but apparently / doesn't count as a word boundary.

What do we do when we need to handle JSON on the command line? We reach for jq.

Jq has a number of parameters that modify how it handles input. To get it to ingest the list of strings produced by find, what seemed to work was using a combination of the --raw-input/-R and --null-input/-n flags, and the inputs builtin function. So the first thing to do is to build the wrapping object.

find . -maxdepth 3 -mindepth 3 -name regenerate-project.command -print | jq -nR '{ "items": [inputs] }'

Running that produces output like this:

{
  "items": [
    "./publisher1/app2/regenerate-project.command",
    "./publisher1/app1/regenerate-project.command",
    "./publisher2/app1/regenerate-project.command"
  ]
}

You could pipe find through sort or you could use jq's sort function, but the order doesn't matter as Alfred will reorder the choices by usage anyway, which is nice.

Next, just because we're careful developers, let's filter out empty entries, just in case we're ever using this with some other source of data:

find … | jq -nR '{ "items": [inputs | select(length>0)] }'

When you're running this with find, it shouldn't affect the output, but if you ever end up feeding it a text file it might be a different story.

Next drop the extra bits from the lines. We don't care about the leading ./ or the script name. They're all the same on all the lines. To lose them split the line into path components, take the two central elements and recombine them:

find … | jq -nR '{
    "items": [
        inputs |
        select(length>0) |
        split("/")[1:3] |
        join("/")
    ]
}'
{
  "items": [
    "publisher1/app2",
    "publisher1/app1",
    "publisher2/app1"
  ]
}

One thing we have to do to before we can build the object literals is capture the values — both the parts array and the combined string — in variables. This is a slightly longer version of the above jq snippet. It produces exactly the same output, but it defines the variables we need in the next step:

find … | jq -nR '{
    "items": [
        inputs |
        select(length>0) |
        split("/")[1:3] as $parts |
        $parts |
        join("/") as $file |
        $file
    ]
}'

OK, good. Now we have a the two folders as an array in $parts and as a string in $file. Then just replace that last bit that produces the array elements with an object literal.

find … | jq -nR '{
    "items": [
        inputs |
        select(length>0) |
        split("/")[1:3] as $parts |
        $parts |
        join("/") as $file |
        {
            "uid": $file,
            "type": "file",
            "title": $file,
            "arg": $file,
            match: $parts | join(": ")
        }
    ]
}'

That's a whole lot of $file and one special element that produces the value for the match field. Now the output looks like this:

{
  "items": [
    {
      "uid": "publisher1/app2",
      "type": "file",
      "title": "publisher1/app2",
      "arg": "publisher1/app2",
      "match": "publisher1: app2"
    },
    {
      "uid": "publisher1/app1",
      "type": "file",
      "title": "publisher1/app1",
      "arg": "publisher1/app1",
      "match": "publisher1: app1"
    },
    {
      "uid": "publisher2/app1",
      "type": "file",
      "title": "publisher2/app1",
      "arg": "publisher2/app1",
      "match": "publisher2: app1"
    }
  ]
}

All right, that's what we were after! Now we need to glue things together. In Alfred's Preferences, go to Workflows and create a new blank workflow. First tap on the "[𝑥]" button to set up variables. You'll need at least one, to specify where your project lives. Call it root, specify your folder as the value, and uncheck "Don't Export" as you want it as an environment variable in your script.

Next ctrl-click in the workflow background to get the context menu and select Inputs > Script Filter. In the filter configuration panel, give your workflow a keyword — I call mine regenios, this is how I invoke it in Alfred — uncheck "with space", and select "Argument Required". Select /bin/bash as the script language, and as text add this:

cd $root
find . -maxdepth 3 -mindepth 3 -name regenerate-project.command -print | jq -nR '{
    "items": [
        inputs |
        select(length>0) |
        split("/")[1:3] as $parts |
        $parts |
        join("/") as $file |
        {
            "uid": $file,
            "type": "file",
            "title": $file,
            "arg": $file,
            match: $parts | join(": ")
        }
    ]
}'

Now click Save to save your Script Filter. Then ctrl-click in the workflow background again and this time select Actions > Terminal Command. Insert the following as the terminal command:

{var:root}/{query}/regenerate-project.command && exit

Again click save. Finally in the workflow editor drag a connection from the Script Filter shape to the Terminal Command box and you're done.

Now when you open the Alfred command window and type regenios and two spaces, you should get a full list of all the items your script produced. If you start typing after the first space, Alfred will match the beginning of each word of the match field of the JSON objects we produced and give a list of the matching items.

As I said at the start of this article, this probably isn't of much use to you as is. But it might be useful as inspiration.

1

Yes, I'm aware of -print0, but it seems jq isn't.

Diff two modified JSON files in fish

Another interesting command line JSON exercise: you have two JSON files, you want to diff a modified version of one to the other, and your shell is fish.

For making JSON diffable, gron is a great choice: it transforms a JSON file into a list of assignment lines. Like this:

$ echo '{"a": ["b", "c"]}' | gron
json = {};
json.a = [];
json.a[0] = "b";
json.a[1] = "c";

gron doesn't help us with modifications, so jq is again a good choice there. In my case, I had one file that contained an array of objects, and wanted to compare the first element of that array to the content of a second file. Additionally I wanted to set the value of one key of the object from the first file to null. With jq that's quick work:

jq '.[0] | .large_field = null' file1.json | gron

The second file I wanted as-is, and that's just

gron file2.json

The last problem I wanted to solve was how to actually get the output from those two commands to diff (or colordiff). I could just pipe them to files and compare those, but that's untidy. As usual it's much easier to find answers to this for bash than for fish. In bash (and zsh, and ksh) it looks like this:

colordiff -u <(jq '.[0] | .large_field = null' file1.json | gron) <(gron file2.json)

A quick peek at the bash manual page reveals that <( is used for "process substitution". Search the web and you'll find that in fish that's accomplished with the magic command psub:

colordiff -u (jq '.[0] | .large_field = null' file1.json | gron | psub) (gron file2.json | psub)

And that's it. Pretty colored diff of the parts you're interested in, and no temporary files to remove afterwards.

© Juri Pakaste 2021