[10.x] Add support for native column modifying #45487

hafezdivandari · 2023-01-02T16:51:46Z

Related to #45258, this PR adds support for native column modifying and drops necessity of installing doctrine/dbal when modifying columns using change() on MariaDB, MySQL, PostgreSQL and SQL Server.

Supported databases, modifiers and types:

Database	Native Support	Supported column modifiers	Supported column types
MariaDB	✅ (docs)	Same as MySQL	All types
MySQL	✅ (docs)	`after`, `first`, `autoIncrement`, `from`, `storedAs`, `storedAsJson`, `virtualAs`, `virtualAsJson`, `invisible`, `unsigned`, `nullable`, `default`, `charset`, `collation`, `comment`, `useCurrent`, `useCurrentOnUpdate`, `srid`, `renameTo`²	All types
PostgreSQL	✅ (docs)	`autoIncrement`³, `from`, `storedAs`⁴, `generatedAs`, `always`, `nullable`, `default`, `collation`, `comment`, `useCurrent`, `isGeometry`, `projection`	All types except `enum`⁵
SQLite	❌ (docs)	N/A	N/A
SQL Server	✅ (docs)	`autoIncrement`⁶, `nullable`, `default`, `collation`, `persisted`, `useCurrent`	All types except `enum`⁷

Notes:

According to MySQL docs, for column definition changes using CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as:
```
$table->integer('col1')->unsigned()->default(1)->comment('my column'); // `col1` int unsigned not null default '1' comment 'my column'
```
and you modify the column as follows, intending to change only INT to BIGINT:
```
$table->bigInteger('col1')->change(); // alter table `t1` modify `col1` bigint not null
```
That statement changes the data type from INT to BIGINT, but it also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To retain them, the statement must include them explicitly:
```
$table->bigInteger('col1')->unsigned()->default(1)->comment('my column')->change(); // alter table `t1` modify `col1` bigint unsigned not null default '1' comment 'my column'
```
The same approach is also used on PostgreSQL and SQL Server, you have to redeclare all the attributes you want to keep on the column definition.
Currently Laravel compiles added and modified column before other commands, It means to change and rename a same column you should call renameColumn command after change command:
```
$table->enum('difficulty', ['easy', 'hard'])->change();
$table->renameColumn('difficulty', 'hardness');
```
However, this PR adds renameTo column modifier on MySQL to rename and modify a column at one go:
```
$table->enum('difficulty', ['easy', 'hard'])->renameTo('hardness')->change();
```
Laravel compiles autoIncrement on PostgreSQL as primary key and also causes column type to be serial. This PR partially supports autoIncrement, it means the autoIncrement modifier still causes a column type to be serial type and you may use from modifier to set it's starting value, but it doesn't add or drop the primary key. You may use primary() and primary(false) commands explicitly to achieve this.
PostgreSQL doesn't have a native alter command for modifying the expression of a generated column or turning a regular column to a stored generated column, it means storedAs modifier is not supported on this PR. However, it is supported to set storedAs(null) explicitly on PostgreSQL >= 13.0 to drop the generated column expression (turns a stored generated column into a normal base column).
PostgeSQL has native enum but the syntax is different, Laravel uses a check constraint on a varchar when adding an enum type. This PR doesn't handle modifying enum type on PostgreSQL.
Laravel compiles autoIncrement modifier as identity primary key on SQL Server. SQL Server doesn't support adding / dropping identity and primary key when using alter column. So this PR also doesn't support that. You may use primary() and primary(false) commands explicitly to achieve this.
SQL server doesn't have a native enum and Laravel is using a check constraint on a nvarchar when adding an enum type. This PR doesn't support modifying enum on SQL Server.

What if I have already installed `doctrine/dbal`?

Just like #45258, if you have already installed Doctrine DBAL, you have to call Schema::useNativeSchemaOperationsIfPossible() method within the boot method of your App\Providers\AppServiceProvider class or within your migrations files to be able to use native schema operations.

src/Illuminate/Database/Schema/Blueprint.php

src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php

taylorotwell · 2023-01-04T20:47:23Z

