LogoLogo
9.5.0
9.5.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Locks
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options and Utilities
      • Clone and Reset
      • Return Format
      • Column Formatter
      • Interception Points
    • Debugging
      • sqlCommenter
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
    • Debugging
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • 9.5.1
  • 9.5.0
  • 9.4.1
  • 9.4.0
  • 9.3.1
  • 9.3.0
  • 9.2.5
  • 9.2.4
  • 9.2.3
  • 9.2.2
  • 9.2.1
  • 9.2.0
  • New Features
  • Bug Fixes
  • 9.1.5
  • 9.1.4
  • 9.1.3
  • 9.1.2
  • 9.1.1
  • 9.1.0
  • New Features
  • Bug Fixes
  • 9.0.2
  • 9.0.1
  • 9.0.0
  • Breaking Changes
  • New Features and Improvements
  • Bug Fixes
  • 8.10.0
  • 8.9.1
  • 8.9.0
  • 8.8.1
  • 8.8.0
  • New Features and Improvements
  • Bug Fixes
  • 8.7.8
  • 8.7.7
  • 8.7.6
  • 8.7.5
  • 8.7.2
  • 8.7.1
  • 8.7.0
  • New Features and Improvements
  • Bug Fixes
  • 8.6.1
  • 8.6.0
  • 8.5.0
  • QueryBuilder
  • SchemaBuilder
  • 8.4.9
  • 8.4.8
  • 8.4.7
  • 8.4.6
  • 8.4.5
  • 8.4.1 - 8.4.4
  • 8.4.0
  • 8.3.0
  • 8.2.2
  • 8.2.1
  • 8.2.0
  • 8.1.0
  • 8.0.3
  • 8.0.2
  • 8.0.1
  • 8.0.0
  • 7.10.0
  • 7.9.9
  • 7.9.8
  • 7.9.7
  • 7.9.6
  • 7.9.5
  • 7.9.4
  • 7.9.3
  • 7.9.2
  • 7.9.1
  • 7.9.0
  • 7.8.0
  • 7.7.3
  • 7.7.2
  • 7.7.1
  • 7.7.0
  • 7.6.2
  • 7.6.1
  • 7.6.0
  • 7.5.1
  • 7.5.0
  • 7.4.0
  • 7.3.15
  • 7.3.14
  • 7.3.13
  • 7.3.12
  • 7.3.9, 7.3.10, 7.3.11
  • 7.3.8
  • 7.3.7
  • 7.3.5, 7.3.6
  • 7.3.4
  • 7.3.2, 7.3.3
  • 7.3.1
  • 7.3.0
  • 7.2.0
  • 7.1.0
  • 7.0.0
  • 6.4.0

Was this helpful?

Edit on GitHub
Export as PDF

What's New?

PreviousIntroductionNextInstallation & Usage

Last updated 1 year ago

Was this helpful?

9.5.1

Add MariaDB support to AutoDiscover@qb grammar. (It will choose the MySQLGrammar@qb.)

9.5.0

Add and methods, inspired by .

9.4.1

Better Support for OracleGrammar in SchemaBuilder

  • Fix trigger creation by escaping colons (:).

  • Try to drop associated sequences and triggers when dropping a table.

  • Better support for creating a table in a different schema by only checking for the last identifier as the table name in and .

9.4.0

Allow for setting a property on a SchemaBuilder instance.

The defaultSchema will be used for methods like and . A passed in schema will still take precedence.

9.3.1

  • Use CHAR for GUID and UUID types in MySQL.

  • Don't call getUtils from inside QueryUtils.

9.3.0

9.2.5

Use named parameters when passing to BaseGrammar. This avoids problems where custom Grammars have extra arguments and we add arguments to the official grammar.

9.2.4

We apologize for the new features in a patch release.

New Features

Bug Fixes

  • Use varchar for clob when converting to a CFML query. This is used when removing a column like in Oracle pagination.

9.2.3

