United Kingdom: +44 (0)208 088 8978

Parsing SQL errors with composable Active Patterns

Isaac shows us how Active Patterns make it easier to reason about SQL errors.

We're hiring Software Developers

Click here to find out more

When working with SQL databases, it's common that you'll have to use two "channels" for handling success / failure:

  • If the SQL command executed successfully, you'll receive a result set back. Depending on the type of query, this result set might have 0, 1 or many rows in it.
  • If the SQL command did not execute successfully, the SQL client will throw an exception.

Although it's common these days - especially within Domain Driven Design circles to perform as much "domain logic" in code as we can by e.g. hydrating our domain and performing in-memory logic, it's often practical to perform some checks in SQL directly. For example, enforcing uniqueness of a specific field across all rows e.g. an ID column. Enforcing this in memory may be impractical as we would need to read all rows from the database into memory, and to enforce integrity would probably need to implement some kind of pessimistic locking strategy - hardly ideal. Instead, we could implement something like a unique constraint on the database, which would handle this for us. The challenge is how this is manifested back to us in code:

We can "model" this as a function with some pseudocode:

Execute SQL -> Data option | Exception

The challenge here is that "real" exceptions - such as invalid connection string or invalid SQL which would not compile - are bundled in with errors that may pertain to business rules such as "each order must have a unique identifier". Even worse, it's not uncommon to see code either not handle such SQL exceptions at all and e.g. simply responding to the caller with a generic HTTP 500, or going the other way and swallowing all exceptions with a simple "retry" message - in the case of a genuine SQL exception such as the database is down you would never know.

Active Patterns to the rescue

Active Patterns make it easier to reason about these kind of errors, by allowing us to make reusable abstractions that can be pattern matched over. Even better, you can easily compose active patterns to make more powerful abstractions over simpler ones. Let's start with a simple active pattern that allows us to match over an exception and will only return if the exception is indeed a SQL Exception. F# allows type-safe type pattern matching over exceptions, but we can go one better and extract out the specific SQL error code and message:

/// An active pattern which matches a SQLException and extracts both the HelpLink.EvtID and exception Message.
let (|SqlException|_|) (ex: exn) =
    match ex with
    | :? SqlException as ex -> Some(ex.Data["HelpLink.EvtID"] :?> string |> int, ex.Message)
    | _ -> None

With this, you can match over an exception as follows:

try
    executeSql "MY SQL COMMAND GOES HERE"
with
| SqlException (errorCode, message) -> $"SQL failure, code {errorCode}: {message}"

Of course, that's not especially interesting. What we want is to be able to reason about SQL Exceptions so that we can determine whether it's a genuine exception or something that we consider a domain error and probably map into a Result. We could start by matching the errorCode and message:

try
    Ok (executeSql "MY SQL COMMAND GOES HERE")
with
| SqlException (2627, "Violation of PRIMARY KEY constraint 'PK_Customer'. Cannot insert duplicate key in object 'dbo.Customer'.)" -> Error "This customer ID already exists."

Composing Active Patterns

Now we've added some literal matches to both the code and message and can be sure the reason for this specific exception (and convert to an Error). However, there's not a great deal of reusability here: we would want to use this same pattern in other SQL commands. And what about other types of errors such as Unique Constraints?

  • We can parse the text of the SQL exception using a Regex active pattern, and return the matches as a list of strings.
  • We can wrap different kinds of SQL exceptions based on their error code.
[<Literal>]
let constraintViolation =
    @"Violation of ([A-Z\s]+) constraint '([^']+)'. Cannot insert duplicate key in object '([^']+)'"

This regex pattern parses the constraint exception we saw earlier and will return four matches - the message itself (which can be ignored), the constraint type (e.g. PRIMARY KEY), the name of the constraint (PK_Customer) and the table (dbo.Customer). Armed with this, we can now create a generic Constraint pattern:

let (|Constraint|_|) exception =
    match exception with
    | SqlException(2627, Regex constraintViolation [ _; constraintType; constraintName; table ]) ->
        Some(Constraint(constraintType, constraintName, table))
    | _ -> None

And on top of this we can make patterns for Primary Key and Unique Key:

/// A Constraint active pattern which matches a unique key constraint and extracts the constraint name and table.
let (|UniqueConstraint|_|) exception =
    match exception with
    | Constraint("UNIQUE KEY", constraintName, table) -> Some(UniqueConstraint(constraintName, table))
    | _ -> None

/// A Constraint active pattern which matches a primary key constraint and extracts the table.
let (|PrimaryKeyConstraint|_|) =
    match exception with
    | Constraint("PRIMARY KEY", _, table) -> Some(PrimaryKeyConstraint table)
    | _ -> None

We can think of these final patterns as wrapping around each other:

and we can finally use these on a SQL exception:

try
    match! executeSql "MY SQL COMMAND GOES HERE" with
    | 0 -> Error "No rows were modified"
    | 1 -> Ok ()
    | n -> Error $"Multiple rows ({n}) were modified"
with
| PrimaryKeyConstraint "dbo.Customer" -> Error "This customer ID already exists."
| UniqueConstraint ("UC_Customer_Name", "dbo.Customer") -> Error "A customer with the same forename and surname already exists."

Any other exceptions will naturally bubble up as normal.

Conclusion

Active Patterns are flexible and have countless applications. In this post, we saw how you can use them to make exceptions easier to reason about using standard pattern matching logic, but also how to compose them from simple and distinct active patterns into more powerful ones that easily can be reused. You can see a more complete example of this in this repository, with a set of reusable SQL Exception active patterns available here and usage shown here.