Adding IntColumn as primary key to existing table

[moritz-weber]

Hi there,

I am currently trying to add an IntColumn to an already existing table.
Basically, I want to change this table:

class Artists extends Table {
  TextColumn get name => text()();

  @override
  Set<Column> get primaryKey => {name};
}

To this one:

class Artists extends Table {
  TextColumn get name => text()();
  IntColumn get id => integer()();

  @override
  Set<Column> get primaryKey => {id};
}

Using the IntColumn as primaryKey would probably be optional, but it should not be nullable.
I tried to use the following migration:

await m.addColumn(artists, artists.id);
await m.alterTable(
  TableMigration(artists, columnTransformer: {
    artists.id: artists.rowId,
  }),
);

But, since rowId can be null, this throws an error. Is there a way to just iterate through the column and write an index into the field?

Thanks in advance,
Moritz

[simolus3]

Your approach for the migration gets pretty close. For me, it works after I remove the addColumn statement altogether - the single alterTable call is enough.

Technically speaking, the problem is not that rowId can be null (it can't). Your migration fails at the addColumn call since the added column is non-nullable. Non-nullable columns can't be added via ALTER TABLE ADD COLUMN (which addColumn uses internally) because existing rows in the table would illegally have a null value then.
On the other hand, alterTable runs a rather complex procedure that involves creating a new table from scratch, copying data over, and then replacing the old table with the new one. This means that it can create all columns like it should. Since alterTable always migrates a table to the latest schema (by re-creating it), there's also no need to be using addColumn before.

(I really like the artists.id: artists.rowId approach, nice call with that. I guess it's technically not needed since artists.id is an alias for the rowid in the new schema and the insert usually happens in order, but being explicit about that is good)

[moritz-weber]

Ah nice, you're right. I always thought that addColumn and alterTable go together somehow. Need to read the docs more carefully :)