1 of 44

9.4.0 Introduction

qb is a fluent query builder for CFML. It is heavily inspired by Eloquent from Laravel.

Using qb, you can:

Quickly scaffold simple queries
Make complex, out-of-order queries possible
Abstract away differences between database engines

Requirements

Adobe ColdFusion 2018+
Lucee 5+

qb supports four major database grammars:

MySQL (MySQLGrammar@qb)
Oracle (OracleGrammar@qb)
Postgres (PostgresGrammar@qb)
Microsoft SQL Server (SqlServerGrammar@qb)
SQLite (SQLiteGrammar@qb)

Discussion & Help

The Box modules discussion group and community can be found here:

https://community.ortussolutions.com/c/box-modules/qb/27

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Code Samples

Compare these two examples:

// Plain old CFML
var results = queryExecute( "SELECT * FROM users" );

// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "users" ).get();

The differences become even more stark when we introduce more complexity:

// Plain old CFML
var results = queryExecute(
    "SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
    [ { value = "5,10,27", cfsqltype = "CF_SQL_NUMERIC", list = true } ]
);

// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
    .whereNotNull( "published_at" )
    .whereIn( "author_id", [ 5, 10, 27 ] )
    .get();

With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:

var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
         .orderBy( "published_at" )
         .select( "post_id", "author_id", "title", "body" )
         .whereLike( "author", "Ja%" )
         .join( "authors", "authors.id", "=", "posts.author_id" )
         .get();

// Becomes
var results = queryExecute(
    "SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
    [ { value = "Ja%", cfsqltype = "CF_SQL_VARCHAR", list = false, null = false } ]
);

qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!

Here's a gist with an example of the powerful models you can create with this! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202

Usage

To start a new query, instantiate a new Builder: wirebox.getInstance( "QueryBuilder@qb" ).

By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar in your moduleSettings.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

If you are not using WireBox, just make sure to wire up the Builder object with the correct grammar:

var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( grammar );

Installation & Usage

Installation

Installation is easy through CommandBox and ForgeBox. Simply type box install qb to get started.

Usage

To start a new query, instantiate a new Builder: wirebox.getInstance('QueryBuilder@qb').

By default, qb uses a generic Grammar. You can specify your specific grammar in ColdBox by setting the defaultGrammar in your moduleSettings.

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb"
    }
};

The grammars provided by qb are:

MySQLGrammar
OracleGrammar
PostgresGrammar
SqlServerGrammar
SQLiteGrammar

If you are not using WireBox, make sure to wire up the Builder object with the correct grammar:

var grammar = new qb.models.Grammars.MySQLGrammar();
var builder = new qb.models.Query.QueryBuilder( grammar );

Configuration Settings

Here are the full configuration settings you can use in the module settings:

moduleSettings = {

    qb : {
        "defaultGrammar": "AutoDiscover@qb",
        "defaultReturnFormat": "array",
        "preventDuplicateJoins": false,
        "strictDateDetection": true,
        "numericSQLType": "CF_SQL_NUMERIC",
        "integerSQLType": "CF_SQL_INTEGER",
        "decimalSQLType": "CF_SQL_DECIMAL",
        "autoAddScale": true,
        "autoDeriveNumericType": true,
        "defaultOptions": {},
        "sqlCommenter": {
            "enabled": false,
            "commenters": [
                { "class": "FrameworkCommenter@qb", "properties": {} },
                { "class": "RouteInfoCommenter@qb", "properties": {} },
                { "class": "DBInfoCommenter@qb", "properties": {} }
            ]
        },
        "shouldMaxRowsOverrideToAll": function( maxRows ) {
            return maxRows <= 0;
        }
    }

}

SQL Type Inferral

QB binds all parameters by default and guesses the SQL type based on passed values. The default SQL type for numeric values is CF_SQL_NUMERIC, which is a floating point number, for the widest compatibility. This can cause performance problems with large recordsets in some database engines. You can provide a different default in coldbox.cfc if you wish to override this setting:

moduleSettings = {
    qb = {
        defaultGrammar = "MySQLGrammar@qb",
        numericSQLType = "CF_SQL_BIGINT"
    }
};

Integrating With FW/1

Note: These instructions assume a basic knowledge of FW/1, a working FW/1 application structure with qb installed in the /subsystems directory (manually or via CommandBox), and a database configured to run with your application.

Wiring Up With DI/1

Once the application structure is setup, now we need to wire up qb to a bean factory using DI/1.

First we will add a mapping in Application.cfc.

this.mappings = {
    "/qb" = expandPath("./subsystems/qb")
};

Next we need to tell DI/1 where qb's components are and how to reference them for later use in the application. We can do so by defining the configuration settings in the variables.framework.subsystems struct in Application.cfc. The example below makes use of a load listener to declare each component instance and pass in any constructor arguments.

qb = {
  diLocations = "/qb/models",
  diConfig = {
    loadListener = function( di1 ) {
      di1.declare( "BaseGrammar" ).instanceOf( "qb.models.Query.Grammars.Grammar" ).done()
         .declare( "MySQLGrammar" ).instanceOf( "qb.models.Query.Grammars.MySQLGrammar" ).done()
         .declare( "QueryUtils" ).instanceOf( "qb.models.Query.QueryUtils" ).done()
         .declare( "QueryBuilder" ).instanceOf( "qb.models.Query.QueryBuilder" )
         .withOverrides({
            grammar = di1.getBean( "MySQLGrammar" ),
            utils = di1.getBean( "QueryUtils" ),
            returnFormat = "array"
         })
         .asTransient();
    }
  }
}

Usage In Your FW/1 Application

Now that everything is configured, you can launch your application with CommandBox by entering start in the terminal or use whatever method you're accustomed to.

To access qb from your application's code, you can call on it by using getBeanFactory().

// Create an instance of qb
builder = getBeanFactory( "qb" ).getBean( "QueryBuilder" );
// Query the database
posts = builder.from( "Posts" ).get();
posts = builder.from( "Posts" ).where( "IsDraft", "=", 0 ).get();

For further instructions on getting started with qb & FW/1, refer to this blog post.

Migration Guide

v9.0.0

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

If you are using uuid with 36 character UUIDs or SQL Server's uniqueidentifier columns, please migrate your uuid calls to guid.

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

If this behavior is fine for your application, you don't need to change anything.

This behavior can be customized by providing a callback to the shouldMaxRowsOverrideToAll setting or init argument.

If you need to revert to the previous behavior, provide the following as the shouldMaxRowsOverrideToAll setting:

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.

This behavior should be an improvement in most every case without any changes needed.

If you need to revert to the previous behavior, provide the following as the autoDeriveNumericType setting:

