Learning iOS GRDB

A deep, hands-on guide to GRDB — a Swift toolkit for SQLite — used to build serious data layers on iOS. We’ll work through the framework end to end: setting up DatabaseQueue and DatabasePool, designing schema with migrations, mapping rows to Swift types via FetchableRecord and PersistableRecord, the type-safe query interface, associations and eager loading, observation with ValueObservation, full-text search, the concurrency model, performance tuning, and SwiftUI integration in both the convenient and the testable styles. iOS-specific examples throughout, with attention to the patterns that hold up in production.

GRDB is different from Core Data and SwiftData. It’s a thin, Swift-idiomatic wrapper around SQLite — you write SQL when you want to, and use a type-safe query interface when you don’t. There’s no object graph manager, no managed contexts, no schema in .xcdatamodeld, no implicit faulting. Records are plain Swift types (often structs) that you fetch, mutate, and save explicitly. This trades some convenience for a great deal of control, transparency, and testability.

If you’ve used Core Data, you’ll find GRDB simpler in many ways and more demanding in others. SQL is back in your face — which is wonderful when you need it, and a small tax when you don’t. If you’ve never written SQL, this guide will teach you enough of it to be productive.

Table of Contents

1. What GRDB Is (and Why You’d Use It)
2. Installation and Project Setup
3. DatabaseQueue and DatabasePool: The Two Database Types
4. Your First Tables: Schema with Migrations
5. CRUD with Raw SQL
6. Records: Mapping Rows to Swift Types
7. The Codable Bridge
8. Database Migrations in Depth
9. Reads and Writes: Sync, Async, Throwing
10. Transactions and Save Points
11. The Query Interface
12. Filtering with Column and SQLExpression
13. Sorting, Limits, and Aggregation
14. Associations: belongsTo, hasMany, hasOne
15. Eager Loading: including, joining, having
16. Saving Records: insert, update, save, upsert
17. Conflict Resolution and onConflict
18. Indexes: Single, Composite, Partial, Expression
19. Full-Text Search with FTS5
20. Triggers, Views, and Generated Columns
21. Database Observation: ValueObservation
22. Combine Integration
23. AsyncSequence and Swift Concurrency
24. SwiftUI Integration: GRDBQuery
25. SwiftUI Without GRDBQuery: @Observable Stores
26. SwiftUI Without GRDBQuery: View Model + AsyncSequence
27. The Repository Pattern with GRDB
28. Concurrency Model: WAL, Readers, and the Writer
29. Performance: Statement Caching, Batches, Profiling
30. Encryption with SQLCipher
31. Common Gotchas and Anti-Patterns
32. Where to Go Deeper

1. What GRDB Is (and Why You’d Use It)

GRDB is a Swift wrapper around SQLite, written by Gwendal Roué and used in production by many serious iOS apps. It’s not a singleton you import; it’s an active project that’s evolved alongside Swift itself, embracing structured concurrency, Combine, SwiftUI integration, and modern Swift idioms while staying close to SQLite — close enough that you can drop into raw SQL whenever you need to.

The single most important sentence to internalize: GRDB is a SQLite library for Swift, not an ORM. It gives you a Swift-idiomatic API for talking to a SQLite database, with optional layers (records, query interface, associations, observation) that make common tasks ergonomic. But under the hood, it’s still SQLite — your tables are real tables, your queries are real SQL, your performance characteristics are SQLite’s performance characteristics.

This positions GRDB very differently from Core Data and SwiftData, which are object graph managers that happen to use SQLite as a backing store. GRDB doesn’t manage an object graph. It doesn’t fault. It doesn’t track changes implicitly. It doesn’t unify objects across queries. When you fetch, you get fresh values. When you save, you write to the database. The mental model is closer to “I have a SQL database; here’s a nice API for it.”

The shape of GRDB

A short tour to set expectations:

• A database is a file. Open it with DatabaseQueue (single-writer, single-reader-at-a-time) or DatabasePool (single-writer, many-readers).
• Tables are defined via migrations. You write a migration that creates tables; GRDB runs migrations once and tracks which have been applied.
• Records are plain Swift types. Make a struct Player that conforms to Codable, FetchableRecord, PersistableRecord — and now Player can be fetched from and saved to the database.
• Queries can be SQL or type-safe. Player.fetchAll(db, sql: "SELECT * FROM player") works. So does Player.order(Column("score").desc).limit(10).fetchAll(db).
• Observation is explicit. Subscribe to a ValueObservation and you get a stream of fresh values whenever the underlying data changes. This works with Combine and AsyncSequence.

That’s the framework. Everything else builds on these primitives.

Comparing to Core Data and SwiftData

Where they differ, in shape:

There’s no “right answer.” Each framework suits different problems:

• Core Data / SwiftData shine when your app’s natural shape is an object graph — entities with relationships you traverse, where keeping a single source of truth across views matters more than fine query control.
• GRDB shines when you think relationally — you want to write the queries you’d write in any database, you want fine control over schema and indexes, you want to test things easily, you want to see what’s happening under the hood.

The choice is partly about cognitive style. If “an order has many line items” feels naturally like “a join between orders and line_items”, GRDB will feel like home. If it feels naturally like “an Order object holds a relationship to LineItem objects”, Core Data will.

When GRDB fits well

• You want full control over schema and SQL. GRDB doesn’t hide it; you can write any SQL you want.
• You need cross-platform sync (server + iOS). SQLite is everywhere; GRDB doesn’t trap you in Apple’s ecosystem.
• You want testability. In-memory databases are trivial; everything is a value type; mocking is straightforward.
• You need performance characteristics you can profile and reason about. SQL → SQLite, no implicit layer adding overhead.
• You’re comfortable (or want to be comfortable) with SQL. The framework rewards SQL knowledge.
• You need full-text search. SQLite’s FTS5 is excellent; GRDB exposes it cleanly.
• You want a thin, deterministic library. GRDB is small enough that you can read its source and understand what’s happening.

When GRDB doesn’t fit

• You want CloudKit sync with one line of code. GRDB doesn’t have a built-in CloudKit story. You can build sync on top of it, but you’re writing the sync code.
• You want SwiftUI to “just work” with auto-updating views and zero boilerplate. @Query with SwiftData is shorter. GRDB needs more wiring (we’ll cover the patterns).
• You want to model an object graph as objects, not tables. GRDB’s records are values, not graph nodes; the graph mental model maps less cleanly.
• You need automatic schema migration with a UI editor. GRDB migrations are SQL/code; there’s no visual designer.
• Your team strongly prefers an ORM. GRDB occupies a position close to the database, which some find too low-level.

What you’ll learn from this guide

By the end:

• The two database types and how to choose between them.
• Schema design via migrations, including indexes and FTS5.
• Records: how to make Swift types fetchable and persistable.
• The query interface for type-safe queries, and when to fall back to SQL.
• Associations and how to avoid N+1 with eager loading.
• The concurrency model and using it without footguns.
• Observation: how to get UI to update automatically as data changes.
• SwiftUI integration in both quick-and-easy and testable-architecture styles.
• Performance tuning and profiling.
• The patterns that hold up in production.

A note on philosophy

GRDB embraces what SQLite is — a fast, reliable, embedded database with sophisticated features (FTS5, virtual tables, partial indexes, ATTACH, etc.). GRDB doesn’t try to hide SQLite; it exposes it well. This means you’ll learn SQLite as you learn GRDB. That’s a feature, not a bug — SQLite knowledge transfers everywhere, including to non-iOS contexts.

If you’ve spent years fighting Core Data’s opacity, GRDB will feel refreshing. If you’ve spent years writing SQL by hand, GRDB will feel like a thoughtful Swift wrapper that doesn’t get in your way.

What to internalize

GRDB is a SQLite library, not an ORM. Records are values, not graph nodes. Schema lives in migration code. Queries can be raw SQL or type-safe. Observation is explicit via ValueObservation. Choose GRDB when you want control, transparency, and SQL fluency. Choose Core Data or SwiftData when you want an object graph manager.

2. Installation and Project Setup

GRDB ships as a Swift Package. Adding it to your project takes a minute. The interesting decisions come right after — where to put your database file, how to organize your data layer, and how to test it.

Adding GRDB via Swift Package Manager

In Xcode: File → Add Package Dependencies → enter the URL https://github.com/groue/GRDB.swift. Pick the latest stable major version. Add the GRDB library to your app target.

In a Package.swift:

dependencies: [
    .package(url: "https://github.com/groue/GRDB.swift", from: "6.0.0")
]

Then in your target:

.product(name: "GRDB", package: "GRDB.swift")

(Version numbers evolve. Check the GitHub repo for the current major version.)

For SQLCipher (encrypted databases), there’s a separate product GRDB-SQLCipher. We’ll cover encryption in section 30.

Where to put the database file

iOS apps have several writable directories. The right choice depends on what the database stores:

• Application Support directory (Library/Application Support/). For data the user creates that should persist across app updates. Not backed up to iCloud/iTunes by default unless you opt in. The most common choice for app data.
• Documents directory (Documents/). For user-visible documents. Backed up to iCloud/iTunes, visible in Files.app if you enable file sharing. Use only when the database file is itself a “document” the user might see.
• Caches directory (Library/Caches/). For data that can be regenerated. The system may delete this under storage pressure. Use for cached server data.
• tmp/. For ephemeral data; deleted between launches.

For most apps, Application Support is right. Set it up:

import Foundation

extension URL {
    static var applicationSupportDirectory: URL {
        try! FileManager.default.url(
            for: .applicationSupportDirectory,
            in: .userDomainMask,
            appropriateFor: nil,
            create: true
        )
    }

    static var defaultDatabaseURL: URL {
        applicationSupportDirectory.appendingPathComponent("database.sqlite")
    }
}

The create: true ensures the directory exists. The trailing filename is yours to pick — database.sqlite is fine; some apps use <AppName>.sqlite so it’s recognizable in Finder when debugging the simulator.

A first database setup

A complete minimal setup:

import GRDB
import Foundation

final class AppDatabase {
    let dbWriter: any DatabaseWriter

    init(_ dbWriter: any DatabaseWriter) throws {
        self.dbWriter = dbWriter
        try migrator.migrate(dbWriter)
    }

    private var migrator: DatabaseMigrator {
        var migrator = DatabaseMigrator()

        migrator.registerMigration("createPlayer") { db in
            try db.create(table: "player") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("name", .text).notNull()
                t.column("score", .integer).notNull()
            }
        }

        return migrator
    }
}

extension AppDatabase {
    static func makeShared() -> AppDatabase {
        do {
            let url = URL.defaultDatabaseURL
            let dbPool = try DatabasePool(path: url.path)
            return try AppDatabase(dbPool)
        } catch {
            fatalError("Database setup failed: \(error)")
        }
    }
}

What’s happening:

• AppDatabase wraps a DatabaseWriter (the abstract type covering both DatabaseQueue and DatabasePool). We’ll cover both in section 3.
• migrator.migrate(dbWriter) runs any pending migrations at startup, in order.
• The migration creates a player table with three columns.
• The makeShared factory builds the database at the conventional location and returns the configured wrapper.

You’d typically construct this once at app launch:

@main
struct PlayersApp: App {
    let database = AppDatabase.makeShared()

    var body: some Scene {
        WindowGroup {
            ContentView()
                .environment(\.appDatabase, database)
        }
    }
}

The .environment(\.appDatabase, database) requires you to define an environment key — covered in section 24 when we discuss SwiftUI integration. For now, treat it as injection.

Database lifecycle

GRDB databases are heavyweight enough that you create them once at app launch, not on demand. Reasons:

• Opening the SQLite file involves I/O and migration checks.
• A DatabasePool allocates connection-pool resources.
• Sharing one connection across the app reduces contention.

It’s perfectly reasonable to make AppDatabase a singleton-ish — created at launch, passed around. Avoid lazy-on-first-use patterns for the database itself.

Testing setup

For tests, you want an in-memory database — fast, isolated, no on-disk pollution:

extension AppDatabase {
    static func makeForTests() throws -> AppDatabase {
        let dbQueue = try DatabaseQueue()
        return try AppDatabase(dbQueue)
    }
}

DatabaseQueue() with no arguments opens an in-memory database. It runs the same migrations as the production database, so your tests exercise real schema. Each test gets a fresh AppDatabase instance — no state bleeds between tests.

In test code:

final class PlayerTests: XCTestCase {
    var database: AppDatabase!

    override func setUp() async throws {
        database = try AppDatabase.makeForTests()
    }

    func test_insertPlayer() async throws {
        try await database.dbWriter.write { db in
            try db.execute(sql: "INSERT INTO player (name, score) VALUES (?, ?)",
                           arguments: ["Arthur", 100])
        }

        let count = try await database.dbWriter.read { db in
            try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player") ?? 0
        }
        XCTAssertEqual(count, 1)
    }
}

Each test runs in milliseconds. The schema is real. The tests exercise the same migrations that run in production — catching schema bugs early.

Inspecting the database file

When debugging, you’ll often want to see what’s in the file. Three approaches:

• In the simulator, use Xcode’s “Console” (Window → Devices and Simulators → select sim → “Open Console”). The simulator’s app container is at: ~/Library/Developer/CoreSimulator/Devices/<device-uuid>/data/Containers/Data/Application/<app-uuid>/Library/Application Support/database.sqlite
• Open with a SQLite browser app like DB Browser for SQLite or the sqlite3 command-line tool.
• During development, log queries with GRDB’s Configuration.publicStatementArguments: swift var config = Configuration() config.publicStatementArguments = true // DEBUG only let dbPool = try DatabasePool(path: url.path, configuration: config) This logs SQL with arguments substituted, useful for “what query is GRDB actually running?” debugging.

The -shm and -wal files alongside database.sqlite are part of SQLite’s write-ahead log. They’re normal; don’t delete them while the app is running.

File protection on iOS

By default, files in Application Support are protected with NSFileProtectionCompleteUntilFirstUserAuthentication. This means the file is readable only after the user has unlocked the device since boot. After unlock, the file is accessible — even when the device is locked again — until reboot.

If you store sensitive data, you can request stronger protection:

var config = Configuration()
config.observesSuspensionNotifications = true
let dbPool = try DatabasePool(path: url.path, configuration: config)

// Then, separately, set the file protection level
try (url as NSURL).setResourceValue(
    URLFileProtection.complete,
    forKey: .fileProtectionKey
)

URLFileProtection.complete means the file is unreadable while the device is locked. Combined with observesSuspensionNotifications, GRDB releases its connections on suspension, which prevents corruption when the file becomes unavailable.

Concurrency target

GRDB works with Swift Concurrency. As of recent versions:

• DatabaseQueue.write { db in ... } is sync.
• DatabaseQueue.asyncWrite { ... } and DatabasePool.read { db in ... } etc. have async forms.
• All database access is done via closures that receive a Database parameter.

We’ll cover the access patterns in detail in sections 3 and 9.

Getting the version right

GRDB has had several major versions. As of writing, GRDB 6+ is the modern stable line. Use the latest. Older GRDB 4.x has different APIs in places; tutorials and forum posts about GRDB 4 may not match what you see in current code.

What to internalize

Add GRDB via SPM. Put your database file in Application Support. Wrap it in an AppDatabase type that holds a DatabaseWriter and runs migrations at init. Build at app launch, share via injection. Use in-memory DatabaseQueue() for tests. Inspect files via simulator paths or a SQLite browser. Set file protection if you store sensitive data.

3. DatabaseQueue and DatabasePool: The Two Database Types

GRDB exposes two ways to access a SQLite database: DatabaseQueue and DatabasePool. They differ in concurrency characteristics, and choosing between them affects how your app performs under load. For most iOS apps, the answer is DatabasePool — but understanding why matters.

The fundamental distinction

SQLite supports concurrent reads but serializes writes. The two database types expose this differently:

• DatabaseQueue — a single connection to the database, used for both reads and writes. Operations are serialized on a queue. Simple, predictable, no concurrent reads.
• DatabasePool — one writer connection plus a pool of reader connections. Writes serialize through the writer; reads can happen concurrently from any reader. Fast for read-heavy workloads.

If you’re not sure which to pick: DatabasePool is almost always the right answer for an iOS app with any meaningful database work. The exception is read-heavy code that has very low write rates and trivially small data — DatabaseQueue is slightly simpler and uses fewer resources.

Setting up a DatabaseQueue

import GRDB

let dbQueue = try DatabaseQueue(path: "/path/to/database.sqlite")

Or in-memory:

let dbQueue = try DatabaseQueue()

You read and write through closures:

try dbQueue.write { db in
    try db.execute(sql: "INSERT INTO player (name, score) VALUES (?, ?)",
                   arguments: ["Alice", 100])
}

let count = try dbQueue.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player") ?? 0
}

The closure receives a Database instance — the actual SQLite connection. You issue queries through it.

write and read block the calling thread until the closure completes. They’re synchronous. Async versions exist:

try await dbQueue.write { db in /* ... */ }
let count = try await dbQueue.read { db in /* ... */ }

The async versions don’t block the calling thread; they suspend until the operation runs on GRDB’s internal queue.

Setting up a DatabasePool

let dbPool = try DatabasePool(path: "/path/to/database.sqlite")

The API is the same as DatabaseQueue:

try dbPool.write { db in /* ... */ }
let result = try dbPool.read { db in /* ... */ }

What’s different: dbPool.read calls can happen concurrently. If you have ten read operations queued, multiple run in parallel on different reader connections. Writes still serialize through the single writer.

The DatabaseWriter protocol

DatabaseQueue and DatabasePool both conform to DatabaseWriter. Code that just needs “something I can read and write to” should accept any DatabaseWriter:

final class PlayerService {
    let dbWriter: any DatabaseWriter

    init(_ dbWriter: any DatabaseWriter) {
        self.dbWriter = dbWriter
    }

    func savePlayer(_ player: Player) async throws {
        try await dbWriter.write { db in
            try player.save(db)
        }
    }
}

In production, you instantiate with DatabasePool. In tests, with DatabaseQueue (in-memory). The service code is identical.

There’s also DatabaseReader — both types conform — for code that only needs reads. Less commonly used because most data layers do both.

Why concurrent reads matter

iOS apps often have a usage pattern like “many small reads from the UI, occasional writes from user actions or sync.” With DatabaseQueue, each read serializes — if the UI thread is reading while a background sync is also reading, one waits. With DatabasePool, both proceed in parallel.

Concrete example: a list view shows 100 cells. Each cell asynchronously fetches some data. With DatabaseQueue, those fetches happen one at a time. With DatabasePool, they can run on concurrent reader connections (up to a limit, configurable).

For modern iOS apps with reactive UIs, the parallelism matters. Use DatabasePool unless you have a reason not to.

Configuration

Both database types take a Configuration to customize behavior:

var config = Configuration()
config.label = "MainDatabase"  // for debugging
config.maximumReaderCount = 5   // pool only — number of concurrent readers (default: 5)
config.busyMode = .timeout(10)  // wait up to 10 seconds when locked
config.qos = .userInitiated     // QoS for GRDB's internal queues

let dbPool = try DatabasePool(path: path, configuration: config)

Useful options:

• label — appears in console logs and Instruments traces; helps when you have multiple databases.
• maximumReaderCount — for pools, how many reader connections to keep. More readers means more parallelism, more memory. Default 5 is reasonable for most apps.
• busyMode — what to do when the database is locked by a writer. Default is to retry up to a small budget. .timeout(seconds) waits longer.
• qos — quality-of-service for GRDB’s internal serialization queues. Higher QoS for app-critical data, lower for background-only.

Foreign keys

SQLite has foreign keys but doesn’t enforce them by default. Always enable enforcement:

var config = Configuration()
config.foreignKeysEnabled = true  // default in GRDB; explicit for clarity

let dbPool = try DatabasePool(path: path, configuration: config)

GRDB enables foreign keys by default in recent versions. You almost always want this on. Without it, you can have orphaned rows whose foreign keys point to nonexistent records.

Database lifecycle and the WAL

When you open a DatabasePool, GRDB switches the database to WAL (write-ahead log) mode. This is what enables concurrent reads. The mode persists in the database file — once a file is in WAL mode, it stays that way until explicitly converted back.

WAL mode produces three files instead of one:

• database.sqlite — the main database.
• database.sqlite-wal — the write-ahead log.
• database.sqlite-shm — shared-memory file (a small index used coordinating WAL access).

If you’re packaging the database, copying it, or backing it up, all three files are needed. The -wal file in particular can be substantial — up to several MB during heavy write activity. SQLite checkpoints the WAL into the main file periodically.

When to use DatabaseQueue

A few scenarios where DatabaseQueue is the right pick:

• Embedded read-only data that ships with the app (e.g., a reference table). Read-only access; concurrency doesn’t matter; queue is simpler.
• Tests in many cases — DatabaseQueue() is the in-memory form. (Pools also support in-memory but queues are usually cleaner.)
• Apps with extremely small data and almost no concurrency — a settings store, a tiny cache. The pool is overkill; the queue’s simplicity is fine.

Otherwise, default to DatabasePool.

Backup and migration considerations

For backups:

let backupPath = "/path/to/backup.sqlite"
try dbPool.backup(to: backupPath)

This creates a fresh, consistent snapshot of the database. The original keeps running.

For migrations between database versions, write code in your migrator (covered in section 8). You don’t manipulate the database type itself.

Multiple databases

You can open multiple databases in one app — for example, a “user data” pool and a “cache” pool:

let userPool = try DatabasePool(path: userDataURL.path)
let cachePool = try DatabasePool(path: cachesURL.path)

Each is independent. Code accessing user data uses userPool.read/write; code accessing cache uses cachePool.read/write.

You can also ATTACH databases together for cross-database queries, but it’s rare and we won’t go deep here. SQLite’s documentation has details.

Closing the database

Database connections close automatically when the type is deallocated. For most apps, this means at app termination — fine, no action needed. If you do need to explicitly close:

// DatabasePool can be released by setting the variable to nil, given no closures are running.
// For more controlled shutdown, see GRDB's documentation.

In practice, you don’t manually close. The OS reaps the file handles when the process exits.

Pitfalls

Holding Database references outside the closure. The Database parameter passed to your closure is valid only inside the closure. Don’t capture and use it later — it’s bound to the GRDB queue.

Wrapping reads inside writes. dbPool.write { db in ... } inside a dbPool.read { db in ... } deadlocks. The write needs the writer; the read holds a reader; they can interfere. Don’t nest different access types.

Multiple writes from concurrent tasks. Concurrent writes are serialized. If you await dbPool.write from many tasks at once, they execute one after another — fine, just expect serial behavior.

Forgetting to enable foreign keys. While GRDB defaults to on, double-check. Explicitly setting it makes intent clear to other developers.

Choosing DatabaseQueue out of conservatism. “I don’t need the parallelism” is often wrong — you don’t realize you need it until your UI starts hitching. Default to pool.

What to internalize

DatabaseQueue is single-connection, serialized. DatabasePool has one writer + multiple readers, parallelism for reads. For iOS apps with active UIs, default to DatabasePool. Both implement DatabaseWriter, so service code is type-agnostic. Pools enable WAL mode; expect three database files. Configure label, foreign keys, busy mode, QoS. Don’t escape Database references from closures. Don’t nest reads in writes or vice versa.

4. Your First Tables: Schema with Migrations

In GRDB, your schema lives in code. There’s no .xcdatamodeld editor and no @Model macro — you write migrations that run SQL statements (or call GRDB’s schema-builder DSL) in a deterministic order. Each migration is registered with a name, runs once, and GRDB tracks which migrations have been applied.

This is more code than visual schema editors but more transparent. You can read your migrations top-to-bottom and see exactly how the schema evolved.

What a migration looks like

A migration is a closure that takes a Database and modifies the schema:

import GRDB

var migrator = DatabaseMigrator()

migrator.registerMigration("createPlayer") { db in
    try db.create(table: "player") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
        t.column("score", .integer).notNull().defaults(to: 0)
        t.column("createdAt", .datetime).notNull()
    }
}

registerMigration("name", body) registers a named migration. The name should be stable — once shipped, never rename a migration, because GRDB stores names in a special table to know which have run.

The closure receives a Database. You call methods on it that issue SQL: db.create(table:), db.alter(table:), db.create(index:), db.execute(sql:), etc.

Running migrations

You apply migrations once, at app startup:

final class AppDatabase {
    let dbWriter: any DatabaseWriter

    init(_ dbWriter: any DatabaseWriter) throws {
        self.dbWriter = dbWriter
        try migrator.migrate(dbWriter)
    }

    private var migrator: DatabaseMigrator {
        var migrator = DatabaseMigrator()

        migrator.registerMigration("createPlayer") { db in
            // ... as above
        }

        // More migrations registered here...

        return migrator
    }
}

migrator.migrate(dbWriter) is synchronous and idempotent. On first launch with an empty database, it runs every migration. On subsequent launches, it runs only migrations that haven’t run before. If a migration fails, the whole migrate call throws, and your app should treat this as fatal.

Migrations are wrapped in transactions automatically — if a migration’s body throws partway through, the partial changes are rolled back, the migration is not recorded as applied, and migrate throws. The next launch will retry.

The schema-builder DSL

db.create(table:) accepts a closure that builds the table:

try db.create(table: "player") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
    t.column("score", .integer).notNull().defaults(to: 0)
    t.column("createdAt", .datetime).notNull()
    t.column("teamId", .integer)
        .references("team", onDelete: .cascade)
}

Each t.column(...) declares a column. The first argument is the name; the second is the type affinity (.text, .integer, .real, .blob, .datetime, .numeric, .boolean, .date).

Column modifiers chain:

• .notNull() — required.
• .unique() — unique constraint.
• .defaults(to: value) — default for the column.
• .indexed() — create an index on this column (single-column).
• .collate(.nocase) — collation, e.g., for case-insensitive sorting.
• .references("table", onDelete: .cascade) — foreign key.
• .check { $0 >= 0 } — CHECK constraint.

For primary keys:

