Coming from <cfquery> or queryExecute?

You might be thinking, I don't need an ORM engine. I don't even know what ORM means! I know SQL backwards and forwards so there's nothing an ORM can offer me. Maybe you've had experience with other ORM engines, whether CFML-based or not, and the experience was less that ideal. Why should you consider Quick?

Quick's ORM philosophy comes down to three main points:

1. Give relevant names to important collections of SQL code. (, , etc.)

2. Make queries easy to compose at runtime to get the exact data you want in the most efficient way (, , etc.)

3. Get out of your way when you need or want to write barebones SQL.

Coming from Hibernate?

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.

We can do better.

A CFML ORM Engine

Why?

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.

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.

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 .

Relationships can be used in two ways.

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();

You can also call the other Quick fetch methods: first, firstOrFail, find, and findOrFail are all supported. This is especially useful to constrain the entities available to a user by using the user's relationships:

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 retrieveAttribute passing in the name of the attribute.

To set an attribute for saving to the database, call assignAttribute passing in the name and the value.

The cookbook is a place to show examples of using Quick in practice. Each cookbook entry will include a short description of the problem to solve or use case and one or more code snippets showing the code.

The cookbook is not meant to teach you the basics of Quick or show you the method signatures. That is the purpose of the Guide and API Docs, respectively. The cookbook is meant to show you advanced and specific use cases.

Additionally, this is a great place to contribute to Quick! If you have solved a particular use case, send in a pull request and add it to the cookbook! We only ask that you take the time to simplify your example as much as you can. This usually means removing any custom naming convention for your attributes, tables, and entities.

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 so Quick knows which datasource to use.

Download Quick

The easiest way to download Quick is to use ForgeBox with CommandBox. Just run the following from the root of your application:

box install quick

Add a mapping for quick in your Application.cfc

For a default installation in a ColdBox template, the following line will do the trick.

this.mappings[ "/quick" ] = COLDBOX_APP_ROOT_PATH & "/modules/quick";

Configure your defaultGrammar in config/ColdBox.cfc

Quick will auto discover your grammar by default on startup. To avoid this check, set a BaseGrammar.

defaultGrammar is a module setting for Quick. Set it in your config/ColdBox.cfc like so:

Valid options are any of the . At the time of writing valid grammar options are: MySQLGrammar@qb, PostgresGrammar@qb, MSSQLGrammar@qb and OracleGrammar@qb. You can also have qb discover your grammar on application init using AutoDiscover@qb. Please check the qb docs for additional options.

If you want to use a different datasource and/or grammar for individual entitities you can do so by attributes to your entities.

To assist you in migrating from CBORM, Quick ships with a small compatibility shim. To use it, have your entity extend quick.models.CBORMCompatEntity. This will map common CBORM methods to their Quick counterparts as well as provide a partial CriteriaBuilder shim. The compatibility shim does not cover differences in properties or relationships.

Entity / Service Methods

• list

• countWhere

• deleteById

• deleteWhere

• exists

• findAllWhere

• findWhere

• get

• getAll

• new

• populate

• save

• saveAll

• newCriteria

Criteria Builder Methods

• getSQL

• between

• eqProperty

• isEQ

delete

You can delete an entity by calling the delete method on it.

3.1.7

• Correct jQuery link in test runner.

3.1.6

• Allow expressions in basic where clauses.

• Fix delete naming collision.

3.1.5

• Add an alias to with to QuickBuilder.

3.1.4

• Fix a stack overflow on nested relationship checks.

3.1.3

• Configured tables (.from) are now used for qualifying columns.

3.1.2

• Remove unnecessary nesting in compare queries.

3.1.1

• Fix when using "OR" combinators.

3.1.0

• Add support for JSON using a new JsonCast@quick component.

3.0.4

• Compatibility updates for ColdBox 6

3.0.3

• Optimize caching

3.0.2

• Apply during .

3.0.1

• Account for null values in .

• Swap structAppend order for a Lucee bug in mementifier integration.

3.0.0

BREAKING CHANGES

Please see the for more information on these changes.

• Drop support for Lucee 4.5 and Adobe ColdFusion 11.

• Virtual Inheritance (using a quick annotation instead of extending quick.models.BaseEntity) has been removed. It was hardly used, and removing it allows us to simplify some of the code paths.

Other Changes

• for memento transformations.

• Use to automatically convert queries to mementos.

• Automatically-generated .

2.5.0

• Define per entity.

2.4.0

• Apply custom setters when hydrating from the database. (Reverted in 2.5.3 for unintended consequences with things like password hashing.)

• This allows you to use scopes to perform query functions and return values. (If you do not want to return a custom value, return the QueryBuilder instance or nothing.)

2.3.0

2.2.0

• (first and find methods)

2.1.0

• Mapping foreign keys for relationships is now optional

Collections are what are returned when calling get or all on an entity. By default, it returns an array. Every entity can override its newCollection method and return a custom collection. This method accepts an array of entities and should return your custom collection.

QuickCollection is a custom collection included in Quick as an extra component. It is a specialized version of CFCollection. It 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.

To use collections you need to install cfcollection and configure it as your as your newCollection.

