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

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

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.

var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.clone();
q2.getFrom(); // "users"

Reset

When you need to remove all configuration for a query, you can call the reset method.

var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.reset();
q2.getColumns(); // "*"

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:

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.

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

queryLog

Each instance of a SchemaBuilder 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.

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.

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.

query.from( "users" ).select( query.raw( "MAX(created_date)" ) );

SELECT MAX(created_date) FROM `users`

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:

• selectRaw

• fromRaw

• joinRaw

• leftJoinRaw

• rightJoinRaw

• crossJoinRaw

• whereRaw

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"

• "none"

• A custom function

By default, qb returns an array of structs as the result of your query. This is the same as specifying array as your returnFormat:

moduleSettings = {
    "qb": {
        "returnFormat": "array"
    }
};

You can get the original query object that CFML generates by setting the returnFormat to query:

moduleSettings = {
    "qb": {
        "returnFormat": "query"
    }
};

This setting can be overridden on a per-instance basis by calling setReturnFormat():

var qb = wirebox.getInstance( "QueryBuilder@qb" );

qb
   .setReturnFormat( 'query' )
   .from( 'users' )
   .get()

If you want complete control over your return result, you can provide a function as a returnFormat. The results of the function will be returned as the results of the builder.

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

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.

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:

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.

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

The when helper is used to allow conditional statements when defining queries without using if statements and having to store temporary variables.

You can pass a third argument to be called in the else case.

when callbacks are automatically scoped and grouped. That means that if a where clause is added inside the callback with an OR combinator the clauses will automatically be grouped (have parenthesis put around them.) You can disable this feature by passing withoutScoping = true to the when callback.

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:

postQBExecute

The following information is available in the interceptData struct:

Dropping tables straightforward in qb.

For dropping columns or constraints, see Alter.

drop

Drop a table from the database.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

dropIfExists

Drop a table from the database if it exists.

Example:

SchemaBuilder

schema.dropIfExists( "user_logins" );

SQL (MySQL)

DROP TABLE IF EXISTS `user_logins`

groupBy

Passing a single string will group by that one column.

You can also pass a list of column names. A single comma (",") will be used as the delimiter.

An array of column names can be provided.

Calling groupBy multiple times will to the current groups.

An can be passed in place of a column.

having

Adds a having clause to a query.

Expressions can be used in place of the column or the value.

Installation

Installation is easy through and . 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

• SQLiteGrammar

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 .

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

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.

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.

Replacing or Inlining Bindings

qb can inline the query bindings into the SQL string that it has built up. This is used by other tools like or to provide a richer debugging experience. It is also publicly available for other libraries to use, such as .

replaceBindings

Replace the question marks (?) in a sql string with the bindings provided.

This method allows you to create a table object.

The majority of the work comes from calling methods on the Blueprint object. A Blueprint defines the and for your tables.

Example:

This would convert to the following SQL in MySQL:

Only one table can be created at a time. If you wanted to create multiple tables, you would call create multiple times.

The callback argument is where you define the schema of your table. It is passed a Blueprint object. This is commonly aliased as table in the callback. Blueprint defines the field, index and constraint methods to build your table. You can find a comprehensive list of all available methods here for and here for .

Custom Parameter Types

When passing a parameter to qb, it will infer the sql type to be used. If you pass a number, CF_SQL_NUMERIC will be used. If it is a date, CF_SQL_TIMESTAMP, and so forth. If you need more control, you can pass a struct with the parameters you would pass to cfqueryparam.

query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );

SELECT *
FROM `users`
WHERE `id` = ?

This can be used when inserting or updating records as well.

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

INSERT INTO `users`
    (`id`, `age`, `updatedDate`)
VALUES
    (?, ?, ?)

Strict Date Detection

By default, qb will try to determine if a variable is a date using the built-in isDate function. This can have some interesting effects with different formatted strings. You can opt in to stricter date detection which will check the underlying Java class of the value to determine if the value is a date. This is more accurate, but does require you to specifically pass date instances instead of strings. For this reason, it is currently opt-in to not break existing applications. It is likely to become the default in the next major version of qb.

You can opt in to stricter date detection by setting strictDateDetection = true in your moduleSettings in config/ColdBox.cfc.

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

Numeric SQL Type

By default, qb will use the CF_SQL_NUMERIC SQL type when it detects a numeric binding. You can specify your own default SQL type to use with numeric values using the numericSQLType setting in your moduleSettings in config/ColdBox.cfc.

moduleSettings = {
    "qb": {
        "numericSQLType": "CF_SQL_INTEGER"
    }
};

There is an opt-in feature to better derive the numeric SQL type for database performance reasons. If you do opt in to this, qb will use a different SQL type for integers than decimals. You can opt in to this feature using the autoDeriveNumericType setting and can customize the SQL types by setting the integerSqlType and decimalSqlType settings.

moduleSettings = {
    "qb": {
        "autoDeriveNumericType": true,
        "integerSqlType": "CF_SQL_INTEGER",
        "decimalSqlType": "CF_SQL_DECIMAL"
    }
};

Automatic Scale Detection

In some combinations of database grammars and CFML engines, the scale argument on a cfqueryparam would default to 0. This would cause issues when attempting to insert a floating point number, even when using the correct SQL type (i.e., CF_SQL_DECIMAL) . In 8.5.0, qb now automatically calculates a scale based on the value provided if the value is a floating point number. This can be disabled by setting autoAddScale in your ColdBox config or passing autoAddScale = false when instantiating your QueryBuilder instance.

Bindings