moduleSettings = {
    "qb": {
        "autoDeriveNumericType": 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.

If you are relying on qb treating any strings as dates you will need to parse them as actual date objects first. (You can do so using functions like parseDateTime.

If you need to revert to the previous behavior, provide the following as the strictDateDetection setting:

moduleSettings = {
    "qb": {
        "strictDateDetection": false
    }
};

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

v8.0.0

Where clauses with an OR combinator are now automatically wrapped inside `when` callbacks

This isn't a breaking change that will affect most people. In fact, it will most likely improve your code.

Previously, when using the when control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

Would generate the following SQL:

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

The problem with this statement is that the OR can short circuit the active check.

The fix is to wrap the LIKE statements in parenthesis. This is done in qb using a function callback to where.

qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

When using the when control flow function, it was easy to miss this. This is because you are already in a closure - it looks the same as when using where to group the clauses.

In qb 8.0.0, when will automatically group added where clauses when needed. That means our original example now produces the SQL we probably expected.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Grouping is not needed if there is no OR combinator. In these cases no grouping is added.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL

If you had already wrapped your expression in a group inside the when callback, nothing changes. Your code works as before. The OR combinator check only works on the top most level of added where clauses.

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Additionally, if you do not add any where clauses inside a when callback, nothing changes from qb 7.

The breaking change part is if you were relying on these statements residing at the same level without grouping. In those cases, you may pass the withoutScoping flag to the when callback.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

Support for Lucee 4.5 and Adobe ColdFusion 11 has been dropped. If you need support for these engines, please remain on an earlier version of qb.

MSSQLGrammar renamed to SqlServerGrammar

MSSQLGrammar was visually too close to MySQLGrammar and was hard to differentiate quickly. SqlServerGrammar is much more unique and easily identifiable. Additionally, more people that use this library refer to their database engine as "SQL Server" than "MSSQL".

To migrate, replace any instances of MSSQLGrammar with SqlServerGrammar. Make sure to also append the @qb namespace, if needed, as explained below.

Variadic Parameters Support Removed

Variadic parameter support was the ability to pass any number of arguments to certain methods like select.

qb.select( "name", "email", "createdDate" );

This code came with a slight performance cost and readability cost. That, combined with the fact that the above syntax is very close to an array, we are dropping support for variadic parameters. To migrate, wrap instances of variadic parameters in an array:

qb.select( [ "name", "email", "createdDate" ] );

defaultGrammar updated to be the full WireBox mapping

In previous versions, the value passed to defaultGrammar was used to look up a mapping in the @qb namespace. This made it difficult to add or use grammars that weren't part of qb. (You could get around this be registering your custom grammar in the @qb namespace, but doing so seemed strange.)

To migrate this code, change your defaultGrammar to be the full WireBox mapping in your moduleSettings:

moduleSettings = {
    "qb": {
        "defaultGrammar": "MSSQLGrammar@qb"
    }
};

value method argument order changed

A defaultValue parameter and optional exception throwing was added to value. This pushed the options struct to the end of the method. If you are using positional parameters with value, you will need to update your method calls to either use named parameters or the new positions.

public any function value(
    required string column,
    string defaultValue = "",
    boolean throwWhenNotFound = false,
    struct options = {}
);

Some methods renamed `callback` to `query`

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 led to changing the callback argument to query in the following cases:

whereSub
whereInSub
whereExists
orWhereExists
whereNotExists
andWhereNotExists
orWhereNotExists
whereNullSub
orderBySub
subSelect

If you are using named parameters with any of the above methods you will need to migrate your method calls.

v5.0.0

Version v5.0.0 brings support for SchemaBuilder inside qb. To avoid naming confusion, Builder was renamed to QueryBuilder. Any references in your code to Builder@qb need to be updated to QueryBuilder@qb.

Contributing & Filing Issues

We welcome all types of contributions!

The most common type of contribution is to fix an incorrect SQL generation for a database grammar.

To debug what SQL is being ran, you can always call toSQL on any QueryBuilder or SchemaBuilder object. Additionally, you can listen to the preQBExecute interception point for the generated SQL.

Each of the database grammars have two tests — {Grammar}QueryBuilderSpec.cfc and {Grammar}SchemaBuilderSpec.cfc. These tests run the same qb syntax across the different grammars. In each test are methods that return SQL strings like so:

// MSSQLQueryBuilderSpec.cfc
function orWhere() {
    // If just a string is returned, we assume the bindings is an empty array ([])
    return {
        sql = "SELECT * FROM [users] WHERE [id] = ? OR [email] = ?",
        bindings = [ 1, "foo" ]
    };
}

// OracleSchemaBuilderSpec.cfc
function boolean() {
    // returns an array since schema builder can execute multiple statements.
    return [ "CREATE TABLE ""USERS"" (""ACTIVE"" NUMBER(1, 0) NOT NULL)" ];
}

If you find an issue with the SQL generated from a grammar, please file a pull request with the correct SQL in these tests. It's okay if you don't submit a fix as well. (But we'd greatly appreciate it!) Doing so will help expedite the fix.

If you want to add support for a new database grammar, simply copy these two tests from an existing grammar, rename them, change the getBuilder method to return your new grammar, and fill out the SQL as it should be. That will guide your implementation to be 100% compatible with the other grammars in qb.

Query Builder

Getting a New Query

A query builder is a stateful, transient object. That means that if you want to execute two different queries, you need two separate instances of QueryBuilder.

QueryBuilder

// This will cause you pain and grief...

var user = query.from( "users" )
  .where( "username", rc.username )
  .first();

var posts = query.from( "posts" ).get();
// This will error because `username` is not a column in `posts`.

As such, be careful when injecting QueryBuilder in to a component. If the component is a singleton, you will need to create the QueryBuilder inline or use a provider. This applies to ColdBox handlers as well.

handlers/posts.cfc

component {

    property name="query" inject="QueryBuilder@qb";

    function create( event, rc, prc ) {
        // This will cause you pain and grief...
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

While the above may seem innoculous, it can run in to issues as multiple requests come in to your application. Each request is sharing the same query builder instance and subsequent requests will have unintended results as the where clause keeps growing request after request.

The solution is to either create the QueryBuilder inline, ensuring that each request has its own query to execute:

handlers/posts.cfc

component {

    function create( event, rc, prc ) {
        getInstance( "QueryBuilder@qb" )
            .table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

Or to use a WireBox provider to create a new query each time it is accessed:

handlers/posts.cfc

component {

    property name="query" inject="provider:QueryBuilder@qb";

    function create( event, rc, prc ) {
        query.table( "posts" )
            .where( "id", rc.id )
            .update( event.getOnly( [ "body" ] ) );
    }

}

One caveat when using a WireBox Provider: WireBox Providers proxy methods on to a new instance of the provided mapping on all methods except get. get is a method on the Provider itself. If you call get as the first method on a Provider it will return a new instance of QueryBuilder, not execute the query. In those (rare) cases you will need to call query.get().get().

newQuery

Once you have access to a QueryBuilder instance, you can create a new query using the same datasource, utils, returnFormat, paginationCollector, columnFormatter, and defaultOptions as the current QueryBuilder instance.

// This will cause you pain and grief...

var user = query.from( "users" )
  .where( "username", rc.username )
  .first();

var posts = query.newQuery().from( "posts" ).get();
// This will work as we expect it to.

Building Queries

Selects

Specifying A Select Clause

You may not always want to select all columns from a database table. You can influence the select list of a query with the following methods.

Individual columns can contain fully-qualified names (some_table.some_column), table aliases (alias.some_column), and even set column aliases themselves (some_column AS c). The columns argument can be a single column, a list of columns (comma-separated), or an array of columns.

select

When calling select any previous columns are discarded. If you want to incrementally select columns, use the addSelect method.

If you pass no columns to this method, it will default to "*".

distinct

Calling distinct will cause the query to be executed with the DISTINCT keyword.

distinct applies to the entire query, not just certain fields.

addSelect

This method adds the columns passed to it to the currently selected columns.

If the QueryBuilder is currently selecting all columns ("*") when this method is called, the incoming columns will becoming the only columns selected.

selectRaw

A shortcut to use a raw expression in the select clause.

The expression is added to the other already selected columns.

subSelect

The method lets you pass either a callback or a QueryBuilder instance to be used as a subselect expression. If a callback is passed it will be passed a new query instance as the only parameter.

The subselect is added to the other already selected columns.

clearSelect

Clears out the selected columns for a query along with any configured select bindings.

reselect

reselectRaw

Clears out the selected columns for a query along with any configured select bindings. Then adds an Expression or array of expressions to the already selected columns.

From

from

Used to set the base table for the query.

You can optionally specify an alias for the table.

table

An alias for from where you like how calling table looks.

fromRaw

Sometimes you need more control over your from clause in order to add grammar specific instructions, such as adding SQL Server table hints to your queries.

Since the fromRaw() takes your string verbatim, it's important that you make sure your SQL declaration is escaped properly. Failure to properly escape your table names may result in SQL errors.

Using fromRaw will most likely tie your code to a specific database, so think carefully before using the fromRaw method if you want your project to be database agnostic.

Many database engines allow you to define User Defined Functions. For example, SQL Server allows you to define UDFs that will return a table. In these type of cases, it may be necessary to bind parameters to your from clause.

You can bind parameters to the fromRaw() method by passing a secondary argument that is an array of the parameters to bind.

fromSub

Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the from statement.

In additional a function callback, a separate QueryBuilder instance can be passed to the fromSub method.

Joins

Join clauses range from simple to complex including joining complete subqueries on multiple conditions. qb has your back with all of these use cases.

join

Applies a join to the query. The simplest join is to a table based on two columns:

When doing a simple join using = as the operator, you can omit it and pass just the column names:

Using raw will most likely tie your code to a specific database, so think carefully before using the raw method if you want your project to be database agnostic.

When you need to specify more clauses to join, you can pass a function as the second argument:

Conditions inside a join clause can be grouped using a function.

joinWhere

Adds a join to another table based on a WHERE clause instead of an ON clause. WHERE clauses introduce parameters and parameter bindings whereas on clauses join between columns and don't need parameter bindings.

For simple joins, this specifies a column on which to join the two tables:

joinRaw

Using joinRaw will most likely tie your code to a specific database, so think carefully before using the joinRaw method if you want your project to be database agnostic.

joinSub

Alternatively, a function may be used to define the derived table:

Complex join conditions are also possible by passing a function as the third parameter:

leftJoin

leftJoinRaw

Using leftJoinRaw will most likely tie your code to a specific database, so think carefully before using the leftJoinRaw method if you want your project to be database agnostic.

leftJoinSub

rightJoin

rightJoinRaw

Using rightJoinRaw will most likely tie your code to a specific database, so think carefully before using the rightJoinRaw method if you want your project to be database agnostic.

rightJoinSub

crossJoin

crossJoinRaw

Uses the raw SQL provided to as the table for the cross join clause. Cross joins cannot be further constrained with on or where clauses.

Using crossJoinRaw will most likely tie your code to a specific database, so think carefully before using the crossJoinRaw method if you want your project to be database agnostic.

crossJoinSub

newJoin

JoinClause

on

Applies a join condition to the JoinClause. An alias for whereColumn.

orOn

Applies a join condition to the JoinClause using an or combinator. An alias for orWhereColumn.

Preventing Duplicate Joins

You can optionally configure qb to ignore duplicate joins. With this setting turned on each JoinClause is inspected and checked if it matches any existing JoinClause instances on the query. This is useful if you have a table shared between optional constraints and want to ensure it is only added once.

You can opt-in to this behavior by setting preventDuplicateJoins = true in your moduleSettings in config/ColdBox.cfc.

Wheres

Where Methods

where

Adds a where clause to a query.

Any of the following operators can be used in a where clause.

When using the "=" constraint, you can use a shortcut and define the value as the second argument.

To group where statements together, pass a function to the where clause as the only parameter.

This grouping can be nested as many levels as you require.

A Function or QueryBuilder can be used as a subselect expression when passed to value.

andWhere

orWhere

whereBetween

Adds a where between clause to the query.

If a function or QueryBuilder is passed it is used as a subselect expression.

whereNotBetween

whereColumn

Adds a where clause to a query that compares two columns.

Just as with where, when using "=" as the operator you can use a shorthand passing the second column in as the operator and leaving the second column null.

Expressions can be passed in place of either column.

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

It can also be configured with a QueryBuilder instance.

whereNotExists

whereLike

whereNotLike

whereIn

Adds a where in clause to the query.

The values passed to whereIn can be a single value, a list of values, or an array of values.

Some database grammars have a hard limit on the number of parameters passed to a SQL statement. Keep this in mind while writing your queries.

If a list of values is passed in, it is converted to an array of values using a single comma (",") delimiter.

Expressions can be freely mixed in with other values.

A function or QueryBuilder instance can be passed to be used as a subquery expression instead of a list of values.

You may find a whereExists method performs better for you than a whereIn with a subquery.

whereNotIn

whereRaw

Shorthand to add a raw SQL statement to the where clauses.

whereNull

Adds a where null clause to the query.

whereNotNull

Dynamic Where Methods

qb uses onMissingMethod to provide a few different helpers when working with where... methods.

andWhere... and orWhere...

Every where... method in qb can be called prefixed with either and or or. Doing so will call the original method using the corresponding combinator.

where{Column}

If you call a method starting with where that does not match an existing qb method, qb will instead call the where method using the rest of the method name as the first column name. (The rest of the arguments will be shifted to account for this.) This also applies to andWhere{Column} and orWhere{Column} method signatures.

Order By

The orderBy method seems simple but has a lot of depth depending on the type of arguments you pass in.

Calling orderBy multiple times appends to the order list.

Order By (String)

Calling orderBy multiple times will append to the order list.

Order By (List)

Order By (Array of Strings)

Order By (Array of Structs)

Order By (Subquery)

You can order with a subquery using either a function or a QueryBuilder instance.

Order By Raw

clearOrders

reorder

Executing Queries

Schema Builder

External Links

Migration Guide

v9.0.0

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()

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

If you are using uuid with 36 character UUIDs or SQL Server's uniqueidentifier columns, please migrate your uuid calls to guid.

Returning all rows from paginate when maxRows is 0 or lower

If this behavior is fine for your application, you don't need to change anything.

This behavior can be customized by providing a callback to the shouldMaxRowsOverrideToAll setting or init argument.

If you need to revert to the previous behavior, provide the following as the shouldMaxRowsOverrideToAll setting:

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

`autoDeriveNumericType` is now the default

This behavior should be an improvement in most every case without any changes needed.

If you need to revert to the previous behavior, provide the following as the autoDeriveNumericType setting:

moduleSettings = {
    "qb": {
        "autoDeriveNumericType": false
    }
};

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

`strictDateDetection` is now the default

If you are relying on qb treating any strings as dates you will need to parse them as actual date objects first. (You can do so using functions like parseDateTime.

If you need to revert to the previous behavior, provide the following as the strictDateDetection setting:

moduleSettings = {
    "qb": {
        "strictDateDetection": false
    }
};

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

v8.0.0

Where clauses with an OR combinator are now automatically wrapped inside `when` callbacks

This isn't a breaking change that will affect most people. In fact, it will most likely improve your code.

Previously, when using the when control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

Would generate the following SQL:

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

The problem with this statement is that the OR can short circuit the active check.

The fix is to wrap the LIKE statements in parenthesis. This is done in qb using a function callback to where.

qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

When using the when control flow function, it was easy to miss this. This is because you are already in a closure - it looks the same as when using where to group the clauses.

In qb 8.0.0, when will automatically group added where clauses when needed. That means our original example now produces the SQL we probably expected.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Grouping is not needed if there is no OR combinator. In these cases no grouping is added.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );

SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

Additionally, if you do not add any where clauses inside a when callback, nothing changes from qb 7.

The breaking change part is if you were relying on these statements residing at the same level without grouping. In those cases, you may pass the withoutScoping flag to the when callback.

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when(
        condition = len( url.q ),
        onTrue = function( q ) {
            q.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );   
        },
        withoutScoping = true
    );

SELECT *
FROM "users"
WHERE "active" = ?
    AND "username" = ?
    OR "email" = ?

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

Support for Lucee 4.5 and Adobe ColdFusion 11 has been dropped. If you need support for these engines, please remain on an earlier version of qb.

MSSQLGrammar renamed to SqlServerGrammar

To migrate, replace any instances of MSSQLGrammar with SqlServerGrammar. Make sure to also append the @qb namespace, if needed, as explained below.

Variadic Parameters Support Removed

Variadic parameter support was the ability to pass any number of arguments to certain methods like select.

qb.select( "name", "email", "createdDate" );

qb.select( [ "name", "email", "createdDate" ] );

defaultGrammar updated to be the full WireBox mapping

To migrate this code, change your defaultGrammar to be the full WireBox mapping in your moduleSettings:

moduleSettings = {
    "qb": {
        "defaultGrammar": "MSSQLGrammar@qb"
    }
};

value method argument order changed

public any function value(
    required string column,
    string defaultValue = "",
    boolean throwWhenNotFound = false,
    struct options = {}
);

Some methods renamed `callback` to `query`

whereSub
whereInSub
whereExists
orWhereExists
whereNotExists
andWhereNotExists
orWhereNotExists
whereNullSub
orderBySub
subSelect

If you are using named parameters with any of the above methods you will need to migrate your method calls.

v5.0.0

What's New?

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 hasTable and hasColumn.

9.4.0

Allow for setting a defaultSchema property on a SchemaBuilder instance.

The defaultSchema will be used for methods like hasTable and hasColumn. 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

Make replaceBindings 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 CommandBox Migrations.

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

Add the ability to pretend to run queries, both in QueryBuilder and SchemaBuilder.
Add query logging to QueryBuilder and SchemaBuilder instances.

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.

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

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

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

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

`strictDateDetection` is now the default

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.

8.10.0

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.

8.9.1

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

8.9.0

Specify defaultOptions inside of your ColdBox config.

8.8.1

Better parsing of raw statements when deriving insertUsing columns.

8.8.0

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.

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

Add an upsert method. upsert can update or insert multiple records at once depending on if a column is matched.
Allow expressions in value and values. Also add a valueRaw and valuesRaw helper method to make that pattern more ergonomic.
Allow JOIN statements in UPDATE statements. (This is not supported on Oracle.)
Allow updates with subselects using closures or builder instances.

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.

8.6.1

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

8.6.0

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

8.5.0

QueryBuilder

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.

SchemaBuilder

Add support for stored computed columns and virtual computed columns.

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

Add support for mediumtext & longtext types for MySQLGrammar.

8.4.5

Fix limit on simplePaginate.

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

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

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

📹 Watch a walkthrough of this change on CFCasts.

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

8.1.0

📹 Watch a walkthrough of these changes on CFCasts.

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.

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

📹 Watch a walkthrough of these changes on CFCasts.

BREAKING CHANGES

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

Other Changes

Combine clearOrders and orderBy with a new reordermethod.
Clear current selected columns with clearSelect.
Combine clearSelect and either select or selectRaw with reselect and reselectRaw respectively.

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

Allow nullable timestamps in MySQL.

7.9.7

Return 0 on null aggregates.

7.9.6

Match type hints to documentation for join functions

7.9.5

Handle enhanced numeric checks with Secure Profile enabled.

7.9.4

Allow raw statements in basic where clauses.

7.9.3

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

7.9.2

Allow for space-delimited sort directions like column DESC.
Add helpful message when trying to use a closure with from instead of fromSub.
``value and values now work with column formatters.
Correctly format RETURNING clauses with column formatters and ignoring table qualifiers.

7.9.1

Handle multi-word columns in queryRemoveColumns.

7.9.0

Remove elvis operator due to ACF compatibility issues

7.8.0

Add support for MONEY and SMALLMONEY data types to SchemaBuilder.

7.7.3

Fix wrapping of enum types for Postgres.

7.7.2

Compatibility fix for ACF 2018 and listLast parsing.
Include current_timestamp default for timestamp columns in SchemaBuilder.
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.
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.

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

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.

7.3.15

Fix using whereBetween with query param structs (07c9b72)

7.3.14

Ignore orders in aggregate queries (39e1338)

7.3.13

Format with cfformat (dc2a9b6)

7.3.12

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

Switch to using ForgeBox Storage.

7.3.8

Allow passing query options in to paginate (cdecfb3)

7.3.7

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)

7.3.4

Correctly wrap comments in MySQLGrammar.

7.3.2, 7.3.3

Publish qb apidocs to Ortus API Docs.

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

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.

7.1.0

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.

7.0.0

BREAKING CHANGES

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

****

6.4.0

Allow Expressions (query.raw) in update statements.

Common Table Expressions (i.e. CTEs)

Common Table Expressions (CTEs) are powerful SQL concept that allow you to create re-usable temporal result sets, which can be referenced as a table within your SQL. CTEs are available in many common database engines and are available in latest versions of all of the support grammars.

CTEs come in two basic types:

Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.
Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.

While all of the grammars currently support CTEs, there is enough difference between the various databases implementations of CTEs that unless your CTEs are fairly basic, using CTEs within your project will most likely tie your project to a specific database, unless you account for the differences in your code.

However, CTEs are can be extremely useful to solve certain use cases.

To add CTEs to your queries, you have two methods available:

with() — Allows you to define a non-recursive CTE.
withRecursive() — Allows you to define a recursive CTE.

Some database engines require the recursive keyword anytime at least one of your CTEs is recursive, but some database engines (e.g. SQL Server and Oracle) do not require the keyword. qb will manage adding the keyword, if necessary. If your query does use recursion you should use the withRecursive()method to avoid issues when migrating grammars.

with

Name

Type

Required

Default

Description

name

string

true

The name of the CTE.

input

QueryBuilder | Function

true

Either a QueryBuilder instance or a function to define the derived query.

columns

Array<String>

false

[]

An optional array containing the columns to include in the CTE.

recursive

boolean

false

Determines if the CTE statement should be a recursive CTE. Passing this as an argument is discouraged. Use the dedicated withRecursive where possible.

You can build a CTE using a function:

QueryBuilder

// qb
query.with( "UserCTE", function ( q ) {
        q
            .select( [ "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .from( "UserCTE" )
    .get();

MySQL

WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
) SELECT * FROM `UserCTE`

Alternatively, you can use a QueryBuilder instance instead of a function:

QueryBuilder

// qb
var cte = query
    .select( [ "fName as firstName", "lName as lastName" ] )
    .from( "users" )
    .where( "disabled", 0 );

query.with( "UserCTE", cte )
    .from( "UserCTE" )
    .get();

MySQL

WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
)
SELECT * FROM `UserCTE`

A single query can reference multiple CTEs:

QueryBuilder

query.with( "UserCTE", function ( q ) {
        q.select( [ "id", "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .with( "BlogCTE", function ( q ) {
        q.from( "blogs" )
            .where( "disabled", 0 );
    } )
    .from( "BlogCTE as b" )
    .join( "UserCTE as u", "b.Creator", "u.id" )
    .get();

MySQL

WITH `UserCTE` AS (
    SELECT
        `id`,
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
),
`BlogCTE` AS (
    SELECT *
    FROM `blogs`
    WHERE `disabled` = 0
)
SELECT *
FROM `BlogCTE` AS `b`
INNER JOIN `UserCTE` AS `u`
ON `b`.`Creator` = `u`.`id`

withRecursive

Name

Type

Required

Default

Description

name

string

true

The name of the CTE.

input

QueryBuilder | Function

true

Either a QueryBuilder instance or a function to define the derived query.

columns

Array<String>

false

[]

An optional array containing the columns to include in the CTE.

IMPORTANT — The way the SQL in a recursive CTEs are written, using them in your code is likely to lock in you in to a specific database engine, unless you structure your code to build the correct SQL based on the current grammar being used.

Here is an example of building a recursive CTE using SQL Server which would return all parent/child rows and show their generation/level depth:

QueryBuilder

query
.withRecursive( "Hierarchy", function ( q ) {
    q.select( [ "Id", "ParentId", "Name", q.raw( "0 AS [Generation]" ) ] )
        .from( "Sample" )
        .whereNull( "ParentId" )
        // use recursion to join the child rows to their parents
        .unionAll( function ( q ) {
            q.select( [
                    "child.Id",
                    "child.ParentId",
                    "child.Name",
                    q.raw( "[parent].[Generation] + 1" )
                ] )
                .from( "Sample as child" )
                .join( "Hierarchy as parent", "child.ParentId", "parent.Id" );
        } );
    }, [ "Id", "ParentId", "Name", "Generation" ] )
    .from( "Hierarchy" )
    .get();

SqlServer

WITH [Hierarchy] ([Id], [ParentId], [Name], [Generation]) AS (
    SELECT
        [Id],
        [ParentId],
        [Name],
        0 AS [Generation]
    FROM [Sample]
    WHERE [ParentId] IS NULL
    UNION ALL
    SELECT
        [child].[Id],
        [child].[ParentId],
        [child].[Name],

[parent].[Generation] + 1
    FROM [Sample] AS [child]
    INNER JOIN [Hierarchy] AS [parent]
        ON [child].[ParentId] = [parent].[Id]
) SELECT * FROM [Hierarchy]

Inserts, Updates, and Deletes

The following methods all have the same return value:

{
    "result": "Value of the `result` parameter to `queryExecute`",
    "query": "Return value of running `queryExecute` - a CFML query object"
}

insert, update, and delete actions always return a query object for query, regardless of your configured returnFormat.

insert

Name

Type

Required

Default

Description

values

struct | array<struct>

true

A struct or array of structs to insert in to the table.

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

This call must come after setting the query's table using from or table.

You can insert a single record by passing a struct:

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = 55
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

Raw values can be supplied to an insert statement.

QueryBuilder

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "updatedDate" = query.raw( "NOW()" )
    } );

MySQL

INSERT INTO `users` (`age`, `email`, `updatedDate`)
VALUES (?, ?, NOW())

Multiple rows can be inserted in a batch by passing an array of structs to insert.

This is not the same as looping over and array and calling insert in the loop. Using an array with insert will batch the inserts in one SQL call. Looping over an array and calling insert each time will create a SQL request for each item in the array. Bottom line, pass your array to insert!

QueryBuilder

query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "jane@example.com", "name" = "Jane Doe" }
] );

INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

insertIgnore

Name

Type

Required

Default

Description

values

struct | array<struct>

true

A struct or array of structs to insert in to the table.

target

array<string>

false

[]

An array of key column names to match on. (SQL Server and Oracle grammars only.)

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

This call must come after setting the query's table using from or table.

Inserts data into a table while ignoring duplicate key conflicts.

target is only required for SQLServerGrammar and OracleGrammar

QueryBuilder

query.from( "users" )
    .insertIgnore(
        values = [
            { "email" = "foo", "name" = "bar" },
            { "email" = "baz", "name" = "bam" }
        ],
        target = [ "email" ]
    );

INSERT IGNORE INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

MERGE [users] AS [qb_target]
USING (VALUES (?, ?), (?, ?)) AS [qb_src] ([email], [name])
ON [qb_target].[email] = [qb_src].[email]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([email], [name]) VALUES ([email], [name]);

INSERT INTO "users" ("email", "name")
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING

MERGE INTO "USERS" "QB_TARGET"
USING (SELECT ?, ? FROM dual UNION ALL SELECT ?, ? FROM dual) "QB_SRC"
ON "QB_TARGET"."EMAIL" = "QB_SRC"."EMAIL"
WHEN NOT MATCHED THEN
INSERT ("EMAIL", "NAME")
VALUES ("QB_SRC"."EMAIL", "QB_SRC"."NAME")

insertUsing

Name

Type

Required

Default

Description

source

function | QueryBuilder

true

A callback or builder instance to serve as the source of the insert.

columns

array<string>

false

An array of column names that will be inserted. If no columns are passed, the columns will be derived from the source columns and aliases.

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

This call must come after setting the query's table using from or table.

Inserts data into a table using a subquery as the source.

qb.from( "users" )
    .insertUsing( function( q ) {
        q.from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 );
    } );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

You can also pass in an array of column names to avoid aliasing in your source query.

qb.from( "users" )
    .insertUsing(
        columns = [ "email", "createdDate" ],
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                 .select( [ "email", "modifiedDate" ] )
                 .where( "active", 1 );
        }
    );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

Alternatively, the source can be defined as a QueryBuilder object:

qb.from( "users" )
    .insertUsing(
        qb.newQuery()
            .from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 )
    );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

update

Name

Type

Required

Default

Description

values

struct

false

{}

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

This call must come after setting the query's table using from or table.

Updates a table with a struct of column and value pairs.

QueryBuilder

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?

Any constraining of the update query should be done using the appropriate WHERE statement before calling update.

QueryBuilder

query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `Id` = ?

You can update a column based on another column using a raw expression.

QueryBuilder

query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );

MySQL

UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?

Updating Null values

Null values can be inserted by using queryparam syntax:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

if you are using full null support the following (easier) syntax is also allowed:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

Updating with Subselects

Subselects can be used to update values by passing a closure as the value

qb.table( "employees" )
    .update( {
		    "departmentName" = function( q ) {
		        q.from( "departments" )
		            .select( "name" )
		            .whereColumn( "employees.departmentId", "departments.id" );
		    } )
		} );

