How to create Many-to-many association?

[wiencheck]

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.

Pardon me if I made a stupid mistake somewhere, I'm learning as I go

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.

[groue]

Hello @wiencheck,

In order to "create a many-to-many association" with GRDB Associations you need to:

1. Define the database schema that supports such a relationship.
2. Define in Swift code the record types that match this database schema.
3. Know which kind of request you want to build.
4. Define the GRDB associations that support those requests.

Define the database schema that supports such a relationship

Many-to-many association, in a relational database, needs 3 database tables.

In the GRBD documentation, there is a many-to-many association between 1. countries and 2. citizens through their 3. passports.

In your case, there is a many-to-many association between 1. albums and 2. songs through their 3... finding a name here is difficult. A good default name is the concatenation of the two associated tables: 3. "album-song".

So let's define three tables:

try db.create(table: "album") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("title", .text).notNull()
    ...
}

try db.create(table: "song") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("title", .text).notNull()
    ...
}

try db.create(table: "albumSong") { t in
    t.primaryKey {
        t.belongsTo("album", onDelete: .cascade)
        t.belongsTo("song", onDelete: .cascade)
    }
}

Note how this sample code defines primary keys and foreign keys that make sure the database can not contain invalid data. If, in your app, album and songs use a string or an uuid primary keys, you will have to adapt this sample code.

So, how do you associate songs and albums? Given this lists of albums and songs:

album
id title
1  Hot Rats
2  Chunga's Revenge
3  Roxy & Elsewhere

song
id title
1  Peaches en Regalia
2  Willie the Pimp
3  Penguin in Bondage

To say that "Peaches en Regalia" is in album "Hot Rats", you add an entry in albumSong:

albumSong
albumId songId
1       1

If one day you want to store in the database the position of a song in an album, you'll need to add a position column in the albumSong table.

Now everything should be clear, at the database level.

Define in Swift code the record types that match this database schema

This is abundantly documented in Recommended Practices for Designing Record Types:

struct Album: Codable, Identifiable {
    var id: Int64?
    var title: String
    ...
}

struct Song: Codable, Identifiable {
    var id: Int64?
    var title: String
    ...
}

struct AlbumSong: Codable {
    var albumId: Int64
    var songId: Int64
}

// Add Database access

extension Album: FetchableRecord, MutablePersistableRecord {
    // Update auto-incremented id upon successful insertion
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

extension Song: FetchableRecord, MutablePersistableRecord {
    // Update auto-incremented id upon successful insertion
    mutating func didInsert(with rowID: Int64, for column: String?) {
        id = rowID
    }
}

extension AlbumSong: FetchableRecord, PersistableRecord { }

Note how records that have their ids auto-incremented by the database, Album and Song, use the MutablePersistableRecord protocol, and define the didInsert(id:for:) method. On the other size, the primary key of AlbumSong is not auto-incremented, because it is the (albumId, songId) pair. That's why AlbumSong only needs PersistableRecord, and can ignore didInsert(id:for:).

That's it!

Know which kind of request you want to build

Let's say you want to fetch all songs from an album. This is what you want to write:

let album: Album = ...
let songs: [Song] = try dbQueue.read { db in
    try album.songs.fetchAll(db)
}

Fine! Let's do that!

Define the GRDB associations that support those requests

So we want to be able to write:

let album: Album = ...
let songs: [Song] = try dbQueue.read { db in
    try album.songs.fetchAll(db)
}

We need to define album.songs. This is a property on Album, right? It returns a request of songs, all songs from this album. We can fetch songs from this requests. This is what the sample code above does.

At the level of the database, we can not really jump directly from the album table to the song table. We need to jump through the albumSong table in the middle. That's what the HasManyThrough GRDB association does. It is a compound association which is defined from simpler, direct associations:

extension Album {
    static let albumSongs = hasMany(AlbumSong.self)
    static let songs = hasMany(Song.self, through: albumSongs, using: AlbumSong.song)
}

extension AlbumSong {
    static let song = belongsTo(Song.self)
}

In the above code:

• Album.albumSongs is a direct HasMany association.
• AlbumSong.song is a direct BelongsTo association.
• Album.songs is an indirect HasManyThrough association.

Now that the Album.songs association is defined, we can define the album.songs property, the one that returns the request for all songs from an album:

extension Album {
    var songs: QueryInterfaceRequest<Song> {
        request(for: Album.songs)
    }
}

And this is the end of our journey:

// It works!
let album: Album = ...
let songs: [Song] = try dbQueue.read { db in
    try album.songs.fetchAll(db)
}

If there is one thing to remember, it is to always follow those steps:

1. Define the database schema that supports such a relationship.
2. Define in Swift code the record types that match this database schema.
3. Know which kind of request you want to build.
4. Define the GRDB associations that support those requests.

---

You also ask how to populate the database.

You do it with regular persistence methods:

try dbQueue.write { db in
    // Insert an album
    var album = Album(id: nil, title: "Hot Rats")
    try album.insert(db) // Now album.id is not nil
    
    // Insert a song
    var song = Song(id: nil, title: "Peaches en Regalia")
    try song.insert(db) // Now song.id is not nil

    // Insert a song in an album
    try AlbumSong(albumId: album.id!, songId: song.id!).insert(db)
}

---

CHANGELOG:

• 2024/09/01: use belongsTo for defining the foreign keys in the "albumSong" table.
• 2023/07/04: upgraded sample code for GRDB v6.15.1 ("albumSong" migration), and link to the Recommended Practices for Designing Record Types guide
• 2021/09/23: first version

[wiencheck]

Thank you so much @groue for such detailed explanation! Just tried that in my project and everything worked flawlessly.

Happy to see Zappa fan in the wild :D

[groue]

You're welcome, this howto guide had to be written eventually, and your question was a good opportunity 👍 Happy that Frank Zappa was a well-chosen example ;-)

[gmedori]

Sorry to draw attention to a super old issue, but I just wanted to add on and say thank you for this write-up. It's perfect and exactly what I wanted to understand. You rock @groue

[groue]

You're welcome @gposcidonio. Your message gave the opportunity to update the answer for the latest GRDB version (the creation of the "albumSong" table).