qb — A Query Builder for the rest of us

Explore a database independent, object-oriented way to write SQL in your CFML applications.

Transcript

1. QB
   A QUERY BUILDER FOR
   THE REST OF US
   

   View Slide

2. WHAT THIS TALK IS NOT:
   > Database performance tips
   > How to integrate qb in your application*
   > About a new ORM Engine (though qb is be the backend for
   one)
   * WE'LL TOUCH ON THIS A TINY BIT
   

   View Slide

3. WHAT THIS TALK IS:
   > How to abstract your SQL statements from your
   database grammar
   > Help to stop concatenating SQL strings
   > Ideas for new patterns to manage complex SQL
   

   View Slide

4. WHO AM I?
   ERIC PETERSON
   !
   Utah
   "
   Ortus
   #
   ForgeBox, ColdBox Elixir
   $
   Prolific Module Author
   %
   1 wife, 2 kids, (1 on the way)
   

   View Slide

5. WHAT IS QB?
   

   View Slide

6. WHAT IS QB?
   QUERYBUILDER & SCHEMABUILDER
   > A fluent, chainable syntax for building SQL statements
   > An abstraction around different database engines
   > An abstraction around queryExecute
   > Heavily inspired by Eloquent from Laravel
   

   View Slide

7. WHY CREATE QB?
   

   View Slide

8. TELL ME IF YOU'VE SEEN
   THIS ONE BEFORE
   

   View Slide

9. function find( slug, createdBy, popular /* ..and so forth. */ ) {
   var q = new Query(); //
   !
   var sql = "SELECT id, slug, createdBy, updatedDate";
   // need this for one of two arguments
   if ( ! isNull( popular ) ) {
   // fun fact, I was missing a comma here from a previous presentation
   // this wouldn't have even worked!
   sql &= ", COUNT(*) AS installCount";
   }
   sql &= " FROM entries"; // now we can add the from
   if ( ! isNull( popular ) ) {
   sql &= " JOIN installs ON entries.id = installs.entryId";
   }
   // need some sort of initial where statement
   // otherwise we have to worry about `WHERE` vs `AND`
   sql &= " WHERE 1 = 1";
   if ( ! isNull( slug ) ) {
   q.addParam(
   name = "slug",
   cfsqltype = "CF_SQL_VARCHAR",
   value = "%#slug#%",
   );
   sql &= " AND slug LIKE :slug"; // don't forget the leading space!!
   }
   if ( ! isNull( createdBy ) ) {
   q.addParam(
   name = "createdBy",
   cfsqltype = "CF_SQL_INTEGER",
   value = "%#createdBy#%",
   );
   sql &= " AND createdBy = :createdBy";
   }
   if ( ! isNull( popular ) ) {
   sql &= " GROUP BY id, slug, createdBy, updatedDate";
   }
   // make sure to put the order by at the end.
   if ( ! isNull( popular ) ) {
   sql &= " ORDER BY installCount DESC";
   } else {
   sql &= " ORDER BY updatedDate DESC";
   }
   q.setSql( sql );
   return q.execute().getResult();
   }
   

   View Slide

10. WHAT PAIN POINTS DO WE SEE?
    > Manually concatenating SQL strings
    > Repeating checks in multiple places
    > if statements mean no chainability
    

    View Slide

11. WHAT COULD THIS LOOK
    LIKE WITH QB?
    

    View Slide

12. function find( slug, createdBy, popular, count /* ..and so forth. */ ) {
    return builder
    .from( "entries" )
    .select( [ "id", "slug", "createdBy", "updatedDate" ] )
    .when( ! isNull( slug ), function( query ) {
    query.where( "slug", "like", "%#slug#%" );
    } )
    .when( ! isNull( createdBy ), function( query ) {
    query.where( "createdBy", createdBy );
    } )
    .when( ! isNull( popular ), function( query ) {
    query
    .addSelect( query.raw( "COUNT(*) AS installCount" ) )
    .join( "installs", "entries.id", "installs.entryId" )
    .groupBy( [ "id", "slug", "createdBy", "updatedDate" ] );
    } )
    .when( ! isNull( popular ), function( query ) {
    query.orderBy("installCount", "desc");
    }, function( query ) {
    query.orderBy("updatedDate", "desc");
    } )
    .get();
    }
    

    View Slide

