Abstracting Over Several DBMSs

In Hackage, you will find a group of packages that focus on the SQL world, which is the biggest one among database users. From these, HDBC (from Haskell Database Connectivity) and hsql hide the specific details of each DBMS but otherwise expose the SQL model (tables, rows, and columns) as is, without any further abstraction. You just need to plug in the package corresponding to the specific DBMS you want to use (such as hsql-mysql or HDBC-postgresql). This gives you as the developer the full power of those systems, allowing the use of prepared statements or controlling the transaction boundaries. However, when using any of these libraries, you need to write the SQL statements by hand, which is usually a source of errors, and you need to marshal the results afterwards (which are given using a specific data type for SQL values) into your own data type. Because of these problems, these packages are not often used directly but rather as a dependent of a higher-level one.

On that higher level, you will find HaskellDB. The idea from its developers was to expose the abstraction that the SQL databases are built upon: the relational algebra. Thus, you work with basic operations such as projection or restriction that can describe all the queries you can pose to the database. Furthermore, the schema for the tables is also embedded in the Haskell code, so you can guarantee the safety of all your queries (you won’t be accessing a nonexistent column, and you won’t be comparing columns of different types). The main drawback of HaskellDB is that it exposes an abstraction that is not as well known as SQL queries, so to use it you have to relearn how to express some concepts.

Introducing Persistent and Esqueleto

Persistent, available in Hackage and Stackage under the package name persistent, supports both relational and nonrelational DBMSs, which eases the transition between those two worlds in case it’s needed for enhancing the scalability of the application. When using Persistent, you still use your Haskell data types (i.e., you don’t need to marshal from and to your Client type), and the library generates all the glue code automatically (by using a Template Haskell). This gives you real type checking of your database statements, preventing a great range of application errors coming from this fact.

If you’re from an object-oriented programming background, you may recognize in this description the concept of an object-relational mapping (ORM), which also takes care of gluing a class with a specific table in a database. However, because it’s implemented in a functional language that embodies purity, Persistent doesn’t save automatically any change you do to the value holding the information about one of your rows. Rather, you need to explicitly ask for insertions, updates, and deletions.

One disadvantage of Persistent is that it supports only those operations common to every database it allows you to connect to. Since this includes both relational and nonrelational DBMSs, the common ground is sometimes limited. For example, you cannot easily express a join of two tables (in the SQL sense), since DBMSs such as MongoDB don’t have this concept. For that matter, some libraries have been developed that extend Persistent in one direction while restricting the applicable databases. This is the case of Esqueleto, a domain-specific language for expressing SQL queries inside Haskell using Persistent models.

Persistent itself encompasses a lot of related functionality in three areas: describing the schema of a database in such a way that can be used to work with usual Haskell data types, creating and adapting the schema of an existing database into a new one and adding or deleting columns (this is called the migration functionality), and performing actual work with the information in the database (insertions, updates, deletions, and queries).

Describing the Entities

