Only this pageAll pages
Powered by GitBook
1 of 43

8.6.0

Loading...

Loading...

Loading...

Loading...

Loading...

Query Builder

Loading...

Building Queries

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Executing Queries

Loading...

Loading...

Loading...

Options and Utilities

Loading...

Loading...

Loading...

Loading...

Parent Query

Loading...

Loading...

Schema Builder

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

External Links

Migration Guide

v8.0.0

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

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

Would generate the following SQL:

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

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

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

qb.from( "users" )
    .where( "active", 1 )
    .where( function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );
    } );
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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

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

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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

// qb 8.0.0
qb.from( "users" )
    .where( "active", 1 )
    .when( url.keyExists( "admin" ), function( q ) {
        q.where( "admin", 1 )
            .whereNotNull( "hireDate" );
    } );
SELECT *
FROM "users"
WHERE "active" = ?
    AND "admin" = ?
    AND "hireDate IS NOT NULL

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

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( function( q2 ) {
            q2.where( "username", "LIKE", q & "%" )
                .orWhere( "email", "LIKE", q & "%" );
        } );
    } );
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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

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

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

v7.0.0

Lucee 4.5 and Adobe ColdFusion 11 EOL

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

MSSQLGrammar renamed to SqlServerGrammar

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

Variadic Parameters Support Removed

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

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

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

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

defaultGrammar updated to be the full WireBox mapping

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

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

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

value method argument order changed

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

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

Some methods renamed callback to query

All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. This led to changing the callback argument to query in the following cases:

  • whereSub

  • whereInSub

  • whereExists

  • orWhereExists

  • whereNotExists

  • andWhereNotExists

  • orWhereNotExists

  • whereNullSub

  • orderBySub

  • subSelect

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

v5.0.0

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

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

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

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

as explained below.

Installation & Usage

Installation

Usage

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

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

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

The grammars provided by qb are:

  • MySQLGrammar

  • OracleGrammar

  • PostgresGrammar

  • SqlServerGrammar

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

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

SQL Type Inferral

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

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

Integrating With FW/1

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

Wiring Up With DI/1

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

First we will add a mapping in Application.cfc.

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

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

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

Usage In Your FW/1 Application

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

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

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

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.

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.

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

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

CommandBox
ForgeBox
this blog post
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`.
handlers/posts.cfc
component {

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

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

}
handlers/posts.cfc
component {

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

}
handlers/posts.cfc
component {

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

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

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

Contributing & Filing Issues

We welcome all types of contributions!

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

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

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

// MSSQLQueryBuilderSpec.cfc
function orWhere() {
    // If just a string is returned, we assume the bindings is an empty array ([])
    return {
        sql = "SELECT * FROM [users] WHERE [id] = ? OR [email] = ?",
        bindings = [ 1, "foo" ]
    };
}
// OracleSchemaBuilderSpec.cfc
function boolean() {
    // returns an array since schema builder can execute multiple statements.
    return [ "CREATE TABLE ""USERS"" (""ACTIVE"" NUMBER(1, 0) NOT NULL)" ];
}

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

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

What's New?

8.6.1

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

8.6.0

8.5.0

QueryBuilder

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

  • Correctly format a COUNT(DISTINCT column) query.

  • Only use bulk insert syntax when needed in OracleGrammar due to interactions between the result parameter to cfquery, Lucee, and the Oracle JDBC driver.

SchemaBuilder

8.4.9

  • Swap master branch to main branch.

8.4.8

  • Remove unnecessary injection for QueryUtils.

8.4.7

  • Account for raw expressions when generating mementos for comparison

8.4.6

8.4.5

8.4.1 - 8.4.4

  • Migrate release process to GitHub Actions.

8.4.0

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

8.3.0

8.2.2

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

8.2.1

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

8.2.0

8.1.0

8.0.3

  • Ignore select bindings for aggregate queries.

  • Allow spaces in table aliases.

  • Split FLOAT and DECIMAL column types in SQL Server.

8.0.2

  • Clear orderBy bindings when calling clearOrders.

8.0.1

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

8.0.0

BREAKING CHANGES

Other Changes

7.10.0

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

7.9.9

  • Fixes for OracleGrammar including table aliases and wrapped subqueries.

7.9.8

7.9.7

7.9.6

7.9.5

  • Handle enhanced numeric checks with Secure Profile enabled.

7.9.4

  • Allow raw statements in basic where clauses.

7.9.3

7.9.2

7.9.1

  • Handle multi-word columns in queryRemoveColumns.

7.9.0

  • Remove elvis operator due to ACF compatibility issues

7.8.0

7.7.3

7.7.2

  • Compatibility fix for ACF 2018 and listLast parsing.

  • Ignore table qualifiers for insert and update.

7.7.1

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

7.7.0

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

7.6.2

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

7.6.1

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

7.6.0

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

7.5.1

Fixed an issue using column formatters with update and insert.

7.5.0

Using a new preventDuplicateJoins setting in the module settings, qb can detect duplicate joins and ignore them. This is especially useful in a heavily filtered and dynamic query where you may or may not need the join at all or more than one column may need the same join. preventDuplicateJoins defaults to false, so it is opt-in. It may be turned on by default in a future breaking release of qb.

7.4.0

You can now use two shortcut methods: orderByAsc and orderByDesc. Additionally, orderBySub or using orderBy with a closure or builder instance will respect the direction argument.

7.3.15

7.3.14

7.3.13

7.3.12

7.3.9, 7.3.10, 7.3.11

7.3.8

7.3.7

7.3.5, 7.3.6

7.3.4

7.3.2, 7.3.3

7.3.1

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

7.3.0

  • Add a parameterLimit public property to SqlServerGrammar. This property is used in Quick to split up eager loading to work around the 2100 param limit of SQL Server.

7.2.0

7.1.0

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

7.0.0

BREAKING CHANGES

  • Drop support for Lucee 4.5 and Adobe ColdFusion 11.

  • MSSQLGrammar renamed to SqlServerGrammar

  • Remove variadic parameters support in builder functions like select.

  • The defaultGrammar mapping needs to be the full WireBox mapping, including the @qb, if needed.

    • For instance, MSSQLGrammar would become MSSQLGrammar@qb.

    • This will allow for other grammars to be more easily contributed via third party modules.

  • The argument names of forPage changed to match the new paginate method.

  • Add defaultValue and optional exception throwing to value. (This changed the argument order.)

  • All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery. (This changed the argument names in some instances.)

Other Changes

  • Completely revamped documentation! (You're looking at it right now.)

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

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

  • Add raw in alterTable segments.

  • Add dropAllObjects support for SqlServerGrammar and OracleGrammar to support migrate fresh from cfmigrations.

  • Add a renameTable alias for rename.

  • Remove default constraints when dropping columns with a default on SqlServerGrammar.

  • Add more column types and column helpers to SchemaBuilder, including:

    • datetimeTz

    • lineString

    • nullableTimestamps

    • point

    • polygon

    • softDeletes

    • softDeletesTz

    • timeTz

    • timestamps

    • timestampTz

    • timestampsTz

    • withCurrent

6.4.0

  • Allow Expressions (query.raw) in update statements.

Introduction

Introduction

Using qb, you can:

  • Quickly scaffold simple queries

  • Make complex, out-of-order queries possible

  • Abstract away differences between database engines

Requirements

  • Adobe ColdFusion 2016+

  • Lucee 5+

qb supports four major database grammars:

  • MySQL (MySQLGrammar)

  • Oracle (OracleGrammar)

  • Postgres (PostgresGrammar)

  • Microsoft SQL Server (SqlServerGrammar)

Discussion & Help

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

Installation

Code Samples

Compare these two examples:

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

// qb
query = wirebox.getInstance('QueryBuilder@qb');
q = query.from('users').get();

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

// Plain old CFML
q = 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
query = wirebox.getInstance('QueryBuilder@qb');
q = query.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:

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

// Becomes

q = 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!

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

Joins

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

join

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

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

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

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

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

joinWhere

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

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

joinRaw

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

joinSub

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

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

leftJoin

leftJoinRaw

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

leftJoinSub

rightJoin

rightJoinRaw

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

rightJoinSub

crossJoin

crossJoinRaw

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

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

crossJoinSub

newJoin

JoinClause

on

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

orOn

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

Preventing Duplicate Joins

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

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

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

Add a method to QueryBuilder.

Add helpers such as , , , and .

to an incoming query param when needed.

Add a shortcut method.

Add support for and .

Add support for & types for MySQLGrammar.

Fix limit on .

Introduce a setting to specify the default numeric SQL type.

📹

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

📹

now can accept bindings.

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

📹

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

Combine and orderBy with a new method.

Clear current selected columns with .

Combine and either or with and respectively.

Allow nullable in MySQL.

Return 0 on null .

Match type hints to documentation for functions

Passed along the options struct to the method when calling .

Allow for space-delimited directions like column DESC.

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

and now work with

Correctly format RETURNING clauses with and ignoring table qualifiers.

Add support for and data types to .

Fix wrapping of types for Postgres.

Include current_timestamp default for columns in SchemaBuilder.

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

now can take an array of expressions.

Enhance order by's with more direction options ()

Fix using whereBetween with query param structs ()

Ignore orders in aggregate queries ()

Format with cfformat ()

Improve column wrapping with trimming ()

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

Switch to using .

Allow passing query options in to paginate ()

Fix for inserting null values directly ()

Use cfformat for automatic formatting ()

Add a type to the onMissingMethod exception ()

Correctly wrap in MySQLGrammar.

Publish qb apidocs to .

Allow a to be set. A parent query will receive any method calls that are not found on the Query Builder instance. This is especially useful for instances like to allow Quick features like scopes to be available inside any closures.

Add an method.

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

Please see the for more information on these changes.

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

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

Allow raw values in calls.

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

Add a method.

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

to grab records from the database in small sets.

Master Branch Build Status
Development Branch Build Status

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

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

Here's a gist with an example of the powerful models you can create with this!

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

You can specify clauses in your joins as well.

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

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.

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.

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:

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

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

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

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

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.

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.

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.

default query options
Watch a walkthrough of this change on CFCasts.
Watch a walkthrough of these changes on CFCasts.
Watch a walkthrough of these changes on CFCasts.
aggregates
join
sort
column formatters
c767ac8
07c9b72
39e1338
dc2a9b6
d98a5cb
f9fd8d1
ForgeBox Storage
cdecfb3
1de27a6
119e434
90d1093
Ortus API Docs
parent query
Quick
Eloquent
Laravel
https://community.ortussolutions.com/c/box-modules/qb/27
CommandBox
ForgeBox
https://gist.github.com/elpete/80d641b98025f16059f6476561d88202
Migration Guide
QueryBuilder
query.from( "users" )
    .join( "posts", "users.id", "=", "posts.author_id" );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "users" )
    .join( "posts", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "users" )
    .join( query.raw( "posts (nolock)" ), "users.id", "=", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]
QueryBuilder
query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.on( "users.prefix", "=", "posts.prefix" );
    } );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `users`.`prefix` = `posts`.`prefix`
QueryBuilder
query.from( "users" )
    .join( "posts", function( j ) {
        j.on( "users.id", "=", "posts.author_id" );
        j.whereNotNull( "posts.published_date" );
    } );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  AND `posts`.`published_date` IS NOT NULL
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" );
    } );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON (
      `users`.`id` = `posts`.`author_id`
      OR `users`.`id` = `posts`.`reviewer_id`
  )
  AND `posts`.`published_date` IS NOT NULL
QueryBuilder
var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "users" )
    .joinWhere( "contacts", "contacts.balance", "<", 100 );
MySQL
SELECT *
FROM `users`
JOIN `contacts`
  WHERE `contacts`.`balance` < ?
QueryBuilder
query.from( "users" )
    .joinRaw( "posts (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
JOIN posts (nolock)
  ON [users].[id] = [posts].[author_id]
QueryBuilder
var sub = query.newQuery()
    .select( "id" )
    .from( "contacts" )
    .whereNotIn( "id", [ 1, 2, 3 ] );

query.from( "users as u" )
    .joinSub( "c", sub, "u.id", "=", "c.id" );
MySQL
SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`
QueryBuilder
query.from( "users as u" )
    .joinSub( "c", function ( q ) {
        q.select( "id" )
            .from( "contacts" )
            .whereNotIn( "id", [ 1, 2, 3 ] );
    }, "u.id", "=", "c.id" );
