DEV Community

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

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 = { |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 =, :tablename)
                                 .where(schemaname: Apartment.tenant_names)
                                 .where.not(tablename: public_table_names)

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 = do |pg_table_row|
    "SELECT * FROM \"#{pg_table_row['schemaname']}\".\"#{pg_table_row['tablename']}\""

  unioned_selects_sql = selects_sql.join(' UNION ALL ')

  create_view_sql = "CREATE OR REPLACE VIEW public.all_#{table_name} AS #{unioned_selects_sql}"

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)

Oldest comments (0)

Timeless DEV post...

Git Concepts I Wish I Knew Years Ago

The most used technology by developers is not Javascript.

It's not Python or HTML.

It hardly even gets mentioned in interviews or listed as a pre-requisite for jobs.

I'm talking about Git and version control of course.

One does not simply learn git