DEV Community

Cover image for Create views to display all data managed by apartment gem
Mario
Mario

Posted on

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";
Enter fullscreen mode Exit fullscreen mode

(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 ...
;
Enter fullscreen mode Exit fullscreen mode

Now we can query all data using:

SELECT * FROM all_contracts;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Note that these are VIEWS and cannot (?) be used to insert, modify or delete data.

(Cover image by Aleks Marinkovic on Unsplash)

Discussion (0)