5.x roadmap

[dereuromark]

High Priority Missing Features

1. Generated/Computed Columns (MySQL)

• PostgreSQL has IDENTITY columns, but MySQL's GENERATED/VIRTUAL columns are not supported
• These are increasingly common for denormalization and JSON extraction
• Example: price DECIMAL(10,2), tax DECIMAL(10,2), total DECIMAL(10,2) AS (price + tax) STORED

2. Views

• No support for CREATE/DROP VIEW
• Common use case for migrations to create database views
• Would be useful for both materialized (PostgreSQL) and regular views
  => Add support for database views and triggers #957

3. Sequences (PostgreSQL)

• While auto-increment is supported, explicit sequence management is not
• Useful for PostgreSQL when you need custom sequences or shared sequences across tables

4. Table Partitioning

• No support for PARTITION BY (RANGE, LIST, HASH)
• Increasingly important for large datasets
• MySQL 8.0+ and PostgreSQL both support this

5. Spatial Indexes

• Geometry/Point/Linestring types are supported, but SPATIAL indexes are not
• Would complete the geospatial feature set

Medium Priority Missing Features

6. Database-level Operations

• User/Role management (CREATE USER, GRANT/REVOKE)
• While often handled outside migrations, could be useful for test environments

7. Triggers

• SQLite adapter preserves them during recreation, but no way to create/modify them
• Common for audit logging and complex business rules
  => Add support for database views and triggers #957

8. Check Constraint Naming

• CHECK constraints are supported but may lack explicit naming control
• Important for dropping/modifying specific constraints later

9. Column Compression (MySQL 8.0.30+)

• COLUMN_FORMAT COMPRESSED for InnoDB
• Useful for large TEXT/BLOB columns

10. Index Visibility (MySQL 8.0+)

• ALTER INDEX ... INVISIBLE/VISIBLE
• Useful for testing index performance impact without dropping

Low Priority (But Potentially Useful)

DEFAULT CURRENT_TIMESTAMP for more column types

• Currently supported for timestamps, could expand to DATE/TIME

---

What do people think? Which ones are relevant and we should look into in the future, which ones should be forget about?

[jamisonbryant]

Table Partitioning

Highly interested in this feature, and could even put some time into it. We've tried to set MySQL partitions up for our Cake 2 app (I know, it's old, but all our Migrations are 5.x). I believe we got it working on a test database, but didn't roll it out to production yet. Having support for this in Migrations would be a benefit to many large-sized apps. @cnizzardini

Spatial Indexes

@umer936 does this seem like something you might use?

[daVinciWanaB]

Partitioning support would be great!

[markstory]

Useful for PostgreSQL when you need custom sequences or shared sequences across tables

I've not come across a scenario where this was needed. Have you?

User/Role management (CREATE USER, GRANT/REVOKE)

Lets keep migrations focused on schema management. Getting into managing permissions is another swamp entirely.

DEFAULT CURRENT_TIMESTAMP for more column types

This sounds like a simple improvement that would be used often.

[dereuromark]

Regarding Partitioning support - Questions for refinement:

1. Composite partition keys - Should partitionBy('RANGE', ['year', 'month']) be supported?
2. Subpartitioning (MySQL) - Worth including in v1?
3. PostgreSQL partition naming - Auto-generate {table}_{partition} or require explicit names?
4. Expressions - Support PARTITION BY RANGE (YEAR(order_date))?

[umer936]

Spatial Indexes

@umer936 does this seem like something you might use?

Wasn't aware of it.
Looking at it now, I have 2 potential use-cases for it, but it's not something too useful for me. Just searching lat/lons has been sufficient so far.

I'd be curious if there are Geo/GIS apps using CakePHP though.

[dereuromark]

A while back I was playing around with spatial ( dereuromark/cakephp-geo#76 ), but it still always had a table scan. So I gave up on it in the end.

Well, given the features I now added as PR, I think we can close this for now.
People can open more concrete issues once they arise.

[dereuromark]

Just as reference here also a highlevel comparison

Missing/Limited Features Comparison

1. Views & Triggers (Already tracked: View/Trigger in database by migration #347)

• Rails: create_view, drop_view methods
• Django: RunSQL for raw SQL views
• EF Core: AlterView, CreateView operations
• Doctrine: Via raw SQL
• Here: coming in 5.x

2. Stored Procedures & Functions

• Laravel: Via raw SQL
• EF Core 6: AlterStoredProcedure, CreateStoredProcedure, DropStoredProcedure, RenameStoredProcedure
• Here: Via raw SQL ?

3. Check Constraints

• Rails 6.1+: add_check_constraint, remove_check_constraint
• Django: CheckConstraint class
• EF Core: Check constraints support
• Ecto: create constraint(..., check:)
• Here: Limited - basic support exists but not well documented

4. Exclusion Constraints (PostgreSQL)

• Django: ExclusionConstraint with GiST indexes
• Ecto: create constraint(..., exclude:)
• Here: Not supported

5. Partial/Filtered Indexes

• Rails: create index ... where: "condition"
• Django: condition parameter on indexes
• EF Core: HasFilter() method
• Ecto: where: option on indexes
• Here: Not supported (would need raw SQL)

6. Expression/Functional Indexes

• Rails: create index "lower(email)"
• Ecto: Expression in index columns
• PostgreSQL native: Common for lower(), upper() indexes
• Here: Not supported

7. Full-Text Indexes

• Laravel 8.79+: $table->fullText('body') native support
• Here: MySQL FULLTEXT via raw SQL only

8. Virtual/Generated Columns

• Laravel: virtualAs($expression), storedAs($expression)
• Django: GeneratedField
• Here: Not directly supported

9. Spatial Columns & Indexes

• Laravel: $table->point(), $table->polygon(), $table->spatialIndex()
• Here: Not supported

10. Schema Drift Detection

• Prisma: Shadow database for automatic drift detection
• Django: makemigrations --check detects unapplied changes
• Here: No built-in drift detection => TestHelper/Setup plugin?

11. Migration Squashing

• Django: squashmigrations command
• Rails: Schema dump serves similar purpose
• Here: Snapshot command exists but no squashing => TestHelper plugin

12. insertOrUpdate() / Upsert (Already tracked: 5.x: insertOrUpdate() #950)

• Laravel: upsert() method
• Rails: upsert_all
• Here: Missing (only insert and insertOrSkip)

13. Data-Only Migrations with RunPython/RunSQL

• Django: RunPython, RunSQL with explicit reversible operations
• Rails: up_only block for one-way data operations
• Here: execute() exists but less structured

14. ALGORITHM=INSTANT (Already tracked: #2323)

• MySQL 8.0+ native: Zero-downtime schema changes
• Here: Not exposed in API

15. Conditional Migration Execution by Environment

• Rails: reversible do |dir| dir.up { } end
• Django: state_operations vs database_operations separation
• Here: shouldExecute() exists but less granular

---

Priority Recommendations

High Value / Reasonable Effort:

1. Partial indexes (where clause support)
2. Expression indexes
3. Full-text indexes (MySQL/PostgreSQL)
4. Virtual/generated columns
5. insertOrUpdate() (already tracked)

Medium Value / More Effort:
6. Check constraints API improvement
7. Views support
8. Schema drift detection

Lower Priority / Database-Specific:
9. Exclusion constraints (PostgreSQL only)
10. Spatial columns (niche use case)
11. Stored procedures/functions (better done outside migrations)