Reading and writing with GRDB

Learning intentions

In this lesson, you will learn practical GRDB patterns for exploring and working with data.

• Dump the database schema so you can inspect tables, columns, and constraints.
• Find a record by primary key ID.
• Find a record by a property value.
• Find a record in a table that uses a composite primary key made of two foreign keys.
• Persist changes by inserting and updating records.

Starter setup

The examples below assume you have already created a DatabaseQueue:

import GRDB

let dbQueue = try DatabaseQueue(path: "./school.db")

Dump the database schema

Use GRDB’s built-in dumpSchema() helper.

try dbQueue.read { db in
    let schema = try db.dumpSchema()
    print(schema)
}

This prints tables, indexes, triggers, and other schema objects without you writing SQL.

Search for a FetchableRecord by ID

Use fetchOne with a key to load one row by primary key.

struct Student: Identifiable, Codable, FetchableRecord, PersistableRecord {
    var id: Int
    var name: String
    var yearLevel: Int
}

let studentId: Int = 42

try dbQueue.read { db in
    let student = try Student.fetchOne(db, key: studentId)
    if let student {
        print("Found student: \(student.name)")
    } else {
        print("No student with id \(studentId)")
    }
}

fetchOne returns an optional because the row may not exist.

Search for one record by property value

Use a filter expression when you are not searching by primary key.

struct Student : Identifiable, Codable, FetchableRecord, PersistableRecord {

    // ...

    enum Columns {
        static let name = Column("name")
        static let yearLevel = Column("yearLevel")
    }
}

try dbQueue.read { db in
    let student = try Student
        .filter(Student.Columns.name == "Aroha")
        .fetchOne(db)

    if let student {
        print("Found by name: \(student)")
    } else {
        print("No match for name Aroha")
    }
}

Searching for more than one record

If your query could return many rows, use fetchAll instead.

try dbQueue.read { db in
    let seniorStudents = try Student
        .filter(Student.Columns.yearLevel >= 12)
        .order(Student.Columns.name)
        .fetchAll(db)

    for student in seniorStudents {
        print("\(student.name) is in year \(student.yearLevel)")
    }
}

Search in a table with a composite primary key (two foreign keys)

A join table often uses two foreign keys as one combined primary key.

struct Enrolment: Codable, FetchableRecord, PersistableRecord {
    var studentId: Int
    var classId: Int
    var enrolledOn: String

    enum Columns {
        static let studentId = Column("studentId")
        static let classId = Column("classId")
    }
}

Assume the Enrolment table already exists in your database with a composite primary key on studentId + classId.

To find one enrolment row, filter by both key columns:

let wantedStudentId: Int = 42
let wantedClassId: Int = 7

try dbQueue.read { db in
    let enrolment = try Enrolment
        .filter(
            Enrolment.Columns.studentId == wantedStudentId &&
            Enrolment.Columns.classId == wantedClassId
        )
        .fetchOne(db)

    print(enrolment as Any)
}

5) Persist data to the database

Use insert, update, or save inside a write block.

try dbQueue.write { db in
    // Insert new row
    var newStudent = Student(id: 12345, name: "Wiremu", yearLevel: 12)
    try newStudent.insert(db)

    // Update existing row
    newStudent.yearLevel = 13
    try newStudent.update(db)

    // Save can insert or update depending on primary key state
    var maybeExisting = Student(id: newStudent.id, name: "Wiremu K", yearLevel: 13)
    try maybeExisting.save(db)
}

Why a write block?

dbQueue.write { ... } gives you one safe transaction context for all changes inside it.

You should aim to only create the block when you need it. Avoid opening it but then doing non-write activities inside. Open it, use it, and then close it (by exiting its scope) by not overstuffing it with irrelevant code.

Tasks

Summary

• Use dumpSchema() to print the current schema.
• Use fetchOne(db, key: ...) for primary-key lookups.
• Use .filter(...) for property-based search, and fetchAll when you need a collection.
• For composite keys, filter by all key columns.
• Use a write block with insert, update, and save to persist data.