GRDB.swift

Associations are connections between Record types that streamline database operations, making them safer and more efficient. Instead of manually filtering records using foreign key IDs, you can declare relationships (like belongsTo or hasMany) to allow GRDB to generate optimized SQL joins and prefetching requests.

// Example: Defining associations between Author and Book

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

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

// Usage: Fetching associated records efficiently
struct BookInfo: Decodable, FetchableRecord {
    let book: Book
    let author: Author?
}

let request = Book.including(optional: Book.author)
let bookInfos = try BookInfo.fetchAll(db, request)

GRDB is designed around several key architectural principles to provide a safe and predictable database experience in Swift:

• Record Types as Plain Values: Unlike traditional ORMs that use 'active records' with auto-updating or lazy loading (which can cause multi-threading issues), GRDB treats database records as simple, immutable-friendly Swift values.
• Raw SQL Support: GRDB embraces raw SQL for complex queries, ensuring that developers aren't forced into inefficient or illegible query builder translations when SQL is the better tool.
• Database as Single Source of Truth: Unlike Core Data or Realm, which may maintain multiple versions of data across contexts or threads, GRDB treats the SQLite database file as the unambiguous, single source of truth.
• SQLite & Application Focus: GRDB is specialized for SQLite and front-end GUI applications. It is not intended for server-side database drivers (like PostgreSQL or MySQL) but provides advanced features like migrations, database observation, and multi-threading safety specifically for client-side needs.

To start using GRDB, follow these four steps:

1. Open a database connection: Use DatabaseQueue to connect to a SQLite file.
2. Define the database schema: Use db.create(table:) within a write block to set up your tables.
3. Define a record type: Create a Swift struct that conforms to Codable, FetchableRecord, and PersistableRecord to map database rows to Swift objects.
4. Write and read data: Use dbQueue.write for modifications (like insert) and dbQueue.read for fetching data (like fetchAll).

import GRDB

// 1. Open a database connection
let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")

// 2. Define the database schema
try dbQueue.write { db in
    try db.create(table: "player") { t in
        t.primaryKey("id", .text)
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull()
    }
}

// 3. Define a record type
struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var name: String
    var score: Int
}

// 4. Write and read in the database
try dbQueue.write { db in
    try Player(id: "1", name: "Arthur", score: 100).insert(db)
    try Player(id: "2", name: "Barbara", score: 1000).insert(db)
}

let players: [Player] = try dbQueue.read { db in
    try Player.fetchAll(db)
}

To start using GRDB, follow these four steps:

1. Open a database connection using DatabaseQueue.
2. Define your database schema using db.create(table:...) within a write block.
3. Define a record type by conforming a struct or class to FetchableRecord and PersistableRecord.
4. Perform writes and reads using dbQueue.write and dbQueue.read blocks.

import GRDB

// 1. Open a database connection
let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")

// 2. Define the database schema
try dbQueue.write { db in
    try db.create(table: "player") { t in
        t.primaryKey("id", .text)
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull()
    }
}

// 3. Define a record type
struct Player: Codable, Identifiable, FetchableRecord, PersistableRecord {
    var id: String
    var name: String
    var score: Int
    
    enum Columns {
        static let name = Column(CodingKeys.name)
        static let score = Column(CodingKeys.score)
    }
}

// 4. Write and read in the database
try dbQueue.write { db in
    try Player(id: "1", name: "Arthur", score: 100).insert(db)
    try Player(id: "2", name: "Barbara", score: 1000).insert(db)
}

try dbQueue.read { db in
    let player = try Player.find(db, id: "1")
    
    let bestPlayers = try Player
        .order(\.score.desc)
        .limit(10)
        .fetchAll(db)
}

When a Codable record contains a property that is not a simple value (like an array, dictionary, or nested struct), GRDB automatically encodes and decodes it as a JSON string in the database.

To customize the JSON format, implement the following protocol requirements:

• static func databaseJSONDecoder(for column: String) -> JSONDecoder in FetchableRecord
• static func databaseJSONEncoder(for column: String) -> JSONEncoder in EncodableRecord

Important: Always set the sortedKeys option on your JSONEncoder to ensure stable JSON output. This is required for Record Comparison and ValueObservation to work correctly.

enum AchievementColor: String, Codable {
    case bronze, silver, gold
}

struct Achievement: Codable {
    var name: String
    var color: AchievementColor
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var name: String
    var score: Int
    var achievements: [Achievement] // stored in a JSON column
}

try dbQueue.write { db in
    let achievement = Achievement(name: "Use Codable Records", color: .gold)
    let player = Player(name: "Arthur", score: 100, achievements: [achievement])
    try player.insert(db)
}