ateliware/triplex

Database multitenancy for Elixir applications!

Triplex

A simple and effective way to build multitenant applications on top of Ecto.

Documentation

Triplex leverages database data segregation techniques (such as Postgres schemas) to keep tenant-specific data separated, while allowing you to continue using the Ecto functions you are familiar with.

Quick Start

1. Add triplex to your list of dependencies in mix.exs:

def deps do
  [
    {:triplex, "~> 1.3.0"},
  ]
end

2. Run in your shell:

mix deps.get

Configuration

Configure the Repo you will use to execute the database commands with:

config :triplex, repo: ExampleApp.Repo

Additional configuration for MySQL

In MySQL, each tenant will have its own MySQL database. Triplex uses a table called tenants in the main Repo to keep track of the different tenants. Generate the migration that will create the table by running:

mix triplex.mysql.install

And then create the table:

mix ecto.migrate

Otherwise, if you wish to skip this behavior, configure Triplex to use the default information_schema.schemata table:

config :triplex, tenant_table: :"information_schema.schemata"

Usage

Here is a quick overview of what you can do with triplex!

Creating, renaming and dropping tenants

To create a new tenant:

Triplex.create("your_tenant")

This will create a new database schema and run your migrations—which may take a while depending on your application.

Rename a tenant:

Triplex.rename("your_tenant", "my_tenant")

This is not something you should need to do often. :-)

Delete a tenant:

Triplex.drop("my_tenant")

More information on the API can be found in documentation.

Creating tenant migrations

To create a migration to run across tenant schemas:

mix triplex.gen.migration your_migration_name

If migrating an existing project to use Triplex, you can move some or all of your existing migrations from priv/YOUR_REPO/migrations to priv/YOUR_REPO/tenant_migrations.

Triplex and Ecto will automatically add prefixes to standard migration functions. If you have custom SQL in your migrations, you will need to use the prefix function provided by Ecto. e.g.

def up do
  execute "CREATE INDEX name_trgm_index ON #{prefix()}.users USING gin (nam gin_trgm_ops);"
end

Running tenant migrations:

mix triplex.migrate

This will migrate all of your existing tenants, one by one. In the case of failure, the next run will continue from where it stopped.

Using Ecto

Your Ecto usage only needs the prefix option. Triplex provides a helper to coerce the tenant value into the proper format, e.g.:

Repo.all(User, prefix: Triplex.to_prefix("my_tenant"))
Repo.get!(User, 123, prefix: Triplex.to_prefix("my_tenant"))

Fetching the tenant with Plug

Triplex includes configurable plugs that you can use to load the current tenant in your application.

Here is an example loading the tenant from the current subdomain:

plug Triplex.SubdomainPlug, endpoint: MyApp.Endpoint

For more information, check the Triplex.Plug documentation for an overview of our plugs.

Thanks

This lib is inspired by the gem apartment, which does the same thing in Ruby on Rails world. We also give credit (and a lot of thanks) to @Dania02525 for the work on apartmentex. A lot of the work here is based on what she has done there. And also to @jeffdeville, who forked (tenantex) taking a different approach, which gave us additional ideas.

triplex's Issues

Document the main usage

We need to document the main usage of this lib, since noone knows how it works pretty well.

Refactor on plugs configuration

Check if we can do it in a better way.

Deprecate the use of `Triplex.put_tenant/2`

Document to use Repo.*(query, prefix: tenant)

Failing to Fetch and Insert into Tenant table

Hi,

I am currently new and just started to use triplex library. I am having an issues that I can not fetch and insert into tenant tables. Please have a look in a log below, this log is generated from an phoenix app, to see more about this issue -

[debug] QUERY ERROR source="departments" db=2.8ms
SELECT d0."id", d0."name", d0."description", d0."inserted_at", d0."updated_at" FROM "awesome"."departments" AS d0 []
** (Postgrex.Error) ERROR 42P01 (undefined_table): relation "awesome.departments" does not exist
    (ecto) lib/ecto/adapters/sql.ex:431: Ecto.Adapters.SQL.execute_and_cache/7
    (ecto) lib/ecto/repo/queryable.ex:133: Ecto.Repo.Queryable.execute/5
    (ecto) lib/ecto/repo/queryable.ex:37: Ecto.Repo.Queryable.all/4

and as you can see the table is already been created with Triplex.create/1 in the psql dialog below -

