Column-level add/drop constraints in changeTable

[IlyaSemenov]

Following #700: to add or drop a single column-level constraint today, the user has to repeat the column type on both sides of t.change():

column: t.change(t.integer(), t.integer().primaryKey()),         // add PK
column: t.change(t.integer().primaryKey(), t.integer()),         // drop PK
column: t.change(t.integer(), t.integer().index({ name: 'i' })), // add index
column: t.change(t.integer().index({ name: 'i' }), t.integer()), // drop index

This duplicates the column type even though the type isn't changing.

For select modifiers (nullable, check, and foreignKey) there are partial workarounds, but generalizing it requires adding noPrimaryKey, noIndex, noUnique, noExclude, and so on — one extra helper per constraint type.

Proposal

Extend t.add and t.drop so that they accept column-level constraint modifiers:

• column: t.add(t.text()) — add a new column (existing behavior).
• column: t.drop(t.boolean()) — drop the column (existing behavior).
• column: t.add(t.primaryKey()) — new: add a column-level constraint to the named column.
• column: t.drop(t.primaryKey()) — new: drop a column-level constraint from the named column. Same as with dropping a column, the constraint will be re-created on rollback.

Example

await db.changeTable('table', (t) => ({
  // Add / drop a column-level constraint:
  col1: t.add(t.primaryKey()),
  col2: t.drop(t.primaryKey()),
  col3: t.add(t.index({ name: 'idx' })),
  col4: t.drop(t.index({ name: 'idx' })),
  col5: t.add(t.unique()),
  col6: t.drop(t.unique()),
  col7: t.add(t.exclude('=')),
  col8: t.drop(t.exclude('=')),
  col9: t.add(t.default(true)),
  col10: t.drop(t.default(true)),

  // Change options of an existing modifier:
  col11: t.change(t.foreignKey('a', 'aId'), t.foreignKey('b', 'bId')),
  col12: t.change(t.check(t.sql`x > 5`), t.check(t.sql`x < 10`)),
  col13: t.change(t.index({ name: 'old' }), t.index({ name: 'new' })),
  col14: t.change(t.default(1), t.default(2)),
  col15: t.change(t.comment('a'), t.comment('b')),
  col16: t.change(t.compression('pglz'), t.compression('lz4')),

  // Change column type:
  col17: t.change(t.integer(), t.string()),
}));

Why this shape and not alternatives

• Per-constraint no* helpers (t.noPrimaryKey(), t.noIndex(), ...) scale poorly: one new helper per constraint type.
• A null marker (t.change(null, t.primaryKey())) is cryptic and harder to type strictly in TS.

Reusing t.add / t.drop matches the verbs the user already knows. The argument itself tells what's being added or dropped.

Compatibility issues

• t.noForeignKey() and t.nonNullable() become redundant. I think they could be dropped, the migration will be easy. Or, they can be deprecated and continue to work.
• t.primaryKey() will have two meanings: with no args, it's a modifier, with multiple args, it works as before. (This is the only thing I don't like.)

[romeerez]

I published a change, here are the updated docs, basically implemented by your specification.

noForeignKey - dropped, it's safe to drop since it was added just a few days ago.

t.primaryKey() will have two meanings: with no args, it's a modifier, with multiple args, it works as before. (This is the only thing I don't like.)

I think it's fine, it's intuitive in a context.

columnName: t.change(t.primaryKey... and columnName: t.add(t.primaryKey makes it obvious it's a primary key for a column.

The other form ...t.add(t.primaryKey(['column']) exists as before, and it's clear it's not in a column context.