Some Claude generated test helpers for snapshotting query index usage and efficiency metrics. Don't bother arguing with me about it, it's not a hill I'm interested in dying on. It's a handy way to keep track of regressions and it's taught me some details about what goes on under the hood.

ExampleCallsite.swift

  @Test("MasonryGalleryItemsQuery - folder")
  func masonryFolder() async throws {
    let ids = try await setupTestData(in: database)

    try await database.read { db in
      let query = MasonryGalleryItemsQuery(galleryType: .folder(ids.folderID), limit: 100).makeQuery()
      try assertInlineSnapshot(of: getQueryIndexes(query, in: db), as: .description) {
        """
        feedFolders: idx_feedFolders_folderID_feedID
        feeds: sqlite_autoindex_Feeds_1
        mediaItems: idx_mediaItems_postID_mediaSource_postOrder
        posts: idx_posts_feedID_date_id
        """
      }
      try assertInlineSnapshot(of: getQueryStatistics(query, in: db), as: .description) {
        """
        QueryStatistics:
          fullScanSteps: 0
          sortOperations: 1
          autoIndexCreations: 0
          vmSteps: ~2000
        """
      }
    }
  }

QueryEfficiencyAssertion.swift

import Foundation
import GRDB
import SQLite3
import StructuredQueriesCore
import Testing

/// Statistics captured from SQLite after query execution.
///
/// These metrics are deterministic for a given query + data set, making them
/// suitable for regression testing without timing variance issues.
public struct QueryStatistics: Sendable, CustomStringConvertible {
  /// Rows examined via full table scan (should be 0 for well-indexed queries)
  public let fullScanSteps: Int

  /// Number of sort operations using temp B-tree (0 if ORDER BY matches index)
  public let sortOperations: Int

  /// Temporary indexes created at runtime (indicates missing index for JOIN)
  public let autoIndexCreations: Int

  /// Total SQLite VM instructions executed (correlates with query complexity)
  public let vmSteps: Int

  /// Rounds a value to the nearest multiple of `nearest`.
  private static func round(_ value: Int, toNearest nearest: Int) -> Int {
    ((value + nearest / 2) / nearest) * nearest
  }

  public var description: String {
    // Round vmSteps to nearest 100 to avoid flaky tests from minor variance
    let roundedVMSteps = Self.round(vmSteps, toNearest: 100)
    return """
    QueryStatistics:
      fullScanSteps: \(fullScanSteps)
      sortOperations: \(sortOperations)
      autoIndexCreations: \(autoIndexCreations)
      vmSteps: ~\(roundedVMSteps)
    """
  }

  /// Whether this query appears to be well-optimized
  public var isEfficient: Bool {
    fullScanSteps == 0 && sortOperations == 0 && autoIndexCreations == 0
  }
}

/// Captures statistics from a SQLite statement after execution.
///
/// Must be called after stepping through all rows but before statement is finalized.
private func captureStatistics(from stmt: OpaquePointer) -> QueryStatistics {
  QueryStatistics(
    fullScanSteps: Int(sqlite3_stmt_status(stmt, SQLITE_STMTSTATUS_FULLSCAN_STEP, 0)),
    sortOperations: Int(sqlite3_stmt_status(stmt, SQLITE_STMTSTATUS_SORT, 0)),
    autoIndexCreations: Int(sqlite3_stmt_status(stmt, SQLITE_STMTSTATUS_AUTOINDEX, 0)),
    vmSteps: Int(sqlite3_stmt_status(stmt, SQLITE_STMTSTATUS_VM_STEP, 0))
  )
}

/// Executes a StructuredQueries statement and captures SQLite execution statistics.
///
/// Use this to detect query efficiency regressions even when the "correct" index is used.
/// For example, detecting when:
/// - A covering index is no longer covering (requires table lookup)
/// - ORDER BY no longer matches index order (requires sort)
/// - Query complexity has increased significantly (more VM steps)
///
/// Example:
/// ```swift
/// try await database.read { db in
///   let query = Post.all.where { $0.feedID.eq(someFeedID) }
///   let stats = try getQueryStatistics(query, in: db)
///
///   #expect(stats.sortOperations == 0, "Query should not require sorting")
///   #expect(stats.fullScanSteps == 0, "Query should not scan rows")
/// }
/// ```
///
/// - Parameters:
///   - query: A StructuredQueries statement to execute
///   - db: The database to execute against
/// - Returns: Statistics about how SQLite executed the query
public func getQueryStatistics(
  _ query: some StructuredQueriesCore.Statement,
  in db: GRDB.Database
) throws -> QueryStatistics {
  let sql = query.query.debugDescription
  let statement = try db.makeStatement(sql: sql)

  // Execute all steps to populate statistics.
  // We use Row.fetchAll which iterates through all results.
  _ = try Row.fetchAll(statement)

  // Capture stats before statement is deallocated
  return captureStatistics(from: statement.sqliteStatement)
}

