Support for cross-schema reference during migration

[lleopoldino]

Referencing a different schema (that belongs to the same DB) in a migration file, throws an error.

Example:
Given the following db schema in Postgres db.

Schema 1: account
Table: user

Schema 2: security
Table: authorized_devices

An error happen when trying to add a FK in the security.authorized_devices table referring to account.user.id column with the following script:

{
    "name": "add_fk_userid_authorized_devices",
    "operations": [
      {
        "alter_column": {
            "table": "authorized_devices",
            "column": "user_id",
            "references": {
                "name": "fk_user_id",
                "table": "account.user", 
                "column": "id",
                "on_delete": "NO ACTION"
            }
        }
      }
    ]
  }

I also tried adding this cross-schema reference in the table creation migration file with no success.

So, is there a way of making this cross-schema reference to work in the current pgroll version?

[andrew-farries]

Hi @lleopoldino, thanks for opening the issue.

Cross schema FKs aren't possible right now with pgroll unfortunately. Each schema is regarded in isolation with regard to versioned migrations currently.

I think this is a useful feature though, and I'll add this issue to our v1 milestone.