1 of 44

9.2.0 Introduction

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

Using qb, you can:

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:

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.

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:

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

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.

Building Queries

From

from

Group By and Having

groupBy

Limit, Offset, and Pagination

limit

Name

Type

Required

Default

Description

value

Sets the limit value for the query.

take

Sets the limit value for the query. Alias for .

offset

Sets the offset value for the query.

forPage

Helper method to calculate the limit and offset given a page number and count per page.

simplePaginate & paginate

This method combines forPage, count, and get to create a pagination struct alongside the results. Information on the simplePaginate or paginate methods, including custom pagination collectors, can be found in the section of the documentation.

Raw Expressions

Raw expressions are the qb escape hatch. While qb strives to provide ways to execute the majority of queries, you will occasionally need to provide raw sql values that are not processed by qb. These SQL snippets are called raw or Expressions in qb.

raw expressions are useful, but shoud be used only if there is not another way to accomplish the same action using other qb methods. This is because a raw expression has the potential to use syntax specific to one database grammar or another, preventing you from easily switching from one grammar to another, one of the major benefits of using qb.

The first way to retrieve an Expression is to call the raw method on the QueryBuilder object.

raw

The sql snippet passed to raw is not processed by qb at all. With that in mind, it is important to follow all best practices and security recommendations with the sql you use with raw.

Expressions can be passed to most qb methods, like select, from, where, or orderBy, among others. Additionally, qb provides some convenience methods to add raw values in different parts of the query:

When / Conditionals

If you store the builder object in a variable, you can use if and else statements like you would expect.

This works, but breaks chainability. To keep chainability you can use the when helper method.

`when`

Executing Queries

Options and Utilities

Query Options

Each query execution method allows for the passing of an options struct. This is the same struct you would pass to queryExecute.

Default Options

qb allows you to specify default options when creating the QueryBuilder instance using the defaultOptions argument.

You can set defaultOptions for the default QueryBuilder (QueryBuilder@qb) in your config/ColdBox.cfc file under moduleSettings.

You can also combine this with WireBox to create custom QueryBuilder instances pointing to different datasources and even different grammars.

When mapping to components provided by modules, such as qb, use the interception point inside your config/WireBox.cfc to ensure all modules are fully loaded and available.

Retrieving results from alternative datasources

In Application.cfc you can specify your default datasource which will be used by qb. If you want to retrieve data from other datasources you can specify this in all retrieval functions by using the extra options parameter such as:

If you also want to use a non-default SQL Grammar you have to specify this when creating your QueryBuilder.

Clone and Reset

Clone

At times you may need to duplicate a query. Using clone you have a performant way to duplicate a query without using the duplicate method.

Return Format

returnFormat refers to the transformation your executed query makes (if any) before being returned to you. You can choose one of three return formats:

"array"
"query"

Column Formatter

Available as an advanced option for framework authors, qb will call out to a column formatter prior to processing a column as part of the SQL query. This allows frameworks like Quick to define queries using aliases and transform them to columns during execution.

You can provide your own column formatter function to qb through the init method or by calling setColumnFormatter. It is a function that takes a column string and returns a string

query.setColumnFormatter( function( column ) {
    return lcase( arguments.column );
} );

Interception Points

Two interception points are available from QB: preQBExecute and postQBExecute. These fire before and after the queryExecute call, respectively.

preQBExecute

The following information is available in the interceptData struct:

Schema Builder

Create

This method allows you to create a table object.

Drop

Dropping tables straightforward in qb.

For dropping columns or constraints, see .

drop

Drop a table from the database.

Debugging

pretend

A SchemaBuilder instance can be put into pretend mode by calling the pretend method. In this mode, the SchemaBuilder 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 SchemaBuilder instance has been set to pretend mode, it cannot be unset. Instead, you will need to obtain a new SchemaBuilder

External Links

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

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

// 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)" ];
}

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

[
  {
    "sql": "CREATE TABLE `users` (`id` INT PRIMARY KEY AUTO_INCREMENT, `email` VARCHAR NOT NULL)",
    "bindings": [],
    "options": { "datasource": "main" },
    "returnObject": "array",
    "pretend": false,
    "result": {},
    "executionTime": 21
  }
]

