Introduction
Master Branch Build Status
Development Branch Build Status
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) - Microsoft SQL Server (
SqlServerGrammar@qb) - SQLite (
SQLiteGrammar@qb)
The Box modules discussion group and community can be found here:
Compare these two examples:
// Plain old CFML
var results = queryExecute( "SELECT * FROM users" );
// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "users" ).get();
The differences become even more stark when we introduce more complexity:
// Plain old CFML
var results = queryExecute(
"SELECT * FROM posts WHERE published_at IS NOT NULL AND author_id IN ?",
[ { value = "5,10,27", cfsqltype = "CF_SQL_NUMERIC", list = true } ]
);
// qb
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
.whereNotNull( "published_at" )
.whereIn( "author_id", [ 5, 10, 27 ] )
.get();
With qb you can easily handle setting order by statements before the columns you want or join statements after a where clause:
var qb = wirebox.getInstance( "QueryBuilder@qb" );
var results = qb.from( "posts" )
.orderBy( "published_at" )
.select( "post_id", "author_id", "title", "body" )
.whereLike( "author", "Ja%" )
.join( "authors", "authors.id", "=", "posts.author_id" )
.get();
// Becomes
var results = queryExecute(
"SELECT post_id, author_id, title, body FROM posts INNER JOIN authors ON authors.id = posts.author_id WHERE author LIKE ? ORDER BY published_at",
[ { value = "Ja%", cfsqltype = "CF_SQL_VARCHAR", list = false, null = false } ]
);
qb enables you to explore new ways of organizing your code by letting you pass around a query builder object that will compile down to the right SQL without you having to keep track of the order, whitespace, or other SQL gotchas!
Here's a gist with an example of the powerful models you can create with this! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202
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 );
Last modified 4mo ago