MySQL
SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `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" );
    } );
MySQL
SELECT *
FROM `users` AS `u`
JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`
  AND `u`.`type` = `c`.`type`

Name

Type

Required

Default

Description

operator

string

false

"="

The boolean operator for the join clause.

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

QueryBuilder
query.from( "posts" )
    .leftJoin( "users", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `posts`
LEFT JOIN `users`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "posts" )
    .leftJoinRaw( "users (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [posts]
LEFT JOIN users (nolock)
  ON [users].[id] = [posts].[author_id]
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" );
MySQL
SELECT *
FROM `users` AS `u`
LEFT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

Name

Type

Required

Default

Description

operator

string

false

"="

The boolean operator for the join clause.

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

QueryBuilder
query.from( "users" )
    .rightJoin( "posts", "users.id", "posts.author_id" );
MySQL
SELECT *
FROM `users`
RIGHT JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
query.from( "users" )
    .rightJoinRaw( "posts (nolock)", "users.id", "posts.author_id" );
SQL Server
SELECT *
FROM [users]
LEFT JOIN posts (nolock)
  ON [users].[id] = [posts].[author_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" );
MySQL
SELECT *
FROM `users` AS `u`
RIGHT JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
) AS `c`
  ON `u`.`id` = `c`.`id`

Name

Type

Required

Default

Description

QueryBuilder
query.from( "users" ).crossJoin( "posts" );
MySQL
SELECT *
FROM `users`
CROSS JOIN `posts`

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

QueryBuilder
query.from( "users" ).crossJoinRaw( "posts (nolock)" );
SQL Server
SELECT *
FROM [users]
CROSS JOIN posts (nolock)

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

​

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

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

query.from( "users as u" ).crossJoinSub( "c", sub );
MySQL
SELECT *
FROM `users` AS `u`
CROSS JOIN (
  SELECT `id`
  FROM `contacts`
  WHERE `id` NOT IN (?, ?, ?)
)
QueryBuilder
var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_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 );
MySQL
-- This is still an inner join because
-- the JoinClause is an inner join
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" );

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
QueryBuilder
var j = query.newJoin( "contacts" )
    .on( "users.id", "posts.author_id" )
    .orOn( "users.id", "posts.reviewer_id" );

query.from( "users" ).join( j );
MySQL
SELECT *
FROM `users`
JOIN `posts`
  ON `users`.`id` = `posts`.`author_id`
  OR `users`.`id` = `posts`.`reviewer_id`
moduleSettings = {
    "qb": {
         "preventDuplicateJoins": true  
    }
};

Common Table Expressions (i.e. CTEs)

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

CTEs come in two basic types:

  • Non-recursive — These are statements that do not reference themselves, in simplified terms they are like a derived table that can be referenced by a user-defined name.

  • Recursive — Recursive CTEs reference themselves and are generally used for creating hierarchical data—such as creating a parent/child relationship within a table.

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

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

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

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

  • withRecursive() — Allows you to define a recursive CTE.

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

with

Name

Type

Required

Default

Description

name

string

true

The name of the CTE.

input

QueryBuilder | Function

true

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

columns

Array<String>

false

[]

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

recursive

boolean

false

false

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

You can build a CTE using a function:

