Home Home Posts Rants about IT DevOps Stuff I'm working on

Duplicating database constraints in code

Arseni Mourzenko
Founder and lead developer, specializing in developer productivity and code quality
105
articles
November 29, 2016
Tags database 2

When I review database models for RDBMS, I'm usually surprised to find little to no constraints. For instance, percentage is often stored in a column of type int (while tinyint would be more appropriate) and there is no CHECK constraint to restrict the value to 0..100 range.

In my own projects where I use relational databases, I have a habit to define a very strict schema, in order to ensure the data is valid as much as possible. When I adopt a more lenient attitude is when performance is at stake, usually when it comes to preventing proper denormalization. In all cases, when I deal with data as simple as a percentage, there is no way I wouldn't introduce a limit constraint on it within the database itself.

When talking with other developers about constraints, they usually tell that:

  • The constraints are enforced at application level, and there is no need to duplicate those rules in the database, as it violates the single source of truth principle.

  • Having a constraint which would be violated by the application brings no benefit. If one has to handle the corresponding exceptions in the data access layer, it would make the application overly complex.

There seems to be an important misunderstanding on both points, and I'll explain it in this article.

SSOT and duplicated constraints

Imagine you're writing a web application which has a form. In this form, one of the fields requires the user to specify her age. It is important to handle a case where the user specifies something which is not a number, or a negative number, or zero, or a number superior to an arbitrary limit of one hundred and twenty.

What would happen if you write this application with no fancy frameworks (for instance, you can use the basic functionality of PHP server-side, and just jQuery on client-side)?

  • You'll do the value checks on server side and report the error to the user if the input doesn't met your expectations. This could be the only place where you do the checks, but this is necessarily the place where you will do it (unless you have an absolute trust in your users, which is, well, slightly problematic.)

  • You might also do the checks on client side in order to make your application more reactive and prevent useless form submissions. Here, you got your first duplication: now if the requirements change in order to handle users aged one hundred and twenty-one, you have to make a change not in one location, but two.

Or maybe you know some fancy framework such as ASP.NET MVC which does all the hard work for you: the only thing you need to do is to specify the rules in a single place, such as a data annotation attribute, and the framework produces the corresponding C# and JavaScript code for you.

So:

  1. There will always be a validation at the server level,
  2. There could be a duplicated validation at the client level,
  3. If tools allow, it's better to avoid duplication by specifying the validation rules once, and letting the tools propagate them both at the server and the client level.

This is exactly what should happen when using the database.

The database doesn't have to trust the applications, so specifying constraints at database level makes perfect sense. In my previous percentage example, this means having those constraints:

alter table [Shop].[Rebate]
add constraint [CK_PercentageRange] check ([Amount] <= 100)

The low limit shouldn't be checked as soon as the type of the Rebate column is tinyint (one byte, values ranging from 0 to 255).

From there, if you interact with the database directly (for instance by writing SQL queries by hand), then the database logic will be duplicated. This means duplicating the structure of the database itself, would it be tables, views, columns or column types, but also its constraints, which is often not very different from types themselves. In the previous example, this would lead to classes such as:

public class Percentage
{
    private readonly int value;

    public Percentage(int value)
    {
        if (value < 0 || value > 100) throw new ArgumentOutOfRangeException(...);

        this.value = value;
    }

    public int Value
    {
        get { return this.value; }
    }
}

public class Rebate
{
    public Percentage Amount { get; set; }
}

If manual duplication is annoying, then code generation tools should exist, transposing the entire database schema to data structures in code. And I'm not talking here about ORMs, which hijack the access to the database, but about a tool which only constructs the data structures and their corresponding constraints. In the previous example, the issue is that the tool won't probably be able to guess that it should create a Percentage class, and will push the validation logic into the Rebate class itself, leading eventually to severe code duplication. While this is not an issue for generated code, it is still a heavy limitation if we care about encapsulation and the single responsibility principle.

Handling the violation exceptions

In a language such as C#, what is the difference between exceptions and assertions?

  • An exception being thrown indicates that something exceptional happened. For instance, a network connection went down. Or we went out of memory. Or the application was denied access to a file it was about to read. Or the user is attempting to divide a number by zero.

  • An assertion being violated indicates that the developer screwed up. Here, we are not in the field of exceptional cases; here, it's more about “this shouldn't have happened”. In other words, an assertion indicates a bug in the system. For instance, it wouldn't make sense to assert that a file exists, and it wouldn't make sense to assert that the user entered a non-zero as a divisor. However, it would make sense to assert that a value which was previously checked for being different from zero is not equal to zero, because here, we are not checking user's input; we are checking the internal logic of the application itself.