/// Error thrown when a query efficiency assertion fails
public struct QueryEfficiencyAssertionError: Error, CustomStringConvertible {
  public let message: String

  public var description: String {
    message
  }
}

/// Asserts that a query meets efficiency expectations.
///
/// Use this alongside `assertQueryUsesIndex` to ensure queries remain optimal.
/// While index assertions verify the right index is chosen, efficiency assertions
/// verify the query executes without unexpected overhead.
///
/// Example:
/// ```swift
/// try await database.read { db in
///   // Verify index is used
///   try assertQueryUsesIndex(query, expectedIndex: "idx_posts_feedID", in: db)
///
///   // Verify efficient execution
///   try assertQueryEfficiency(
///     query,
///     in: db,
///     maxSortOperations: 0,  // ORDER BY should use index
///     maxFullScanSteps: 0    // Should use index for filtering
///   )
/// }
/// ```
///
/// - Parameters:
///   - query: A StructuredQueries statement to analyze
///   - db: The database to execute against
///   - maxFullScanSteps: Maximum allowed full scan steps (default: 0)
///   - maxSortOperations: Maximum allowed sort operations (default: 0)
///   - maxAutoIndexes: Maximum allowed auto-created indexes (default: 0)
///   - maxVMSteps: Optional maximum VM steps (no limit if nil)
///   - sourceLocation: Source location for test failure reporting
/// - Returns: The captured statistics for further inspection
@discardableResult
public func assertQueryEfficiency(
  _ query: some StructuredQueriesCore.Statement,
  in db: GRDB.Database,
  maxFullScanSteps: Int = 0,
  maxSortOperations: Int = 0,
  maxAutoIndexes: Int = 0,
  maxVMSteps: Int? = nil,
  sourceLocation: SourceLocation = #_sourceLocation
) throws -> QueryStatistics {
  let sql = query.query.debugDescription
  let stats = try getQueryStatistics(query, in: db)

  var failures: [String] = []

  if stats.fullScanSteps > maxFullScanSteps {
    failures.append("fullScanSteps: \(stats.fullScanSteps) (max: \(maxFullScanSteps))")
  }

  if stats.sortOperations > maxSortOperations {
    failures.append("sortOperations: \(stats.sortOperations) (max: \(maxSortOperations))")
  }

  if stats.autoIndexCreations > maxAutoIndexes {
    failures.append("autoIndexCreations: \(stats.autoIndexCreations) (max: \(maxAutoIndexes))")
  }

  if let maxVM = maxVMSteps, stats.vmSteps > maxVM {
    failures.append("vmSteps: \(stats.vmSteps) (max: \(maxVM))")
  }

  if !failures.isEmpty {
    let message = """
    Query efficiency assertion failed:
      \(failures.joined(separator: "\n  "))

    Full statistics:
    \(stats)

    SQL:
    \(sql)
    """

    Issue.record(
      Comment(rawValue: message),
      sourceLocation: sourceLocation
    )
    throw QueryEfficiencyAssertionError(message: message)
  }

  return stats
}

QueryPlanAssertion.swift

import Foundation
import GRDB
import StructuredQueriesCore
import Testing

/// Error thrown when a query plan assertion fails
public struct QueryPlanAssertionError: Error, CustomStringConvertible {
  public let message: String

  public var description: String {
    message
  }
}

/// Represents a parsed query plan step from SQLite's EXPLAIN QUERY PLAN output
public struct QueryPlanStep: Sendable {
  public let id: Int
  public let parent: Int
  public let detail: String

  public init(id: Int, parent: Int, detail: String) {
    self.id = id
    self.parent = parent
    self.detail = detail
  }