After several language extensions that you need to enable, which are listed inside the {-# LANGUAGE #-} pragma, there’s a call to mkPersist. This function needs settings for generating the code and the description of the database. In this case, the code is telling the code generation to follow SQL standard conventions by giving sqlSettings as first parameter.¹ The latter parameter is not given using usual Haskell syntax but rather using quasiquotation : the code between [, |, and |] won’t be parsed by the Haskell compiler but rather by a custom reader (in this case, persistLowerCase). Thus, the important thing to know is how to express the database schema for this quasiquoter.

As you can see, there’s a block per each kind of entity you want to store. In this case, you have only one, namely, Client. Then, you can find all the fields that make that entity, first the name and then the type. Finally, you can include a deriving clause like you would do with the definition of any other Haskell data type. These are the most basic constructs; you will see more as the chapter progresses.

Since the code uses persistLowerCase as a quasiquoter, each entity will be mapped to a table and each field to a column, whose name will consist of converting from camel case (each word in an identifier starts with capital letter) to underscore case (different words in an identifier are separated by underscores). In this example, the data will be saved in a client table with the columns first_name, last_name, address, and age. If you want to retain the names using camel case, you need to use persistUpperCase instead of persistLowerCase.

In addition to the glue code for contacting the database, mkPersist will also generate data type constructors representing each of the fields in the entity. These constructors’ names are created by juxtaposing the name of the entity with the name of each of the fields, in camel case. In this case, you would obtain ClientFirstName, ClientLastName, and so on. In particular, this means you can use the same field name in different entities (e.g., using a field name for both individual and company entities) since the prefix will take them apart. This is different from Haskell records, which cannot share field names.

You may have noticed that so far there hasn’t been any description of an identifier , that is, no indication of what the table’s primary key might be. The primary key is a piece of information that uniquely determines a row in a table, and it’s commonly used in SQL environments. Persistent developers found this idea of an identifier interesting too, and that’s why a schema always defines an implicit id field for each entity. In conclusion, in addition to all the explicitly declared fields, you have a ClientId field representing the unique identifier of each row.

With this schema definition, the insertions shown in the previous section are correct Haskell code. Notice that Persistent has created a data type for each entity. This data type can be thought of as being defined as a record whose only constructor shares its name with the entity being defined. Thus, in the example, the constructors Country and Client have been generated. Another remark is that insert returns the identifier of the new entity in the database, and the code uses that for referring to the country "Spain" in the client.

I have discussed how Persistent always generates a special identifier field per entity. However, in other cases, there are other uniqueness constraints, which are a set of fields that uniquely identifies a value in the database. Since the combined value of those fields must appear only once in the database, it must be protected by the DBMS for duplicates. In that way, the database can protect data for a whole class of incorrect values. Furthermore, the extra work in inserting and updating is often surpassed by the increase in performance that can be achieved when the uniqueness is guaranteed.

For data mining purposes, it’s helpful to save the age of each client. However, not all clients would be happy giving that piece of information, so it’s better to make it optional. This corresponds to the notion of a nullable column in SQL and of a value wrapped on Maybe in Haskell. Indeed, that last word is the one chosen by Persistent to indicate that a field is optional for a value. If you want to make age optional, change its declaration from age Int to age Int Maybe.

In addition to the age, it’s also useful to save information about the gender of the clients. Near the beginning of the book, I discussed how using a Boolean value is not the right choice for representing the gender in Haskell. Rather, you should use a data type specific to that task. This increases type safety and removes the errors because of inconsistent mappings (is True equal to male or female?).

The previous information makes up the basics of the schema language, but many more features are available. For that reason, I strongly recommend you survey the Persistent documentation² or the Book of Yesod³ before declaring your entities.

Creating the Database

The source of this error is simple. You have declared how to map entities to a database, but none of the tables referenced in that code exists yet. The good news is that, as I mentioned in the introduction of this chapter, Persistent includes functionality to automatically create or adapt a database to the format expected by a schema. This adaptation process is called the migration of the database.

It’s important to notice that migrations can only add and delete columns; there’s no way for the code to know when a field name has been changed into another one. However, the simple case of adding a new field poses a question: which value should be written for those rows that were already there? The solution comes in the form of a new attribute that can be added to a field, namely, default, which defines this value to write in the case of a migration.

The sidebar “Using An Existing Database” shows how to deal with legacy databases having table names and column names not matching your entities and fields. Then, Exercise 11-1 guides you in defining the entity that will be used throughout this chapter for products.

Queries by Identifier or Uniqueness

It’s important to notice that the result of getBy is not just the value you asked for but rather a combination of the identifier associated with the value and the value itself, provided inside the Entity data type. In the previous example you can find two different usages of getBy: one for finding the identifier of the country given its name (check how in the Entity pattern matching only the identifier is actually bound to a variable) and another one for finding a client with given personal attributes and belonging to that country.

Selecting Several Entities

The full power of a database is unleashed when you perform queries not only via identifiers but also based on other fields within entities. Furthermore, the languages in which queries are posed are usually quite expressive. For example, you could be asking for all those clients coming from the United States who have bought at least two products in the last few years to create a better campaign for clients in that part of the world.

Those queries are usually not guaranteed to return only one or no results but can produce a set of values that fulfill the requirements of the query. You can ask Persistent to represent that set using two different approaches: either as a Source from the conduit library or as a plain list. You can also choose to just return the first value of the set wrapped on a Maybe. You may then get Nothing back if the query selects an empty set.

All the functions listed in Table 11-2 have the same parameters and are used in the same way, so in the examples I’ll use selectList because it’s the one that shows output simply while still letting you form an idea about what’s going on in the database.

The first of the two parameters is a list of filters . Each filter constrains the value of one or more fields to satisfy a specified condition. For example, you might require the age of a Client to be greater than 18. The way in which you write those filters is by using one of the constructors that Persistent has created for each of the fields in an entity and a set of built-in operators. The age filter, for example, is represented as ClientAge >=. Just 18 (you need to include Just because age was an optional field, so its values are wrapped in Maybe).

I’ve been silently adding an empty list as a second parameter to . selectList . That list represents the options to the query, which do not affect the results themselves but rather the way the results are presented. One typical option involves sorting by one or more fields. In the previous example, if you want to return the clients from the oldest to the youngest one, you must change the list of options to [ Desc ClientAge ]. As you can see, the way to indicate the ordering is by using one of the constructors Asc or Desc and the constructor corresponding to the field.

SQL Queries with Esqueleto

If you are used to relational databases, there’s a feature from its queries that would come to your mind to solve this problem: joins . However, Persistent aims to support also nonrelational databases, making the ability to join unavailable to you.

As I have already introduced, the solution to this problem is using another library, namely, Esqueleto. The esqueleto package provides support for writing SQL queries that are type checked. Furthermore, it reuses the schema definitions from Persistent, making the former a great companion to the latter.

As in the previous case, you can return the results in two different data structures. The select function shows the queried values as a list, whereas selectSource wraps them into a Conduit source. Both take as an argument a SqlQuery, so you need to focus on how to construct values of that type.

The first thing to do is to select which entities you’ll query. To do that, you use from. This is quite an interesting piece of the library because it takes a function as an argument, and depending on the type of the function, a certain subset of entities is queried. For example, if you want to query for clients, you must pass a function whose unique argument is of type Client. In most of the cases, that type is inferred by the compiler because of further filters (e.g., if you filter ClientAge, the compiler would infer that you’re querying Client), but in some cases you may need extra annotations. If you want to perform a query that involves more than one entity, you must pass them on a tuple.

The code makes explicit that ClientAge has a Maybe value. The constant value the field is compared to must be wrapped first with val and then with just.

In addition to inner joins, Esqueleto supports speaking about outer joins, which are useful in many situations.

To wrap up this fast introduction to Esqueleto, now you need to learn a bit about expressing grouping like you do in SQL. The grouping itself is done via the groupBy function, which takes as an argument a field to make the grouping. Then, you can use aggregation operators such as sum_, min_, max_, avg_, or countRows over other fields to perform that operation over all values of each group. Explaining in detail how these operations work would take a long time, and that is a task better suited to a book on SQL, from which Esqueleto takes its concepts .

The language supported by Esqueleto is indeed expressive and allows you to express queries far beyond what has been explained here. My recommendation for those moments when you need to perform powerful queries against a relational database is to read its documentation in more detail to discover the full generality of its constructions.