One strategy of the apartment gem is to store tenant data in dedicated schemas in a Postgres database.
If we have a model Contract and tenants company_a and company_b, we will find these tables in the database:
SELECT * FROM "public"."contracts"; -- is supposed to be empty
SELECT * FROM "company_a"."contracts";
SELECT * FROM "company_b"."contracts";
(The actual tenant schema names may differ)
Having the data distributed in multiple schemas makes "cross-tenant-analysis" quite tricky.
To solve this problem we could create views that collects all data from all tenant tables, like so:
CREATE OR REPLACE VIEW public.all_contracts AS
SELECT * FROM "company_a"."contracts"
UNION ALL SELECT * FROM "company_b"."contracts"
-- UNION ALL ...
;
Now we can query all data using:
SELECT * FROM all_contracts;
Following Ruby script creates these views automatically:
# we want to exclude all "public" models, since these data is not distributed
public_table_names = Apartment.excluded_models.map { |model| model.constantize.table_name.remove(/^public\./) }
# create and execute an SQL query to collect all tables from all schemas (= tenants)
class PgTable < ActiveRecord::Base; end
tenants_table_names_sql = PgTable.select(:schemaname, :tablename)
.where(schemaname: Apartment.tenant_names)
.where.not(tablename: public_table_names)
.order(:tablename)
.to_sql
all_pg_tables_rows = ActiveRecord::Base.connection.execute(tenants_table_names_sql)
# The next lines builds and executes the "CREATE OR REPLACE VIEW" query, which is described at the beginning of the article
all_pg_tables_rows.group_by { |row| row['tablename'] }.each do |table_name, pg_tables_rows|
selects_sql = pg_tables_rows.map do |pg_table_row|
"SELECT * FROM \"#{pg_table_row['schemaname']}\".\"#{pg_table_row['tablename']}\""
end
unioned_selects_sql = selects_sql.join(' UNION ALL ')
create_view_sql = "CREATE OR REPLACE VIEW public.all_#{table_name} AS #{unioned_selects_sql}"
ActiveRecord::Base.connection.execute(create_view_sql)
end
Disclaimer: Obviously this is a hacky script and should probably not be used in production.
If we want to query these views using ActiveRecord we could create corresponding models.
class AllContract < ActiveRecord::Base; end
AllContract.where(...) # go crazy here
Note that these are VIEWS and cannot (?) be used to insert, modify or delete data.
(Cover image by Aleks Marinkovic on Unsplash)
Top comments (0)