What is Selda?

Selda is an embedded domain-specific language for interacting with relational databases. It was inspired by LINQ and Opaleye.

Features

• Creating, dropping and querying tables using type-safe database schemas.
• Monadic query language with products, filtering, joins and aggregation.
• Inserting, updating and deleting rows from tables.
• Transaction support.
• Configurable, automatic, consistent in-process caching of query results.
• Multiple backends: SQLite and PostgreSQL.
• Lightweight and modular: non-essential features are optional or split into add-on packages.

Requirements

Selda requires SQLite 3.7.11+, or PostgreSQL 9+. To build the SQLite backend, you need a C compiler installed. To build the PostgreSQL backend, you need the libpq development libraries installed (libpq-dev on Debian-based Linux distributions).

A brief tutorial

Defining a schema

To work productively with Selda, you will need to enable the TypeOperators and OverloadedStrings extensions.

Table schemas are defined as the product of one or more columns, stitched together using the ¤ operator. A table is parameterized over the types of its columns, with the column types separated by the :*: operator. This, by the way, is why you need TypeOperators.

people :: Table (Text :*: Int :*: Maybe Text)
people = table "people" $ primary "name" ¤ required "age" ¤ optional "pet"

addresses :: Table (Text :*: Text)
addresses = table "addresses" $ required "name" ¤ required "city"

Columns may be either required or optional. Although the SQL standard supports nullable primary keys, Selda primary keys are always required.

Running queries

Selda queries are run in the SeldaT monad transformer. Any MonadIO can be extended with database capabilities. Throughout this tutorial, we will simply use SeldaT on top of the plain IO monad. SeldaT is entered using a backend-specific withX function. For instance, the SQLite backend uses the withSQLite function:

main :: IO ()
main = withSQLite "my_database.sqlite" $ do
  people <- getAllPeople
  liftIO (print people)

getAllPeople :: SeldaT IO [Text :*: Int :*: Maybe Text]
getAllPeople = query (select people)

This will open the my_database.sqlite database for the duration of the computation. If the computation terminates normally, or if it raises an exception, the database is automatically closed.

Note the somewhat weird return type of getAllPeople. In Selda, queries are represented using inductive tuples: a list of values, separated by the :*: operator, but where each element can have a different type. You can think of them as tuples with a slightly different syntax. In this example, getAllPeople having a return type of [Text :*: Int :*: Maybe Text] means that it returns a list of "3-tuples", where the three elements have the types Text, Int and Maybe Text respectively.

You can pattern match on these values as you would on normal tuples:

firstOfThree :: (a :*: b :*: c) -> a
firstOfThree (a :*: b :*: c) = a

Since inductive tuples are inductively defined, you may also choose to pattern match on just the first few elements:

firstOfN :: (a :*: rest) -> a
firstOfN (a :*: _) = a

Throughout the rest of this tutorial, we will simply use inductive tuples as if they were "normal" tuples.

Creating and deleting databases

You can use a table definition to create the corresponding table in your database backend, as well as delete it.

setup :: SeldaT IO ()
setup = do
  createTable people
  createTable addresses

teardown :: SeldaT IO ()
teardown = do
  tryDropTable people
  tryDropTable addresses

Both creating and deleting tables comes in two variants: the try version which is a silent no-op when attempting to create a table that already exists or delete one that doesn't, and the "plain" version which raises an error.

Inserting data

Data insertion is done in batches. To insert a batch of rows, pass a list of rows where each row is an inductive tuple matching the type of the table. Optional values are encoded as Maybe values.

populate :: SeldaT IO ()
populate = do
  insert_ people
    [ "Link"      :*: 125 :*: Just "horse"
    , "Velvet"    :*: 19  :*: Nothing
    , "Kobayashi" :*: 23  :*: Just "dragon"
    , "Miyu"      :*: 10  :*: Nothing
    ]
  insert_ addresses
    [ "Link"      :*: "Kakariko"
    , "Kobayashi" :*: "Tokyo"
    , "Miyu"      :*: "Fuyukishi"
    ]

Insertions come in two variants: the "plain" version which reports back the number of inserted rows, and one appended with an underscore which returns (). Use the latter to explicitly indicate your intent to ignore the return value.

There is one gotcha when inserting tuples: auto-incrementing primary keys. When inserting data into a table with such a primary key, the primary key column must be omitted from any inserted tuples to avoid a type error. The reason for this is that it's usually a bad idea to set auto-incrementing keys manually. The following example inserts a few rows into a table with an auto-incrementing primary key:

people' :: Table (Auto Int :*: Text :*: Int :*: Maybe Text)
people' = table "people_with_ids"
        $ autoPrimary "id"
        ¤ required "name"
        ¤ required "age"
        ¤ optional "pet"