dev=# select * from 
awesome.             companies            hello.               information_schema.  pg_catalog.          pg_temp_1.           pg_toast.            pg_toast_temp_1.     public.              schema_migrations    
dev=# select * from awesome.
awesome.deparments         awesome.schema_migrations  
dev=# select * from awesome.deparments 
dev-# ;
 id | name | description | inserted_at | updated_at 
----+------+-------------+-------------+------------
(0 rows)

dev=# select * from awesome.schema_migrations ;
    version     |        inserted_at         
----------------+----------------------------
 20180405100559 | 2018-04-11 00:17:15.038774
(1 row)

I am using version 1.2.0 from hexpm and I am putting %{prefix: Triplex.to_prefix(tenant)} as the third parameter for both Repo.insert and other Repo's functions.
I was wondering are there anyways I can debug this to see what is the source of this issue?

Migrating on existing tenants in Production

How do you run migrations in production environments with no mix?

I made changes to some of my tables and would like to propagate this to already existing tenants.

Do we need to keep a table with existing [tenants]?(

triplex/lib/triplex/config.ex

Line 19 in f49c43f

tenant_table: :tenants

)

Force prefix option repo ?

I have a doubt
I configured the Plug, Triplex.SubdomainPlug correctly so that I have the prefix to the "tenant" variable always in @conn

however, when I give a Repo.All(envs) for example, shouldn't it automatically put the prefix based on my subdomain?
or do I really have to put the prefix: prefix: Triplex.to_prefix(@Tenant) parameter in every query?

Mariaex.Error on 1.3.0-rc.0

I am getting the following error:
lib/triplex.ex:176: Mariaex.Error.__struct__/0 is undefined, cannot expand struct Mariaex.Error

Remove deprecated code

Is there any plan or intention to support SQLite?

Hello!

I just discovered Triplex and while I was exploring it I realized the SQLite-based multi-tenant implementation I'm working on is quite similar to what Triplex does for Postgres and MySQL. So, I'm wondering if there are plans, or perhaps intentions, to support SQLite.

Triplex.create is too slow for tests

I'm trying to figure out how to incorporate Triplex into my unit tests.

Right now, I have Triplex.create :test in my setup function, but this has exponentially increased the length of time it takes to run the tests. Is there a better way?

Remove hard dependency on plug

It would be cool not to depend hardly on plug, and compile the plug only when plug is a loaded dependency.

Resolve `redefining module` warnings

Investigate the dynamic repo's idea

This is the article about it https://underjord.io/ecto-multi-tenancy-dynamic-repos-part-1-getting-started.html

Running tenant migrations without Mix for apps deployed via mix release

Scenario

We deploy using releases, thus Mix is not available and we cannot migrate in production (or other server environments) using the Triplex Mix tasks. We run our public schema migrations via a similar Release module to the one recommended in the Phoenix documentation. To that basic structure, we added an additional migrate_tenants function:

defmodule MyApp.Release do
  @app :my_app

  def migrate do
    load_app()

    for repo <- repos() do
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
    end
  end

  def migrate_tenants do
    load_app()

    for repo <- repos() do
      for tenant <- Triplex.all(repo) do
        Triplex.migrate(tenant, repo)
      end
    end
  end

  defp repos do
    Application.fetch_env!(@app, :ecto_repos)
  end

  defp load_app do
    Application.load(@app)
  end
end

Problem

The migrate_tenants function succeeds when run via a remote IEx session or rpc, but fails when run via eval, ie bin/my_app eval "MyApp.Release.migrate_tenants()". This is in contrast to the migrate function which succeeds when run via either remote IEx, rpc, or eval. The error observed is the following:

** (RuntimeError) could not lookup Ecto repo MyApp.Repo because it was not started or it does not exist
    lib/ecto/repo/registry.ex:19: Ecto.Repo.Registry.lookup/1
    lib/ecto/adapter.ex:127: Ecto.Adapter.lookup_meta/1
    lib/ecto/adapters/sql.ex:404: Ecto.Adapters.SQL.query/4
    lib/ecto/adapters/sql.ex:362: Ecto.Adapters.SQL.query!/4
    lib/triplex.ex:289: Triplex.all/1
    (myapp 0.1.0) lib/myapp/release.ex:21: anonymous fn/2 in MyApp.Release.migrate_tenants/0
    (elixir 1.11.4) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
    (myapp 0.1.0) lib/myapp/release.ex:20: MyApp.Release.migrate_tenants/0

