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)

Latest comments (0)

An Animated Guide to Node.js Event Loop

Node.js doesn’t stop from running other operations because of Libuv, a C++ library responsible for the event loop and asynchronously handling tasks such as network requests, DNS resolution, file system operations, data encryption, etc.

What happens under the hood when Node.js works on tasks such as database queries? We will explore it by following this piece of code step by step.