A Gist to show how to join 3+ tables together in psql, for the Vapor 3 Framework

FizBuzPivots.swift

final class Foo: PostgreSQLModel, Migration {
    var id: Int?
}

final class Bar: PostgreSQLModel, Migration {
    var id: Int?
}

final class Baz: PostgreSQLModel, Migration {
    var id: Int?
}

final class FizBuz: PostgreSQLModel {
    var id: Int?
    var fooId: Foo.ID
    var barId: Bar.ID
    var bazId: Baz.ID
}

extension FizBuz: Migration {
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.create(self, on: connection) { builder in
            try addProperties(to: builder)
            try builder.addReference(from: \.fooId, to: \Foo.id)
            try builder.addReference(from: \.barId, to: \Bar.id)
            try builder.addReference(from: \.bazId, to: \Baz.id)
        }
    }
}

struct FizBuzUniqueMultiIndexMigration: Migration {
    typealias Database = PostgreSQLDatabase

    static let indexName = "fiz_buz_ref_index"

    public static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.transaction(on: connection) { (conn) -> Future<Void> in
            return try conn.query("CREATE UNIQUE INDEX  \(indexName) UNIQUE(\"fooId\", \"barId\", \"bazId\");")
                .map(to: Void.self, { (_) -> Void in () })

        }
    }

    public static func revert(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.transaction(on: connection) { (conn) -> Future<Void> in
            return try conn.query("DROP INDEX IF EXISTS \(indexName);").map(to: Void.self, { (_) -> Void in () })
        }
    }
}