What to expect

  • An overview of ColdBox and Quick
  • Why you would use this library
  • Example app to explore how it works
  • Lots of live coding!

What not to expect

  • A short session
  • How to get started with CommandBox or ColdBox
  • A deep dive into any related or used modules

What is Quick and Why would I use it?

What is Quick?

  • Map database tables to components
  • Create relationships between components
  • Query and manipulate data
  • Persist changes to your database

Why use an ORM Engine?

  • Skip writing repetitive SQL
  • Automate tasks like eager loading
  • Encourage Object-Oriented code

Why Quick?

  • Not tied to CFML engine releases
  • Better error messages
  • Lightweight
  • Adoptable
  • Contribute-able

Setup

// config/ColdBox.cfc
component {

    function configure() {
        moduleSettings = {
            "quick" = {
                "defaultGrammar" = "AutoDiscover@qb"
            }
        };
    }

}
box install quick

Installation

Defining Entities

Mapping an Entity

// models/User.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";

    property name="email"
        update="false"
        insert="true";

    property name="password";

    property name="createdDate"
        column="created_date";

    property name="modifiedDate"
        column="modified_date";

    property name="lastLoggedIn"
        column="last_logged_in"
        nullValue="REALLY_NULL";

    property name="number"
        sqltype="cf_sql_varchar"
        convertToNull="false";

}

// models/User.cfc
component extends="quick.models.BaseEntity" accessors="true" {
  
    property name="bcrypt" inject="@BCrypt" persistent="false";
    property name="password";
    property name="createdDate" column="created_date";

    /* ... */

    function setPassword( password ) {
        assignAttribute(
            "password",
            bcrypt.hashPassword( password )
        );
        return this;
    }

    function getCreatedDate() {
        return dateFormat(
            retrieveAttribute( "createdDate" ),
            "mm/dd/yyyy"
        );
    }
  
}

Custom Getters and Setters

Fetching

Active Entity Pattern

// handlers/users.cfc
component {
    function index( event, rc, prc ) {
        var user = getInstance( "User" );
        /* ... */
    }
}

Virtual Service Pattern

// handlers/users.cfc
component {
    property name="userService" inject="quickService:User";

    /* ... */
}

Fetch Methods

  • all
  • get
  • find{orFail}
  • first{orFail}

Basic Fetch Methods

// handlers/users.cfc
component {
    
    property name="userService" inject="quickService:User";

    // GET /users
    function index( event, rc, prc ) {
        prc.users = userService.all();
    }

    // GET /users/:id
    function show( event, rc, prc ) {
        prc.user = getInstance( "User" ).findOrFail( rc.id );
    }

}

Advanced Fetch Methods

// handlers/users.cfc
component {
    
    property name="userService" inject="quickService:User";

    // GET /users/search
    function search( event, rc, prc ) {
        prc.users = userService
            .whereBetween( "createdDate", rc.startDate, rc.endDate )
            .when( rc.search != "", function( q ) {
                q.where( "username", "like", "%#rc.search#%" );
            } );
            .get();
    }

    // GET /users/:username
    function show( event, rc, prc ) {
        prc.user = userService
            .where( "username", rc.username )
            .firstOrFail();
    }

}

Persistence

Creating and Updating

// handlers/users.cfc
component {
    property name="userService" inject="quickService:User";

    // POST /users
    function store( event, rc, prc ) {
        userService.create( {
            "email" = rc.email,
            "password" = rc.password,
            "createdDate" = now(),
            "modifiedDate" = now()
        } );
        relocate( "users" );
    }

    // PUT /users/:id
    function update( event, rc, prc ) {
        var user = userService.findOrFail( rc.id );
        user.update( {
            "email" = rc.email,
            "password" = rc.password,
            "modifiedDate" = now()
        } );
        relocate( "users.#user.getId()#" );
    }
}

Key Types

  • NullKeyType
  • AutoIncrementingKeyType
  • ReturningKeyType
  • RowIDKeyType
  • UUIDKeyType

  • Custom Key Types

Key Types

// models/User.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    function keyType() {
        return variables._wirebox
            .getInstance( "NullKeyType@quick" );
    }

}

Deleting

// handlers/users.cfc
component {

    property name="userService" inject="quickService:User";

    // DELETE /users/:id
    function delete( event, rc, prc ) {
        var user = userService.findOrFail( rc.id );
        user.delete();
        // OR
        userService.deleteAll( rc.id );
    }

}

Mass Update and Deletes

// handlers/inactiveUsers.cfc
component {

    property name="userService" inject="quickService:User";

    // PATCH /inactive-users
    function update( event, rc, prc ) {
        userService
            .where( "last_logged_in", "<", dateAdd( "m", -3, now() ) )
            .updateAll( {
                "active" = 0
            } );
    }

    // DELETE /inactive-users
    function delete( event, rc, prc ) {
        userService
            .where( "active", 0 )
            .deleteAll();
    }

}

Scopes and Relationships

Query Scopes

// models/User.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";

    property name="createdDate"
        column="created_date";
    
    /* ... */

    function scopeLatest( query ) {
        return query.orderBy( "createdDate", "desc" );
    }

}

// handlers/users.cfc
component {

    function index( event, rc, prc ) {
        prc.users = getInstance( "User" )
            .latest()
            .get();
    }

}

Dynamic Query Scopes

// models/User.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";

    property name="createdDate"
        column="created_date";
    
    function scopeCreatedInYear( query, year ) {
        return query.whereBetween(
            "createdDate",
            createDate( year, 1, 1 ),
            createDate( year, 12, 31 )
        );
    }

}

// handlers/users.cfc
component {

    function index( event, rc, prc ) {
        prc.users = getInstance( "User" )
            .createdInYear( 2018 )
            .get();
    }

}