• t.autoIncrementedPrimaryKey("id") — INTEGER PRIMARY KEY AUTOINCREMENT.
• t.primaryKey("id", .text) — primary key on a specific column.
• t.primaryKey(["columnA", "columnB"]) — composite primary key.

For multi-column constraints:

• t.uniqueKey(["columnA", "columnB"]) — composite unique constraint.
• t.foreignKey(["columnA"], references: "team", onDelete: .cascade) — composite foreign key.

A more complete schema

A notes app:

migrator.registerMigration("createSchema") { db in
    try db.create(table: "folder") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull()
        t.column("createdAt", .datetime).notNull()
    }

    try db.create(table: "tag") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull().unique()
    }

    try db.create(table: "note") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text).notNull()
        t.column("body", .text).notNull().defaults(to: "")
        t.column("createdAt", .datetime).notNull()
        t.column("updatedAt", .datetime).notNull()
        t.column("folderId", .integer)
            .references("folder", onDelete: .cascade)
            .indexed()
    }

    // Many-to-many between note and tag
    try db.create(table: "noteTag") { t in
        t.column("noteId", .integer)
            .notNull()
            .references("note", onDelete: .cascade)
        t.column("tagId", .integer)
            .notNull()
            .references("tag", onDelete: .cascade)
        t.primaryKey(["noteId", "tagId"])
    }

    try db.create(index: "noteByUpdatedAt", on: "note", columns: ["updatedAt"])
}

Read top-to-bottom: folders, tags, notes (referencing folders), and a join table for many-to-many notes↔tags. Foreign keys cascade on delete. An index on updatedAt for time-ordered queries.

This is one migration registering a complete schema. As you evolve the app, you’ll add more migrations.

Type affinity

SQLite has a flexible type system. The type: parameter on columns is a type affinity — it suggests what kind of values to expect, but SQLite doesn’t strictly enforce it.

Common affinities and what they mean in practice:

• .text — strings.
• .integer — integers (Int, Int64, Bool stored as 0/1).
• .real — floating-point (Double, Float).
• .blob — Data blobs.
• .numeric — anything; SQLite picks the best representation.
• .datetime — dates. SQLite stores them as text (ISO 8601) or numeric (Unix timestamps). GRDB defaults to ISO 8601 strings.
• .boolean — booleans. Stored as 0/1.
• .date — date-only.

Pick the affinity that matches the Swift type you’ll use. For dates, .datetime is the standard.

Naming conventions

A few conventions worth adopting:

• Lowercase, singular table names: player, not Players or players. (Some teams use plural; pick one and stick to it.)
• camelCase column names: createdAt, not created_at. This matches Swift property names directly when you use Codable records.
• Foreign keys named after the referenced table: folderId references folder.id.
• Indexes named with intent: noteByUpdatedAt indicates “an index on note for sorting/filtering by updatedAt.”

Conventions don’t matter to SQLite. They matter to your future self.

Check before alter

For a brand-new database, you write the schema as one big create migration. As your app evolves, schema changes go in new migrations:

migrator.registerMigration("v1.createSchema") { db in
    // initial schema
}

migrator.registerMigration("v1.1.addArchivedToNote") { db in
    try db.alter(table: "note") { t in
        t.add(column: "archived", .boolean).notNull().defaults(to: false)
    }
}

migrator.registerMigration("v1.2.addTagColor") { db in
    try db.alter(table: "tag") { t in
        t.add(column: "color", .text).notNull().defaults(to: "blue")
    }
}

Each is a separate migration. Each has a stable name. Once shipped, never modify a previous migration — modify forward.

The naming pattern v<release>.<intent> is helpful for tracking when a migration was added; “v1.0” through “v1.5” makes the chronology obvious. Other teams use timestamps (20240115_addArchived) or sequential numbers. Pick one.

eraseDatabaseOnSchemaChange — for development

During early development, you may iterate on the schema rapidly. Renaming a migration breaks GRDB’s tracking and causes errors. To make iteration easier:

migrator.eraseDatabaseOnSchemaChange = true

With this flag, if the migrator detects a schema mismatch (your migrations changed names or content in ways incompatible with what’s recorded), GRDB erases the database and re-applies all migrations from scratch.

Use this flag only during development. In production, you must not erase user data on schema mismatches. Ship with eraseDatabaseOnSchemaChange = false (the default).

A common pattern:

private var migrator: DatabaseMigrator {
    var migrator = DatabaseMigrator()

    #if DEBUG
    migrator.eraseDatabaseOnSchemaChange = true
    #endif

    // register migrations...

    return migrator
}

Running migrations transactionally

Each registered migration runs inside a transaction by default. If the migration body throws, the transaction rolls back, the migration is not marked as applied, and the next launch will retry.

You can opt out of transactions for migrations that need it (rare):

migrator.registerMigration("longRunningMigration", foreignKeyChecks: .deferred) { db in
    // For migrations that need to defer foreign key checks
}

The foreignKeyChecks option controls whether foreign keys are deferred during the migration — useful for restructuring schemas where temporary inconsistency is unavoidable.

Inspecting applied migrations

Useful for debugging:

try dbWriter.read { db in
    let applied = try migrator.appliedMigrations(db)
    print("Applied migrations: \(applied)")
}

This returns the names of migrations that have run on this database. If your app launches and you’re confused about what state the schema is in, dump this list.

Common mistakes

Renaming a migration after shipping. GRDB tracks names; renaming breaks tracking. Existing users’ databases will be in a confused state.

Modifying a previous migration’s body after shipping. Same issue. The old migration ran with old logic; new launches don’t re-run it. Modify forward instead — write a new migration that fixes whatever was wrong.

Schema changes outside migrations. If you do a db.execute("CREATE TABLE...") outside a migration, the table exists but GRDB doesn’t know about it for migration tracking. Always go through migrations.

Migrations that depend on app state. Migrations should be pure schema operations; they shouldn’t fetch user data from the network or read app preferences. If you need post-migration data tasks, run them separately at app launch.

Forgetting to call migrate. Without migrator.migrate(dbWriter), no migrations run, your tables don’t exist, and queries fail.

What to internalize

Schema lives in migration code. Each migration has a stable name and runs once. migrator.migrate(dbWriter) at app startup applies pending migrations idempotently. Use the schema DSL (db.create(table:)) for clarity. Use eraseDatabaseOnSchemaChange = true during development, off in production. Once a migration ships, never modify it — only add new ones forward.

5. CRUD with Raw SQL

GRDB’s raw-SQL API is the foundation. Even if you mostly use records and the query interface, knowing raw SQL CRUD makes you understand what’s happening underneath. And for one-off operations, ad-hoc queries, or patterns the higher layers don’t express well, raw SQL is the right tool.

Executing a SQL statement

The most basic write:

try dbPool.write { db in
    try db.execute(sql: """
        INSERT INTO player (name, score, createdAt)
        VALUES (?, ?, ?)
        """, arguments: ["Arthur", 100, Date()])
}

db.execute(sql:arguments:) runs a SQL statement. Arguments are passed via arguments: — never interpolate values into the SQL string itself. The ? placeholders are bound to the arguments in order, with proper escaping that prevents SQL injection.

For multiple statements:

try db.execute(sql: """
    INSERT INTO player (name, score) VALUES ('Alice', 100);
    INSERT INTO player (name, score) VALUES ('Bob', 80);
    """)

When passing multiple statements as a string, you can’t use ? placeholders — the whole string is parsed and run as-is. For parameterized inserts, use a single statement and run it multiple times.

Reading: fetching values

try dbPool.read { db in
    let count = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player") ?? 0
    print("Player count: \(count)")
}

Int.fetchOne(db, sql:) runs a query and returns the first column of the first row as an Int?. Many basic types support fetchOne, fetchAll, fetchCursor:

• Int.fetchOne(db, sql:) → Int?
• Int.fetchAll(db, sql:) → [Int]
• String.fetchAll(db, sql: "SELECT name FROM player") → [String]
• Double.fetchAll(db, sql:) → [Double]
• Date.fetchAll(db, sql:) → [Date]
• Data.fetchAll(db, sql:) → [Data]

For full rows:

let rows = try Row.fetchAll(db, sql: "SELECT * FROM player ORDER BY score DESC")
for row in rows {
    let name: String = row["name"]
    let score: Int = row["score"]
    print("\(name): \(score)")
}

Row is a dictionary-like type indexed by column name. Subscripting returns an inferred type via type-coercion. If a column doesn’t exist, you get nil; if it does but the value is null, you get nil (when subscripting as an optional) or a crash (when subscripting as non-optional).

For typed access:

let name = row["name"] as String
let score = row["score"] as Int
let createdAt = row["createdAt"] as Date

Arguments

GRDB supports two argument styles:

Positional (?):

try db.execute(sql: "INSERT INTO player (name, score) VALUES (?, ?)",
               arguments: ["Alice", 100])

