qb is a fluent query builder for CFML. It is heavily inspired by Eloquent from Laravel.
Using qb, you can:
Installation is easy through CommandBox and ForgeBox. Simply type box install qb
to get started.
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 Quick 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!
Here's a gist with an example of the powerful models you can create with this! https://gist.github.com/elpete/80d641b98025f16059f6476561d88202
To use the SQLite grammar for qb you will need to setup a datasource that connects to a SQLite database.
/lib
directoryConfigure the application to load the library by adding this line in your Application.cfc
file.
this.javaSettings = { loadPaths : [ ".\lib" ] };
You can configure your datasource for Lucee or Adobe Coldfusion using the steps below. You can also use cfconfig with CommandBox to do it automatically for you.
For both Lucee and ACF you need to set the JDBC Driver class to org.sqlite.JDBC
. Then you need to specify the JDBC connection string as jdbc:sqlite:<your database path>
. i.e. jdbc:sqlite:C:/data/my_database.db
Lucee
org.sqlite.JDBC
for Classjdbc:sqlite:<db path>
ACF
other
for the datasource driverorg.sqlite.JDBC
for the Driver Classorg.sqlite.JDBC
for the Driver Namejdbc:sqlite:<db path>
You can browse the full documentation at https://qb.ortusbooks.com