The migrator modifies native SQL

[DisboardTetta]

Description

Let's look at a snippet from my migration:

$b->createTable(..., [
       <...>
            'owner_user_id' => 'bigint REFERENCES users(id) ON DELETE SET NULL', <...>
        ]);

For some reason, I get the following error:

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ","     
  LINE 9: ...ner_user_id" bigint NULL REFERENCES users(id) ON DELETE SET, 

But if we look closely at the resulting script, or rather, a portion of it, for some unknown reason, the part with NULL has simply disappeared, resulting in a dangling SET:

  The SQL being executed was: CREATE TABLE <...>                                                                         
        "owner_user_id" bigint NULL REFERENCES users(id) ON DELETE SET,                         
        "uploaded_at" <...>
  )   

Package version

2.0.1

PHP version

8.4.18

[DisboardTetta]

In fact, there's another similar problem.
If we use the recommended Postgres construct instead of bigserial, we'll get the corresponding error.

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "AS"  
  LINE 2:  "id" bigint DEFAULT AS GENERATED BY IDENTITY PRIMARY KEY,   

When original snippet

$b->createTable(..., [
            'id'    => 'bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY',
            <...>
        ]);

DEFAULT is moved before GENERATED and has incorrect syntax

[Tigrov]

Use Expression object to wrap column definition as a custom string.

$b->createTable(..., [
       <...>
            'owner_user_id' => new Expression('bigint REFERENCES users(id) ON DELETE SET NULL'), <...>
        ]);

[DisboardTetta]

But why does the string still get mangled if I don't use Expression? It seems to me that when using a string directly, the builder shouldn't perform any string manipulation, since this is implicit behavior.

If the key value requires an object, how about throwing an exception if a pure string is passed?

[Tigrov]

By default, the column definition is made to match different DBMSs. Also it implements support for abstract and pseudo types.

[DisboardTetta]

So wouldn't it be better, in this case, to leave string modifications at the chained method level and leave the raw string alone?

Again, the main problem is that this particular behavior is implicit and breaks seemingly valid syntax.

I still insist it's a bug, since the raw string in a migration should be immutable.

[vjik]

Yii DB always parse strings definitions to prepare values, to support pseudotypes and other features. To use string in result query as is user should use Expression class. Such design.

[DisboardTetta]

My position is simple: either completely prohibit string values ​​in column definitions, or don't mutate them.

Currently, passing a string like bigint REFERENCES users(id) ON DELETE SET NULL or bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY results in silent corruption (the NULL is removed, the DEFAULT is moved). This is unacceptable for database migrations.

I see two clean solutions:

1. Completely prohibit raw strings – require explicit use of an object, use ColumnBuilder for typical definitions, and Expression for any custom column definition. If a string is passed, throw an exception. No magical parsing.

2. Leave strings as is – if a string is passed, pass it to the database unchanged and without parsing. Responsibility for correctness lies with the developer, as was the case in Yii1 and Yii2.

Half-parsing that breaks valid SQL constructs (SET NULL, GENERATED BY DEFAULT AS IDENTITY) is a bug, not a feature. Choose one consistent rule.

Also, I believe it's necessary to explicitly indicate the magical behavior of migrations in PHPDoc annotations. Please add an annotation for the corresponding methods (createTable, addColumn, etc.) – for example, @see with a note about native SQL. Example:

@see Yiisoft\Db\Expression\Expression::class If you need to use a native column definition via SQL, wrap it in an Expression.

Passing a regular string does not guarantee that it will match the final SQL query.

This will allow developers to see the requirement directly in the IDE without opening the online documentation. Currently, the current behavior violates the following principles and practices:

• Principle of Least Astonishment – The developer expects the string "bigint REFERENCES users(id) ON DELETE SET NULL" to be written to the database as is (as in Yii1/Yii2 and most tools). Instead, the system silently removes the NULL, breaking the query.

• Explicit over Implicit – Complex string transformations should be explicitly visible in the code (for example, via an Expression). Hidden parsing behind a simple string violates the "explicit is better than implicit" principle.

• Single Responsibility Principle – createTable should create a table, not perform incomplete parsing of SQL expressions, which breaks the syntax. String parsing is either a separate class or not at all.

Considering your own statements,

Yii has always followed the same principles of being performant, flexible but with good defaults, practice-oriented, simple, explicit, and consistent. Yii3 is no exception.

I think you should really make this part explicit, simple, and consistent. Thank you.

[Tigrov]

Thank you for the detailed feedback and for highlighting these specific cases (like GENERATED BY DEFAULT AS IDENTITY). We completely understand the expectation: when a string is passed, it feels like it should be treated as a "raw" definition.

However, the current architecture of yiisoft/db-migration (and yiisoft/db in general) is designed to handle multiple DBMSs. To achieve this, the package supports abstract and pseudo-types, which requires parsing the string to map these types and their constraints to the specific syntax of the database being used.

Yii1 and Yii2 also performed search-and-replace for column types, though the parsing was indeed less "deep" than it is now. Moving to a "pass-through" model for strings would effectively disable the built-in functionality for abstract types, which is a core feature of the migration system.

That said, your points about the Principle of Least Astonishment and silent corruption are valid.

Our next steps:

1. Documentation: We definitely need to improve the PHPDoc and official documentation for createTable, addColumn, etc., to explicitly state that strings are parsed and that Expression should be used for native, immutable SQL.
2. Internal Review: We will look into how we can improve the parser to better handle modern SQL constructs or provide a more consistent "opt-out" mechanism for parsing without breaking backward compatibility.

We want to keep the balance between "smart" migrations and developer control, and your feedback helps us identify where that balance has shifted too far toward "magical" behavior.