QueryBuilder
// qb
query.with( "UserCTE", function ( q ) {
        q
            .select( [ "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .from( "UserCTE" )
    .get();
MySQL
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
) SELECT * FROM `UserCTE`

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

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

query.with( "UserCTE", cte )
    .from( "UserCTE" )
    .get();
MySQL
WITH `UserCTE` AS (
    SELECT
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
)
SELECT * FROM `UserCTE`

A single query can reference multiple CTEs:

QueryBuilder
query.with( "UserCTE", function ( q ) {
        q.select( [ "id", "fName as firstName", "lName as lastName" ] )
            .from( "users" )
            .where( "disabled", 0 );
    } )
    .with( "BlogCTE", function ( q ) {
        q.from( "blogs" )
            .where( "disabled", 0 );
    } )
    .from( "BlogCTE as b" )
    .join( "UserCTE as u", "b.Creator", "u.id" )
    .get();
MySQL
WITH `UserCTE` AS (
    SELECT
        `id`,
        `fName` as `firstName`,
        `lName` as `lastName`
    FROM `users`
    WHERE `disabled` = 0
),
`BlogCTE` AS (
    SELECT *
    FROM `blogs`
    WHERE `disabled` = 0
)
SELECT *
FROM `BlogCTE` AS `b`
INNER JOIN `UserCTE` AS `u`
ON `b`.`Creator` = `u`.`id`

withRecursive

Name

Type

Required

Default

Description

name

string

true

The name of the CTE.

input

QueryBuilder | Function

true

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

columns

Array<String>

false

[]

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

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

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

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

[parent].[Generation] + 1
    FROM [Sample] AS [child]
    INNER JOIN [Hierarchy] AS [parent]
        ON [child].[ParentId] = [parent].[Id]
) SELECT * FROM [Hierarchy]
where
where
Expressions
joinRaw
JoinClause
join
join
leftJoinRaw
rightJoinRaw
join
join
joinSub
join
joinSub
joinSub
where
JoinClause
JoinClause
JoinClause
JoinClause
JoinClause
JoinClause
join
leftJoin
rightJoin
JoinClause
orderByRaw
clearOrders
reorder
clearOrders
orderByRaw
clearSelect
clearSelect
select
selectRaw
reselect
reselectRaw
selectRaw

Table of Contents

Name

Type

Required

Default

Description

table

true

​

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

false

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

​

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

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

type

string

false

"inner"

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

table

true

​

first

string | Expression | Function

false

second

false

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

​

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

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

table

true

​

first

false

second

false

Name

Type

Required

Default

Description

table

string

true

​

The raw SQL string to use as the table.

first

false

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

Name

Type

Required

Default

Description

alias

string

true

The alias for the derived table.

input

Function | QueryBuilder

true

​

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

first

true

operator

string

false

"="

The boolean operator for the join clause.

second

false

where

boolean

false

false

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use a closure to define the where clauses where possible.

table

true

​

Name

Type

Required

Default

Description

table

true

​

type

string

false

"inner"

The type of the join. Valid types are inner, left, right, or cross.

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

combinator

string

false

"and"

The boolean combinator for the clause (e.g. "and" or "or").

Name

Type

Required

Default

Description

first

false

operator

string

false

"="

The boolean operator for the condition.

second

false

Order By

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

Calling orderBy multiple times appends to the order list.

Order By (String)

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc".

QueryBuilder
query.from( "users" )
    .orderBy( "email" );
MySQL
SELECT *
FROM `users`
ORDER BY `email` ASC

Calling orderBy multiple times will append to the order list.

QueryBuilder
query.from( "users" )
    .orderBy( "email" )
    .orderBy( "username", "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC
QueryBuilder
query.from( "users" )
    .orderBy( query.raw( "DATE(created_at)" ) );
MySQL
SELECT *
FROM `users`
ORDER BY DATE(created_at)

Order By (List)

Name

Type

Required

Default

Description

column

any

true

The list of the columns to order by. Each column can optionally declare it's sort direction after a pipe delimiter. (e.g. `"height

desc"`).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column list that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( "email|asc,username", "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Strings)

Name

Type

Required

Default

Description

column

any

true

The array of the columns to order by. Each column can optionally declare it's sort direction after a pipe delimiter. (e.g. `"height

desc"`).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column array that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( [ "email|asc", "username" ], "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Array of Structs)

Name

Type

Required

Default

Description

column

any

true

The array of the columns to order by. Each column can optionally declare it's sort direction using a struct. The struct should have a column key and an optional direction key. (e.g. { column = "favorite_color", direction = "desc" }).

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc". This value will be used as the default value for all entries in the column array that fail to specify a direction for a specific column.

QueryBuilder
query.from( "users" )
    .orderBy( [
        { "column": "email", "direction": "asc" },
        "username"
    ], "desc" );
MySQL
SELECT *
FROM `users`
ORDER BY
  `email` ASC,
  `username` DESC

Order By (Subquery)

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

Ignored when using a Function or QueryBuilder instance.

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

QueryBuilder
query.from( "users" )
    .orderBy( function( q ) {
        q.selectRaw( "MAX(created_date)" )
            .from( "logins" )
            .whereColumn( "users.id", "logins.user_id" );
    } );
MySQL
SELECT *
FROM `users`
ORDER BY (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
)

Order By Raw

Name

Type

Required

Default

Description

expression

string

true

The raw SQL expression to use.

bindings

array

false

[]

Any bindings (?) used in the expression.

QueryBuilder
query.from( "users" )
    .orderByRaw( "CASE WHEN status = ? THEN 1 ELSE 0 END DESC", [ 1 ] );
MySQL
SELECT *
FROM `users`
ORDER BY CASE WHEN status = ? THEN 1 ELSE 0 END DESC

clearOrders

Name

Type

Required

Default

Description

No arguments

QueryBuilder
query.from( "users" )
    .orderBy( "email" )
    .clearOrders();
MySQL
SELECT *
FROM `users`

reorder

Name

Type

Required

Default

Description

column

any

true

direction

string

false

"asc"

The direction by which to order the query. Accepts "asc"or "desc".

QueryBuilder
query.from( "users" )
    .orderBy( "email" )
    .reorder( "username" );
MySQL
SELECT *
FROM `users`
ORDER BY `username` ASC

Selects

Specifying A Select Clause

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

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

select

Name

Type

Required

Default

Description

columns

string | array

false

​"*"

A single column, list of columns, or array of columns to retrieve.

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

QueryBuilder
query.select( [ "fname AS firstName", "age" ] ).from( "users" );
SQL (MySQL)
SELECT `fname` AS `firstName`, `age` FROM `users`

distinct

Name

Type

Required

Default

Description

state

boolean

false

​true

Value to set the distinct flag.

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

QueryBuilder
query.select( "username" ).distinct().from( "users" );
SQL (MySQL)
SELECT DISTINCT `username` FROM `users`

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

addSelect

Name

Type

Required

Default

Description

columns

string | array

true

​

A single column, list of columns, or array of columns to add to the select.

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

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

QueryBuilder
query.addSelect( [ "fname AS firstName", "age" ] ).from( "users" );
SQL (MySQL)
SELECT `fname` AS `firstName`, `age` FROM `users`

selectRaw

Name

Type

Required

Default

Description

expression

any

true

​

The raw expression for the select statement.

bindings

array

false

[]

Any bindings needed for the raw expression.

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

The expression is added to the other already selected columns.

QueryBuilder
query.selectRaw( "YEAR(birthdate) AS birth_year" ).from( "users" );
SQL (MySQL)
SELECT YEAR(birthdate) AS birth_year FROM `users`

subSelect

Name

Type

Required

Default

Description

alias

string

true

​

The alias for the subselect expression.

query

Function | QueryBuilder

true

The callback or query to use in the 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.

QueryBuilder
query.subSelect( "last_login_date", function( q ) {
    q.selectRaw( "MAX(created_date)" )
        .from( "logins" )
        .whereColumn( "users.id", "logins.user_id" );
} ) ).from( "users" );
SQL (MySQL)
SELECT (
    SELECT MAX(created_date)
    FROM `logins`
    WHERE `users`.`id` = `logins`.`user_id`
) AS `last_login_date`
FROM `users

clearSelect

Name

Type

Required

Default

Description

No arguments

``

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

QueryBuilder
query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .clearSelect();
SQL (MySQL)
SELECT * FROM `users`

reselect

Name

Type

Required

Default

Description

columns

string | array

false

​"*"

A single column, list of columns, or array of columns to retrieve.

QueryBuilder
query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselect( "username" );
SQL (MySQL)
SELECT `username` FROM `users`

reselectRaw

Name

Type

Required

Default

Description

expression

any

true

​

The raw expression for the select statement.

bindings

array

false

[]

Any bindings needed for the raw expression.

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.

QueryBuilder
query.from( "users" )
    .select( [ "fname AS firstName", "age" ] )
    .reselectRaw( "YEAR(birthdate) AS birth_year" );
SQL (MySQL)
SELECT YEAR(birthdate) AS birth_year FROM `users`

Group By and Having

groupBy

Name

Type

Required

Default

Description

groups

string | array

true

Passing a single string will group by that one column.

QueryBuilder
query.from( "users" )
    .groupBy( "country" );
MySQL
SELECT *
FROM `users`
GROUP BY `country`

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

QueryBuilder
query.from( "users" )
    .groupBy( "country,city" );
MySQL
SELECT *
FROM `users`
GROUP BY `country`, `city`

An array of column names can be provided.

QueryBuilder
query.from( "users" )
    .groupBy( [ "country", "city" ] );
MySQL
SELECT *
FROM `users`
GROUP BY `country`, `city`

Calling groupBy multiple times will to the current groups.

QueryBuilder
query.from( "users" )
    .groupBy( "country" )
    .groupBy( "city" );
MySQL
SELECT *
FROM `users`
GROUP BY `country`, `city`
QueryBuilder
query.from( "users" )
    .groupBy( query.raw( "DATE(created_at)" ) );
MySQL
SELECT *
FROM `users`
GROUP BY DATE(created_at)

having

Name

Type

Required

Default

Description

column

true

operator

any

false

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

value

any

false

combinator

string

false

"and"

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andHaving and orHaving methods instead.

Adds a having clause to a query.

QueryBuilder
query.from( "users" )
    .groupBy( "email" )
    .having( "email", ">", 1 );
MySQL
SELECT *
FROM `users`
GROUP BY `email`
HAVING `email` > ?

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

QueryBuilder
query.from( "users" )
    .groupBy( "email" )
    .having( query.raw( "COUNT(email)" ), ">", 1 );
MySQL
SELECT *
FROM `users`
GROUP BY `email`
HAVING COUNT(email) > ?

Unions

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.

IMPORTANT: The QueryBuilder instances passed to a union statement cannot contain a defined order. Any use of the orderBy() method on the unioned QueryBuilder instances will result in an OrderByNotAllowedexception. To order the results, add an orderBy() call to the parent source Query Builder instance.

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.

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:

Retrieving Results

get

The get method is the most common method used for retrieving results. It executes using the configured QueryBuilder and returns the results.

get can also take a list or array of columns to use as a shortcut. If any are passed, those columns will be used instead of any columns previously set on the QueryBuilder.

first

If you just need to retrieve a single row from the database table, you may use the first method. This method will return a single record (a Struct by default). If no row is found an empty Struct will be returned by default.

values

If you don't even need an entire row, you may extract a single value from each record using the values method. The values method will return the column of your choosing as a simple array.

value

This method is similar to values except it only returns a single, simple value. Where values calls get under the hood, this method calls first.

If no records are returned from the query, one of two things will happen. If the throwWhenNotFound boolean is set to true, a RecordCountException will be thrown. Otherwise the defaultValue provided to the method will be returned.

chunk

Large datasets can be broken up and retrieved in chunks. This allows you to work with a subset of results at once to keep your memory footprint under control.

chunk can be called on any query like you would call get. You can stop the retrieving and processing early by returning false from the callback.

paginate

Generates a pagination struct along with the results of the executed query. It does this by calling both count and forPage.

simplePaginate

Generates a simple pagination struct along with the results of the executed query. It does so without getting a count of the number of records the query would return. This can be desirable for performance reasons if your query count is rather large. It instead determines if there are more records by asking for one more row that your specified maxRows. If the number of rows returned exceeds your specified maxRows then the pagination returns hasMore: true. The results will always contain your specified maxRows (or less, if there aren't enough records).

Custom Pagination Collectors

generateWithResults

You can set your custom pagination collector either in the constructor using the paginationCollector argument or by calling setPaginationCollector on a query builder instance.

In qb 8.4.0 the simplePaginate method was added. This uses a new method on the paginationCollector.

generateSimpleWithResults

If you use a custom paginationCollector, ensure it has been updated with this new generateSimpleWithResults method before calling simplePaginate.

Locks

qb includes a few methods to help you lock certain rows when executing select statements.

Note: For locks to work properly, they must be nested inside a transaction. qb does not handle any of the transaction lifecycle for you.

sharedLock

A shared lock prevents the selected rows from being modified until your transaction is committed.

lockForUpdate

A lock for update lock prevents the selected rows from being modified or selected with another shared lock until your transaction is committed.

The main difference between a sharedLock and lockForUpdate is that a lockForUpdate prevents other reads or selects as well as updates.

noLock

noLock will instruct your grammar to ignore any shared locks when executing the query.

Currently this only makes a difference in SQL Server grammars.

lock

The lock method will allow you to add a custom lock directive to your query. Think of it as the raw method for lock directives.

These lock directives vary from grammar to grammar.

clearLock

Clears any lock directive on the query.

Aggregates

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.

count

Returns an integer number of rows returned by the query.

max

Returns the maximum value for the given column.

min

Returns the minimum value for the given column.

sum

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

Limit, Offset, and Pagination

limit

Sets the limit value for the query.

take

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

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.

Reset

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

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

You can insert a single record by passing a struct:

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!

returning

returning is only supported in PostgresGrammar and SqlServerGrammar. 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.

update

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

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 Lucee with full null support the following (easier) syntax is also allowed:

addUpdate

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.

delete

Deletes all records that the query returns.

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

Wheres

Where Methods

where

Adds a where clause to a query.

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

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

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

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

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

andWhere

orWhere

whereBetween

Adds a where between clause to the query.

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

whereNotBetween

whereColumn

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

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

Expressions can be passed in place of either column.

whereExists

Adds a where exists clause to the query.

It can be configured with a function.

It can also be configured with a QueryBuilder instance.

whereNotExists

whereLike

whereNotLike

whereIn

Adds a where in clause to the query.

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

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

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

Expressions can be freely mixed in with other values.

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

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

whereNotIn

whereRaw

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

whereNull

Adds a where null clause to the query.

whereNotNull

Dynamic Where Methods

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

andWhere... and orWhere...

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

where{Column}

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

Query Options

Default Options

qb allows you to specify default options when creating the QueryBuilder instance using the defaultOptions argument. You can 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.

locking
column formatters.
SchemaBuilder
Hyper

string | |

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

Sets if the value of second should be interpreted as a column or a value. Passing this as an argument is discouraged. Use the dedicated or a join closure where possible.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and with a join function where possible.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

The type of the join. Passing this as an argument is discouraged for readability. Use the dedicated methods like and where possible.

string | |

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a left join. The instance's join type will be used.)

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | |

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a right join. The instance's join type will be used.)

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | | Function

The first column or to join the table on. Alternatively, a function can be passed to configure complex join statements.

string |

The second column or to join the table on.

string | |

The name of the table or a object from which the query is based. Alternatively, a configured instance can be passed.

(Note: a instance may have a different join type than a cross join. The instance's join type will be used.)

string |

The name of the table or a object from which the query is based.

string | | Function

The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.

string |

The second column or of the condition.

string | | Function

The first column or of the condition. Alternatively, a function can be passed to nest conditions with parenthesis.

string |

The second column or of the condition.

The name of the column to order by. An can be passed as well.

You can also provide an .

The name of the column to order by. An can be passed as well. An array can be passed with any combination of simple values, array, struct, or list for each entry in the array (an example with all possible value styles: column = [ "last_name", [ "age", "desc" ], { column = "favorite_color", direction = "desc" }, "height|desc" ];. The column argument can also just accept a comman delimited list with a pipe ( | ) as the secondary delimiter denoting the direction of the order by. The pipe delimiter is also used when parsing the column argument when it is passed as an array and the entry in the array is a pipe delimited string.

Clears the currently configured orders for the query. Usually used by downstream libraries like .

The name of the column to order by. An can be passed as well.

Clears the currently configured orders for the query and sets the new orders passed in. Any valid argument to can be passed here. Usually used by downstream libraries like .

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

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.

A single column name, a list of column names, or an array of column names to group by. An can be passed as well.

An can be passed in place of a column.

string |

The name of the column or with which to constrain the query.

The value with which to constrain the column. An can be passed as well.

A pagination collector is the name given to the struct returned from calling the method. It can be a struct or a component. It needs one function defined and will be passed the following parameters.

By default, qb ships with as its pagination collector. The return format of cbpaginator is the example shown above.

Sets the limit value for the query. Alias for .

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.

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

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

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

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.

Adds values to a later , similar to .

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.

You may also use statements to simplify this further.

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

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

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.

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.

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

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

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

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.

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.

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

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.

Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Quick
orderBy
Quick
Raw Expressions
Expression
join
joinRaw
joinSub
joinWhere
leftJoin
leftJoinRaw
leftJoinSub
newJoin
rightJoin
rightJoinRaw
rightJoinSub
JoinClause
crossJoin
crossJoinRaw
crossJoinSub
Expression
JoinClause
Expression
JoinClause
leftJoin
rightJoin
joinWhere
leftJoin
rightJoin
leftJoinRaw
rightJoinRaw
leftJoinSub
rightJoinSub
Expression
JoinClause
Expression
JoinClause
JoinClause
JoinClause
Expression
JoinClause
Expression
JoinClause
JoinClause
JoinClause
Expression
JoinClause
Expression
JoinClause
JoinClause
JoinClause
select

Name

Type

Required

Default

Description

input

Function | QueryBuilder

true

The function or QueryBuilder instance to use as the unioned query.

all

boolean

false

false

Determines if statement should be a "UNION ALL". Passing this as an argument is discouraged. Use the dedicated unionAll where possible.

QueryBuilder
query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .union( function ( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
    } );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
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 );
    } );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
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 );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION
SELECT `name`
FROM `users`
WHERE `id` = ?

Name

Type

Required

Default

Description

input

Function | QueryBuilder

true

The function or QueryBuilder instance to use as the unioned query.

QueryBuilder
query.from( "users" )
    .select( "name" )
    .where( "id", 1 )
    .unionAll( function( q ) {
        q.from( "users" )
            .select( "name" )
            .where( "id", 2 );
     } );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
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 );
     } );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
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 );
MySQL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?
UNION ALL
SELECT `name`
FROM `users`
WHERE `id` = ?

Name

Type

Required

Default

Description

sql

string

true

The raw sql to wrap up in an Expression.

QueryBuilder
query.from( "users" ).select( query.raw( "MAX(created_date)" ) );
MySQL
SELECT MAX(created_date) FROM `users`

Name

Type

Required

Default

Description

columns

string | array

false

A shortcut parameter to retrieve only these columns overriding any columns previously set on the QueryBuilder.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).get();
SQL (MySQL)
SELECT * FROM `users`
QueryBuilder
query.from( "users" ).get( [ "id", "name" ] );
SQL (MySQL)
SELECT `id`, `name` FROM `users`

Name

Type

Required

Default

Description

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).first();
SQL (MySQL)
SELECT * FROM `users`
 LIMIT(1)

Name

Type

Required

Default

Description

column

string

true

The name of the column to retrieve.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).values( "firstName" );
Result
[ "jon", "jane", "jill", ... 
]

Name

Type

Required

Default

Description

column

string

true

The name of the column to retrieve.

defaultValue

string

false

(empty string)

The default value returned if there are no records returned for the query.

throwWhenNotFound

boolean

false

false

If true, it throws a RecordCountException if no records are returned from the query.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).value( "firstName" );
Result
"jon"

Name

Type

Default

Description

max

numeric

The number of results to return in each chunk.

callback

Function

The function that will be called with each chunk.

options

struct

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).chunk( 100, function( users ) {
    // Process the users here
    // Returning false from the callback stops processing
} );

Name

Type

Required

Default

Description

page

numeric

false

1

The page number to retrieve.

maxRows

numeric

false

25

The number of records per page. If a number less than 0 is passed, 0 is used instead.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" )
    .paginate();
Results
{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "totalPages": 2,
        "totalRecords": 45
    },
    "results": [ { /* ... */ }, ]
}

Name

Type

Required

Default

Description

page

numeric

false

1

The page number to retrieve.

maxRows

numeric

false

25

The number of records per page. If a number less than 0 is passed, 0 is used instead.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" )
    .simplePaginate();
Results
{
    "pagination": {
        "maxRows": 25,
        "offset": 0,
        "page": 1,
        "hasMore": true
    },
    "results": [ { /* ... */ }, ]
}

Name

Type

Description

totalRecords

numeric

The total records count.

results

any

The results of the query execution. It will be passed as whatever return format the user has defined.

page

numeric

The current page number.

maxRows

numeric

The maximum number of rows retrieved per page.

Name

Type

Description

results

any

The results of the query execution. It will be passed as whatever return format the user has defined.

page

numeric

The current page number.

maxRows

numeric

The maximum number of rows retrieved per page.

simplePaginate
value
values
value
values
paginate
PaginationCollector
Add a new chunk method

Name

Type

Required

Default

Description

No arguments

query.from( "users" )
    .where( "id", 1 )
    .sharedLock();
SELECT *
FROM `users`
WHERE `id` = ?
LOCK IN SHARE MODE
SELECT *
FROM [users] WITH (ROWLOCK,HOLDLOCK)
WHERE [id] = ?
SELECT *
FROM "users"
WHERE "id" = ?
FOR SHARE
LOCK TABLE "USERS"
IN SHARE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

Name

Type

Required

Default

Description

No arguments

query.from( "users" )
    .where( "id", 1 )
    .lockForUpdate();
SELECT *
FROM `users`
WHERE `id` = ?
FOR UPDATE
SELECT *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK)
WHERE [id] = ?
SELECT *
FROM "users"
WHERE "id" = ?
FOR UPDATE
LOCK TABLE "USERS"
IN ROW EXCLUSIVE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

Name

Type

Required

Default

Description

No arguments

query.from( "users" )
    .where( "id", 1 )
    .noLock();
SELECT *
FROM [users] WITH (NOLOCK)
WHERE [id] = ?

Name

Type

Required

Default

Description

value

string

true

The custom lock directive to add to the query.

Name

Type

Required

Default

Description

No arguments

lock
noLock
lockForUpdate
sharedLock

Name

Type

Required

Default

Description

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).where( "username", "like", "jon%" ).exists();
SQL (MySQL)
SELECT COUNT(*) AS aggregate 

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

Name

Type

Required

Default

Description

column

string

false

"*"

The column on which to count records.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).count();
SELECT COUNT(*) AS aggregate 

FROM `users`
SELECT COUNT(*) FROM [users]

Name

Type

Required

Default

Description

column

string

true

The column on which to find the max.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).max( "age" );
SQL (MySQL)
SELECT MAX(age) AS aggregate FROM `users`

Name

Type

Required

Default

Description

column

string

true

The column on which to find the min.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "users" ).min( "age" );
SQL (MySQL)
SELECT MIN(age) AS aggregate FROM `users`

Name

Type

Required

Default

Description

column

string

true

The column to sum.

options

struct

false

{}

Any additional queryExecute options.

QueryBuilder
query.from( "employees" ).sum( "salary" );
SQL (MySQL)
SELECT SUM(salary) AS aggregate FROM `employees`

Name

Type

Required

Default

Description

value

numeric

true

The limit value for the query.

QueryBuilder
query.from( "users" )
    .limit( 5 );
MySQL
SELECT *
FROM `users`
LIMIT 5

Name

Type

Required

Default

Description

value

numeric

true

The limit value for the query.

QueryBuilder
query.from( "users" )
    .take( 5 );
MySQL
SELECT *
FROM `users`
LIMIT 5

Name

Type

Required

Default

Description

value

numeric

true

The offset value for the query.

QueryBuilder
query.from( "users" )
    .offset( 25 );
MySQL
SELECT *
FROM `users`
OFFSET 25

Name

Type

Required

Default

Description

page

numeric

true

The page number to retrieve.

maxRows

numeric

true

The number of records per page. If a number less than 0 is passed, 0 is used instead.

QueryBuilder
query.from( "users" )
    .forPage( 3, 15 );
MySQL
SELECT *
FROM `users`
LIMIT 15
OFFSET 30
count
paginate
QueryBuilder
var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.clone();
q2.getFrom(); // "users"
QueryBuilder
var q1 = query.from( "users" ).where( "firstName", "like", "Jo%" );
var q2 = q1.reset();
q2.getColumns(); // "*"
reset
{
    "result": "Value of the `result` parameter to `queryExecute`",
    "query": "Return value of running `queryExecute` - a CFML query object"
}

Name

Type

Required

Default

Description

values

struct | array<struct>

true

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

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

false

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

QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = 55
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)
QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)
QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "updatedDate" = query.raw( "NOW()" )
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `updatedDate`)
VALUES (?, ?, NOW())
QueryBuilder
query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "jane@example.com", "name" = "Jane Doe" }
] );
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)
INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

Name

Type

Required

Default

Description

columns

string | array

true

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

QueryBuilder
query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );
INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)
INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"
QueryBuilder
query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?
QueryBuilder
query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?
QueryBuilder
query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `Id` = ?
QueryBuilder
query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );
MySQL
UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?
query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )
query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

Name

Type

Required

Default

Description

values

struct

true

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

QueryBuilder
query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();
MySQL
UPDATE `users`
SET `email` = ?,
    `foo` = ?,
    `name` = ?
WHERE `Id` = ?

Name

Type

Required

Default

Description

values

struct

true

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

options

boolean

false

{}

Any additional queryExecute options.

toSql

boolean

false

false

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

QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `email` = ?
LIMIT 1
QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );
MySQL
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?)
QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .delete();
MySQL
DELETE FROM `users`
WHERE `email` = ?
QueryBuilder
query.from( "users" )
    .delete( 1 );
MySQL
DELETE FROM `users`
WHERE `id` = ?
insert
addUpdate
QueryBuilder
query.from( "users" )
    .where( "active", "=", 1 );
MySQL
SELECT *
FROM `users`
WHERE `active` = ?
QueryBuilder
query.from( "users" )
    .where( "last_logged_in", ">", query.raw( "NOW()" ) );
MySQL
SELECT *
FROM `users`
WHERE `last_logged_in` > NOW()

Valid Operators

=

<

>

<=

>=

<>

!=

like

like binary

not like

between

ilike

&

|

^

<<

>>

rlike

regexp

not regexp

~

~*

!~

!~*

similar to

not similar to

QueryBuilder
query.from( "users" )
    .where( "active", 1 );
MySQL
SELECT *
FROM `users`
WHERE `active` = ?
QueryBuilder
query.from( "users" )
    .where( function( q ) {
        q.where( "active", 1 )
            .where( "last_logged_in", ">", dateAdd( "ww", -1, now() ) )
    } );
MySQL
SELECT *
FROM `users`
WHERE (
    `active` = ?
    AND
    `last_logged_in` > ?
)
QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .orWhere( "id", "=", function( q ) {
        q.select( q.raw( "MAX(id)" ) )
            .from( "users" )
            .where( "email", "bar" );
    } );
MySQL
SELECT *
FROM `users`
WHERE `email` = ?
  OR `id` = (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
  )
QueryBuilder
query.from( "users" )
    .whereBetween( "id", 1, 2 );
MySQL
SELECT *
FROM `users`
WHERE `id` BETWEEN ? AND ?
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" )
    );
MySQL
SELECT *
FROM `users`
WHERE `id` BETWEEN (
    SELECT MIN(id)
    FROM `users`
    WHERE `email` = ?
)
AND (
    SELECT MAX(id)
    FROM `users`
    WHERE `email` = ?
)
QueryBuilder
query.from( "users" )
    .whereColumn( "first_name", "=", "last_name" );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
QueryBuilder
query.from( "users" )
    .whereColumn( "first_name", "last_name" );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = `last_name`
QueryBuilder
query.from( "users" )
    .whereColumn( "first_name", query.raw( "LOWER(first_name)" ) );
MySQL
SELECT *
FROM `users`
WHERE `first_name` = LOWER(first_name)
QueryBuilder
query.from( "orders" )
    .whereExists( function( q ) {
        q.select( q.raw( 1 ) )
            .from( "products" )
            .whereColumn( "products.id", "orders.id" );
    } );
MySQL
SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `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 );
MySQL
SELECT *
FROM `orders`
WHERE EXISTS (
    SELECT 1
    FROM `products`
    WHERE `products`.`id` = `orders`.`id`
)
QueryBuilder
query.from( "users" )
    .whereLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