13. AND TRUST ME, WE CAN (AND WILL)
    MAKE THAT EVEN BETTER!
    

    View Slide

14. QB IS VALUABLE FOR SMALL SQL
    STATEMENTS AS WELL!
    

    View Slide

15. SPOT THE PROBLEM
    function loadPosts() {
    return queryExecute("SELECT * FROM `posts`");
    }
    

    View Slide

16. THE SYNTAX IS DATABASE
    ENGINE SPECIFIC
    

    View Slide

17. THIS IS AN ALTERNATIVE SYNTAX FOR
    OTHER ENGINES
    function loadPosts() {
    return queryExecute('SELECT * FROM "posts"');
    }
    

    View Slide

18. IN QB, BOTH OF THESE STATEMENTS ARE COVERED
    WITH THIS:
    function loadPosts() {
    return builder.from("posts").get();
    }
    

    View Slide

19. AND THAT'S JUST THE TIP
    OF THE ICEBERG
    

    View Slide

20. SO, WHY CREATE QB?
    > Reduce typos
    > Bridge database engine idiosyncrasies
    > Stop concatentating SQL strings
    > Create expressive fluent statements instead of code
    littered with if statements
    > Enable new Object-Oriented patterns with SQL
    statements
    

    View Slide

21. QB BUILDING BLOCKS
    > Query Builder / Schema Builder
    > Grammars
    > Query Utils
    

    View Slide

22. CONFIGURATION
    

    View Slide

23. DEFAULT GRAMMAR
    CONFIGURATION
    

    View Slide

24. DEFAULT GRAMMAR
    CONFIGURATION
    Bundled grammars include:
    MSSQLGrammar, MySQLGrammar,
    OracleGrammar, PostgresGrammar
    (We would love your help adding more.)
    

    View Slide

25. RETURN FORMAT
    CONFIGURATION
    > Return a query
    > Return an array (default)
    > Return something custom!
    

    View Slide

26. moduleSettings = {
    qb = {
    returnFormat = function( query ) {
    return application.wirebox.getInstance(
    name = "CFCollection",
    initArguments = { collection = query }
    );
    }
    }
    }
    

    View Slide

27. CONFIGURATION
    Configure your settings through the moduleSettings
    struct in config/ColdBox.cfc.
    component {
    function configure() {
    moduleSettings = {
    qb = {
    defaultGrammar = "MySQLGrammar"
    }
    }
    }
    }
    

    View Slide

28. QUERYBUILDER
    SYNTAX
    AN OVERVIEW
    

    View Slide

29. FROM
    query.from( "posts" );
    > Specifies the base table for the query
    

    View Slide

30. SELECT
    query.select( "id" );
    > Default is *
    > Takes a single value, list, or array
    > Overwrites any existing selected columns
    

    View Slide

31. ADDSELECT
    query.addSelect( "id" );
    > Adds a column to the select list
    > If the selection is currently *, then the added column
    will be the only column selected.
    

    View Slide

32. JOIN
    query.from( "posts" )
    .join( "users", "posts.user_id", "=", "users.id" );
    > Add a join statement to the query
    > Simple syntax for simple joins
    > Complex joins can be specified using a callback
    

    View Slide

33. WHERE
    query.where( "title", "=", "My Title" );
    > Adds a where statement to the query
    > Automatically params the value passed
    > Accepts any SQL operator like <=, <>, or LIKE
    

    View Slide

34. WHERE MAGIC
    query.where( "title", "My Title" )
    Can skip the second parameter if you want the operator
    to be =
    

    View Slide

35. MORE WHERE MAGIC
    query.whereTitle( "My Title" )
    onMissingMethod lets you write really terse equality
    where statements
    

    View Slide

36. NESTED WHERE
    query.from( "users" )
    .where( "email", "foo" )
    .orWhere( function( q ) {
    q.where( "name", "bar" )
    .where( "age", ">=", "21" );
    } );
    Becomes
    SELECT *
    FROM `users`
    WHERE `email` = ?
    OR ( `name` = ? AND `age` >= ? )
    

    View Slide

