Grdb Patterns

Migrations

Use DatabaseMigrator with versioned, never-modified migrations:

var migrator = DatabaseMigrator()

migrator.registerMigration("v1_createEmails") { db in
    try db.create(table: "email") { t in
        t.autoIncrementedPrimaryKey("id")
        t.column("accountEmail", .text).notNull()
        t.column("gmailId", .text)
        t.column("graphMessageId", .text)
        t.column("threadId", .text).notNull().indexed()
        t.column("subject", .text).notNull()
        t.column("sender", .text).notNull()
        t.column("snippet", .text).notNull().defaults(to: "")
        t.column("receivedAt", .datetime).notNull()
        t.column("isRead", .boolean).notNull().defaults(to: false)
        t.column("isStarred", .boolean).notNull().defaults(to: false)
        t.column("labelIds", .text).notNull().defaults(to: "[]")
    }
    // Composite index for account-scoped queries ordered by date
    try db.create(
        index: "idx_email_accountEmail_receivedAt",
        on: "email",
        columns: ["accountEmail", "receivedAt"]
    )
}

migrator.registerMigration("v2_addAttachmentsTable") { db in
    try db.create(table: "attachment") { t in
        t.autoIncrementedPrimaryKey("id")
        t.belongsTo("email", onDelete: .cascade).notNull()
        t.column("filename", .text).notNull()
        t.column("mimeType", .text).notNull()
        t.column("size", .integer).notNull()
    }
}

Migration Rules

1. NEVER modify an existing migration. Always add a new one.
2. Name migrations with a version prefix: v1_, v2_, etc.
3. Always specify .notNull() and .defaults(to:) where appropriate.
4. Foreign keys use .belongsTo() with explicit onDelete behavior.

Query Patterns

Simple fetches

// Fetch all unread emails for an account, newest first
let unread = try dbQueue.read { db in
    try Email
        .filter(Column("accountEmail") == accountEmail)
        .filter(Column("isRead") == false)
        .order(Column("receivedAt").desc)
        .fetchAll(db)
}

Mandatory: Every query MUST filter by accountEmail to prevent cross-account data leaks.

Request types for observation

extension Email {
    static func inboxRequest(accountEmail: String) -> QueryInterfaceRequest<Email> {
        Email
            .filter(Column("accountEmail") == accountEmail)
            .filter(literal: "labelIds LIKE '%\"INBOX\"%'")
            .order(Column("receivedAt").desc)
    }
}

Database Observation (ValueObservation)

For live UI updates, use ValueObservation. Prefer the modern GRDB 7+ async for try await pattern:

// In ViewModel — modern GRDB 7+ async observation (preferred)
func startObserving(accountEmail: String) async {
    let observation = ValueObservation.tracking { db in
        try Email.inboxRequest(accountEmail: accountEmail).fetchAll(db)
    }
    do {
        for try await emails in observation.values(in: dbQueue) {
            self.messages = emails
        }
    } catch {
        Logger.debug("Observation failed: \(error)", category: .database)
    }
}

Callback-based alternative (legacy, use only when async context is unavailable):

let observation = ValueObservation.tracking { db in
    try Email.inboxRequest(accountEmail: accountEmail).fetchAll(db)
}

let cancellable = observation.start(in: dbQueue, onError: { error in
    // handle
}, onChange: { [weak self] emails in
    self?.messages = emails
})

Observation Rules

1. Prefer the async for try await pattern (GRDB 7+) — it integrates naturally with Swift concurrency and structured task cancellation.
2. Use ValueObservation for read-only UI bindings — not manual polling.
3. When using the callback-based API, store the cancellable and cancel it on deinit.
4. For write-heavy paths, use .removeDuplicates() to avoid excessive UI updates.

DatabaseQueue vs DatabasePool

• DatabaseQueue: Use for single-writer scenarios (current default for UnleashedMail).
• DatabasePool: Use if you need concurrent reads while writing. Requires WAL mode.

Stick with DatabaseQueue unless profiling shows read contention.

Testing

Always test database code with an in-memory DatabaseQueue:

func makeTestDB() throws -> DatabaseQueue {
    let db = try DatabaseQueue()
    var migrator = AppDatabase.migrator // reuse production migrations
    try migrator.migrate(db)
    return db
}