Episode #371

Pivot Tables for Many to Many Relationships

Series: Server-side Swift with Vapor

14 minutes
Published on January 10, 2019

This video is only available to subscribers. Get access to this video and 578 others.

When you have a many-to-many relationship you typically rely on a join table, or what Vapor calls a Pivot table to relate the records together. In this episode we will create a relationship to allow an issue to have many tags, and also allow a tag to apply to many issues. We'll see how we can use Vapor's ModifiablePivot and Sibling types to make working with these relationships easier.

Episode Links

New Tag Model

Here we have a new model called Tag. An Issue can have many Tags, and Tags can belong to many Issues. This means we have a many-to-many relationship.

final class Tag : UUIDModel, TimestampModel {
  // ...

Designing the Join Model

In order for us to have many on both sides of the relationship, we need a model that refers to the linking of these records. Sometimes these are called join tables, but Vapor calls them Pivot tables.

final class IssueTag : Pivot, UUIDModel {
    static var name: String = "issue_tag"
    typealias Left = Issue
    typealias Right = Tag

    static var leftIdKey: WritableKeyPath = \.issueId
    static var rightIdKey: WritableKeyPath = \.tagId

    var id: UUID?
    var issueId: UUID
    var tagId: UUID

Then we can create a migration to create this table.

extension IssueTag : Migration {
    static func prepare(on conn: PostgreSQLConnection) -> Future<Void> {
        return PostgreSQLDatabase.create(self, on: conn) { builder in
            builder.field(for: \.issueId)
            builder.field(for: \.tagId)
            builder.reference(from: \.issueId, to: \Issue.id, onUpdate: nil, onDelete: .cascade)
            builder.reference(from: \.tagId, to: \Tag.id, onUpdate: nil, onDelete: .cascade)

And don't forget to add this line to the migrations list in configure.swift.

Setting up the Sibling Relationship

Over on Issue.swift, we can add a new Siblings relationship:

var tags: Siblings<Issue, Tag, IssueTag> {
    return siblings()

These are specified in from, to, through order (which is how I like to think about it).

We can do the same on the Tag side:

var issues: Siblings<Tag, Issue, IssueTag> {
    return siblings()

Creating Sibling Records

Since we don't have any useful information in our Pivot model beyond the two keys, we can use the ModifiablePivot type, which requires an initializer:

final class IssueTag : ModifiablePivot, UUIDModel {
    // ...

    init(_ left: Issue, _ right: Tag) throws {
        issueId = try left.requireID()
        tagId = try right.requireID()

    // ...

Given an issue and tag instance, you can relate them like this:

return issue.tags.attach(tag, on: req).flatMap { issueTag in
    print("Created issue tag: \(issueTag.id!)")

This episode uses Swift 4.2, Vapor 3.0.8, Fluent postgresql-1.0.0, Xcode 10.1.