Here's how you would configure an entity to return a QuickCollection.

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 chapter.

Usage

A hasMany relationship is a one-to-many relationship. For instance, a User may have multiple Posts.

The first value passed to hasMany

What's the difference between `posts()` and `getPosts()`?

Answered by

Usage

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.

Signature

Returns a polymorphicBelongsTo relationship between this entity and the entity defined by relationName.

Visualizer

Usage

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:

If your parent entity does not use id as its primary key, or you wish to join the child entity to a different column, you may pass a third argument to the belongsTo method specifying your parent table's custom key.

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.

withDefault

HasOne relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

Signature

Returns a HasOne relationship between this entity and the entity defined by relationName.

Visualizer

Debugging a Single Query

toSQL

Returns the SQL that would be executed for the current query.

The bindings for the query are represented by question marks (?) just as when using queryExecute. qb can replace each question mark with the corresponding cfqueryparam-compatible struct by passing showBindings = true to the method.

tap

Executes a callback with the current entity passed to it. The return value from tap is ignored and the current entity is returned.

While not strictly a debugging method, tap makes it easy to see the changes to an entity after each call without introducing temporary variables.

Debugging All Queries

cbDebugger

Starting in 2.0.0 you can view all your Quick and qb queries for a request. This is the same output as using qb standalone. This is enabled by default if you have qb installed. Make sure your debug output is configured correctly and scroll to the bottom of the page to find the debug output.

Additionally, with Quick installed you will see number of loaded entities for the request. This can help identify places that are missing pagination or relationships that could be tuned or converted to a subselect.

LogBox Appender

Quick is set to log all queries to a debug log out of the box via qb. To enable this behavior, configure LogBox to allow debug logging from qb's grammar classes.

Interception Points

ColdBox Interception Points can also be used for logging, though you may find it easier to use LogBox. See the documentation for or Quick's for more information.

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:

Quick bundles in the excellent Mementifier library to handle converting entities to mementos. This gives you excellent control over serialization using a this.memento struct on the entity and passing in arguments to the getMemento function.

this.memento

By default, Quick includes all defined attributes as includes. You can change this or add other Mementifier options by defining your own this.memento struct on your entity. Your custom this.memento struct will be merged with Quick's default, so you can only define what changes you need.

Here is the default Quick memento struct. It is inside the instanceReady() lifecycle method in this example because retrieveAttributeNames() relies on the entity being wired (though not loaded); it is not otherwise necessary to put this.memento inside instanceReady().

getMemento Arguments

You can also control the serialization of a memento at call time using Mementifier's getMemento arguments.

Custom getMemento

If this does not give you the control you need, you can further modify the memento by overriding the getMemento function on your entity. In this case, a $getMemento function will be available which is the Mementifier function.

asMemento

Sometimes when retrieving entities or executing a Quick query, you already know you want mementos back. You can skip the step of calling getMemento yourself or mapping over the array of results returned by calling asMemento before executing the query. asMemento takes the same arguments that getMemento does. It will pass those arguments on and convert your entities to mementos after executing the query. This works for all the query execution methods - find, first, get, paginate, etc.

$renderData

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. By default this will call getMemento().

QuickCollection also defines a $renderData method, which will delegate the call to each entity in the collection and return the array of serialized entities.

Usage

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

The inverse of polymophicHasMany is polymorphicBelongsTo.

Signature

Returns a polymorphicHasMany relationship between this entity and the entity defined by relationName.

Visualizer

Usage

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

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.

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.

By default, if you have a key in the struct that doesn't match a property in the entity the update method will fail. If you add the optional argument ignoreNonExistentAttributes set to true, those missing keys are ignored. Now you can pass the rc scope from your submitted form directly into the update method and not worry about any other keys in the rc like event that would cause the method to fail.

updateOrCreate

Updates an existing record or creates a new record with the given attributes.

updateAll

Updates matching entities with the given attributes according to the configured query. This is analagous to

fresh

Retrieves a new entity from the database with the same key value as the current entity. Useful for seeing any changes made to the record in the database. This function executes a query.

refresh

Refreshes the attributes data for the entity with data from the database. This differs from fresh in that it operates on the current entity instead of returning a new one. This function executes a query.

Usage

A hasOneThrough relationship is either a many-to-one or a one-to-one relationship. It is used when you want to access a related entity through one or more intermediate entities. For instance, a Team may have one latest Post through a User.

The only value needed for hasOneThrough is an array of relationship function names to walk through to get to the related entity. The first relationship function name in the array must exist on the current entity. Each subsequent function name must exist on the related entity of the previous relationship result. For our previous example, members must be a relationship function on Team. posts must then be a relationship function on the related entity resulting from calling Team.members(). This returns a hasMany relationship where the related entity is User. So, User must have a posts relationship function. That is the end of the relationship function names array, so the related entity resulting from calling User.posts()

This approach can scale to as many related entities as you need. For instance, let's expand the previous example to include an Office that houses many Teams.

withDefault

HasOneThrough relationships can be configured to return a default entity if no entity is found. This is done by calling withDefault on the relationship object.

