Working with multi-schema database in Rails

With Active Record accessing databases made very easy. Though you don’t know about SQL, developers can take advantage of active record and get results from the database. This works well if the database has a single schema. What about multi-schema database? Will Rails allow to work with multi-schema database? Let’s checkout steps to access multi-schema database.

As of matter of fact, whenever developers create a table in database it stores in public schema and whenever active record fires a query SQL engine by default will search tables in public schema. To make SQL engine aware of newly created schemas we need to set search path in config/database.yml

set schema search path

Once search path has set, then add a migration to create a new schema:

schema_migration.rb

Then add tables to a particular schema in our case it is gis

add_location_to_gis.rb

Once db has been migrated then verify db/structure.sql file has following lines or not:

db/structure.sql

Modify table name as schema.table_name to avoid conflicts between tables with the same name in different schemas.

Note: If you didn’t modify table name then SQL fetches table as per search path order.

set table name

Once the table has been set then we can access tables in different schemas through models. To derive a relation between tables across schemas, one has to set relation from both sides. Otherwise, it gives IrreversibleOrderError .

Once above steps are completed then you are ready to play with multi-schema database.

Hope you have learned.

If you like it leave your feedback in the comment section and share this post.