A new major release of qb is now available on ForgeBox! This comes jam-packed with awesome features from new SQLite grammar support to SQLCommenter support to add contextual comments to your queries. More on the new features in a bit.

Waxing Nostalgic

If you'll permit me for a bit, I wanted to wax nostalgic on qb's 7 year history.

It's crazy to think that qb has been around for 9 major releases now. It's first commit was back on April 11, 2016. Fun fact — back then, before it was released, it was named Quick. It wasn't until about 8 months later that it was renamed to qb.

The base of qb was done before I ever worked for Ortus. I wanted to stretch my programming muscles, and I was also tired of writing pagination queries for Oracle 11g. (In case you haven't ever had the pleasure of working with Oracle 11g or earlier, pagination must be done by adding a rownum column to your query, wrapping your query in a subquery, and then limiting the records by the rownum column. 😵 )

The first and second major versions were released over the first 2 years of the project. After I started working for Ortus in 2017, they and other wonderful companies and individuals have sponsored my time to work on the project. That has brought the next 7 major releases over the next 6 years.

qb has spun off lots of other projects as well. SchemaBuilder enabled cfmigrations and commandbox-migrations which are used to deploy schema changes and seed development data to databases.

I developed CommandBox Semantic Release as a way to keep up with all the PRs people were contributing to qb. I didn't want to hold up releases because I wasn't near a keyboard and mouse. With Semantic Release, I could merge a PR from my phone and see the correct version released in a matter of minutes.

cbPaginator, while not a direct brainchild of qb, was heavily inspired by it. I worked closely with Javier to make sure that cbPaginator was flexible enough for any of the pagination needs of qb users.

Because of the locking helpers added to qb, I was able to create a performant database provider for cbq, allowing anyone with a database to start queueing jobs for asynchronous, durable processing.

And of course, Quick, would not be possible without qb powering the underlying query building. I love how it came full circle with qb starting out being named as Quick then powering a CFML ORM named Quick. 😄

In short, it's been an incredible 7 years of this library. Thank you to any who have contributed in any way, and especially thank you to Ortus Solutions for believing in me and in this project.

qb 9.0.0 Updates

Okay, enough nostalgia. Let's get to the changes.

(You can always see these changes in the What's New section of the qb docs.)

Breaking Changes

Dropped support for Adobe ColdFusion 2016

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

SchemaBuilder's uuid split into guid() and uuid()

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.

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

Returning all rows from paginate when maxRows is 0 or lower

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;
        }
    }
};

autoDeriveNumericType is now the default

Introduced in 8.10.0, 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.

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

strictDateDetection is now the default

Introduced in 8.1.0, 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 now() 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 parseDateTime built-in function can accomplish that.

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

New Features and Improvements

SQLite Grammar Support

Thanks to Jason Steinhouer, 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 Support

sqlCommenter is a specification by Google 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.

sumRaw helper function

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

Dedicated dropIndex method

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

columnList helper method

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

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.
• Added the missing keyword in the Postgres upsert syntax.
• Don't add DISTINCT when doing a COUNT(*).
• Support aggregates for unioned queries.