populate' :: SeldaT IO ()
populate' = do
  insert_ people'
    [ "Link"      :*: 125 :*: Just "horse"
    , "Velvet"    :*: 19  :*: Nothing
    , "Kobayashi" :*: 23  :*: Just "dragon"
    , "Miyu"      :*: 10  :*: Nothing
    ]

Note that the tuple list passed to insert_ is the same as for the previous example, even though our new table has an additional field id. Since the id field is an auto-incrementing primary key, it will automatically be assigned a unique, increasing value. Thus, the resulting table would look like this:

id | name      | age | pet
-----------------------------
 0 | Link      | 125 | horse
 1 | Velvet    | 19  |
 2 | Kobayashi | 23  | dragon
 3 | Miyu      | 10  |

If you want manual control over your primary keys, do not use autoPrimary.

Updating rows

To update a table, pass the table and two functions to the update function. The first is a mapping over table columns, specifying how to update each row. The second is a predicate over table columns. Only rows satisfying the predicate are updated.

age10Years :: SeldaT IO ()
age10Years = do
  update_ people (\(name :*: _ :*: _) -> name ./= "Link")
                 (\(name :*: age :*: pet) -> name :*: age + 10 :*: pet)

Note that you can use arithmetic, logic and other standard SQL operations on the columns in either function. Columns implement the appropriate numeric type classes. For operations with less malleable types -- logic and comparisons, for instance -- the standard Haskell operators are prefixed with a period (.).

Deleting rows

Deleting rows is quite similar to updating them. The only difference is that the delete operation takes a table and a predicate, specifying which rows to delete. The following example deletes all minors from the people table:

byeMinors :: SeldaT IO ()
byeMinors = delete_ people (\(_ :*: age :*: _) -> age .< 20)

Basic queries

Queries are written in the Query monad, in which you can query tables, restrict the result set, and perform inner, aggregate queries. Queries are executed in some Selda monad using the query function.

The following example uses the select operation to draw each row from the people table, and the restrict operation to remove out all rows except those having an age column with a value greater than 20.

grownups :: Query s (Col s Text)
grownups = do
  (name :*: age :*: _) <- select people
  restrict (age .> 20)
  return name

printGrownups :: SeldaT IO ()
printGrownups = do
  names <- query grownups
  liftIO (print names)

You may have noticed that in addition to the return type of a query, the Query type has an additional type parameter s. We'll cover this parameter in more detail when we get to aggregating queries, so for now you can just ignore it.

Products and joins

Of course, data can be drawn from multiple tables. The unfiltered result set is essentially the cartesian product of all queried tables. For this reason, restrict calls should be made as early as possible, to avoid creating an unnecessarily large result set.

Arbitrary Haskell values can be injected into queries. As injected values are passed as parameters to prepared statements under the hood, there is no need to escape data; SQL injection is impossible by construction.

The following example uses data from two tables to find all grown-ups who reside in Tokyo. Note the use of the text function, to convert a Haskell Text value into an SQL column literal, as well as the use of name .== name' to remove all elements from the result set where the name in the people table does not match the one in the addresses table.