Questions

• Is it possible to migrate tenants using Triplex via eval on a release?
• Is there a known root cause for this issue?
• Is there a recommended approach for using Triplex with Mix releases in general?

Notes:

• This issue seems somewhat similar to #66, but I have different questions so I opened a new issue.
• Posted same scenario on Elixir forum: https://elixirforum.com/t/how-to-run-triplex-tenant-migrations-when-deploying-via-mix-release/42135

`mix triplex.migrate` didn't migrate public

After move priv/repo/migration to priv/repo/tenant_migrations, mix ecto.migrate is not working anymore. Should mix triplex.migrate also migrate the public prefix?

Does Tripex support the new MyXQL package?

I don’t seems to get triplex to work with myxql package and I can’t use the mariaex package because it’s obsolete.

Deprecate `put_current_tenant/1` and `current_tenant/0` from `Triplex`

These functions are a little bit stateful and don't please me that much.

Creating tenant in tests

Saw #61 but I still get an error
{:error, "connection not available and request was dropped from queue after XXms. You can configure how long requests wait in the queue using :queue_target and :queue_interval. See DBConnection.start_link/2 for more information"}
For context I'm calling Triplex.create inside the test.

Create triplex plugs

For now, let's create:

• Triplex.Plug - which will have some basic functions for the plugs like saving the :current_tenant assign
• Triplex.ParamPlug - which reads the :current_tenant value from a param
• Triplex.SessionPlug - which reads the :current_tenant value from session
• Triplex.SubdomainPlug - - which reads the :current_tenant value from the subdomain
• Make the plugs accept a function callback that will be called after setting the current tenant
• Define a way pass this :current_tenant all the way to our database queries and use it to execute the queries and commands

Get a bigger coverage

Up to 70% at least

Ecto 3 Support

Please Make this library working with ecto 3.
I updated to ecto 3,
but when i run the migration with Triplex.create(..) in Repo.transaction(),this line:
https://github.com/ateliware/triplex/blob/master/lib/triplex.ex#L263
gives error: 'schema not found'
but it created schema before successfully with create schema ..;

other notes:
source_repo_priv function has moved to https://github.com/elixir-ecto/ecto_sql/blob/8c7acebea4d04b7e541441b678be62c820c88e22/lib/mix/ecto_sql.ex#L88

Making Triplex MySQL compatible

Hi everyone,

we started using Elixir for a new project with my team recently, and we are quite excited :)
We also needed multi-tenancy, and we liked Triplex approach to it. However, we also wanted to keep using our good old MySQL database. Because Triplex does not currently support MySQL, we extended it with MySQL support. Our approach works as follows:

1. We added a mix task (triplex.init.tenant ) that generates a migration to create a tenant table in the main ecto Repo's database. This table will contain a list of all registered tenants.
2. When using the MySQL ecto adapter, calls to Triplex.create and Triplex.drop will add/remove entries from the tenannts table and create/drop the respective MySQL databases. Calls to Triplex.rename will raise an exception because MySQL does not support renaming of databases
3. Everything else works as in the PostgreSQL case by prefixing table names with the name of the tenant (and thus the database).

This is our fork: https://github.com/maxmarcon/triplex-mysql

It's still rough at the edges, we will at the very minimum need to update your tests, but I was wondering if you guys are interested in eventually including our changes into your project (after a code review process, of course).

Thanks,
Max

cross schema relation in migration. is it possible to foreign key of a table from public schema to tenant schema in postgresql?

user table in public schema

  use Ecto.Migration

  def change do
    execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")
    execute("CREATE TYPE gender_t AS ENUM ('male', 'female', 'other')")
    execute("CREATE TYPE user_t AS ENUM ('teacher', 'student', 'admin', 'other')")

    create table(:users, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:email, :string, null: false)
      add(:phone, :string, null: false)
      add(:user_type, :user_t, null: false)
      add(:gender, :gender_t, null: false)
      add(:hash_password, :string)
      add(:first_name, :string)
      add(:last_name, :string)
      add(:username, :string, null: false)
      add(:deleted_at, :utc_datetime)
      add(:is_active, :boolean, default: true)
      timestamps()
    end

    create unique_index(:users, [:email])
    create unique_index(:users, [:username])
  end
