How to create many-to-many association using GRDB in Swift?

Question

I'm trying to setup an association between songs and albums. Each song can appear on one or more albums and each album can contain one or more songs. I decided to go with GRDB for my database solution but I'm stuck on this issue.

What I tried: As documentation suggests, I created a passport struct, like this:

public struct AlbumPassport: TableRecord {
    static let track = belongsTo(SPTTrack.self)
    static let album = belongsTo(SPTAlbum.self)
}

Then in SPTTrack class:

public static let passports = hasMany(AlbumPassport.self)
public static let albums = hasMany(SPTAlbum.self, through: passports, using: AlbumPassport.album)

And in SPTAlbum class:

public static let passports = hasMany(AlbumPassport.self)
public static let tracks = hasMany(SPTTrack.self, through: passports, using: AlbumPassport.track)

I cannot find in the documentation a good example on how to build a request using those associations. In SPTAlbum class I added linkedTracks property

public var linkedTracks: QueryInterfaceRequest<SPTTrack> {
    request(for: Self.tracks)
}

And then in my database manager:

func fetchTracks(for album: SPTAlbum) -> [SPTTrack] {
    do {
        return try dbQueue.read { db in
            try album.linkedTracks.fetchAll(db)
        }
    } catch {
        print(error)
    }
    return []
}

I'm getting error:

SQLite error 1: no such table: albumPassport

which is pretty self-explanatory, but I have no clue how and where should I create table for the AlbumPassport struct and if there are any additional steps I should take to actually populate this table with album/track connections.

Both SPTTrack/SPTAlbum have a field called id which is set as primaryKey during first migration.

Answer 1

There are no issues with your associations. All hasMany() and belongsTo() are correct. The error you are getting tells me that there is something wrong with your database setup (which you didn't include in your question).

Here is how i would implement it:

import GRDB

struct Album: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let passports = hasMany(AlbumPassport.self)
    static let tracks = hasMany(Track.self, through: passports, using: AlbumPassport.track)
}

struct Track: Codable, Hashable, FetchableRecord, MutablePersistableRecord {
    mutating func didInsert(with rowID: Int64, for column: String?) { id = rowID }
    var id: Int64?
    var name: String
    static let passports = hasMany(AlbumPassport.self)
    static let albums = hasMany(Album.self, through: passports, using: AlbumPassport.album)
}

struct AlbumPassport: Codable, Hashable, FetchableRecord, PersistableRecord {
    let track: Int64
    let album: Int64
    static let track = belongsTo(Track.self)
    static let album = belongsTo(Album.self)
}

let queue = DatabaseQueue()
try queue.write { db in
    
    try db.create(table: "album") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "track") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    try db.create(table: "albumPassport") { t in
        t.column("track", .integer).notNull().indexed().references("track")
        t.column("album", .integer).notNull().indexed().references("album")
        t.primaryKey(["track", "album"])
    }
    
    // Testing real data from https://music.apple.com/de/artist/yiruma/73406786
    
    var solo = Album(name: "SOLO")
    try solo.insert(db)
    var sometimes = Track(name: "Sometimes Someone")
    try sometimes.insert(db)
    try AlbumPassport(track: sometimes.id!, album: solo.id!).insert(db)
    var destiny = Track(name: "Destiny Of Love")
    try destiny.insert(db)
    try AlbumPassport(track: destiny.id!, album: solo.id!).insert(db)
    
    var bestOf = Album(name: "Best of Yiroma")
    try bestOf.insert(db)
    var poem = Track(name: "Poem")
    try poem.insert(db)
    try AlbumPassport(track: poem.id!, album: bestOf.id!).insert(db)
    var river = Track(name: "River Flows In You")
    try river.insert(db)
    try AlbumPassport(track: river.id!, album: bestOf.id!).insert(db)
}

// Fetch all albums and their tracks
try queue.read { db in
    struct AlbumInfo: FetchableRecord, Decodable, CustomStringConvertible {
        var album: Album
        var tracks: Set<Track>
        var description: String { "\(album.name) → \(tracks.map(\.name))" }
    }
    let request = Album.including(all: Album.tracks)
    let result = try AlbumInfo.fetchAll(db, request)
    print(result)
    // > [SOLO → ["Sometimes Someone", "Destiny Of Love"], Best of Yiroma → ["River Flows In You", "Poem"]]
}

See my answer of Many-to-many relationship where one entity has multiple properties of the same type for another code example.