Safe Haskell | Safe |
---|---|
Language | Haskell2010 |
This module attepts to define a type-level language for describing database shcemas (i.e. schemas "as a type"), and the queries that operate on them in such a way that:
1) The schema and/or query is completely defined at the type level (sans runtime arguments to query parameters).
2) The meaning of a schema and/or query is immediately obvious to anyone who knows SQL, and
3) The schema and/or query can be extended, deconstructed, or interpreted by third parties for their own purposes.
To that end, each schema is a new type, defined by you, using the constructors provided by this library. The same goes for queries. Each query is a separate type defined with constructors from this library.
We provide a PostgreSQL backend so that real work can be accomplished, but if the backend is missing something you need, then the idea is that you can use your own type families and type classes to extend the schema and query languages, or interpret them differently for your own needs including writing entirely new backends if need be.
Synopsis
- class Table relation where
- data Field name typ
- data a :> b = a :> b
- data Select fields
- data From proj relation
- data As relation (name :: Symbol)
- data On join (conditions :: *)
- data LeftJoin left right
- data InsertInto table fields
- data DeleteFrom table
- data Update table (fields :: [Symbol])
- data Where query conditions
- data Equals (l :: k1) (r :: k2)
- data NotEquals l r
- data Lt l r
- data Lte l r
- data Gt l r
- data Gte l r
- data And l r
- data Or (l :: k1) (r :: k2)
- data Not a
- data IsNull (field :: Symbol)
- data NotNull (field :: Symbol)
- data (?)
- type family ArgsType query where ...
- type family ResultType query where ...
- type family Render a :: Symbol
Quick Start
Defining a Table
To define a table you need a type:
data Company
(Note: It is not required that the type contain any data, but it can if you like. Unlike some db frameworks, the set of columns stored in the table represented by this type is not directly tied to the Haskell record fields it contains. It is mainly used as a type-level symbol to reference your table.)
And you need a type class instance Table
:
instance Table Company where type Name Company = "companies" type DBSchema Company = Field "id" Int :> Field "name" Text :> Field "address" (Maybe Text)
The different parts of this typeclass instance include:
- A Name:
type Name Company = "companies"
- And a schema definition:
type DBSchema Company = Field "id" Int :> Field "name" Text :> Field "address" (Maybe Text)
Let's go ahead and define another table. We will use these two tables in the following examples:
data Employee instance Table Employee where type Name Employee = "employees" type DBSchema Employee = Field "company_id" Int :> Field "id" Int :> Field "name" Text :> Field "salary" (Maybe Int) :> Field "quit_date" (Maybe Day) :> Field "birth_date" Day
Building a Query
To write queries against these tables, use the query constructors defined in this module:
-- Given a company name as a query parameter, return all the -- employees that work at that company along with their salary. type MyQuery = Select '["e.name", "e.salary"] `From` '[ Company `As` "c", Employee `As` "e" ] `Where` "c.id" `Equals` "e.company_id" `And` "c.name" `Equals` (?)
Using a Query
Now that we have some tables and a query, how do we make use of them? Well, the first thing to notice is that a query like this needs inputs (the query parameter), and provides outputs (the selected rows). These inputs and outputs need to be typed, and indeed they are thanks to a couple of special type families:
ArgsType
- Given a query, produce the type of the embedded query parameters.ResultType
- Given a query, produce the type of rows produced by that query.
Example | Resulting type |
---|---|
ArgsType MyQuery | Only Text |
ResultType MyQuery | Only Text :> Only (Maybe Int ) |
The Database.Ribbit.PostgreSQL module provides a
query
function:
query :: forall m query. ( MonadIO m , KnownSymbol (Render query) , ToRow (ArgsType query) , FromRow (ResultType query) ) => Connection -> Proxy query -> ArgsType query -> m [ResultType query]
Notice that it accepts an (
argument, and returns a
list of ArgsType
query)(
values.ResultType
query)
Therefore, we can invoke the query thusly:
results <- query conn (Proxy :: Proxy MyQuery) (Only "Some Company")
The (
argument fulfils the query parameters,
and the results will be a list of rows which can be deconstructed
using pattern matching. E.g.:Only
"Some Company")
sequence_ [ putStrLn (show name <> " - " <> show salary) | (Only name :> Only salary) <- results ]
Inserting values
To insert values into our example tables above, we need to write a couple of insert statements:
E.g.:
type InsertCompany = InsertInto Company '["id", "name", "address"] type InsertEmployee = InsertInto Employee '["company_id", "id", "name", "birth_date"]
That's it really. Insert statements are much simpler than select queries. These statement will automatically be parameterized according to the listed fields.
There is a little bit of important nuance here: Note that
InsertEmployee
omits the "salary" field. That field is nullable,
and so the database will insert a null value when this insert statement
is used.
This can be particularly useful for allowing the database to supply default values, such as auto-incremented id fields. This library is not (yet) sophisticated enough understand which fields can safely be omitted, so it lets you omit any field. If you omit a field for which the database cannot supply a default value then that will result in a runtime error. This is a problem we plan to fix in a future version. On the other hand if you try to include a field that is not part of the schema, you will get a compile time error like you are supposed to.
To execute these insert statements, use Database.Ribbit.PostgreSQL's
execute
function:
do let myBirthday :: Day myBirthday = ... execute conn (Proxy :: Proxy InsertCompany) (Only 1 :> Only "Owens Murray" :> Only (Just "Austin, Tx")) execute conn (Proxy :: Proxy InsertEmployee) (Only 1 :> Only 1 :> Only "Rick" :> Only myBirthday)
Deleting values
Deleting a value is similar to inserting a value, but simpler because you only have to specify the delete conditions (if there are any).
e.g.:
type DeleteAllEmployees = DeleteFrom Employee type DeleteEmployeeById = DeleteFrom Employee `Where` "company_id" `Equals` (?) `And` "id" `Equals` (?)
Then just execute the query, providing the appropriate query params.
do let employeeId :: Int employeeId = ... execute conn (Proxy :: Proxy DeleteEmployeeById) (Only employeeId) -- Or maybe delete all employees. execute conn (Proxy :: Proxy DeleteAllEmployees) ()
Updating values
Updating values is almost the same as inserting values. Instead of specifying the fields that get inserted, you specify the fields that get updated, along with the conditions that match the rows to be updated.
{- Update an employee's salary (hopefully a raise!). -} type UpdateSalary = Update '[ "salary" ] `Where` "company_id" `Equals` (?) "id" `Equals` (?) ... let newSalary :: Int newSalary = 2 targetCompany :: Int targetCompany = 1 targetEmployee :: Int targetEmployee = 1 in execute conn (Proxy :: Proxy UpdateSalary) (Only newSalary :> Only targetCompany :> Only targetEmployee)
Schema Definition Types
Type class for defining your own tables. The primary way for you to introduce a new schema is to instantiate this type class for one of your types.
E.g.:
data MyTable instance Table MyTable where type Name MyTable = "my_table" type DBSchema MyTable = Field "id" Int :> Field "my_non_nullable_text_field" Text :> Field "my_nullable_int_field" (Maybe Int)
Define a field in a database schema, where:
name
: is the name of the database column, expressed as a type-level string literal, andtyp
: is the Haskell type whose values get stored in the column.
E.g:
Instances
(KnownSymbol name, HasFields more) => HasFields (Field name typ :> more :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
(HasIsNullable typ, HasPsqlType typ, HasPsqlTypes more) => HasPsqlTypes (Field name typ :> more :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
KnownSymbol name => HasFields (Field name typ :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
(HasIsNullable typ, HasPsqlType typ) => HasPsqlTypes (Field name typ :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL |
String two types together. Int
:>
Int
:>
Int
is similar in
principal to the nested tuple (Int
, (Int
, Int
)), but looks a
whole lot nicer when the number of elements becomes large.
This is how you build up a schema from a collection of Field
types.
E.g.:
Field "foo" Int :> Field "bar" Text :> Field "baz" (Maybe Text)
It also the mechanism by which this library builds up the Haskell
types for query parameters and resulting rows that get returned. So
if you have a query that accepts three text query parameters, that
type represented in Haskell is going to be (
.Only
Text
:>
Only
Text
:>
Only
Text
)
If that query returns rows that contain a Text, an Int, and a Text,
then the type of the rows will be (
.Only
Text
:>
Only
Int
:>
Only
Text
)
a :> b infixr 5 |
Instances
(KnownSymbol name, HasFields more) => HasFields (Field name typ :> more :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
(HasIsNullable typ, HasPsqlType typ, HasPsqlTypes more) => HasPsqlTypes (Field name typ :> more :: Type) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
(Eq a, Eq b) => Eq (a :> b) Source # | |
(Ord a, Ord b) => Ord (a :> b) Source # | |
Defined in Database.Ribbit.Table | |
(Show a, Show b) => Show (a :> b) Source # | |
(FromRow a, FromRow b) => FromRow (a :> b) Source # | |
Defined in Database.Ribbit.PostgreSQL | |
(ToRow a, ToRow b) => ToRow (a :> b) Source # | |
Defined in Database.Ribbit.PostgreSQL |
SQL Statement Constructors
Query Constructors
Types in the section are used to construct SELECT queries.
e.g.
List all employees who have quit, for all companies. (Left Joined version, which shows companies that have had no employees quit.)
Select '["c.name", "e.name", "e.quit_date"] `From` Company `As` "c" `LeftJoin` Employee `As` "e" `On` "c.id" `Equals` "e.company_id" `Where` NotNull "e.quit_date"
List all employees who have quit, for all companies. (Inner join version, which omits companies from which no employee has quit.)
Select '["c.name", "e.name", "e.quit_date"] `From` '[Company `As` "c", Employee `As` "e"] `Where` "c.id" `Equals` "e.company_id" `And` NotNull "e.quit_date"
SELECT constructor, used for starting a SELECT
statement.
data From proj relation infixl 6 Source #
FROM constructor, used for attaching a SELECT projection to a relation in the database.
data As relation (name :: Symbol) infix 9 Source #
AS constructor, used for attaching a name to a table in a FROM clause.
Instances
Table table => Table (As table alias ': more :: [Type]) Source # | Cross Product |
Table (On (LeftJoin (As l lname) (As r rname)) conditions :: Type) Source # | |
type Name (As table alias ': more :: [Type]) Source # | |
Defined in Database.Ribbit.Select | |
type Name (On (LeftJoin (As l lname) (As r rname)) conditions :: Type) Source # | |
type DBSchema (As table alias ': more :: [Type]) Source # | |
Defined in Database.Ribbit.Select | |
type DBSchema (On (LeftJoin (As l lname) (As r rname)) conditions :: Type) Source # | |
data On join (conditions :: *) infix 7 Source #
ON keyword, for joins.
data LeftJoin left right infix 8 Source #
Left Joins.
Insert Constructors
Construct insert statements.
e.g.
Insert one row into the Employee Table.
InsertInto Employee '["id", "company_id", "name"]
The values which are inserted into the specified fields are provided as query parameters.
data InsertInto table fields Source #
Insert statement.
Instances
type Render (InsertInto table fields) Source # | |
Defined in Database.Ribbit.Insert |
Delete Constructors
Construct delete statements.
e.g.
Delete all rows from the Company table:
type Statement = DeleteFrom Company
Delete a specific row from the Company table:
type Statement = DeleteFrom Company `Where` "id" `Equals` (?)
data DeleteFrom table Source #
Delete statement.
Instances
type Render (DeleteFrom table) Source # | |
Defined in Database.Ribbit.Delete | |
type Render (Where (DeleteFrom table) conditions) Source # | |
Defined in Database.Ribbit.Delete |
Update Constructors
Construct update statements
e.g.
Update a specific employee's salary:
type Statement = Update Employee '["salary"] `Where` "company_id" `Equals` (?) `And` "id" `Equals` (?)
The values which are inserted into the specified fields are provided as query parameters.
e.g.
execute conn Statement (Only newSalary :> Only companyId :> Only employeeId)
Condition Constructors
Use these types to construct a WHERE
clause.
data Where query conditions infixl 6 Source #
WHERE constructor, used for attaching conditions to a query.
Instances
type Render (Where (From proj table) conditions) Source # | |
Defined in Database.Ribbit.Select | |
type Render (Where (DeleteFrom table) conditions) Source # | |
Defined in Database.Ribbit.Delete | |
type Render (Where (Update table fields) conditions) Source # | |
Defined in Database.Ribbit.Update |
Transformations on Statement Types
These type families are useful for transforming the query types in various ways, or extracting certain information from them.
e.g.
Given the query:
type Query = Select '["name"] `From` Company `Where` "id" `Equals` (?)
Render the query as a String
value (using symbolVal
):
symbolVal (Proxy :: Proxy (Render Query)) == "SELECT name FROM companies WHERE id = ?"
Produce the Haskell type corresponding to the query parameters for a select statement:
ArgsType Query ~ Only Int -- Our statement has only one parameter, which is an int.
Produce the Haskell type corresponding to the rows produced by the query:
ResultType Query ~ Only Text -- Our query procudes only one column, a text.
type family ArgsType query where ... Source #
Produce the type represeting the placeholder ("?") values in a paramaterized query.
ArgsType ((_ `From` relation) `Where` conditions) = ArgsType (DBSchema relation, conditions) | |
ArgsType (InsertInto relation fields) = ProjectionType fields (DBSchema relation) | |
ArgsType (DeleteFrom relation `Where` conditions) = ArgsType (DBSchema relation, conditions) | |
ArgsType (Update relation fields) = ProjectionType fields (DBSchema relation) | |
ArgsType (Update relation fields `Where` conditions) = ProjectionType fields (DBSchema relation) :> ArgsType (DBSchema relation, conditions) | |
ArgsType (schema, And a b) = StripUnit (Flatten (ArgsType (schema, a) :> ArgsType (schema, b))) | |
ArgsType (schema, Or a b) = StripUnit (Flatten (ArgsType (schema, a) :> ArgsType (schema, b))) | |
ArgsType (schema, Condition field (?)) = ProjectionType '[field] schema | |
ArgsType (schema, Condition (?) field) = ProjectionType '[field] schema | |
ArgsType (schema, Condition l r) = If (ValidField r schema) (If (ValidField l schema) () (NotInSchema l schema)) (NotInSchema r schema) | |
ArgsType (schema, Equals l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, NotEquals l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, Lt l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, Lte l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, Gt l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, Gte l r) = ArgsType (schema, Condition l r) | |
ArgsType (schema, Not a) = ArgsType (schema, a) | |
ArgsType _ = () |
type family ResultType query where ... Source #
Produce the type of rows return by a query.
ResultType (Select fields `From` relation) = ProjectionType fields (DBSchema relation) | |
ResultType (query `Where` conditions) = ResultType query | |
ResultType query = TypeError (Text "Malformed Query" :$$: ShowType query) |
type family Render a :: Symbol Source #
Render a query.
Instances
type Render (DeleteFrom table) Source # | |
Defined in Database.Ribbit.Delete | |
type Render (Update table fields) Source # | |
Defined in Database.Ribbit.Update | |
type Render (InsertInto table fields) Source # | |
Defined in Database.Ribbit.Insert | |
type Render (Where (From proj table) conditions) Source # | |
Defined in Database.Ribbit.Select | |
type Render (Where (DeleteFrom table) conditions) Source # | |
Defined in Database.Ribbit.Delete | |
type Render (Where (Update table fields) conditions) Source # | |
Defined in Database.Ribbit.Update | |
type Render (From (Select proj) table) Source # | |
Defined in Database.Ribbit.Select |