1 of 30

Into The Box 2020�QB For the Rest Of Us: 1.21 Gigawatts of Fluent and Functional

Samuel W. Knowlton

sam@inleague.io / @MordantWastrel / @SKnowlton (CFML Slack)

inLeague LLC

Austin, TX

​

2 of 30

Professional Dilettante Generalist

  • Fixed computers for Best Buy back when Packard Bell made computers; ran a Bulletin Board System (BBS!)
  • Some CS Background, but mostly English Literature & British drama
  • Central Texas Liaison for the Actors’ Equity Association, the union of live theatre actors/stage managers
  • Founder & Principal developer at inLeague; SaaS for the American Youth Soccer Organization & Success Academy Charter Schools
  • Started inLeague in 2002, left for NYC in 2004
  • Private Pilot
  • Lives in Austin, TX (since 2012)

​

​

​

​

3 of 30

What is QB?

  • https://www.forgebox.io/view/qb
  • Written by Eric Peterson at Ortus (Twitter: @_elpete; CFML Slack: @elpete); a dozen contributors on Github
  • A module with two utilities: QueryBuilder and SchemaBuilder
  • QueryBuilder: a Chainable syntax for building SQL statements that abstracts queryExecute() and database grammar
  • SchemaBuilder: Database-agnostic tool for building or editing database objects in code
  • Works with or without Coldbox
  • The basis of the Quick ORM (also by Eric)
  • Inspired by Eloquent for Laravel

​

4 of 30

Queries: Why We CFML!�What should we be using in 2020?

  • Just queries, please! No ORM (for now…)

​

  • qb …
  • <cfquery> …
  • new query() …
  • queryExecute() …

​

5 of 30

Queries: Why We CFML!�What should we be using in 2020?

​

​

  • qb … Great!
  • <cfquery> … Not Great
  • new query() … Not Great
  • queryExecute() … Still Pretty Great!

​

6 of 30

5 Reasons To Use

  1. Database Grammar-Neutral

​

  • SchemaBuilder / Migrations

​

3. It’s the basis for Quick ORM

​

4. Modern patterns for managing complex SQL: No more concatenating query text

​

​

5. Builder Syntax means Functional Programming and coolguy method chaining:

​

​

7 of 30

8 of 30

9 of 30

.from(), .select(), and .selectRaw()

10 of 30

Ordinary QB and Escape Hatches

  • Regular SQL:

queryExecute( “SELECT wo.description FROM `workorder`”);

  • Grammar-specific syntax: `workorder`
  • QB takes care of this with .from( “workorder” )
  • QB Grammars: MySQL, Oracle, Postgres, SQL Server (PRs welcome for additional Grammars!)
  • Not all SQL functions are implemented in the Grammar (e.g. COALESCE); and sometimes you just want to write some SQL!
  • .raw(), .selectRaw(), .whereRaw(), .fromRaw()

​

11 of 30

WHERE Bootstrapping in QB:�NOPE

12 of 30

Conditionals

13 of 30

Detour: Where A and (B OR C)

14 of 30

Can I Still Use queryExecute()?

  • Yes.
  • Can another developer make sense of your query?
  • Are you concatenating SQL?
  • If it’s readable, short, and there isn’t any dynamic input…
  • …you probably don’t need a builder object.

15 of 30

Joins and WhereIn

16 of 30

QB Query Params

17 of 30

Order By

Or an array of strings, an array of structs, a subQuery, or a raw expression!

18 of 30

The Final workorder query, inLeague edition

19 of 30

toSQL(): Debugging QB

20 of 30

Re-Using Builder Objects

21 of 30

22 of 30

…get()? Executing the Query & Return Types

  • QB returns arrays by default, but can return traditional query objects (returnFormat = ‘query’) or custom objects (below)
  • .get(), .first(), .values(), .value(), .chunk(), .paginate(): These ‘terminate’ the builder and execute the query

23 of 30

QB Interceptors: High-Powered QB Debugging in Coldbox

24 of 30

cbDebugger 2.0 and QB

25 of 30

When Ortus Makes a Better Version of Your Demo

26 of 30

SchemaBuilder and Migrations

  • Migrations: Database changes in code
  • Handled by separate modules (cfmigrations or commandbox-migrations) but these both use QB
  • CFMigrations is run from code while commandbox-migrations is run from the CLI, usually during CI/CD, but otherwise they work the same way
  • Migration files are created and date-stamped, and your DB gets a cfmigrations table to track which migrations have been applied
  • Migrations use SchemaBuilder and QueryBuilder, but can also contain queryExecute or other code

​

27 of 30

Update, Insert, Delete

  • .insert() takes a struct or an array of structs (bulk insert) with column names as keys; .update() takes a struct of column and value pairs as well, but also has .addUpdate() to build them dynamically (like .addSelect()
  • .insert with .returning() will include column(s) returned from INSERT for grammars that support return values (SQL Server, Postgres)
  • .insert(), .update() and .delete() can be executed on any QB object in lieu of .get(); they are terminators that execute the query unless the toSQL = true argument is passed
  • .updateOrInsert() will update a record if it exists and create one if it does not, using the WHERE constraints supplied
  • More information: https://qb.ortusbooks.com/query-builder/executing-queries/inserts-updates-deletes

28 of 30

Sir Not Appearing In This Film:�Other Great QB Features

.groupBy( string / array )

.having( column, operator, value, combinator)

.limit( value )

.offset( offset )

.forPage( page, maxRows )

.paginate()

.union( closure / QueryBuilder )

.unionAll( closure / QueryBuilder )

.with( name, input, columns, recursive)

.withRecursive( name, input, columns )

.whereBetween( column, start, end )

.whereColumn( first, operator, second )

.whereExists( query )

.whereLike( column, value )

.whereNull( column )

.count(), .max(), .min(), .sum(), .exists()

29 of 30

What’s New in QB 7

  • New & Improved documentation (and Ortus groupies to talk about them)
  • Several new column types for SchemaBuilder, particularly timezone-related
  • .chunk() method to work on large queries in smaller groups
  • showBindings option in .toSQL() to for debugging queries with their parameters
  • New paginate() method to generate a pagination struct alongside the results
  • .whereLike()
  • Lambdas (arrow funtions) supported in all QB closures
  • All methods that could conceivably take a subquery as well as a value now accept a closure or another builder instance to use as a subquery.
  • …and more: https://qb.ortusbooks.com/whats-new

30 of 30

QB Resources