Arguments fill placeholders in order.

Named (:name):

try db.execute(sql: """
    INSERT INTO player (name, score) VALUES (:name, :score)
    """, arguments: ["name": "Alice", "score": 100])

Named arguments make complex statements more readable. The keys in the dictionary match the names in the SQL.

You can mix both in different statements but not in the same statement.

Updating

Update is just SQL:

try db.execute(sql: """
    UPDATE player SET score = ? WHERE id = ?
    """, arguments: [200, playerId])

To know how many rows were affected:

try db.execute(sql: "UPDATE player SET score = score + 10 WHERE score < 100")
let count = db.changesCount
print("\(count) rows updated")

db.changesCount returns the number of rows affected by the last execute. Useful for “did we actually update anything?” checks.

Deleting

try db.execute(sql: "DELETE FROM player WHERE id = ?", arguments: [playerId])
let deletedCount = db.changesCount

To delete all:

try db.execute(sql: "DELETE FROM player")

Note that DELETE FROM table is not the same as DROP TABLE table — the table still exists, just empty. Schema operations should always go through migrations.

Last inserted row ID

After an INSERT INTO ... AUTOINCREMENT table:

try db.execute(sql: "INSERT INTO player (name, score) VALUES (?, ?)",
               arguments: ["Alice", 100])
let id = db.lastInsertedRowID
print("New player has id \(id)")

lastInsertedRowID is the auto-incremented id of the row just inserted. Useful when you need to know the new row’s ID for subsequent queries.

Cursors

For fetching many rows without loading them all at once:

try dbPool.read { db in
    let cursor = try Row.fetchCursor(db, sql: "SELECT * FROM player")
    while let row = try cursor.next() {
        let name: String = row["name"]
        // process one row at a time
    }
}

A cursor reads rows lazily. For 10,000-row queries where you process and discard, this avoids buffering 10,000 rows in memory. For typical fetches, just use fetchAll.

Cursors are not Sequence-conforming because their iteration can throw. Use the while let pattern.

Querying by columns

Sometimes you want a single column from many rows:

let names = try String.fetchAll(db, sql: "SELECT name FROM player")
// names: [String]

Or pairs:

let rows = try Row.fetchAll(db, sql: "SELECT name, score FROM player")
let pairs = rows.map { ($0["name"] as String, $0["score"] as Int) }

Aggregates

let totalScore = try Int.fetchOne(db, sql: "SELECT SUM(score) FROM player") ?? 0
let avgScore = try Double.fetchOne(db, sql: "SELECT AVG(score) FROM player") ?? 0
let maxScore = try Int.fetchOne(db, sql: "SELECT MAX(score) FROM player") ?? 0

Standard SQL aggregates work fine. The query interface (covered later) gives type-safe equivalents.

EXPLAIN QUERY PLAN

For understanding query performance:

let plan = try Row.fetchAll(db, sql: "EXPLAIN QUERY PLAN SELECT * FROM player WHERE score > 50")
for row in plan {
    print(row)
}

The output shows whether SQLite uses an index (SEARCH player USING INDEX) or a full scan (SCAN player). For slow queries, this is the diagnostic to run.

Transactions

db.execute and Type.fetchAll operate against the database. To group multiple statements in one atomic transaction, use the explicit transaction APIs (covered in section 10), or pass them within a single write closure:

try dbPool.write { db in
    try db.execute(sql: "INSERT INTO player ...")
    try db.execute(sql: "UPDATE team SET playerCount = playerCount + 1 WHERE id = ?")
    // Both succeed or both rollback (the write closure is one transaction)
}

Each write closure is wrapped in a transaction automatically. If the closure throws, GRDB rolls back. If it returns successfully, GRDB commits. We’ll see explicit transactions for finer control later.

Performance: prepared statements

When you run the same query many times, parsing it each time costs. SQLite supports prepared statements — parsing once, executing many times with different arguments.

GRDB does this transparently if you use the records API or the query interface. With raw SQL, you can do it manually:

try dbPool.write { db in
    let statement = try db.makeStatement(sql: """
        INSERT INTO player (name, score) VALUES (?, ?)
        """)

    for (name, score) in players {
        try statement.execute(arguments: [name, score])
    }
}

db.makeStatement parses the SQL once. Then we execute it many times with different arguments. For inserting 10,000 rows, this is significantly faster than 10,000 separate db.execute calls.

(GRDB also caches statements internally for raw SQL with the same string — the savings from explicit prepared statements are real but not always dramatic.)

Pitfalls

SQL injection via interpolation. Don’t:

// ❌ NEVER
try db.execute(sql: "SELECT * FROM player WHERE name = '\(userInput)'")

Always use parameter binding:

// ✅
try db.execute(sql: "SELECT * FROM player WHERE name = ?", arguments: [userInput])

Missing read vs write distinction. db.execute for INSERT/UPDATE/DELETE must be inside write. Inside read, it’ll fail.

Forgetting to handle nil. Int.fetchOne returns Int?. The query might return zero rows (no result) or a row with NULL. Both produce nil. Handle accordingly.

Inconsistent column names between SQL and Swift. If your SQL aliases (SELECT name AS playerName ...), the row’s column is playerName, not name. Match.

Long-running closures. Anything inside a write blocks other writers. Don’t do network calls or heavy computation inside the closure; do them outside, then write the result.

What to internalize

db.execute(sql:arguments:) for inserts/updates/deletes; Type.fetchAll(db, sql:) for queries. Use parameter binding, not string interpolation. lastInsertedRowID for inserted IDs; changesCount for affected rows. Row.fetchAll for full rows; Type.fetchAll for single-column. Use cursors for huge results. Each write closure is a transaction. Prepared statements for repeated queries.

6. Records: Mapping Rows to Swift Types

Raw SQL is the foundation, but most of your code shouldn’t deal in Row instances. GRDB’s records are Swift types — usually structs — that map cleanly to database rows. You declare which protocols a type conforms to, and you get fetching and saving methods for free, with full type safety.

This is where GRDB starts to feel ergonomic.

The protocols

Three core protocols:

• FetchableRecord — the type can be loaded from a database row. Provides fetchOne, fetchAll, fetchCursor static methods.
• PersistableRecord — the type can be saved to a database. Provides insert, update, save, delete instance methods.
• TableRecord — the type knows what table it belongs to. Required for query-interface use.

Most records conform to all three (often via the convenience FetchableRecord & PersistableRecord pair, which inherits TableRecord indirectly).

A first record

Define a Player struct:

import GRDB

struct Player: Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    var score: Int
    var createdAt: Date

    // For autoincrement to work, capture the ID after insert
    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

A few notes:

• Codable conformance is what GRDB uses to decode rows into the type and encode the type into row values. The property names match the column names.
• MutablePersistableRecord is for types whose insert can mutate self (specifically to assign the new ID). Use this for autoincrement IDs.
• didInsert(_:) runs after a successful insert. We use it to capture the new row ID into the struct.

If you don’t have an autoincrement ID — say, you generate UUIDs yourself — you can use PersistableRecord (immutable) instead.

Fetching

try dbPool.read { db in
    // All players
    let players = try Player.fetchAll(db)

    // One player by ID
    let player = try Player.fetchOne(db, key: 42)

    // Players matching a SQL query
    let highScorers = try Player.fetchAll(db, sql: """
        SELECT * FROM player WHERE score > ?
        """, arguments: [50])
}

Player.fetchAll(db) returns [Player]. The SQL is generated automatically from the table name (which GRDB derives from the type name — Player → player). For custom queries, pass sql:.

Player.fetchOne(db, key: id) looks up by primary key. Returns Player? — nil if no row matches.

Specifying the table name

If your table name doesn’t match the convention:

struct Player: Codable, FetchableRecord, MutablePersistableRecord, TableRecord {
    static var databaseTableName: String = "players"  // plural form
    // ...
}

databaseTableName is a static property of TableRecord. Default is the lowercased type name; override if needed.

Inserting and saving

try dbPool.write { db in
    var player = Player(id: nil, name: "Alice", score: 100, createdAt: Date())
    try player.insert(db)
    print("Inserted with id: \(player.id!)")

    player.score = 150
    try player.update(db)

    // Or "save" — inserts if id is nil/0, updates otherwise
    try player.save(db)

    try player.delete(db)
}

The semantics:

• insert(db) — INSERT a new row. Calls didInsert to update the record’s ID.
• update(db) — UPDATE an existing row by primary key. Throws if no matching row.
• save(db) — UPSERT-like: tries update, falls back to insert if the row doesn’t exist.
• delete(db) — DELETE the row. Returns whether a row was deleted.

For most workflows, save is what you want. For finer control, use insert or update explicitly.

Custom column names

If your Swift property names don’t match column names exactly:

struct Player: Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    var score: Int
    var createdDate: Date  // column is `createdAt`

    // Map property names to column names
    enum CodingKeys: String, CodingKey {
        case id, name, score
        case createdDate = "createdAt"
    }
}

CodingKeys is standard Swift Codable. GRDB respects it.

Counting

let count = try Player.fetchCount(db)
let highCount = try Player.filter(Column("score") > 100).fetchCount(db)

fetchCount runs SELECT COUNT(*) — fast, no row materialization.

Existence check

let exists = try Player.fetchOne(db, key: 42) != nil

Or more efficiently:

let exists = try Player.filter(key: 42).fetchCount(db) > 0

Compound primary keys

For tables with composite primary keys:

struct NoteTag: Codable, FetchableRecord, PersistableRecord {
    var noteId: Int64
    var tagId: Int64
}

// Fetching by composite key:
try NoteTag.fetchOne(db, key: ["noteId": 1, "tagId": 2])

// Or via filter:
try NoteTag.filter(Column("noteId") == 1 && Column("tagId") == 2).fetchOne(db)

The dictionary form is convenient for arbitrary primary keys.

Hashable, Equatable, Identifiable

For SwiftUI integration and general Swift use:

struct Player: Codable, FetchableRecord, MutablePersistableRecord, Hashable, Identifiable {
    var id: Int64?
    var name: String
    var score: Int
    var createdAt: Date

    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

Hashable is automatic for structs whose properties are all Hashable. Identifiable requires an id property of an Hashable type. With var id: Int64?, Player is Identifiable for SwiftUI’s List and ForEach.

Equatable is automatic too — useful for diffing in SwiftUI.

Read-only records

A record might be useful for fetching but not saving. Conform to only FetchableRecord:

struct PlayerStats: Codable, FetchableRecord {
    let playerId: Int64
    let totalScore: Int
    let gameCount: Int
}

try dbPool.read { db in
    let stats = try PlayerStats.fetchAll(db, sql: """
        SELECT player.id AS playerId,
               SUM(game.score) AS totalScore,
               COUNT(*) AS gameCount
        FROM player
        JOIN game ON game.playerId = player.id
        GROUP BY player.id
        """)
}

PlayerStats is a fetch-only record. It can decode rows from any query producing matching columns. It doesn’t correspond to a single table — that’s fine; FetchableRecord doesn’t require it.

EncodableRecord for inserts only

For records you write but don’t read:

struct Event: Codable, EncodableRecord, PersistableRecord {
    let type: String
    let timestamp: Date
    let payload: String

    static let databaseTableName = "event"
}

Write-only is rare but valid. Most records are both fetchable and persistable.

Class-based records

You can use classes too, though structs are idiomatic. Classes are inherited from a base Record class:

final class PlayerRecord: Record {
    var id: Int64?
    var name: String
    var score: Int

    override class var databaseTableName: String { "player" }

    init(name: String, score: Int) {
        self.name = name
        self.score = score
        super.init()
    }

    required init(row: Row) throws {
        id = row["id"]
        name = row["name"]
        score = row["score"]
        try super.init(row: row)
    }

    override func encode(to container: inout PersistenceContainer) throws {
        container["id"] = id
        container["name"] = name
        container["score"] = score
    }

    override func didInsert(_ inserted: InsertionSuccess) {
        super.didInsert(inserted)
        id = inserted.rowID
    }
}

Classes give you mutability, identity, and reference semantics. Trade-offs:

• ✅ You can pass instances around, mutate, observe via property changes.
• ❌ You manage equality, hashing, observation manually.
• ❌ More boilerplate.
• ❌ Doesn’t fit cleanly into Codable.

For most apps, structs are better. Use classes only when reference semantics is essential.

Customizing Codable

If Codable doesn’t quite fit, override the encoding/decoding:

struct Player: Codable, FetchableRecord, MutablePersistableRecord {
    var id: Int64?
    var name: String
    var score: Int
    var rank: PlayerRank  // custom enum

    enum PlayerRank: Int, Codable {
        case bronze = 1, silver, gold, platinum
    }

    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

Codable handles the enum naturally. The column rank stores integers (1, 2, 3, 4); the Swift type is the enum.

For more complex transformations, implement init(from decoder:) and encode(to encoder:) manually. Section 7 covers Codable in depth.

Pitfalls

Forgetting didInsert. Without it, insert succeeds but the record’s id stays nil. Subsequent update calls fail because there’s no key.

MutablePersistableRecord vs PersistableRecord. Use MutablePersistableRecord when insert needs to mutate (assign autoincrement ID). Use PersistableRecord for immutable operations (e.g., when ID is provided externally).

Mismatched column names. If your Player has a joinedAt property but the column is createdAt, decoding fails silently — the struct gets a default zero date. Use CodingKeys to map properties.

Unknown table. If databaseTableName doesn’t match an actual table (or you forgot to register it via TableRecord), fetches throw “no such table.” Check the table name.

Records with unsupported types. Not every type is automatically fetchable. URLs, custom enums with associated values, complex nested types — handle via Codable customization or store as serialized blobs.

What to internalize

Records map Swift types to database rows. Conform to Codable, FetchableRecord, MutablePersistableRecord (or PersistableRecord for non-autoincrement). Capture inserted IDs in didInsert. Use CodingKeys to map property-to-column. Static methods like Player.fetchAll(db) and instance methods like player.save(db) give you ergonomic data access. Structs preferred over classes.

7. The Codable Bridge

GRDB integrates deeply with Swift’s Codable. When your record conforms to Codable, GRDB uses the synthesized encoding/decoding to translate between Swift values and database row values. This is convenient — but the bridge has rules. Knowing them prevents subtle bugs.

How GRDB uses Codable

When you fetch a record:

1. GRDB reads a row from SQLite.
2. It builds a synthesized “decoder” that extracts column values from the row.
3. The Swift Codable machinery uses this decoder to construct your struct.

When you save a record:

1. The Swift Codable machinery encodes your struct into a synthesized “encoder.”
2. The encoder produces values for each column.
3. GRDB binds these values to the parameters of an INSERT or UPDATE statement.

This means: anything Codable can encode/decode, GRDB can save/fetch. The breadth of Swift types you can use is determined by Codable’s reach.

Type mappings

Standard types map naturally:

Optionals work: String? maps to a TEXT column that may be NULL.

Enums with raw values are encoded as their raw value: enum Rank: String, Codable { case gold } is stored as the string “gold”.

Enums

enum PlayerStatus: String, Codable {
    case active, inactive, banned
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var name: String
    var status: PlayerStatus
}

The status column is TEXT. Codable maps the enum to its raw value automatically.

For Int-raw enums:

enum Priority: Int, Codable {
    case low = 1, medium = 2, high = 3
}

Stored as INTEGER.

Date handling

By default, Codable encodes Date as the seconds-since-2001 reference date (a Double). GRDB overrides this to use ISO 8601 strings — which is more readable and portable.

You can customize:

let encoder = JSONEncoder()
encoder.dateEncodingStrategy = .secondsSince1970

// GRDB uses its own encoders; for custom date strategies, you typically use Codable's
// or override individual properties via custom init(from:) and encode(to:)

Most apps don’t need custom date handling. Just use Date and trust GRDB’s defaults.

If you need numeric timestamps (e.g., for compatibility with a server that expects Unix timestamps), encode/decode manually:

struct Event: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var name: String
    var timestamp: Date

    enum CodingKeys: String, CodingKey {
        case id, name, timestamp
    }

    init(from decoder: Decoder) throws {
        let container = try decoder.container(keyedBy: CodingKeys.self)
        id = try container.decodeIfPresent(Int64.self, forKey: .id)
        name = try container.decode(String.self, forKey: .name)
        let unixTimestamp = try container.decode(Double.self, forKey: .timestamp)
        timestamp = Date(timeIntervalSince1970: unixTimestamp)
    }

    func encode(to encoder: Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encodeIfPresent(id, forKey: .id)
        try container.encode(name, forKey: .name)
        try container.encode(timestamp.timeIntervalSince1970, forKey: .timestamp)
    }
}

The column timestamp is REAL (a Double). The Swift property is Date. We translate at the boundary.

Optional vs required

A String? property maps to a column that allows NULL. A String property requires the column to be non-NULL.

If your schema says the column is required (.notNull()) but you use String? in Swift, GRDB will accept saving — because Swift’s String? can be non-nil — but you lose compile-time safety against accidentally sending nil.

If your schema says nullable but Swift says required, you’ll crash on null when fetching.

Match them. Required columns → required Swift types. Nullable columns → optional Swift types.

Nested structs

A struct property whose type is Codable is encoded as JSON in a TEXT column:

struct Address: Codable {
    var street: String
    var city: String
}

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var name: String
    var address: Address  // stored as JSON in TEXT column
}

The schema:

try db.create(table: "player") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
    t.column("address", .text)  // stores JSON
}