  /// Extracts the index name from the detail string if this step uses an index
  public var indexName: String? {
    // Check for regular named indexes first
    // Match patterns like:
    // "SEARCH TABLE Posts USING INDEX idx_posts_feedID (feedID=?)"
    // "SCAN TABLE Posts USING INDEX idx_posts_date_id"
    // "SEARCH TABLE Posts USING COVERING INDEX idx_posts_feedID_date_id (feedID=?)"
    if let match = detail.firstMatch(of: #/USING (?:COVERING )?INDEX (\w+)/#) {
      return String(match.1)
    }

    // Check for primary key usage
    // "USING INTEGER PRIMARY KEY" or "USING PRIMARY KEY"
    if detail.contains("USING"), detail.contains("PRIMARY KEY") {
      return "PRIMARY KEY"
    }

    return nil
  }

  /// Extracts the table name from the detail string
  public var tableName: String? {
    // Match patterns like:
    // "SEARCH TABLE Posts ..." (older SQLite)
    // "SCAN TABLE MediaItems ..." (older SQLite)
    // "SEARCH posts ..." (newer SQLite without TABLE keyword)
    // "SCAN mediaItems ..." (newer SQLite without TABLE keyword)
    if let match = detail.firstMatch(of: #/(?:SEARCH|SCAN)(?: TABLE)? (\w+)/#) {
      return String(match.1)
    }
    return nil
  }

  /// Whether this step uses an index (any index)
  public var usesIndex: Bool {
    indexName != nil
  }

  /// Whether this step performs a table scan without an index
  public var isTableScan: Bool {
    detail.contains("SCAN") && !usesIndex
  }
}

/// Gets the query plan for a StructuredQueries statement
///
/// - Parameters:
///   - query: A StructuredQueries statement
///   - db: The database to explain the query against
/// - Returns: An array of query plan steps showing how SQLite will execute the query
public func getQueryPlan(
  _ query: some StructuredQueriesCore.Statement,
  in db: GRDB.Database
) throws -> [QueryPlanStep] {
  // Convert the statement to SQL
  let sql = query.query.debugDescription

  // Execute EXPLAIN QUERY PLAN
  let rows = try GRDB.Row.fetchAll(db, sql: "EXPLAIN QUERY PLAN \(sql)")

  return rows.map { row in
    QueryPlanStep(
      id: row["id"],
      parent: row["parent"],
      detail: row["detail"]
    )
  }
}

/// Asserts that a query uses a specific index
///
/// This helper verifies that SQLite's query planner will use the expected index
/// for the given query. This is useful for ensuring that indexes are being used
/// correctly and that query performance doesn't regress.
///
/// Example:
/// ```swift
/// try await withDependencies {
///   $0.defaultDatabase = try appDatabase()
/// } operation: {
///   @Dependency(\.defaultDatabase) var database
///
///   let query = Post.all
///     .where { $0.feedID.eq(Feed.ID()) }
///     .order { $0.date.desc() }
///
///   try await database.read { db in
///     #assertQueryUsesIndex(
///       query,
///       expectedIndex: "idx_posts_feedID_date_id",
///       in: db
///     )
///   }
/// }
/// ```
///
/// - Parameters:
///   - query: A StructuredQueries statement to analyze
///   - expectedIndex: The name of the index that should be used
///   - tableName: Optional table name to filter the query plan steps (useful for joins)
///   - db: The database to explain the query against
///   - sourceLocation: The source location for test failures
@discardableResult
public func assertQueryUsesIndex(
  _ query: some StructuredQueriesCore.Statement,
  expectedIndex: String,
  tableName: String? = nil,
  in db: GRDB.Database,
  sourceLocation: SourceLocation = #_sourceLocation
) throws -> [QueryPlanStep] {
  let plan = try getQueryPlan(query, in: db)

  // Filter by table name if specified (case-insensitive since SQLite lowercases table names in query plans)
  let relevantSteps = if let tableName {
    plan.filter { $0.tableName?.lowercased() == tableName.lowercased() }
  } else {
    plan
  }

  // Check if any step uses the expected index
  let usesExpectedIndex = relevantSteps.contains { step in
    step.indexName == expectedIndex
  }

  guard usesExpectedIndex else {
    let usedIndexes = relevantSteps.compactMap(\.indexName)
    let sql = query.query.debugDescription
    let message = if usedIndexes.isEmpty {
      "Expected query to use index '\(expectedIndex)', but no index was used.\n\nQuery plan:\n\(formatQueryPlan(plan))\n\nSQL:\n\(sql)"
    } else {
      "Expected query to use index '\(expectedIndex)', but used: \(usedIndexes.joined(separator: ", "))\n\nQuery plan:\n\(formatQueryPlan(plan))\n\nSQL:\n\(sql)"
    }

    Issue.record(
      Comment(rawValue: message),
      sourceLocation: sourceLocation
    )
    throw QueryPlanAssertionError(message: message)
  }

  return plan
}