Relationships

// models/Post.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";
    property name="title";
    property name="userId" column="user_id";
    
    /* ... */

    function author() {
        return belongsTo( "User" );
    }

}

<!-- views/post/show.cfm -->
<cfoutput>
    <h3>#prc.post.getTitle()#</h3>
    <h4>By #prc.post.getAuthor().getEmail()#</h4>
    <!-- ... -->
</cfoutput>

Constraining Relationships

// models/Post.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";
    property name="slug";
    
    /* ... */

    function comments() {
        return hasMany( "Comment" );
    }

}

// handlers/posts.cfc
component {

    // GET /posts/:slug
    function show( event, rc, prc ) {
        prc.post = getInstance( "Post" ).whereSlug( slug ).findOrFail();
        prc.comments = prc.post.comments().approved().get();
    }

}

Constraining Relationships

// models/Post.cfc
component extends="quick.models.BaseEntity" accessors="true" {

    property name="id";
    property name="slug";
    
    /* ... */

    function comments() {
        return hasMany( "Comment" );
    }

    function approvedComments() {
        return comments().approved();
    }

}

// handlers/posts.cfc
component {

    // GET /posts/:slug
    function show( event, rc, prc ) {
        prc.post = getInstance( "Post" ).whereSlug( slug ).findOrFail();
        prc.comments = prc.post.getApprovedComments();
    }

}

Serialization

Memento

getInstance( "User" ).findOrFail( 1 ).getMemento();

{
    "id" = 1,
    "email" = "john@example.com",
    "createdDate" = "{ts '2019-04-13 02:33:44'}",
    "modifiedDate" = "{ts '2019-04-13 02:33:44'}"
}
component extends="quick.models.BaseEntity" {

    property name="id";
    property name="email";
    property name="createdDate" column="created_date";
    property name="modifiedDate" column="modified_date";

}

Mementifier

component displayname="User" extends="quick.models.BaseEntity" accessors="true" {

    property name="id";
    property name="username";
    property name="email";
    property name="password";
    property name="createdDate";
    property name="modifiedDate";

}
var memento = user.getMemento(
    excludes = [ "password" ]
);

/*
{
    "id" = 1,
    "username" = "JaneDoe",
    "email" = "jane@example.com",
    "createdDate" = "{ts '2018-03-12 16:14:10'}",
    "modifiedDate" = "{ts '2018-03-12 16:14:10'}"
}
*/

asMemento

var users = getInstance( "User" )
    .asMemento( excludes = [ "password" ] )
    .get()

/*
[
    {
        "id" = 1,
        "username" = "JaneDoe",
        "email" = "jane@example.com",
        "createdDate" = "{ts '2018-03-12 16:14:10'}",
        "modifiedDate" = "{ts '2018-03-12 16:14:10'}"
    },
    {
        "id" = 2,
        "username" = "JohnDoe",
        "email" = "john@example.com",
        "createdDate" = "{ts '2018-03-12 16:14:10'}",
        "modifiedDate" = "{ts '2018-03-12 16:14:10'}"
    },
    ...
]
*/

asQuery

var users = getInstance( "User" )
    .select( [ "id", "email" ] )
    .asQuery()
    .get()

/*
[
    {
        "id" = 1,
        "email" = "jane@example.com"
    },
    {
        "id" = 2,
        "email" = "john@example.com"
    },
    ...
]
*/

Subselects

Subselects

var post = getInstance( "Post" )
    .addSubselect( "authorEmail", function( qb ) {
        return qb.select( "email" )
            .from( "users" )
            .whereColumn( "users.id", "posts.userID" );
    } )
    .firstOrFail();

post.getAuthorEmail(); // john@example.com

Subselects via Relationships

component displayname="Post" extends="quick.models.BaseEntity" accessors="true" {
  
    property name="id";
    property name="title";
    property name="body";
    property name="createdDate";
    property name="modifiedDate";
  
    function author() {
        return belongsTo( "User" );
    }
  
}
var post = getInstance( "Post" )
    .addSubselect( "authorEmail", "author.email" )
    .firstOrFail();

post.getAuthorEmail(); // john@example.com
component displayname="User" extends="quick.models.BaseEntity" accessors="true" {
  
    property name="id";
    property name="email";
    property name="createdDate";
    property name="modifiedDate";
  
}

Relationship Counts

var post = getInstance( "Post" )
    .addSubselect( "commentsCount", function( qb ) {
        qb.selectRaw( "COUNT(*)" )
            .from( "comments" )
            .whereColumn( "comments.postID", "posts.id" );
    } )
    .firstOrFail();

post.getCommentsCount(); // 5

Relationship Counts

component displayname="Post" extends="quick.models.BaseEntity" accessors="true" {
  
    property name="id";
    property name="title";
    property name="body";
    property name="createdDate";
    property name="modifiedDate";
  
    function comments() {
        return hasMany( "Comment" );
    }
  
}
var post = getInstance( "Post" )
    .withCount( [ "comments" ] )
    .firstOrFail();

post.getCommentsCount(); // 5

Debugging

toSQL

var sql = getInstance( "Post" )
    .latest()
    .toSQL();

/*
SELECT *
FROM `posts`
ORDER BY `posts`.createdDate` DESC
*/

dump

var posts = getInstance( "Post" )
    .dump( label = "before latest scope" )
    .latest()
    .dump(
        showBindings = "inline",
        label = "after latest scope"
    )
    .get();

LogBox Debug Logs

// config/ColdBox.cfc
component {
  
    function configure() {
        logbox = {
            debug = [ "qb.models.Grammars" ]
        };  
    }
  
}

cbDebugger

Now, to the code!