Adding constraint on a foreign key with node-pg-migration

node-pg-migration is a useful library. You can add a constraint of a foreign key with a simple references property on the column. For example, you have table client_session with publishable_key column and you want to reference publishable_key column in workspace table where it is not a primary key (just a unique key). The following usage with object: schema, name, won’t work:

 pgm.createConstraint({
    schema: "seam",
    name: "client_session",
  }, "client_session_publishable_key_fkey", {
    foreignKeys: {
      columns: "publishable_key",
      references: {
        schema: "seam",
        name: "workspace",
      },
    },
  })

 If you write a migration for Postgress and Node.js using node-pg-migration (e.g., table 1) and want to create a foreign key reference / constraint (createConstraint or references on createTable) on a non-primary key of the other table (table 2), then references by an object containing schema and table name, do not work. This is probably a bug in node-pg-migration. It tries to match primary keys only, but our publishable_key is not a PK in workspace.

It looks like node-pg-migration is not smart enough to add the column/field name like it does for primary keys. You can use a string (seam.table2(col)) as the references value, not an object, or just use raw SQL to bypass node-pg-migration behavior, e.g.,

 pgm.sql(`
    ALTER TABLE seam.client_session
    ADD CONSTRAINT client_session_publishable_key_fkey
    FOREIGN KEY (publishable_key) REFERENCES seam.workspace(publishable_key);
  `)

Or in the node-pg-migration method:

 pgm.createConstraint({
    schema: "seam",
    name: "client_session",
  }, "client_session_publishable_key_fkey", {
    foreignKeys: {
      columns: "publishable_key",
      references: "seam.workspace(publishable_key)"
    },
  })
},