config/ColdBox.cfc

moduleSettings = {
    "qb": {
        "returnFormat": function( q ) {
            return application.wirebox.getInstance(
                "name" = "Collection",
                "initArguments" = { "collection": q }
            );
        }
    }
};

config/WireBox.cfc

component {

    function afterAspectsLoad() {
        binder.map( "MyCustomQueryBuilder" )
            .to( "qb.models.Query.QueryBuilder" )
            .initArg( name = "grammar", ref = "AutoDiscover@qb" )
            .initArg( name = "defaultOptions", value = {
                "datasource": "my_custom_datasource" 
            } );
    }

}

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" );
    table.timestamp( "modified_date" );
    table.timestamp( "last_logged_in" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP NOT NULL,
    `modified_date` TIMESTAMP NOT NULL,
    `last_logged_in` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

sqlCommenter

qb supports the sqlCommenter specification by Google for appending contextual information to executed queries.

sqlCommenter support is off by default, but can be activated by setting the sqlCommenter.enabled setting.

Once enabled, qb will append a comment on to every non-commented query. This happens as the query is ran, so you will not see this output when calling toSQL() or dump().

sqlCommenter will only add a comment to non-commented queries. If you query contains a comment anywhere in it, sqlCommenter will ignore it.

An example query with a sqlCommenter comment looks like this:

Configuring sqlCommenter

The default configuration structure for sqlCommenter is as follows:

When the enabled flag is false, no comments will be appended.

The commenters array are the different components that will add contextual information to each query. You define them by defining a struct with a class key pointing to a WireBox mapping and a properties key containing a struct of any necessary properties.

Commenters

Each Commenter must implement the ICommenter interface. (The implements keyword is not required.). They will be called with the sql being commented and the current datasource. It should return a struct of key/value pairs that will become comments.

Here is an example of the FrameworkCommenter@qb:

You may use any. all, or none of the commenters provided by qb. You may also create your own for your application. You may even see commenters pop up on for popular use cases.

For example, if you use cbauth (or cbsecurity using cbauth), this commenter will add the current user ID to each query.

Parsing Commented SQL

The comment generated by sqlCommenter is escaped and url-encoded. It can be reversed by calling the SQLCommenter.parseCommentedSQL method with the full query or the SQLCommenter.parseCommentString method with just the comment.

parseCommentedSQL

Parses a commented SQL string into the SQL and a struct of the key/value pair comments.

parseCommentString

Parses a comment string into a struct.

Integration with non-ColdBox applications

Out of the box, qb includes a ColdBoxSQLCommenter. Since sqlCommenter adds contextual information, some level of framework or application integration is necessary. You can create your own sqlCommenter instance by extending the qb.models.SQLCommenter.SQLCommenter abstract component. If you create a SQLCommenter for a specific framework, consider sharing it with others on .

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.

The grammars provided by qb are:

MySQLGrammar
OracleGrammar
PostgresGrammar
SqlServerGrammar

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

Configuration Settings

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

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:

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.

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.

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

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

QueryBuilder

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", function ( q ) {
        q.select( [ "lName as lastName", "fName as firstName" ] )
            .from( "users" )
            .where( "age", ">=", 21 )
        ;
    } )
    .orderBy( "lastName" )
    .get()

QueryBuilder

var legalUsersQuery = query
    .select( [ "lName as lastName", "fName as firstName" ] )
    .from( "users" )
    .where( "age", ">=", 21 );

query.select( [ "firstName", "lastName" ] )
    .fromSub( "legalUsers", legalUsersQuery )
    .orderBy( "lastName" )
    .get();

QueryBuilder

query.from( "posts" )
    .when(
        someFlag,
        function( q ) {
            q.orderBy( "published_date", "desc" );
        },
        function( q ) {
            q.orderBy( "modified_date", "desc" );
        }
    );

SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/

settings = {
    "sqlCommenter": {
        "enabled": false,
        "commenters": [
            { "class": "FrameworkCommenter@qb", "properties": {} },
            { "class": "RouteInfoCommenter@qb", "properties": {} },
            { "class": "DBInfoCommenter@qb", "properties": {} }
        ]
    }
};

