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
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
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)