Allow index creation upon table creation

[saintazunya]

Hi community,

I recently found that we are not able to create index upon table creation.
Here is a minimum reproduce of errors:

{
  "name": "01_test",
  "operations": [
    {
      "create_table": {
        "name": "test_user",
        "columns": [
          {
            "name": "user_id",
            "type": "int",
            "pk": true
          }
        ]
      }
    },
    {
      "create_index": {
        "name": "idx_test_user_id",
        "table": "test_user",
        "columns": [
          "user_id"
        ]
      }
    }
  ]
}

I got an error saying Failed to start migration: migration is invalid: table "test_user" does not exist when running migrations.
We can walkaround by spliting migrations into two files, but it comes with a problem that might break the zero-downtime promise.

• We need to run migration sequencely, that said, I have to complete 01_create_tableand then run02_create_index`.
• At which time point 01_create_table is completed and I will not have a chance to roll it back if there is a bug in DDL.

If index creation is allowed upon table creation, it will make the migrations more compact.

Thanks!

[andrew-farries]

Hi, thanks for reporting this issue @saintazunya.

This is a limitation with the current implementation of migrations that contain multiple operations; later operations in the migration typically can't reference resources created by earlier operations in the same migration.

This is a significant restriction and one we hope to remove in an upcoming release.

For this specific case of adding an index to a table it would also make sense to be able to do it as part of the create table operation, in the same way that constraints and foreign keys can be added at table creation time.