Skip to main content
Associations are connections between record types that mirror the foreign key relationships in your database schema. They enable efficient joins, prefetching, and filtering across related tables.

​
Why Use Associations?

Without associations, loading related data requires multiple queries: 
// Without associations: N+1 queries problem
let books = try Book.fetchAll(db)
let bookInfos = try books.map { book -> BookInfo in
    let author = try Author.fetchOne(db, id: book.authorId) // One query per book!
    return BookInfo(book: book, author: author)
}
With associations, GRDB generates optimized SQL with joins: 
// With associations: Single query
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var author: Author
}

let bookInfos = try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)
// SELECT book.*, author.* 
// FROM book 
// JOIN author ON author.id = book.authorId

​
Required Protocols

To use associations, your record types must adopt:
  • TableRecord - Declares the database table name
  • FetchableRecord - Enables fetching records from the database
  • EncodableRecord - Enables the request(for:) method for associated records
struct Author: Codable, FetchableRecord, PersistableRecord, TableRecord {
    var id: Int64
    var name: String
}

struct Book: Codable, FetchableRecord, PersistableRecord, TableRecord {
    var id: Int64
    var authorId: Int64
    var title: String
}
PersistableRecord includes EncodableRecord plus insertion/update/deletion methods.

​
Association Types

GRDB provides five association types:

BelongsTo

One-to-one: A book belongs to an author

HasMany

One-to-many: An author has many books

HasOne

One-to-one: A country has one demographics profile

HasManyThrough

Many-to-many: A citizen has many countries through passports

HasOneThrough

One-to-one via join: A book has one return address through library

​
BelongsTo Association

A BelongsTo association indicates that a record contains a foreign key pointing to another record.

​
Database Schema

migrator.registerMigration("Books and Authors") { db in
    try db.create(table: "author") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
    }
    
    try db.create(table: "book") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("author", onDelete: .cascade).notNull()
        t.column("title", .text).notNull()
    }
}
This creates a book.authorId column with a foreign key to author.id.

​
Defining the Association

extension Book {
    static let author = belongsTo(Author.self)
}

​
Using BelongsTo

extension Book {
    static let author = belongsTo(Author.self)
    
    var author: QueryInterfaceRequest<Author> {
        request(for: Book.author)
    }
}

// Fetch the author of a book
let book: Book = ...
let author = try book.author.fetchOne(db) // Author?

​
HasMany Association

A HasMany association indicates a one-to-many relationship, typically the inverse of a BelongsTo.

​
Defining the Association

extension Author {
    static let books = hasMany(Book.self)
}

​
Using HasMany

extension Author {
    static let books = hasMany(Book.self)
    
    var books: QueryInterfaceRequest<Book> {
        request(for: Author.books)
    }
}

// Fetch all books by an author
let author: Author = ...
let books = try author.books.fetchAll(db) // [Book]

// Filter the associated books
let novels = try author.books
    .filter { $0.genre == "novel" }
    .order(\.$publishedAt.desc)
    .fetchAll(db)

​
HasOne Association

A HasOne association sets up a one-to-one connection, usually for denormalized data split across tables.

​
Database Schema

migrator.registerMigration("Countries") { db in
    try db.create(table: "country") { t in
        t.primaryKey("code", .text)
        t.column("name", .text).notNull()
    }
    
    try db.create(table: "demographics") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("country", onDelete: .cascade)
            .notNull()
            .unique()
        t.column("population", .integer)
        t.column("density", .double)
    }
}

​
Defining the Association

extension Country {
    static let demographics = hasOne(Demographics.self)
}

​
Using HasOne

struct CountryInfo: Decodable, FetchableRecord {
    var country: Country
    var demographics: Demographics?
}

let countryInfos = try Country
    .including(optional: Country.demographics)
    .asRequest(of: CountryInfo.self)
    .fetchAll(db)

​
HasManyThrough Association