/// Asserts that a query does NOT use a table scan (i.e., it uses an index)
///
/// This is useful when you want to ensure a query is indexed but don't care
/// about the specific index used.
///
/// - Parameters:
///   - query: A StructuredQueries statement to analyze
///   - tableName: Optional table name to check (useful for joins)
///   - db: The database to explain the query against
///   - sourceLocation: The source location for test failures
@discardableResult
public func assertQueryUsesAnyIndex(
  _ query: some StructuredQueriesCore.Statement,
  tableName: String? = nil,
  in db: GRDB.Database,
  sourceLocation: SourceLocation = #_sourceLocation
) throws -> [QueryPlanStep] {
  let plan = try getQueryPlan(query, in: db)

  // Filter by table name if specified (case-insensitive since SQLite lowercases table names in query plans)
  let relevantSteps = if let tableName {
    plan.filter { $0.tableName?.lowercased() == tableName.lowercased() }
  } else {
    plan
  }

  let hasTableScan = relevantSteps.contains { $0.isTableScan }

  guard !hasTableScan else {
    let sql = query.query.debugDescription
    let message = "Expected query to use an index, but found table scan.\n\nQuery plan:\n\(formatQueryPlan(plan))\n\nSQL:\n\(sql)"
    Issue.record(
      Comment(rawValue: message),
      sourceLocation: sourceLocation
    )
    throw QueryPlanAssertionError(message: message)
  }

  return plan
}

/// Formats a query plan for readable output in test failures
private func formatQueryPlan(_ plan: [QueryPlanStep]) -> String {
  plan.map { step in
    let indent = String(repeating: "  ", count: step.parent)
    return "\(indent)\(step.id): \(step.detail)"
  }.joined(separator: "\n")
}

/// A summary of indexes used by a query, suitable for snapshot testing.
///
/// This provides a stable, human-readable representation of which indexes
/// SQLite chose for a query, making it easy to detect changes in query planning.
public struct QueryIndexSummary: Sendable, CustomStringConvertible {
  /// List of (tableName, indexName) pairs showing which index is used for each table
  public let indexes: [(table: String, index: String)]

  public var description: String {
    if indexes.isEmpty {
      return "No indexes used (full table scans)"
    }
    return indexes
      .sorted { $0.table < $1.table }
      .map { "\($0.table): \($0.index)" }
      .joined(separator: "\n")
  }

  public init(indexes: [(table: String, index: String)]) {
    self.indexes = indexes
  }
}

/// Extracts a summary of indexes used by a query for snapshot testing.
///
/// Returns a `QueryIndexSummary` showing which index is used for each table access.
/// This is useful for inline snapshot tests to detect when query planning changes.
///
/// Example:
/// ```swift
/// let summary = try getQueryIndexes(query, in: db)
/// assertInlineSnapshot(of: summary, as: .description) {
///   """
///   MediaItems: idx_mediaItems_postDate_postOrder
///   Posts: idx_posts_feedID_date_id
///   """
/// }
/// ```
public func getQueryIndexes(
  _ query: some StructuredQueriesCore.Statement,
  in db: GRDB.Database
) throws -> QueryIndexSummary {
  let plan = try getQueryPlan(query, in: db)

  let indexes = plan.compactMap { step -> (table: String, index: String)? in
    guard let table = step.tableName else { return nil }

    if let index = step.indexName {
      return (table, index)
    } else if step.isTableScan {
      return (table, "TABLE SCAN")
    }
    return nil
  }

  return QueryIndexSummary(indexes: indexes)
}