Hi everyone!
I’m pleased to announce the initial release of pg-schema, a Haskell library designed for a database-first workflow with PostgreSQL.
Hackage: https://hackage.haskell.org/package/pg-schema
How it works
The library introspects your existing PostgreSQL database and generates type-level descriptions within your Haskell modules. These descriptions then serve as a single source of truth for generating type-safe DML operations.
Unlike full-blown SQL DSLs that try to cover every possible query, pg-schema focuses on providing a compact and safe way to handle common patterns, especially when dealing with nested data.
Key Features:
-
Schema Introspection: Automatically generate Haskell type-level definitions from your live database.
-
Type-Safe DML including Nested Data Support: Generate
SELECT,INSERT, andUPDATEoperations with compile-time checks for table names, columns, types, relations between tables. -
Clean interface: Two ways of describing input/output data: as an ordinal Haskell record or as labelled values.
Code example:
data Order = Order { num :: Text, createdAt :: Day, items :: \[OrdPos\] } deriving Generic
data OrdPos = OrdPos { id :: Int32, num :: Int32, article :: Article, price :: Double } deriving Generic
data Article = Article { id :: Int32, name :: Text } deriving Generic
type MyAnn tabName = 'Ann 5 CamelToSnake MySch ("dbSchema" ->> tabName)
...
do
void $ insertJSON_ (MyAnn "orders") conn
[ "num" =: "num1" :. "ord__ord_pos" =:
[ "num" =: 1 :. "articleId" =: 42 :. "price" =: 10
, "num" =: 2 :. "articleId" =: 41 :. "price" =: 15 ] ]
(xs :: \[Order\]) <- selectSch (MyAnn "orders") conn $ qRoot do
qWhere $ "created_at" >? someDay
&&& pchild "ord__ord_pos" defTabParam
(pparent "ord_pos__article" $ "name" \~\~? "%pencil%")
qOrderBy \[descf "created_at", descf "num"\]
qPath "ord__ord_pos" do
qWhere $ pparent "ord_pos__article" $ "name" \~\~? "%pencil%"
qOrderBy \[ascf "num"\]
qLimit 20