Called this way will return a new unloaded entity with no data. You can also specify any default attributes data by passing in a struct of data to withDefault.

Signature

Quick allows you to hook in to multiple points in the entity lifecycle. If the event is on the component, you do not need to prefix it with quick. If you are listening to an interception point, include quick at the beginning.

quickInstanceReady

Fired after dependency injection has been performed on the entity and the metadata has been inspected.

interceptData structure

quickPreLoad

Fired before attempting to load an entity from the database.

interceptData structure

quickPostLoad

Fired after loading an entity from the database.

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

Usage

A hasManyThrough relationship is either a one-to-many or a many-to-many relationship. It is used when you want to access related entities through one or more intermediate entities. 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 only value needed for hasManyThrough is an array of relationship function names to walk through to get to the related entity. The first relationship function name in the array must exist on the current entity. Each subsequent function name must exist on the related entity of the previous relationship result. For our previous example, userPermissions must be a relationship function on User. permission must then be a relationship function on the related entity resulting from calling User.userPermissions(). This returns a hasMany relationship where the related entity is UserPermission. So, UserPermission must have a permission relationship function. That is the end of the relationship function names array, so the related entity resulting from calling UserPermission.permission()

Let's take a look at another example. HasManyThrough relationships can go up and down the relationship chain. Here's an example of finding a User's teammates

The inverse of hasManyThrough is either a belongsToThrough or a hasManyThrough relationship.

This approach can scale to as many related entities as you need. For instance, let's expand the previous example to include an Office that houses many Teams.

This next example can get a little gnarly - you can include other hasManyThrough relationships in a hasManyThrough relationship function names array. You can rewrite the officemates relationship like so:

As you can see, this is a very powerful relationship type that can save you many unnecessary queries to get the data you need.

Signature

To get started with Quick, you need an entity. You start by extending quick.models.BaseEntity.

That's all that is needed to get started with Quick. There are a few defaults of Quick worth mentioning here.

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, override thekeyType function and return the desired key type from that function.

Quick ships with the following key types:

• AutoIncrementingKeyType

• NullKeyType

• ReturningKeyType

keyType can be any component that adheres to the keyType interface, so feel free to create your own and distribute them via ForgeBox.

Attributes

You specify what attributes are retrieved by adding properties to your component.

Now, only the id, username, and email attributes will be retrieved.

Persistent

To prevent Quick from mapping a property to a database column add the persistent="false" attribute to the property. This is needed mostly when using dependency injection.

Column

If the column name in your table is not the column name you wish to use in Quick, you can specify the column name using the column metadata attribute. The attribute will be available using the name of the attribute.

Null Values

To work around CFML's lack of null, you can use the nullValue and convertToNull attributes.

nullValue defines the value that is considered null for a attribute. By default it is an empty string. ("")

convertToNull is a flag that, when false, will not try to insert null in to the database. By default this flag is true.

Read Only

The readOnly attribute will prevent setters, updates, and inserts to a attribute when set to true.

SQL Type

In some cases you will need to specify an exact SQL type for your attribute. Any value set for the sqltype attribute will be used when inserting or updating the attribute in the database. It will also be used when you use the attribute in a where constraint.

Casts

The casts attribute allows you to use a value in your CFML code as a certain type while being a different type in the database. A common example of this is a boolean which is usually represented as a BIT in the database.

Two casters ship with Quick: BooleanCast@quick and JsonCast@quick. You can add them using those mappings to any applicable columns.

Custom Casts

The casts attribute must point to a WireBox mapping that resolves to a component that implements the quick.models.Casts.CastsAttribute interface. (The implements keyword is optional.) This component defines how to get a value from the database in to the casted value and how to set a casted value back to the database. Below is an example of the built-in BooleanCast, which comes bundled with Quick.

Casted values are lazily loaded and cached for the lifecycle of the component. Only cast values that have been loaded will have set called on them when persisting to the database.

Casts can be composed of multiple fields as well. Take this Address value object, for example:

This component is not a Quick entity. Instead it represents a combination of fields stored on our User entity:

Noticed that the casted address is neither persistent nor does it have a getter or setter created for it.

The last piece of the puzzle is our AddressCast component that handles casting the value to and from the native database values:

You can see that returning a struct of values from the set function assigns multiple attributes from a single cast.

Insert & Update

You can prevent inserting and updating a property by setting the insert or update attribute to false.

Formula, Computed, or Subselect properties

Quick handles formula, computed, or subselect properties using query scopes and the addSubselect helper method.

Multiple datasource support

Quick uses a default datasource and default grammar, as described . 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@qb, PostgresGrammar@qb, SqlServerGrammar@qb and OracleGrammar@qb. Please check the for additional options.

Comparing Entities

You can compare entities using the isSameAs and isNotSameAs methods. Each method takes another entity and returns true if the two objects represent the same entity.

When querying an entity, you may want to restrict the query based on the existence or absence of a related entity. You can do that using the following four methods:

has

Checks for the existence of a relationship when executing the query.

By default, a has constraint will only return entities that have one or more of the related entity.

An optional operator and count can be added to the call.

Nested relationships can be checked by passing a dot-delimited string of relationships.

doesntHave