Most SaaS backends start the same way: one database, a TenantId column in every table, and a WHERE TenantId = :tenant in every query. It works well, until the business grows:

• A large customer wants its data in a dedicated database, for compliance or performance reasons.
• The shared database keeps growing, and you want to spread tenants over several database servers.
• A customer stops paying, and you need to suspend it now, not at the next deploy.
• Sign-ups should create a tenant automatically, with its initial data, in whichever database has room.

From there, every team writes the same infrastructure: a table mapping tenants to databases, a connection pool per database, a cache so that table isn't queried on every request, error handling for unknown and suspended tenants, and some way to run background jobs for a given tenant. We have seen several of these in production Delphi servers. They all look alike, and each one took real effort to get right.

The next versions of TMS Aurelius and TMS XData ship that infrastructure built in: a tenant catalog with database sharding, wired into the request pipeline by a single component.

A quick refresher

TMS XData is a framework for building REST APIs in Delphi. It builds on TMS Aurelius, an ORM framework, to query and persist your entities, and runs on TMS Sparkle, a high-performance HTTP server framework.

Each of the three answers one question that a multi-tenant request raises:

The new TXDataTenantConnectionPool component ties them together: for every request it resolves the tenant, finds its database, hands XData a connection to it and enables the tenant filter. Your endpoints stay exactly as they are - no tenant parameter, no extra where clause, no second entity model.

One model, three topologies

A shard, here, is simply a database that hosts one or more tenants, and the catalog maps every tenant to exactly one shard. That single idea covers the usual layouts:

• Sharded: a few databases, several tenants in each. The general case.
• Database per tenant: one tenant per shard.
• Single database: everybody in one. You don't even need a catalog for this one - the tenant middleware plus a global filter is the whole solution.

Because they share one model, moving your biggest customer to a dedicated database later is a change in the catalog, not a rewrite.

Configuration, not code

A sharded server is four components on a data module:

• a TSparkleTenantMiddleware in the XData server's middleware list, saying where the tenant id comes from (HeaderName = 'tenant-id', for example) and whether it is mandatory;
• a TAureliusConnection pointing at the catalog database - the one that stores which tenant lives in which shard;
• a TAureliusTenantCatalog, connected to it;
• a TXDataTenantConnectionPool, with its Catalog property set to the catalog and EnableFilter set to True. Set it as the Pool of your TXDataServer and the server is multi-tenant.

The only routing code you write is one event: given a shard, return a connection to it. The catalog stores just a logical shard id, never a connection string or a password, so credentials stay in your configuration:

procedure TServerContainer.TenantPoolCreateShardConnection(Sender: TObject;
  const Shard: TShardInfo; var Connection: IDBConnection);
begin
  // FConfig is your configuration file, with one section per shard id
  Connection := TFireDacConnectionAdapter.Create(
    CreateFDConnection(FConfig, Shard.ShardId), True);
end;

The component pools connections per shard, creating each pool the first time a tenant of that shard shows up.

Starting the server is a handful of idempotent calls, safe to run at every start:

TenantCatalog.UpdateCatalogSchema;              // create/update the catalog tables
TenantPool.UpdateShardSchemas;                  // your tables, in every registered shard
TenantCatalog.AddTenant('acme', 'ACME Corp', 'shard-a');
TenantCatalog.AddTenant('globex', 'Globex');    // no shard given: the allocator picks one

Note that schema updates are an explicit step - they never happen as a side effect of serving a request.

Row isolation you can't forget to apply

When several tenants share a shard, the global filter declared on your entities is what keeps them apart:

  [Entity, Automapping]
  [FilterDef('Multitenant', '{TenantId} = :tenant_id')]
  [FilterDefParam('Multitenant', 'tenant_id', TypeInfo(string))]
  [Filter('Multitenant')]
  TProduct = class
  ...
    [XDataExcludeProperty]
    FTenantId: string;

From there it's automatic, in three places:

• The pool component enables the filter, with the current tenant id, on the object manager of every request. Queries only ever see the tenant's own rows.
• The filter enforcer stamps the tenant id on every insert and refuses writes to rows belonging to other tenants.
• XDataExcludeProperty keeps the tenant id out of the JSON payloads, so clients never see it and cannot set it.

Clients then simply say who they are:

GET /tms/api/Product HTTP/1.1
tenant-id: acme

Service operations get the same treatment: TXDataOperationContext.Current.GetManager returns a manager already connected to the current tenant's shard, with the filter enabled.

The tenant comes from the token, not from a header

A header is fine for an internal API, but on a public one any client can send any header. What you want is a tenant derived from something the caller can't forge.

The catalog stores identifiers for that: external keys registered for a tenant - a user id, an API key, a custom domain - which the pool's TenantLookups collection resolves on every request:

// The tenant of the authenticated user: token claim 'sub' -> identifier 'user_id'
TenantPool.TenantLookups.Add(TTenantLookupSource.UserClaim, 'sub', 'user_id');
// The tenant of an API client: header 'x-api-key' -> identifier 'api_key'
TenantPool.TenantLookups.Add(TTenantLookupSource.Header, 'x-api-key', 'api_key');
// The tenant owning a custom domain: request host name -> identifier 'domain'
TenantPool.TenantLookups.Add(TTenantLookupSource.Host, '', 'domain');

You register those keys as tenants gain users, API clients or domains (TenantCatalog.SaveIdentifier('user_id', UserId, 'acme')), and the lookups are cached - including the misses - so they don't hit the catalog database on every request.

They also close the obvious hole: if the header says globex but the access token belongs to an ACME user, the request is rejected with 400 TenantMismatch. A user of one tenant cannot reach another tenant's data by editing a header.

If your tokens come from TMS Sphinx, our OAuth 2.0 and OpenID Connect server framework, you can go further and put the tenant id in the token itself at issue time.

Failure modes a single-tenant server doesn't have

An unknown tenant, a suspended customer, a database that is down for maintenance: each has a right answer, and the component gives it, in the standard XData JSON error format.

The 503 responses carry a generic message, so connection errors and other infrastructure details never reach the client. And if you'd rather not let callers discover which tenant ids exist, AntiEnumeration answers inactive tenants with the same 404 as unknown ones.

Running the business, without restarting the server

This is where a catalog pays off. The everyday operations are one-liners, typically exposed through an administrative service:

• TenantCatalog.SuspendTenant('acme') and ActivateTenant - status is checked on every request, even when the tenant's connections are already pooled, so a suspended customer stops being served at once.
• TenantCatalog.MoveTenantToShard('acme', 'shard-b') - changes the routing, after you've copied the data.
• TenantPool.InvalidateShard('shard-a') - drops a shard's pool after rotating its database credentials. Requests in progress finish on their current connections; new ones get a pool built with the new settings. No restart.

Routing information is cached (30 seconds by default), so the catalog database isn't in the path of every request. Changes made through the component invalidate the cache immediately; other server processes pick them up when their entries expire.

New customers provision themselves

AddTenant is more than an insert: it allocates a shard, runs your provisioning code and only then activates the tenant. If provisioning fails halfway, the tenant stays Pending and the next call resumes it, so a sign-up that timed out can simply be retried.

• The shard is chosen for you. The default allocator is a weighted hash over the active shards; round robin, fixed shard, one shard per tenant and a custom callback are also available. Shards marked Draining keep serving their tenants but receive no new ones - that's how you retire a database gracefully.
• OnProvisionTenant runs inside a tenant scope, with routing already pointing at the new tenant's shard, so seeding initial data is ordinary Aurelius code.
• OnProvisionShard runs when a tenant needs a shard that doesn't exist yet - your handler creates the database and registers it. Combine it with the TShardPerTenantAllocator, which gives every tenant its own shard, and a sign-up ends with a brand new database, created and ready, before the tenant is activated.

Jobs, imports and schedulers

Not everything happens inside an HTTP request. A nightly report or a queue consumer needs to run "as" a tenant too, and TTenantScope does that for the current thread:

Scope := TTenantScope.Use(TenantId);
try
  // TenantPool now routes to this tenant's shard
  Manager := TObjectManager.Create(TenantPool.GetPoolInterface.GetConnection);
  ...
finally
  Scope := nil;
end;

Scopes nest, and the same routine works whether it's called from a scheduled job or from a service operation that has to act on another tenant.

And none of this is tied to HTTP. The catalog, the routing cache, the per-shard pools, the tenant scope and the provisioner are Aurelius classes (the new Aurelius.Tenancy.* units), usable from a desktop application, a worker process or a command-line provisioning tool. You can also plug in your own catalog - a client of a central catalog service, for instance - instead of the database-backed one.

Conclusion

Multi-tenancy used to be something every XData team built for itself. Now it's configuration:

• Any topology - shared database, database per tenant, or tenants spread over shards - from one model, with room to move between them as you grow.
• One event handler for routing. Resolution, pooling, caching and row filtering are handled by the components.
• Secure by default: isolation enforced on reads and writes, tenants resolved from tokens rather than headers, mismatch detection, anti-enumeration, and no infrastructure details in error responses.
• Day-2 operations built in: suspend, move and provision tenants, rotate credentials and update every shard's schema, without a restart.
• Beyond requests: the same routing in background jobs, and the whole layer usable without HTTP.

The MultiTenancy demo folder in TMS XData has it running: MultiTenantCatalog for the sharded setup described here, next to the simpler MultiTenantByHeader.

To learn more, read the XData Multi-Tenancy chapter, the Aurelius Multi-Tenancy chapter and Global Filters, and the what's new lists of TMS XData and TMS Aurelius. And visit the product pages of TMS XData and TMS Aurelius, both part of TMS BIZ.