Spectro 3.0.0

Spectro 3.0 closes most of the gap with Ecto for PostgreSQL. Upgrading from 2.x: give @Column properties a DatabaseValue type; properties of other types were silently neither stored nor decoded. Add staleRecord to exhaustive switches over SpectroError, custom to those over FieldType, and the new column types to those over MigrationColumnType. Call SchemaMapper.extractData with try. A non-NULL value a field cannot decode, such as an unknown enum label, now throws SpectroError.resultDecodingFailed instead of leaving the field's default.

• Add orderBy(sql:_:) to Query and TupleQuery, to sort by an SQLFragment. Fragment and field sorts keep call order, and the fragment's parameters bind after the filters'. count(), aggregates, and direct bulk writes drop the ordering with its parameters.
• Add Repo.executeRawQuery(_:), like Ecto's Repo.query, to run a statement written as an SQLFragment, with its placeholders numbered.
• Add Query.distinct() and Query.distinct(_:) for DISTINCT and DISTINCT ON, and TupleQuery.distinct(). DISTINCT ON columns the ordering does not start with are sorted first, as Ecto does. count() and aggregates count each distinct row once.
• Add subqueries: in(_:) and notIn(_:) take a single-column TupleQuery, and QueryCondition.exists(_:) takes a Query, correlated through the outer query's fields. Add Query.exists(), like Ecto's Repo.exists?.
• Compare columns with columns: ==, !=, <, <=, >, and >= between QueryFields; == and != also compare a nullable column with a nonnullable one.
• Add union, unionAll, intersect, intersectAll, except, and exceptAll. Each query keeps its filters, joins, ordering, and limit; clauses added to the result apply to the combined rows. Queries with a row lock or selectFields throw invalidQuery.
• Add Query.recursive(_:), a WITH RECURSIVE query that follows rows from the query's rows, such as a tree's descendants or ancestors. It skips soft-deleted rows and ends on cycles.
• Select SQL expressions with QueryBuilder.expression(_:as:), such as aggregates and window functions, alongside columns: select takes any Selection, a QueryField of a DatabaseValue type or an SQLExpression. A projection of a query with groupBy and having is now grouped, one result per group, and its count() counts the groups; the grouping was ignored.
• Declare nullable foreign keys with @ForeignKey var editorId: UUID?. ForeignKey now takes a ForeignKeyValue: a PrimaryKeyType, or an Optional of one. A nil key stores NULL and preloads no record.
• Add Changeset.castAssoc for has-many and has-one relationships, like Ecto's cast_assoc. Nested params become changesets of the loaded or new related records, which inserting or updating the changeset writes with their foreign key in one transaction, returning the record with the relationship loaded. Related errors join as "posts[1].title", and OnReplace decides what happens to loaded records the params leave out; has-one params carrying another key replace the loaded record. Inside a caller's transaction, the writes run in a savepoint, and errors keep their type, such as staleRecord, instead of transactionFailed. Changeset.errors has an internal setter instead of a private one.
• Cast many-to-many relationships with castAssoc: it writes the related records and inserts their missing join rows, and onReplace: .delete deletes only the join rows of the records left out, as in Ecto. A relationship of another kind gets the error "is not a has-many or many-to-many relationship".
• Add Changeset.putAssoc for has-many, has-one, and many-to-many relationships, like Ecto's put_assoc. It sets a relationship to records already stored, inserting the missing join rows or setting the foreign key of records that belong elsewhere, or to changesets, which insert or update related records in the same transaction. A join row that exists is not inserted again. A later castAssoc or putAssoc of the same relationship replaces an earlier one, with its errors, as in Ecto.
• Select five or more columns and expressions: select takes a closure returning any number of Selections and returns Swift tuples, such as (UUID, String, String, Int, Bool). Selections of one to four columns still return values and Tuple2 to Tuple4.
• Add Query.joinLateral(as:_:) and leftJoinLateral(as:_:) for JOIN LATERAL, like Ecto's lateral joins: the rows a subquery selects for each row, such as each author's latest posts. The subquery refers to the row through the closure's builder; decode the joined rows with executeJoin(with:as:). The subquery keeps its own soft-delete filter, and cannot lock rows.
• Add Query.qualify(_:), which keeps the rows for which a condition that may use window functions holds, as SQL's QUALIFY does, such as the top posts of each author. The window functions see the rows left by the query's distinct, limit, and offset; clauses added after it apply to the remaining rows.
• Add @EmbeddedSchema, like Ecto's embedded_schema: a schema without a table, which may omit @ID, conforming to JSONBValue and stored in a jsonb column of another schema, or a list of them in a jsonb[] column. Properties are stored under their column names, with nil as null, and a missing key keeps the property's default. cast accepts only values of the type for such a field. On its own, an embedded schema validates input without a table, such as a form.
• Add Changeset.castEmbed and putEmbed for an optional, a nonoptional, and a list of embedded schemas, like Ecto's cast_embed and put_embed. castEmbed reads the field's nested params from the params given to cast, now kept as Changeset.params: without them, the field is left as it is, and nil or NSNull removes an optional value. The values are changes of the field, written with the record, and their errors join as "address.city" and "phones[1].number". In a list, params carrying the primary key of one of the record's values update it, others add values, and the values left out are removed. A later cast or put of the field replaces an earlier one, with its errors.
• The @Schema macro extends a type nested in another one, and skips conformances the type already declares, such as Encodable. Both failed to compile.
• Changeset.applyToInstance() applies the changes to @Schema and @EmbeddedSchema types, like Ecto's apply_action; it returned their data unchanged. The result does not keep loaded relationships.
• Add Changeset.prepareChanges(_:), like Ecto's prepare_changes: a function that runs in the write's transaction just before it, and is skipped for an invalid changeset.
• Name changeset fields with key paths: cast(_:params:permitting:), validateRequired(_:), validateLength, validateFormat, validateNumber, validateCustom, and the constraint declarations take key paths; validateInclusion and validateExclusion compare values of the field's type; getField, getChange, and putChange are typed; errors(on:) reads a field's errors.
• Add Changeset.optimisticLock(_:), like Ecto's optimistic_lock. An update increments the integer version field and matches the row only while it holds the loaded version; otherwise it throws the new SpectroError.staleRecord. A version cast from params, such as a form's hidden field, wins over the loaded one, as in Ecto. Add staleRecord to exhaustive SpectroError switches.
• Extend the migration DSL. AlterTable removes columns, changes types, nullability, and defaults, and adds check, unique, and foreign-key constraints, each with an automatic rollback; dropConstraint belongs in an explicit branch. Add RenameTable, CreateEnum, DropIndex, and CreateIndex.concurrently(), and the column types smallint, real, doublePrecision, date, timestamp, bytea, json, array(_:), and named(_:schema:). Add the new cases to exhaustive MigrationColumnType switches.
• Run a migration outside a transaction, for CREATE INDEX CONCURRENTLY, like Ecto's @disable_ddl_transaction: set Migration.runsInTransaction to false, or start a SQL file with -- migrate:up transaction:false, as in dbmate. PreparedMigration gains runsInTransaction.
• Add transaction(isolation:_:) to GenericDatabaseRepo, Spectro, and DatabaseConnection, with TransactionIsolation levels readCommitted, repeatableRead, and serializable.
• Add insertAll(_:onConflict:includePrimaryKey:) and insert(_:onConflict:includePrimaryKey:) with ConflictAction.nothing(on:) and .replace(on:columns:), like Ecto's on_conflict. They return the records inserted or updated.
• Add Repo.reload(_:) for one record or many, in one query, like Ecto's Repo.reload.
• Add Query.batches(of:), an AsyncSequence of primary key batches for reading large results, like Ecto's Repo.stream. A row that a join repeats appears once.
• Load no parent with loadBelongsTo for a nil foreign key; it threw missingRequiredField.
• Add DatabaseValue, Spectro's counterpart of Ecto.Type. @Column, filters, changesets, selections, and updates accept any conforming type. Built in: Decimal (numeric), Data (bytea), arrays of conforming types (text[], uuid[], integer[], …), [String: Value] dictionaries (jsonb), and Optionals of all of them. Conform a raw-value enum to store its raw value; a String raw value fits text, varchar, and PostgreSQL enum columns. Conform a Codable type to JSONBValue to store it as jsonb; dates in jsonb values are stored as ISO 8601 strings with milliseconds, as Ecto does. Implement toPostgresData(), fromPostgresData(_:), and optionally cast(_:) for other types. PrimaryKeyType now refines DatabaseValue; existing conformances keep compiling.
• @Column requires a DatabaseValue type. Properties of other types compiled but were silently neither stored nor decoded; conform the type, or store it in a plain property.
• Add FieldType.custom and FieldInfo.valueType. Add custom to exhaustive switches over FieldType.
• Decode String columns of extension and user-defined types, such as citext and PostgreSQL enums. They decoded as missing values.
• Register @Column properties of type Data. They were neither inserted nor decoded.
• Add array filters contains(_:) (= ANY), containsAll(_:) (@>), and overlaps(_:) (&&). The latter two cast the column to the bound array type, so [Int] filters integer[] columns and [String] filters varchar[] and PostgreSQL enum arrays; the cast is free, and keeps GIN indexes usable, when the types match.
• Decimal holds 38 significant digits: decoding a longer numeric fails instead of rounding, encoding NaN throws instead of trapping in PostgresNIO, and changesets reject strings such as "1,000" or "12 USD" instead of reading their numeric prefix.
• Add QueryCondition.fragment(_:) and SQLFragment, like Ecto's fragment. Interpolated fields become qualified columns, interpolated values become bound parameters, and a literal ?, such as the jsonb operator, stays in the SQL. Use fragments in where and having.
• Add QueryBuilder.table(_:as:) to filter on a joined table's columns.
• Add Query.updateAll(_:) and Query.deleteAll(), like Ecto's update_all and delete_all. They return the number of affected rows. Assignments are set(_:), set(sql:), increment(by:), push(_:), and pull(_:). Joins, ordering, limits, and offsets select the affected rows. deleteAll() marks rows of @SoftDelete schemas as deleted, as delete does.
• Add Query.lock(_:_:) for FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE, with NOWAIT or SKIP LOCKED. Only the queried table's rows are locked.
• Preload relationships of preloaded records, and customize the query that loads a relationship: .preload(\.$posts) { $0.where { … }.orderBy { … }.preload(\.$comments) }. Many-to-many preloads keep the related query's order.
• Add Repo.preload(_:_:) for records already fetched, in one query for all of them, also inside transactions.
• Preload belongs-to relationships through nullable foreign key columns such as @Column var editorId: UUID?. Accept a preload's foreignKey: as a property or a column name; a column name loaded no records. Match many-to-many keys of any PrimaryKeyType.
• Add logger and onQuery to DatabaseConfiguration. The logger receives a debug message for each statement with its SQL, rows, and timings, and never its bound values. onQuery receives a QueryEvent with the SQL, parameters, query time, queue time, row count, and error of each statement, including BEGIN, COMMIT, and ROLLBACK.
• Add Spectro.sandbox(_:), like Ecto's SQL sandbox. It runs its body in a transaction that is always rolled back. Every repository of the instance, in the body and the tasks that inherit its task-local values, uses that transaction. transaction becomes a savepoint that holds the connection until it ends. Each statement outside a transaction runs in its own savepoint, so a failed one, such as an expected unique violation, leaves the sandbox usable. Statements from a Task { } that outlives the sandbox throw instead of reaching the database.
• A transaction's repository and TransactionContext throw SpectroError.noActiveTransaction once the transaction has ended. A repository captured by a task that outlived the transaction ran its statements on the released connection, outside any transaction.
• Insert a record with no inserted columns using DEFAULT VALUES. It trapped. upsert and insertAll without columns throw SpectroError.invalidSchema; they trapped or sent invalid SQL.
• Throw from inserts, upserts, and SchemaMapper.extractData, now throws, when a value cannot be encoded. The column was silently left out.
• Size insertAll batches to stay under PostgreSQL's 65535 parameters for tables with more than 65 columns.
• Key SchemaRegistry by type identity. Two schema types with the same name, such as nested types in different enclosing types, shared metadata.
• Throw SpectroError.resultDecodingFailed for a non-NULL value a field's type cannot decode, such as an unknown enum label or JSON of another shape. The field silently kept its default, which a later save wrote back.
• Decode models selected with selectFields from a subset of their columns; the other fields keep their defaults. Decoding trapped. Decoding any other result that lacks a model's column throws SpectroError.resultDecodingFailed instead of trapping.
• Share one implementation of CRUD between GenericDatabaseRepo and TransactionRepo.
• Report 3.0.0 from spectro --version, pin the Mint installation examples and the Mintfile to 3.0.0, and require 3.0.0 in the package installation examples.

Full comparison with 2.6.0