Select Page

It’s OK To Be Super Explicit In Your Data Access Method Names

Cyberdime
Published: June 14, 2022

This post is mostly a note to myself – a sort of “permission slip” to relax the previous constraints of my mind. The other day, I was writing an Update method in a data access object (DAO) within my ColdFusion application. This update method was updating an optional relationship within my domain model. Meaning, the target row wasn’t guaranteed to exist. As such, I decided to author the underlying SQL statement as an Upsert method that would use the ON DUPLICATE KEY UPDATE semantics. This would allow me to either update the existing row or create a new row on-the-fly. Technically, this worked like a charm; but, emotionally, I felt deeply conflicted.

ASIDE: In the following discussion, I am using “Thing” as a placeholder for any arbitrary domain concept.

The problem I was “feeling” was that the method was called updateThing(). Now, there’s nothing wrong with calling a method updateThing() – I use that form of method name all the time in my data access layer. However, with all my other “update” methods, most of the arguments are optional; and, a given column is only updated if the relevant argument is provided. As such, my CFQuery UPDATE SQL statements usually look a lot like this (truncated):

component {
	public void function updateThing(
		required numeric id,
		string valueOne,
		string valueTwo,
		string valueThree
		) {
		```
		<cfquery name="local.results" result="local.metaResults">
			UPDATE
				thing
			SET
				<cfif arguments.keyExists( "valueOne" )>
					valueOne = <cfqueryparam value="#valueOne#" sqltype="varchar" />,
				</cfif>
				<cfif arguments.keyExists( "valueTwo" )>
					valueTwo = <cfqueryparam value="#valueTwo#" sqltype="varchar" />,
				</cfif>
				<cfif arguments.keyExists( "valueThree" )>
					valueThree = <cfqueryparam value="#valueThree#" sqltype="varchar" />,
				</cfif>
				/* No-op assignment here to make the trailing commas consistent. */
				id = <cfqueryparam value="#id#" sqltype="bigint" />
			WHERE
				id = <cfqueryparam value="#id#" sqltype="bigint" />
		</cfquery>
		```
	}
}

With an Upsert statement, however, the arguments can’t be optional because they need to be able to create a new, fully-formed row if the target row doesn’t exist. As such, all Upsert method arguments have to be marked as required.

And that’s where my brain started to explode. I couldn’t, in good conscience, author an update() method that completely broke from the standard pattern of optional arguments. It was too much of a deviation from any reasonable expectation that another developer would have when consuming this method.

The name of the method wasn’t telegraphing the intent of underlying query.

So my brain finally said to me: THEN NAME IT SOMETHING ELSE you big doofus!

And so, I changed it to upsertThing(). And my brain said, Yay! Because, now, the method was doing the thing that it said it was doing and it became clear as to why all the arguments were marked required.

component {
	public void function upsertThing(
		required numeric ownerID,
		required string valueOne,
		required string valueTwo,
		required string valueThree
		) {
		```
		<cfquery name="local.results" result="local.metaResults">
			INSERT INTO
				thing
			SET
				/* !!! Owner ID column might cause a key-conflict. !!! */
				ownerID = <cfqueryparam value="#ownerID#" sqltype="bigint" />,
				valueOne = <cfqueryparam value="#valueOne#" sqltype="varchar" />,
				valueTwo = <cfqueryparam value="#valueTwo#" sqltype="varchar" />,
				valueThree = <cfqueryparam value="#valueThree#" sqltype="varchar" />
			ON DUPLICATE KEY UPDATE
				valueOne = <cfqueryparam value="#valueOne#" sqltype="varchar" />,
				valueTwo = <cfqueryparam value="#valueTwo#" sqltype="varchar" />,
				valueThree = <cfqueryparam value="#valueThree#" sqltype="varchar" />
		</cfquery>
		```
	}
}

This was an important moment of clarity for me because, normally, my brain craves consistency. As such, I wanted this data access method to be named the same thing as other data access methods. But, it’s not the same thing. And that’s OK.

What I’m seeing now is that data access methods that mutate data should never be surprising. They should say exactly what they are going to do; and then do only what’s printed on the tin.

So, for example, in my previous post on handling MySQL key conflicts in Lucee CFML, I had a method that would either INSERT a new row or return the id (primary key) of an existing row:

component {
	public numeric function createThing( required string value ) {
		```
		<cfquery name="local.results" result="local.metaResults">
			INSERT INTO
				thing
			SET
				value = <cfqueryparam value="#value#" sqltype="varchar" />
			ON DUPLICATE KEY UPDATE
				id = LAST_INSERT_ID( id ) -- Store existing ID into "generatedKey".
			;
		</cfquery>
		```
		return( metaResults.generatedKey );
	}
}

The problem with this method, as I now see it, is that it’s not telling me what it does. It’s not “creating a Thing” – it’s “creating a Thing or returning the id of the existing Thing”. As such, it should be renamed to literally say as much:

createThingOrReturnExistingID()

It’s a bit of a mouth-full, but so what? I already firmly believe that your data access layer should have as many methods as you find helpful. So, there’s nothing inherently wrong with having a few more “create” methods that are more explicit in how they differentiate from one another.

This is a personal revelation, but it’s certainly not a new idea. If you look in the MongoDB Java API, for example, they have all sorts of methods that perform very specific actions: updateOne(), updateMany(), findAndModify(), findOneAndUpdate(), findOneAndReplace(), bulkWrite(), etc. So, there’s much precedence for being explicit; and, my brain is finally getting on the band-wagon.

Want to use code from this post?
Check out the license.

Source: www.bennadel.com