PostgreSQL: Option to create tenant with template schema instead of public

[martinbarilik]

I've got a database with 100+ tables and hundreds of schemas including public schema which is the one with shared data of other schemas ( data that is the same for all schemas ), mostly codebooks, currency rates and so on.

I am trying to assign foreign keys from specific schemas to a public schema where the shared data is.

Assigning foreign keys to a public schema is no problem for postgresql, but creating database from structure.sql is.

I need to recreate database for test purposes.

Apartment::Tenant.create("tenant_name") is by default using public schema to create a new schema, but structure.sql's dump of public is missing assignments because i suppose it replaces all public. prefixes with a tenant_name., therefore i am forced to use _apartment_template schema to create new schema where these prefixes are not replaced ( only _apartment_template. is replaced, but public. stays untouched )

I am fighting to find a way to create tenant with template other then public.

Expected behavior

Apartment::Tenant.create("tenant_name") uses schema _apartment_template as template for creating new schema

Actual behavior

Apartment::Tenant.create("tenant_name") uses public schema by default to create new schema

System configuration

Debian 9+

structure.sql can be generated with both, public and _apartment_template when adding _apartment_template to search_schema_path. There was a discussion a long while ago to implement this but i guess it was never implemented. When public schema is created, these foreign keys are farwarded to public there fore public. prefix is missing which is fine. Using schema.rb is no-go for obvious reasons.

• Database: (Tell us what database and its version you use.) Postgresql 11+

• Apartment version: 3.0.4

• Apartment config (in config/initializers/apartment.rb or so):

# frozen_string_literal: true

# You can have Apartment route to the appropriate Tenant by adding some Rack middleware.
# Apartment can support many different "Elevators" that can take care of this routing to your data.
# Require whichever Elevator you're using below or none if you have a custom one.
#
# require 'apartment/elevators/generic'
# require 'apartment/elevators/domain'
require 'apartment/elevators/subdomain'
# require 'apartment/elevators/first_subdomain'
# require 'apartment/elevators/host'

#
# Apartment Configuration
#
Apartment.configure do |config|
    # Add any models that you do not want to be multi-tenanted, but remain in the global (public) namespace.
    # A typical example would be a Customer or Tenant model that stores each Tenant's information.

    config.excluded_models = %w[User and so on ...]

    # In order to migrate all of your Tenants you need to provide a list of Tenant names to Apartment.
    # You can make this dynamic by providing a Proc object to be called on migrations.
    # This object should yield either:
    # - an array of strings representing each Tenant name.
    # - a hash which keys are tenant names, and values custom db config
    # (must contain all key/values required in database.yml)
    #
    config.tenant_names = -> { FirmaPublic.pluck(:nazov_schemy) }
    #
    # config.tenant_names = ['tenant1', 'tenant2']
    # config.tenant_names = {
    #   'tenant1' => {
    #     adapter: 'postgresql',
    #     host: 'some_server',
    #     port: 5555,
    #     database: 'postgres' # this is not the name of the tenant's db
    #                          # but the name of the database to connect to before creating the tenant's db
    #                          # mandatory in postgresql
    #   },
    #   'tenant2' => {
    #     adapter:  'postgresql',
    #     database: 'postgres' # this is not the name of the tenant's db
    #                          # but the name of the database to connect to before creating the tenant's db
    #                          # mandatory in postgresql
    #   }
    # }
    # config.tenant_names = lambda do
    #   Tenant.all.each_with_object({}) do |tenant, hash|
    #     hash[tenant.name] = tenant.db_configuration
    #   end
    # end
    #
    # config.tenant_names = -> { ToDo_Tenant_Or_User_Model.pluck :database }

    # PostgreSQL:
    #   Specifies whether to use PostgreSQL schemas or create a new database per Tenant.
    #
    # MySQL:
    #   Specifies whether to switch databases by using `use` statement or re-establish connection.
    #
    # The default behaviour is true.
    #
    config.use_schemas = true

    #
    # ==> PostgreSQL only options

    # Apartment can be forced to use raw SQL dumps instead of schema.rb for creating new schemas.
    # Use this when you are using some extra features in PostgreSQL that can't be represented in
    # schema.rb, like materialized views etc. (only applies with use_schemas set to true).
    # (Note: this option doesn't use db/structure.sql, it creates SQL dump by executing pg_dump)
    #
    config.use_sql = true

    # There are cases where you might want some schemas to always be in your search_path
    # e.g when using a PostgreSQL extension like hstore.
    # Any schemas added here will be available along with your selected Tenant.
    #
    config.persistent_schemas = %w[hstore public]

    # <== PostgreSQL only options
    #

    # By default, and only when not using PostgreSQL schemas, Apartment will prepend the environment
    # to the tenant name to ensure there is no conflict between your environments.
    # This is mainly for the benefit of your development and test environments.
    # Uncomment the line below if you want to disable this behaviour in production.

    # config.prepend_environment = !Rails.env.production?

    # When using PostgreSQL schemas, the database dump will be namespaced, and
    # apartment will substitute the default namespace (usually public) with the
    # name of the new tenant when creating a new tenant. Some items must maintain
    # a reference to the default namespace (ie public) - for instance, a default
    # uuid generation. Uncomment the line below to create a list of namespaced
    # items in the schema dump that should *not* have their namespace replaced by
    # the new tenant
    #
    # config.pg_excluded_names = ["uuid_generate_v4"]

    # Specifies whether the database and schema (when using PostgreSQL schemas) will prepend in ActiveRecord log.
    # Uncomment the line below if you want to enable this behavior.

    config.active_record_log = true
end

# Setup a custom Tenant switching middleware. The Proc should return the name of the Tenant that
# you want to switch to.
# Rails.application.config.middleware.use Apartment::Elevators::Generic, lambda { |request|
#   request.host.split('.').first
# }

# Rails.application.config.middleware.use Apartment::Elevators::Domain
Rails.application.config.middleware.use Apartment::Elevators::Subdomain
# Rails.application.config.middleware.use Apartment::Elevators::FirstSubdomain
# Rails.application.config.middleware.use Apartment::Elevators::Host

• use_schemas: true

• Rails (or ActiveRecord) version: Rails 7.0.4

• Ruby version: 3.1.5

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.

[mnovelo]

@martinbarilik is this still a blocking issue for you?

You can create the schema however you want and then just manually add the necessary record so that your FirmaPublic.pluck(:nazov_schemy) returns your new schema. I'm not sure if you need Apartment to handle your usecase? At the same time, I'd be happy to look over a PR if you thought that Apartment should cover this case!