Can you elaborate on point 3 and point 6 in your PR description? This only applies to changing columns? Can you give some examples of what would work and what wouldn't work?

Laravel compiles autoIncrement on PostgreSQL as primary key and also causes column type to be serial. This PR partially supports autoIncrement, it means the autoIncrement modifier still causes a column type to be serial type and you may use from modifier to set it's starting value, but it doesn't add or drop the primary key. You may use primary and dropPrimary commands explicitly to achieve this.

hafezdivandari · 2023-01-04T21:17:15Z

Can you elaborate on point 3 and point 6 in your PR description? This only applies to changing columns? Can you give some examples of what would work and what wouldn't work?

@taylorotwell sure. It's not just about modifying columns, currently on creating a table or adding a column, the autoIncrement modifier means both "auto-increment" and "primary key". This may not cause a problem at most cases, but it does in some cases for example in MySql you can do this:

CREATE TABLE users (
     id bigint unsigned not null auto_increment,
     name varchar(255) not null,
     primary key (id, name)
);

but you can't do this on Laravel, because autoIncrement modifier compiles to auto_increment primary key instead of just auto_increment. (I wanted to send another PR maybe to fix this on 10.x)

Ok that was the whole picture, but when you want to modify a column with autoIncrement modifier on MySQL, this is still not a big problem because MySQL accepts this (although that primary key part may not be what the user intended to add):

alter table `users` modify `id` bigint unsigned not null auto_increment primary key

but PostgreSQL and SQL Server doesn't accept that when altering a column. it means you can't control the adding/dropping of a primary key when changing a column. That is what I tried to explain on point 3 and 6. As I said this needs another BC PR to change what autoIncrement modifier means in Laravel when we already have ->primary() to handle that.

PS: Related to this, the SingleStore DB has withoutPrimaryKey modifier as a workaround for this: https://github.com/singlestore-labs/singlestoredb-laravel-driver#increment-columns-without-primary-key

taylorotwell · 2023-01-04T21:29:26Z

OK, but for adding columns everything still works as before on 9.x? However, when modifying columns, can you give an example of some actual migration code that would now throw an error after this PR?

hafezdivandari · 2023-01-04T21:58:52Z

Yes, for adding everything works as before. And when modifying an auto-increment column, no error would be thrown but there are some differences in compare to when adding a column:

Examples of `autoIncrement` modifier

MySQL / MariaDB

// when adding
$table->integer('id')->autoIncrement(); // alter table `users` add `id` int not null auto_increment primary key
$table->increments('id'); // alias to above

// when modifying
$table->integer('id')->autoIncrement()->change(); // alter table `users` modify `id` int not null auto_increment primary key
$table->increments('id')->change(); // alias to above

✅ Everything works as expected.

PostgreSQL

// when adding
$table->integer('id')->autoIncrement(); // alter table "users" add column "id" serial not null primary key
$table->increments('id'); // alias to above

// when modifying
$table->integer('id')->autoIncrement()->change(); // alter table "users" alter column "id" type serial, alter column "id" set not null, alter column "id" drop default, alter column "id" drop identity if exists; comment on column "users"."id" is NULL
$table->increments('id')->change(); // alias to above

⚠️ The column type is serial as expected, but we don't handle the primary key.

SQL Server

// when adding
$table->integer('id')->autoIncrement(); // alter table "users" add "id" int not null identity primary key
$table->increments('id'); // alias to above

// when modifying
$table->integer('id')->autoIncrement()->change(); // alter table "users" alter column "id" int not null
$table->increments('id')->change(); // alias to above

❌ Both identity and primary key are missing and autoIncrement is ignored.

taylorotwell · 2023-01-04T22:24:47Z

OK - this is a pretty risky PR I think but I'm willing to take a chance on it since it would be nice to not have doctrine/dbal for changing columns.

hafezdivandari added 3 commits January 1, 2023 02:14

compile auto increment starting value as fluent command

99ff6b8

separate types and modifiers

7f429a5

support native column modifying on MySQL, PostgreSQL and SQL Server

7ff01e1

hafezdivandari mentioned this pull request Jan 2, 2023

[9.x] Add support for native column modifying #45281

Closed

hafezdivandari marked this pull request as ready for review January 2, 2023 21:57

hafezdivandari and others added 2 commits January 3, 2023 02:57

fix tests

75932df

Update Connection.php

03da2b5

taylorotwell reviewed Jan 4, 2023

View reviewed changes

src/Illuminate/Database/Schema/Blueprint.php Show resolved Hide resolved

taylorotwell reviewed Jan 4, 2023

View reviewed changes

src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php Show resolved Hide resolved

formatting

cb7f0dc

taylorotwell merged commit e901b02 into laravel:master Jan 4, 2023

hafezdivandari deleted the master-patch-1 branch January 4, 2023 22:34

This was referenced Jan 10, 2023

[10.x] Add support for getting native column type #45598

Closed

[9.x] Add support for native rename/drop column commands #45258

Merged

hafezdivandari mentioned this pull request Jan 20, 2023

[10.x] Support native column modifying laravel/docs#8456

Merged

ankurk91 mentioned this pull request Mar 17, 2023

MYSQL 8 Migrating text to alternative text type does not change column #46492

Closed

hafezdivandari mentioned this pull request May 11, 2023

Schema Builder: Error when changing a column to a tinyInteger #8840

Closed

NiceAesth mentioned this pull request Aug 10, 2023

Remove doctrine/dbal and fix migration improper column types ppy/osu-web#10454

Merged

This was referenced Sep 11, 2023

[10.x] WIP #48352

Closed

[10.x] Get columns of a table #48357

Merged

hafezdivandari mentioned this pull request Oct 31, 2023

[11.x] Remove Doctrine DBAL #48864

Merged

30 tasks

hafezdivandari mentioned this pull request Feb 9, 2024

[11.x] Non-default schema names #50019

Merged

hafezdivandari mentioned this pull request Feb 29, 2024

Laravel 11: Remove Doctrine DBAL filamentphp/filament#11429

Merged

3 tasks

This was referenced Jul 16, 2024

Fix column modifying on columnstore tables singlestore-labs/singlestoredb-laravel-driver#88

Merged

Can not change field definition to JSON with default value. doctrine/dbal#5689

Open

hafezdivandari mentioned this pull request Aug 4, 2025

fix(postgres): resolve enum column change syntax error #56523

Closed

hafezdivandari mentioned this pull request Sep 5, 2025

[12.x] Support Check Constraints #56929

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

[10.x] Add support for native column modifying #45487

[10.x] Add support for native column modifying #45487

Uh oh!

hafezdivandari commented Jan 2, 2023 •

edited

Loading

Uh oh!

Uh oh!

Uh oh!

taylorotwell commented Jan 4, 2023 •

edited

Loading

Uh oh!

hafezdivandari commented Jan 4, 2023 •

edited

Loading

Uh oh!

taylorotwell commented Jan 4, 2023 •

edited

Loading

Uh oh!

hafezdivandari commented Jan 4, 2023 •

edited

Loading

Uh oh!

taylorotwell commented Jan 4, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

[10.x] Add support for native column modifying #45487

[10.x] Add support for native column modifying #45487

Uh oh!

Conversation

hafezdivandari commented Jan 2, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Supported databases, modifiers and types:

Notes:

What if I have already installed doctrine/dbal?

Uh oh!

Uh oh!

Uh oh!

taylorotwell commented Jan 4, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

hafezdivandari commented Jan 4, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

taylorotwell commented Jan 4, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

hafezdivandari commented Jan 4, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Examples of autoIncrement modifier

MySQL / MariaDB

PostgreSQL

SQL Server

Uh oh!

taylorotwell commented Jan 4, 2023

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

hafezdivandari commented Jan 2, 2023 •

edited

Loading

What if I have already installed `doctrine/dbal`?

taylorotwell commented Jan 4, 2023 •

edited

Loading

hafezdivandari commented Jan 4, 2023 •

edited

Loading

taylorotwell commented Jan 4, 2023 •

edited

Loading

hafezdivandari commented Jan 4, 2023 •

edited

Loading

Examples of `autoIncrement` modifier