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.