Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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.

Eric Peterson

April 26, 2018
Tweet

More Decks by Eric Peterson

Other Decks in Programming

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" = "[email protected]",
    "password" = bcryptedPassword
    } );
    > Inserts data into a table

    View Slide

  60. BATCH INSERT
    query.from( "users" ).insert( [
    {
    "username" = "johndoe",
    "email" = "[email protected]",
    "password" = bcryptedPassword
    },
    {
    "username" = "janedoe",
    "email" = "[email protected]",
    "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" = "[email protected]",
    "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( "[email protected]" );
    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