roc-pg

Interface with PostgreSQL databases from Roc.

This package implements a PostgreSQL client on pure Roc that depends only on a TCP effect from the platform.

It exposes a simple API that allows you to run SQL commands as strings and a query builder that helps you write composable type-safe queries against your schema.

Status

I'd like this to become a stable PostgreSQL interface for Roc, but this project is currently a work in progress.

You can already use this to build useful applications. However, until we have a platform with TLS support, you should stick to experiments or simple apps where you run the database in the same machine.

Query Builder

The query builder is one of the most exciting features of this package, but more experimental than the lower-level API.

You can currently generate a Roc module from your schema that you can use through the functions exposed under Sql to compose type-safe SELECT statements.

The plan is to support all the other SQL commands, but that's coming later. In the meantime, you can perform those by creating a raw SQL command with Pg.Cmd.new.

See an example of a simple HTTP API built with the query builder!

Examples

Connecting and performing a query

task : Task (List { name: Str, price: Dec }) _
task =
    client <- Pg.Client.withConnect {
              host: "localhost",
              port: 5432,
              user: "postgres",
              database: "postgres",
              auth: Password "password"
          }

    Pg.Cmd.new "select name, price from products"
    |> Pg.Cmd.expectN (
        Pg.Result.succeed { 
            name: <- Pg.Result.str "name" |> Pg.Result.apply, 
            price: <- Pg.Result.dec "price" |> Pg.Result.apply
        }
    ) 
    |> Pg.Client.command client

Parameterized queries

Pg.Cmd.new "select name, price from products where id = $1"
|> Pg.Cmd.bind [ Pg.Cmd.u32 productId ]
|> Pg.Cmd.expect1 (
    Pg.Result.succeed { 
        name: <- Pg.Result.str "name" |> Pg.Result.apply, 
        price: <- Pg.Result.dec "price" |> Pg.Result.apply
    }
) 
|> Pg.Client.command client

Prepared statements

selectUser <-
    "select email from users where id = $1"
    |> Pg.Client.prepare { client, name: "selectUser" }
    |> await

selectUser
|> Pg.Cmd.bind [ Pg.Cmd.u32 userId ]
|> Pg.Cmd.expect1 (Pg.Result.str "email")
|> Pg.Client.command client

Batch commands in a single roundtrip (applicative)

Pg.Batch.succeed \email -> \products -> { email, products }
|> Pg.Batch.with
    (
        selectUser
        |> Pg.Cmd.bind [ Pg.Cmd.u32 userId ]
        |> Pg.Cmd.expect1 (Pg.Result.str "email")
    )
|> Pg.Batch.with
    (
        Pg.Cmd.new
            """
            select name, price from products
            inner join orders on orders.product_id = products.id
            where orders.id = $1
            """
        |> Pg.Cmd.bind [ Pg.Cmd.u32 orderId ]
        |> Pg.Cmd.expectN (
            Pg.Result.succeed { 
                name: <- Pg.Result.str "name" |> Pg.Result.apply, 
                price: <- Pg.Result.dec "price" |> Pg.Result.apply
            }
        ) 
    )
|> Pg.Client.batch client

Note: selectUser referes to prepared statement in the previous example

Batch commands in a single roundtrip (list)

updateCmd = \product ->
    Pg.Cmd.new "update products set desc = $1 where id = $2"
    |> Pg.Cmd.bind [ Pg.Cmd.str product.desc, Pg.Cmd.u32 product.id ]

productsToUpdate
|> List.map updateCmd
|> Pg.Batch.sequence
|> Pg.Client.batch client

Note: roc-pg automatically reuses statements in a batch by only parsing (and describing) once per unique SQL string. This also works with applicative batches.

Documentation

The API has been in a state of flux until recently. I have now started working on documentation, but the examples is probably the best we have for now.

Feel free to DM me at Roc's Zulip, though!

Features

• Connection handling
• Parameterized queries
• Decoding results
• Decoding errors
• Authentication methods
  • Cleartext password
  • MD5 password *
  • SASL / SCRAM-SHA-256 *
• Prepared statements
• Close prepared statements
• Pipelining
  • Applicative batches
  • Sequence list of commands expecting same type
  • 🚀 Parse and Describe once per unique SQL string
• Bulk copying
• Cursors
• SSL *
• Connection pooling *
• Notifications (listen/notify)
• Notices

* Requires new platform primitives

This list does not include features of the query builder as I'm still figuring out those.

Resources

• PostgreSQL Protocol Flow
• PostgreSQL Message Formats