In my ColdFusion applications, I always have a “Data Access Layer,” often referred to as a “DAL”. This layer abstracts the persistence mechanisms for the application; and, encapsulates the complexities of interacting with low-level data inputs and outputs (I/O). While I love using a DAL, I’ve never felt confident in how I manage requests for records with a unique identifier. And, in fact, my approach changes with new implementations. This weekend, however, after listening to the JS Party episode on Postgres.js, I’ve decided to always return some form of collection from the data access layer in my ColdFusion applications.
Historically, I’ve thought about “filtering” and “direct access” as two distinctly different things:
Filtering: Get me records based on some column values. Zero or more records may match this filter; so, returning zero rows is a legitimate outcome.
Direct Access: Get me the record with a given unique value (ex, primary key). A single record is expected to exist; so, returning zero rows is an invalid request.
When filtering, my data access layer will happily return an empty collection if no records match. However, when using a direct access approach, I will often throw an error if the record with the given key cannot be found:
component {
/**
* Pseudo-code example of a direct-access method.
*/
public struct function getByID( required numeric id ) {
if ( ! cacheIndex.keyExists( id ) ) {
throw( type = "DAL.NotFound" );
}
return( cacheIndex[ id ] );
}
/**
* Pseudo-code example of a filter method.
*/
public array function getByUserID( required numeric userID ) {
var results = cache.filter(
( record ) => {
return( record.userID == userID );
}
);
return( results );
}
}
As you can see, in the “get by ID” method, if the record with the ID (primary key) doesn’t exist, I am throw()ing an error. I believe I started following this pattern for two reasons:
I really like the idea that a method (whether inside or outside of the DAL) throws an error when it cannot do what it was asked to do – ie, when it cannot uphold its “contract”. In this case, the method was asked to retrieve a specific object; and, it cannot do that, so it must throw an error.
In some cases, it made the calling context easier to code because I never had to check for
isNull()on the resultant value. I always knew that I would either get the record in question; or, the code would throw an error and the control-flow would return to higher-up in the call-stack. This allowed my calling code to be rich with “logic” and free of error-handling noise.
The “get by UserID” method, on the other hand, will happily return an empty collection if none of the records have the given userID value. This is because there is no “contract” that this method should return records.
At first, this dichotomy worked well. But, over time, I’ve run into too many situations where having the “direct access” method throw an error was actually creating friction, not removing it. Then last week, I had to create a data access layer (DAL) that abstracted a remote API, not a database; and, translating 404 Not Found HTTP response codes into throw() statements just felt super dirty because it starting to build what felt like business logic into my data access layer.
Circling back to the JS Party podcast, the hosts discussed the fact that the SQL library for Postgres will always return an array, even when no rows can be found. And, more importantly, they talked about how nice that was. This is exactly what the CFQuery object and the queryExecute() method do in ColdFusion; but, for some reason, hearing people talk about the comforting predictability of this “symmetrical access pattern” in a different context gave my brain a moment to think about it from a fresh perspective.
And, what I’ve decided is that, going forward, my data access layer (DAL) will always return a collection when asked for records, even when a single record is expected:
component {
/**
* Pseudo-code example of a direct-access method.
*/
public array function getByID( required numeric id ) {
if ( cacheIndex.keyExists( id ) ) {
return( [ cacheIndex[ id ] ] );
}
return( [] );
}
/**
* Pseudo-code example of a filter method.
*/
public array function getByUserID( required numeric userID ) {
var results = cache.filter(
( record ) => {
return( record.userID == userID );
}
);
return( results );
}
}
In this approach, if there is no record with a given unique key, the DAL just returns an empty array. This means that the calling context – the associated “service layer” object – will have to check for this case; but, this will make all of my service layer / data access layer interactions consistent. And, I believe that this consistency will make my code simpler and more predictable in the long-run.
I think what I’m also coming to realize is that “filter” methods and “direct access” methods are NOT fundamentally different. They are both, ultimately, just filtering methods. What makes them different is what the calling context expects, not what the data access layer expects. In fact, we can code both patterns into a single “get by filters” method:
component {
/**
* Pseudo-code example of a filter method.
*/
public array function getByFilters( required struct filters ) {
var results = cache.filter(
( record ) => {
// Filter on id.
if ( filters.keyExists( "id" ) && ( record.id != filters.id ) ) {
return( false );
}
// Filter on userID.
if ( filters.keyExists( "userID" ) && ( record.userID != filters.userID ) ) {
return( false );
}
// If none of the filters excluded the record, allow it.
return( true );
}
);
return( results );
}
}
When we code it like this, it becomes much more clear that “get by ID” and “get by filter” are fundamentally the same: they are merely ways to winnow down records to those that match. Whether or not the results of the filtering are “valid” is a matter for the “service layer”, not the “data access layer”.
As always, programming is a journey. And, it’s possible that this new path in my journey will prove to be problematic; and, if so, I’ll address it then. But, for the time being, I’m going to start programming my data access layers to return arrays, even empty one; and, we’ll see how it goes.
This use of collections only applies to DAL methods that aim to return records. This does not apply to DAL methods that return aggregates and existence checks. Those methods can continue to return numbers and Booleans.
