MySQL multi-tenancy

@stratum-hq/mysql adds multi-tenant row isolation for applications using MySQL. Stratum supports PostgreSQL, MongoDB, and MySQL in the same project.

How it fits together: The control plane (tenant hierarchy, config inheritance, audit log, and GDPR operations) always lives in PostgreSQL via @stratum-hq/lib. MySQL carries your application data. The two databases are complementary, not competing.

Choosing a strategy

Three isolation strategies are available, set once per deployment:

Strategy	Boundary	Isolation Level	Best For
SHARED_TABLE	tenant_id column	Application-enforced	Most applications, high tenant count
TABLE_PER_TENANT	Table namespace	Application-enforced	Apps needing separate indexes per tenant
DATABASE_PER_TENANT	Separate MySQL database	Connection-level	Compliance requirements, sensitive data

Shared table

All tenants share the same MySQL tables. A tenant_id column on every table scopes queries. The adapter’s structured methods (scopedSelect, scopedInsert, scopedUpdate, scopedDelete) automatically inject and filter tenant_id on every operation.

Tradeoff: Isolation is application-enforced. MySQL has no equivalent of PostgreSQL Row-Level Security. A bug in your application code could expose cross-tenant data. Mitigate this with isolation tests in your test suite and code review discipline.

To start a project on this strategy, generate it with npx @stratum-hq/create my-app --preset mysql-shared-<orm>-<framework>, where <orm> is pg (the mysql2 driver, through MysqlSharedAdapter), knex or sequelize. See @stratum-hq/create.

Table-per-Tenant

Each tenant gets its own set of tables, named {table}_{tenantSlug}. This provides namespace separation but not security isolation: both tenants’ tables still live in the same database and are accessible to the same connection.

Tradeoff: Good for applications that need different indexes per tenant. Does not prevent a misconfigured query from reading another tenant’s table.

Database-per-Tenant

Each tenant gets a dedicated MySQL database with its own connection pool. This is the strongest isolation available in MySQL.

Tradeoff: Each database requires its own connection pool. For large tenant counts, configure the pool manager’s maxPools and idleTimeoutMs to avoid exhausting MySQL connections. Idle pools are automatically closed after 60 seconds by default.

Tip

Prefer DATABASE_PER_TENANT when tenants have compliance requirements (healthcare, financial services) or when your threat model requires physical data separation.

Getting started

Install the package:

npm install @stratum-hq/mysql mysql2

Shared table

import mysql from "mysql2/promise";
import { Pool } from "pg";
import { Stratum } from "@stratum-hq/lib";
import { MysqlSharedAdapter } from "@stratum-hq/mysql";

// Control plane: always PostgreSQL
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const stratum = new Stratum({ pool, autoMigrate: true });
await stratum.initialize();

// MySQL adapter for application data
const mysqlPool = mysql.createPool(process.env.MYSQL_URL!);
const adapter = new MysqlSharedAdapter({
  pool: mysqlPool,
  databaseName: "myapp",
});

// Create a tenant (stored in PostgreSQL control plane)
const tenant = await stratum.createTenant({
  name: "Acme Corp",
  slug: "acme",
});

// Structured query methods auto-inject tenant_id
const users = await adapter.scopedSelect(tenant.id, "users");
await adapter.scopedInsert(tenant.id, "users", { name: "Alice", email: "alice@acme.com" });
await adapter.scopedUpdate(tenant.id, "users", { name: "Bob" }, { id: 1 });
await adapter.scopedDelete(tenant.id, "users", { id: 1 });

Table-per-Tenant

import { MysqlTableAdapter } from "@stratum-hq/mysql";

const adapter = new MysqlTableAdapter({
  pool: mysqlPool,
  databaseName: "myapp",
  // Every base table that has a per-tenant copy. Required by scopedTable and purgeTenantData.
  baseTables: ["users", "orders"],
});

// Returns the escaped table name: `users_acme`
const tableName = adapter.scopedTable("acme", "users");

// Use the pool directly with the scoped table name
const [rows] = await mysqlPool.query(`SELECT * FROM ${tableName}`);

Database-per-Tenant

import { MysqlDatabaseAdapter } from "@stratum-hq/mysql";

const adapter = new MysqlDatabaseAdapter({
  createPool: (uri) => mysql.createPool(uri),
  baseUri: "mysql://root@localhost:3306/placeholder",
  maxPools: 20,         // LRU eviction beyond this count
  idleTimeoutMs: 60000, // close idle pools after 60 seconds
});

// Returns a pool connected to stratum_tenant_acme
const tenantPool = await adapter.getPool("acme");
const [rows] = await tenantPool.query("SELECT * FROM users");

// Clean up on app shutdown
await adapter.closeAll();

ORM integrations

TypeORM subscriber

Auto-injects tenant_id on every insert using AsyncLocalStorage context, and never lets an update change tenant_id (save() keeps the loaded value; update() and query builder updates drop it from the SET values):

import { registerStratumSubscriber } from "@stratum-hq/mysql";

const dataSource = new DataSource({
  // ... your TypeORM config
});
await dataSource.initialize();
// Adds one StratumTypeOrmSubscriber. A second call adds nothing.
registerStratumSubscriber(dataSource);

Call registerStratumSubscriber() after dataSource.initialize(). It throws before that, because initialize() replaces the subscriber list. Do not put StratumTypeOrmSubscriber in the subscribers option: TypeORM only loads classes decorated with @EventSubscriber() from that option.

The subscriber reads the current tenant from the AsyncLocalStorage context of @stratum-hq/sdk. The SDK’s Express and Fastify middleware set that context for each request. Anywhere else, for example in a background job, run the queries inside runWithTenantContext, and await them inside the callback so they run in the context:

import { runWithTenantContext } from "@stratum-hq/sdk";

// `client` is a StratumClient; resolveTenant returns the tenant's context.
const context = await client.resolveTenant(tenantId);
const notes = await runWithTenantContext(context, async () => {
  return await repo.find();
});

Install @stratum-hq/sdk in your application (npm install @stratum-hq/sdk), so that your code and the subscriber use the same copy of the context.

registerStratumSubscriber() also scopes updates and deletes to the current tenant: repository update(), delete(), softDelete(), restore(), save() of an existing row, remove(), and query builder updates and deletes get tenant_id = <current tenant> ANDed to their WHERE clause. A row of another tenant is left unchanged, and an update or delete of a tenant table outside a tenant context is refused. save() of a row that belongs to another tenant throws: before an insert (other than an upsert) whose entity supplies its whole primary key, the subscriber looks that key up without the read scope, uses the result only for this check, and refuses the insert when the row belongs to another tenant. An update or delete builder aimed at a raw table name (not an entity) is treated as a tenant table and always gets the tenant condition, so it fails on a table without tenant_id. TRUNCATE of a table with a tenant_id column (repository.clear(), queryRunner.clearTable()) is refused, because it would remove every tenant’s rows. A subscriber added to dataSource.subscribers by hand refuses every UPDATE and DELETE, so always register it with registerStratumSubscriber().

Upserts (repository.upsert() and .orUpdate()) also get the current tenant’s tenant_id on insert. If the conflict update writes tenant_id, the subscriber rejects the statement before it runs. To upsert, leave tenant_id out of the entity values and out of the orUpdate() columns:

// Allowed: the conflict update writes `name` only.
await repo.upsert({ id: 1, name: "Renamed" }, ["id"]);

// Rejected: the conflict update would write `tenant_id`.
await repo.upsert({ id: 1, tenant_id: "other", name: "Renamed" }, ["id"]);

Inserts get the current tenant’s tenant_id, in whichever entity property maps to the tenant_id column (a relation whose join column is tenant_id cannot set or change it), and updates never change it. The insert and update query builders enforce both, so save(entity, { listeners: false }) and .callListeners(false) do not skip them.

INSERT ... SELECT (valuesFromSelect()) into a tenant entity is refused, because the tenant of the selected rows cannot be set. Into another table it is allowed, and a SELECT of a tenant entity keeps its tenant condition.

MySQL applies ON DUPLICATE KEY UPDATE on a conflict with any unique key of the table, whatever conflict columns you pass. The subscriber therefore allows an upsert only when every unique key of the target table, including the primary key, contains tenant_id (for example PRIMARY KEY (tenant_id, id)). It reads the keys from information_schema before the statement runs, and rejects the upsert otherwise.