Handle more numeric SQL types like AtomicInteger and Long.

9.2.2

Add millisecond accuracy to inline bindings.

9.2.1

Separate having bindings from where bindings.

9.2.0

New Features

We now support the returning function inside update and delete statements for supported Grammars. Supported grammars are SQL Server, Postgres, and SQLite.

Bug Fixes

  • Fix raw table name parsing in update queries for SqlServerGrammar.

  • Fix truncating text in nested wheres inside joins.

  • Fix out of order bindings in joinSub

9.1.5

Switch from table_catalog to table_schema when referencing schema for PostgresGrammar.

9.1.4

CommandBox-friendly injections for SQL Commenter.

9.1.3

Add support for from bindings, used especially in fromSub queries.

9.1.2

This release reverts the use of native returntypes. There are too many bugs between engine implementations to make it viable. No end-user changes should be visible.

9.1.1

Make withReturnFormat a public method.

9.1.0

New Features

Add ability to inline bindings when calling toSQL and dump. These strings can be executed in a DBMS application.

Bug Fixes

  • Move coldbox namespace injection to the function body so CommandBox doesn't blow up.

  • Correctly apply native returntypes after newQuery and withReturnFormat.

9.0.2

  • Fix losing defaultOptions when calling newQuery.

  • Shortcut for no return format using none.

  • Allow for native struct returntypes. Requires a return format of none.

9.0.1

Fix RouteInfoCommenter file name.

9.0.0

Breaking Changes

Dropped support for Adobe ColdFusion 2016

Adobe has ended support for ACF 2016, and so must we.

CFML's uuid does not match other languages; it's one character shorter. Because of this, the value from createUUID() cannot be used in some database column types like SQL Server's uniqueidentifier. This made for some confusion in SchemaBuilder since it wasn't clear if uuid meant CFML's definition or the wider world's definition.

Popular grid frameworks like Quasar and Datatables use values of 0 or -1 to return all rows from a query. This is now supported in qb. Previously, it generated an invalid query (SELECT * FROM users LIMIT 0 OFFSET 0).

This behavior can be customized by providing a callback to the shouldMaxRowsOverrideToAll setting or init argument. For instance, to revert to the previous behavior you would set the function as follows:

moduleSettings = {
    "qb": {
        "shouldMaxRowsOverrideToAll": function( maxRows ) {
            return false;
        }
    }
};

Note: the option to revert to the old behavior will be removed in the next major version.

Note: the option to revert to the old behavior may be removed in the next major version.

New Features and Improvements

SQLite Grammar Support

There's a new shortcut method to return qb.sum( qb.raw( expression ) ). You're welcome. 😉

Bug Fixes

  • Correctly compile insertUsing statements that use Common Table Expressions (CTEs).

  • Update announceInterception calls for ColdBox 7. (Thank you, Michael Born.)

  • Fixed insertUsing not placing Common Table Expressions (CTEs) in the correct order.

  • Don't add DISTINCT when doing a COUNT(*).

  • Support aggregates for unioned queries.

8.10.0

8.9.1

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

8.9.0

8.8.1

8.8.0

New Features and Improvements

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.

8.7.8

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

8.7.7

  • Explicit arguments scoping.

8.7.6

  • arrayEach is slow compared to merging arrays.

8.7.5

  • Fix wheres with joins in update statements.

8.7.2

  • Add better null handling to inferSqlType.

8.7.1

  • Correctly format columns being updated.

8.7.0

New Features and Improvements

Bug Fixes

8.6.1

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

8.6.0

8.5.0

QueryBuilder

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

  • 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.

SchemaBuilder

8.4.9

  • Swap master branch to main branch.

8.4.8

  • Remove unnecessary injection for QueryUtils.

8.4.7

  • Account for raw expressions when generating mementos for comparison

8.4.6

8.4.5

8.4.1 - 8.4.4

  • Migrate release process to GitHub Actions.

