Quick was built out of lessons learned and persistent challenges in developing complex RDBMS applications using built-in Hibernate ORM in CFML.
Hibernate ORM error messages often obfuscate the actual cause of the error because they are provided directly by the Java classes.
Complex CFML Hibernate ORM applications can consume significant memory and processing resources, making them cost-prohibitive and inefficient when used in microservices architecture.
Hibernate ORM is tied to the engine releases. This means that updates come infrequently and may be costly for non-OSS engine users.
We can do better.
What?
Quick is an ORM (Object Relational Mapper) written in CFML for CFML. It provides an implementation for working with your database. With it you can map database tables to components, create relationships between components, query and manipulate data, and persist all your changes to your database.
Prerequisites
You need the following configured before using Quick:
Configure a default datasource in your CFML engine
ColdBox 4.3+
Add a mapping for quick in your Application.cfc
See for more details.
Supported Databases
Quick supports all databases supported by .
Example
Here's a "quick" example to whet your appetite.
We'll show the database structure using a . This isn't required to use quick, but it is highly recommended.
Now that you've seen an example, with Quick!
Prior Art, Acknowledgements, and Thanks
Quick is backed by . Without qb, there is no Quick.
Quick is inspired heavily by . Thank you Taylor Otwell and the Laravel community for a great library.
Development of Quick is sponsored by Ortus Solutions. Thank you Ortus Solutions for investing in the future of CFML.
Hibernate ORM is built in Java. This limits contributions from CFML developers who don't know Java or don't feel comfortable contributing to a Java project.
Hibernate ORM doesn't take advantage of a lot of dynamic- and meta-programming available in CFML. (Tools like CBORM have helped to bridge this gap.)
// User
component extends="quick.models.BaseEntity" {
// the name of the table is the pluralized version of the model
// all fields in a table are mapped by default
// both of these points can be configured on a per-entity basis
}
// handlers/Users.cfc
component {
// /users/:id
function show( event, rc, prc ) {
// this finds the User with an id of 1 and retrieves it
prc.user = getInstance( "User" ).findOrFail( rc.id );
event.setView( "users/show" );
}
}
Sometimes you want to use a different value in your code than is stored in your database. Perhaps you want to enforce that setting a password always is hashed with BCrypt. Maybe you have a Date value object that you want wrapping each of your dates. You can accomplish this using custom getters and setters.
A custom getter or setter is simply a function in your entity.
To retrieve the attribute value fetched from the database, call getAttribute passing in the name of the attribute.
To set an attribute for saving to the database, call setAttribute passing in the name and the value.
component extends="quick.models.BaseEntity" {
property name="bcrypt" inject="@BCrypt";
function setPassword( value ) {
return setAttribute( "password", bcrypt.value );
}
function getCreatedDate( value ) {
return dateFormat( getAttribute( "createdDate" ), "DD MMM YYYY" );
}
}
Note: Custom getters and setters with not be called when hydrating a model from the database.
Getting Started
Configure a default datasource in your CFML engine
You can do this any way you'd like: through the web admin, in Application.cfc, or using .
Make sure to set this.datasource in your Application.cfc
Deleting Entities
delete
You can delete an entity by calling the delete method on it.
Note: The entity will still exist in any variables you have stored it in, even though it has been deleted from the database.
Relationships
Relationships are the heart of any ORM engine. They let you interact with relational database tables in an object-oriented way.
Quick's relationship engine provides readable relationship types, extendible relations at runtime, eager loading, and much more.
Start by checking out the different .
Creating New Entities
save
New Quick entities can be created and persisted to the database by creating a new entity instance, setting the attributes on the entity, and then calling the save method.
When we call save, the record is persisted from the database and the primary key is set to the auto-generated value (if any).
The first is as a getter. Calling user.getPosts() will execute the relationship, cache the result, and return it.
var posts = user.getPosts();
The second is as a relationship. Calling user.posts() returns a Relationship instance to retrieve the posts that can be further constrained. A Relationship is backed by qb as well, so feel free to call any qb method to further constrain the relationship.
var newestPosts = user.posts().orderBy( "publishedDate", "desc" ).get();
so Quick knows which datasource to use.
Add a mapping for quick in your Application.cfc
For a default installation in a ColdBox template, the following line will do the trick.
A polymorphicBelongsTo relationship is a many-to-one relationship. This relationship is used when an entity can belong to multiple types of entities. The classic example for this type of relationship is Posts, Videos, and Comments. For instance, a Comment may belong to a Post or a Video.
The only value passed to polymorphicBelongsTo is a prefix for the polymorphic type. A common convention where is to add able to the end of the entity name, though this is not automatically done. In our example, this prefix is commentable. This tells quick to look for a commentable_type and a commentable_id column in our Comment entity. It stores our entity's mapping as the _type and our entity's primary key value as the _id.
When retrieving a polymorphicBelongsTo relationship the _id is used to retrieve a _type from the database.
The inverse of polymorphicBelongsTo is also polymorphicHasMany. It is important to choose the right relationship for your database structure. hasOne assumes that the related model has the foreign key for the relationship.
hasOne
Defining
A hasOne relationship is a "one-to-one" relationship. For instance, a User entity might have an UserProfile entity attached to it.
The first value passed to hasOne is a WireBox mapping to the related entity.
Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the UserProfile entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:
The inverse of hasOne is . It is important to choose the right relationship for your database structure. hasOne assumes that the related model has the foreign key for the relationship.
Updating Existing Entities
save
Updates are handled identically to inserts when using the save method. The only difference is that instead of starting with a new entity, we start with an existing entity.
var user = getInstance( "User" ).find( 1 );
user.setPassword( "newpassword" );
user.save();
update
You can update multiple fields at once using the update method. This is similar to the create method for creating new entities.
There is no need to call save when using the update method.
updateAll
Updates can be performed against any number of entities that match a given query.
Defining An Entity
To get started with Quick, you need an entity. There are two ways to define an entity.
The first way is to extend quick.models.BaseEntity.
The second way, in a ColdBox application, is to annotate your component with the quick annotation.
This will use WireBox's to accomplish the same as extending the BaseEntity.
That's all that is needed to get started with Quick. There are a few defaults of Quick worth mentioning here.
polymorphicHasMany
A polymorphicHasMany relationship is a one-to-many relationship. This relationship is used when an entity can belong to multiple types of entities. The classic example for this type of relationship is Posts, Videos, and Comments.
The first value passed to polymophicHasMany is a WireBox mapping to the related entity.
The second value is a prefix
Retrieving Entities
Once you have an entity and its associated database table you can start retrieving data from your database.
You start every interaction with Quick with an instance of an entity. The easiest way to do this is using WireBox. getInstance is available in all handlers by default. WireBox can easily be injected in to any other class you need using inject="wirebox".
Quick is backed by qb, a CFML Query Builder. With this in mind, think of retrieving records for your entities like interacting with qb. For example:
In addition to using for you can utilize the each function on
var user = getInstance( "User" ).find( 1 );
user.update( {
email = "[email protected]",
password = "newpassword"
} );
Tables
We don't need to tell Quick what table name to use for our entity. By default, Quick uses the pluralized name of the component for the table name. That means for our User entity Quick will assume the table name is users. You can override this by specifying a table metadata attribute on the component.
Primary Key
By default, Quick assumes a primary key of id. The name of this key can be configured by setting variables.key in your component.
Quick also assumes a key type that is auto-incrementing. If you would like a different key type, inject it as the `keyType` property. Quick ships with a `UUID` type that you can use as well.
keyType can be any class that adheres to the keyType interface, so feel free to create your own and distribute them via ForgeBox.
Columns
You specify what columns are retrieved by adding properties to your component.
Now, only the id, username, and email columns will be retrieved.
Note: the primary key (id by default) will be retrieved regardless of the properties specified.
To prevent Quick from mapping a property to the database add the persistent="false" attribute to the property.
If the column name in your table is not the column name you wish to use in quick, you can alias it using the column metadata attribute.
Multiple datasource support
Quick uses a default datasource and default grammar, as described here. If you are using multiple datasources you can override default datasource by specifying a datasource metadata attribute on the component. If your extra datasource has a different grammar you can override your grammar as well by specifying a grammar attribute.
At the time of writing Valid grammar options are: MySQLGrammar,PostgresGrammar, MSSQLGrammar and OracleGrammar. Please check the qb docs for additional options.
interface displayname="KeyType" {
/**
* Called to handle any tasks before inserting into the database.
* Recieves the entity as the only argument.
*/
public void function preInsert( required entity );
/**
* Called to handle any tasks after inserting into the database.
* Recieves the entity and the queryExecute result as arguments.
*/
public void function postInsert( required entity, required struct result );
}
var users = getInstance( "User" ).all();
// users is a QuickCollection. To get an array,
// first call `get` or `toArray`
for ( var user in users.get() ) {
writeOutput( user.getUsername() );
}
var users = getInstance( "User" ).all();
prc.users.each( function( user ) {
writeOutput( user.getUsername() );
} );
A hasManyThrough relationship is a many-to-many relationship. It is used when you want to access a related entity through another entity. The most common example for this is through a pivot table. For instance, a User may have multiple Permissions via a UserPermission entity. This allows you to store additional data on the UserPermission entity, like a createdDate .
The first value passed to hasManyThrough is a WireBox mapping to the related entity.
The second value passed is a WireBox mapping to the intermediate entity.
Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Permission entity is assumed to have a permissionId foreign key. You can override this by passing a foreign key in as the third argument:
The intermediateKey is also determined by Quick. It is the foreign key of the current entity for the intermediate entity's table. In our example, this would be userId, since User is our entity and it is for the UserPermissions table. You can override this by passing in the intermediateKey as the fourth argument.
Lastly, the owningKey is the primary key of the entity. Usually this is just id. You can override this by passing in the owningKey as the fifth argument.
The inverse of hasManyThrough is also hasManyThrough. A note that the intermediate entity would use belongsTo relationships to link back to each side of the hasManyThrough relationship. These relationships are not needed to use a hasManyThrough relationship.
hasMany
Defining
A hasMany relationship is a one-to-many relationship. For instance, a User may have multiple Posts.
Eager Loading
The Problem
Let's imagine a scenario where you are displaying a list of posts. You fetch the posts:
And start looping through them:
When you visit the page, though, you notice it takes a while to load. You take a look at your SQL console and you've executed 26 queries for this one page! What?!?
Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Post entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:
There are two ways to add an entity to a hasMany relationship. Both mirror the insert API for entities.
save
You can call the save method on the relationship passing in an entity to relate.
This will add the User entity's id as a foreign key in the Post and save the Post to the database.
Note: the save method is called on the posts relationship, not the getPosts collection.
create
Use the create method to create and save a related entity directly through the relationship.
This example will have the same effect as the previous example.
Removing
Removing a hasMany relationship is handled in two ways: either by using the dissociate method on the belongsTo side of the relationship or by deleting the belongsTo side of the relationship.
Turns out that each time you loop through a post to display its author's username you are executing a SQL query to retreive that author. With 25 posts this becomes 25 SQL queries plus one initial query to get the posts. This is where the N+1 problem gets its name.
So what is the solution? Eager Loading.
Eager Loading means to load all the needed users for the posts in one query rather than separate queries and then stitch the relationships together. With Quick you can do this with one method call.
The Solution
with
You can eager load a relationship with the with method call.
with takes one parameter, the name of the relationship to load. Note that this is the name of the function, not the entity name. For example:
To eager load the User in the snippet above you would call pass author to the with method.
For this operation, only two queries will be executed:
Quick will then stitch these relationships together so when you call post.getAuthor() it will use the fetched relationship value instead of going to the database.
You can eager load multiple relationships by passing an array of relation names to with or by calling with multiple times.
load
Finally, you can postpone eager loading until needed by using the load method on QuickCollection. load has the same function signature as with. QuickCollection is the object returned for all Quick queries that return more than one record. Read more about it in Collections.
var post = getInstance( "Post" ).fill( {
"title" = "My Post",
"body" = "Hello, world!"
} );
var user = getInstance( "User" ).findOrFail( 1 );
user.posts().save( post );
var user = getInstance( "User" ).findOrFail( 1 );
user.posts().create( {
"title" = "My Post",
"body" = "Hello, world!"
} );
SELECT * FROM `posts` LIMIT 25
SELECT * FROM `users` WHERE `id` IN (1, 2, 3, 4, 5, 6, ...)
belongsTo
A belongsTo relationship is a many-to-one relationship. For instance, a Post may belong to a User.
The first value passed to belongsTo is a WireBox mapping to the related entity.
Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the Post entity is assumed to have a userId foreign key. You can override this by passing a foreign key in as the second argument:
The inverse of belongsTo is or .
Updating
To update a belongsTo relationship, use the associate method. associate takes the entity to associate as the only argument.
Note:associate does not automatically save the entity. Make sure to call save when you are ready to persist your changes to the database.
Removing
To remove a belongsTo relationship, use the dissociate method.
Note:dissociate does not automatically save the entity. Make sure to call save when you are ready to persist your changes to the database.
Collections
All queries that potentially return more than one record using Quick are returned using a QuickCollection. QuickCollection is a specialized version of . It is a component that smooths over the various CFML engines to provide an extendible, reliable array wrapper with functional programming methods. You may be familiar with methods like map (ArrayMap), filter (ArrayFilter), or reduce (ArrayReduce). These methods work in every CFML engine with CFCollection.
Collections are more powerful than plain arrays. There are many methods that can make your work easier. For instance, let's say you needed to group each active user by the first letter of their username in a list.
So powerful! We think you'll love it.
load
Additionally, QuickCollection includes a load method. load lets you eager load a relationship after executing the initial query.
This is the same as if you had initially executed:
$renderData
QuickCollection includes a $renderData method that lets you return a QuickCollection directly from your handler and translates the results and the entities within to a serialized version. Check out more about it in the Serialization chapter.
var posts = getInstance( "Post" ).all();
if ( someCondition ) {
posts.load( "user" );
}
getInstance( "Post" ).with( "user" ).all()
Interception Points
Quick allows you to hook in to multiple points in the entity lifecycle.
quickPreLoad
Fired before attempting to load an entity from the database.
This method is only called for find actions.
interceptData structure
quickPostLoad
Fired after loading an entity from the database.
This method is only called for find actions.
interceptData structure
quickPreSave
Fired before saving an entity to the database.
This method is called for both insert and update actions.
interceptData structure
quickPostSave
Fired after saving an entity to the database.
This method is called for both insert and update actions.
interceptData structure
quickPreInsert
Fired before inserting an entity into the database.
interceptData structure
quickPostInsert
Fired after inserting an entity into the database.
interceptData structure
quickPreUpdate
Fired before updating an entity in the database.
interceptData structure
quickPostUpdate
Fired after updating an entity in the database.
interceptData structure
quickPreDelete
Fired before deleting a entity from the database.
interceptData structure
quickPostDelete
Fired after deleting a entity from the database.
interceptData structure
belongsToMany
A belongsToMany relationship is a many-to-many relationship. For instance, a User may have multiple Permissions while a Permission can belong to multiple Users.
The first value passed to belongsToMany is a WireBox mapping to the related entity.
belongsToMany makes some assumptions about your table structure. To support a many-to-many relationship, you need a pivot table. This is, at its simplest, a table with each of the foreign keys as columns.
As you can see, Quick uses a convention of combining the entity table names with an underscore (_) to create the new pivot table name. If you want to override this convention, you can do so by passing the desired table name as the second parameter or the table parameter.
Quick determines the foreign key of the relationship based on the entity name and key values. In this case, the User entity is assumed to have a userId foreign key and the Permission entity a permissionId foreign key. You can override this by passing a foreignKey in as the third argument and a relatedKey as the fourth argument:
The inverse of belongsToMany is also belongsToMany. The foreignKey and relatedKey arguments are swapped on the inverse side of the relationship.
If you find yourself needing to interact with the pivot table (permissions_users) in the example above, you can create an intermediate entity, like UserPermission. You will still be able to access the end of the relationship chain using the hasManyThrough relationship type.
attach
Use the attach method to relate two belongsToMany entities together. attach can take a single id, a single entity, or an array of ids or entities (even mixed and matched) to associate.
detach
Use the detach method to remove an existing entity from a belongsToMany relationship. detatch can also take a single id, a single entity, or an array of ids or entities (even mixed and matched) to remove.
sync
Sometimes you just want the related entities to be a list you give it. For these situations, use the sync method.
Now, no matter what relationships existed before, this Post will only have three tags associated with it.
Serialization
getMemento
The memento pattern is an established pattern in ColdBox apps. A memento in this case is a simple representation of your entity using arrays, structs, and simple values.
For instance, the following example shows a User entity and its corresponding memento:
You can modify the memento by overriding the getMemento
Query Scopes
Definition
Query scopes are a way to encapsulate query constraints in your entities while giving them readable names .
The $renderData method is a special method for ColdBox. When returning a model from a handler, this method will be called and the value returned will be used as the serialized response. This let's you simply return an entity from a handler for your API.
QuickCollection also defines a $renderData method, which will delegate the call to each entity in the collection and return the array of serialized entities.
For instance, let's say that you need to write a report for subscribers to your site. Maybe you track subscribers in a users table with a boolean flag in a subscribed column. Additionally, you want to see the oldest subscribers first. You keep track of when a user subscribed in a subscribedDate column. Your query might look as follows:
Now nothing is wrong with this query. It retrieves the data correctly and you continue on with your day.
Later, you need to retrieve a list of subscribed users for a different part of the site. So, you write a query like this:
We've duplicated the logic for how to retrieve active users now. If the database representation changed, we'd have to change it in multiple places. For instance, what if instead of keeping track of a boolean flag in the database, we just checked that the subscribedDate column wasn't null?
Now we see the problem. Let's look at the solution.
The key here is that we are trying to retrieve subscribed users. Let's add a scope to our User entity for subscribed:
Now, we can use this scope in our query:
We can use this on our first example as well, for our report.
We've successfully encapsulated our concept of a subscribed user!
We can add as many scopes as we'd like. Let's add one for longestSubscribers.
Now our query is as follows:
Best of all, we can reuse those scopes anywhere we see fit without duplicating logic.
Usage
All query scopes are methods on an entity that begin with the scope keyword. You call these functions without the scope keyword (as shown above).
Each scope is passed two arguments: query, a reference to the current QueryBuilder instance; and args, any arguments passed to the scope call.
var post = getInstance( "Post" ).findOrFail( 1 );
var tag = getInstance( "Tag" ).create( "miscellaneous" );
// pass an id
post.tags().attach( tag.getId() );
// or pass an entity
post.tags().attach( tag );
var post = getInstance( "Post" ).findOrFail( 1 );
var tag = getInstance( "Tag" ).create( "miscellaneous" );
// pass an id
post.tags().detach( tag.getId() );
// or pass an entity
post.tags().detach( tag );
var post = getInstance( "Post" ).findOrFail( 1 );
post.tags().sync( [ 2, 3, 6 ] );