registerStratumSubscriber() also scopes reads. Every query that TypeORM’s select query builder builds on the data source gets tenant_id = <current tenant>: repository find*(), findOne*(), count*(), exists*(), sum() / average() / minimum() / maximum() and preload(), query builder getMany(), getOne(), getRawMany(), getRawOne(), getCount(), getManyAndCount(), getExists() and stream(), relation loading (joins, eager relations, relationLoadStrategy: "query"), the row that save() loads before it writes, subqueries, and the count and pagination queries TypeORM builds internally. The condition is ANDed to the WHERE clause for the entity in FROM, so an orWhere() cannot widen the read, and it is added to the ON condition of every joined entity with a tenant_id column, so leftJoinAndSelect() still returns the parent row and leaves another tenant’s related row out. A read of an entity with a tenant_id column outside a tenant context is refused. Entities without a tenant_id column, and data sources without the subscriber, are not affected.

A view entity whose expression is a query builder is created by synchronize() without the tenant condition, because the view definition is one schema object shared by all tenants. Reads from a view entity are scoped like reads from a table when the view has a tenant_id column. A view without a tenant_id column is read unscoped.

Caution

Not scoped: raw SQL (dataSource.query(), queryRunner.query()), SQL taken from getQuery() / getQueryAndParameters() and run by hand (a select builder’s SQL includes the tenant condition, but an update or delete builder adds it only when its execute() runs), reads from and inserts into a table name that does not exactly match (including letter case) the name or table name of an entity on the data source (such inserts are not given a tenant_id), and many-to-many junction tables. Add the tenant condition to those yourself, or use the shared-table adapter’s structured methods.

Knex helper

Wraps any Knex query builder with automatic WHERE tenant_id = ?:

import { withTenantScope } from "@stratum-hq/mysql";

const tenantKnex = withTenantScope(knex, "acme");
const users = await tenantKnex("users").where("name", "like", q).orWhere("email", "like", q);
// Compiles to: WHERE tenant_id = 'acme' AND (name LIKE ? OR email LIKE ?)

Your where clauses are always grouped after the tenant filter, including on clones and when the builder is used as a subquery. insert() sets tenant_id, update() never changes it, and onConflict().merge(), upsert(), truncate() and modify() throw (a modify() callback would call the builder without these rules).

Joins (join(), leftJoin(), crossJoin(), joinRaw() and the other join forms) and union() / unionAll() also throw, because the tenant filter covers only the builder’s own table. To combine tables, use a tenant-scoped builder as a whereIn() subquery, or write the query with plain Knex and a tenant_id condition on every table.

Sequelize adapter

Scopes Sequelize models to one tenant for the duration of a callback:

import { withMysqlTenantScope } from "@stratum-hq/mysql";

await withMysqlTenantScope(sequelize, "acme", async (scoped, transaction) => {
  // Models with a tenant_id attribute see and change only this tenant's rows.
  const notes = await Note.findAll({ include: ["owner"], transaction });
  await Note.create({ name: "new" }, { transaction }); // tenant_id is set for you
});

Inside the callback, for every model with a tenant_id attribute:

• findAll(), findOne(), findByPk(), findAndCountAll(), count(), sum(), min(), max() and reload() return only the tenant’s rows. Every include of a tenant model is filtered in its join condition, including includes added by default and named scopes, by an included model’s own default scope, by include: { all: true } and by hooks, so an include that is not required still returns the parent row. A scope’s own where clause is kept.
• update(), destroy(), restore(), increment() and decrement() change only the tenant’s rows. Updates never write tenant_id, whether it is given by attribute name or column name, in any letter case, and increment() / decrement() of tenant_id are refused. As outside the helper, update(), destroy() and increment() without a where are refused by Sequelize.
• create(), save() of a new instance and bulkCreate() write the tenant’s tenant_id, whatever value the caller passed, and add it to a fields list that leaves it out.
• save(), destroy() and restore() of an instance whose row belongs to another tenant throw, and so do they for an existing instance of a model without a primary key, whose row cannot be checked.
• upsert(), bulkCreate() with updateOnDuplicate, truncate(), and or: true or right: true on an include of a tenant model are refused.
• Right before each query, the tenant condition is checked again on the where clause and on every include of a tenant model. A query whose condition a hook removed (for example a beforeFind hook that replaces options.where, or a beforeFindAfterOptions hook that adds includes) is refused.
• A query whose options name a tenant model or instance but that did not go through these methods throws, also when it runs inside a model hook. This covers, for example, queryInterface.select(Note, ...). queryInterface calls that take only a table name, such as bulkInsert(), bulkUpdate() and bulkDelete(), carry no model and are not scoped, like raw SQL.
• hooks: false does not skip the filter.

