fix: correct attributeToSQL across all dialects - #18367

Description of Changes

Second of the stack re-implementing #17121. Depends on #18366 — its commit is the first of the four here, since GitHub cannot base a cross-fork PR on a branch that only exists on the fork. Review #18366 first; only the last three commits belong to this PR.

#18366 pinned current behaviour exactly. This PR fixes it. Every expectation that changes in attribute-to-sql.test.ts / attributes-to-sql.test.ts is a behaviour delta, so the test diff is the review surface.

All nine dialect unit suites pass (2,296–2,906 tests each), clean eslint and clean tsc on both --noEmit and test/tsconfig.json.

Commits

fix: make the "this type cannot have a default" guards work again

Every dialect that refuses a DEFAULT for certain types was checking conditions that can never be true on v7 data types:

• attribute.type._binary was a v6 property of STRING/CHAR set by .BINARY. It does not exist in v7 — I checked the v6 source in git history to confirm what it meant. It is now attribute.type.options?.binary.
• db2, mssql and oracle compared attribute.type !== 'TEXT', i.e. a DataType instance against a string.
• mariadb, mysql, snowflake and ibmi looked the rendered SQL string up in a set of type names, so DataTypes.TEXT('tiny') → TINYTEXT missed, and ibmi never matched at all since its BLOB renders as BLOB(1M).

Lookups now use a new attributeTypeToDataTypeId core utility built on the existing getDataTypeId(), which returns a type's dialect-independent identity. Each dialect keeps exactly the list of types it already named — I did not re-decide which types reject defaults.

A second commit then removes most of those lists again. Once the guards were live I checked them against real servers, and the restrictions largely do not exist:

• db2, mssql and oracle all accept a DEFAULT on their LOB types. CLOB DEFAULT 'abc', NVARCHAR(MAX) DEFAULT N'abc' and BLOB DEFAULT BLOB('abc') all work, and the value round-trips. The SQL Server folklore appears to come from CREATE TABLE's rule that CHECK constraints cannot be defined on text/ntext/image — a different clause. Those three lists are removed.
• .BINARY on a string type is a collation, not a storage class, and every server tested accepts a default on such a column. That guard is removed everywhere rather than repaired.

So the net effect of the two commits is: the guards no longer silently discard a defaultValue the user asked for. mariadb, mysql, snowflake and ibmi keep the lists they had, now matched by data type identity rather than by rendered SQL string. (#18369 revisits mariadb's and mysql's, which turned out to be wrong too.)

fix: give attributeToSQL one way to name the table and the column

The table arrived under two names and the column under three, and no caller passed all of them:

• Core passed table; mysql/mariadb/snowflake/db2/ibmi read tableName. Passing the name declared in AttributeToSqlOptions threw on mariadb/mysql and produced undefined_..._foreign_idx on snowflake/db2/ibmi.
• The column was key on postgres, attributeName on oracle, foreignKey elsewhere — all duplicating attribute.field.

Now: one tableOrModel option, and the column always read from attribute.field, which attributesToSQL and every addColumnQuery set. schema is gone — core already stamps the multi-tenancy schema onto the table it passes, so an explicitly-qualified reference target now keeps its own schema rather than being overridden.

Also fixed here:

• snowflake/ibmi built FK constraint names from an already-quoted column ("myTable_""myColumn""_foreign_idx"); db2 left the name unquoted entirely.
• sqlite3, snowflake and ibmi ignored withoutForeignKeyConstraints.
• postgres dereferenced three options unguarded, so attributeToSQL threw for any attribute with references and no options — which also made deferrable references unreachable.
• mssql and ibmi threw on plain string attributes ({ id: 'INTEGER' }) that the other seven accept.
• snowflake and sqlite3 did not normalize the data type in addColumnQuery.
• columnName is now honoured by every dialect, not only sqlite3.

fix(db2,ibmi): emit the nullability change as its own ALTER COLUMN clause

Neither can change a column's type and nullability in one ALTER COLUMN clause.

db2 returned an array of fragments in the changeColumn context so changeColumnQuery could split them — one method returning string or string[] depending on its options. The COMMENT branch then appended to that array, coercing it to DATA TYPE INTEGER,DROP NOT NULL COMMENT Test and collapsing both clauses back into one invalid fragment.

ibmi had the same bug invisibly, emitting ALTER COLUMN "c" SET DATA TYPE INTEGER DROP NOT NULL, and joined multiple columns with a comma instead of repeating ALTER COLUMN.

attributeToSQL now always returns a string; changeColumnQuery receives the attribute definitions alongside the generated SQL and emits the nullability clause itself.

Notes for review

• Integration suites now run against real servers: mssql 411 passing, oracle 388 passing (needs TZ=UTC), db2 19 passing on the query-interface suite. ibmi is still unverified — there is no way to run it locally, so its ALTER COLUMN change rests on code reading alone.
• Two items from fix: overhaul test suite of attribute(s)ToSQL #17121 turned out not to need fixing: ARRAY(ENUM) on postgres was already corrected upstream, and comment escaping is already done by every consumer that extracts the in-band COMMENT marker. Escaping at the source (as fix: overhaul test suite of attribute(s)ToSQL #17121 does) would double-escape and is what caused that PR to leak N'FooBar' into describeTable.
• db2's attributeToSQL still has an unreachable addColumn FK branch — its addColumnQuery never triggers it. I fixed the quoting rather than deleting it, since whether db2 needs the , CONSTRAINT form is a call I can't verify.

Remaining follow-up

• Convert both methods to TypeScript and rename to attributeToSql / attributesToSql

List of Breaking Changes

AttributeToSqlOptions changes shape (table/tableName/schema/key/foreignKey/attributeName → tableOrModel plus attribute.field). It is exported only under _non-semver-use-at-your-own-risk_.

🤖 Generated with Claude Code