An Entity stores the following information about the structure of a database table:

• Its name
• Its primary key
• The fields it contains

Example

data ExampleEntity = E
  { key    :: Key
  , field1 :: Int
  , field2 :: Bool
  }
  deriving stock (Eq, Show, Generic)
  deriving anyclass (FromRow, ToRow)
  deriving Entity
     via (GenericEntity '[TableName "entities"] ExampleEntity)

When using the functions provided by this library, you will sometimes need to be explicit about the Entity you are referring to.

Since: 0.0.1.0

A wrapper for table fields.

Since: 0.0.1.0

Select an entity by its primary key.

Since: 0.0.1.0

Select precisely one entity by a provided field.

Since: 0.0.1.0

Select potentially many entities by a provided field.

Since: 0.0.1.0

Select statement with a non-null condition

See _selectWhereNotNull for the generated query.

Since: 0.0.1.0

Select statement with a null condition

See _selectWhereNull for the generated query.

Since: 0.0.1.0

Select statement when for an entity where the field is one of the options passed

Since: 0.0.2.0

Perform a INNER JOIN between two entities

Since: 0.0.1.0

Perform a INNER JOIN ON field1 WHERE field2 = value between two entities

Since: 0.0.2.0

Perform a SELECT + ORDER BY query on an entity

Since: 0.0.2.0

Insert an entity.

Since: 0.0.1.0

Insert multiple rows of an entity.

Since: 0.0.2.0

Insert an entity with a "ON CONFLICT DO UPDATE" clause on the primary key as the conflict target

Since: 0.0.2.0

Update an entity.

The Id of the entity is put at the end of the query automatically through the use of UpdateRow. Examples

let newAuthor = oldAuthor{…}
update @Author newAuthor

Since: 0.0.1.0

Update rows of an entity matching the given value

Example

let newName = "Tiberus McElroy" :: Text
let oldName = "Johnson McElroy" :: Text
updateFieldsBy @Author [[field| name |]] ([field| name |], oldName) (Only newName)

Since: 0.0.1.0

Delete an entity according to its primary key.

Since: 0.0.1.0

Delete rows according to the given fields

Example

deleteByField @BlogPost [[field| title |]] (Only "Echoes from the other world")

Since: 0.0.1.0

Produce a SELECT statement for a given entity.

Examples

>>> _select @BlogPost
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\""

Since: 0.0.1.0

Produce a SELECT statement with explicit fields for a given entity

Examples

>>> _selectWithFields @BlogPost [ [field| blogpost_id |], [field| created_at |] ]
"SELECT \"blogposts\".\"blogpost_id\", \"blogposts\".\"created_at\" FROM \"\"blogposts\"\""

Since: 0.0.1.0

Produce a WHERE clause, given a vector of fields.

It is most useful composed with a _select or _delete, which is why these two combinations have their dedicated functions, but the user is free to compose their own queries.

The Entity constraint is required for _where in order to get any type annotation that was given in the schema. Fields that do not exist in the Entity will be kept so that PostgreSQL can report the error.

Examples

>>> _select @BlogPost <> _where [[field| blogpost_id |]]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"blogpost_id\" = ?"

>>> _select @BlogPost <> _where [ [field| uuid_list |] ]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"uuid_list\" = ?"

Since: 0.0.1.0

Produce a SELECT statement for a given entity and fields.

Examples

>>> _selectWhere @BlogPost [ [field| author_id |] ]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"author_id\" = ?"

>>> _selectWhere @BlogPost [ [field| author_id |], [field| title |]]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"author_id\" = ? AND \"title\" = ?"

Since: 0.0.1.0

Produce a SELECT statement where the provided fields are checked for being non-null. r

>>> _selectWhereNotNull @BlogPost [ [field| author_id |] ]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"author_id\" IS NOT NULL"

Since: 0.0.1.0

Produce a SELECT statement where the provided fields are checked for being null.

>>> _selectWhereNull @BlogPost [ [field| author_id |] ]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"author_id\" IS NULL"

Since: 0.0.1.0

Produce a SELECT statement where the given field is checked aginst the provided array of values .

>>> _selectWhereIn @BlogPost [field| title |] [ "Unnamed", "Mordred's Song" ]
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" WHERE \"title\" IN ('Unnamed', 'Mordred''s Song')"

Since: 0.0.2.0

Produce a "SELECT FROM" over two entities.

Examples

>>> _joinSelect @BlogPost @Author
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\", authors.\"author_id\", authors.\"name\", authors.\"created_at\" FROM \"blogposts\" INNER JOIN \"authors\" USING(author_id)"

Since: 0.0.1.0

Produce a "INNER JOIN … USING(…)" fragment.

Examples

>>> _innerJoin @BlogPost [field| author_id |]
" INNER JOIN \"blogposts\" USING(author_id)"

Since: 0.0.1.0

Produce a "SELECT [table1_fields, table2_fields] FROM table1 INNER JOIN table2 USING(table2_pk)" statement. The primary is used as the join point between the two tables.

Examples

>>> _joinSelectWithFields @BlogPost @Author [ [field| title |] ] [ [field| name |] ]
"SELECT \"blogposts\".\"title\", \"authors\".\"name\" FROM \"blogposts\" INNER JOIN \"authors\" USING(author_id)"

Since: 0.0.1.0

Produce a "SELECT FROM" over two entities.

Examples

>>> _joinSelectOneByField @BlogPost @Author [field| author_id |] [field| name |] :: Query
"SELECT blogposts.\"blogpost_id\", blogposts.\"author_id\", blogposts.\"uuid_list\", blogposts.\"title\", blogposts.\"content\", blogposts.\"created_at\" FROM \"blogposts\" INNER JOIN \"authors\" ON \"blogposts\".\"author_id\" = \"authors\".\"author_id\" WHERE authors.\"name\" = ?"

Since: 0.0.2.0

Produce an INSERT statement for the given entity.

Examples

>>> _insert @BlogPost
"INSERT INTO \"blogposts\" (\"blogpost_id\", \"author_id\", \"uuid_list\", \"title\", \"content\", \"created_at\") VALUES (?, ?, ?, ?, ?, ?)"

Since: 0.0.1.0

Produce a "ON CONFLICT (target) DO UPDATE SET …" statement.

Examples

>>> _onConflictDoUpdate [[field| blogpost_id |]] [ [field| title |], [field| content |]]
" ON CONFLICT (blogpost_id) DO UPDATE SET title = EXCLUDED.title, content = EXCLUDED.content"

>>> _onConflictDoUpdate [[field| blogpost_id |], [field| author_id |]] [ [field| title |], [field| content |]]
" ON CONFLICT (blogpost_id, author_id) DO UPDATE SET title = EXCLUDED.title, content = EXCLUDED.content"

>>> _insert @BlogPost <> _onConflictDoUpdate [[field| blogpost_id |]] [ [field| title |], [field| content |]]
"INSERT INTO \"blogposts\" (\"blogpost_id\", \"author_id\", \"uuid_list\", \"title\", \"content\", \"created_at\") VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT (blogpost_id) DO UPDATE SET title = EXCLUDED.title, content = EXCLUDED.content"

Since: 0.0.2.0

Produce an UPDATE statement for the given entity by primary key

Examples

>>> _update @Author
"UPDATE \"authors\" SET (\"name\", \"created_at\") = ROW(?, ?) WHERE \"author_id\" = ?"

>>> _update @BlogPost
"UPDATE \"blogposts\" SET (\"author_id\", \"uuid_list\", \"title\", \"content\", \"created_at\") = ROW(?, ?, ?, ?, ?) WHERE \"blogpost_id\" = ?"

Since: 0.0.1.0

Produce an UPDATE statement for the given entity by the given field.

Examples

>>> _updateBy @Author [field| name |]
"UPDATE \"authors\" SET (\"name\", \"created_at\") = ROW(?, ?) WHERE \"name\" = ?"

Since: 0.0.1.0

Produce an UPDATE statement for the given entity and fields, by primary key.

>>> _updateFields @Author [ [field| name |] ]
"UPDATE \"authors\" SET (\"name\") = ROW(?) WHERE \"author_id\" = ?"

Since: 0.0.1.0

Produce an UPDATE statement for the given entity and fields, by the specified field.

>>> _updateFieldsBy @Author [ [field| name |] ] [field| name |]
"UPDATE \"authors\" SET (\"name\") = ROW(?) WHERE \"name\" = ?"

>>> _updateFieldsBy @BlogPost [[field| author_id |], [field| title |]] [field| title |]
"UPDATE \"blogposts\" SET (\"author_id\", \"title\") = ROW(?, ?) WHERE \"title\" = ?"

Since: 0.0.1.0

Produce a DELETE statement for the given entity, with a match on the Primary Key

Examples

>>> _delete @BlogPost
"DELETE FROM \"blogposts\" WHERE \"blogpost_id\" = ?"

Since: 0.0.1.0

Produce a DELETE statement for the given entity and fields

Examples

>>> _deleteWhere @BlogPost [[field| title |], [field| created_at |]]
"DELETE FROM \"blogposts\" WHERE \"title\" = ? AND \"created_at\" = ?"

Since: 0.0.1.0

Produce an ORDER BY clause with one field and a sorting keyword

Examples

>>> _orderBy ([field| title |], ASC)
" ORDER BY \"title\" ASC"

Since: 0.0.2.0

Produce an ORDER BY clause with many fields and sorting keywords

Examples

>>> _orderByMany (V.fromList [([field| title |], ASC), ([field| created_at |], DESC)])
" ORDER BY \"title\" ASC, \"created_at\" DESC"

Since: 0.0.2.0