When GRDB serializes the address, it sees a custom-Codable property — it uses JSONEncoder to produce a string. Decoding is the reverse.

This works but has trade-offs:

• ✅ Convenient — no separate table.
• ❌ Can’t query into the JSON (well, you can with SQLite’s JSON1 functions, but it’s awkward).
• ❌ Updates are wholesale — you can’t update just address.city directly.

For data you query against, normalize into separate columns or tables. For data that’s purely a “blob of structured info” associated with the row, JSON works.

Arrays and dictionaries

Arrays of Codable values are also stored as JSON:

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var name: String
    var skills: [String]  // stored as JSON array in TEXT column
}

Saved as ["fishing", "hiking", "cooking"] — a JSON string.

For querying within the array, you’d need SQL JSON functions:

try db.execute(sql: """
    SELECT * FROM player WHERE EXISTS (
        SELECT 1 FROM json_each(player.skills) WHERE value = ?
    )
    """, arguments: ["fishing"])

This works but feels heavy. For data you’ll query, model it as a related table (a playerSkill table). For data that’s just a “list of strings the user attached”, JSON works.

Custom types with Codable

Any type conforming to Codable can be a property of a record, with GRDB serializing/deserializing it via JSON. This includes complex nested types:

struct Settings: Codable {
    var theme: String
    var notifications: Bool
    var preferences: [String: String]
}

struct User: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var name: String
    var settings: Settings
}

The settings column is TEXT, holding the JSON representation. Convenient for “blob of config” patterns.

DatabaseValueConvertible — for non-Codable types

Some types don’t conform to Codable but you still want to use them. DatabaseValueConvertible is the lower-level protocol:

import GRDB

struct Color: DatabaseValueConvertible {
    var red: Double
    var green: Double
    var blue: Double

    var databaseValue: DatabaseValue {
        // Encode as a hex string
        let hex = String(format: "%02X%02X%02X",
                         Int(red * 255), Int(green * 255), Int(blue * 255))
        return hex.databaseValue
    }

    static func fromDatabaseValue(_ dbValue: DatabaseValue) -> Color? {
        guard let hex = String.fromDatabaseValue(dbValue),
              hex.count == 6 else { return nil }
        let r = Int(hex.prefix(2), radix: 16) ?? 0
        let g = Int(hex.dropFirst(2).prefix(2), radix: 16) ?? 0
        let b = Int(hex.suffix(2), radix: 16) ?? 0
        return Color(red: Double(r) / 255, green: Double(g) / 255, blue: Double(b) / 255)
    }
}

DatabaseValueConvertible is the foundation that Codable builds on. Direct conformance gives you full control. Most types prefer Codable for simplicity.

Snake_case ↔ camelCase

If your team uses snake_case in SQL but camelCase in Swift, configure the JSON encoder/decoder:

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var firstName: String
    var lastName: String
}

// In your AppDatabase or migration:
try db.create(table: "player") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("first_name", .text).notNull()
    t.column("last_name", .text).notNull()
}

Use CodingKeys to map:

struct Player: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var firstName: String
    var lastName: String

    enum CodingKeys: String, CodingKey {
        case id
        case firstName = "first_name"
        case lastName = "last_name"
    }
}

The verbose form. For consistency, the easier path is to use camelCase column names everywhere — Swift convention, less translation.

Pitfalls

Forgetting init(from:) for non-trivial decoders. If you customize encoding, you must also customize decoding (and vice versa). Asymmetric Codable produces hard-to-debug errors.

Implicit JSON serialization of complex types. A Codable struct property is silently serialized as JSON. The first time you try to query it (WHERE settings.theme = ?), you realize it’s not a column — it’s a string with JSON in it.

Date timezone gotchas. Codable encodes Date with the encoder’s strategy. GRDB’s default is ISO 8601. If you read these strings outside GRDB (e.g., a tool that doesn’t parse ISO 8601), you’ll have surprises.

Optional with default value. var status: String = "active" is non-optional with a default. It still requires a value when decoding. If the column is missing or null, decoding fails. Use var status: String? if it might be missing.

UUID storage size. UUIDs as strings take 36 bytes. As BLOB, 16 bytes. For high-volume tables, BLOB is more efficient. Configure via DatabaseValueConvertible if needed.

What to internalize

GRDB uses Codable for record encoding/decoding. Standard Swift types map naturally. Enums with raw values map to their raw values. Dates are ISO 8601 strings by default. Nested Codable structs are stored as JSON in TEXT columns — convenient but not queryable. Use CodingKeys for property-to-column mapping. Use DatabaseValueConvertible for non-Codable types.

8. Database Migrations in Depth

We’ve used migrations in section 4. Now let’s go deeper — the patterns for evolving schema over time, the ordering rules, the foreign-key handling, the recipes for non-trivial changes. Migrations are how your app’s database survives across releases. Get them right and updates are seamless. Get them wrong and users lose data.

The mental model

Each migration:

• Has a stable, unique name.
• Runs at most once per database.
• Runs inside a transaction (default).
• Either fully succeeds or fully fails (rolls back).

GRDB tracks applied migrations in a hidden table called grdb_migrations. On each launch:

1. Open the database.
2. Read grdb_migrations to find which migrations have already run.
3. Run any registered migrations not in that list, in registration order.
4. After each successful migration, record its name in grdb_migrations.

This is deterministic: a fresh database goes through every migration; an updated database only runs new ones. There’s no inference, no dynamic schema diffing — what your migrations say happens.

Initial schema

Your first migration creates the entire schema:

migrator.registerMigration("initial") { db in
    try db.create(table: "user") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("email", .text).notNull().unique()
        t.column("name", .text).notNull()
    }

    try db.create(table: "post") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("userId", .integer)
            .notNull()
            .references("user", onDelete: .cascade)
        t.column("title", .text).notNull()
        t.column("body", .text).notNull()
        t.column("createdAt", .datetime).notNull()
    }

    try db.create(index: "postByUser", on: "post", columns: ["userId"])
}

Adding a column

A common evolution — add an archived flag:

migrator.registerMigration("addArchivedToPost") { db in
    try db.alter(table: "post") { t in
        t.add(column: "archived", .boolean).notNull().defaults(to: false)
    }
}

db.alter(table:) adds or modifies columns. t.add(column:) adds a new column. The default value is essential — existing rows get this value when the column is added.

If you forget the default and the column is notNull(), the migration fails because existing rows would have NULL in a non-null column.

For optional columns, you don’t need a default:

t.add(column: "deletedAt", .datetime)  // optional, defaults to NULL

Renaming a column

SQLite supports column rename since version 3.25. GRDB exposes it:

migrator.registerMigration("renameTitleToHeadline") { db in
    try db.alter(table: "post") { t in
        t.rename(column: "title", to: "headline")
    }
}

After this migration, the column is headline. Update your records’ CodingKeys (or property names) to match.

For a rename that crosses a release boundary — old client uses title, new uses headline — be careful. If both clients access the database (e.g., via a shared store with an extension), the schema is whatever the last migration set; older clients may break. Plan deployments carefully.

Dropping a column

SQLite supports column drop since version 3.35:

migrator.registerMigration("dropOldField") { db in
    try db.alter(table: "post") { t in
        t.drop(column: "oldField")
    }
}

For older SQLite versions (older iOS deployment targets), you’d recreate the table without the column:

migrator.registerMigration("dropOldField") { db in
    try db.create(table: "newPost") { t in
        // schema without oldField
        t.autoIncrementedPrimaryKey("id")
        t.column("title", .text).notNull()
        // ...
    }
    try db.execute(sql: "INSERT INTO newPost SELECT id, title FROM post")
    try db.drop(table: "post")
    try db.rename(table: "newPost", to: "post")
}

This pattern is general — it works for any column drop or type change SQLite doesn’t directly support. The cost is you’re reading and rewriting the entire table.

Adding a table

migrator.registerMigration("addTags") { db in
    try db.create(table: "tag") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("name", .text).notNull().unique()
    }

    try db.create(table: "postTag") { t in
        t.column("postId", .integer)
            .notNull()
            .references("post", onDelete: .cascade)
        t.column("tagId", .integer)
            .notNull()
            .references("tag", onDelete: .cascade)
        t.primaryKey(["postId", "tagId"])
    }
}

Adding tables is straightforward — just declare them. Existing tables aren’t affected.

Backfilling data in a migration

Sometimes a schema change requires reorganizing existing data. Suppose v1 has a fullName column; v2 splits into firstName and lastName:

migrator.registerMigration("splitFullName") { db in
    // 1. Add new columns
    try db.alter(table: "user") { t in
        t.add(column: "firstName", .text)
        t.add(column: "lastName", .text)
    }

    // 2. Backfill from old column
    let cursor = try Row.fetchCursor(db, sql: "SELECT id, fullName FROM user")
    while let row = try cursor.next() {
        let id: Int64 = row["id"]
        let fullName: String = row["fullName"]
        let parts = fullName.split(separator: " ", maxSplits: 1).map(String.init)
        let first = parts.first ?? ""
        let last = parts.dropFirst().first ?? ""
        try db.execute(sql: """
            UPDATE user SET firstName = ?, lastName = ? WHERE id = ?
            """, arguments: [first, last, id])
    }

    // 3. Drop old column
    try db.alter(table: "user") { t in
        t.drop(column: "fullName")
    }
}

This is one migration that:

1. Adds the new columns.
2. Walks existing rows and computes new values.
3. Drops the old column.

Do all three in one migration so it’s atomic — either all succeed (transaction commits) or none do (transaction rolls back).

For performance with large tables, consider doing it in chunks. But typically migrations are run on user devices with limited data, so this kind of read-update-write is fine.

Migration ordering

GRDB applies migrations in registration order, not name order. So this:

migrator.registerMigration("zPlatformUpdate") { db in /* ... */ }
migrator.registerMigration("aFix") { db in /* ... */ }

runs zPlatformUpdate first, then aFix. Names are just identifiers; order is the order you registered.

This is why migration names should reflect intent and chronology, but the actual ordering comes from registration code. Keep your migration registrations in chronological order in your source.

Foreign-key checks during migrations

By default, GRDB defers foreign key checking inside migrations. This lets you do things like:

migrator.registerMigration("restructure") { db in
    // Temporarily violate FK constraints during restructuring
    try db.execute(sql: "INSERT INTO temp_table SELECT * FROM old_table")
    try db.drop(table: "old_table")
    // FK referenced from elsewhere — checked at end of migration
}

If at the end of the migration the constraints aren’t satisfied, the whole migration fails.

You can opt out:

migrator.registerMigration("noChecks", foreignKeyChecks: .disabled) { db in
    // Foreign keys not checked at all in this migration
}

.disabled skips checks entirely — useful if you’re rebuilding the table structure in ways where transient FK violations are intentional.

eraseDatabaseOnSchemaChange

We touched on this earlier. During development:

#if DEBUG
migrator.eraseDatabaseOnSchemaChange = true
#endif

If GRDB sees that the migrations registered now don’t match what’s in grdb_migrations, it erases the database and re-applies all migrations from scratch. Useful when iterating — you don’t have to manually reset the simulator.

Never enable this in production. It would erase user data.

A subtle case: even with this flag, if you rename a previously-shipped migration after release, you can’t safely change it again — production users have it under the old name. The flag is for local iteration only.

Testing migrations

A common pattern: snapshot a database from a released version and verify migrations bring it forward correctly.

class MigrationTests: XCTestCase {
    func test_migrationFromV1ToV3() throws {
        // Create a v1-state database
        let dbQueue = try DatabaseQueue()
        try dbQueue.write { db in
            try db.create(table: "user") { t in
                t.autoIncrementedPrimaryKey("id")
                t.column("fullName", .text).notNull()
            }
            try db.execute(sql: "INSERT INTO user (fullName) VALUES (?)",
                          arguments: ["John Doe"])
            try db.execute(sql: "INSERT INTO user (fullName) VALUES (?)",
                          arguments: ["Jane Smith"])
            // Mark the v1 migration as applied (so it doesn't re-run)
            try db.execute(sql: """
                CREATE TABLE IF NOT EXISTS grdb_migrations (
                    identifier TEXT NOT NULL PRIMARY KEY
                )
                """)
            try db.execute(sql: """
                INSERT INTO grdb_migrations (identifier) VALUES (?)
                """, arguments: ["v1.initial"])
        }

        // Now run the full migrator
        let appDatabase = try AppDatabase(dbQueue)

        // Verify migrations applied correctly
        try dbQueue.read { db in
            let users = try Row.fetchAll(db, sql: "SELECT firstName, lastName FROM user")
            XCTAssertEqual(users.count, 2)
            XCTAssertEqual(users[0]["firstName"] as String, "John")
            XCTAssertEqual(users[0]["lastName"] as String, "Doe")
        }
    }
}

This is more involved than typical tests but catches migration regressions.

Defensive patterns

Idempotency. A migration runs once. But during development, you might be re-running with eraseDatabaseOnSchemaChange = true, which means full schema rebuild. Make sure your migrations work from a fresh database, not just from the version that preceded them.

No dependencies on app state. Migrations should be pure schema operations + data manipulation. Don’t fetch from the network, don’t read user defaults, don’t depend on anything outside the database.

Migrations as code review focus. When you ship a migration, every team member should review it. Migrations on user devices are run-once events; bugs are unfixable after the fact (you’d ship another migration to compensate).

Test on real data. Migrate in development with a database close to what users have — same row counts, same data shapes. Migrations that work on 10 rows might be slow on 100,000.

Multiple tables in one migration

A single migration can do many things:

migrator.registerMigration("v2.bigRefactor") { db in
    try db.create(table: "newTable") { t in /* ... */ }
    try db.alter(table: "user") { t in
        t.add(column: "newField", .text)
    }
    try db.execute(sql: "UPDATE user SET newField = 'default'")
    try db.create(index: "userByEmail", on: "user", columns: ["email"])
    try db.drop(table: "obsoleteTable")
}

All of these happen in one transaction. If any step throws, all are rolled back — the migration is not marked applied.

For very large refactorings, split into multiple migrations. Smaller migrations are easier to debug and roll back if something goes wrong.

DatabaseSchemaChange

GRDB exposes the migrator’s state programmatically:

try dbWriter.read { db in
    let applied = try migrator.appliedMigrations(db)
    let hasCompleted = try migrator.hasCompletedMigrations(db)
    print("Applied: \(applied), all done: \(hasCompleted)")
}

Useful for diagnostics, or for showing a “first launch — setting up…” UI during initial migration on a fresh install.

Pitfalls

Renaming a registered migration. Breaks tracking. Old users have it tracked under the old name; new code has it under the new name. Don’t.

Modifying a migration’s body after release. Same as above. The old code ran with old logic; new launches don’t re-run it. Modify forward.

Migrations with side effects. A migration that calls a network API doesn’t fit the model — migrations should be deterministic and idempotent in shape. Side effects belong in app launch code, after migration.

Schema changes outside migrations. If you db.execute("CREATE TABLE...") outside a migration, the table exists but isn’t tracked. Future migrations that depend on it might fail on databases where someone created it manually but not via migration. Always go through migrations.

Using eraseDatabaseOnSchemaChange in production. Catastrophic data loss for users.

What to internalize

Each migration has a stable name, runs once, runs in a transaction. Order is registration order. Use the schema DSL for clarity. Backfill data within the migration that introduces the change. Test migrations with realistic data. Use eraseDatabaseOnSchemaChange = true only in DEBUG. Never modify a migration after release.

9. Reads and Writes: Sync, Async, Throwing

GRDB has a small but important set of access methods on DatabaseWriter/DatabaseReader. Knowing them — and when to use which — is core to writing correct, ergonomic GRDB code.

The sync forms

The most basic:

try dbPool.write { db in
    // Synchronous write block.
    // Blocks the current thread.
    try db.execute(sql: "INSERT INTO player ...")
}

let count = try dbPool.read { db in
    // Synchronous read block.
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player") ?? 0
}

write and read are sync. They block the calling thread until the closure completes. The closure runs on GRDB’s internal queue — your code waits there.

Use sync forms when:

• You’re already on a background queue.
• The operation is fast (a few rows).
• You need the result immediately for subsequent logic.

Don’t use sync forms on the main thread for anything non-trivial — you’ll freeze the UI.

The async forms

try await dbPool.write { db in
    try db.execute(sql: "INSERT INTO player ...")
}

let count = try await dbPool.read { db in
    try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM player") ?? 0
}

The async forms suspend the calling task instead of blocking. Conceptually identical to the sync forms but cooperate with Swift Concurrency.

These are the modern path. For new code, prefer async. The async closure body still runs on GRDB’s queue; you just don’t block the caller.

read vs write

The distinction:

• read uses a reader connection. With DatabasePool, multiple reads can happen concurrently. With DatabaseQueue, reads serialize through the single queue.
• write uses the writer connection. Always serialized — only one write at a time.

Inside a read, you can only run SELECT-style queries. Trying to INSERT/UPDATE/DELETE inside a read closure throws. (Strict reader/writer separation prevents accidental writes.)

Inside a write, you can do both — but if the operation only reads, prefer read so you’re not unnecessarily holding the writer lock.

unsafeRead and unsafeReentrantWrite

GRDB exposes “unsafe” variants for advanced patterns:

try dbPool.unsafeRead { db in
    // Same as read, but allows the closure to call write reentrantly
    // (only safe under specific conditions)
}