Compound indexes are not supported by (auto-)migrations

[Lordfirespeed]

MigrationManager is missing add_index method, and there doesn't seem to be any way to specify a multi-column index should be created in the schema format.

[sinisaos]

@Lordfirespeed There been several discussions on the issue. Here is a recent discussion with a link to the PR and the current workaround.

[Lordfirespeed]

@sinisaos that discussion seems to be about specifically composite primary keys - I am talking about multi column indexes, which are already supported by Piccolo with await Table.create_index(...).

[sinisaos]

@Lordfirespeed Sorry, my mistake. You're right.

[sinisaos]

@Lordfirespeed I find it much simpler to create manual migrations to add and drop a composite index,

Process is like this

1. Create auto migration for example table and run migration
2. After table exist in db create an empty migration with e.g. piccolo migrations new home
3. Add a table and create a composite index in the migration file like this

Example migration file

from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.table import Table
from piccolo.columns import Varchar, Boolean


class Task(Table):
    name = Varchar()
    completed = Boolean(default=False)


ID = "2025-05-14T15:40:40:932586"
VERSION = "1.26.0"
DESCRIPTION = ""


async def forwards():
    manager = MigrationManager(migration_id=ID, app_name="", description=DESCRIPTION)

    async def run():
        await Task.create_index([Task.name, Task.completed]) # create composite index

    manager.add_raw(run)

    return manager

and run piccolo migrations forwards home

4. For drop composite index, make another empty migration and add content to it like this

Example migration file

from piccolo.apps.migrations.auto.migration_manager import MigrationManager
from piccolo.table import Table
from piccolo.columns import Varchar, Boolean


class Task(Table):
    name = Varchar()
    completed = Boolean(default=False)


ID = "2025-05-14T15:49:53:402641"
VERSION = "1.26.0"
DESCRIPTION = ""


async def forwards():
    manager = MigrationManager(migration_id=ID, app_name="", description=DESCRIPTION)

    async def run():
        await Task.drop_index([Task.name, Task.completed])  # drop composite index

    manager.add_raw(run)

    return manager