37. WHERECOLUMN
    query.whereColumn( "updated_date", ">", "created_date" );
    > Compares two columns
    > Accepts any SQL operator like <=, <>, or LIKE
    

    View Slide

38. WHEREIN
    query.whereIn( "id", [ 1, 45, 101 ] );
    > Adds a where in statement to the query
    > Automatically params the values passed
    

    View Slide

39. OTHER WHERE METHODS
    query.whereBetween( "createdDate", startDate, endDate );
    query.whereNull( "deletedDate" );
    query.whereExists( function( q ) {
    q.select( q.raw( 1 ) ).from( "products" )
    .where( "products.id", "=", q.raw( """orders"".""id""" ) );
    } );
    

    View Slide

40. WHERE METHODS WRAP-UP
    There are corresponding orWhere and whereNot
    methods for each where method.
    

    View Slide

41. GROUPBY
    query.groupBy( "id" );
    > Adds a group by statement
    > Further calls add to any existing groupings
    > Takes a single value, list, or array
    

    View Slide

42. HAVING
    query.from( "users" )
    .groupBy( "email" )
    .having( builder.raw( "COUNT(email)" ), ">", 1 );
    > Adds a having statement
    > Works pretty much like a where statement
    

    View Slide

43. ORDERBY
    query.orderBy( "id", "desc" );
    > Adds a order by statement
    > Further calls add to any existing orders
    > Takes a single value, list, or array
    

    View Slide

44. LIMIT & OFFSET
    query.limit( 1 ).offset( 5 );
    > Adds a limit and offset to the query
    > This one is a great example where normalizing database
    engines is super nice.
    

    View Slide

45. MYSQL
    LIMIT
    SELECT * FROM `users` LIMIT 3
    LIMIT & OFFSET
    SELECT * FROM `users` LIMIT 15 OFFSET 30
    

    View Slide

46. POSTGRES
    LIMIT
    SELECT * FROM "users" LIMIT 3
    LIMIT & OFFSET
    SELECT * FROM "users" LIMIT 15 OFFSET 30
    

    View Slide

47. MSSQL
    LIMIT
    SELECT TOP (3) * FROM [users]
    LIMIT & OFFSET
    SELECT * FROM [users]
    ORDER BY 1
    OFFSET 30 ROWS
    FETCH NEXT 15 ROWS ONLY
    

    View Slide

48. ORACLE
    LIMIT
    SELECT * FROM (
    SELECT results.*, ROWNUM AS "QB_RN"
    FROM (SELECT * FROM "USERS") results
    ) WHERE "QB_RN" <= 3
    LIMIT & OFFSET
    SELECT * FROM (
    SELECT results.*, ROWNUM AS "QB_RN"
    FROM (SELECT * FROM "USERS") results
    ) WHERE "QB_RN" > 30 AND "QB_RN" <= 45
    

    View Slide

49. RAW
    query.select( query.raw( "COUNT(*) AS post_count" ) );
    > The qb escape hatch
    > Outputs exactly the text you give it
    

    View Slide

50. WHEN
    query.when( rc.isActive, function( q ) {
    q.where( "is_active", 1 );
    }, function( q ) {
    q.whereNull( "is_active" );
    } );
    > Control flow for chainable methods
    

    View Slide

51. TOSQL
    query.from( "users" ).toSQL()
    > Builds the query into a SQL statement
    > Bindings will be available in the getBindings method
    

    View Slide

52. GET
    query.from( "users" ).get();
    > Builds and executes the current query
    > Can accept columns as a shortcut
    > Also accepts any options that can be passed to
    queryExecute
    

    View Slide

53. FIND
    query.from( "users" ).find( 1 );
    > Shortcut method for retrieving records by primary key
    > Default idColumn is id
    > idColumn can be specified as second argument
    

    View Slide

54. FIRST
    query.from( "users" ).first();
    > Adds a limit( 1 ) to your query
    > Also returns the struct value, not an array of one
    > Returns an empty struct if nothing is found
    

    View Slide

55. EXISTS
    query.from( "logins" )
    .where( "user_id", user.getId() )
    .exists();
    > Returns a boolean
    > Works with any configured query
    > Just call exists instead of get
    

    View Slide