While violations of database constraints will usually be reported as exceptions by the SQL library, it is essential to understand that those violations are not exceptional per se: instead, they indicate a critical bug in the system—either the code was flawed, leading to a situation where it violated the constraint, or the constraint itself is wrong (which could happen for instance if the change in requirements was reflected in code, but not in the database.) Therefore, this situation should be treated exactly as a violated assertion: it should be logged, and then the code should be fixed.

This means that there is nothing to handle at any application level here. The database layer is unable to handle this exception: it won't, for example, retry the query—something it could do for instance if the exception reports that the database is temporary unavailable. Similarly, the business layer doesn't know how to handle the exception either. Nor would it be possible at the presentation layer. The exception will simply reach the global handler which will log it and let the application crash, eventually gracefully.

But NoSQL...

And then there are proponents of NoSQL who claim that there are no CHECK constraints in MongoDB or ElasticSearch, and that NoSQL databases are great without them, and that logically, there is no use for constraints in RDBMS.

While I find the logic itself particularly disturbing (I mean, should we start making all variables in C# dynamic just because Python is a dynamically-typed language and works great?), there is an important aspect which those developers forget: the way NoSQL databases are often used today.

In most cases where a NoSQL database is involved, there is either a single application which uses it, or a service in an SOA system.

With SOA, the situation is very simple. The same constraints exist, but at a different level. Instead of being within the database, they are situated at the level of the service, in code. The result is the same: a client, instead of connecting to a RDBMS, connects to a REST/SOAP service as if it was a database. All the applications interact exclusively through the endpoint, which also controls the data which comes in.

In a case of a single application, the logic is moved to the data access layer. Direct access to the data happens rarely, if at all, and scripts such as cron jobs usually tend to reuse the data access layer of the application itself. This doesn't mean that problematic cases where the wrong data reaches the database are impossible, it simply means that the habit of playing with data directly as developers often do with RDBMS is not there yet.

But this is business logic...

One point I'm used to hear from colleagues is that constraints are business logic, and business logic doesn't belong in database.

This is a valid point for stored procedures. Indeed, SQL is rarely the right tool for complex business logic, and the fact that it's possible to use SQL for this purpose doesn't mean we should do it.

Constraints, however, are a different beast.

What happens when you make a choice between a tinyint and a smallint? You're choosing the most memory-efficient solution which matches the requirements. In other words, if you know that you need to store values from 1 to 2000, tinyint would be the wrong type.

Many constraints, in essence, are just extensions of types. My example of 0..100 range for a percentage is just that: a custom type which has a tinyint footprint but accepts only a subset of its values. In Ada, for instance, the range of accepted values can be part of the type definition:

subtype Percentage is Integer range 0 .. 100;

Including pieces of code in Ada in a blog article increases karma.

Those of the constraints that are not extensions of types could be considered as data contracts. For instance, having a constraint which indicates that a rebate should be expressed either as an absolute value or a percentage, but never both, is naturally a business rule, but also a data contract. Its goal is to extend the basic database schema by providing additional information which cannot be described by other means. This is not very different from having the database schema in the first place: defining that the table Person contains columns a non-null FirstName and a non-null LastName consists of defining specific business rules, which could have to change later (for instance because one notices that not everyone has a first and a last name). If you want to keep your relational database free of business rules, your only choice is to have a single table with a single nvarchar(max) column containing a single row which stores all the data.

Conclusion

Overall, constraints are an excellent tool which, while not crucial, makes your database a slightly safer place. What happens is that when both the number and the complexity of the apps accessing a given database start to grow, the risk of introducing invalid data grows as well. Some practices, such as accessing the database directly by hand in order to fix stuff, clean things, etc., are increasing this risk even more; while they should be avoided at all costs, pragmatic approach makes it sometimes impossible to prohibit them.

While the same constraints should be replicated within the applications, it should be noted that such replication could be automated through some clever code generation tools—something which is technically feasible, although I don't remember seeing such tools yet, maybe because of the popularity of ORMs.