What's New?


  • Add a firstOrFail fetch method inspired by Quick.

  • There are now specific numeric SQL types for integers and decimals used during the inferSQLType check in QueryUtils. This is an opt-in feature, enabled by setting the autoDeriveNumericType setting. The previous approach was to use CF_SQL_NUMERIC for all numeric types which could cause performance issues in some grammars as they interpreted all CF_SQL_NUMERIC as floating point numbers.


  • HOLDLOCK and READPAST are mutually exclusive table locks in SQL Server but were mistakenly being applied together.



  • Better parsing of raw statements when deriving insertUsing columns.


New Features and Improvements

  • Insert data based off of a callback or builder using insertUsing.

  • Insert data ignoring duplicate key errors using insertIgnore.

  • Use a callback or builder as the source for an upsert statement.

  • Allow for deleting unmatched source records in upserts (SQL Server only).

  • Add a new skipLocked flag to lockForUpdate.

Bug Fixes

  • Don't uppercase quoted aliases in Oracle.

  • Fix for aliases in update statements.

  • Don't sort columns for insertUsing.

  • Add subquery bindings in insert and upsert statements.

  • Maintain column order when using source in upsert.


  • Fix for Oracle returning custom column types when renaming a column.


  • Explicit arguments scoping.


  • arrayEach is slow compared to merging arrays.


  • Fix wheres with joins in update statements.


  • Add better null handling to inferSqlType.


  • Correctly format columns being updated.


New Features and Improvements

Bug Fixes

  • Better handling of group by and having clauses in pagination queries.

  • Allow any value to be returned from aggregates including strings, numbers, and dates.

  • Provide default values for sum and count if no records are returned.

  • Test in CI with full null support.


  • Correctly wrap CTE expressions with parenthesis when required in certain grammars.


  • SchemaBuilder can now be configured with default query options. (Default options will still be overridden by options passed to each SchemaBuilder method.)



  • Add a reset method to QueryBuilder.

  • Add locking helpers such as lock, noLock, lockForUpdate, and sharedLock.

  • Correct return aggregate values for date values from max and min executors.

  • Automatically add a scale to an incoming query param when needed.

  • Add a whereNotLike shortcut method.

  • Correctly format a COUNT(DISTINCT column) query.

  • Only use bulk insert syntax when needed in OracleGrammar due to interactions between the result parameter to cfquery, Lucee, and the Oracle JDBC driver.



  • Swap master branch to main branch.


  • Remove unnecessary injection for QueryUtils.


  • Account for raw expressions when generating mementos for comparison



8.4.1 - 8.4.4

  • Migrate release process to GitHub Actions.


  • Add a simplePaginate pagination method for quicker performance when total records or total pages are not needed or too slow.


  • Introduce a numericSQLType setting to specify the default numeric SQL type.


  • Default to html for the dump format argument to writeDump.


  • Correctly use the passed in strictDateDetection to the QueryUtils.cfc.


  • Added a dump command to aid in debugging a query while chaining.


  • ``orderByRaw now can accept bindings.

  • A new, optional strictDateDetection setting is available to check the underlying Java class of a date object instead of using isDate.


  • Ignore select bindings for aggregate queries.

  • Allow spaces in table aliases.

  • Split FLOAT and DECIMAL column types in SQL Server.


  • Clear orderBy bindings when calling clearOrders.


  • Trim table definitions before searching for aliases. Makes qb more lenient with extra whitespace.



  • ``when callbacks now automatically scope and group where clauses when an OR combinator is used.

Other Changes


  • Expose nested where functions to enable advanced query manipulation in downstream libraries like Quick.


  • Fixes for OracleGrammar including table aliases and wrapped subqueries.




  • Match type hints to documentation for join functions


  • Handle enhanced numeric checks with Secure Profile enabled.


  • Allow raw statements in basic where clauses.


  • Passed along the options struct to the count method when calling paginate.



  • Handle multi-word columns in queryRemoveColumns.


  • Remove elvis operator due to ACF compatibility issues



  • Fix wrapping of enum types for Postgres.


  • Compatibility fix for ACF 2018 and listLast parsing.

  • Include current_timestamp default for timestamp columns in SchemaBuilder.

  • Ignore table qualifiers for insert and update.


  • Fix a bug with preventDuplicateJoins when using the closure syntax with a join.


  • Add executionTime to the data output from BaseGrammar, including being available in interceptors.


  • Fix a case where a column was not wrapped correctly when a where used a subquery for the value.


  • Avoid duplicate function due to cbORM / Hibernate bugs when used in the same application.


  • Split off a private whereBasic method. This is used in Quick to provide extra sql type features.

  • Add a clearOrders method. Any already configured orders are cleared. Any orders added after this call will be added as normal.

  • selectRaw now can take an array of expressions.