QueryBuilder
query.from( "users" )
    .whereNotLike( "username", "J%" );
MySQL
SELECT *
FROM `users`
WHERE `username` NOT LIKE ?
QueryBuilder
query.from( "orders" )
    .whereIn( "id", [ 1, 4, 66 ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
QueryBuilder
query.from( "orders" )
    .whereIn( "id", "1,4,66" );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
QueryBuilder
query.from( "orders" )
    .whereIn( "id", [ 1, 4, { value = "66", cfsqltype = "CF_SQL_VARCHAR" } ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (?, ?, ?)
QueryBuilder
query.from( "orders" )
    .whereIn( "id", [ query.raw( "MAX(id)" ), 4, 66 ] );
MySQL
SELECT *
FROM `orders`
WHERE `id` IN (MAX(id), ?, ?)
QueryBuilder
query.from( "users" )
    .whereIn( "id", function( q ) {
        q.select( "id" )
            .from( "users" )
            .where( "age", ">", 25 );
    } );
MySQL
SELECT *
FROM `users`
WHERE IN (
    SELECT `id`
    FROM `users`
    WHERE `age` > ?
)
QueryBuilder
query.from( "users" )
    .whereRaw(
        "id = ? OR email = ? OR is_admin = 1",
        [ 1, "foo" ]
    );
MySQL
SELECT *
FROM `users`
WHERE id = ? OR email = ? OR is_admin = 1
QueryBuilder
query.from( "users" )
    .whereNull( "id" );
MySQL
SELECT *
FROM `users`
WHERE `id` IS NULL
QueryBuilder
query.from( "users" )
    .where( "username", "like", "j%" )
    .andWhere( function( q ) {
        q.where( "isSubscribed", 1 )
            .orWhere( "isOnFreeTrial", 1 );
     } );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND (
    `isSubscribed` = ?
    OR
    `isOnFreeTrial` = ?
  )
QueryBuilder
query.from( "users" )
    .whereUsername( "like", "j%" )
    .whereActive( 1 );
MySQL
SELECT *
FROM `users`
WHERE `username` LIKE ?
  AND `active` = ?
whereNotLike
whereLike
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" 
            } );
    }

}
QueryBuilder
query.from( "users" )
    .get( options = { datasource: "MyOtherDatasourceName" } );
