On Returning SQL Queries As Arrays In ColdFusion
In my ColdFusion applications, I use a tiered architecture in which the inner-most layer is the Data Access Layer (DAL). In all the years that I've been using ColdFusion, this DAL has returned database records using the ColdFusion-native Query object. The Query object is a huge part of what has, historically, made ColdFusion so effortless to work with. But, as I've continued to evolve my programming practices, I've leaned very heavily into using Structs and Arrays. As such, I decided that Dig Deep Fitness - my ColdFusion fitness tracker - would only ever return Arrays, not queries, from the Data Access Layer. After several months of working this way, I thought it might be nice to stop and reflect on the practice.
In ColdFusion, switching from a Query object over to an Array for a SQL recordset is as simple as adding returnType="array" to the CFQuery tag. So, as an example, in my Dig Deep Fitness ExerciseGateway.cfc component, I have a method titled, getExercisesByFilter(), which returns zero-or-more records based on the provided filter criteria:
ASIDE: Normally, my data access layer would be coded using Tag Islands such that my component could be authored in
CFScriptwhile my SQL would get wrapped in the supreme developer ergonomics of theCFQuerytag. Unfortunately, tag islands are only supported in Lucee CFML and my Dig Deep Fitness app is being written in Adobe ColdFusion. As such, I have decided to make all of my data access components Tag-based. I'd rather useCFQuerytags and eat the cruft of the other tag-based constructs than have to deal with thequeryExecute()string-based nonsense.
<cfcomponent>
<cffunction name="getExercisesByFilter" returnType="array">
<cfargument name="id" type="numeric" required="false" />
<cfargument name="userID" type="numeric" required="false" />
<cfif (
isNull( id ) &&
isNull( userID )
)>
<cfthrow
type="SQL.ForbiddenSelect"
message="Query requires at least one indexed column."
/>
</cfif>
<cfquery name="local.results" result="local.metaResults" returnType="array">
/* DEBUG: lib.model.resistance.ExerciseGateway.getExercisesByFilter(). */
SELECT
e.id,
e.name,
e.description,
e.movementPlaneID,
e.movementLateralityID,
e.shoulderMovementTypeID,
e.elbowMovementTypeID,
e.wristMovementTypeID,
e.hipMovementTypeID,
e.kneeMovementTypeID,
e.ankleMovementTypeID,
e.spineMovementTypeID,
e.userID,
e.catalogID,
e.equipmentSettings,
e.notes,
e.isFavorite,
e.createdAt,
e.updatedAt
FROM
resistance_exercise e
WHERE
TRUE
<cfif ! isNull( id )>
AND
e.id = <cfqueryparam value="#id#" cfsqltype="cf_sql_bigint" />
</cfif>
<cfif ! isNull( userID )>
AND
e.userID = <cfqueryparam value="#userID#" cfsqltype="cf_sql_bigint" />
</cfif>
ORDER BY
e.id ASC
;
</cfquery>
<cfreturn results />
</cffunction>
</cfcomponent>
There's very little about the Data Access Layer that actually changes when I switch from returning queries to returning arrays. The returnType of the Function changes to array; and the returnType of the CFQuery tag changes to array; but, other than that, this code - including the SQL - is exactly the same as it would be historically.
The real change happens when it comes to consuming the data returned from the data access layer. In my ColdFusion applications, this is often the "Entity Service" layer. I don't do Object Oriented Programming (OOP); but, I do love to collocate "pure functions" around the cohesive set of methods that represent access to a single entity concept.
To look at both consuming a single row and multiple rows, let's look at my ExerciseService.cfc methods for returning an exercise based on id and for returning an exercise based on a userID. This code is truncated to hide parts irrelevant to this discussion:
component
output = false
hint = "I provide service methods for the resistance exercise entity."
{
// Define properties for dependency-injection.
property name="gateway" ioc:type="lib.model.resistance.ExerciseGateway";
property name="validation" ioc:type="lib.model.resistance.ExerciseValidation";
// ---
// PUBLIC METHODS.
// ---
/**
* I get the exercise with the given ID.
*/
public struct function getExercise( required numeric id ) {
var results = gateway.getExercisesByFilter( id = id );
if ( ! results.len() ) {
validation.throwNotFoundError();
}
return( asDto( results.first() ) );
}
/**
* I get the exercises associated with the given user.
*/
public array function getExercisesByUser( required numeric userID ) {
var results = gateway.getExercisesByFilter( userID = userID );
return( asDtoArray( results ) );
}
// ---
// PRIVATE METHODS.
// ---
/**
* I return the given results as a data-transfer-object (DTO).
*/
private struct function asDto( required struct results ) {
// The isFavorite flag is stored in the database as a Tiny Int. But, we don't want
// this implementation detail to "leak out" into the calling context. As such, we
// need to convert our isFavorite value to a real Boolean flag.
results.isFavorite = !! results.isFavorite;
return( results );
}
/**
* I return the given results as a array of data-transfer-objects (DTO).
*/
private array function asDtoArray( required array results ) {
return( results.map( asDto ) );
}
}
The ColdFusion component methods that directly consume the data access layer aren't that much different when consuming arrays vs. when consuming queries:
Instead of checking
.recordCount, they have to check.len().Instead of returning a 1-row query, they return
.first()(thereby plucking the first Struct out of the array-of-structs return by the DAL).
Notice, however, that my entity service layer doesn't transparently return the data from the data access layer. Instead it passes the data through a "Data Transfer Object" (DTO) conversion. The SQL database represents an implementation detail regarding data persistence. I don't want those details to leak out into the calling context.
For example, all my True/False flags are actually stored as Tiny Ints in the database. Which means, the isFavorite flag comes out of the data access layer as 1, not true. In many cases, this wouldn't matter since ColdFusion sees both 1 and true as "Truthy" values. However, this is not an implementation detail that I want to force on the rest of the application. As such, I use the DTO-conversion step to make sure that my entity service layer always returns data in the human-expected format.
ASIDE: You could easily make the argument that the Data Access Layer (DAL) should be performing the DTO conversion. The DAL represents an "abstraction". And, if anything, having the DAL return a "query artifact" is, in and of itself, a leaking of storage implementation details. That said, I simply prefer to keep my DAL as simple as possible, deferring this type of data-handling to the layer above the DAL. This is a preference - not a Truth.
Returning arrays (of structs) from the Data Access Layer makes for extremely easy DTO conversions. In a best case scenario - where all the data is already in the "correct" format - I can literally just return the data structures returned from the DAL. In this case, I am performing the one isFavorite flag conversion; but, even at that, having the data in an Array makes mapping over the records a one-liner:
return( results.map( asDto ) )
Here, I'm passing the asDto() method reference as the operator to the .map() function. ColdFusion will then iterate over the array, passing each Struct (record) to the asDto() operator, which will, in turn, perform the isFavorite mapping and return the resultant object.
CAUTION: When passing a "naked method" reference around, it loses its binding to the ColdFusion Component in which it was defined. As such, the
asDto()method - when passed to.map()- actually has no reference to theExerciseService.cfccomponent and wouldn't be able to call any of the other methods in the component from within its own logic. That said, I know that myasDto()method is a "pure function". As such, this is not a constraint that causes me any worry.
Overall, I must say that I've really enjoyed having my ColdFusion data access layer in Dig Deep Fitness return Arrays. Plus, with my ever-increasing use of .map() and .filter() methods in my general programming practices, having Arrays (of Structs) coming out of the DAL just fits more cleanly with the way that I'm usually thinking about data.
I suspect that all of my ColdFusion data access layers will be returning arrays going forward!
Want to use code from this post? Check out the license.
Reader Comments
I'm curious if you still use this approach if needing to output the resultset with grouping? While I'm also more comfortable using
.map,.filter, etc. with array objects than their query equivalents, I find that outputting grouped data to be cleaner with a query resultset.Apart from this, I'm also curious about your preference in using syntax like
return( results );vs.return results;?@Andrew,
Great question. When it comes to grouping, I'll usually use some sort of Lodash-inspired functions to help me slice-and-dice arrays. Probably the ones that I use most often are:
utilities.indexBy( collection, key )utilities.groupBy( collection, key )I find that having these two functions gets me just about anywhere I need to be.
Oh, and re:
return( value ), I tend to do that because I never like to have ungrouped compound values. Meaning, I'll eventually have something like:return( "Hello " & user.name )... which I include the parens to visually isolate the
&operator. And, since I do it for that, I just started doing it for all returns to keep it consistent.But, to be clear, it is 100% personal preference.
@Andrew,
I actually have a post talking about grouping queries, if you're interested:
www.bennadel.com/blog/4205-ask-ben-grouping-query-output-by-column-value-in-coldfusion.htm
Reading this made me think about how we pull data from SQL server into CF.
We use SQL server's inbuilt JSON and XML return types. Super handy and FAST. Means we can get hierarchical data back from the DL. Before we had to do a lot of munging data
We never deal with recordsets. We get XML or JSON back from SQL.
I think you're using MySQL, it might offer something similar.
Anyway, in case it helps.
I used to have QoQ everywhere, but probably have to join mainstream with array methods.
@Rodyon,
I don't think there's anything wrong with Query of Queries (QoQ). In fact, the Lucee CFML team just made some major improvements to the speed of QoQ in recent updates to their CFML runtime. SQL is a wonderfully expressive language; and, QoQ just brings more of that magic into your application 🙂 All to say, don't feel bad about QoQ - it's just another tool to get things done.
@Dave,
That's an interesting approach. Yes, I am using MySQL, and I do sometimes use
JSON_ARRAYAGG()to pull back multiple rows as a JSON payload in a derived column; but, when I'm dealing with the top-level set of records, I've just been using the standard recordset structure.When you say that you get back "hierarchical" data, so you mean that you can get nested-loop kinds of data returned as nested children in the resultant payload?
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →