dropConstraint: add ifTableExists option for idempotent retries

[lsantire]

Summary

pgm.dropConstraint(table, name, { ifExists: true }) currently compiles to:

ALTER TABLE "table" DROP CONSTRAINT IF EXISTS "name";

The IF EXISTS only guards the constraint name — not the table. If the table itself has already been dropped (for example, by a partially-applied non-transactional migration that's being retried), this statement errors with relation "table" does not exist, leaving the migration stuck and non-retryable.

PostgreSQL natively supports guarding both sides:

ALTER TABLE IF EXISTS "table" DROP CONSTRAINT IF EXISTS "name";

There's currently no way to emit this through pgm.dropConstraint. The only workaround is dropping down to raw pgm.sql, which loses the type safety and ergonomics of the builder API.

Reproduction

A common pattern for dropping a table that has FKs to high-traffic tables:

exports.up = (pgm) => {
  pgm.noTransaction();
  pgm.sql(`SET lock_timeout = '2s';`);

  pgm.dropConstraint('my_table', 'my_table_userId_fkey', { ifExists: true });
  pgm.dropConstraint('my_table', 'my_table_orgId_fkey', { ifExists: true });

  pgm.dropTable('my_table', { ifExists: true });
  pgm.dropTable('my_table_enum', { ifExists: true });
};

If dropTable('my_table') succeeds but dropTable('my_table_enum') fails (transient error, crash, lock timeout), the migration row is never written (because of noTransaction), so node-pg-migrate retries up on the next attempt. The retry now errors on the first dropConstraint because my_table no longer exists — even though ifExists: true is set, that flag only protects the constraint name.

Proposed API

Add a new ifTableExists?: boolean option to DropConstraintOptions. When true, emit ALTER TABLE IF EXISTS in place of ALTER TABLE. Existing ifExists semantics are unchanged.

pgm.dropConstraint('my_table', 'my_table_userId_fkey', {
  ifExists: true,        // existing — guards the constraint
  ifTableExists: true,   // new — guards the table
});

Resulting SQL:

ALTER TABLE IF EXISTS "my_table" DROP CONSTRAINT IF EXISTS "my_table_userId_fkey";

Alternatives considered

• Repurpose ifExists to guard both sides. Backward-incompatible — existing users relying on the table-must-exist behavior would see silent no-ops where they used to see errors.
• Accept ifExists: 'constraint' | 'table' | 'both'. More flexible but adds API surface area for a relatively uncommon need. A boolean addon seems closer to the project's existing conventions (e.g. ifNotExists on createTable).
• Document raw SQL as the recommended workaround. Works, but loses the builder ergonomics and any future improvements (e.g. cascade handling).

[Shinigami92]

All ideas have up and downsides, but I thing the proposed way of implementing ifTableExists is currently the one with least compatibility issues.

Do you want to implement it? I'm not sure if I find the time this week.

Around end of this month, I might want to release v9 stable and move over to work on v10 alpha.
This issue here is currently the last holding back a v9 stable release.