Database and Multiple Datasources

Every Wheels model reads and writes through a named datasource. Most apps have exactly one, configured in config/settings.cfm and overridden per environment. Some apps need more — a legacy reporting database, a separate read replica, a tenant-per-database pattern. This page covers the knobs Wheels gives you for all of those cases, plus the transaction and raw-query escape hatches for the times the ORM isn’t enough.

You’ll learn:

• How to configure the default datasource for the whole app
• How to override it per environment and per model
• How to wrap a multi-statement unit of work in a transaction
• How to fall through to raw SQL when the ORM can’t express what you need
• What Wheels 4.0 supports across database engines

Note

You should already know how to define models. See Models and the ORM.

The default datasource

Wheels reads dataSourceName from config/settings.cfm at application start and stores it on application.wheels.dataSourceName. Every model uses this datasource unless it declares its own.

set(dataSourceName = "myapp_dev");

The name you set here must match a datasource registered with your CFML engine — Wheels only cares that the name resolves to a working connection. The next section shows the registration shape for each setup. In development, a name that doesn’t resolve surfaces as a Wheels.DataSourceNotFound error page naming the datasource (served with HTTP 404).

config/settings.cfm is the shared file loaded in every environment; per-environment overrides shadow it.

Registering a datasource

Where the registration lives depends on what serves your app:

With the wheels CLI — datasources live in lucee.json at the app root. wheels new writes the SQLite pair for you (it also registers them as this.datasources in config/app.cfm); add more under configuration.datasources using the same shape:

lucee.json (excerpt — the shape wheels new generates)

"configuration": {
  "datasources": {
    "myapp_dev": {
      "class": "org.sqlite.JDBC",
      "database": "myapp_dev",
      "dbdriver": "Other",
      "dsn": "jdbc:sqlite:{project}/db/development.sqlite",
      "host": "", "username": "", "password": ""
    }
  }
}