QueryBuilder
var query = wirebox.getInstance( "QueryBuilder@qb" )
    .setGrammar( wirebox.getInstance( "MSSQLGrammar@qb" ) );
default queryExecute options

When / Conditionals

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

QueryBuilder
var q = query.from( "posts" );
if ( someFlag ) {
    q.orderBy( "published_date", "desc" );
}

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

when

Name

Type

Required

Default

Description

condition

boolean

true

The condition to switch on.

onTrue

Function

true

The callback to execute if the condition is true. It is passed the builder object as the only parameter.

onFalse

Function

false

function( q ) { return q; }

The callback to execute if the conditions is false. It is passed the builder object as the only parameter.

withoutScoping

boolean

false

false

Flag to turn off the automatic scoping of where clauses during the callback.

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

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

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

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

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.

qb.from( "users" )
    .where( "active", 1 )
    .when( len( url.q ), function( q ) {
        q.where( "username", "LIKE", q & "%" )
            .orWhere( "email", "LIKE", q & "%" );   
    } );
SELECT *
FROM "users"
WHERE "active" = ?
    AND (
        "username" = ?
        OR "email" = ?
    )

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"

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

config/ColdBox.cfc
moduleSettings = {
    "qb": {
        "returnFormat": "array"
    }
};

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

config/ColdBox.cfc
moduleSettings = {
    "qb": {
        "returnFormat": "query"
    }
};

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

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.

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

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

From

from

Name

Type

Required

Default

Description

from

string | Expression

true

​

The name of the table or a Expression object from which the query is based.

Used to set the base table for the query.

QueryBuilder
query.from( "users" );
MySQL
SELECT * FROM `users`

You can optionally specify an alias for the table.

QueryBuilder
query.from( "users as u" );
MySQL
SELECT * FROM `users` AS `u`

table

Name

Type

Required

Default

Description

table

string | Expression

true

​

The name of the table or a Expression object from which the query is based.

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

QueryBuilder
query.table( "users" ).insert( { "name" = "jon" } );
MySQL
INSERT INTO `users` (`name`) VALUES (?)

fromRaw

Name

Type

Required

Default

Description

from

string

true

​

The sql snippet to use as the table.

bindings

array

false

[]

Any bindings needed for the expression.

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.

QueryBuilder
query.fromRaw( "[users] u (nolock)" ).get();
SQL Server
SELECT * FROM [users] u (nolock) 

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

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

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

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

QueryBuilder
query.fromRaw(
    "dbo.generateDateTable(?, ?, ?) as dt",
    [ "2017-01-01", "2017-12-31", "m" ]
).get();
SQL Server
SELECT * FROM dbo.generateDateTable(?, ?, ?) as dt

fromSub

Name

Type

Required

Default

Description

alias

string

true

​

The alias for the derived table.

input

Function | QueryBuilder

