SQLiteData Advanced Reference

Overview

Advanced query patterns and schema composition techniques for SQLiteData by Point-Free. Built on GRDB and StructuredQueries.

For core patterns (CRUD, CloudKit setup, @Table basics), see the axiom-sqlitedata discipline skill.

This reference covers advanced querying, schema composition, views, and custom aggregates.

Requires iOS 17+, Swift 6 strict concurrency Framework SQLiteData 1.4+

Column Groups and Schema Composition

SQLiteData provides powerful tools for composing schema types, enabling reuse, better organization, and single-table inheritance patterns.

Column Groups

Group related columns into reusable types with @Selection :

// Define a reusable column group @Selection struct Timestamps { let createdAt: Date let updatedAt: Date? }

// Use in multiple tables @Table nonisolated struct RemindersList: Identifiable { let id: UUID var title = "" let timestamps: Timestamps // Embedded column group }

@Table nonisolated struct Reminder: Identifiable { let id: UUID var title = "" var isCompleted = false let timestamps: Timestamps // Same group, reused }

Important: SQLite has no concept of grouped columns. Flatten all groupings in your CREATE TABLE:

CREATE TABLE "remindersLists" ( "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()), "title" TEXT NOT NULL DEFAULT '', "createdAt" TEXT NOT NULL, "updatedAt" TEXT ) STRICT

Querying Column Groups

Access fields inside groups with dot syntax:

// Query a field inside the group RemindersList .where { $0.timestamps.createdAt <= cutoffDate } .fetchAll(db)

// Compare entire group (flattens to tuple in SQL) RemindersList .where { $0.timestamps <= Timestamps(createdAt: date1, updatedAt: date2) }

Nesting Groups in @Selection

Use column groups in custom query results:

@Selection struct Row { let reminderTitle: String let listTitle: String let timestamps: Timestamps // Nested group }

let results = try Reminder .join(RemindersList.all) { $0.remindersListID.eq($1.id) } .select { Row.Columns( reminderTitle: $0.title, listTitle: $1.title, timestamps: $0.timestamps // Pass entire group ) } .fetchAll(db)

Single-Table Inheritance with Enums

Model polymorphic data using @CasePathable @Selection enums — a value-type alternative to class inheritance:

import CasePaths

@Table nonisolated struct Attachment: Identifiable { let id: UUID let kind: Kind

@CasePathable @Selection
enum Kind {
    case link(URL)
    case note(String)
    case image(URL)
}

}

Note: @CasePathable is required and comes from Point-Free's CasePaths library.

SQL Schema for Enum Tables

Flatten all cases into nullable columns:

CREATE TABLE "attachments" ( "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()), "link" TEXT, "note" TEXT, "image" TEXT ) STRICT

Querying Enum Tables

// Fetch all — decoding determines which case let attachments = try Attachment.all.fetchAll(db)

// Filter by case let images = try Attachment .where { $0.kind.image.isNot(nil) } .fetchAll(db)

Inserting Enum Values

try Attachment.insert { Attachment.Draft(kind: .note("Hello world!")) } .execute(db) // Inserts: (id, NULL, 'Hello world!', NULL)

Updating Enum Values

try Attachment.find(id).update { $0.kind = #bind(.link(URL(string: "https://example.com")!)) } .execute(db) // Sets link column, NULLs note and image columns

Complex Enum Cases with Grouped Columns

Enum cases can hold structured data using nested @Selection types:

@Table nonisolated struct Attachment: Identifiable { let id: UUID let kind: Kind

@CasePathable @Selection
enum Kind {
    case link(URL)
    case note(String)
    case image(Attachment.Image)  // Fully qualify nested types
}

@Selection
struct Image {
    var caption = ""
    var url: URL
}

}

SQL schema flattens all nested fields:

CREATE TABLE "attachments" ( "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()), "link" TEXT, "note" TEXT, "caption" TEXT, "url" TEXT ) STRICT

Passing Rows to Database Functions

With column groups, @DatabaseFunction can accept entire table rows:

@DatabaseFunction func isPastDue(reminder: Reminder) -> Bool { !reminder.isCompleted && reminder.dueDate < Date() }

// Use in queries — columns are flattened/reconstituted automatically let pastDue = try Reminder .where { $isPastDue(reminder: $0) } .fetchAll(db)

Column Groups vs SwiftData Inheritance

Approach SQLiteData SwiftData

Type Value types (enums/structs) Reference types (classes)

Exhaustivity Compiler-enforced switch Runtime type checking

Verbosity Concise enum cases Verbose class hierarchy

Inheritance Single-table via enum @Model class inheritance

Reusable columns @Selection groups Manual repetition

SwiftData equivalent (more verbose):

@Model class Attachment { var isActive: Bool } @Model class Link: Attachment { var url: URL } @Model class Note: Attachment { var note: String } @Model class Image: Attachment { var url: URL } // Each needs explicit init calling super.init

Query Composition

Build reusable scopes as static properties/methods:

extension Item { static let active = Item.where { !$0.isArchived && !$0.isDeleted } static let inStock = Item.where(.isInStock)

static func createdAfter(_ date: Date) -> Where<Item> {
    Item.where { $0.createdAt > date }
}

}

// Chain scopes let results = try Item.active.inStock.order(by: .title).fetchAll(db)

// Use as base for @FetchAll @FetchAll(Item.active) var items

Extend Where<Item> to add composable filters:

extension Where<Item> { func matching(_ search: String) -> Where<Item> { self.where { $0.title.contains(search) || $0.notes.contains(search) } } } let results = try Item.inStock.matching(searchText).fetchAll(db)

Custom Fetch Requests with @Fetch

Use @Fetch when you need multiple pieces of data in a single read transaction (use @FetchAll /@FetchOne for single-table queries):

struct DashboardRequest: FetchKeyRequest { struct Value: Sendable { let totalItems: Int let activeItems: [Item] let categories: [Category] }

func fetch(_ db: Database) throws -> Value {
    try Value(
        totalItems: Item.count().fetchOne(db) ?? 0,
        activeItems: Item.where { !$0.isArchived }.order(by: \.updatedAt.desc()).limit(10).fetchAll(db),
        categories: Category.order(by: \.name).fetchAll(db)
    )
}

}

@Fetch(DashboardRequest()) var dashboard

Dynamic loading with .load() :

@Fetch var results = SearchRequest.Value()

.task(id: query) { try? await $results.load(SearchRequest(query: query), animation: .default) }

Key benefits: atomic reads, automatic observation, type-safe results.

Advanced Query Patterns

String Functions

Function Usage SQL

upper() / lower()

$0.title.upper()

UPPER/LOWER

trim() / ltrim() / rtrim()

$0.title.trim()

TRIM

substr(start, len)

$0.title.substr(0, 3)

SUBSTR

replace(old, new)

$0.title.replace("old", "new")

REPLACE

length()

$0.title.length()

LENGTH

instr(search)

$0.title.instr("search") > 0

INSTR

like(pattern)

$0.title.like("%phone%")

LIKE

hasPrefix / hasSuffix / contains

$0.title.contains("Max")

Swift-style

collate(.nocase)

$0.title.collate(.nocase).eq(#bind("X"))

COLLATE

Null Handling

// Coalesce — first non-null value let name = try User.select { $0.nickname ?? $0.firstName ?? "Anonymous" }.fetchAll(db)

// Null checks let withDue = try Reminder.where { $0.dueDate.isNot(nil) }.fetchAll(db) let noDue = try Reminder.where { $0.dueDate.is(nil) }.fetchAll(db)

// Null-safe ordering let sorted = try Item.order { $0.priority.desc(nulls: .last) }.fetchAll(db)

Range and Set Membership

// IN (set or subquery) let selected = try Item.where { $0.id.in(selectedIds) }.fetchAll(db) let inActive = try Item.where { $0.categoryID.in( Category.where(.isActive).select(.id) )}.fetchAll(db)

// NOT IN let excluded = try Item.where { !$0.id.in(excludedIds) }.fetchAll(db)

// BETWEEN (or Swift range syntax) let midRange = try Item.where { $0.price.between(10, and: 100) }.fetchAll(db)

Pagination

// Offset-based let items = try Item.order(by: .createdAt).limit(20, offset: page * 20).fetchAll(db)

// Cursor-based (more efficient for deep pages) let items = try Item.where { $0.id > lastSeenId }.order(by: .id).limit(20).fetchAll(db)

Distinct Results

let categories = try Item.select(.category).distinct().fetchAll(db)

RETURNING Clause

Fetch generated values from INSERT, UPDATE, or DELETE operations:

// Insert and get auto-generated ID let newId = try Item.insert { Item.Draft(title: "New Item") } .returning(.id).fetchOne(db)

// Update and return new values let updates = try Item.find(id).update { $0.count += 1 } .returning { ($0.id, $0.count) }.fetchOne(db)

// Capture deleted records before removal let deleted = try Item.where { $0.isArchived }.delete() .returning(Item.self).fetchAll(db)

Use RETURNING to avoid a second query for auto-generated IDs, audit deletions, or verify updates.

Joins

Join Types

// INNER JOIN — only matching rows let items = try Item.join(Category.all) { $0.categoryID.eq($1.id) }.fetchAll(db)

// LEFT JOIN — all from left, matching from right (nullable) let items = try Item.leftJoin(Category.all) { $0.categoryID.eq($1.id) } .select { ($0, $1) } // (Item, Category?) .fetchAll(db)

Also available: .rightJoin() (all from right) and .fullJoin() (all from both).

Multi-table joins chain naturally:

extension Reminder { static let withTags = group(by: .id) .leftJoin(ReminderTag.all) { $0.id.eq($1.reminderID) } .leftJoin(Tag.all) { $1.tagID.eq($2.primaryKey) } }

Self-Joins with TableAlias

struct ManagerAlias: TableAlias { typealias Table = Employee }

let employeesWithManagers = try Employee .leftJoin(Employee.all.as(ManagerAlias.self)) { $0.managerID.eq($1.id) } .select { (employeeName: $0.name, managerName: $1.name) } .fetchAll(db)

Case Expressions

// Simple case — map values let labels = try Item.select { Case($0.priority).when(1, then: "Low").when(2, then: "Medium") .when(3, then: "High").else("Unknown") }.fetchAll(db)

// Searched case — boolean conditions let status = try Order.select { Case().when($0.shippedAt.isNot(nil), then: "Shipped") .when($0.paidAt.isNot(nil), then: "Paid").else("Unknown") }.fetchAll(db)

// Case in updates (toggle pattern) try Reminder.find(id).update { $0.status = Case($0.status) .when(#bind(.incomplete), then: #bind(.completing)) .when(#bind(.completing), then: #bind(.completed)) .else(#bind(.incomplete)) }.execute(db)

Common Table Expressions (CTEs)

Non-Recursive CTEs

// Single CTE let expensiveItems = try With { Item.where { $0.price > 1000 } } query: { expensive in expensive.order(by: .price).limit(10) }.fetchAll(db)

// Multiple CTEs let report = try With { Customer.where { $0.totalSpent > 10000 } } with: { Order.where { $0.createdAt > lastMonth } } query: { highValue, recentOrders in highValue.join(recentOrders) { $0.id.eq($1.customerID) } .select { ($0.name, $1.total) } }.fetchAll(db)

Use CTEs to break complex queries into readable parts, reuse subqueries, or improve query plans.

Recursive CTEs

Query hierarchical data (trees, org charts, threaded comments):

@Table nonisolated struct Category: Identifiable { let id: UUID var name = "" var parentID: UUID? // Self-referential }

// Get all descendants of a root category let allDescendants = try With { Category.where { $0.id.eq(#bind(rootCategoryId)) } // Base case } recursiveUnion: { cte in Category.all.join(cte) { $0.parentID.eq($1.id) }.select { $0 } // Recursive case } query: { cte in cte.order(by: .name) }.fetchAll(db)

Reverse the join condition ($0.id.eq($1.parentID) ) to walk up the tree instead of down.

Full-Text Search (FTS5)

Basic FTS5

@Table struct ReminderText: FTS5 { let rowid: Int let title: String let notes: String let tags: String }

// Create FTS table in migration try #sql( """ CREATE VIRTUAL TABLE "reminderTexts" USING fts5( "title", "notes", "tags", tokenize = 'trigram' ) """ ) .execute(db)

Advanced FTS5 Features

// Highlight search terms let results = try ItemText.where { $0.match(query) } .select { ($0.rowid, $0.title.highlight("<b>", "</b>")) }.fetchAll(db)

// Snippets with context let snippets = try ItemText.where { $0.match(query) } .select { $0.description.snippet("<b>", "</b>", "...", 64) }.fetchAll(db)

// BM25 relevance ranking let ranked = try ItemText.where { $0.match(query) } .order { $0.bm25().desc() }.fetchAll(db)

Aggregation

String and JSON Aggregation

// groupConcat — comma-separated tags per item let itemsWithTags = try Item.group(by: .id) .leftJoin(ItemTag.all) { $0.id.eq($1.itemID) } .leftJoin(Tag.all) { $1.tagID.eq($2.id) } .select { ($0.title, $2.name.groupConcat(separator: ", ")) } .fetchAll(db) // ("iPhone", "electronics, mobile, apple")

// jsonGroupArray — aggregate into JSON array let itemsJson = try Store.group(by: .id) .leftJoin(Item.all) { $0.id.eq($1.storeID) } .select { ($0.name, $1.title.jsonGroupArray()) } .fetchAll(db)

Options: .groupConcat(distinct: true) , .groupConcat(order: { $0.asc() }) , .jsonGroupArray(filter: $1.isActive) , jsonObject("key", $0.value) .

Conditional Aggregation

All aggregate functions accept a filter: parameter:

let stats = try Item.select { Stats.Columns( total: $0.count(), activeCount: $0.count(filter: $0.isActive), avgActivePrice: $0.price.avg(filter: $0.isActive), totalRevenue: $0.revenue.sum(filter: $0.status.eq(#bind(.completed))) ) }.fetchOne(db)

HAVING Clause

.where() filters rows before grouping; .having() filters groups after aggregation:

let frequentCustomers = try Customer.group(by: .id) .leftJoin(Order.all) { $0.id.eq($1.customerID) } .having { $1.count() > 5 } .select { ($0.name, $1.count()) } .fetchAll(db)

Schema Creation with #sql Macro

The #sql macro enables type-safe raw SQL for schema creation and migrations.

CREATE TABLE

migrator.registerMigration("Create initial tables") { db in try #sql(""" CREATE TABLE "items" ( "id" TEXT PRIMARY KEY NOT NULL DEFAULT (uuid()), "title" TEXT NOT NULL DEFAULT '', "isInStock" INTEGER NOT NULL DEFAULT 1, "price" REAL NOT NULL DEFAULT 0.0, "createdAt" TEXT NOT NULL DEFAULT (datetime('now')) ) STRICT """).execute(db) }

Parameter Interpolation

• (value) → Automatically escaped (safe for user input)

• (raw: value) → Inserted literally (only for identifiers you control)

• Never use (raw: userInput) — SQL injection vulnerability

Other DDL

// CREATE INDEX (with optional WHERE for partial indexes) try #sql("""CREATE INDEX "idx_items_search" ON "items" ("title") WHERE "isArchived" = 0""").execute(db)

// CREATE TRIGGER try #sql(""" CREATE TRIGGER "update_timestamp" AFTER UPDATE ON "items" BEGIN UPDATE "items" SET "updatedAt" = datetime('now') WHERE "id" = NEW."id"; END """).execute(db)

// ALTER TABLE try #sql("""ALTER TABLE "items" ADD COLUMN "notes" TEXT NOT NULL DEFAULT ''""").execute(db)

Use #sql for DDL (CREATE, ALTER, indexes, triggers). Use the query builder for regular CRUD.

Foreign Key Relationships

migrator.registerMigration("Create tables with foreign keys") { db in try #sql(""" CREATE TABLE "itemCategories" ( "itemID" TEXT NOT NULL REFERENCES "items"("id") ON DELETE CASCADE, "categoryID" TEXT NOT NULL REFERENCES "categories"("id") ON DELETE CASCADE, PRIMARY KEY ("itemID", "categoryID") ) STRICT """).execute(db) }

Critical: Enable foreign key enforcement — SQLite disables it by default:

var configuration = Configuration() configuration.prepareDatabase { db in try db.execute(sql: "PRAGMA foreign_keys = ON") }

Without PRAGMA foreign_keys = ON , REFERENCES and ON DELETE CASCADE are silently ignored.

Transaction Context for Batch Operations

Wrap batch operations in explicit transactions for atomicity and performance:

try database.write { db in // All operations share one transaction for item in items { try Item.insert { Item.Draft(title: item.title) }.execute(db) } } // Commits once on success, rolls back entirely on failure

The database.write { } block is already a transaction. For read-heavy batch analysis, use database.read { } which provides a consistent snapshot.

Database Views

@Selection for Custom Query Results

@Selection generates a .Columns type for compile-time verified query results:

@Selection struct ReminderWithList: Identifiable { var id: Reminder.ID { reminder.id } let reminder: Reminder let remindersList: RemindersList }

@FetchAll( Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) } .select { ReminderWithList.Columns(reminder: $0, remindersList: $1) } ) var reminders: [ReminderWithList]

Also works for aggregate queries — see the Conditional Aggregation section above.

Temporary Views

For reusable complex queries, combine @Table @Selection and createTemporaryView :

@Table @Selection private struct ReminderWithList { let reminderTitle: String let remindersListTitle: String }

try database.write { db in try ReminderWithList.createTemporaryView( as: Reminder.join(RemindersList.all) { $0.remindersListID.eq($1.id) } .select { ReminderWithList.Columns(reminderTitle: $0.title, remindersListTitle: $1.title) } ).execute(db) }

// Query like a table — join complexity hidden let results = try ReminderWithList.order { ($0.remindersListTitle, $0.reminderTitle) }.fetchAll(db)

Temporary views exist for the connection lifetime. For persistent views, use #sql("CREATE VIEW ...") in migrations.

To make views writable, add createTemporaryTrigger(insteadOf: .insert { ... }) to reroute operations to underlying tables.

Custom Aggregate Functions

Write complex aggregation in Swift with @DatabaseFunction , avoiding contorted SQL subqueries:

// 1. Define — takes Sequence<T?>, returns aggregate result @DatabaseFunction func mode(priority priorities: some Sequence<Reminder.Priority?>) -> Reminder.Priority? { var occurrences: [Reminder.Priority: Int] = [:] for priority in priorities { guard let priority else { continue } occurrences[priority, default: 0] += 1 } return occurrences.max { $0.value < $1.value }?.key }

// 2. Register configuration.prepareDatabase { db in db.add(function: $mode) }

// 3. Use in queries let results = try RemindersList.group(by: .id) .leftJoin(Reminder.all) { $0.id.eq($1.remindersListID) } .select { ($0.title, $mode(priority: $1.priority)) } .fetchAll(db)

Common uses: mode, median, weighted average, custom filtering. Functions run in Swift (not SQLite's C engine), so use built-in aggregates (count , sum , avg , min , max ) when possible.

Batch Upsert Performance

For high-volume sync (50K+ records), use cached statements instead of the type-safe API:

func batchUpsert(_ items: [Item], in db: Database) throws { let statement = try db.cachedStatement(sql: """ INSERT INTO items (id, name, libraryID, remoteID, updatedAt) VALUES (?, ?, ?, ?, ?) ON CONFLICT(libraryID, remoteID) DO UPDATE SET name = excluded.name, updatedAt = excluded.updatedAt WHERE excluded.updatedAt >= items.updatedAt """) for item in items { try statement.execute(arguments: [item.id, item.name, item.libraryID, item.remoteID, item.updatedAt]) } }

For even higher throughput, build multi-row VALUES clauses. Query the variable limit at runtime: sqlite3_limit(db.sqliteConnection, SQLITE_LIMIT_VARIABLE_NUMBER, -1) (32,766 on iOS 14+, 999 on iOS 13).

Pattern Throughput Trade-off

Type-safe upsert ~1K rows/sec Best DX, compile-time checks

Cached statement ~10K rows/sec Good balance

Multi-row VALUES ~50K rows/sec Most complex

Miscellaneous Advanced Patterns

Database Triggers

try database.write { db in try Reminder.createTemporaryTrigger( after: .insert { new in Reminder .find(new.id) .update { $0.position = Reminder.select { ($0.position.max() ?? -1) + 1 } } } ) .execute(db) }

Custom Update Logic

extension Updates<Reminder> { mutating func toggleStatus() { self.status = Case(self.status) .when(#bind(.incomplete), then: #bind(.completing)) .else(#bind(.incomplete)) } }

// Usage try Reminder.find(reminder.id).update { $0.toggleStatus() }.execute(db)

Enum Support

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

enum Status: Int, QueryBindable { case incomplete = 0 case completing = 1 case completed = 2 }

@Table nonisolated struct Reminder: Identifiable { let id: UUID var priority: Priority? var status: Status = .incomplete }

Compound Selects

// UNION (deduplicated), UNION ALL (keep duplicates) let all = try Customer.select(.email).union(Supplier.select(.email)).fetchAll(db)

// INTERSECT (in both), EXCEPT (in first but not second) let shared = try Customer.select(.email).intersect(Supplier.select(.email)).fetchAll(db)

Resources

GitHub: pointfreeco/sqlite-data, pointfreeco/swift-structured-queries, groue/GRDB.swift

Skills: axiom-sqlitedata, axiom-sqlitedata-migration, axiom-database-migration, axiom-grdb

Targets: iOS 17+, Swift 6 Framework: SQLiteData 1.4+ History: See git log for changes