component singleton accessors="true" {

    property name="coldboxVersion" inject="coldbox:coldboxSetting:version";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "version": "coldbox-#variables.coldboxVersion#" };
    }

}

component singleton accessors="true" {

    property name="auth" inject="AuthenticationService@cbauth";

    property name="properties";

    /**
     * Returns a struct of key/value comment pairs to append to the SQL.
     *
     * @sql         The SQL to append the comments to. This is provided if you need to
     *              inspect the SQL to make any decisions about what comments to return.
     * @datasource  The datasource that will execute the query. If null, the default datasource will be used.
     *              This can be used to make decisions about what comments to return.
     */
    public struct function getComments( required string sql, string datasource ) {
        return { "userId": variables.auth.getUserId() };
    }

}

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentedSQL( "SELECT * FROM foo /*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/" );

Result

{
    "sql": "SELECT * FROM foo",
    "comments": {
        "action": "index",
        "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
        "event": "Main.index",
        "framework": "coldbox-6.0.0",
        "handler": "Main",
        "route": "/"
    }
}

getInstance( "ColdBoxSQLCommenter@qb" )
    .parseCommentString(
        "/*action='index',dbDriver='mysql-connector-java-8.0.25%20%28Revision%3A%2008be9e9b4cba6aa115f9b27b215887af40b159e0%29',event='Main.index',framework='coldbox-6.0.0',handler='Main',route='%2F'*/"
    );

Result

{
    "action": "index",
    "dbDriver": "mysql-connector-java-8.0.25 (Revision: 08be9e9b4cba6aa115f9b27b215887af40b159e0)",
    "event": "Main.index",
    "framework": "coldbox-6.0.0",
    "handler": "Main",
    "route": "/"
}

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

}

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

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

You can pass include any parameters you would use with cfqueryparam including null, list, etc. This applies anywhere parameters are used including where, update, and insert methods.

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } )
    .union( function ( q ) {
        q.from( "users" )
            .select("name")
            .where( "id", 3 );
    } );

QueryBuilder

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( q1 )
    .union( q2 );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } );

QueryBuilder

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 3 );
     } );

QueryBuilder

var q1 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 2 );
    
var q2 = query.newQuery()
    .from( "users" )
    .select( "name" )
    .where( "id", 3 );

query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( q1 )
    .unionAll( q2 );

QueryBuilder

query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );

QueryBuilder

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

QueryBuilder

query.from( "users" )
    .join( "logins", function( j ) {
        j.on( "users.id", "logins.user_id" );
        j.where( "logins.created_date", ">", dateAdd( "m", -1, "01 Jun 2019" ) );
    } )
    .where( "active", 1 );

Result

{
    "commonTables" = [],
    "select" = [],
    "join" = [
        { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    ],
    "where" = [
        { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
    ],
    "union" = [],
    "insert" = [],
    "insertRaw" = [],
    "update" = []
};

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE ON DELETE NO ACTION
)

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL
)

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

You can build a CTE using a function:

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

A single query can reference multiple CTEs:

withRecursive

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:

Debugging

Debugging a Single Query

toSQL

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

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.

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

tap

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.

dump

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.

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 .

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:

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

Debugging All Queries

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

For more information, check out the .

cbDebugger

Starting in 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.

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

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 for more information.

QueryBuilder

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

