Some monad with Selda SQL capabilitites.

Type of database backend used by m.

Thrown by any function in SeldaT if an error occurs.

An error occurred when validating a database table. If this error is thrown, there is a bug in your database schema, and the particular table that triggered the error is unusable. Since validation is deterministic, this error will be thrown on every consecutive operation over the offending table.

Therefore, it is not meaningful to handle this exception in any way, just fix your bug instead.

Monad transformer adding Selda SQL capabilities.

The simplest form of Selda computation; SeldaT specialized to IO.

Any type which has a corresponding relation. To make a Relational instance for some type, simply derive Generic.

Note that only types which have a single data constructor, and where all fields are instances of SqlValue can be used with this module. Attempting to use functions in this module with any type which doesn't obey those constraints will result in a very confusing type error.

Wrapper for single column tables. Use this when you need a table with only a single column, with table or selectValues.

A database table, based on some Haskell data type. Any single constructor type can form the basis of a table, as long as it derives Generic and all of its fields are instances of SqlType.

An SQL query.

A database row. A row is a collection of one or more columns.

A database column. A column is often a literal column table, but can also be an expression over such a column or a constant expression.

An acceptable query result type; one or more columns stitched together with :*:.

Run a query within a Selda monad. In practice, this is often a SeldaT transformer on top of some other monad. Selda transformers are entered using backend-specific withX functions, such as withSQLite from the SQLite backend.

Perform the given query, and insert the result into the given table. Returns the number of inserted rows.

Perform the given computation atomically. If an exception is raised during its execution, the entire transaction will be rolled back and the exception re-thrown, even if the exception is caught and handled within the transaction.

Run the given computation as a transaction without enforcing foreign key constraints.

If the computation finishes with the database in an inconsistent state with regards to foreign keys, the resulting behavior is undefined. Use with extreme caution, preferably only for migrations.

On the PostgreSQL backend, at least PostgreSQL 9.6 is required.

Using this should be avoided in favor of deferred foreign key constraints. See SQL backend documentation for deferred constraints.

Generate a new random UUID using the system's random number generator. UUIDs generated this way are (astronomically likely to be) unique, but not necessarily unpredictable.

For applications where unpredictability is crucial, take care to use a proper cryptographic PRNG to generate your UUIDs.

Any datatype representable in (Selda's subset of) SQL.

Any type that's bounded, enumerable and has a text representation, and thus representable as a Selda enumerable.

While it would be more efficient to store enumerables as integers, this makes hand-rolled SQL touching the values inscrutable, and will break if the user a) derives Enum and b) changes the order of their constructors. Long-term, this should be implemented in PostgreSQL as a proper enum anyway, which mostly renders the performance argument moot.

Any column tuple.

Denotes that scopes s and t are identical.

The order in which to sort result rows.

An inductively defined "tuple", or heterogeneous, non-empty list.

Query the given table.

Query an ad hoc table of type a. Each element in the given list represents one row in the ad hoc table.

Convenient shorthand for fmap (! sel) q. The following two queries are quivalent:

q1 = name `from` select people
q2 = do
  person <- select people
  return (person ! name)

Remove all duplicates from the result set.

Restrict the query somehow. Roughly equivalent to WHERE.

Drop the first m rows, then get at most n of the remaining rows from the given subquery.

Sort the result rows in ascending or descending order on the given row.

If multiple order directives are given, later directives are given precedence but do not cancel out earlier ordering directives. To get a list of persons sorted primarily on age and secondarily on name:

peopleInAgeAndNameOrder = do
  person <- select people
  order (person ! name) ascending
  order (person ! age) ascending
  return (person ! name)

For a table [(Alice, 20), (Bob, 20), (Eve, 18)], this query will always return [Eve, Alice, Bob].

The reason for later orderings taking precedence and not the other way around is composability: order should always sort the current result set to avoid weird surprises when a previous order directive is buried somewhere deep in an earlier query. However, the ordering must always be stable, to ensure that previous calls to order are not simply erased.

Ordering for order.

Ordering for order.

Sort the result rows in random order.

Explicitly create an inner query. Equivalent to innerJoin (const true).

Sometimes it's handy, for performance reasons and otherwise, to perform a subquery and restrict only that query before adding the result of the query to the result set, instead of first adding the query to the result set and restricting the whole result set afterwards.

Create and filter an inner query, before adding it to the current result set.

q suchThat p is generally more efficient than select q >>= x -> restrict (p x) >> pure x.

A column selector. Column selectors can be used together with the ! and with functions to get and set values on rows, or to specify foreign keys.

Coalesce nested nullable column into a single level of nesting.

Any table type t, which has a field named name.

The type of the name field, in the record type t.

Extract the given column from the given row.

Extract the given column from the given nullable row. Nullable rows usually result from left joins. If a nullable column is extracted from a nullable row, the resulting nested Maybes will be squashed into a single level of nesting.

A selector-value assignment pair.

For each selector-value pair in the given list, on the given tuple, update the field pointed out by the selector with the corresponding value.

Add the given column to the column pointed to by the given selector.

Subtract the given column from the column pointed to by the given selector.

Multiply the column pointed to by the given selector, by the given column.

Logically OR the column pointed to by the given selector with the given column.

Logically AND the column pointed to by the given selector with the given column.

Apply the given function to the given column.

Any container type for which we can check object membership.

A typed row identifier. Generic tables should use this instead of RowID. Use untyped to erase the type of a row identifier, and cast from the Database.Selda.Unsafe module if you for some reason need to add a type to a row identifier.