A HasManyThrough association sets up a many-to-many connection through an intermediate join table.

​
Many-to-Many Example

// Database schema
migrator.registerMigration("Passports") { db in
    try db.create(table: "country") { t in
        t.primaryKey("code", .text)
        t.column("name", .text)
    }
    
    try db.create(table: "citizen") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text)
    }
    
    try db.create(table: "passport") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("country", onDelete: .cascade).notNull()
        t.belongsTo("citizen", onDelete: .cascade).notNull()
        t.column("issueDate", .date)
    }
}

// Associations
struct Country: TableRecord {
    static let passports = hasMany(Passport.self)
    static let citizens = hasMany(
        Citizen.self,
        through: passports,
        using: Passport.citizen
    )
}

struct Passport: TableRecord {
    static let country = belongsTo(Country.self)
    static let citizen = belongsTo(Citizen.self)
}

struct Citizen: TableRecord {
    static let passports = hasMany(Passport.self)
    static let countries = hasMany(
        Country.self,
        through: passports,
        using: Passport.country
    )
}

​
Using HasManyThrough

struct CitizenInfo: Decodable, FetchableRecord {
    var citizen: Citizen
    var countries: [Country]
}

// Fetch citizens with their countries
let citizenInfos = try Citizen
    .including(all: Citizen.countries)
    .asRequest(of: CitizenInfo.self)
    .fetchAll(db)

​
Nested Through Example

// Document has many sections, section has many paragraphs
struct Document: TableRecord {
    static let sections = hasMany(Section.self)
    static let paragraphs = hasMany(
        Paragraph.self,
        through: sections,
        using: Section.paragraphs
    )
}

struct Section: TableRecord {
    static let paragraphs = hasMany(Paragraph.self)
}

struct Paragraph: TableRecord { }

// Fetch all paragraphs in a document
let document: Document = ...
let paragraphs = try document
    .request(for: Document.paragraphs)
    .fetchAll(db)

​
HasOneThrough Association

A HasOneThrough association sets up a one-to-one connection through an intermediate record. 
// Database schema
migrator.registerMigration("Libraries") { db in
    try db.create(table: "library") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text)
    }
    
    try db.create(table: "address") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("library").notNull().unique()
        t.column("street", .text)
    }
    
    try db.create(table: "book") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("library").notNull()
        t.column("title", .text)
    }
}

// Associations
struct Book: TableRecord {
    static let library = belongsTo(Library.self)
    static let returnAddress = hasOne(
        Address.self,
        through: library,
        using: Library.address
    )
}

struct Library: TableRecord {
    static let address = hasOne(Address.self)
}

struct Address: TableRecord { }

// Fetch where to return the book
let book: Book = ...
let address = try book
    .request(for: Book.returnAddress)
    .fetchOne(db)

​
Joining Methods

Associations are used with joining methods that control how related records are fetched:

​
including(required:)

Joins and includes the associated record. Excludes base records without a match (INNER JOIN). 
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var author: Author // Required, never nil
}

let bookInfos = try Book
    .including(required: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)
// SELECT book.*, author.*
// FROM book
// JOIN author ON author.id = book.authorId

​
including(optional:)

Joins and includes the associated record. Includes base records even without a match (LEFT JOIN). 
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var author: Author? // Optional, may be nil
}

let bookInfos = try Book
    .including(optional: Book.author)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)
// SELECT book.*, author.*
// FROM book
// LEFT JOIN author ON author.id = book.authorId

​
including(all:)

Includes all associated records using a separate query. 
struct AuthorInfo: Decodable, FetchableRecord {
    var author: Author
    var books: [Book]
}

let authorInfos = try Author
    .including(all: Author.books)
    .asRequest(of: AuthorInfo.self)
    .fetchAll(db)
// SELECT author.* FROM author
// SELECT book.* FROM book WHERE authorId IN (...)

​
joining(required:)