UPDATE `employees`
SET `departmentName` = (
    SELECT `name`
    FROM `departments`
    WHERE `employees`.`departmentId` = `departments`.`id`
)

You can also pass a builder instance in place of the closure.

qb.table( "employees" )
    .update( {
		    "departmentName" = qb.newQuery()
		        .from( "departments" )
		        .select( "name" )
		        .whereColumn( "employees.departmentId", "departments.id" )
		    } )
		} );

Updating with Joins

qb will correctly format JOIN clauses in your UPDATE statements for your database grammar.

OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using subselects in your UPDATE statement instead.

qb.table( "employees" )
    .join( "departments", "departments.id", "employees.departmentId" )
    .update( {
        "employees.departmentName": qb.raw( "departments.name" )
    } );

UPDATE `employees`
INNER JOIN `departments`
    ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name

UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
    ON [departments].[id] = [employees].[departmentId]

UPDATE "employees"
SET "employees"."departmentName" = departments.name
FROM "departments"
WHERE "departments"."id" = "employees"."departmentId"

addUpdate

Name

Type

Required

Default

Description

values

struct

true

A struct of column and value pairs to add to the update clause.

Adds values to a later update, similar to addSelect.

QueryBuilder

query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();

MySQL

UPDATE `users`
SET `email` = ?,
    `foo` = ?,
    `name` = ?
