Dynamic multi-datasource support in Grails

Many Australian software teams operate SaaS products in which customer records do not all live in one database. A booking platform might keep ordinary customers in a primary PostgreSQL database while enterprise tenants receive isolated databases. The application must select the right connection from an authenticated tenant mapping at request time, without editing Grails configuration or restarting the service.

Grails supplies named datasources, GORM persistence, transaction managers and connection pooling. Dynamic multi-datasource support comes from combining those pieces with a small routing layer that treats the datasource choice as application context. The design must also prevent a session, transaction or cached query from crossing into another tenant's data.

Define the datasource boundary

Start by defining what a datasource means in your design. It is more than a JDBC URL: it includes a pool, a Hibernate session factory or entity manager factory, entity mappings and usually a transaction manager. Those resources must always describe the same physical database. Mixing a pool from one source with a transaction manager from another creates subtle failures that may appear only under load.

Keep ordinary connections in Grails’ normal dataSource configuration and declare named alternatives under grails.gorm.datasources. A minimal reporting profile looks like this:

dataSource:
  url: ${PRIMARY_DB_URL}
  username: ${PRIMARY_DB_USER}
  password: ${PRIMARY_DB_PASSWORD}
  driverClassName: org.postgresql.Driver
  dbCreate: none

grails:
  gorm:
    datasources:
      reporting:
        url: ${REPORTING_DB_URL}
        username: ${REPORTING_DB_USER}
        password: ${REPORTING_DB_PASSWORD}
        driverClassName: org.postgresql.Driver
        dbCreate: none

Configuration keys and GORM package mapping can differ between Grails releases, so use the conventions established by your project. Environment variables should supply secrets rather than literal passwords.

This separation makes runtime selection safer. The request chooses a known profile such as reporting; it never supplies a JDBC URL, username or password. If administrators genuinely need to add a database while the service runs, expose a privileged provisioning workflow that validates the endpoint, stores credentials in a secret manager, creates the pool and publishes the new profile atomically. Most applications should avoid that extra complexity.

Route requests without changing configuration

Build routing around a trusted tenant directory. It maps the authenticated tenant identifier to a stable source key, while a registry holds the managed datasource objects created during startup. Application code asks the registry for a required key and fails closed when the mapping is missing.

For example, the service can resolve the tenant and obtain its source-specific component without changing static configuration:

String sourceKey = tenantDirectory.sourceKeyFor(tenant.id)
ManagedDataSource target = registry.require(sourceKey)
target.orderStore.save(order)

Here, orderStore must be explicitly bound to the selected datasource rather than relying on Grails’ default GORM persistence context.

Passing the selected datasource through service boundaries is safer than hiding it in a global variable. Grails controllers can resolve it after authentication, but domain code should receive an explicit context object. Request-scoped state works for synchronous calls; executors, jobs and webhooks need the source key copied into their payload because a thread-local value will not travel with them.

The registry should cache pools and their session factories instead of opening a connection for every request. It can also support approved runtime profiles, but additions should be rare, audited and performed through a restricted management path. A missing profile, disabled tenant or expired database credential should produce a clear failure without an automatic switch to another customer’s source.

Keep sessions and transactions together

Named datasources do not make every GORM call dynamic. Static operations such as Order.list() normally use the default persistence context, so calling them after choosing reporting can still query the primary database. Keep this distinction explicit in method names and package boundaries.

Wrap the session and transaction manager for each managed datasource so callers cannot accidentally combine them. A small application-level wrapper can expose a consistent shape, even when helper names differ between Grails versions:

managed.withTransaction { status ->
    managed.withSession { session ->
        // Query and persist entities mapped to managed.source
    }
}

Use the session supplied or bound by the transaction, and complete the entire unit of work against one source. Do not resolve a tenant halfway through a transaction, move entities between persistence contexts or retry a failed operation through another datasource. A route change belongs between separate transactions, where the application can validate it again.

Entity ownership needs an equally clear rule. Separate domain packages work well when sources contain different models or table structures. When identical domain classes must operate against several databases, avoid static GORM lookups and use the explicit session for that source. Remember that a Session is not thread-safe and should never be cached in a singleton or shared between requests.

