0

We have a SAAS platform written in Rails using the postgres' schema based multitenancy and Apartment gem. The different schemas are identical, with same number of tables and same columns in each table. We want to migrate to foreign key based multitenant system where we want to merge all the records from different schemas into a single schema, identifying each record with a tenant_id. What is the proper way of merging all the records from the different schemas, and preserving the foreign key relationships.

Abhishek Bhatta
  • 71
  • 2
  • 8
  • I fail to see the problem. What foreign keys between what tables would be difficult to preserve? Maybe a small example would help. – Laurenz Albe Jun 09 '20 at 07:54
  • 1
    @LaurenzAlbe I think OP is concerned about duplicate primary keys across the schemas... if there is a `foo` table entry with `id` of 1 in many schemas and `bar` records have an association of `foo_id` of 1, OP needs to ensure the `bar` points to the *correct* `foo` after migration. – SteveTurczyn Jun 09 '20 at 08:00
  • 1
    Simple - add the `tenant_id` to all tables, primary and foreign keys. – Laurenz Albe Jun 09 '20 at 08:11
  • Adding tenant_id is simple. However how would you merge two records from different schemas with same primary key, (say record of user with id=1 that reside in different schemas ) into a single table. – Abhishek Bhatta Jun 09 '20 at 08:48

1 Answers1

0

This is a situation that will need care. I think (I could be wrong) that the best approach is to add to all tables tenant_id and original_id ... before attempting migration populate original_id in all tables with id of that record. Essentially this is to have a record of what the value of id was before the merging.

After merge you can then run a rake task that rebuilds the associations. So if you had...

class Foo
has_many :bars

Your migration script would do (after migration)

Bar.all.each do |bar|
  foo = Foo.find_by(tenant_id: bar.tenant_id, original_id: bar.foo_id)
  bar.update_column(:foo_id, foo.id)
end

You'd need to do something similar for every relation, so it's a bit of a slog.

Hopefully, someone else will come up with a better solution.

NOTE THIS IS NOT IDEMPOTENT. If it errors, you can't restart it except by redoing the merging completely.

SteveTurczyn
  • 36,057
  • 6
  • 41
  • 53