A typed row identifier which is guaranteed to not match any row in any table.

Is the given typed row identifier invalid? I.e. is it guaranteed to not match any row in any table?

Create a typed row identifier from an integer. Use with caution, preferably only when reading user input.

Create a typed row identifier from an integer. Use with caution, preferably only when reading user input.

A row identifier for some table. This is the type of auto-incrementing primary keys.

A row identifier which is guaranteed to not match any row in any table.

Is the given row identifier invalid? I.e. is it guaranteed to not match any row in any table?

Inspect a row identifier.

Create a row identifier from an integer. Use with caution, preferably only when reading user input.

Comparisons over columns. Note that when comparing nullable (i.e. Maybe) columns, SQL NULL semantics are used. This means that comparing to a NULL field will remove the row in question from the current set. To test for NULL, use isNull instead of .== literal Nothing.

Comparisons over columns. Note that when comparing nullable (i.e. Maybe) columns, SQL NULL semantics are used. This means that comparing to a NULL field will remove the row in question from the current set. To test for NULL, use isNull instead of .== literal Nothing.

The SQL LIKE operator; matches strings with % wildcards. For instance:

"%gon" `like` "dragon" .== true

Boolean negation.

A literal expression.

Returns true if the given field in the given row is equal to the given literal.

Specialization of literal for integers.

Specialization of literal for doubles.

Specialization of literal for text.

True and false boolean literals.

True and false boolean literals.

SQL NULL, at any type you like.

Round a column to the given number of decimals places.

Calculate the length of a string column.

Is the given column null?

Perform a conditional on a column

If the second value is Nothing, return the first value. Otherwise return the second value.

Applies the given function to the given nullable column where it isn't null, and returns the given default value where it is.

This is the Selda equivalent of maybe.

Create a new column with the given fields. Any unassigned fields will contain their default values.

Create a singleton table column from an appropriate value.

Any container type which can be mapped over. Sort of like Functor, if you squint a bit.

Round a value to the nearest integer. Equivalent to roundTo 0.

Lift a non-nullable column to a nullable one. Useful for creating expressions over optional columns:

data Person = Person {name :: Text, age :: Int, pet :: Maybe Text}
  deriving Generic
instance SqlRow Person

people :: Table Person
people = table "people" []

peopleWithCats = do
  person <- select people
  restrict (person ! #pet .== just "cat")
  return (person ! #name)

Convert a boolean column to any numeric type.

Convert an integer column to any numeric type.

Convert any SQL type to a string.

A single aggregate column. Aggregate columns may not be used to restrict queries. When returned from an aggregate subquery, an aggregate column is converted into a non-aggregate column.

One or more aggregate columns.

Convert one or more inner column to equivalent columns in the outer query. OuterCols (Aggr (Inner s) a :*: Aggr (Inner s) b) = Col s a :*: Col s b, for instance.

The results of a left join are always nullable, as there is no guarantee that all joined columns will be non-null. JoinCols a where a is an extensible tuple is that same tuple, but in the outer query and with all elements nullable. For instance:

 LeftCols (Col (Inner s) Int :*: Col (Inner s) Text)
   = Col s (Maybe Int) :*: Col s (Maybe Text)

Denotes an inner query. For aggregation, treating sequencing as the cartesian product of queries does not work well. Instead, we treat the sequencing of aggregate with other queries as the cartesian product of the aggregated result of the query, a small but important difference.

However, for this to work, the aggregate query must not depend on any columns in the outer product. Therefore, we let the aggregate query be parameterized over Inner s if the parent query is parameterized over s, to enforce this separation.

Any column type that can be used with the min_ and max_ functions.

Perform an INNER JOIN with the current result set and the given query.

Perform a LEFT JOIN with the current result set (i.e. the outer query) as the left hand side, and the given query as the right hand side. Like with aggregate, the inner (or right) query must not depend on the outer (or right) one.

The given predicate over the values returned by the inner query determines for each row whether to join or not. This predicate may depend on any values from the outer query.

For instance, the following will list everyone in the people table together with their address if they have one; if they don't, the address field will be NULL.

getAddresses :: Query s (Col s Text :*: Col s (Maybe Text))
getAddresses = do
  (name :*: _) <- select people
  (_ :*: address) <- leftJoin (\(n :*: _) -> n .== name)
                              (select addresses)
  return (name :*: address)

Execute a query, returning an aggregation of its results. The query must return an inductive tuple of Aggregate columns. When aggregate returns, those columns are converted into non-aggregate columns, which may then be used to further restrict the query.

Note that aggregate queries must not depend on outer queries, nor must they return any non-aggregate columns. Attempting to do either results in a type error.

The SQL HAVING keyword can be implemented by combining aggregate and restrict:

-- Find the number of people living on every address, for all addresses
-- with more than one tenant:
-- SELECT COUNT(name) AS c, address FROM housing GROUP BY name HAVING c > 1

numPpl = do
  (num_tenants :*: theAddress) <- aggregate $ do
    h <- select housing
    theAddress <- groupBy (h ! address)
    return (count (h ! address) :*: theAddress)
 restrict (num_tenants .> 1)
 return (num_tenants :*: theAddress)

Group an aggregate query by a column. Attempting to group a non-aggregate query is a type error. An aggregate representing the grouped-by column is returned, which can be returned from the aggregate query. For instance, if you want to find out how many people have a pet at home:

aggregate $ do
  person <- select people
  name' <- groupBy (person ! name)
  return (name' :*: count(person ! pet_name) .> 0)

The number of non-null values in the given column.

The average of all values in the given column.