true

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

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

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()
MySQL
SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`

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

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();
MySQL
SELECT `firstName`, `lastName`
FROM (
    SELECT `lName` as `lastName`, `fName` as `firstName`
    FROM `users`
    WHERE `age` >= 21
) AS `legalUsers`
ORDER BY `lastName`
Expression
Expression
Expression
Expression
Expression
Expression
Expression
cbpaginator
WHERE
Expression
queryExecute
afterAspectsLoad
selectRaw
joinRaw
leftJoinRaw
rightJoinRaw
crossJoinRaw
whereRaw
whereColumn
dynamic where{Column}
where
where
whereBetween
whereBetween
whereExists
whereExists
where
where
whereIn
whereNull
whereNull
paginate
Retreiving Results
limit
addSelect
update
when
when
when
dump
toSQL( showBindings = true )
mediumtext
longtext
timestamps
MONEY
SMALLMONEY
enum
timestamp
Automatically add a scale
numericSQLType
strictDateDetection
cfqueryparam
query param
cfqueryparam
query param
custom parameter type
from
fromSub
fromRaw
from
table
from
table

Name

Type

Required

Default

Description

values

struct

false

{}

options

struct

false

{}

Any additional queryExecute options.

toSQL

boolean

false

false

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

Name

Type

Required

Default

Description

id

any

false

idColumn

string

false

"id"

The name of the id column for the delete shorthand.

options

boolean

false

{}

Any additional queryExecute options.

toSql

boolean

false

false

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

Table of Contents

Name

Type

Required

Default

Description

column

true

operator

false

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

value

any

false

combinator

string

false

"and"

Name

Type

Required

Default

Description

column

true

operator

false

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

value

any

false

Name

Type

Required

Default

Description

column

true

operator

false

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

value

any

false

Name

Type

Required

Default

Description

column

string | Expression

true

start

any | Function | QueryBuilder

true

The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.

end

any | Function | QueryBuilder

true

The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.

combinator

string

false

"and"

negate

boolean

false

false

False for BETWEEN, True for NOT BETWEEN.

Name

Type

Required

Default

Description

column

string | Expression

true

start

any | Function | QueryBuilder

true

The beginning value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.

end

any | Function | QueryBuilder

true

The end value of the BETWEEN statement. If a function or QueryBuilder is passed it is used as a subselect expression.

combinator

string

false

"and"

Name

Type

Required

Default

Description

first

true

operator

true

The operator to use for the constraint (i.e. "=", "<", ">=", etc.). A value can be passed as the operator and the value left null as a shortcut for equals (e.g. where( "column", 1 ) == where( "column", "=", 1 ) ).

second

string | Expression

false

combinator

string

false

"and"

Name

Type

Required

Default

Description

query

Function | QueryBuilder

true

A function or QueryBuilder instance to be used as the exists subquery.

combinator

string

false

"and"

negate

boolean

false

false

False for EXISTS, True for NOT EXISTS.

Name

Type

Required

Default

Description

query

Function | QueryBuilder

true

A function or QueryBuilder instance to be used as the not exists subquery.

combinator

string

false

"and"

Name

Type

Required

Default

Description

column

true

value

any

false

combinator

string

false

"and"

Name

Type

Required

Default

Description

column

true

value

any

false

combinator

string

false

"and"

Name

Type

Required

Default

Description

column

string | Expression

true

values

true

combinator

string

false

"and"

negate

boolean

false

false

False for IN, True for NOT IN.

Name

Type

Required

Default

Description

column

string | Expression

true

values

true

combinator

string

false

"and"

Name

Type

Required

Default

Description

sql

string

true

The raw SQL to add to the query.

whereBindings

array

false

[]

combinator

string

false

"and"

Name

Type

Required

Default

Description

column

string | Expression

true

combinator

string

false

"and"

negate

boolean

false

false

False for NULL, True for NOT NULL.

Name

Type

Required

Default

Description

column

string | Expression

true

combinator

string

false

"and"

negate

boolean

false

false

False for NULL, True for NOT NULL.

Debugging

Debugging a Single Query

toSQL

Name

Type

Required

Default

Description

showBindings

boolean

false

​false

If true, the bindings for the query will be substituted back in where the question marks (?) appear.

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

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

writeOutput( q.toSQL() );
Result
SELECT * FROM "users" WHERE "active" = ?

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

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

writeOutput( q.toSQL( showBindings = true ) );
Result
SELECT * FROM "users" WHERE "active" = {"value":1,"cfsqltype":"CF_SQL_NUMERIC","null":false}

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

tap

Name

Type

Required

Default

Description

callback

Function

true

​

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

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

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

QueryBuilder
query.from( "users" )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } )
    .where( "active", "=", 1 )
    .tap( function( q ) {
        writeOutput( q.toSQL() & "<br>" );
    } );
Result
SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

dump

Name

Type

Required

Default

Description

showBindings

boolean

false

false

If true, the bindings for the query will be substituted back in where the question marks (?) appear.

All other writeDump arguments are supported.

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

QueryBuilder
query.from( "users" )
    .dump()
    .where( "active", "=", 1 )
    .dump( label = "after where", showBindings = true, abort = true )
    .get();
Result
SELECT * FROM "users"
SELECT * FROM "users" WHERE "active" = ?

Debugging All Queries

cbDebugger

LogBox Appender

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

config/ColdBox.cfc
logbox = {
    debug = [ "qb.models.Grammars" ]
};

ColdBox Interception Points

Overview

QB ships with a schema builder to help you build your database objects. This provides a few benefits:

  • The syntax is expressive and fluent, making it easy to understand what is being executed

  • The syntax is database-agnostic. Specific quirks are isolated in a Grammar file, making it easy to migrate between engines.

You start with a SchemaBuilder object. The SchemaBuilder takes the same Grammar that a QueryBuilder takes. It can additionally take a struct of default query options forwarded on to queryExecute.

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

Note: the SchemaBuilder is a transient, and a new one should be created for each operation.

The SchemaBuilder has four main methods to start your database object creation:

Create a new table in the database.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to create.

callback

function

true

A callback function used to define the table body. It is passed a Blueprint as the only argument.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

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

SQL (MySQL)

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

Alter an existing table in the database.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to alter.

callback

function

true

A callback function used to define the changes to the table. It is passed a Blueprint as the only argument.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
    table.dropColumn( "last_logged_in" );
} );

SQL (MySQL)

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
ALTER TABLE `users` DROP COLUMN `last_logged_in`;

Drop a table from the database.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to drop.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

Additionally, there are a few utility methods defined on SchemaBuilder as well:

rename

Rename a table from an old name to a new name

Argument

Type

Required

Default

Description

from

string

true

The old table name.

to

string

true

The new table name.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.rename( "posts", "blog_posts" );

SQL (MySQL)

RENAME TABLE `posts` TO `blog_posts`

hasTable

Check if a table exists in the database.

Argument

Type

Required

Default

Description

name

string

true

The name of the table to check.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.hasTable( "users" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`tables`
WHERE `table_name` = 'users'

hasColumn

Check if a column exists in a table in the database.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to check for the column in.

column

string

true

The column to check for in the table.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.hasColumn( "users", "last_logged_in" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
    AND `column_name` = 'last_logged_in'

Create

This method allows you to create a table object.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to create.

callback

function

true

A callback function used to define the table body. It is passed a Blueprint as the only argument.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

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

This would convert to the following SQL in MySQL:

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

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

Columns

bigIncrements

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

indexName

string

false

The name for the primary key index. If no name is passed in, the name will be dynamically created based off of the table name and column name.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigIncrements( "id" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

bigInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigInteger( "salary" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `salary` BIGINT NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bigInteger( "salary", 5 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `salary` BIGINT(5) NOT NULL
)

bit

Create a column using a BIT equivalent type for your database. The length can be specified as the second argument.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

1

The length for the column.

Example (default length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bit( "is_active" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_active` BIT(1) NOT NULL
)

Example (custom length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.bit( "is_active", 2 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_active` BIT(2) NOT NULL
)

boolean

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.boolean( "is_subscribed" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `is_subscribed` TINYINT(1) NOT NULL
)

char

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

1

The length for the column.

Example (default length):

SchemaBuilder

schema.create( "students", function( table ) {
    table.char( "grade" );
} );

SQL (MySQL)

CREATE TABLE `students` (
    `grade` CHAR(1) NOT NULL
)

Example (custom length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.char( "tshirt_size", 4 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `tshirt_size` CHAR(4) NOT NULL
)

date

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.date( "birthday" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `birthday` DATE NOT NULL
)

datetime

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.datetime( "hire_date" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `hire_date` DATETIME NOT NULL
)

datetimeTz

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

Some databases do not have the concept of a timezone-specific datetime. Those databases will use a normal DATETIME type.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.datetimeTz( "posted_date" );
} );

SQL (SQL Server)

CREATE TABLE [posts] (
    [posted_date] DATETIMEOFFSET NOT NULL
)

decimal

Create a column using a DECIMAL equivalent type for your database. The length and precision can be specified as the second and third arguments.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

10

The length of the column.

precision

numeric

false

0

The precision of the column.

Example (with defaults):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.decimal( "temperature" );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` DECIMAL(10,0) NOT NULL
)

Example (with length):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.decimal( "temperature", 4 );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` DECIMAL(4,0) NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.decimal( name = "temperature", precision = 2 );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` DECIMAL(10,2) NOT NULL
)

enum

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.enum( "tshirt_size", [ "S", "M", "L", "XL", "XXL" ] );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `tshirt_size` ENUM(`S`, `M`, `L`, `XL`, `XXL`) NOT NULL
)

float

Create a column using a FLOAT equivalent type for your database. The length and precision can be specified as the second and third arguments.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

10

The length of the column.

precision

numeric

false

0

The precision of the column.

Example (with defaults):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.float( "temperature" );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` FLOAT(10,0) NOT NULL
)

Example (with length):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.float( "temperature", 4 );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` FLOAT(4,0) NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "weather", function( table ) {
    table.float( name = "temperature", precision = 2 );
} );

SQL (MySQL)

CREATE TABLE `weather` (
    `temperature` FLOAT(10,2) NOT NULL
)

increments

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

indexName

string

false

The name for the primary key index. If no name is passed in, the name will be dynamically created based off of the table name and column name.

Example:

SchemaBuilder

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

SQL (MySQL)

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

integer

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.integer( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` INTEGER NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.integer( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` INTEGER(3) NOT NULL
)

json

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.json( "options" ).nullable();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `options` JSON
)

lineString

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.lineString( "positions" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `positions` LINESTRING NOT NULL
)

longText

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.longText( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` LONGTEXT NOT NULL
)

mediumIncrements

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

indexName

string

false

The name for the primary key index. If no name is passed in, the name will be dynamically created based off of the table name and column name.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.mediumIncrements( "id" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

mediumInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

10

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.mediumInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` MEDIUMINT NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.mediumInteger( "score", 5 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` MEDIUMINT(5) NOT NULL
)

mediumText

Create a column using a MEDIUMTEXT equivalent type for your database. For databases that distinguish between unicode and non-unicode fields, creates a non-unicode field.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.mediumText( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` MEDIUMTEXT NOT NULL
)

SQL (MSSQL)

CREATE TABLE `posts` (
    `body` VARCHAR(MAX) NOT NULL
)

money

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "transactions", function( table ) {
    table.money( "amount" );
} );

SQL (MySQL)

CREATE TABLE `transactions` (
    `amount` INTEGER NOT NULL
)

SQL (MSSQL)

CREATE TABLE [transactions] (
    [amount] MONEY NOT NULL
)

morphs

Creates the necessary columns for a polymorphic relationship. It takes the name provided and creates an _id and an _type column.

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

Argument

Type

Required

Default

Description

name

string

true

The prefix for the polymorphic columns.

Example:

SchemaBuilder

schema.create( "tags", function( table ) {
    table.morphs( "taggable" );
} );

SQL (MySQL)

CREATE TABLE `tags` (
    `taggable_id` INTEGER UNSIGNED NOT NULL,
    `taggable_type` VARCHAR(255) NOT NULL,
    INDEX `taggable_index` (`taggable_id`, `taggable_type`)
)

nullableMorphs

Creates the necessary columns for a polymorphic relationship. It takes the name provided and creates an _id and an _type column. The only difference between this method and morphs is that the columns created here are nullable.

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

Argument

Type

Required

Default

Description

name

string

true

The prefix for the polymorphic columns.

Example:

SchemaBuilder

schema.create( "tags", function( table ) {
    table.nullableMorphs( "taggable" );
} );

SQL (MySQL)

CREATE TABLE `tags` (
    `taggable_id` INTEGER UNSIGNED,
    `taggable_type` VARCHAR(255),
    INDEX `taggable_index` (`taggable_id`, `taggable_type`)
)

nullableTimestamps

Creates the createdDate and modifiedDate TIMESTAMP columns. It creates the columns as nullable.

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

Argument

Type

Required

Default

Description

No arguments

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.nullableTimestamps();
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `createdDate` TIMESTAMP,
    `modifiedDate` TIMESTAMP
)

point

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.point( "position" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `position` POINT NOT NULL
)

polygon

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.polygon( "positions" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `positions` POLYGON NOT NULL
)

raw

An escape hatch to directly insert any sql in to the statement.

Argument

Type

Required

Default

Description

sql

string

true

The sql to insert directly into the statement.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.raw( "`profile_image` BLOB NOT NULL" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `profile_image` BLOB NOT NULL
)

smallIncrements

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

indexName

string

false

The name for the primary key index. If no name is passed in, the name will be dynamically created based off of the table name and column name.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.smallIncrements( "id" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

smallInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.smallInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` SMALLINT NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.smallInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` SMALLINT(3) NOT NULL
)

smallMoney

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "transactions", function( table ) {
    table.smallMoney( "amount" );
} );

SQL (MySQL)

CREATE TABLE `transactions` (
    `amount` INTEGER NOT NULL
)

SQL (MSSQL)

CREATE TABLE [transactions] (
    [amount] SMALLMONEY NOT NULL
)

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.

Argument

Type

Required

Default

Description

No arguments

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.softDeletes();
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `deletedDate` TIMESTAMP
)

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.

Argument

Type

Required

Default

Description

No arguments

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.softDeletesTz();
} );

SQL (SQL Server)

CREATE TABLE [posts] (
    [deletedDate] DATETIMEOFFSET
)

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.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

255

The length of the column.

Example (with defaults):

