qb is a fluent query builder for CFML. It is heavily inspired by Eloquent from Laravel.
Using qb, you can:
Quickly scaffold simple queries
Make complex, out-of-order queries possible
Abstract away differences between database engines
Adobe ColdFusion 2018+
Lucee 5+
qb supports four major database grammars:
MySQL (MySQLGrammar@qb)
Oracle (OracleGrammar@qb)
Postgres (PostgresGrammar@qb)
The Box modules discussion group and community can be found here:
Installation is easy through and . Simply type box install qb to get started.
Compare these two examples:
The differences become even more stark when we introduce more complexity:
With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:
qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!
Here's a gist with an example of the powerful models you can create with this!
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.
If you are not using WireBox, just make sure to wire up the Builder object with the correct grammar:
Microsoft SQL Server (SqlServerGrammar@qb)
SQLite (SQLiteGrammar@qb)
// Plain old CFML
var results = queryExecute( "SELECT * FROM users" );
// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "users" ).get();// Plain old CFML
var results = queryExecute(
"SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
[ { value = "5,10,27", cfsqltype = "CF_SQL_NUMERIC", list = true } ]
);
// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
.whereNotNull( "published_at" )
.whereIn( "author_id", [ 5, 10, 27 ] )
.get();var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
.orderBy( "published_at" )
.select( "post_id", "author_id", "title", "body" )
.whereLike( "author", "Ja%" )
.join( "authors", "authors.id", "=", "posts.author_id" )
.get();
// Becomes
var results = queryExecute(
"SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
[ { value = "Ja%", cfsqltype = "CF_SQL_VARCHAR", list = false, null = false } ]
);moduleSettings = {
qb = {
defaultGrammar = "MySQLGrammar@qb"
}
};var grammar = new qb.models.Query.Grammars.MySQLGrammar();
var builder = new qb.models.Query.Builder( grammar );