end```

Institute Table in tenant schema 

```defmodule Data.Repo.Migrations.CreateInstitutes do
  use Ecto.Migration

  def change do
    create table(:institutes, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:title, :string, null: false)
      add(:established_at, :utc_datetime)
      add(:contact_no, :string, null: false)
      add(:image, :string)
      add(:email, :string)
      add(:location, :string)
      add(:deleted_at, :utc_datetime)
      # add(:inserted_by_id, references(:users, column: :id, type: :uuid))
      # add(:updated_by_id, references(:users, column: :id, type: :uuid))
      # add(:deleted_by_id, references(:users, column: :id, type: :uuid))

      timestamps()
    end

  end
end```


For adding user_id to institute table 

```defmodule Data.Repo.Migrations.AddUserIdToInstituteTable do
  use Ecto.Migration
  @fk_name "institutes_users_fkey"
  def up do
    prefix = Ecto.Migration.prefix
    query = "alter table #{prefix}.institutes add constraint #{@fk_name} foreign key (user_id) references public.users(id)"
             IO.inspect(query)
    Ecto.Adapters.SQL.query!(Data.Repo, query, [])
  end
end```




I'm using Triplex lib for multitenancy

Test coverage for mix tasks

Custom SQL in migrations

How do i get the schema name for custom SQL migrations?

e.g.

def up do
  execute "CREATE INDEX name_trgm_index ON users USING gin (nam gin_trgm_ops);"
end

When I run mix triplex.migrate, this fails because table "users" does not exist.

cast_assoc is not saving prefix.

company has many contacts i want to save contact via cast_assoc function

%Company{}
           |> Repo.preload([:contacts], prefix: tenant)
           |> Company.changeset(company_params,tenant)
           |> Ecto.Changeset.cast_assoc(:contacts, prefix: tenant)
           |> Repo.insert(prefix: tenant)

last line gives error. (undefined_table): relation "contacts" does not exist
Please Help.

Problem getting the migrations inside a OTP release

Hi,
Tried moving an app from heroku to a vps
Deployment using gatling and everything runs well except Triple Triplex.create("tenant") returns {:ok, []) on production.

The app is still running heroku and everything is great.

Platform
Ubuntu 16.04
Erlang/OTP 19 [erts-8.3.5] [source] [64-bit] [async-threads:10] [hipe] [kernel-poll:false]
Elixir 1.4.5
Deployment with gatling

Accept maps and structs on put_tenant

• Accept a map or a struct as the tenant in the function put_tenant\2
• Create a configuration of which key on the map or struct to use for the tenant name
• By default use the id
• Return itself if nil tenant

Triplex.all should not return prefixes

It should take config().tenant_prefix out. It is a breaking change though, as some people might be relying on the fact that it returns the prefixes.

A Triplex.migrate_all would be nice addition too.

Triplex.create fails with extremely cryptic error message using OTP 24.2

Today I encountered a very cryptic error message trying to run the seeds from a project. We don't have much insight from the stack trace besides knowing it was raised by Triplex.create/1.

** (MatchError) no match of right hand side value {:error, "Erlang error: :notsup"}

Since I couldn't find anything specific on the internet, I documented the problem here: erlang/otp#4577 (comment). It seems it has to do with the OpenSSL version 3 support and it was solved by upgrading the OPT version.

I'd like to see if it would be possible to provide a little bit more information on the stack trace on the Triplex side. Since the VM just crashes without giving more information on why or what dependency caused it.

Mix.Project.deps_paths/0 not found deploying with edeliver

Any idea on how to deploy this with edeliver? Whenever I ran mix edeliver migrate staging it does not run the tenant migrations.

Any help would be greatly appreciated.

Thanks!

Create a configuration for the tenants prefix

Empty by default.

Do not use Ecto's private APIs

Review the whole lib and change ecto's private APIs calls to public ones.

function Mix.Project.deps_paths/0 is undefined (module Mix.Project is not available) with Edeliver

While trying to create a tenant using Triplex.create("tenantname") for some reason I am getting this error. Here is the error when running the application in foreground using edeliver :

INSERT INTO "customers" ("name","subdomain","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["Dev Company", "dev-company", {{2018, 6, 15}, {11, 53, 40, 496542}}, {{2018, 6, 15}, {11, 53, 40, 496557}}]
[debug] QUERY OK db=4.7ms queue=0.2ms
CREATE SCHEMA "dev-company" []
[info] Sent 500 in 30ms
[error] #PID<0.1768.0> running AppWeb.Endpoint (cowboy_protocol) terminated
Server: myediteddomain.com:80 (http)
Request: POST /create_step_two
** (exit) an exception was raised:
    ** (UndefinedFunctionError) function Mix.Project.deps_paths/0 is undefined (module Mix.Project is not available)
        Mix.Project.deps_paths()
        (ecto) lib/mix/ecto.ex:172: Mix.Ecto.source_repo_priv/1
        (triplex) lib/triplex.ex:188: Triplex.migrations_path/1
        (triplex) lib/triplex.ex:172: Triplex.migrate/2
        (app) lib/app_web/controllers/workspace_controller.ex:62: appWeb.WorkspaceController.create_step_two/2
        (app) lib/app_web/controllers/workspace_controller.ex:1: appWeb.WorkspaceController.action/2
        (app) lib/app_web/controllers/workspace_controller.ex:1: appWeb.WorkspaceController.phoenix_controller_pipeline/2
        (app) lib/app_web/endpoint.ex:1: appWeb.Endpoint.instrument/4

The project works fine locally but the error is appearing only in production. I am assuming it is doing it when trying to run tenant migrations that are under tenant_migrations folder. There is already an issue related to this #45 which claims to have solve this problem however that is not the case.

@kelvinst Your prompt reply would be appreciated as it is a blocker for us.

Configure coveralls.io

Bug: Triplex.all/1 does not consider tenant_prefix

Hello!

I have a database with different schemata, some belong to tenants, some not. To separate it better with Triplex, I am using the tenant_prefix config option. When I run Triplex.all() I'd expect to get only schemata which start with that configured prefix. But I just get all schemata in the database. I've noticed thatTriplex.exists?/1 takes the tenant_prefix into consideration.

Release 1.3?

Noticed that 1.3.0-rc.1 already out for a while. When can you publish the version to 1.3? I asked because we want to use ecto 3, which doesn't work with 1.2. thanks.

warnings in compile

Erlang/OTP 25 [erts-13.0.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit:ns]

Elixir 1.14.3 (compiled with Erlang/OTP 25)

==> triplex
Compiling 17 files (.ex)
warning: incompatible types:

map() !~ atom()

in expression:

# lib/mix/triplex.ex:152
repo.config()

where "repo" was given the type map() (due to calling var.field) in:

# lib/mix/triplex.ex:152
repo.__adapter__

where "repo" was given the type atom() (due to calling var.fun()) in:

# lib/mix/triplex.ex:152
repo.config()

HINT: "var.field" (without parentheses) implies "var" is a map() while "var.fun()" (with parentheses) implies "var" is an atom()

Conflict found at
lib/mix/triplex.ex:152: Mix.Triplex.ensure_started/2

warning: incompatible types:

map() !~ atom()

in expression:

# lib/mix/triplex.ex:117
repo.stop()

where "repo" was given the type map() (due to calling var.field) in:

# lib/mix/triplex.ex:101
repo.config

where "repo" was given the type atom() (due to calling var.fun()) in:

# lib/mix/triplex.ex:117
repo.stop()

HINT: "var.field" (without parentheses) implies "var" is a map() while "var.fun()" (with parentheses) implies "var" is an atom()

Conflict found at
lib/mix/triplex.ex:117: Mix.Triplex.run_tenant_migrations/5

New release on hex.pm?

The last release on hex.pm is from May 31, 2019. There have been a few commits since then, and there are also a couple of PRs pending. Is there anything the users of the lib could do to help move this along?

Cheers!

Extract plugs to another lib

Create a new lib triplex_plug and extract all plugs to this lib. This way we will make triplex leaner.

Create a `Triplex.put_tenant\2` function to add the given prefix to prefixables

The prefixable objects are:

• An Ecto.Changeset
• An Ecto.Schema
• An Ecto.Queryable

Looking for a top level `rollback` function like `Triplex.migrate/2`

I'm adding a in-code Release module so I can perform migrations (and rollbacks) from the complied binary. I see that there is an in-code Triplex.migrate/2 function but I could not find a similar function for rollback behavior. Am I missing this? Interested in a patch?

Thanks for your help, here and with the project in general.