8.4.0

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

8.3.0

8.2.2

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

8.2.1

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

8.2.0

8.1.0

8.0.3

  • Ignore select bindings for aggregate queries.

  • Allow spaces in table aliases.

  • Split FLOAT and DECIMAL column types in SQL Server.

8.0.2

  • Clear orderBy bindings when calling clearOrders.

8.0.1

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

8.0.0

BREAKING CHANGES

Other Changes

7.10.0

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

7.9.9

  • Fixes for OracleGrammar including table aliases and wrapped subqueries.

7.9.8

7.9.7

7.9.6

7.9.5

  • Handle enhanced numeric checks with Secure Profile enabled.

7.9.4

  • Allow raw statements in basic where clauses.

7.9.3

7.9.2

7.9.1

  • Handle multi-word columns in queryRemoveColumns.

7.9.0

  • Remove elvis operator due to ACF compatibility issues

7.8.0

7.7.3

7.7.2

  • Compatibility fix for ACF 2018 and listLast parsing.

  • Ignore table qualifiers for insert and update.

7.7.1

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

7.7.0

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

7.6.2

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

7.6.1

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

7.6.0

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

7.5.1

Fixed an issue using column formatters with update and insert.

7.5.0

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.

7.4.0

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.

7.3.15

7.3.14

7.3.13

7.3.12

7.3.9, 7.3.10, 7.3.11

7.3.8

7.3.7

7.3.5, 7.3.6

7.3.4

7.3.2, 7.3.3

7.3.1

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

7.3.0

  • 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.

7.2.0

7.1.0

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

7.0.0

BREAKING 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.)

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

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

  • 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

****

6.4.0

Make publicly available in QueryUtils.

This is used by qb to inline query bindings in toSQL or dump calls and can be used to inline the bindings in other tools like.

Add the ability to pretend to run queries, both in and .

Add query logging to and instances.

SchemaBuilder's uuid split into and