WHERE `Id` = ?

updateOrInsert

Name

Type

Required

Default

Description

values

struct

true

A struct of column and value pairs to either update or insert.

options

boolean

false

{}

Any additional queryExecute options.

toSql

boolean

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

If an update statement is performed qb applies a limit( 1 ) to the update statement.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

MySQL

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `email` = ?
LIMIT 1

If the configured query returns 0 records, then an insert statement is performed.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

MySQL

INSERT INTO `users` (`email`, `name`)
VALUES (?, ?)

upsert

Name

Type

Required

Default

Description

values

struct | array<struct> | array<string>

true

A struct or array of structs to insert into or update on the table. If a source is provided, this should be an array of column names to update instead.

target

string | array<string>

true

A column name or array of column names to match the values to the table. If a match is found, the record will be updated. Otherwise, a new record will be inserted. Most database grammars required these columns to have either a primary key or a unique index.

update

array | struct

false

null

Either an array of columns to update using the current value matched or a struct containing the column names as keys and the corresponding to update. If blank, it will update all the columns in the passed in value.

source

function | QueryBuilder

false

null

A callback function or QueryBuilder object to use as the source for the upsert. When using this parameter, values must be an array of column names to update.

deleteUmatched

boolean

false

Boolean flag to delete any unmatched source records as part the upsert. (SQL Server only.)