Joins without including. Use for filtering by associated records. 
// Books by French authors (don't fetch author)
let books = try Book
    .joining(required: Book.author
        .filter { $0.country == "France" })
    .fetchAll(db)
// SELECT book.*
// FROM book
// JOIN author ON author.id = book.authorId
//             AND author.country = 'France'

​
joining(optional:)

Left join without including. Rarely used alone. 
let books = try Book
    .joining(optional: Book.author)
    .fetchAll(db)
// SELECT book.*
// FROM book
// LEFT JOIN author ON author.id = book.authorId

​
annotated(withRequired:)

Joins and adds specific columns from associated record. 
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var authorName: String
    var authorCountry: String
}

let bookInfos = try Book
    .annotated(withRequired: Book.author.select(\.$name, \.$country))
    .asRequest(of: BookInfo.self)
    .fetchAll(db)
// SELECT book.*, author.name, author.country
// FROM book
// JOIN author ON author.id = book.authorId

​
annotated(withOptional:)

Left join and adds specific columns (may be NULL). 
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var authorName: String?
}

let bookInfos = try Book
    .annotated(withOptional: Book.author.select(\.$name))
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

​
Filtering by Associations

Filter records based on properties of associated records: 
// Books by authors from France
let books = try Book
    .joining(required: Book.author
        .filter { $0.country == "France" })
    .fetchAll(db)

// Authors with more than 10 books
let prolificAuthors = try Author
    .having(Author.books.count > 10)
    .fetchAll(db)

// Books with no reviews
let unreviewedBooks = try Book
    .joining(optional: Book.reviews)
    .filter { $0.reviews.isEmpty }
    .fetchAll(db)

​
Association Aggregates

Compute aggregates on associated records: 
struct AuthorInfo: Decodable, FetchableRecord {
    var author: Author
    var bookCount: Int
    var avgScore: Double?
}

let authorInfos = try Author
    .annotated(with: Author.books.count)
    .annotated(with: Author.books.average(\.$score))
    .asRequest(of: AuthorInfo.self)
    .fetchAll(db)
Available aggregates:
  • count - Count associated records
  • isEmpty - Check if no associated records exist
  • min(column) - Minimum value
  • max(column) - Maximum value
  • average(column) - Average value
  • sum(column) - Sum of values

​
Combining Associations

Combine multiple associations in one request: 
struct BookInfo: Decodable, FetchableRecord {
    var book: Book
    var author: Author
    var reviews: [Review]
}

// Parallel associations
let bookInfos = try Book
    .including(required: Book.author)
    .including(all: Book.reviews)
    .asRequest(of: BookInfo.self)
    .fetchAll(db)

// Chained associations
struct BookInfo2: Decodable, FetchableRecord {
    var book: Book
    var author: Author
    var authorCountry: Country
}

let bookInfos2 = try Book
    .including(required: Book.author
        .including(required: Author.country))
    .asRequest(of: BookInfo2.self)
    .fetchAll(db)

​
Custom Foreign Keys

When your schema doesn’t follow conventions or has multiple foreign keys: 
struct Book: TableRecord {
    enum Columns {
        static let authorId = Column("authorId")
        static let translatorId = Column("translatorId")
    }
    
    static let authorForeignKey = ForeignKey([Columns.authorId])
    static let translatorForeignKey = ForeignKey([Columns.translatorId])
    
    static let author = belongsTo(Person.self, using: authorForeignKey)
    static let translator = belongsTo(Person.self, using: translatorForeignKey)
}

struct Person: TableRecord {
    static let writtenBooks = hasMany(Book.self, using: Book.authorForeignKey)
    static let translatedBooks = hasMany(Book.self, using: Book.translatorForeignKey)
}

​
Next Steps

SQL Interpolation

Mix type-safe SQL with associations

Migrations

Learn how to define foreign keys in migrations

Build docs developers (and LLMs) love

Get started for freeTalk to us