56. COUNT, MAX, MIN, SUM
    (AGGREGATES)
    query.from( "users" )
    .max( "last_logged_in" );
    > Returns a single value from query
    > Takes the column to perform the operation on
    

    View Slide

57. VALUE
    query.from( "users" )
    .where( "id", "=", rc.id )
    .value( "username" );
    > Fetches only one record (like first)
    > Returns the value of the column specified
    

    View Slide

58. VALUES
    query.from( "users" )
    .values( "username" );
    > Returns an array of all matched records
    > Returns only the value of the column specified in the
    array
    

    View Slide

59. INSERT
    query.from( "users" ).insert( {
    "username" = "johndoe",
    "email" = "john@example.com",
    "password" = bcryptedPassword
    } );
    > Inserts data into a table
    

    View Slide

60. BATCH INSERT
    query.from( "users" ).insert( [
    {
    "username" = "johndoe",
    "email" = "john@example.com",
    "password" = bcryptedPassword
    },
    {
    "username" = "janedoe",
    "email" = "jane@example.com",
    "password" = bcryptedPassword
    }
    ] );
    > Uses the database-specific batch insert syntax
    

    View Slide

61. UPDATE
    query.from( "users" )
    .whereId( rc.id )
    .update( { "password" = newPassword } );
    > Updates all matched rows, so be careful
    > Works off of the built query
    

    View Slide

62. UPDATEORINSERT
    query.from( "users" )
    .whereId( rc.id )
    .updateOrInsert( {
    "username" = "johndoe",
    "email" = "john@example.com",
    "password" = bcryptedPassword
    } );
    > Updates or inserts dependening on if the query exists
    

    View Slide

63. DELETE
    query.from( "users" )
    .whereConfirmed( 0 )
    .delete();
    > Deletes all matched rows, so be careful
    > Works off of the built query
    

    View Slide

64. WHEW...
    !
    

    View Slide

65. QueryFilters
    EXAMPLE
    

    View Slide

66. QueryFilters EXAMPLE
    // AbstractQueryFilters.cfc
    component {
    property name="query";
    function apply( required query, rc = {} ) {
    variables.query = arguments.query;
    for ( var key in rc ) {
    if ( variables.keyExists( key ) && isCustomFunction( variables[ key ] ) ) {
    var func = variables[ key ];
    func( rc[ key ] );
    }
    }
    return variables.query;
    }
    }
    

    View Slide

67. QueryFilters EXAMPLE
    // EntryFilters.cfc
    component extends="AbstractQueryFilters" {
    function slug( slug ) {
    query.where( "slug", "like", "%#slug#%" );
    }
    function createdBy( authorName ) {
    query.where( "createdBy", createdBy );
    }
    function popular() {
    query.addSelect( query.raw( "COUNT(*) AS installCount" ) )
    .join( "installs", "entries.id", "installs.entryId" )
    .groupBy( [ "entries.id", "slug", "createdBy", "updatedDate" ] )
    .orderBy( "installCount", "desc" );
    }
    }
    

    View Slide

68. QueryFilters EXAMPLE
    // handlers/Entries.cfc
    component {
    property name="EntryFilters" inject="id";
    function index(event, rc, prc) {
    var query = getInstance( "Builder@qb" );
    query.select( [ "entries.id", "slug", "createdBy", "updatedDate" ] )
    .from( "entries" )
    .orderBy( "updatedDate", "desc" );
    EntryFilters.apply( query, rc );
    prc.entries = query.get();
    }
    }
    

    View Slide

69. SEE?
    

    View Slide

70. I TOLD YOU WE'D MAKE
    THAT EVEN BETTER!
    

    View Slide

71. DEMO!
    

    View Slide

72. CONTRIBUTING
    

    View Slide

73. GRAMMARS
    CONTRIBUTING
    

    View Slide

74. DOCS
    CONTRIBUTING
    

    View Slide

75. View Slide

76. WHO KNOWS WHAT YOU WILL
    CREATE WITH THIS?
    

    View Slide

77. THANKS!
    > qb docs
    > qb on ForgeBox
    > me on Twitter (_elpete)
    > me on ForgeBox (elpete))
    > CFML Slack (elpete)
    

    View Slide