qb — A Query Builder for the rest of us

qb — A Query Builder for the rest of us

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

Cbddee54e0016667b9bcb0fdec4ab21e?s=128

Eric Peterson

April 26, 2018
Tweet

Transcript

  1. QB A QUERY BUILDER FOR THE REST OF US

  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
  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
  4. WHO AM I? ERIC PETERSON ! Utah " Ortus #

    ForgeBox, ColdBox Elixir $ Prolific Module Author % 1 wife, 2 kids, (1 on the way)
  5. WHAT IS QB?

  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
  7. WHY CREATE QB?

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

  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(); }
  10. WHAT PAIN POINTS DO WE SEE? > Manually concatenating SQL

    strings > Repeating checks in multiple places > if statements mean no chainability
  11. WHAT COULD THIS LOOK LIKE WITH QB?

  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(); }
  13. AND TRUST ME, WE CAN (AND WILL) MAKE THAT EVEN

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

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

    `posts`"); }
  16. THE SYNTAX IS DATABASE ENGINE SPECIFIC

  17. THIS IS AN ALTERNATIVE SYNTAX FOR OTHER ENGINES function loadPosts()

    { return queryExecute('SELECT * FROM "posts"'); }
  18. IN QB, BOTH OF THESE STATEMENTS ARE COVERED WITH THIS:

    function loadPosts() { return builder.from("posts").get(); }
  19. AND THAT'S JUST THE TIP OF THE ICEBERG

  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
  21. QB BUILDING BLOCKS > Query Builder / Schema Builder >

    Grammars > Query Utils
  22. CONFIGURATION

  23. DEFAULT GRAMMAR CONFIGURATION

  24. DEFAULT GRAMMAR CONFIGURATION Bundled grammars include: MSSQLGrammar, MySQLGrammar, OracleGrammar, PostgresGrammar

    (We would love your help adding more.)
  25. RETURN FORMAT CONFIGURATION > Return a query > Return an

    array (default) > Return something custom!
  26. moduleSettings = { qb = { returnFormat = function( query

    ) { return application.wirebox.getInstance( name = "CFCollection", initArguments = { collection = query } ); } } }
  27. CONFIGURATION Configure your settings through the moduleSettings struct in config/ColdBox.cfc.

    component { function configure() { moduleSettings = { qb = { defaultGrammar = "MySQLGrammar" } } } }
  28. QUERYBUILDER SYNTAX AN OVERVIEW

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

    the query
  30. SELECT query.select( "id" ); > Default is * > Takes

    a single value, list, or array > Overwrites any existing selected columns
  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.
  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
  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
  34. WHERE MAGIC query.where( "title", "My Title" ) Can skip the

    second parameter if you want the operator to be =
  35. MORE WHERE MAGIC query.whereTitle( "My Title" ) onMissingMethod lets you

    write really terse equality where statements
  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` >= ? )
  37. WHERECOLUMN query.whereColumn( "updated_date", ">", "created_date" ); > Compares two columns

    > Accepts any SQL operator like <=, <>, or LIKE
  38. WHEREIN query.whereIn( "id", [ 1, 45, 101 ] ); >

    Adds a where in statement to the query > Automatically params the values passed
  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""" ) ); } );
  40. WHERE METHODS WRAP-UP There are corresponding orWhere and whereNot methods

    for each where method.
  41. GROUPBY query.groupBy( "id" ); > Adds a group by statement

    > Further calls add to any existing groupings > Takes a single value, list, or array
  42. HAVING query.from( "users" ) .groupBy( "email" ) .having( builder.raw( "COUNT(email)"

    ), ">", 1 ); > Adds a having statement > Works pretty much like a where statement
  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
  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.
  45. MYSQL LIMIT SELECT * FROM `users` LIMIT 3 LIMIT &

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

    OFFSET SELECT * FROM "users" LIMIT 15 OFFSET 30
  47. MSSQL LIMIT SELECT TOP (3) * FROM [users] LIMIT &

    OFFSET SELECT * FROM [users] ORDER BY 1 OFFSET 30 ROWS FETCH NEXT 15 ROWS ONLY
  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
  49. RAW query.select( query.raw( "COUNT(*) AS post_count" ) ); > The

    qb escape hatch > Outputs exactly the text you give it
  50. WHEN query.when( rc.isActive, function( q ) { q.where( "is_active", 1

    ); }, function( q ) { q.whereNull( "is_active" ); } ); > Control flow for chainable methods
  51. TOSQL query.from( "users" ).toSQL() > Builds the query into a

    SQL statement > Bindings will be available in the getBindings method
  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
  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
  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
  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
  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
  57. VALUE query.from( "users" ) .where( "id", "=", rc.id ) .value(

    "username" ); > Fetches only one record (like first) > Returns the value of the column specified
  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
  59. INSERT query.from( "users" ).insert( { "username" = "johndoe", "email" =

    "john@example.com", "password" = bcryptedPassword } ); > Inserts data into a table
  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
  61. UPDATE query.from( "users" ) .whereId( rc.id ) .update( { "password"

    = newPassword } ); > Updates all matched rows, so be careful > Works off of the built query
  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
  63. DELETE query.from( "users" ) .whereConfirmed( 0 ) .delete(); > Deletes

    all matched rows, so be careful > Works off of the built query
  64. WHEW... !

  65. QueryFilters EXAMPLE

  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; } }
  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" ); } }
  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(); } }
  69. SEE?

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

  71. DEMO!

  72. CONTRIBUTING

  73. GRAMMARS CONTRIBUTING

  74. DOCS CONTRIBUTING

  75. None
  76. WHO KNOWS WHAT YOU WILL CREATE WITH THIS?

  77. THANKS! > qb docs > qb on ForgeBox > me

    on Twitter (_elpete) > me on ForgeBox (elpete)) > CFML Slack (elpete)