Bindings are the values that will be sent as parameters to a prepared SQL statement. This protects you from SQL injection. In CFML, this uses cfqueryparam to parameterize the values.

If you need to inspect the bindings for the current query you can retrieve them in order using the getBindings method.

getBindings

This method returns the current bindings in order to be used for the query.

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

[
    { value = "01 May 2019", cfsqltype = "CF_SQL_TIMESTAMP"  },
    { value = 1, cfsqltype = "CF_SQL_NUMERIC" }
]

You can also retrieve the bindings associated to their corresponding types.

getRawBindings

This method returns the current bindings to be used for the query associated to their corresponding types.

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

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

A TableIndex can be created directly from a Blueprint or from a existing Column. The TableIndex includes methods for further configuring the index which is required when defining foreign keys.

references

Set the referencing column for a foreign key relationship. For example, id for a country_id column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );

SQL (MySQL)

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
)

onTable

Sets the referencing table for a foreign key relationship. For example, countries for a country_id column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );

SQL (MySQL)

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
)

onUpdate

Set the strategy for updating foreign keys when the parent key is updated.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onUpdate( "CASCADE" );
} );

SQL (MySQL)

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
)

onDelete

Set the strategy for updating foreign keys when the parent key is deleted.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onDelete( "SET NULL" );
} );

SQL (MySQL)

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
)

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.

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.

limit

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.

Debugging a Single Query

toSQL

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

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

writeOutput( q.toSQL() );

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.

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

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

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

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

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

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

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.

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

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

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.

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

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.

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.

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

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.

The query builder also lets you create union statements on your queries using either UNION or UNION ALL strategies.

The union methods take either a Query Builder instance or a closure which you use to define a new QueryBuilder instance.

Union statements are added in the order in which the union methods are invoked, but the union statements can be in any order in your API call stack. This means you can safely declare your union method calls before the select, from and orderBy calls on the source Query Builder instance.

• union() — This method builds a SQL statement using the UNION clause which combines two SQL queries into a single result set containing all the matching rows. The two queries must have the same defined columns and compatible data types or the SQL engine will generate an error. The union clause only returns unique rows.

• unionAll() — This builds a SQL statement using the UNION ALL clause. This is the same as union but includes duplicate rows.

union

Adds a UNION statement to the query.

Adding multiple union statements will append it to the query.

It can also add union queries as QueryBuilder instances.

unionAll

Adds a UNION ALL statement to the query.

Adding multiple unionAll statements will append it to the query.

It can also add union queries as QueryBuilder instances.

The query builder also provides a variety of aggregate methods such as count, max, min, and sum. These methods take the headache out of setting up these common aggregate functions.

When executing any of the aggregate functions, any where restrictions on your query will still be applied.

Instead of returning a query, these methods return a simple value.

exists

Returns true if the query returns any rows. Returns false otherwise.

query.from( "users" ).where( "username", "like", "jon%" ).exists();

SELECT COUNT(*) AS aggregate 

FROM `users` WHERE `username` LIKE 'jon%'

count

Returns an integer number of rows returned by the query.

query.from( "users" ).count();

SELECT COUNT(*) AS aggregate FROM `users`

SELECT COUNT(*) FROM [users]

max

Returns the maximum value for the given column.

query.from( "users" ).max( "age" );

SELECT MAX(age) AS aggregate FROM `users`

min

Returns the minimum value for the given column.

query.from( "users" ).min( "age" );

SELECT MIN(age) AS aggregate FROM `users`

sum

Returns the sum of all returned rows for the given column.

query.from( "employees" ).sum( "salary" );

SELECT SUM(salary) AS aggregate FROM `employees`

sumRaw

Returns the sum of all returned rows for the expression.

query.from( "accounts" ).sumRaw( "netAdditions + netTransfers" )

SELECT SUM(netAdditions + netTransfers) AS aggregate FROM `accounts`

columnList

Retrieves the columns for the configured table.

query.from( "users" ).columnList();

[ "id", "firstName", "lastName", "username", "email", "password" ]

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.

moduleSettings = {
    "qb": {
        "sqlCommenter": {
            "enabled": true
        }
    }
};

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

An example query with a sqlCommenter comment looks like this:

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'*/

Configuring sqlCommenter

The default configuration structure for sqlCommenter is as follows:

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

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:

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

}

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

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

}

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.

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'*/" );

{
    "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": "/"
    }
}

parseCommentString

Parses a comment string into a struct.

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'*/"
    );

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

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

Sometimes you want to add constraints on a table level, rather than a column level. The following methods will let you accomplish that.

index

Create a generic index from one or more columns.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.string( "first_name" );
    table.string( "last_name" );
    table.index( [ "first_name", "last_name" ], "idx_users_full_name" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    INDEX `idx_users_full_name` (`first_name`, `last_name`)
)

foreignKey

Create a foreign key constraint from one or more columns. Follow up this call with calls to the TableIndex's references and onTable methods.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" );
    table.foreignKey( "country_id" ).references( "id" ).onTable( "countries" );
} );

SQL (MySQL)

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
)

primaryKey

Create a primary key constraint from one or more columns.

Example:

SchemaBuilder

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

SQL (MySQL)

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

unique

Create a unique constraint from one or more columns.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "username ");
    table.unique( "username" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    UNIQUE (`username`)
)

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

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

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

is now the default

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

is now the default

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

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

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

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.

addSelect

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

selectRaw

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

The expression is added to the other already selected columns.

(To learn more about raw and expressions, check out the docs on .)

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

Clears out the selected columns for a query along with any configured select bindings. Then sets a selection of columns to select from the query. Any valid argument to can be passed here.

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.

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

Order By (String)