So, the types have been split, following Lucee's pattern, into (matching CFML's ) and (matching Java's UUID or on Lucee).

Returning all rows from when maxRows is 0 or lower

is now the default

Introduced in , this feature uses separate SQL types for integers and decimals to increase performance in certain database grammars. This feature is now the default, but the previous behavior can be enabled by setting autoDeriveNumericType to false.

is now the default

Introduced in , this feature only returns a SQL type of CF_SQL_TIMESTAMP if the param is a date object, not just a string that looks like a date. This helps avoid situations where some strings were incorrectly interpreted as dates. For many, the migration path is straightforward — calls to are already date objects as well as any function that operates on a date. If you need to parse a string as a date, the built-in function can accomplish that.

Thanks to , qb now supports SQLite for both QueryBuilder and SchemaBuilder. You can use it in your apps by specifying SQLiteGrammar@qb as the default grammar.

sqlCommenter is a for adding contextual information as a comment at the end of a SQL statement. This can give insights into your application, especially when diagnosing slow queries. Examples of the information you can append to your queries are route, handler, action, version, and others, as well as the ability to add your own, such as loggedInUser and more.

helper function

Dedicated method

Some grammars, like SQL Server, do not treat simple indexes as constraints. For this reason, we've added a method alongside the existing .

helper method

will return either an array of column names for the configured table or the query that is generated by cfdbinfo for the configured table. Especially useful when working with dynamically generated grids.

Added the missing keyword in the Postgres syntax.

Add a fetch method inspired by .

There are now 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.

Specify defaultOptions

Better parsing of raw statements when deriving columns.

Insert data based off of a callback or builder using .

Insert data ignoring duplicate key errors using .

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

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

Add a new skipLocked flag to .

Add an method. upsert can update or insert multiple records at once depending on if a column is matched.

Allow expressions in and . Also add a and helper method to make that pattern more ergonomic.

Allow . (This is not supported on Oracle.)

Allow using closures or builder instances.

Better handling of and clauses in queries.

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

Provide default values for and if no records are returned.

Test in CI with support.

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

Add a method to QueryBuilder.

Add helpers such as , , , and .

to an incoming query param when needed.

Add a shortcut method.

Add support for and .

Add support for & types for MySQLGrammar.

Fix limit on .

Introduce a setting to specify the default numeric SQL type.

📹

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

📹

now can accept bindings.

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

📹

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

Combine and orderBy with a new method.

Clear current selected columns with .

Combine and either or with and respectively.

Allow nullable in MySQL.

Return 0 on null .

Match type hints to documentation for functions

Passed along the options struct to the method when calling .

Allow for space-delimited directions like column DESC.

Add helpful message when trying to use a closure with instead of .

`` and now work with

Correctly format RETURNING clauses with and ignoring table qualifiers.

Add support for and data types to .

Fix wrapping of types for Postgres.

Include current_timestamp default for columns in SchemaBuilder.

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

now can take an array of expressions.

Enhance order by's with more direction options ()

Fix using whereBetween with query param structs ()

Ignore orders in aggregate queries ()

Format with cfformat ()

Improve column wrapping with trimming ()

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

Switch to using .

Allow passing query options in to paginate ()

Fix for inserting null values directly ()

Use cfformat for automatic formatting ()

Add a type to the onMissingMethod exception ()

Correctly wrap in MySQLGrammar.

Publish qb apidocs to .

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 to allow Quick features like scopes to be available inside any closures.

Add an method.

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

Please see the for more information on these changes.

Add new flag to to replace question marks (?) with cfqueryparam-compatible structs for debugging.

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

Allow raw values in calls.

Allow to be configure at a Query Builder level. This also enables custom QueryBuilders a la .

Add a method.

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

to grab records from the database in small sets.

CommandBox Migrations
Jason Steinhouer
sqlCommenter Support
specification by Google
inside of your ColdBox config.
aggregates
full
null
default query options
Watch a walkthrough of this change on CFCasts.
Watch a walkthrough of these changes on CFCasts.
Watch a walkthrough of these changes on CFCasts.
aggregates
join
sort
column formatters
c767ac8
07c9b72
39e1338
dc2a9b6
d98a5cb
f9fd8d1
ForgeBox Storage
cdecfb3
1de27a6
119e434
90d1093
Ortus API Docs
Quick
Allow Expressions (query.raw) in update statements.
8.10.0
now()
parseDateTime
8.1.0
Quick
defaultSchema
createUUID()
createGUID()
Quick
locking
column formatters.
SchemaBuilder
Hyper
replaceBindings
default queryExecute options
paginate
firstOrFail
value
values
valueRaw
valuesRaw
simplePaginate
value
values
value
values
paginate
PaginationCollector
Add a new chunk method
sumRaw
columnList
columnList
sum
count
dump
toSQL( showBindings = true )
findOrFail
existsOrFail
count
QueryBuilder
QueryBuilder
dropIndex
dropIndex
dropConstraint
hasTable
hasColumn
hasTable
hasColumn
SchemaBuilder
SchemaBuilder
autoDeriveNumericType
strictDateDetection
specific numeric SQL types for integers and decimals
Automatically add a scale
numericSQLType
strictDateDetection
reset
when
group by
having
orderByRaw
clearOrders
reorder
clearOrders
orderByRaw
stored computed columns
virtual computed columns
comments
pagination
paginate
from
fromSub
Migration Guide
clearSelect
clearSelect
select
selectRaw
reselect
reselectRaw
selectRaw
lockForUpdate
lock
noLock
lockForUpdate
sharedLock
upsert
insertUsing
insertUsing
insertIgnore
upsert
upserts
upsert
JOIN statements in UPDATE statements
updates with subselects
insert
addUpdate
guid()
uuid()
uuid
guid
mediumtext
longtext
timestamps
MONEY
SMALLMONEY
enum
timestamp
whereNotLike
whereLike