The helper also sets @stratum_tenant_id on the transaction’s connection and clears it in a finally block. Pass the transaction to queries that must run in it.

Not scoped: raw sequelize.query(), queryInterface calls by table name, models without a tenant_id attribute, the through (junction) model of a many-to-many include, and any code outside the callback. The helper throws when it is given something other than a Sequelize v6 instance.

MySQL views (not supported)

createTenantView() is deprecated and always throws. MySQL does not allow a view to read a session variable (ER_VIEW_SELECT_VARIABLE), so a view filtered on @stratum_tenant_id cannot be created. Use the shared-table adapter’s structured methods. setTenantSession() and dropTenantView() remain available.

GDPR compliance

All three adapters implement purgeTenantData() to permanently delete tenant data:

// Shared table: discovers tenant tables via INFORMATION_SCHEMA, deletes rows
const result = await sharedAdapter.purgeTenantData("acme");

// Table-per-tenant: drops exactly {base}_acme for each entry in baseTables
const result = await tableAdapter.purgeTenantData("acme");

// Database-per-tenant: drops the entire tenant database
const result = await dbAdapter.purgeTenantData("acme");

Check the result for partial failures:

if (!result.success) {
  console.error("Purge incomplete:", result.errors);
  // result.errors lists tables that failed with the error
}

Caution

Always check result.success after calling purgeTenantData(). Partial purges leave tenant data in place. For GDPR compliance, treat any error as a blocking issue.

Security considerations

MySQL does not have a Row-Level Security equivalent. Unlike PostgreSQL, there is no database-level mechanism that enforces tenant isolation independent of application code.

What this means in practice:

• For SHARED_TABLE and TABLE_PER_TENANT: if a query is executed without using the adapter’s structured methods, it will see all tenants’ data.
• For DATABASE_PER_TENANT: isolation is at the connection level: a connection opened to tenantA’s database cannot see tenantB’s data. This is the strongest isolation MySQL offers.

Recommendations:

1. Use DATABASE_PER_TENANT for sensitive data. This provides connection-level isolation that does not depend on application-layer filtering.
2. Always use structured methods (scopedSelect, scopedInsert, etc.) or the Knex/Sequelize integrations for tenant-scoped queries. Never query raw tables without tenant filtering.
3. Add isolation tests to your test suite to catch regressions before they reach production.
4. Audit all query paths that touch tenant data, especially background jobs and admin scripts.

Note

This is an honest limitation of the MySQL data model. If your compliance requirements mandate database-level enforcement, use DATABASE_PER_TENANT or consider whether PostgreSQL with RLS is a better fit for your data.

Performance

Shared table index requirements

Add a compound index with tenant_id as the leading field on every table:

CREATE INDEX idx_users_tenant ON users (tenant_id, created_at);
CREATE INDEX idx_orders_tenant ON orders (tenant_id, status);

Without tenant-prefixed indexes, every scoped query does a full table scan.

Database-per-Tenant pool tuning

Each tenant database gets its own connection pool. Tune maxPools and idleTimeoutMs based on your active tenant count and MySQL instance capacity:

const adapter = new MysqlDatabaseAdapter({
  createPool: (uri) => mysql.createPool(uri),
  baseUri: process.env.MYSQL_URL!,
  maxPools: 50,          // max concurrent tenant pools (default: 20)
  idleTimeoutMs: 60000,  // close idle pools after 60s (default: 60000)
});

A rough formula: maxConnections = activeTenants * connectionsPerPool. For 50 active tenants at mysql2’s default connectionLimit: 10, budget 500 connections on your MySQL instance.

MySQL views performance

Views referencing session variables (@stratum_tenant_id) may not use indexes efficiently because MySQL treats session variables as opaque runtime values. The optimizer cannot push them into index lookups.

For high-performance workloads, use the shared-table adapter’s structured methods instead. The parameterized WHERE tenant_id = ? in structured methods allows MySQL to use indexes normally.

See also

• Compare Stratum with plain MySQL, feature by feature