Perfect-CRUD 3.0.0

The first release since 2.0.0 (January 2020). It brings the package up to Swift 6, adds connection pooling, async entry points, migrations and a dynamic (runtime) query API, and fixes a long list of correctness bugs in key path resolution, joins, optional foreign keys and table-structure caching. It is a major version: there are source-breaking changes and several queries that used to return wrong or empty results now throw instead. Read "Breaking changes and behaviour changes" before upgrading.

Requirements

• Swift tools 6.2 (// swift-tools-version: 6.2); 2.0.0 declared 5.1.
• Platforms: macOS 12+ (was 10.15+), iOS 15+ (newly declared), Linux.
• Still no external SwiftPM dependencies.

.package(url: "https://github.com/PerfectlySoft/Perfect-CRUD.git", from: "3.0.0")

CI now runs on GitHub Actions for every push and pull request: Linux (Swift 6.2 and 6.4) and macOS (Xcode 26.0.1 and 26.6), each in debug and release, plus the tests on an iOS Simulator at the iOS 15 deployment target, with warnings treated as errors. This delivers the CI promised when #67 was closed.

Highlights

• Swift 6 and Sendable (#70). The library builds cleanly in the Swift 6 language mode. The query-builder types (Database, Table, Where, Join, JoinPivot, Ordering, Limit) are conditionally Sendable, and CRUDExpression, the logging types, TableCreatePolicy and the foreign-key action types are Sendable. The global table-structure cache is now lock-protected (it was an unsynchronised dictionary that could be corrupted under concurrent create()/index() calls).
• Connection pooling (#70). DatabaseConnectionPool<C> is an actor that hands out connections through withConnection { db in … }, or manually through acquire() / release(_:), with configurable minimum and maximum sizes and prewarm().
• Async entry points (#70). Async-suffixed methods (sqlAsync, transactionAsync, firstAsync, insertAsync, updateAsync, deleteAsync) and fetchAll() run the existing blocking connector work on a dedicated dispatch queue so it doesn't block Swift's cooperative thread pool. The synchronous API is unchanged and remains the primary surface. Cancellation is best-effort: a query already running in the C library is not interrupted.
• Nested transactions and isolation levels (#70). transaction called inside a transaction on the same Database value now uses SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT. A new isolation: parameter takes a TransactionIsolationLevel; it applies only at the outermost level and only on connectors that implement SQLGenDelegate.setIsolationLevelSQL(_:) (the default is a no-op). No PerfectlySoft connector implements it yet, so for now isolation: has no effect. Connector authors: the isolation SQL is sent before BEGIN, which suits MySQL's SET TRANSACTION; on PostgreSQL a SET TRANSACTION outside a transaction block only warns, so a PostgreSQL implementation needs a different approach.
• Migrations (#70). DatabaseMigrator<C> applies registered migrations in registration order, records them in a perfectcrud_migrations table, and can roll back the most recent one when it has an explicit down. It sits on top of TableCreatePolicy.reconcileTable rather than replacing it.
• Dynamic queries (#70). Database gains select(_: DynamicQuery), mutate(_: DynamicMutation) and execute(_: DynamicSQL) for queries whose table and field names are only known at runtime, returning DynamicResult / DynamicRow / DynamicValue. Reading dynamic rows needs connector support (SQLExeDelegate.nextDynamicRow()); the default implementation throws. execute rejects what looks like multiple statements unless allowsMultipleStatements is set.
• CRUDTableStructure() is public (#70), so host modules can inspect a model's table structure.
• Comparisons on Optional columns (#71). <, <=, > and >= accept key paths to Optional columns of String, Double, Bool, UUID, Date and every Int/UInt width. The right-hand side must be non-optional; rows whose column is NULL never match.
• Optional @ForeignKey / @PrimaryKey columns work (#75, #79). @ForeignKey(...) var parentId: Int? now creates a nullable column of the wrapped type, binds NULL, can be filtered with ==, == nil and != nil, and decodes from JSON with the key absent. ON DELETE SET NULL now works.
• Joins on optional keys (#82). New join overloads accept an optional key on either side (for example id: Int against parentId: Int?), so parent/child relations and trees with nullable foreign keys can be joined. A NULL key matches nothing.
• Self-referencing models (#74, #81). A @ForeignKey to the same type (or a cycle of types) and a Codable model that contains an optional of its own type (var next: Node?) no longer overflow the stack.

Breaking changes and behaviour changes

Source and build changes

• Toolchain and platforms. Swift tools 6.2 is required and the minimum macOS is 12. Upgrade: use a Swift 6.2 or newer toolchain and raise your own deployment target to at least macOS 12 / iOS 15.
• Expression typealias removed (#71). It collided with Foundation's Expression when building against the macOS 15 SDK or later, making an unqualified Expression ambiguous. Upgrade: replace Expression with CRUDExpression; it is the same type.
• CRUDLogDestination.custom takes a @Sendable closure (#70). Upgrade: make the closure you pass @Sendable (capture only Sendable state).
• Foreign-key action providers' action is now static let (#70), not static var. Upgrade: only code that assigned to ForeignKeyActionCascade.action (etc.) is affected; stop mutating it.
• Connector protocols gained requirements, all with default implementations (#70): SQLGenDelegate.setIsolationLevelSQL(_:) (default nil), and SQLExeDelegate.nextDynamicRow() (default throws), affectedRowCount() (default 0) and lastInsertedID() (default nil). Existing connectors compile unchanged. Upgrade (connector authors): implement these to support isolation levels and the dynamic API.
• Custom decode overloads for the wrappers (#79). New public KeyedDecodingContainer.decode overloads for PrimaryKey<V?> and ForeignKey<T, D, U, V?> are now preferred over a package's own overload written generically over WrappedCodableProvider. Upgrade: if you had such an overload, it is no longer called for optional-wrapping primary and foreign keys.

Runtime behaviour changes

• Nested transaction uses savepoints (#70). Top-level transactions still emit exactly BEGIN / COMMIT / ROLLBACK. A nested call on the same Database value used to issue a second BEGIN; it now opens a savepoint, and an error in the inner body rolls back only to that savepoint. Upgrade: if you relied on an inner failure rolling back the whole outer transaction, rethrow from the outer body (the inner error does propagate unless you catch it).
• Optional @ForeignKey / @PrimaryKey column schema (#75). These columns are now created as nullable columns of the wrapped type; in 2.0.0 they came out as json NOT NULL (MySQL, where CREATE TABLE then failed), jsonb NOT NULL (PostgreSQL) or TEXT NOT NULL (SQLite). .reconcileTable only adds and drops columns, so existing tables keep the old column. Upgrade: recreate or ALTER those columns. Separately, encoding an unassigned non-optional @ForeignKey now throws from encode(to:) instead of trapping (reads and insert/update still trap).
• Missing optional wrapper columns read as nil (#79). If a query result lacks the column for an optional @ForeignKey / @PrimaryKey, the value now reads nil, as a plain optional does (Perfect-SQLite and Perfect-PostgreSQL used to throw). Encoding is unchanged: a nil wrapped value still writes an explicit null.
• Key paths into nested properties throw (#76). \T.sub?.x, \T.addr.id and similar used to resolve silently to one of the table's own columns (often the primary key), in where, the optional comparisons, order(by:) and insert/update setKeys/ignoreKeys. They now throw CRUDSQLGenError. A key path to a property init(from:) never decodes also throws. One accepted false positive: a decoded non-Bool column derived so that shifting its input by one either way gives the same value (for example rounding to a multiple of 3) is reported as undecoded. Upgrade: filter or order only on top-level stored columns.
• Same-typed Codable columns resolve correctly, or throw (#77). Two join arrays of one element type (kids and pets: [Kid]?) used to make \Parent.pets mean kids, so the join filled the wrong property. They now resolve to their own column. Some pairs that can't be told apart now throw for both columns (two non-optional empty structs, two non-optional dictionaries, two non-optional arrays of a String raw-value enum), as does a computed property of the shared type. Upgrade: if one of these now throws, give the columns distinguishable types or drop the key path.
• Unresolvable join targets throw (#78). A join whose target key path can't be resolved made select() return [] and first() return nil. Both now throw CRUDSQLGenError naming the key path. count() is unaffected.
• Joining the same key path twice throws (#80). This trapped in 2.0.0 (a duplicate-key crash); select() now throws instead.
• Unsupported join key types throw when the query is built (#82), including from count(), which used to succeed because joins don't affect the count SQL.
• Unlinked pivot rows no longer attach to a parent (#82). A pivot-joined row that no parent links to has a NULL pivot id. Going by the connector sources (not reproduced against live servers), Perfect-SQLite and Perfect-PostgreSQL would decode it as 0, "" or false and attach the row to a parent with that key, and Perfect-MySQL would throw. Such rows now match nothing.
• A where on a type joined more than once throws (#84). When a type appears in a query more than once (two joins of one type, or a pivot table that is also a joined type) and is not the master type, a where on it used to mean the first table of that type in every statement. It now throws an ambiguity CRUDSQLGenError from select() and from count(), which built valid SQL with first-table semantics in 2.0.0. A where on the master's own type still means the master table, including in self-joins. Upgrade: there is no API to say which join a key path means; drop that where or restructure the query.
• Orderings on self-joins sort the children (#84). .order(by:) after a self-join or self-pivot join sorted the joined rows by the parent's column; it now sorts the joined table's own rows. Combined with .limit() on that join, this can change which rows are returned. More generally, an ordering now always applies to the statement of the table or join it follows.

Fixes

• Release builds with Swift 6.2 and 6.3 (Xcode 26.x) no longer crash computing the table structure of a model with an array property, which every join uses. The compiler mishandled the array-element existential; it's now opened through a generic function. Seen with Xcode 26.3 (Swift 6.2.4) and on CI with Xcode 26.0.1 and 26.6 (Swift 6.3); Swift 6.4 and Linux builds weren't affected.
• #70: The table-structure cache is lock-protected against concurrent access; a primitive-typed @ForeignKey column read back through a connector no longer fails with "Unsupported type".
• #71: Comparison operators on Optional columns; Expression alias removed; tests pin down subclass models that call super.init(from:) with the same decoder.
• #72: The table-structure cache is keyed by type identity rather than unqualified name, so same-named nested, function-local or cross-module types no longer share a structure. SQL table names are unchanged.
• #73: The cache stores only canonical structures, so primaryKey: is honoured after a cache hit and a type first seen as a sub-table no longer caches a structure missing its own sub-tables.
• #74: Foreign-key cycles among a type's own columns (self-references, mutual references) no longer overflow the stack.
• #75: @ForeignKey / @PrimaryKey wrapping an Optional becomes a nullable column and can be inserted, updated, filtered and read.
• #76: Key paths through nested or optional-chained properties throw instead of resolving to the wrong column (and no longer crash on an undecoded UUID).
• #77: Same-typed columns that carry no decoded values (such as two join arrays of one element type) resolve to their own column.
• #78: select() / first() throw when a join's target key path can't be resolved, instead of returning no rows.
• #79: Optional @ForeignKey / @PrimaryKey decode from an absent key, like a plain optional.
• #80: A pivot join followed by a plain join returns rows; select statements are paired with the right tables.
• #81: Self-referential Codable models no longer overflow the stack in the column-name and key-path decoders.
• #82: Joins on optional keys compile and return rows; a NULL key matches nothing.
• #83: A where on a pivot-joined type joins its pivot table; a where on the pivot type works; a where on a joined TableNameProvider type uses its table name.
• #84: WHERE key paths on a type with more than one table resolve explicitly (or throw); ORDER BY applies to its own statement's table.

Upgrade notes

1. Point your dependency at from: "3.0.0" and build with Swift 6.2 or newer; raise deployment targets to macOS 12 / iOS 15.
2. Replace any Expression with CRUDExpression, and mark custom log closures @Sendable.
3. If you use optional @ForeignKey / @PrimaryKey properties, recreate or alter the affected columns in existing databases; .reconcileTable won't change them.
4. Run your query tests. Queries that previously returned wrong or empty results because of a bad key path, an unresolvable join or an ambiguous where now throw CRUDSQLGenError; treat each new throw as a query that was already wrong.
5. Check any .order(by:) placed after a self-join, especially with .limit().
6. If you nest transaction calls, confirm the savepoint semantics suit you.
7. Connector authors: the new SQLGenDelegate / SQLExeDelegate requirements have defaults, so implement them only to support isolation levels and the dynamic API.

Known limitations that remain: Select's iterator still logs and swallows errors raised while decoding rows (some cases above now throw earlier, before iteration); creating two tables that reference each other needs database-specific steps (MySQL/MariaDB foreign_key_checks, PostgreSQL ALTER TABLE); key paths past a self-referential cut (\Node.next?.next) can't be resolved.

Legacy

The pre-Swift-6 code is preserved on the legacy branch, which points at the same commit as the 2.0.0 tag. Stay on 2.0.0 if you can't move to Swift 6.2 yet.