Blog

Eric Peterson

December 19, 2017

Spread the word


Share your thoughts

In the CFML scene, there seems to be two names when it comes to data persistance plain ole' `cfquery` and ORM — not much inbetween. Our module highlight today is out to change that.

Meet qb.

qb

qb is a query builder. It allows you to specify SQL commands with a fluent syntax. The builder is then compiled to different database grammars such as MySQL, Oracle, and SQL Server. The result is fluent, readable code that can compile to any database grammar you need. Let's take a quick high-level overview of the syntax:

Selects, Joins, and Wheres

Query methods can be specified in any order — qb will take care of putting them in the right order for you. You end with a call to get to execute the query.

query.from( "posts" )
    .select( "post_id", "author_id", "title", "body" )
    .join( "authors", "authors.id", "=", "posts.author_id" )
    .whereLike( "authors.name", "Ja%" )
    .orderBy( "posts.published_at" )
    .get();

/*
  SELECT `post_id`,
         `author_id`,
         `title`,
         `body`
    FROM `posts`
    JOIN `authors`
      ON `authors`.`id` = `posts`.`author_id`
   WHERE `authors`.`name` LIKE 'Ja%'
ORDER BY `posts`.`published_at`
*/

Inserts, Updates, and Deletes

Inserts, updates, and deletes use the same methods to restrict the data set. The only difference is the method called at the end of the builder chain — insert, update, or delete instead of get.

query.from( "posts" )
    .whereId( 1 )
    .update( {
        "title" = "New Title",
        "updated_at" = now()
    } );

/*
UPDATE `posts`
   SET `title` = 'New Title',
       `updated_at` = '2017-12-19 08:00:   00'
 WHERE `id` = 1
*/

Aggregates

qb provides helpers for common aggregate functions on data sets. As usual, use any other qb methods to shape the data set before getting the aggregate.

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

/*
SELECT COUNT(*)
  FROM `users`
*/

query.from( "orders" )
    .whereBetween("created_date", dateAdd( "d", -7, now() ), now() )
    .sum( "total" );

/*
SELECT SUM(`total`)
  FROM `orders`
 WHERE `created_date` BETWEEN '2017-12-12 08:00:   00' AND '2017-12-19 08:00:   00'
*/

Interception Points

qb supports two interception points — preQBExecute and postQBExecute. Use it to log or modify the sql as you need.

Wrap Up

qb enables a slew of new patterns. Check out an example of one in this gist. Be sure to check out all the examples in the official docs to harness the raw power qb offers.

Add Your Comment

Recent Entries

CBSecurity 3.x Released

CBSecurity 3.x Released

We are incredibly excited to release CBSecurity 3. This is a significant release with over six months of work invested in it. We have completely revamped our security module to make ColdBox applications secure, flexible, and ready for the upcoming ColdBox 7 release. The first major announcement for this release is that we have a brand-new logo!

Luis Majano
Luis Majano
January 30, 2023
CBWIRE 2.2 Released

CBWIRE 2.2 Released

We're excited to announce the release of CBWIRE 2.2. This release includes several added enhancements, such as new lifecycle hooks and simplified Turbo Drive integration. We changed the previous lifecycle hook of mount() to onMount() to be consistent, and several bugs were squashed.

Maria Jose Herrera
Maria Jose Herrera
January 09, 2023
FORGEBOX v7.1.0 Released

FORGEBOX v7.1.0 Released

We are so happy to announce a minor release for our package management system. In this release, we take care of some bug fixes reported and a lot of work for our elastic search engine. Now better search results with an improved response time. We have updated our core modules to the latest versions for better security and performance.

Javier Quintero
Javier Quintero
December 30, 2022