Create views to display all data managed by apartment gem

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)