For MySQL/Postgres, swap class/dsn for the driver’s JDBC form (e.g. com.mysql.cj.jdbc.Driver with jdbc:mysql://localhost:3306/myapp). Restart the server (wheels stop && wheels start) after editing.

With CommandBox — apps installed with CommandBox (box install wheels-base-template) ship a box.json that includes commandbox-cfconfig; declare datasources in server.json and CFConfig applies them at server start. A wheels new app has no box.json or server.json:

server.json (excerpt)

"cfconfig": {
  "datasources": {
    "myapp_dev": {
      "dbdriver": "MySQL",
      "host": "localhost", "port": 3306,
      "database": "myapp",
      "username": "wheels", "password": "${DB_PASSWORD}"
    }
  }
}

With your own server (manual installs) — register the datasource in the engine’s admin: Lucee’s server or web admin, or the ColdFusion Administrator on Adobe (don’t rely on runtime-defined datasources on ACF — admin-registered is the reliable path).

Adobe ColdFusion + MySQL 8

If the app connects but models and the migrator act as if your tables don’t exist, add nullCatalogMeansCurrent=true to the datasource’s Connection String in the CF Administrator — MySQL Connector/J 8 changed its metadata default and schema reads come back empty without it. Details in IIS and Windows Deployment.

Adobe ColdFusion 2025: install graphqlclient

Install the graphqlclient package (cfpm install graphqlclient). Without it, Adobe ColdFusion 2025 raises The graphqlclient package is not installed. while loading your application’s datasources at startup, and the error log records it after every restart, even though the app never uses GraphQL. Installing the package stops the notice and the log noise. Wheels itself also needs the image, mail, zip and caching packages, and a MySQL datasource needs the Connector/J jar in cfusion/lib; see Manual Installation.

Per-environment datasource

Development, test, and production usually point at different databases. Keep the shared default in config/settings.cfm, then shadow it from config/<environment>/settings.cfm:

set(dataSourceName = "myapp_production");
set(dataSourceUserName = env("DB_USER"));
set(dataSourcePassword = env("DB_PASSWORD"));

Environment-specific settings are loaded after the shared ones, so whatever you set here wins. Read secrets from the environment with env("VAR_NAME") — never commit credentials into the repo. See Environments and Configuration for the full configuration loading order.

Per-model override

Any model can point at a different datasource by calling dataSource() in config(). This is the standard pattern for reaching into a legacy database that lives alongside your primary schema.

component extends="Model" {
    function config() {
        dataSource("legacy_reporting");
        table("vw_legacy_users");
        setPrimaryKey("user_id");
    }
}

The typical legacy case is all three overrides at once: a non-default datasource, a non-convention table name (often a view), and a non-id primary key. The rest of the model still behaves like any other — associations, validations, finders, and the query builder all use the datasource you’ve declared.

Caution

The table-name setter is table(). tableName() is a zero-argument getter — calling tableName("vw_legacy_users") silently ignores the argument, the model resolves the conventional table name instead, and the first query throws Wheels.TableNotFound (#3079).

dataSource() also accepts username and password arguments for databases that need per-model credentials:

component extends="Model" {
    function config() {
        dataSource(datasource="legacy_reporting", username="report_reader", password=env("REPORT_DB_PASSWORD"));
    }
}

Read replicas

Wheels 4.0 does not ship a built-in read-replica feature — there’s no readReplica="..." argument on finders or a framework-level read/write split. If you want to send reads to a replica and writes to the primary, you build the pattern yourself with two datasources and per-model overrides.

The simplest approach: register both databases, keep the primary as your default, and declare a thin read-only model that points at the replica.

config/settings.cfm

set(dataSourceName = "myapp_primary");

app/models/UserReadOnly.cfc

component extends="Model" {
    function config() {
        dataSource("myapp_replica");
        table("users");
        setPrimaryKey("id");
    }
}

Queries against model("UserReadOnly") hit the replica; model("User") continues to hit the primary. You are responsible for making sure nothing mutates data through the read-only model, and for understanding replica lag — reading back a record you just wrote may return the pre-write version for a short window.

A framework-level read/write split is under consideration for a future release. Until then, the pattern above is the supported path.

Transactions

Wheels gives you three ways to wrap work in a transaction. Which one you pick depends on how many statements you’re coordinating.

Single model method. Every persistence method — save, create, update, delete — accepts a transaction argument. Pass "rollback" to run the statement inside a transaction and always roll it back (useful for dry-runs and tests) or "commit" (the default — persistence methods default their transaction argument to the transactionMode setting, whose framework default is "commit") to commit normally.

component extends="Controller" {
    function preview() {
        user = model("User").findByKey(params.key);
        user.update(balance=user.balance - 100, transaction="rollback");
        // changes are discarded; user.balance in memory reflects the attempted value
    }
}

Multiple statements, one model. Call invokeWithTransaction(method="...") to run a single model method inside an explicit transaction block.

component extends="Controller" {
    function settle() {
        account = model("Account").findByKey(params.key);
        account.invokeWithTransaction(method="settleOutstandingCharges");
    }
}

The invoked method must return a boolean — invokeWithTransaction() throws Methods invoked using invokeWithTransaction must return a boolean value otherwise. Returning true commits; returning false rolls the transaction back, even with transaction="commit". Make sure settleOutstandingCharges() ends with return true; on its success path.

Multiple statements, multiple models. Use a native CFML transaction block. Everything inside runs against the same connection and commits or rolls back as a unit. An uncaught exception inside rolls back automatically; an explicit rollback uses transaction action="rollback".

Model writes inside the block join it on every engine, whatever isolation level the block uses. Add isolation="..." to the block only to choose that level. Without it, the block runs at the engine’s or driver’s default, which isn’t always the database’s: on MySQL it’s REPEATABLE READ under Lucee and BoxLang, but READ COMMITTED through Adobe ColdFusion’s MySQL datasource. If you pass isolation to a model method inside the block yourself, it must match the block’s: Adobe ColdFusion can’t nest a different level, so Wheels raises Wheels.TransactionIsolationMismatch there rather than silently dropping the level you asked for.

component extends="Controller" {
    function transfer() {
        transaction {
            from = model("Account").findByKey(params.fromKey);
            to = model("Account").findByKey(params.toKey);
            from.update(balance=from.balance - params.amount);
            to.update(balance=to.balance + params.amount);
        }
        redirectTo(route="accounts");
    }
}

afterCommit and afterRollback inside a raw transaction

Model afterCommit and afterRollback callbacks don’t fire reliably for writes inside a raw transaction {} block. Lucee, BoxLang and Adobe CF skip them with a warning; on RustCFML the outcome is up to the engine. Use invokeWithTransaction() for writes that rely on them. See After the transaction.

Transactions commit when the block exits cleanly and roll back on any thrown exception. All statements in a transaction must run against the same datasource — cross-database transactions aren’t a thing the engine can give you. Adobe ColdFusion enforces this with an error (Datasource names for all the database tags within the cftransaction tag must be the same.); Lucee 7 does not — it runs the statements against both datasources but provides no cross-database atomicity, so the failure mode is silent.

Isolation level

Transactions that Wheels opens for model writes and invokeWithTransaction() use the read_committed isolation level unless the call passes isolation. The transactionIsolation setting changes that default for the whole app:

config/settings.cfm

set(transactionIsolation = "repeatable_read");

It accepts read_uncommitted, read_committed (the default), repeatable_read and serializable. It also accepts "", which sends no isolation attribute at all, so the transaction uses the engine’s or driver’s default. The same per-engine difference as raw transaction blocks applies to "". On MySQL it’s REPEATABLE READ under Lucee and BoxLang and READ COMMITTED through Adobe ColdFusion’s MySQL datasource. To get one level everywhere, name it rather than using "".

An isolation argument on the call always wins over the setting. A model write inside a raw transaction block joins it whatever the setting is.

Nested units with savepoints

A persistence call or invokeWithTransaction() made while a transaction is already open joins that transaction. A joined call can’t undo only its own writes: returning false from it, or throwing, doesn’t roll back just its part.

transaction="savepoint" runs the call as a nested unit instead. Inside an open transaction, Wheels sets a savepoint and runs the method. If the method returns false or throws, Wheels rolls back to the savepoint, which undoes only the unit’s writes. A throw is then rethrown. The outer transaction keeps its earlier writes and commits or rolls back on its own outcome. With no transaction open, savepoint behaves exactly like commit. It works through every persistence method’s transaction argument too, for example create(..., transaction="savepoint").

component extends="Model" {
    // Imports each manifest as its own all-or-nothing unit. A bad manifest is
    // rolled back on its own; the good ones commit with the batch.
    function importBatch() {
        for (var manifest in variables.manifests) {
            variables.current = manifest;
            invokeWithTransaction(method="importCurrentManifest", transaction="savepoint");
        }
        return true;
    }
}

Called as model("Shipment").invokeWithTransaction(method="importBatch"), the batch is one transaction and each manifest is a savepoint inside it.

• Callbacks: records written inside a unit that rolls back get their afterRollback callbacks right away, and never afterCommit. Records written outside the unit follow the outer transaction’s outcome.
• Each unit runs one extra query first: a primary-key read on the model’s table. Lucee silently drops a savepoint set before the transaction’s first query, and the read makes sure the connection is active.
• Same datasource: a unit nests in the transaction open on its own model’s datasource. Run units through models that use the outer transaction’s datasource, as with any transaction.
• Raw transaction {} blocks: a savepoint unit detects a raw outer transaction {} on Lucee, BoxLang and Adobe ColdFusion. On RustCFML the unit can’t see the raw block, and it joins it like commit. To nest units portably, open the outer transaction with invokeWithTransaction().

If you write raw savepoint code yourself, the same Lucee rule applies: run a query in the transaction before transaction action="setsavepoint". Otherwise the savepoint is silently dropped, and the later transaction action="rollback" savepoint="..." throws There are no savepoint with name [...] set.

Raw queries

For the rare query the ORM can’t express cleanly — a reporting aggregate, a window function, a vendor-specific feature — drop through to native CFML queryExecute(). Point it at the app’s configured datasource with the datasource option (don’t hardcode an environment-specific name — that undoes the per-environment configuration above) and use positional parameters for any user input.

component extends="Model" {
    public query function topContributors(numeric limit=10) {
        return queryExecute(
            "SELECT userId, COUNT(*) AS postCount FROM posts GROUP BY userId ORDER BY postCount DESC LIMIT ?",
            [arguments.limit],
            {datasource: get("dataSourceName")}
        );
    }
}

The ? placeholder plus the parameters array gets you parameter binding through cfqueryparam. Never concatenate user input into the SQL string — that’s the canonical SQL-injection gap. The rule is: if a value comes from params, session, or any request-scoped source, it goes through a placeholder, not through string interpolation.

For model instances, use findOne()/findByKey() or findAll(returnAs="objects") — plain findAll() already returns a query result by default, so reach for raw queryExecute() only when neither the ORM nor the query builder can express the SQL you need.

Connection pooling

Each CFML engine keeps a connection pool per datasource. Lucee manages this through the datasource configuration in lucee.json (or the web admin); default pool sizing handles most workloads without tuning. If you run into connection-exhaustion errors under load, the usual culprits are long-running transactions, unclosed cursors in custom code, or pool-size settings that haven’t been raised from the default. Consult your engine’s documentation for the exact knobs — the tunables live below the Wheels layer.

Supported databases

Wheels 4.0 has database adapters for:

• PostgreSQL — recommended for new apps.
• MySQL / MariaDB — fully supported, broad production use.
• Microsoft SQL Server — fully supported, common in enterprise shops.
• SQLite — great for development and testing, production-fine for small apps.
• H2 — embedded, great for tests that need a fresh database per run (Lucee-only in the CI matrix).
• CockroachDB — distributed SQL, PostgreSQL wire-compatible, with its own adapter.
• Oracle — supported with its own adapter (runs as a soft-fail leg in the CI matrix).

The Wheels 4.0 core test suite runs on Lucee 7 + SQLite for day-to-day development; the full CI matrix covers additional engines and databases. SQLite is the reference — if something works there but not on your target database, that’s a bug worth filing.

Related guides

Models and the ORM Defining a model, finders, persistence, and the non-convention overrides this page extends.

Query Builder and Scopes Chainable queries, reusable scopes, enums, and batch processing — the safe alternative to raw SQL.

Migrations Schema changes, column types, and indexes. The table structure your datasource exposes.

Environments and Configuration The configuration loading order. Datasource settings usually differ per environment.