A ColdBox ORM Engine

Who am I?

🏡 Utah

🏬 Ortus

📦 Quick, qb, ColdBox Elixir

🔥 Prolific Module Author

👨‍👩‍👦‍👦 1 wife, 3 kids

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"
            }
        };
    }

}

box install quick

Mapping an Entity

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

    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";

}

Custom Getters and Setters

// models/User.cfc
component extends="quick.models.BaseEntity" {
    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"
        );
    }
}

Active Entity

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

Virtual Service

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

// 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
UUIDKeyType
ReturningKeyType

Key Types

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

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

}

Deleting Entities

// 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 Updates 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()
    }

}

Query Scopes

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

    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" {

    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" {

    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>

Refining Relationships

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

    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().active().get();
    }
}

Refined Relationships

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

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

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

    function activeComments() {
        return comments().active();
    }

}

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

N+1 Problem

<!-- views/posts/show.cfm -->
<cfoutput>
    <cfloop var="#prc.posts#" item="post">
        <h3>#post.getTitle()#</h3>
        <h5>By #post.getAuthor().getEmail()#</h5>
        <!-- ... -->
    </cfloop>
</cfoutput>

SELECT * FROM `posts` WHERE `id` = ? LIMIT 1
SELECT * FROM `users` WHERE `id` = ? LIMIT 1

Queries Executed

Number of Posts	Number of Queries
1	2
2	3
10	11

Eager Loading

// handlers/posts.cfc
component {

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

}

Number of Posts	Number of Queries
1	2
2	2
10	2

SELECT * FROM `posts` WHERE `id` = ? LIMIT 1
SELECT * FROM `users` WHERE `id` IN (?, ?, ?, ?, ...)

Queries Executed

Nested Eager Loading

// handlers/users.cfc
component {

    // GET /users
    function show( event, rc, prc ) {
        prc.users = getInstance( "User" )
            .with( "posts.comments" )
            .get();
    }

}

Number of Posts	Number of Queries
1	3
10	3
100	3

SELECT * FROM `users`
SELECT * FROM `posts` WHERE `user_id` IN (?, ?, ?, ?, ...)
SELECT * FROM `comments` WHERE `post_id` IN (?, ?, ?, ?, ...)

Queries Executed

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";

}

Custom Memento

component extends="quick.models.BaseEntity" {

    /* ... */

    function getMemento() {
        return {
            "id" = getId(),
            "email" = getEmail(),
            "createdDate" = dateFormat( getCreatedDate(), "mm/dd/yyyy" ),
            "modifiedDate" = dateFormat( getModifiedDate(), "mm/dd/yyyy" )
        };
    }

}

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

{
    "id" = 1,
    "email" = "john@example.com",
    "createdDate" = "04/13/2019",
    "modifiedDate" = "04/13/2019"
}

Use with mementifier

component extends="quick.models.BaseEntity" {

    this.memento = { /*... */ };

    function getMemento() {
        return $getMemento();
    }

}

Subselects

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

    function posts() {
        return hasMany( "Post", "user_id" );
    }

    function scopeWithPostCount( query ) {
        query.select( "users.*" )
            .subselect( "postCount", function( query ) {
                query.selectRaw( "COUNT(*)" )
                    .from( "posts" )
                    .whereColumn( "user_id", "users.id" );
            } );
    }

}

<!-- /views/posts/show.cfc -->
<cfoutput>
    <p>#prc.user.getEmail()# has #prc.user.getPostCount()# post(s).</p>
</cfoutput>

Subselect Relationships

// models/User.cfc
component extends="quick.models.BaseEntity" {
    /* ... */
    function scopeWithLatestPost( query ) {
        variables._nullValues[ "latestPostId" ] = "";
        query.select( "users.*" )
            .subselect( "latestPostId", function( query ) {
                query.select( "id" )
                    .from( "posts" )
                    .whereColumn( "user_id", "users.id" )
                    .orderBy( "created_date", "desc" )
                    .limit( 1 );
            } )
            .with( "latestPost" );
    }

    function latestPost() {
        return belongsTo( "Post", "latestPostId" );
    }
}

<!-- /views/posts/show.cfc -->
<cfoutput>
    <h3>#prc.user.getEmail()#</h3>
    <dl>
        <dt>Latest Post</dt>
        <dd>#prc.user.getLatestPost().getTitle()</dd>
    </dl>
</cfoutput>

Lifecycle Methods

instanceReady	preLoad	postLoad
preSave	postSave	preInsert
postInsert	preUpdate	postUpdate

preDelete

postDelete

Custom Datasources & Grammars

component
    extends="quick.models.BaseEntity"
    table="tbl_posts"
    datasource="secondarydb"
    grammar="PostgresGrammar"
{
    /* ... */
}

Who am I?

What is Quick?

Why use an ORM Engine?

Why Quick?

Setup

Mapping an Entity

Custom Getters and Setters

Active Entity

Virtual Service

Fetch Methods

Basic Fetch Methods

Advanced Fetch Methods

Persistence

Key Types

Key Types

Deleting Entities

Mass Updates and Deletes

Query Scopes

Dynamic Query Scopes

Relationships

Refining Relationships

Refined Relationships

N+1 Problem

Eager Loading

Nested Eager Loading

Memento

Custom Memento

Use with mementifier

Subselects

Subselect Relationships

Lifecycle Methods

Custom Datasources & Grammars

Demo