Book Review: SQL Antipatterns

SQL is an interesting yet temperamental tool in the programmer's kit. You can use it to find amazing insights in your data, but you can watch a poorly formed query grind your application to a halt. SQL Anitpatterns wants to help you avoid the latter by identifying common pitfalls when designing and using databases.

The book identifies twenty-four antipatterns and breaks each down in the same formula -- an objective one is trying to achieve, the antipattern typically used as a solution, how to spot this antipattern, the legitimate uses of the antipattern, and alternative solutions. The author introduces each antipattern with a brief example scenario followed by a summary of the objective people are typically trying to achieve.

Next, examples of the antipattern are given with an explanation of why you would not want to do this. Even with examples, the author provides other ways to recognize the antipattern; most of these are questions you are likely to ask or hear colleagues ask. If you hear or ask "How do I do X", that would be a good opportunity to pull this book off the shelf, give it a quick skim, and possibly avoid a pitfall.

However, the author recognizes there are legitimate uses of each antipattern and presents them before the solutions to avoid it. This allows you to decide if this is a pitfall to avoid or a necessary evil in your application. Finally, the appropriate solutions are presented with attention given to how the solutions might differ between database vendors.

Easy to read and digest, this is the worthy reference book to add to your shelf. As you extend or update a database and related application code, you will find yourself reaching for it to reduce the likelihood of being paged at night by a misbehaving database.

Photo by Karolina Szczur on Unsplash

Fresh Baked Roles with Zend Permissions RBAC

As our Traackr application has grown, so has our need to restrict access to features or pieces of features. Recently, our search feature added new filters to use. However, some of these filters are only meant to be available to enterprise customers. This was not a new problem for us; our analytics feature added a new report type, one only enterprise customers should have.

Feature flags were our initial solution to both efforts. This worked well enough for our analytics use case. Our customer success team attaches an account to the feature flag, and the account can view the new report. Cracks started showing in this approach when applied to our search effort.

Let's say we added four new filters to search. That is four new feature flags to create, not a terrible burden from a coding standpoint. But our poor customer success team... They now have to go account by account and flip four new switches to make sure the account has access to the search filters granted to them by their subscription. Yuck...

Role-based Access Control to the Rescue!

During one of our hack weeks, I had tinkered with the Zend Permissions RBAC package without a solid use case in mind. It was just an interesting concept that crossed my path at the time. Now, it seemed a perfect use case for it was in front of me. Before we dive into details, we should first answer an important question.

What is role-based access control (RBAC)?

A standard access control list (ACL) focuses on objects and who can act on them. Your computer's file system is an example of an ACL. Using a Linux file system, each file and directory is an object. The ACL for a file contains three entities -- the owner of the object, the group associated with the object, and everyone else. A feature in our application would be an object, and the feature flag ended up being the ACL attached to it.

The statement ACL makes is "This object is associated with these people, and each person can access the object in specific ways". Phrased in the context of our application, "this feature can be accessed by the following accounts."

How does RBAC differ then? I'll let the package's documentation make the initial introduction:

RBAC differs from access control lists (ACL) by putting the emphasis on roles and their permissions rather than objects (resources).

RBAC makes the statement "These people are associated with these roles. These roles grant them these permissions." Again, in the context of our application, the statement becomes "This account is associated with this role. This role allows them access to these features." This concept has an immediate benefit for us. Accounts can be granted a specific role. As we add a new feature, we update the role to have new permissions for the feature, and every account is automatically able to use the feature. No longer do we need to edit accounts individually.

Baking up our roles

Let's start building this out! We first need to define our roles and the permissions associated with them. Here is a stripped down version of our class to manage that:

Our "Enterprise Subscription Package" starts with a parent role representing just the subscription for the account. It contains no feature specific permissions and would contain more general permissions instead -- number of users allowed in the account, number of concurrent logins, quotas, etc. A child "Search" role is added to the subscription role. Within the "Search" role we grant permission (search) to use the search feature and grant permission to use the topic (search.topics), brand (search.brands), and location (search.locations) filters. The dot notation is our own convention for role and permissions names. Names can be any string, but this gives us a visual hierarchy of the pieces of a feature.

May I?

When a customer logs into our site, we check the subscription attached to their account and attach the appropriate Role object to their session. Our search page must now check they can access the page, and if so, what filters they should see.

That smells good...

The sample checks above may not seem much different than what you would do with features flags. We are just replacing "are they on this feature flag" with "do they have this role and permission". The benefit here is in the ease of maintenance, especially for managing accounts and features. Using RBAC, a new feature is added to a role, that role attached to a subscription, and that role addition / subscription update is shipped with the feature. Every account tied to that subscription has immediate access to the feature. Our customer success team no longer has to take time to turn the feature on for an account.

RBAC has also made it easy to extend the system even further. As an example, we built an override and exclusion system on top of it. If a customer abuses the location search feature, we can shut it off just for them. Need an incentive for a customer to upgrade? We can override the permissions to allow them access to a feature for a short time.

We will be migrating our existing feature flagged based items to RBAC. Using it has also sparked ways to improve other areas of our code, particularly user based permissions. Our code and process has been much more manageable since we baked RBAC into it. But don't take my word; give the recipe a try for yourself.

Yoda conditionals... Change my mind I should?

I had another blog topic in mind but ran across the article "The Mistakes I Made As a Beginner Programmer". The entire article is worth reading, but this item leaped off the page at me:

... avoid Yoda conditions and assignments within conditionals.

I have encountered this statement in other articles and in the coding standards for various projects. Reduced readability is the criticism typically levied against the pattern, though they seldom elaborate on why readability suffers. I picked up the habit of using Yoda conditionals from a former mentor, but it has never been a practice I strongly advocated using. Perhaps, it is time to reexamine if there is any value in using it at all.

What exactly is a "Yoda conditional"? Rather than saying this

a Yoda conditional reverses the expression:

It is a coding practice where you always phrase a conditional statement with the "constant" portion, 66 in this example, first. The goal of the practice is to avoid errors where you unintentionally perform variable assignment inside the conditional rather than making an actual comparison.

The first conditional will always pass because variable assignment results in a truth-y value; your clone troopers will exterminate the Jedi on every order given. Having Yoda on the field though causes an immediate disturbance in the Force. Hidden this subtle bug is not.

There is a clear benefit here, but is it enough to trump the benefits of readable code? Is there truly a problem with it being less readable? On the surface, there appears to be no issue. You see the "if" and know some sort of comparison will be made. What does it matter if 66 is listed first?

Our brains do not read code the way a machine does. It makes no difference to the machine what the order is. However, say the conditional out loud. "If sixty-six equals the order variable." "If the order variable equals sixty-six." The latter falls into more familiar patterns of speech. Reading one slightly awkward statement may not be a big deal, but try reading more and more of them while debugging a large code base. It will tax your mental capacity.

My development environment also includes a bevy of code sniffers and linters. They constantly remind me to use the triple equals for equality checks along with warnings when assigning variables inside a conditional. I am less likely to fat finger away an equal sign and end up with an assignment rather than a conditional check.

So... Hrm... Yeah, I think I finally agree; the benefit of using Yoda conditionals is not sufficient enough compared against code readability. I would much rather have my code be easy for people to read and understand. No longer use this pattern will I. Wait... Apologies... I will no longer use this pattern. There, that is much better.