SchemaBuilder

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

SQL (MySQL)

CREATE TABLE `users` (
    `username` VARCHAR(255) NOT NULL
)

Example (with length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.string( "username", 50 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `username` VARCHAR(50) NOT NULL
)

text

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.text( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` TEXT NOT NULL
)

time

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "recurring_tasks", function( table ) {
    table.time( "fire_time" );
} );

SQL (Postgres)

CREATE TABLE "recurring_tasks" (
    "fire_time" TIME NOT NULL
)

timeTz

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "recurring_tasks", function( table ) {
    table.timeTz( "fire_time" );
} );

SQL (Postgres)

CREATE TABLE "recurring_tasks" (
    "fire_time" TIME WITH TIME ZONE NOT NULL
)

timestamp

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.timestamp( "created_at" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `created_at` TIMESTAMP NOT NULL
)

timestamps

Creates the createdDate and modifiedDate TIMESTAMP columns.

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

Argument

Type

Required

Default

Description

No arguments

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.timestamps();
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `createdDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

timestampTz

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.timestampTz( "posted_date" );
} );

SQL (Postgres)

CREATE TABLE "posts" (
    "posted_date" TIMESTAMP WITH TIME ZONE NOT NULL
)

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.

Argument

Type

Required

Default

Description

No arguments

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.timestampsTz();
} );

SQL (Postgres)

CREATE TABLE "posts" (
    "createdDate" TIMESTAMP WITH TIME ZONE NOT NULL,
    "modifiedDate" TIMESTAMP WITH TIME ZONE NOT NULL
)

tinyIncrements

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

indexName

string

false

The name for the primary key index. If no name is passed in, the name will be dynamically created based off of the table name and column name.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.tinyIncrements( "id" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

tinyInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.tinyInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` TINYINT NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.tinyInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` TINYINT(3) NOT NULL
)

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.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.longText( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` LONGTEXT NOT NULL
)

SQL (MSSQL)

CREATE TABLE [posts] (
    [body] NVARCHAR(MAX) NOT NULL
)

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.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.unicodeMediumText( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` MEDIUMTEXT NOT NULL
)

SQL (MSSQL)

CREATE TABLE [posts] (
    [body] NVARCHAR(MAX) NOT NULL
)

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.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

length

numeric

false

255

The length of the column.

Example (with defaults):

SchemaBuilder

schema.create( "users", function( table ) {
    table.unicodeString( "username" );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `username` VARCHAR(255) NOT NULL
)

SQL (MSSQL)

CREATE TABLE [users] (
    [username] NVARCHAR(255) NOT NULL
)

Example (with length):

SchemaBuilder

schema.create( "users", function( table ) {
    table.unicodeString( "username", 50 );
} );

SQL (MySQL)

CREATE TABLE `users` (
    `username` VARCHAR(50) NOT NULL
)

SQL (MSSQL)

CREATE TABLE [users] (
    [username] NVARCHAR(50) NOT NULL
)

unicodeText

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "posts", function( table ) {
    table.unicodeText( "body" );
} );

SQL (MySQL)

CREATE TABLE `posts` (
    `body` TEXT NOT NULL
)

SQL (MSSQL)

CREATE TABLE [posts] (
    [body] NVARCHAR(MAX) NOT NULL
)

unsignedBigInteger

Create a column using a UNSIGNED BIGINT equivalent type for your database.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedBigInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` BIGINT UNSIGNED NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedBigInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` BIGINT(3) UNSIGNED NOT NULL
)

unsignedInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` INTEGER UNSIGNED NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` INTEGER(3) UNSIGNED NOT NULL
)

unsignedMediumInteger

Create a column using a UNSIGNED MEDIUMINT equivalent type for your database.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedMediumInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` MEDIUMINT UNSIGNED NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedMediumInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` MEDIUMINT(3) UNSIGNED NOT NULL
)

unsignedSmallInteger

Create a column using a UNSIGNED SMALLINT equivalent type for your database.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedSmallInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` SMALLINT UNSIGNED NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedSmallInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` SMALLINT(3) UNSIGNED NOT NULL
)

unsignedTinyInteger

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

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

precision

numeric

false

The precision for the column.

