Indexes

Indexes are changed from inside altertable with three keys: add_index, modify_index and drop_index. Indexes are emitted as standalone CREATE INDEX / DROP INDEX statements, separate from the ALTER TABLE query.

An index definition takes a name and columns, with optional unique (boolean) and type (e.g. "btree"). On Postgres a condition may be supplied for a partial index (see Conditions); MySQL does not support partial indexes.

Add index

add_index takes an array of index definitions.

{
  "altertable": {
    "users": {
      "add_index": [
        { "name": "users_email_idx", "columns": ["email"], "unique": true }
      ]
    }
  }
}

Modify index

modify_index has the same shape as add_index. An index cannot be changed in place, so it is compiled to a drop (by name) followed by recreating it from the new definition.

{
  "altertable": {
    "users": {
      "modify_index": [
        { "name": "users_email_idx", "columns": ["email", "tenant_id"], "unique": true }
      ]
    }
  }
}

Drop index

drop_index takes an array of index names.

{
  "altertable": {
    "users": {
      "drop_index": ["users_email_idx"]
    }
  }
}