QueryBuilder

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

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

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`

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]

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)

schema.create( "posts_users", function( table ) {
    table.unsignedInteger( "post_id" ).references( "id" ).onTable( "posts" );
    table.unsignedInteger( "user_id" ).references( "id" ).onTable( "users" );
    table.primaryKey( [ "post_id", "user_id" ], "pk_posts_users" );
} );

CREATE TABLE `posts_users` (
    `post_id` VARCHAR(255) NOT NULL,
    `user_id` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`),
    CONSTRAINT `fk_posts_users_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_posts_users_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT ""pk_users_first_name_last_name"" PRIMARY KEY (""first_name"", ""last_name"")
)

QueryBuilder

query.subSelect( "last_login_date", function( q ) {
    q.selectRaw( "MAX(created_date)" )
        .from( "logins" )
        .whereColumn( "users.id", "logins.user_id" );
} ) ).from( "users" );

QueryBuilder

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

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

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 and

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 (matching CFML's ) and (matching Java's UUID or 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 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:

is now the default

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

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:

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

is now the default

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

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 .

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

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 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 control flow function, you were fully responsible for the wrapping of your where statements. For example, the following query:

Would generate the following SQL:

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.

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.

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

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.

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.

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,

Variadic Parameters Support Removed

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

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:

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:

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.

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

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.

orWhereExists

// 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 & "%" );   
    } );

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 & "%" );
        } );
    } );

// 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
    );

// manually
var schema = new qb.models.schema.SchemaBuilder(
    grammar = new qb.models.grammars.MySQLGrammar(),
    defaultOptions = { datasource: "my_datasource" }
);

// WireBox
var schema = wirebox.getInstance( "SchemaBuilder@qb" );

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" ).nullable();
    table.timestamp( "modified_date" ).nullable();
} );

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP,
    `modified_date` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

Column Modifiers

When creating a column from the Blueprint object, a Column object is returned. This column gives you access to a few modifier commands to further configure the column.

Sets the column as unsigned.

Example:

SchemaBuilder

SQL (MySQL)

unique

Sets the column to have the UNIQUE constraint.

Example:

SchemaBuilder

SQL (MySQL)

withCurrent

Sets the column to have the a default value of CURRENT_TIMESTAMP.

Example:

SchemaBuilder

SQL (Postgres)

storedAs

Creates a stored computed column. Computed columns are defined as expressions between other columns and/or constant values. Stored computed columns are saved in the database to avoid computing on every query.

Your database grammar may not differentiate between stored computed columns and virtual computed columns. Research your grammar's implementation for more details.

virtualAs

Creates a virtual computed column. Computed columns are defined as expressions between other columns and/or constant values. Virtual computed columns are computed on every query.

Your database grammar may not differentiate between stored computed columns and virtual computed columns. Research your grammar's implementation for more details.

schema.create( "users", function( table ) {
    table.boolean( "is_active" ).default( 1 );
    table.timestamp( "created_date" ).default( "NOW()" );
    tablVIRTUAL NOT NULLe.string( "country" ).default( "'USA'" );
} );

CREATE TABLE `users` (
    `country_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `fk_users_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE NO ACTION ON DELETE CASCADE
)

QueryBuilder

query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );

What's New?

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 and .
Add query logging to and 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 and

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

Returning all rows from when maxRows is 0 or lower

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:

is now the default

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

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 , qb now supports SQLite for both QueryBuilder and SchemaBuilder. You can use it in your apps by specifying SQLiteGrammar@qb as the default grammar.

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

helper function

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

Dedicated method

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

helper method

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

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.

8.10.0

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

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

8.8.1

Better parsing of raw statements when deriving columns.

8.8.0

New Features and Improvements

Insert data based off of a callback or builder using .
Insert data ignoring duplicate key errors using .
Use a callback or builder as the source for an statement.

Bug Fixes

Don't uppercase quoted aliases in Oracle.
Fix for aliases in update statements.
Don't sort columns for insertUsing.

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 method. upsert can update or insert multiple records at once depending on if a column is matched.
Allow expressions in and . Also add a and helper method to make that pattern more ergonomic.
Allow . (This is not supported on Oracle.)

Bug Fixes

Better handling of and clauses in queries.
Allow any value to be returned from including strings, numbers, and dates.
Provide default values for and if no records are returned.

8.6.1

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

8.6.0

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

8.5.0

QueryBuilder

Add a method to QueryBuilder.
Add helpers such as , , , and .
Correct return aggregate values for date values from max and min executors.

SchemaBuilder

Add support for and .

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 & types for MySQLGrammar.

8.4.5

Fix limit on .

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

📹

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

8.1.0

📹

now can accept bindings.
A new, optional 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

📹

BREAKING CHANGES

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

Other Changes

Combine and orderBy with a new method.
Clear current selected columns with .
Combine and either or with and 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 in MySQL.

7.9.7

Return 0 on null .

7.9.6

Match type hints to documentation for 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 method when calling .

7.9.2

Allow for space-delimited directions like column DESC.
Add helpful message when trying to use a closure with instead of .
`` and now work with

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 and data types to .

7.7.3

Fix wrapping of types for Postgres.

7.7.2

Compatibility fix for ACF 2018 and listLast parsing.
Include current_timestamp default for 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 method. Any already configured orders are cleared. Any orders added after this call will be added as normal.
now can take an array of expressions.

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

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

7.3.14

Ignore orders in aggregate queries ()

7.3.13

Format with cfformat ()

7.3.12

Improve column wrapping with trimming ()
Prefer the parent query over magic methods when the parent query has the exact method. ()

7.3.9, 7.3.10, 7.3.11

Switch to using .

7.3.8

Allow passing query options in to paginate ()

7.3.7

Fix for inserting null values directly ()

7.3.5, 7.3.6

Use cfformat for automatic formatting ()
Add a type to the onMissingMethod exception ()

7.3.4

Correctly wrap in MySQLGrammar.

7.3.2, 7.3.3

Publish qb apidocs to .

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 to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like to allow Quick features like scopes to be available inside any closures.

7.1.0

Lambdas (arrow functions) are now allowed wherever closures are allowed.
Add an method.
Allow for fully-qualified column names (table_name.column.name) in the and methods.

7.0.0

BREAKING CHANGES

Please see the 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.

Other Changes

Completely revamped documentation! (You're looking at it right now.)
Add new flag to 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.

****

6.4.0

Wheres

Table of Contents

Where Methods

where

Adds a where clause to a query.

Using the where method will parameterize the value passed. If you want to constrain a column to another column, use the method.

You can also pass an as the value.

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.

You may also use statements to simplify this further.

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

This method is simply an alias for with the combinator set to "and".

orWhere

This method is simply an alias for with the combinator set to "or".

whereBetween

Adds a where between clause to the query.

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

whereNotBetween

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

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

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

whereLike

A shortcut for calling with "like" set as the operator.

whereNotLike

A shortcut for calling with "not like" set as the operator.

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.

Any value in the list or array can also be passed using a to have more control over the parameter settings.

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

Adds a where not in clause to the query. This behaves identically to the whereIn method with the negateflag set to true. See the documentation for for usage and examples.

whereRaw

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

whereNull

Adds a where null clause to the query.

whereNotNull

Adds a where not in clause to the query. This behaves identically to the method with the negateflag set to true. See the documentation for for usage and examples.

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.

QueryBuilder

query.from( "users" )
    .where( "email", "foo" )
    .orWhere( "id", "=", function( q ) {
        q.select( q.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" );
    } );

QueryBuilder

query.from( "users" )
    .whereBetween(
        "id",
        function( q ) {
            q.select( q.raw( "MIN(id)" ) )
                .from( "users" )
                .where( "email", "bar" );
        },
        builder.newQuery()
            .select( builder.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" )
    );

QueryBuilder

query.from( "orders" )
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) )
            .from( "products" )
            .whereColumn( "products.id", "orders.id" );
    } );

QueryBuilder

var existsQuery = query.newQuery()
    .select( q.raw( 1 ) )
    .from( "products" )
    .whereColumn( "products.id", "orders.id" );

query.from( "orders" )
    .whereExists( existsQuery );

Inserts, Updates, and Deletes

The following methods all have the same return value:

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

insert

Name

Type

Required

Default

Description

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

You can insert a single record by passing a struct:

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

Raw values can be supplied to an insert statement.

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!

insertIgnore

Name

Type

Required

Default

Description

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

Inserts data into a table while ignoring duplicate key conflicts.

target is only required for SQLServerGrammar and OracleGrammar

insertUsing

Name

Type

Required

Default

Description

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

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

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

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

update

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

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

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

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

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

Updating Null values

Null values can be inserted by using queryparam syntax:

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

Updating with Subselects

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

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

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 in your UPDATE statement instead.

addUpdate

Adds values to a later , similar to .

updateOrInsert

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.

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

upsert

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.

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

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.

delete

Deletes all records that the query returns.

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

returning

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.

The returning function also applies to update and delete calls.

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

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

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]);

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

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

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

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

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

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

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

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

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

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

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

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

QueryBuilder

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

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.

Table of Contents

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:

`` are also supported as the table argument (though you may prefer the readability of the method):

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:

You can specify clauses in your joins as well.

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

A preconfigured can also be passed to the join function. This allows you to extract shared pieces of code out to different functions.

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:

For complex joins, a function can be passed to first. This allows multiple on and where conditions to be applied to the join. See the documentation for for more information.

joinRaw

Uses the raw SQL provided to as the table for the join clause. All the other functionality of joinRaw matches the method. Additionally, there are , , and crossJoinRaw methods available.

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

Adds a join to a derived table. All the functionality of the method applies to constrain the query. The derived table can be defined using a QueryBuilder instance:

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

Uses the raw SQL provided to as the table for the left join clause. All the other functionality of leftJoinRaw matches the method.

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

Adds a left join to a derived table. All the functionality of the method applies to define and constrain the query.

rightJoin

rightJoinRaw

Uses the raw SQL provided to as the table for the right join clause. All the other functionality of rightJoinRaw matches the method.

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

Adds a right join to a derived table. All the functionality of the method applies to define and constrain the query.

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

Adds a cross join to a derived table. The derived table can be defined using a QueryBuilder instance or a function just as with . Cross joins cannot be constrained, however.

newJoin

Creates a new . A is a specialized version of a QueryBuilder. You may call on or orOn to constrain the . You may also call any methods.

Creating a directly is useful when you need to share a join between different queries. You can create and configure the in a function and pass it to queries as needed.

Although a can be passed to , , , and crossJoin, the type of the will override the type of the function.

JoinClause

A JoinClause is a specialized version of a QueryBuilder. You may call on or orOn to constrain the JoinClause. You may also call any methods.

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.

QueryBuilder

query.from( "users" )
    .join( "posts", function( j ) {
        j.on( function( j1 ) {
            j1.on( "users.id", "posts.author_id" )
                .orOn( "users.id", "posts.reviewer_id" );
        } );
        j.whereNotNull( "posts.published_date" );
    } );

QueryBuilder

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, "u.id", "=", "c.id" );

QueryBuilder

query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, function( j ) {
        j.on( "u.id", "c.id" );
        j.on( "u.type", "c.type" );
    } );

QueryBuilder

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .leftJoinSub( "c", sub, "u.id", "=", "c.id" );

QueryBuilder

var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .rightJoinSub( "c", sub, "u.id", "=", "c.id" );

QueryBuilder

// This is still an inner join because
// the JoinClause is an inner join
var j = query.newJoin( "contacts", "inner" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).leftJoin( j );

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

Introduction

Introduction

Contributing & Filing Issues

Query Builder

Getting a New Query

newQuery

Building Queries

From

from

Group By and Having

groupBy

Limit, Offset, and Pagination

limit

take

offset

forPage

simplePaginate & paginate

Raw Expressions

raw

When / Conditionals

when

Executing Queries

Options and Utilities

Query Options

Default Options

Retrieving results from alternative datasources

Clone and Reset

Clone

Return Format

Column Formatter

Interception Points

preQBExecute

Schema Builder

Create

Drop

drop

Debugging

pretend

External Links

Building Queries

Column Formatter

Introduction

Introduction

Executing Queries

Options and Utilities

Requirements

Discussion & Help

Installation

Code Samples

Usage

Contributing & Filing Issues

Getting a New Query

newQuery

Debugging

pretend

Clone and Reset

Clone

Return Format

queryLog

Query Options

Default Options

Retrieving results from alternative datasources

Drop

drop

Interception Points

preQBExecute

Create

dropIfExists

postQBExecute

Limit, Offset, and Pagination

limit

take

offset

forPage

simplePaginate & paginate

Raw Expressions

raw

From

from

`when`

`when`