grownupsIn :: Text -> Query s (Col s Text)
grownupsIn city = do
  (name :*: age :*: _) <- select people
  restrict (age .> 20)
  (name' :*: home) <- select addresses
  restrict (home .== text city .&& name .== name')
  return name

printGrownupsInTokyo :: SeldaT IO ()
printGrownupsInTokyo = do
  names <- query (grownupsIn "Tokyo")
  liftIO (print names)

Also note that this is slightly different from an SQL join. If, for instance, you wanted to get a list of all people and their addresses, you might do something like this:

allPeople :: Query s (Col s Text :*: Col s Text)
allPeople = do
  (people_name :*: _ :*: _) <- select people
  (addresses_name :*: city) <- select addresses
  restrict (people_name == addresses_name)
  return (people_name :*: city)

This will give you the list of everyone who has an address, resulting in the following result set:

name      | city
---------------------
Link      | Kakariko
Kobayashi | Tokyo
Miyu      | Fuyukishi

Note the absence of Velvet in this result set. Since there is no entry for Velvet in the addresses table, there can be no entry in the product table people × addresses where both people_name and addresses_name are equal to "Velvet". To produce a table like the above but with a NULL column for Velvet's address (or for anyone else who does not have an entry in the addresses table), you would have to use a join:

allPeople' :: Query s (Col s Text :*: Col s Maybe Text)
allPeople' = do
  (name :*: _ :*: _) <- select people
  (_ :*: city) <- leftJoin (\(name' :*: _) -> name .== name')
                           (select addresses)
  return (name :*: city)

This gives us the result table we want:

name      | city
---------------------
Link      | Kakariko
Velvet    |
Kobayashi | Tokyo
Miyu      | Fuyukishi

The leftJoin function left joins its query argument to the current result set for all rows matching its predicate argument. Note that all columns returned from the inner (or right) query are converted by leftJoin into nullable columns. As there may not be a right counter part for every element in the result set, SQL and Selda alike set any missing joined columns to NULL.

Aggregate queries, grouping and sorting

You can also perform queries that sum, count, or otherwise aggregate their result sets. This is done using the aggregate function. This is where the additional type parameter to Query comes into play. When used as an inner query, aggregate queries must not depend on any columns from the outer query. To enforce this, the aggregate function forces all operations to take place in the Query (Inner s) monad, if the outer query takes place in the Query s monad. This ensures that aggregate inner queries can only communicate with their outside query by returning some value.

Like in standard SQL, aggregate queries can be grouped by column name or by some arbitrary expression. An aggregate subquery must return at least one aggregate column, obtained using sum_, avg, count, or one of the other provided aggregate functions. Note that aggregate columns, having type Aggr s a, are different from normal columns of type Col s a. Since SQL does not allow aggregate functions in WHERE clauses, Selda prevents them from being used in arguments to restrict.

The following example uses an aggregate query to calculate how many home each person has, and order the result set with the most affluent homeowners at the top.

countHomes :: Query s (Col s Text :*: Col s Int)
countHomes = do
  (name :*: _ :*: _) <- select people
  (owner :*: homes) <- aggregate $ do
    (owner :*: city) <- select addresses
    owner' <- groupBy owner
    return (count city :*: owner')
  restrict (owner .== name)
  order homes descending
  return (owner :*: homes)

Note how groupBy returns an aggregate version of its argument, which can be returned from the aggregate query. In this example, returning owner instead of owner' wouldn't work since the former is a plain column and not an aggregate.

Transactions

All databases supported by Selda guarantee that each query is atomic: either the entire query is performed in one go, with no observable intermediate state, or the whole query fails without leaving a trace in the database. However, sometimes this guarantee is not enough. Consider, for instance, a money transfer from Alice's bank account to Bob's. This involves at least two queries: one to remove the money from Alice's account, and one to add the same amount to Bob's. Clearly, it would be bad if this operation were to be interrupted after withdrawing the money from Alice's account but before depositing it into Bob's.

The solution to this problem is transactions: a mechanism by which a list of queries gain the same atomicity guarantees as a single query always enjoys. Using transactions in Selda is super easy:

transferMoney :: Text -> Text -> Double -> SeldaT s ()
transferMoney from to amount = do
  transaction $ do
    update_ accounts (\(owner :*: _) -> owner .== text from)
                     (\(owner :*: money) -> owner :*: money - float amount)
    update_ accounts (\(owner :*: _) -> owner .== text to)
                     (\(owner :*: money) -> owner :*: money + float amount)

This is all there is to it: pass the entire computation to the transaction function, and the whole computation is guaranteed to either execute atomically, or to fail without leaving a trace in the database. If an exception is raised during the computation, it will of course be rolled back.

Do be careful, however, to avoid performing IO within a query. While they will not affect the atomicity of the computation as far as the database is concerned, the computations themselves can obviously not be rolled back.

In-process caching

In many applications, read operations are orders of magnitude more common than write operations. For such applications, it is often useful to cache the results of a query, to avoid having the database perform the same, potentially heavy, query over and over even though we know we'll get the same result every time.

Selda supports automatic caching of query results out of the box. However, it is turned off by default. To enable caching, use the setLocalCache function.

main = withPostgreSQL connection_info $ do
  setLocalCache 1000
  ...

This will enable local caching of up to 1,000 different results. When that limit is reached, the least recently used result will be discarded, so the next request for that result will need to actually execute the query on the database backend. If caching was already enabled, changing the maximum number of cached results will discard the cache's previous contents. Setting the cache limit to 0 disables caching again.

To make sure that the cache is always consistent with the underlying database, Selda keeps track of which tables each query depends on. Whenever an insert, update, delete or drop is issued on a table t, all cached queries that depend on t will be discarded.

This guarantees consistency between cache and database, but only under the assumption that no other process will modify the database. If this assumption does not hold for your application, you should avoid using in-process caching. It is perfectly fine, however, to have multiple threads within the same application modifying the same database as long as they're all using Selda to do it, as the cache shared between all Selda computations running in the same process.

TODOs

Features that would be nice to have but are not yet implemented.

• If/else.
• Examples.
• Foreign keys.
• WHERE x IN (SELECT ...)
• SELECT INTO.
• Constraints other than primary key.
• Database schema upgrades.
• Stack build.