Example (no precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedTinyInteger( "score" );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` TINYINT UNSIGNED NOT NULL
)

Example (with precision):

SchemaBuilder

schema.create( "games", function( table ) {
    table.unsignedTinyInteger( "score", 3 );
} );

SQL (MySQL)

CREATE TABLE `games` (
    `score` TINYINT(3) UNSIGNED NOT NULL
)

uuid

SQL Server: Create a column using a uniqueidentifier.

MySQL and Others: Create a column using a CHAR equivalent type for your database and a length of 36. Used in conjunction with the CFML createUUID method.

Argument

Type

Required

Default

Description

name

string

true

The name for the column.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.uuid( "id" ).primaryKey();
} );

MySQL (SQL Server)

CREATE TABLE `games` (
    `id` uniqueidentifier NOT NULL,
    CONSTRAINT `pk_games_id` PRIMARY KEY (`id`)
)

SQL (MySQL)

CREATE TABLE `games` (
    `id` VARCHAR(36) NOT NULL,
    CONSTRAINT `pk_games_id` PRIMARY KEY (`id`)
)

Query Parameters and Bindings

Custom Parameter Types

QueryBuilder
query.from( "users" )
    .where( "id", "=", { value = 18, cfsqltype = "CF_SQL_VARCHAR" } );
MySQL
SELECT *
FROM `users`
WHERE `id` = ?

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

QueryBuilder
query.table( "users" )
    .insert( {
        "id" = { value 1, cfsqltype = "CF_SQL_VARCHAR" },
        "age" = 18,
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_DATE" }
    } );
MySQL
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"
    }
};

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

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

Use these methods only for debugging. Modifying the bindings directly will likely cause issues when executing your query. Adding or removing bindings should be done using the public API.

getBindings

Name

Type

Required

Default

Description

No arguments

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

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

Name

Type

Required

Default

Description

No arguments

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

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" = []
};

Drop

Dropping tables straightforward in qb.

drop

Drop a table from the database.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to drop.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

dropIfExists

Drop a table from the database if it exists.

Argument

Type

Required

Default

Description

table

string

true

The name of the table to drop.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Example:

SchemaBuilder

schema.dropIfExists( "user_logins" );

SQL (MySQL)

DROP TABLE IF EXISTS `user_logins`

Column Modifiers

comment

Attach a comment to the column.

Example:

SchemaBuilder

SQL (MySQL)

default

Sets a default value for the column.

Note: The value is not escaped, allowing you to specify functions like NOW() or literals like 1. To specify a literal string, wrap the value in quotes.

Example:

SchemaBuilder

SQL (MySQL)

nullable

Sets the column to allow null values.

All columns are created as NOT NULL by default. As such, there is no notNull method.

Example:

SchemaBuilder

SQL (MySQL)

primaryKey

Adds the column as a primary key for the table.

Example:

SchemaBuilder

SQL (MySQL)

references

Creates a foreign key constraint for the column.

Example:

SchemaBuilder

SQL (MySQL)

unsigned

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.

Column Constraints

references

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

Example:

SchemaBuilder

SQL (MySQL)

onTable

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

Example:

SchemaBuilder

SQL (MySQL)

onUpdate

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

Example:

SchemaBuilder

SQL (MySQL)

onDelete

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

Example:

SchemaBuilder

SQL (MySQL)

Alter

The alter method loads up an existing table in order to make modifications. These modifications may include adding, renaming, or dropping columns and constraints.

Calling multiple methods inside a single alter callback creates multiple SQL statements to be executed. qb takes care of this execution for you by default.

The following methods off of Blueprint let you modify the table inside the callback:

addColumn

Add a new column to an existing table. Takes a Column instance as the only argument.

Example:

SchemaBuilder

SQL (MySQL)

raw

An escape hatch to directly insert any sql in to the statement.

Example:

SchemaBuilder

SQL (MySQL)

dropColumn

Drop a column on an existing table.

Example:

SchemaBuilder

SQL (MySQL)

modifyColumn

Modify an existing column on a table.

Example:

SchemaBuilder

SQL (MySQL)

renameColumn

Rename a column on a table. A full Column instance is required as the second argument for Grammars that need to redeclare the column definition when renaming.

Example:

SchemaBuilder

SQL (MySQL)

addConstraint

Example:

SchemaBuilder

SQL (MySQL)

dropConstraint

Drop an existing table constraint.

Example:

SchemaBuilder

SQL (MySQL)

renameConstraint

Rename an existing table constraint.

Example:

SchemaBuilder

SQL (MySQL)

renameTable

Rename an existing table.

Example:

SchemaBuilder

SQL (MySQL)

rename

An alias for renameTable.

Example:

SchemaBuilder

SQL (MySQL)

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:

postQBExecute

The following information is available in the interceptData struct:

A struct of column and value pairs to update. These column and value pairs are appended to any already set with the method.

A convenience argument for `where( "id", "=", arguments.id ). The query can be constrained by normal methods as well.

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the and methods instead.

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

string | | Function

The name of the column or with which to constrain the query. A function can be passed to begin a nested where statement.

string |

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

The name of the column or with which to constrain the query.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The name of the column or with which to constrain the query.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

string |

The name of the first column or with which to constrain the query.

string |

The name of the second column or with which to constrain the query.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

string |

The name of the column or with which to constrain the query.

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

string |

The name of the column or with which to constrain the query.

The value with which to constrain the column. An can be passed as well. If a QueryBuilder or Function is passed, it will be used as a subselect expression.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The name of the column or with which to constrain the query.

string | array | | Function | QueryBuilder

A single value, list of values, or array of values to constrain a column with. may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The name of the column or with which to constrain the query.

string | array | | Function | QueryBuilder

A single value, list of values, or array of values to constrain a column with. may be used in any place a value is used. Alternatively, a function or QueryBuilder instance can be passed in to be used as a subquery expression.

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

Any bindings needed for the raw SQL. Bindings can be simple values or .

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The name of the column to check if it is NULL. Can also pass an .

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

The name of the column to check if it is NULL. Can also pass an .

The boolean combinator for the clause. Valid options are "and" or "or". Avoid passing this parameter explicitly. Where possible use the andWhere and orWhere instead.

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.

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 can also be used for logging, though you may find it easier to use LogBox. See the documentation for for more information.

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

In addition to using the and off of the passed-in Blueprint object, the Blueprint contains helpers such as addConstraint, removeConstraint, addColumn, renameColumn, and dropColumn to assist in altering existing tables.

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

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 indexes and constraints.

The Blueprint object has many column types available to construct your table schema. Additionally, you can modify the columns created and .

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 .

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

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

You can view the current SQL for the query with bindings inline for debugging purposes using the method.

For dropping columns or constraints, see .

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

The primaryKey method returns a Additional methods can be chained off of it.

IMPORTANT: Additional configuration of the foreign constraint is done by calling methods on the returned

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

To begin altering an existing table, call the alter method off of the SchemaBuilder. This method takes a callback as the second parameter that is passed a Blueprint object, much like the method.

Any instance of Column is valid like those returned by the (integer, string, etc.) as well as the (unsigned, nullable, etc.).

Add an index or key to an existing table. Any TableIndex instance is valid, like those created by the (unique, index, primaryKey, etc.).

WHERE
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expression
Expressions
Expression
Expression
Expressions
Expression
Expression
cbDebugger
ColdBox's environment controls
qb's Interception Points
create
columns
indexes
alter
columns
indexes
drop and dropIfExists
columns
columns
with an additional set of methods
indexes
cfqueryparam
cfqueryparam
SQL injection.
cfqueryparam
Alter
addUpdate
where
andWhere
orWhere
whereBetween
whereNotBetween
whereColumn
whereExists
whereNotExists
whereLike
whereIn
whereNotIn
whereRaw
whereNull
whereNotNull
andWhere
orWhere
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
dynamic methods
custom parameters
toSQL

Argument

Type

Required

Default

Description

comment

string

true

The comment text.

schema.create( "users", function( table ) {
    table.integer( "age" ).comment( "Do not lie about your age" );
} );
CREATE TABLE `users` (
    `age` INTEGER NOT NULL COMMENT `Do not lie about your age`
)

Argument

Type

Required

Default

Description

value

string

true

The default value.

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` (
    `is_active` TINYINT(1) DEFAULT 1,
    `created_date` TIMESTAMP DEFAULT NOW(),
    `country` VARCHAR(255) DEFAULT 'USA'
)

Argument

Type

Required

Default

Description

No arguments

schema.create( "users", function( table ) {
    table.timestamp( "last_logged_in" ).nullable()
} );
CREATE TABLE `users` (
    `last_logged_in` TIMESTAMP
)

Argument

Type

Required

Default

Description

indexName

string

false

A derived name built from the table name and column name.

The name to use for the primary key constraint.

schema.create( "users", function( table ) {
    table.uuid( "id" ).primaryKey();
} );
CREATE TABLE `users` (
    `id` CHAR(35) NOT NULL,
    CONSTAINT `pk_users_id` PRIMARY KEY (`id`)
)

Argument

Type

Required

Default

Description

value

string

true

The default value.

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" ).onDelete( "cascade" );
} );
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
)

Argument

Type

Required

Default

Description

No arguments

schema.create( "users", function( table ) {
    table.integer( age" ).unsigned();
} );
CREATE TABLE `users` (
    `age` INTEGER UNSIGNED NOT NULL
)

Argument

Type

Required

Default

Description

No arguments

schema.create( "email", function( table ) {
    table.string( email" ).unique();
} );
CREATE TABLE `users` (
    `email` VARCHAR(255) NOT NULL UNIQUE
)

Argument

Type

Required

Default

Description

No arguments

schema.create( "posts", function( table ) {
    table.timestamp( "posted_date" ).withCurrent();
} );
CREATE TABLE "posts" (
    "posted_date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)

Argument

Type

Required

Default

Description

expression

string

true

The SQL used to define the computed column.

schema.create( "products", function( table ) {
    table.integer( "price" );
    table.integer( "tax" ).storedAs( "price * 0.0675" );
} );
CREATE TABLE `products` (
    `price` INTEGER NOT NULL,
    `tax` INTEGER GENERATED ALWAYS AS (price * 0.0675) STORED NOT NULL
)
CREATE TABLE [products] (
    [price] INTEGER NOT NULL,
    [tax] AS (price * 0.0675) PERSISTED
)
CREATE TABLE "products" (
    "price" INTEGER NOT NULL,
    "tax" INTEGER NOT NULL GENERATED ALWAYS AS (price * 0.0675) STORED
)
CREATE TABLE "PRODUCTS" (
    "PRICE" NUMBER(10, 0) NOT NULL,
    "TAX" NUMBER(10, 0) GENERATED ALWAYS AS (price * 0.0675)
)

Argument

Type

Required

Default

Description

expression

string

true

The SQL used to define the computed column.

schema.create( "products", function( table ) {
    table.integer( "price" );
    table.integer( "tax" ).virtualAs( "price * 0.0675" );
} );
CREATE TABLE `products` (
    `price` INTEGER NOT NULL,
    `tax` INTEGER GENERATED ALWAYS AS (price * 0.0675) VIRTUAL NOT NULL
)
CREATE TABLE [products] (
    [price] INTEGER NOT NULL,
    [tax] AS (price * 0.0675)
)
CREATE TABLE "products" (
    "price" INTEGER NOT NULL,
    "tax" INTEGER GENERATED ALWAYS AS (price * 0.0675) STORED
)
CREATE TABLE "PRODUCTS" (
    "PRICE" NUMBER(10, 0) NOT NULL,
    "TAX" NUMBER(10, 0) GENERATED ALWAYS AS (price * 0.0675) VIRTUAL
)
stored computed columns
virtual computed columns
comments

Argument

Type

Required

Default

Description

columns

any

true

A column or array of columns that represents the foreign key reference.

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );
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
)

Argument

Type

Required

Default

Description

table

string

true

The referencing table name.

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" ).references( "id" ).onTable( "countries" );
} );
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
)

Argument

Type

Required

Default

Description

option

string

true

The strategy to use. Available values are: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onUpdate( "CASCADE" );
} );
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
)

Argument

Type

Required

Default

Description

option

string

true

The strategy to use. Available values are: RESTRICT, CASCADE, SET NULL, NO ACTION, SET DEFAULT

schema.create( "users", function( table ) {
    table.unsignedInteger( "country_id" )
        .references( "id" )
        .onTable( "countries" )
        .onDelete( "SET NULL" );
} );
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
)

Argument

Type

Required

Default

Description

table

string

true

The name of the table to alter.

callback

function

true

A callback function used to define the alterations to the table. It is passed a Blueprint as the only argument.

options

struct

false

{}

Options to pass to queryExecute.

execute

boolean

false

true

Run the query immediately after building it.

Argument

Type

Required

Default

Description

column

Column

true

A column object to add to the table.

schema.alter( "users", function( table ) {
    table.addColumn( table.boolean( "is_active" ) );
} );
ALTER TABLE `users` ADD `is_active` TINYINT(1) NOT NULL

Argument

Type

Required

Default

Description

sql

string

true

The sql to insert directly into the statement.

schema.alter( "registrars", function ( table ) {
    table.addColumn(
        table.raw( "HasDNSSecAPI bit NOT NULL CONSTRAINT DF_registrars_HasDNSSecAPI DEFAULT (0)" )
    );
} );
ALTER TABLE `registrars`
ADD HasDNSSecAPI bit NOT NULL
CONSTRAINT DF_registrars_HasDNSSecAPI DEFAULT (0)

Argument

Type

Required

Default

Description

name

string

true

The name of the column to drop.

schema.alter( "users", function( table ) {
    table.dropColumn( "username" );
} );
ALTER TABLE `users` DROP COLUMN `username`

Argument

Type

Required

Default

Description

name

string

true

The name of the column to modify.

column

Column

true

A column object to replace the named column.

schema.alter( "users", function( table ) {
    table.modifyColumn( "name", table.string( "username" ) );
} );
ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

Argument

Type

Required

Default

Description

name

string

true

The current name of a column.

column

Column

true

A column object with the new column name and definition.

schema.alter( "users", function( table ) {
    table.renameColumn( "name", table.string( "username" ) );
} );
ALTER TABLE `users` CHANGE `name` `username` VARCHAR(255) NOT NULL

Argument

Type

Required

Default

Description

constraint

TableIndex

true

The TableIndex instance to add to the table.

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
} );
ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`)

Argument

Type

Required

Default

Description

name

string OR TableIndex

true

The name of the constraint to drop. You can alternatively pass a TableIndex instance to use the dynamic name generated.

schema.alter( "users", function( table ) {
    table.dropConstraint( "unq_users_full_name" );
    table.dropConstraint( table.unique( "username" ) );
} );
ALTER TABLE `users` DROP INDEX `unq_users_full_name`
ALTER TABLE `users` DROP INDEX `unq_users_username`

Argument

Type

Required

Default

Description

oldName

string OR TableIndex

true

The old or current name of the constraint to rename. You can alternatively pass a TableIndex instance to use the dynamic name generated.

newName

string OR TableIndex

true

The new name of the constraint. You can alternatively pass a TableIndex instance to use the dynamic name generated.

schema.alter( "users", function( table ) {
    table.renameConstraint( "unq_users_first_name_last_name", "unq_users_full_name" );
} );
ALTER TABLE `users` RENAME INDEX `unq_users_first_name_last_name` TO `unq_users_full_name`

Argument

Type

Required

Default

Description

oldName

string

true

The old or current name of the table to rename.

newName

string

true

The new name of the table.

schema.renameTable( "workers", "employees" );
RENAME TABLE `workers` TO `employees`

Argument

Type

Required

Default

Description

oldName

string

true

The old or current name of the table to rename.

newName

string

true

The new name of the table.

schema.rename( "workers", "employees" );
RENAME TABLE `workers` TO `employees`

Name

Type

Description

sql

String

The SQL string to execute.

bindings

Struct

The struct of bindings (keys and values) for the query.

options

Struct

Any options to pass along to queryExecute.

returnObject

String

The type to return: query or result.

Name

Type

Description

sql

String

The SQL string to execute.

bindings

Struct

The struct of bindings (keys and values) for the query.

options

Struct

Any options to pass along to queryExecute.

returnObject

String

The type to return: query or result.

query

Query | null

The query object or null if there isn't one.

result

Struct

The query result struct.

index methods

Creating Table Constraints

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.

Argument

Type

Required

Default

Description

columns

string or array

true

The column or array of columns that make up the index.

name

string

false

A generated name consisting of the table name and column name(s).

The name of the index constraint.

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.

Argument

Type

Required

Default

Description

columns

string or array

true

The column or array of columns that references a key or keys on another table.

name

string

false

A generated name consisting of the table name and column name(s).

The name of the foreign key constraint.

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.

Argument

Type

Required

Default

Description

columns

string or array

true

The column or array of columns that make up the primary key.

name

string

false

A generated name consisting of the table name and column name(s).

The name of the primary key constraint.

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.

Argument

Type

Required

Default

Description

columns

string or array

true

The column or array of columns that make up the unique constraint.

name

string

false

A generated name consisting of the table name and column name(s).

The name of the unique constraint.

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`)
)
creating a column
TableIndex instance.
TableIndex instance.
Blueprint
Column
create
column methods
column modifier methods