CODEWITHSUNDEEP
postgresqlruby-on-rails-7
Jerome
Jerome

Reputation: 6189

Rails migrations for multiple databases and postgresql relations awareness

Assuming all databases of a rails 7.0.3 application are on the same server (be they replicas or not)

If a migration is created in the secondary database, where

t.references :shop, index: true, null: false, foreign_key: true
t.references :role, index: true, null: false, foreign_key: true

this migration will not pass, as postgresql has no knowledge of the connection to the other database: ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: relation "shops" does not exist

Different users exist for main database deploy_root and replicas deploy_readonly.

It does not appear that this is set out in rails functionalities, thus how can it be accomplished with postgresql

Upvotes: 1

Views: 231

Answers (1)

smathy
smathy

Reputation: 27961

There's no way to have a foreign key constraint (or any constraint) between databases (not in postgresql, and not in any engine AFAIK). The whole idea of a relational database is that the relations are contained within a (single) database. Anything outside of that (arguably other than something like a read replica) needs to be handled outside of that.

Upvotes: 1

Related Questions