Separate credentials from runtime choices

Keep configuration, credentials and routing policy in separate layers. YAML or equivalent files define connection profiles and pool limits; a tenant directory defines which profile each customer may use; the request supplies only the authenticated tenant identity. This prevents a client from turning a datasource name into an arbitrary database selector.

Read credentials from environment variables or a managed secret store at startup. Give every profile a bounded connection pool and sensible idle and maximum lifetime values. A tenant should not be able to create a new pool by changing a request parameter, and logging should identify the profile without exposing passwords, full JDBC URLs or sensitive tenant information. Restrict database accounts to the operations the application actually needs.

Validate the profile mapping before opening a connection and cap the number of datasource profiles per process. Tenant-specific labels and formats belong in the presentation layer after routing; the guidance on localised view messages shows how message bundles can keep those strings outside domain code. Locale selection and database selection are related context, but they must remain independent policies.

Handle failure without crossing tenants

Treat each datasource as an independent failure domain. Set connection and query timeouts, monitor pool saturation and keep health checks specific to the database behind that profile. A slow tenant database should not consume every worker thread or exhaust the default pool.

Give logs and traces a stable route tag, source identifier and operation name. A route tag is metadata rather than an authorisation mechanism, much as poker player tags describe a category without deciding which cards a player receives. Use low-cardinality source labels for dashboards, while keeping customer identifiers out of metric names to prevent accidental disclosure and uncontrolled metric growth.

Retries are safe only for idempotent work and should use the same source. Do not fall back from a dedicated tenant database to the shared database, because that can create cross-tenant queries or duplicate writes. A circuit breaker can reject new work for an unhealthy source, while a controlled reconnect can refresh a stale pool. The user-facing error should preserve the request context so support staff can identify the affected profile without guessing which connection was used.

Prove isolation under Australian conditions

Prove isolation with integration tests that use two real database engines through Testcontainers or an equivalent disposable setup. Create records under tenant A and tenant B, verify each query sees only its own rows, roll back a transaction, then repeat after connection-pool exhaustion and application restart. Also test unknown tenant mappings and a stale source profile; both should reject access rather than fall back to the default datasource.

Australian operations expose date and daylight-saving mistakes quickly. Display 31/12/2026 in DD/MM/YYYY format while storing timestamps in UTC, and never derive a tenant’s offset from the server locale. Sydney and Melbourne change their clocks during daylight saving, Brisbane remains on AEST, and Tasmania does not use the same daylight-saving rule, so a fixed +10:00 offset is unsafe.

Choose cloud placement from measured customer locations rather than a country-wide assumption. Sydney’s ap-southeast-2 and Melbourne’s ap-southeast-1 regions are common options, yet a customer in Brisbane or a regional Australian centre may still benefit from a different placement. Load tests should measure connection setup and query latency from each state, particularly when a tenant database sits in another region.

Australian buyers also evaluate recurring SaaS charges in AUD, usually including GST, and procurement may require contract terms that identify where data is stored. In incident notes, a time such as “four in the arvo” is ordinary Australian usage, so record timestamps with an explicit IANA zone and preserve the original zone when presenting operational schedules. Clear labels prevent local daylight-saving ambiguity from becoming a support incident.

Roll out through a repeatable workflow

Roll out the capability as a narrow, observable change. Begin with one extra datasource and one synthetic tenant, then introduce a registry, an explicit routing context and wrappers for GORM access. Avoid migrating every domain class on day one; isolate the feature behind a service boundary and expand only after tenant-isolation tests pass.

Before release, document each datasource profile, its owning service, pool limits, timeout behaviour and recovery procedure. Include the source key in structured logs and dashboards, test connections against every regional deployment, and rehearse rollback without changing tenant mappings. Keep a disabled profile from receiving traffic while preserving its configuration for diagnosis and controlled restoration.

Create the two-datasource branch now: configure one additional database, route a synthetic tenant through it, and make the isolation, timeout and rollback tests pass before connecting any production tenant.