Rails migrations for multiple databases and postgresql relations awareness

Question

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

Where primary database (app_development) has a table shop
a secondary database (appusers_development) has a table role
there is a third database (applocal_development) that will need to connect as well.

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

smathy · Accepted Answer · 2022-08-18 03:10:15Z

1

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.

answered Aug 18, 2022 at 3:10

smathy

28k5 gold badges51 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails migrations for multiple databases and postgresql relations awareness

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related