Fixed an issue using column formatters with update and insert.


Using a new preventDuplicateJoins setting in the module settings, qb can detect duplicate joins and ignore them. This is especially useful in a heavily filtered and dynamic query where you may or may not need the join at all or more than one column may need the same join. preventDuplicateJoins defaults to false, so it is opt-in. It may be turned on by default in a future breaking release of qb.


Enhance order by's with more direction options (c767ac8)

You can now use two shortcut methods: orderByAsc and orderByDesc. Additionally, orderBySub or using orderBy with a closure or builder instance will respect the direction argument.


  • Fix using whereBetween with query param structs (07c9b72)


  • Ignore orders in aggregate queries (39e1338)



  • Improve column wrapping with trimming (d98a5cb)

  • Prefer the parent query over magic methods when the parent query has the exact method. (f9fd8d1)

7.3.9, 7.3.10, 7.3.11


  • Allow passing query options in to paginate (cdecfb3)


  • Fix for inserting null values directly (1de27a6)

7.3.5, 7.3.6

  • Use cfformat for automatic formatting (119e434)

  • Add a type to the onMissingMethod exception (90d1093)


  • Correctly wrap comments in MySQLGrammar.

7.3.2, 7.3.3


  • Fix for null values breaking the new checkIsActuallyNumeric method in QueryUtils.


  • Add a parameterLimit public property to SqlServerGrammar. This property is used in Quick to split up eager loading to work around the 2100 param limit of SQL Server.


  • Allow a parent query to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like Quick to allow Quick features like scopes to be available inside any closures.


  • Lambdas (arrow functions) are now allowed wherever closures are allowed.

  • Add an orderByRaw method.

  • Allow for fully-qualified column names (table_name.column.name) in the value and values methods.



Please see the Migration Guide for more information on these changes.

  • Drop support for Lucee 4.5 and Adobe ColdFusion 11.

  • MSSQLGrammar renamed to SqlServerGrammar

  • Remove variadic parameters support in builder functions like select.

  • The defaultGrammar mapping needs to be the full WireBox mapping, including the @qb, if needed.

    • For instance, MSSQLGrammar would become MSSQLGrammar@qb.

    • This will allow for other grammars to be more easily contributed via third party modules.

  • The argument names of forPage changed to match the new paginate method.

  • Add defaultValue and optional exception throwing to value. (This changed the argument order.)

  • All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. (This changed the argument names in some instances.)

Other Changes

  • Completely revamped documentation! (You're looking at it right now.)

  • Add new flag to toSQL( showBindings = true ) to replace question marks (?) with cfqueryparam-compatible structs for debugging.

  • Preserve column case and order when converting a query to an array using the default "array" return format.

  • Add a new paginate method to generate a pagination struct alongside the results. This can be customized using a custom PaginationCollector.

  • Allow raw values in insert calls.

  • Allow default queryExecute options to be configure at a Query Builder level. This also enables custom QueryBuilders a la Hyper.

  • Add a whereLike method.

  • Allow closures to be used in left and right joins.

  • Provide an addUpdate method to programmatically build the SET clause of an update query.

  • Add a new chunk method to grab records from the database in small sets.

  • Add raw in alterTable segments.

  • Add dropAllObjects support for SqlServerGrammar and OracleGrammar to support migrate fresh from cfmigrations.

  • Add a renameTable alias for rename.

  • Remove default constraints when dropping columns with a default on SqlServerGrammar.

  • Add more column types and column helpers to SchemaBuilder, including:

    • datetimeTz

    • lineString

    • nullableTimestamps

    • point

    • polygon

    • softDeletes

    • softDeletesTz

    • timeTz

    • timestamps

    • timestampTz

    • timestampsTz

    • withCurrent



Last updated