Forem

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

Posted on

1

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)

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Speedy emails, satisfied customers

Postmark Image

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay