# Perfect CRUD
CRUD is an object-relational mapping (ORM) system for Swift, built on Swift's `Codable` and KeyPath
features. It maps `Codable` types to SQL tables, and can create tables, insert, update, select, and
join, all in a type-safe manner — no additional dependencies, generics and KeyPaths catch misuse at
compile time.
**This package has been modernized for Swift 6**: `Sendable` conformance throughout for strict
concurrency, plus a new "Dynamic Select" runtime query API. It's foundational — [Perfect-MySQL](https://github.com/PerfectlySoft/Perfect-MySQL),
[Perfect-MariaDB](https://github.com/PerfectlySoft/Perfect-MariaDB), [Perfect-PostgreSQL](https://github.com/PerfectlySoft/Perfect-PostgreSQL),
[Perfect-SQLite](https://github.com/PerfectlySoft/Perfect-SQLite), [Perfect-NIO](https://github.com/PerfectlySoft/Perfect-NIO),
and [PerfectTemplate](https://github.com/PerfectlySoft/PerfectTemplate) all depend on it directly.
The pre-Swift-6 version of this package (2.0.0 and earlier) is preserved on the [`legacy`](../../tree/legacy) branch.
**Requirements:** Swift 6.2 or newer. macOS 12+, iOS 15+ and Linux; CI runs the tests on Linux (Swift
6.2 and 6.4, Ubuntu 24.04) and macOS (Xcode 26.0.1 and 26.6) in debug and release, and on the iOS
Simulator.
```swift
dependencies: [
.package(url: "https://github.com/PerfectlySoft/Perfect-CRUD.git", from: "3.1.0")
],
targets: [
.target(name: "MyTarget", dependencies: [
.product(name: "PerfectCRUD", package: "Perfect-CRUD")
])
]
```
Then build and test with the standard SwiftPM commands: `swift build`, `swift test`.
To use CRUD with a specific database, add the connector of your choice:
```swift
.package(url: "https://github.com/PerfectlySoft/Perfect-PostgreSQL.git", from: "5.0.0")
.package(url: "https://github.com/PerfectlySoft/Perfect-MySQL.git", from: "4.0.0")
.package(url: "https://github.com/PerfectlySoft/Perfect-MariaDB.git", from: "4.0.0")
.package(url: "https://github.com/PerfectlySoft/Perfect-SQLite.git", from: "6.0.0")
```
CRUD support is built directly into each connector package. These connector releases depend on CRUD
`from: "3.0.1"`, so SwiftPM picks up 3.1.0 with them; list CRUD itself `from: "3.1.0"` as above to
use `all()` and the other 3.1.0 additions.
## Upgrading to 3.1
3.1.0 adds throwing ways to read a query's results (see [Reading results](#reading-results)).
Existing code keeps compiling, with one exception: if your app declares its own `all()`,
`forEachRow(_:)` or `makeThrowingIterator()` on `Select`, or on `Sequence` (which `Select` conforms
to), calls to it on a `Select` now become ambiguous or resolve to CRUD's throwing method and need
`try`. Rename yours. Otherwise the changes are errors that used to be lost, and results that used to
be wrong:
* `first()`, `firstAsync()` and `fetchAll()` now throw when the query fails or a joined table's rows
can't be decoded, and `fetchAll()` throws when any row can't be decoded. They used to return `nil`
or the rows read before the failure.
* `first()` on a join returns the parent with all of its joined objects. It used to apply its
`LIMIT 1` to the joined table, so the parent got at most one child (often none, when the first
child row belonged to another parent). It still reads a single parent row, and now only that
parent's joined objects. With joins and no `order`, it returns the parent with the lowest join key. Where that key can be NULL, MySQL and SQLite sort NULLs first, so the result can be a parent with no joined objects; PostgreSQL sorts them last. Add an `order` if that matters.
* After a `SelectIterator` returns `nil` or throws, it keeps returning `nil`. Some connectors used to
run the query again, or carry on past a row that failed to decode.
* `DatabaseMigrator.migrate()` throws if a migration record can't be read, instead of running the
migrations recorded after it again (see [Migrations](#migrations) to recover).
* `for row in select` behaves as before: it logs an error and stops. Switch to `try select.all()` or
`try select.forEachRow { … }` where losing rows silently isn't acceptable.
## Migrating from 2.x
3.0.0 is a major release. The [release notes](../../releases/tag/3.0.0) list every change; the ones
most likely to need attention when upgrading:
* **Toolchain and platforms.** Swift tools 6.2 is required, and the minimum macOS is now 12 (was 10.15).
* **`Expression` is gone.** The `Expression` typealias collided with Foundation's `Expression` on the
macOS 15+ SDK. Use `CRUDExpression`, the same type.
* **`Sendable`.** A `CRUDLogDestination.custom` closure must be `@Sendable`.
* **Nested transactions.** A `transaction` inside another on the same `Database` value now uses a
savepoint instead of a second `BEGIN`, so an inner failure rolls back only to its savepoint. The
error still propagates, and rolls back the outer transaction too unless the outer body catches it.
* **Optional `@ForeignKey` / `@PrimaryKey`.** These are now nullable columns of the wrapped type, not
JSON/text columns. Existing tables keep the old column (`.reconcileTable` won't change it): recreate
or alter them.
* **Queries that were silently wrong now throw `CRUDSQLGenError`.** This covers key paths through nested or
optional-chained properties (`\T.sub?.x`) or to properties `init(from:)` never decodes; a join
whose target key path can't be resolved (`select()` / `first()` used to return nothing); joining the
same key path twice (used to trap); an unsupported join key type (now thrown while the query is
built, `count()` included); and a `where` on a type that appears more than once in the query and
isn't the master's type (2.0.0's `count()` silently used the first table of that type).
* **Self-join orderings.** `.order(by:)` after a self-join or self-pivot join now sorts the joined rows
by their own column, not the parent's, which can change the rows returned with `.limit()`.
* **Connector authors.** `SQLGenDelegate` gained `setIsolationLevelSQL(_:)`, and `SQLExeDelegate`
gained `nextDynamicRow()`, `affectedRowCount()` and `lastInsertedID()`. All have default
implementations; implement them to support isolation levels and Dynamic Select.
# Contents
* General Usage
* Operations
* Database
* Transaction
* Create
* Policy
* Table
* SQL
* Dynamic Select
* Connection Pooling
* Async Execution
* Migrations
* Table
* Index
* Join
* Parent Child
* Many to Many
* Self Join
* Junction Join
* Where
* Expression Operators
* Order
* Limit
* Update
* Insert
* Delete
* Select
* Reading results
* Count
* Database Specific Operations
* MySQL
* Last Insert Id
* SQLite
* Last Insert Id
* PostgreSQL
* Returning
* Codable Types
* Identity
* Error Handling
* Logging
* License
## General Usage
This is a simple example to show how CRUD is used.
```swift
// CRUD can work with most Codable types.
struct PhoneNumber: Codable {
let personId: UUID
let planetCode: Int
let number: String
}
struct Person: Codable {
let id: UUID
let firstName: String
let lastName: String
let phoneNumbers: [PhoneNumber]?
}
// CRUD usage begins by creating a database connection.
// The inputs for connecting to a database will differ depending on your client library.
// Create a `Database` object by providing a configuration.
// These examples will use SQLite for demonstration purposes,
// but all code would be identical regardless of the datasource type.
let db = Database(configuration: try SQLiteDatabaseConfiguration(testDBName))
// Create the table if it hasn't been done already.
// Table creates are recursive by default, so "PhoneNumber" is also created here.
try db.create(Person.self, policy: .reconcileTable)
// Get a reference to the tables we will be inserting data into.
let personTable = db.table(Person.self)
let numbersTable = db.table(PhoneNumber.self)
// Add an index for personId, if it does not already exist.
try numbersTable.index(\.personId)
// Insert some sample data.
do {
// Insert some sample data.
let owen = Person(id: UUID(), firstName: "Owen", lastName: "Lars", phoneNumbers: nil)
let beru = Person(id: UUID(), firstName: "Beru", lastName: "Lars", phoneNumbers: nil)
// Insert the people
try personTable.insert([owen, beru])
// Give them some phone numbers
try numbersTable.insert([
PhoneNumber(personId: owen.id, planetCode: 12, number: "555-555-1212"),
PhoneNumber(personId: owen.id, planetCode: 15, number: "555-555-2222"),
PhoneNumber(personId: beru.id, planetCode: 12, number: "555-555-1212")])
}
// Perform a query.
// Let's find all people with the last name of Lars which have a phone number on planet 12.
let query = try personTable
.order(by: \.lastName, \.firstName)
.join(\.phoneNumbers, on: \.id, equals: \.personId)
.order(descending: \.planetCode)
.where(\Person.lastName == "Lars" && \PhoneNumber.planetCode == 12)
.select()
// Loop through the results and print the names. `forEachRow` throws if the query fails or a row
// can't be decoded; a plain `for user in query` would log the error and stop instead.
try query.forEachRow { user in
// We joined PhoneNumbers, so we should have values here.
guard let numbers = user.phoneNumbers else {
return
}
for number in numbers {
print(number.number)
}
}
```
## Operations
Activity in CRUD is accomplished by obtaining a database connection object and then chaining a series of operations on that database. Some operations execute immediately while others (select) are executed lazily. Each operation that is chained will return an object which can be further chained or executed.
Operations are grouped here according to the objects which implement them. Note that many of the type definitions shown below have been abbreviated for simplicity and some functions implemented in extensions have been moved in to keep things in a single block.
Note: several usage examples below use `XCTAssertEqual`/`XCTFail`-style assertions purely for illustration. The package's own test target (`Tests/PerfectCRUDTests`) uses Swift Testing (`import Testing`, `@Test`), not XCTest — if you're copying an example into a test, adapt it to Swift Testing's `#expect`/`#require` macros rather than pasting it verbatim.
### Database
A Database object wraps and maintains a connection to a database. Database connectivity is specified by using a `DatabaseConfigurationProtocol` object. These will be specific to the database in question.
```swift
// postgres sample configuration
let db = Database(configuration:
try PostgresDatabaseConfiguration(database: postgresTestDBName, host: "localhost"))
// sqlite sample configuration
let db = Database(configuration:
try SQLiteDatabaseConfiguration(testDBName))
// MySQL sample configuration
let db = Database(configuration:
try MySQLDatabaseConfiguration(database: "testDBName", host: "localhost", username: "username", password: "password"))
```
Database objects implement this set of logical functions:
```swift
public struct Database: DatabaseProtocol {
public typealias Configuration = C
public let configuration: Configuration
public init(configuration c: Configuration)
public func table(_ form: T.Type) -> Table>
public func transaction(_ body: () throws -> T) throws -> T
public func create(_ type: A.Type,
policy: TableCreatePolicy = .defaultPolicy) throws -> Table
public func create(_ type: A.Type,
primaryKey: KeyPath? = nil,
policy: TableCreatePolicy = .defaultPolicy) throws -> Table
}
```
The operations available on a Database object include `transaction`, `create`, and `table`.
#### Transaction
The `transaction` operation will execute the body between a set of "BEGIN" and "COMMIT" or "ROLLBACK" statements. If the body completes execution without throwing an error then the transaction will be committed, otherwise it is rolled-back.
```swift
public extension Database {
func transaction(isolation: TransactionIsolationLevel? = nil, _ body: () throws -> T) throws -> T
}
```
Transactions nest. A `transaction` called inside another on the same `Database` value runs between `SAVEPOINT` and `RELEASE SAVEPOINT`, and a failure rolls back only to that savepoint. The error is still rethrown, so the outer transaction rolls back as well unless its body catches the error.
`isolation` (`.readUncommitted`, `.readCommitted`, `.repeatableRead`, `.serializable`) applies only to the outermost transaction, and only on connectors that implement `SQLGenDelegate.setIsolationLevelSQL(_:)`; on the others it's ignored. None of the PerfectlySoft connectors implement it yet.
Example usage:
```swift
try db.transaction {
... further operations
}
```
The body of a transaction may optionally return a value.
```swift
let value = try db.transaction {
... further operations
return 42
}
```
#### Create
The `create` operation is given a Codable type. It will create a table corresponding to the type's structure. The table's primary key can be indicated as well as a "create policy" which determines some aspects of the operation.
```swift
public extension DatabaseProtocol {
func create(
_ type: A.Type,
policy: TableCreatePolicy = .defaultPolicy) throws -> Table
func create(
_ type: A.Type,
primaryKey: KeyPath? = nil,
policy: TableCreatePolicy = .defaultPolicy) throws -> Table
}
```
There are two overloads: one with no `primaryKey` parameter, and one generic over the primary key's value type `V` taking a typed `KeyPath?`. Both return a `Table`, not a `Create` value — `Create` is used internally to build and execute the `CREATE TABLE` statement, but it is never the type a caller sees.
Example usage:
```swift
try db.create(TestTable1.self, primaryKey: \.id, policy: .reconcileTable)
```
`TableCreatePolicy` consists of the following options:
* .reconcileTable - If the database table already exists then any columns which differ between the type and the table will be either removed or added. Note that this policy will not alter columns which exist but have changed their types. For example, if a type's property changes from a String to an Int, this policy will not alter the column changing its type to Int.
* .shallow - If indicated, then joined type tables will not be automatically created. If not indicated, then any joined type tables will be automatically created.
* .dropTable - The database table will be dropped before it is created. This can be useful during development and testing, or for tables that contain ephemeral data which can be reset after a restart.
Calling create on a table which already exists is a harmless operation resulting in no changes unless the `.reconcileTable` or `.dropTable` policies are indicated. Existing tables will not be modified to match changes in the corresponding Codable type unless `.reconcileTable` is indicated.
#### Table
The `table` operation returns a Table object based on the indicated Codable type. Table objects are used to perform further operations.
```swift
public protocol DatabaseProtocol {
func table(_ form: T.Type) -> Table
}
```
Example usage:
```swift
let table1 = db.table(TestTable1.self)
```
#### SQL
CRUD can also execute bespoke SQL statements, mapping the results to an array of any suitable Codable type.
```swift
public extension Database {
func sql(_ sql: String, bindings: Bindings = []) throws
func sql(_ sql: String, bindings: Bindings = [], _ type: A.Type) throws -> [A]
}
```
Example Usage:
```swift
try db.sql("SELECT * FROM mytable WHERE id = 2", TestTable1.self)
```
#### Dynamic Select
CRUD's primary API remains the type-safe `Codable`/KeyPath query builder. For
runtime-driven use cases such as template engines, admin tools, query builders,
and legacy adapters, CRUD also exposes a dynamic read API that keeps the same
connector quoting, binding, logging, and execution machinery.
```swift
let result = try db.select(DynamicQuery(
table: "products",
fields: ["id", "sku", "name"],
predicates: [
DynamicPredicate(
field: "active",
comparison: .equal,
value: .int(1)
),
DynamicPredicate(
field: "name",
comparison: .contains,
value: .string("jacket")
),
],
orderings: [
DynamicOrdering(field: "name")
],
limit: 25
))
for row in result.rows {
print(row["sku"] ?? .null)
}
```
The dynamic API is intentionally a sibling of the typed API, not a replacement
for it. Runtime table and column names are still quoted through the active SQL
generator, and runtime values are still bound instead of interpolated.
Connectors support dynamic rows by implementing:
```swift
func nextDynamicRow() throws -> DynamicRow?
```
The default implementation throws, so connectors opt in explicitly.
#### Connection Pooling
`DatabaseConnectionPool` maintains a pool of connections for a given `DatabaseConfigurationProtocol`, checked out for the duration of a unit of work and returned automatically afterward.
```swift
let pool = try DatabaseConnectionPool(
configuration: .init(minConnections: 1, maxConnections: 5),
makeConnection: { try MySQLDatabaseConfiguration(database: "mydb", host: "localhost") }
)
try await pool.prewarm() // optional: opens minConnections up front instead of on first use
// Safe, high-level API: checks a connection out, runs body, always checks it back in
// (even on throw or task cancellation).
let people = try await pool.withConnection { db in
try db.table(Person.self).select().map { $0 }
}
```
`withConnection(_:)`'s body closure must be `Sendable`, along with everything it captures and returns — if that's too restrictive for a particular call site (e.g. building a route handler where the surrounding value isn't `Sendable`), use the manual pairing instead:
```swift
let connection = try await pool.acquire()
defer { Task { await pool.release(connection) } } // release in every exit path — see below
let db = Database(configuration: connection)
// ... use db ...
```
Nothing enforces that `release(_:)` is actually called exactly once with the connection that was acquired — `withConnection(_:)` is the safer default; only reach for manual acquire/release when a genuine `Sendable` constraint forces it (this is exactly why `PerfectNIOCRUD`'s `Routes.db(pool:)`/`.table(pool:)` route overloads use the manual pair internally instead of `withConnection`).
#### Async Execution
CRUD's synchronous API (`select()`, `insert()`, `sql()`, etc.) is still the primary surface, and connector work is always blocking under the hood — there is no async I/O in libmysqlclient/libpq/sqlite3. Calling it directly from inside an `async` function (a PerfectNIO route handler, for example) blocks whatever thread is running that task, which must never happen on Swift's cooperative thread pool.
A parallel family of `Async`-suffixed methods routes that same blocking work through a dedicated executor instead:
```swift
try await db.sqlAsync("DELETE FROM sessions WHERE expires_at < NOW()")
let user = try await table.firstAsync()
try await table.insertAsync(newUser)
try await table.updateAsync(updatedUser)
try await table.deleteAsync()
let all = try await table.fetchAll() // no sync equivalent to collide with, so no "Async" suffix
try await db.transactionAsync { try /* ...synchronous CRUD calls... */ }
```
These are suffixed rather than plain overloads of the existing sync method names — an identically-named `async` overload anywhere in scope forces `await` at every call site even for a logically-unrelated sync call, which would have silently broken every existing synchronous call inside an already-`async` context. Reach for the `Async` versions specifically when you're calling CRUD from inside `async` code; the plain synchronous API is unchanged and still correct to use from a synchronous context (a CLI tool, a script, a background thread you already own).
#### Migrations
`DatabaseMigrator` is a versioned migration system layered on top of (not replacing) `TableCreatePolicy.reconcileTable` — an individual migration is free to call `db.create(_:policy: .reconcileTable)` for a simple additive column change, or drop to `db.sql("ALTER TABLE ...")` for anything `reconcileTable` can't express.
```swift
let migrator = DatabaseMigrator()
try migrator.register("2026_07_create_posts", up: { db in
try db.create(Post.self, policy: .shallow)
}, down: { db in
try db.sql("DROP TABLE IF EXISTS \(Post.CRUDTableName)")
})
try migrator.register("2026_08_add_posts_published_flag", up: { db in
try db.sql("ALTER TABLE \(Post.CRUDTableName) ADD COLUMN published INTEGER DEFAULT 0")
})
// register every migration once, at startup, before calling migrate(_:)
try migrator.migrate(db) // applies every registered migration not yet recorded, in
// registration order (not a lexical/timestamp sort on the
// identifier), each inside its own transaction
try migrator.rollbackLast(db) // rolls back only the most recently applied migration; throws
// if it has no `down` — a reverse is never auto-inferred
```
A migration's `identifier` must be unique — `register` throws if it's already registered, since migrations are meant to be a fixed, reviewed sequence rather than silently overwritable. Applied migrations are tracked in a `perfectcrud_migrations` table, created automatically on first `migrate(_:)` call. Running `migrate(_:)` concurrently from every instance of a horizontally-scaled service against one shared network database is not safe — run it from exactly one process/deploy step for a network connector (SQLite's single-writer semantics make this less of a concern there).
`migrate(_:)` reads only the `identifier` column of `perfectcrud_migrations`. If the table can't be read (for example a column with an unexpected type), it throws a `CRUDSQLExeError` that names the table, and runs no migrations: it can't tell which ones were applied. To recover, look at the table, then fix that row's `identifier`, or delete the row if that migration should run again, and call `migrate(_:)` again. `rollbackLast(_:)` reads `appliedAt` too, so a row whose `appliedAt` can't be read blocks it until the row is fixed.
### Table
**Table** can follow: `Database`.
**Table** supports: `update`, `insert`, `delete`, `join`, `order`, `limit`, `where`, `select`, and `count`.
A Table object can be used to perform updates, inserts, deletes or selects. Tables can only be accessed through a database object by providing the Codable type which is to be mapped. A table object can only appear in an operation chain once, and it must be the first item.
Table objects are parameterized based on the Swift object type you provide when you retrieve the table. Tables indicate the over-all resulting type of any operation. This will be referred to as the *OverAllForm*.
Example usage:
```swift
// get a table object representing the TestTable1 struct
// any inserts, updates, or deletes will affect "TestTable1"
// any selects will produce a collection of TestTable1 objects.
let table1 = db.table(TestTable1.self)
```
In the example above, TestTable1 is the OverAllForm. Any destructive operations will affect the corresponding database table. Any selects will produce a collection of TestTable1 objects.
#### Index
**Index** can follow: `table`.
Database indexes are important for good query performance. Given a table object, a database index can be added by calling the `index` function. Indexes should be added along with the code which creates the table.
The `index` function accepts one or more table keypaths.
Example usage:
```swift
struct Person: Codable {
let id: UUID
let firstName: String
let lastName: String
}
// create the Person table
try db.create(Person.self)
// get a table object representing the Person struct
let table = db.table(Person.self)
// add index for lastName column
try table.index(\.lastName)
// add unique index for firstName & lastName columns
try table.index(unique: true, \.firstName, \.lastName)
```
Indexes can be created for individual columns, or for columns as a group. If multiple columns are frequenty used together in queries, then it can often improve performance by adding indexes including those columns.
By including the `unique: true` parameter, a unique index will be created, meaning that only one row can contain any possible column value. This can be applied to multiple columns, as seen in the example above. Consult your specific database's documentation for the exact behaviours of database indexes.
The `index` function is defined as:
```swift
public extension Table {
func index(unique: Bool = false, _ keys: PartialKeyPath...) throws -> Index
}
```
### Join
**Join** can follow: `table`, `order`, `limit`, or another `join`.
**Join** supports: `join`, `where`, `order`, `limit`, `select`, `count`.
A `join` brings in objects from another table in either a parent-child or many-to-many relationship scheme.
Parent-child example usage:
```swift
struct Parent: Codable {
let id: Int
let children: [Child]?
}
struct Child: Codable {
let id: Int
let parentId: Int
}
try db.transaction {
try db.create(Parent.self, policy: [.shallow, .dropTable]).insert(
Parent(id: 1, children: nil))
try db.create(Child.self, policy: [.shallow, .dropTable]).insert(
[Child(id: 1, parentId: 1),
Child(id: 2, parentId: 1),
Child(id: 3, parentId: 1)])
}
let join = try db.table(Parent.self)
.join(\.children,
on: \.id,
equals: \.parentId)
.where(\Parent.id == 1)
guard let parent = try join.first() else {
return XCTFail("Failed to find parent id: 1")
}
guard let children = parent.children else {
return XCTFail("Parent had no children")
}
XCTAssertEqual(3, children.count)
for child in children {
XCTAssertEqual(child.parentId, parent.id)
}
```
The example above joins Child objects on the Parent.children property, which is of type `[Child]?`. When the query is executed, all objects from the Child table that have a parentId which matches the Parent id 1 will be included in the results. This is a typical parent-child relationship.
Many-to-many example usage:
```swift
struct Student: Codable {
let id: Int
let classes: [Class]?
}
struct Class: Codable {
let id: Int
let students: [Student]?
}
struct StudentClasses: Codable {
let studentId: Int
let classId: Int
}
try db.transaction {
try db.create(Student.self, policy: [.dropTable, .shallow]).insert(
Student(id: 1, classes: nil))
try db.create(Class.self, policy: [.dropTable, .shallow]).insert([
Class(id: 1, students: nil),
Class(id: 2, students: nil),
Class(id: 3, students: nil)])
try db.create(StudentClasses.self, policy: [.dropTable, .shallow]).insert([
StudentClasses(studentId: 1, classId: 1),
StudentClasses(studentId: 1, classId: 2),
StudentClasses(studentId: 1, classId: 3)])
}
let join = try db.table(Student.self)
.join(\.classes,
with: StudentClasses.self,
on: \.id,
equals: \.studentId,
and: \.id,
is: \.classId)
.where(\Student.id == 1)
guard let student = try join.first() else {
return XCTFail("Failed to find student id: 1")
}
guard let classes = student.classes else {
return XCTFail("Student had no classes")
}
XCTAssertEqual(3, classes.count)
for aClass in classes {
let join = try db.table(Class.self)
.join(\.students,
with: StudentClasses.self,
on: \.id,
equals: \.classId,
and: \.id,
is: \.studentId)
.where(\Class.id == aClass.id)
guard let found = try join.first() else {
XCTFail("Class with no students")
continue
}
guard nil != found.students?.first(where: { $0.id == student.id }) else {
XCTFail("Student not found in class")
continue
}
}
```
Self Join example usage:
```swift
struct Me: Codable {
let id: Int
let parentId: Int
let mes: [Me]?
init(id i: Int, parentId p: Int) {
id = i
parentId = p
mes = nil
}
}
try db.transaction {
try db.create(Me.self, policy: .dropTable).insert([
Me(id: 1, parentId: 0),
Me(id: 2, parentId: 1),
Me(id: 3, parentId: 1),
Me(id: 4, parentId: 1),
Me(id: 5, parentId: 1)])
}
let join = try db.table(Me.self)
.join(\.mes, on: \.id, equals: \.parentId)
.where(\Me.id == 1)
guard let me = try join.first() else {
return XCTFail("Unable to find me.")
}
guard let mes = me.mes else {
return XCTFail("Unable to find meesa.")
}
XCTAssertEqual(mes.count, 4)
```
Junction Join example usage:
```swift
struct Student: Codable {
let id: Int
let classes: [Class]?
init(id i: Int) {
id = i
classes = nil
}
}
struct Class: Codable {
let id: Int
let students: [Student]?
init(id i: Int) {
id = i
students = nil
}
}
struct StudentClasses: Codable {
let studentId: Int
let classId: Int
}
try db.transaction {
try db.create(Student.self, policy: [.dropTable, .shallow]).insert(
Student(id: 1))
try db.create(Class.self, policy: [.dropTable, .shallow]).insert([
Class(id: 1),
Class(id: 2),
Class(id: 3)])
try db.create(StudentClasses.self, policy: [.dropTable, .shallow]).insert([
StudentClasses(studentId: 1, classId: 1),
StudentClasses(studentId: 1, classId: 2),
StudentClasses(studentId: 1, classId: 3)])
}
let join = try db.table(Student.self)
.join(\.classes,
with: StudentClasses.self,
on: \.id,
equals: \.studentId,
and: \.id,
is: \.classId)
.where(\Student.id == 1)
guard let student = try join.first() else {
return XCTFail("Failed to find student id: 1")
}
guard let classes = student.classes else {
return XCTFail("Student had no classes")
}
XCTAssertEqual(3, classes.count)
```
Joins are not currently supported in updates, inserts, or deletes (cascade deletes/recursive updates are not supported).
The Join protocol has two functions. The first handles standard two table joins. The second handles junction (three table) joins.
```swift
public protocol Joinable: TableProtocol {
// standard join
func join(
_ to: KeyPath,
on: KeyPath,
equals: KeyPath) throws -> Join
// junction join
func join(
_ to: KeyPath,
with: Pivot.Type,
on: KeyPath,
equals: KeyPath,
and: KeyPath,
is: KeyPath) throws -> JoinPivot
}
```
A standard join requires three parameters:
`to` - keypath to a property of the OverAllForm. This keypath should point to an Optional array of non-integral Codable types. This property will be set with the resulting objects.
`on` - keypath to a property of the OverAllForm which should be used as the primary key for the join (typically one would use the actual table primary key column).
`equals` - keypath to a property of the joined type which should be equal to the OverAllForm's `on` property. This would be the foreign key.
A junction join requires six parameters:
`to` - keypath to a property of the OverAllForm. This keypath should point to an Optional array of non-integral Codable types. This property will be set with the resulting objects.
`with` - The type of the junction table.
`on` - keypath to a property of the OverAllForm which should be used as the primary key for the join (typically one would use the actual table primary key column).
`equals` - keypath to a property of the junction type which should be equal to the OverAllForm's `on` property. This would be the foreign key.
`and` - keypath to a property of the child table type which should be used as the key for the join (typically one would use the actual table primary key column).
`is` - keypath to a property of the junction type which should be equal to the child table's `and` property.
Any joined type tables which are not explicitly included in a join will be set to nil for any resulting OverAllForm objects.
If a joined table is included in a join but there are no resulting joined objects, the OverAllForm's property will be set to an empty array.
### Where
**Where** can follow: `table`, `join`, `order`.
**Where** supports: `select`, `count`, `update` (when following `table`), `delete` (when following `table`).
A `where` operation introduces a criteria which will be used to filter exactly which objects should be selected, updated, or deleted from the database. Where can only be used when performing a select/count, update, or delete.
```swift
public protocol Whereable: TableProtocol {
func `where`(_ expr: CRUDBooleanExpression) -> Where
}
```
`Where` operations are optional, but only one `where` can be included in an operation chain and it must be the penultimate operation in the chain.
Example usage:
```swift
let table = db.table(TestTable1.self)
// insert a new object and then find it
let newOne = TestTable1(id: 2000, name: "New One", integer: 40)
try table.insert(newOne)
// search for this one object by id
let query = table.where(\TestTable1.id == newOne.id)
guard let foundNewOne = try query.first() else {
...
}
```
The parameter given to the `where` operation is a `CRUDBooleanExpression` object. These are produced by using any of the supported expression operators.
Standard Swift Operators:
• Equality: `==`, `!=`
• Comparison: `<`, `<=`, `>`, `>=`
• Logical: `!`, `&&`, `||`
Custom Comparison Operators:
• Contained/In: `~`, `!~`
• Like: `%=%`, `=%`, `%=`, `%!=%`, `!=%`, `%!=`
For the equality and comparison operators, the left-hand operand must be a KeyPath indicating a Codable property of a Codable type. The right-hand operand can be Int, Double, String, [UInt8], Bool, UUID, or Date. The KeyPath can indicate an Optional property value. With `==` and `!=`, the right-hand operand may then be `nil` to indicate an "IS NULL", "IS NOT NULL" type of query. The comparison operators also accept an Optional KeyPath (`\TestTable1.score < 3.0` where `score` is a `Double?`), but their right-hand operand must be non-optional: as in SQL, a row whose column is NULL never matches `<`, `<=`, `>` or `>=`.
PerfectCRUD's expression type is `CRUDExpression`. The old `Expression` typealias has been removed: it collided with Foundation's `Expression` when building with the macOS 15 SDK or later (whatever the deployment target), making an unqualified `Expression` ambiguous. Replace any remaining uses with `CRUDExpression`.
The equality and comparison operators are type-safe, meaning you can not make a comparison between, for example, an Int and a String. The type of the right-hand operand must match the KeyPath property type. This is how Swift normally works, so it should not come with any surprises.
Any type which has been introduced to the query through a `table` or `join` operation can be used in an expression. Using KeyPaths for types not used elsewhere in the query is a runtime error.
In this snippet:
```swift
table.where(\TestTable1.id > 20)
```
`\TestTable1.id` is the KeyPath, pointing to the Int id for the object. 20 is the literal operand value. The `>` operator between them produces a `CRUDBooleanExpression` which can be given directly to `where` or used with other operators to make more complex expressions.
The logical operators permit `and`, `or`, and `not` operations given two `CRUDBooleanExpression` objects. These use the standard Swift `&&`, `||`, and `!` operators.
```swift
table.where(\TestTable1.id > 20 &&
!(\TestTable1.name == "Me" || \TestTable1.name == "You"))
```
The contained/in operators take a KeyPath in the right-hand side and an array of objects on the left.
```swift
table.where(\TestTable1.id ~ [2, 4])
table.where(\TestTable1.id !~ [2, 4])
```
The above will select all TestTable1 objects whose `id` is in the array, or not in the array, respectively.
The Like operators are used only with String values. These permit begins with, ends with, and contains searches on String based columns.
```swift
try table.where(\TestTable2.name %=% "me") // contains
try table.where(\TestTable2.name =% "me") // begins with
try table.where(\TestTable2.name %= "me") // ends with
try table.where(\TestTable2.name %!=% "me") // not contains
try table.where(\TestTable2.name !=% "me") // not begins with
try table.where(\TestTable2.name %!= "me") // not ends with
```
**Property Optionals**
In some cases you may need to query using an optional parameter in your model. In the `Person` model above, we may need to add an optional `height` field:
```swift
struct Person: Codable {
// ...
var height: Double? // height in cm
}
```
In the case we wanted to search for `People` who have not yet provided their height, this simple query will find all rows where `height` is `NULL`.
```swift
let people = try personTable.where(\Person.height == nil).select().map{ $0 }
```
Alternatively, you may need to query for individuals a certain height or taller.
```swift
let queryHeight = 170.0
let people = try personTable.where(\Person.height! >= queryHeight).select().map{ $0 }
```
Notice the force-unwraped key path - `\Person.height!`. _This is type-safe_ and required by the compiler in order to compare the optional type on the model to the non-optional value in the query.
### Order
**Order** can follow: `table`, `join`.
**Order** supports: `join`, `where`, `order`, `limit` `select`, `count`.
An `order` operation introduces an ordering of the over-all resulting objects and/or of the objects selected for a particular join. An order operation should immediately follow either a `table` or a `join`. You may also order over fields with optional types.
```swift
public protocol Orderable: TableProtocol {
func order(by: PartialKeyPath