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"
{
    /* ... */
}

Demo

Quick — a ColdBox ORM Engine

By Eric Peterson

Quick — a ColdBox ORM Engine

  • 2,711

More from Eric Peterson