options

boolean

false

{}

Any additional queryExecute options.

toSql

boolean

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

An upsert is a batch operation that either inserts or updates a row depending on if a target match is found. If a row is matched with the target column(s), then the matched row is updated. Otherwise a new row is inserted.

In most database grammars, the target columns are required to be primary key or unique indexes.

qb.table( "users" )
    .upsert(
        values = [
            {
                "username": "johndoe",
                "active": 1,
                "createdDate": "2021-09-08 12:00:00",
                "modifiedDate": "2021-09-08 12:00:00"
            },
            {
                "username": "janedoe",
                "active": 1,
                "createdDate": "2021-09-10 10:42:13",
                "modifiedDate": "2021-09-10 10:42:13"
            },
        ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ],
    );

INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS [qb_src]
    ([active], [createdDate], [modifiedDate], [username])
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([active], [createdDate], [modifiedDate], [username])
    VALUES
    ([active], [createdDate], [modifiedDate], [username])

INSERT INTO "users"
    ("active", "createdDate", "modifiedDate", "username")
VALUES
    (?, ?, ?, ?),
    (? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT ?, ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("ACTIVE", "CREATEDDATE", "MODIFIEDDATE", "USERNAME")
    VALUES
    ("QB_SRC"."ACTIVE", "QB_SRC"."CREATEDDATE", "QB_SRC"."MODIFIEDDATE", "QB_SRC"."USERNAME")

The update clause in a upsert can also accept raw values, making it very useful for tracking data like statistics.

qb.table( "stats" )
    .upsert(
        values = [
            { "postId": 1, "viewedDate": "2021-09-08", "views": 1 },
            { "postId": 2, "viewedDate": "2021-09-08", "views": 1 }
        ],
        target = [ "postId", "viewedDate" ],
        update = { "views": qb.raw( "stats.views + 1" ) }
    );

INSERT INTO `stats`
    (`postId`, `viewedDate`, `views`)
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON DUPLICATE KEY UPDATE
    `views` = stats.views + 1

MERGE [stats] AS [qb_target]
USING (VALUES (?, ?, ?), (?, ?, ?)) AS [qb_src]
    ([postId], [viewedDate], [views])
ON [qb_target].[postId] = [qb_src].[postId]
    AND [qb_target].[viewedDate] = [qb_src].[viewedDate]
WHEN MATCHED THEN UPDATE
    SET [views] = stats.views + 1
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([postId], [viewedDate], [views])
    VALUES
    ([postId], [viewedDate], [views])

INSERT INTO "stats"
    ("postId", "viewedDate", "views")
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON CONFLICT ("postId", "viewedDate") DO UPDATE
    "views" = stats.views + 1

MERGE INTO "STATS" "QB_TARGET"
USING (
    SELECT ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."POSTID" = "QB_SRC"."POSTID"
    AND "QB_TARGET"."VIEWEDDATE" = "QB_SRC"."VIEWEDDATE"
WHEN MATCHED THEN UPDATE
    SET "VIEWS" = stats.views + 1
WHEN NOT MATCHED THEN INSERT
    ("POSTID", "VIEWEDDATE", "VIEWS")
    VALUES
    ("QB_SRC"."POSTID", "QB_SRC"."VIEWEDDATE", "QB_SRC"."VIEWS")

A source callback or QueryBuilder instance can be used instead of explicit values. This allows you to do upserts across tables or subqueries.

To do this, provide a source that is either a function to configure a new QueryBuilder instance or an already configured QueryBuilder instance. Then specify the columns that will be affected as an array of strings to values.

qb.table( "stats" )
    .upsert(
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                .select( [
                    "username",
                    "active",
                    "createdDate",
                    "modifiedDate"
                ] );
        },
        values = [ "username", "active", "createdDate", "modifiedDate" ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ]
    );

INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

MERGE [users] AS [qb_target]
USING (
    SELECT [username], [active], [createdDate], [modifiedDate]
    FROM [activeDirectoryUsers]
) AS [qb_src]
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([username], [active], [createdDate], [modifiedDate])
VALUES ([username], [active], [createdDate], [modifiedDate]);

INSERT INTO "users"
("username", "active", "createdDate", "modifiedDate")
SELECT "username", "active", "createdDate", "modifiedDate"
FROM "activeDirectoryUsers"
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT "USERNAME", "ACTIVE", "CREATEDADATE", "MODIFIEDDATE"
    FROM "ACTIVEDIRECTORYUSERS"
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("USERNAME", "ACTIVE", "CREATEDDATE", "MODIFIEDDATE")
    VALUES
    (
        "QB_SRC"."USERNAME",
        "QB_SRC"."ACTIVE",
        "QB_SRC"."CREATEDDATE",
        "QB_SRC"."MODIFIEDDATE"
    )

delete

Name

Type

Required

Default

Description

any

false

idColumn

string

false

"id"

The name of the id column for the delete shorthand.

options

boolean

false

{}

Any additional queryExecute options.

toSql

boolean

false

If true, returns the raw SQL string instead of running the query. Useful for debugging.

Deletes all records that the query returns.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .delete();

MySQL

DELETE FROM `users`
WHERE `email` = ?

The id argument is a convenience to delete a single record by id.

QueryBuilder

query.from( "users" )
    .delete( 1 );

MySQL

DELETE FROM `users`
WHERE `id` = ?

returning

Name

Type

Required

Default

Description

columns

string | array

true

A single column, a list or columns, or an array of columns to return from the inserted query.

returning is only supported in PostgresGrammar, SqlServerGrammar, and SQLiteGrammar. Using this method on unsupported grammars will result in an UnsupportedOperation exception. Be aware that using this method constrains your grammar choices.

Specifies columns to be returned from the insert query.

QueryBuilder

query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );

INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

The returning function also applies to update and delete calls.

QueryBuilder

query.table( "users" )
    .returning( [ "id", "modifiedDate" ] )
    .where( "id", 1 )
    .update( { "email": "john@example.com" } );

UPDATE [users]
SET [email] = ?
OUTPUT INSERTED.[id], INSERTED.[modifiedDate]
WHERE [id] = ?

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

QueryBuilder

query.table( "users" )
    .returning( "id" )
    .where( "active", 0 )
    .delete();

DELETE FROM [users]
OUTPUT DELETED.[id]
WHERE [active] = ?

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

You can also use raw Expressions in a returning call. This is especially useful for SQL Server returning both the old and new values from an update call.

QueryBuilder

qb.from( "users" )
    .where( "id", 1 )
    .returningRaw( [
        "DELETED.modifiedDate AS oldModifiedDate",
        "INSERTED.modifiedDate AS newModifiedDate"
    ] )
    .update( { "email": "john@example.com" } );

UPDATE [users]
SET [email] = ?
OUTPUT
    DELETED.modifiedDate AS oldModifiedDate,
    INSERTED.modifiedDate AS newModifiedDate
WHERE [id] = ?

Debugging

Debugging a Single Query

toSQL

Name

Type

Required

Default

Description

showBindings

boolean | string

false

If true, the bindings for the query will be substituted back in where the question marks (?) appear as cfqueryparam structs. If inline, the binding value will be substituted back creating a query that can be copy and pasted to run in a SQL client.

Returns the SQL that would be executed for the current query.

QueryBuilder

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL() );

Result

SELECT * FROM "users" WHERE "active" = ?

The bindings for the query are represented by question marks (?) just as when using queryExecute. qb can replace each question mark with the corresponding cfqueryparam-compatible struct by passing showBindings = true to the method.

QueryBuilder

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL( showBindings = true ) );

Result

SELECT * FROM "users" WHERE "active" = {"value":1,"cfsqltype":"CF_SQL_NUMERIC","null":false}

If you want to show the SQL that would be executed for the update, insert, updateOrInsert, or delete methods, you can pass a toSQL = true flag to those methods. Please see those individual methods for more information.

To get back a SQL string that can be copied and pasted into a SQL client to run can be retrieved by passing showBindings = "inline".

QueryBuilder

var q = query.from( "users" )
    .where( "active", "=", 1 );

writeOutput( q.toSQL( showBindings = "inline" ) );

Result

SELECT * FROM "users" WHERE "active" = 1

tap

Name

Type

Required

Default

Description

callback

Function

true

A function to execute with a clone of the current query.

Executes a callback with a clone of the current query passed to it. Any changes to the passed query is ignored and the original query returned.

While not strictly a debugging method, tap makes it easy to see the changes to a query after each call without introducing temporary variables.

QueryBuilder

query.from( "users" )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } )
    .where( "active", "=", 1 )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } );

Result

SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

dump

Name

Type

Required

Default

Description

showBindings

boolean | string

false

A shortcut for the most common use case of tap. This forwards on the SQL for the current query to writeDump. You can pass along any writeDump argument to dump and it will be forward on. Additionally, the showBindings argument will be forwarded on to the toSQL call.

QueryBuilder

query.from( "users" )
    .dump()
    .where( "active", "=", 1 )
    .dump( label = "after where", showBindings = true, abort = true )
    .get();

Result

SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

pretend

A QueryBuilder instance can be put into pretend mode by calling the pretend method. In this mode, the QueryBuilder will turn all query operations into no-ops. A log of the SQL that would have been executed can be retrieved from the query log.

Once a QueryBuilder instance has been set to pretend mode, it cannot be unset. Instead, you will need to obtain a new query.

queryLog

Each instance of a QueryBuilder maintains a log of queries it executed. This can be accessed by calling getQueryLog. This will return an array of structs like so:

[
  {
    "sql": "SELECT * FROM `users` WHERE `active` = ?",
    "bindings": [ { "value": 1, "sqltype": "bit" } ],
    "options": { "datasource": "main" },
    "returnObject": "array",
    "pretend": false,
    "result": {},
    "executionTime": 21
  }
]

This can be very useful in combination with the pretend feature to see what SQL will be executed before actually executing it.

Debugging All Queries

sqlCommenter

You can add contextual information as a comment to all executed queries using sqlCommenter, a specification from Google.

For more information, check out the dedicated sqlCommenter page.

cbDebugger

Starting in cbDebugger 2.0.0 you can view all your qb queries for a request. This is enabled by default if you have qb installed. Make sure your debug output is configured correctly and scroll to the bottom of the page to find the debug output.

LogBox Appender

qb is set to log all queries to a debug log out of the box. To enable this behavior, configure LogBox to allow debug logging from qb's grammar classes.

config/ColdBox.cfc

logbox = {
    debug = [ "qb.models.Grammars" ]
};

qb can be quite chatty when executing many database queries. Make sure that this logging is only enabled for your development environments using ColdBox's environment controls.

ColdBox Interception Points

ColdBox Interception Points can also be used for logging, though you may find it easier to use LogBox. See the documentation for qb's Interception Points for more information.

Columns

Example:

SchemaBuilder

SQL (MySQL)

char

Create a column using a CHAR equivalent type for your database.

Example (default length):

SchemaBuilder

SQL (MySQL)

Example (custom length):

SchemaBuilder

SQL (MySQL)

date

MySQL and Others: Create a column using a CHAR equivalent type for your database and a length of 36. Used in conjunction with the Lucee createGUID method or Java's java.util.UUID.randomUUID().

Example:

SchemaBuilder

MySQL (SQL Server)

SQL (MySQL)

increments

Create an auto-incrementing column using an unsigned INTEGER type. This column is also set as the primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

integer

Create a column using a INTEGER equivalent type for your database.

Example (no precision):

SchemaBuilder

SchemaBuilder

SQL (MySQL)

smallMoney

Create a column using a SMALLMONEY equivalent type for your database.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

softDeletes

Creates a nullable deletedDate TIMESTAMP column.

If you want different names for your timestamp column, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (MySQL)

softDeletesTz

Creates a nullable deletedDate timezone-specific TIMESTAMP column.

If you want different names for your timestamp column, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (SQL Server)

string

Create a column using a VARCHAR equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a non-unicode string.

Example (with defaults):

SchemaBuilder

timestampTz

Create a column using a timezone-specific TIMESTAMP equivalent type for your database.

Example:

SchemaBuilder

SQL (Postgres)

timestampsTz

Creates the createdDate and modifiedDate timezone-specific TIMESTAMP columns.

If you want different names for your timestamp columns, feel free to call other schema builder methods individually.

Example:

SchemaBuilder

SQL (Postgres)

tinyIncrements

Create an auto-incrementing column using an unsigned TINYINT type. This column is also set as the primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

tinyInteger

Create a column using a TINYINT equivalent type for your database.

Example (no precision):

SchemaBuilder

SQL (MySQL)

Example (with precision):

SchemaBuilder

SQL (MySQL)

unicodeLongText

Create a column using a LONGTEXT equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode text field.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

unicodeMediumText

Create a unicode-enabled column using a MEDIUMTEXT equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode text field.

Example:

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

unicodeString

Create a column using a NVARCHAR equivalent type for your database. For databases that distinguish between unicode- and non-unicode string data types, this function will create a unicode string.

Example (with defaults):

SchemaBuilder

SQL (MySQL)

SQL (MSSQL)

Example (with length):

SchemaBuilder

SQL (MySQL)

Creates a column using a CHAR equivalent type for your database and a length of 35. Used in conjunction with the CFML createUUID method.

Example:

SchemaBuilder

SQL (MySQL)

9.4.0

Introduction

Introduction

Requirements

Discussion & Help

Installation

Code Samples

Usage

Installation & Usage

Installation

Usage

Configuration Settings

SQL Type Inferral

Integrating With FW/1

Wiring Up With DI/1

Usage In Your FW/1 Application

For further instructions on getting started with qb & FW/1, refer to this blog post.

Migration Guide

v9.0.0

Dropped support for Adobe ColdFusion 2016

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

Returning all rows from paginate when maxRows is 0 or lower

autoDeriveNumericType is now the default

strictDateDetection is now the default

v8.0.0

Where clauses with an OR combinator are now automatically wrapped inside when callbacks

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

MSSQLGrammar renamed to SqlServerGrammar

Variadic Parameters Support Removed

defaultGrammar updated to be the full WireBox mapping

value method argument order changed

Some methods renamed callback to query

v5.0.0

Contributing & Filing Issues

Query Builder

Getting a New Query

newQuery

Building Queries

Selects

Specifying A Select Clause

select

distinct

addSelect

selectRaw

subSelect

clearSelect

reselect

reselectRaw

From

from

table

fromRaw

fromSub

Joins

join

joinWhere

joinRaw

joinSub

leftJoin

leftJoinRaw

leftJoinSub

rightJoin

rightJoinRaw

rightJoinSub

crossJoin

crossJoinRaw

crossJoinSub

newJoin

JoinClause

on

orOn

Preventing Duplicate Joins

Wheres

Where Methods

where

andWhere

orWhere

whereBetween

whereNotBetween

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

`autoDeriveNumericType` is now the default

`strictDateDetection` is now the default

Where clauses with an OR combinator are now automatically wrapped inside `when` callbacks

Some methods renamed `callback` to `query`

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

`autoDeriveNumericType` is now the default

`strictDateDetection` is now the default

Where clauses with an OR combinator are now automatically wrapped inside `when` callbacks

Some methods renamed `callback` to `query`