Summary
When a Wheels model is backed by a database column whose name is a SQL reserved word (e.g., key in MSSQL, order in many dialects, etc.), several model operations fail because the SELECT/WHERE clause builder emits the column unquoted.
Even worse for the specific name key: it also collides with the built-in Model.key() accessor that returns the primary-key value. Using key as a non-PK column on a Wheels model is therefore doubly painful.
Repro
Migration:
```cfml
t = createTable(name="datapai_permissions");
t.string(columnNames="key", limit=100, null=false);
t.timestamps();
t.create();
addIndex(table="datapai_permissions", columnNames="key", unique=true);
```
The migration succeeds (Wheels' DDL generator does bracket the column name as `[key]` in CREATE TABLE / ADD CONSTRAINT). The problem appears at the model layer.
Model:
```cfml
component extends="app.models.Model" {
function config() {
table("datapai_permissions");
property(name="permissionKey", column="key"); // try to alias around the reserved word
validatesUniquenessOf(property="permissionKey");
super.config();
}
}
```
Then:
```cfml
model("DataPaiPermission").findOne(where="permissionKey = 'foo'");
// → 'Incorrect syntax near \'key\'.' (MSSQL)
```
The error happens because the WHERE-clause builder substitutes the property name for the column name in the user-supplied SQL fragment, producing unquoted `key = '...'`, which the SQL engine then rejects as a syntax error.
`findAll(where=...)`, `deleteAll(where=...)`, and dynamic finders like `findOneByPermissionKey()` hit the same wall.
`create()` and `update()` work because they use parameterized inserts/updates with bracketed identifiers in the DDL-generated path.
Impact
Anyone modeling external data with column names that happen to be SQL reserved words (`key`, `order`, `group`, `user`, `role`, etc.) hits this. The workaround — renaming the column — is invasive once production data exists.
The framework already brackets reserved words in DDL paths (table creation, index creation). The same logic doesn't reach the SELECT/WHERE builder.
Suggested fixes (any of)
- Bracket reserved words consistently in the WHERE/SELECT/dynamic-finder builders, not just in DDL. Lucee can detect the engine (`request.wheels.adapter`) and apply the right quoting (`[col]` for MSSQL, `"col"` for ANSI, backticks for MySQL).
- Detect-and-warn at migration time: when a migration creates a column named one of the common SQL reserved words, print a warning suggesting an alternate name and noting the WHERE-clause limitation.
- Doc fix: explicitly document this limitation in the model guide, list the reserved words that can't be used as column names, and recommend renaming.
Specifically for the name `key`
The collision with `Model.key()` is a separate, related issue — even if (1) is fixed, the property name `key` would still shadow the PK accessor. A specific warning when a migration creates a `key` column would help users avoid the pitfall before it becomes a load-bearing column.
Repo / version
- Wheels Core: `4.0.0-SNAPSHOT+1779`
- File: `vendor/wheels/model/sql.cfc` (WHERE-clause builder, around line 567)
Where found
Surfaced building the DataPAI permissions table in Titan (Phase 0). The implementer worked around it via raw `cfmlQuery` calls but the workaround forced the seed migration to also drop the model API — increasing cognitive overhead.
🤖 Found while executing a Claude-Code-driven implementation plan
Summary
When a Wheels model is backed by a database column whose name is a SQL reserved word (e.g.,
keyin MSSQL,orderin many dialects, etc.), several model operations fail because the SELECT/WHERE clause builder emits the column unquoted.Even worse for the specific name
key: it also collides with the built-inModel.key()accessor that returns the primary-key value. Usingkeyas a non-PK column on a Wheels model is therefore doubly painful.Repro
Migration:
```cfml
t = createTable(name="datapai_permissions");
t.string(columnNames="key", limit=100, null=false);
t.timestamps();
t.create();
addIndex(table="datapai_permissions", columnNames="key", unique=true);
```
The migration succeeds (Wheels' DDL generator does bracket the column name as `[key]` in CREATE TABLE / ADD CONSTRAINT). The problem appears at the model layer.
Model:
```cfml
component extends="app.models.Model" {
function config() {
table("datapai_permissions");
property(name="permissionKey", column="key"); // try to alias around the reserved word
validatesUniquenessOf(property="permissionKey");
super.config();
}
}
```
Then:
```cfml
model("DataPaiPermission").findOne(where="permissionKey = 'foo'");
// → 'Incorrect syntax near \'key\'.' (MSSQL)
```
The error happens because the WHERE-clause builder substitutes the property name for the column name in the user-supplied SQL fragment, producing unquoted `key = '...'`, which the SQL engine then rejects as a syntax error.
`findAll(where=...)`, `deleteAll(where=...)`, and dynamic finders like `findOneByPermissionKey()` hit the same wall.
`create()` and `update()` work because they use parameterized inserts/updates with bracketed identifiers in the DDL-generated path.
Impact
Anyone modeling external data with column names that happen to be SQL reserved words (`key`, `order`, `group`, `user`, `role`, etc.) hits this. The workaround — renaming the column — is invasive once production data exists.
The framework already brackets reserved words in DDL paths (table creation, index creation). The same logic doesn't reach the SELECT/WHERE builder.
Suggested fixes (any of)
Specifically for the name `key`
The collision with `Model.key()` is a separate, related issue — even if (1) is fixed, the property name `key` would still shadow the PK accessor. A specific warning when a migration creates a `key` column would help users avoid the pitfall before it becomes a load-bearing column.
Repo / version
Where found
Surfaced building the DataPAI permissions table in Titan (Phase 0). The implementer worked around it via raw `cfmlQuery` calls but the workaround forced the seed migration to also drop the model API — increasing cognitive overhead.
🤖 Found while executing a Claude-Code-driven implementation plan