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)"
},
})
},
