Create index migrations could use "IF NOT EXISTS"

[frmoraess]

Description

The generated Drift migrations for index, for example:

  name: 'test_index',
  columns: {
    #id
  },
  unique: true,
)

Could benefit from the statement "IF NOT EXISTS" (CREATE UNIQUE INDEX IF NOT EXISTS), just like the tables that has their migrations generated with "CREATE TABLE IF NOT EXISTS" to avoid possible conflicts.

If there is some technical decision supporting the idea of not using this statement, it should be a good idea do have an attribute in the TableIndex annotation to support this.

I would be happy to open an PR myself when i have some time, just opening the issue first to understand author consideration and someone may have free-time before me

[simolus3]

I agree that it would be nice to allow IF NOT EXISTS for index definitions too. I wonder if that option should really be set on the index though, since it's really an option you only set when you create the index (it doesn't affect the index itself).

So I think extending the Migrator APIs to add an optional ifNotExists parameter to the relevant create methods would be a good API for this. But with the current implementation it might not be that easy (because we generate the full SQL statement at build time, we'd have to provide more information in generated code to know where to inject the IF NOT EXISTS).

[frmoraess]

@simolus3 Did you see a downside to use IF NOT EXISTS as default? It probably would be easier to implement, and since the table creation is already using IF NOT EXISTS i think that this change make sense and doesn't have critical impact.

If the ifNotExists parameter is added to the Migrator that would be a global config right (applied to all tables)? I think if the case is to provide an option, the index annotation is better for this, because we would have control over each table behavior. Yes, it only affects in the creation of index, but i don't think it is a big deal an optional parameter for the TableIndex annotation, i mean, it is a index config.