Binding to Postgres.js — sharing experience

1

Hello there,

After a long pause I want to give ReScript a try on backend of an app I’m working on. One required aspect is a PostgreSQL database access. Previously I had experience with it using Knex and corresponding bindings. But that was in the ReasonML era, so not applicable to modern ReScript anymore.

This time I want to use Postgres.js that has quite an opinionated interface based on tagged string templates, but nevertheless ergonomic and tested in production. Quickly searching if anyone succeeded with binding to it I found some traces of failed attempts. That was frustrating, and I thought maybe a practical binding is indeed impossible at the current stage. However, after some bruteforcing I came up with a working solution.

Sharing the approach here for anyone who possible want to use Postgres.js from ReScript too:

Sql.res 

/**
 * ReScript bindings to Postgres.js
 */
type sql

type part

type connectionOptions = {
  host?: string,
  port?: int,
  path?: string,
  database?: string,
  username?: string,
  password?: string,
  ssl?: bool,
  max?: int,
  max_lifetime?: int,
  idle_timeout?: int,
  connect_timeout?: int,
  prepare?: bool,
}

@module("postgres")
external connect: (~options: connectionOptions=?) => sql = "default"

@module("postgres")
external connectWithUrl: (string, ~options: connectionOptions=?) => sql = "default"

@module("./sqlShim.js") @val
external _wrap: (sql, 'a) => 'b = "sqlWrap"

@module("./sqlShim.js") @val
external _wrap2: (sql, 'a, 'b) => 'c = "sqlWrap2"

@module("./sqlShim.js") @taggedTemplate
external query: (sql, array<string>, array<part>) => Promise.t<array<'row>> = "sqlQuery"

@send
external end: sql => promise<unit> = "end"

let object = (sql: sql, obj: {..}, ~columns: option<array<string>>=?): part => {
  switch columns {
  | Some(columns) => sql->_wrap2(obj, columns)
  | None => sql->_wrap(obj)
  }
}

let arrayOfObjects = (sql: sql, obj: array<{..}>): part => sql->_wrap(obj)

external stringParam: string => part = "%identity"
external intParam: string => part = "%identity"
external floatParam: string => part = "%identity"

sqlShim.js – the necessary evil to work around ReScript 11.1 limitations


export function sqlQuery(sqlInstance, strings, ...values) {
  const stringsWithRaw = [...strings];
  stringsWithRaw.raw = [...strings];
  return sqlInstance.call(sqlInstance, stringsWithRaw, ...values);
}

export function sqlWrap(sqlInstance, valueToWrap) {
  return sqlInstance(valueToWrap)
}

export function sqlWrap2(sqlInstance, valueToWrap1, valueToWrap2) {
  return sqlInstance(valueToWrap1, valueToWrap2)
}

Usage

MaterialDb.resi 


let findAll: ShopContext.t => promise<array<MaterialDto.t>>
let findById: (ShopContext.t, Uuid.t) => promise<option<MaterialDto.t>>
let findBySku: (ShopContext.t, string) => promise<option<MaterialDto.t>>
let create: (ShopContext.t, MaterialDto.t) => promise<Uuid.t>
let update: (ShopContext.t, Uuid.t, MaterialDto.Patch.t) => promise<unit>
let delete: (ShopContext.t, Uuid.t) => promise<unit>

MaterialDb.res 

module Object = RescriptCore.Object

let findAll = async (ctx): array<MaterialDto.t> => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop
  await sql->(
    Sql.query`
      SELECT * FROM materials
      WHERE shop = ${Sql.stringParam(shop)}
      ORDER BY sku
    `
  )
}

let findById = async (ctx, id): option<MaterialDto.t> => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop
  let rows = await sql->(
    Sql.query`
      SELECT *
      FROM materials
      WHERE
        shop = ${Sql.stringParam(shop)}
        AND id = ${Sql.stringParam(id)}
    `
  )
  rows->ArrayX.first
}

let findBySku = async (ctx, sku): option<MaterialDto.t> => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop
  let rows = await sql->(
    Sql.query`
      SELECT *
      FROM materials
      WHERE
        shop = ${Sql.stringParam(shop)}
        AND sku = ${Sql.stringParam(sku)}
    `
  )

  rows->ArrayX.first
}

let create = async (ctx, material: MaterialDto.t): Uuid.t => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop

  let values: {..} = ObjectX.merge([material->Obj.magic, {"shop": shop}])

  let rows = await sql->(
    Sql.query`
      INSERT INTO materials
        ${sql->Sql.object(values)}
      RETURNING id
    `
  )

  (rows->ArrayX.first->Option.getExn)["id"]
}

let update = async (ctx, id: Uuid.t, patch: MaterialDto.Patch.t) => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop
  let values: {..} = patch->Obj.magic
  let _ = await sql->(
    Sql.query`
      UPDATE materials
      SET ${sql->Sql.object(values, ~columns=values->Object.keysToArray)}
      WHERE
        shop = ${Sql.stringParam(shop)}
        AND id = ${Sql.stringParam(id)}
    `
  )
}

let delete = async (ctx, id) => {
  let sql = ctx->ShopContext.sql
  let shop = ctx->ShopContext.shop
  let _ = await sql->(
    Sql.query`
      DELETE FROM materials
      WHERE
        shop = ${Sql.stringParam(shop)}
        AND id = ${Sql.stringParam(id)}
    `
  )
}

TestDb.res 


let sql = switch NodeJs.Process.process.env->Js.Dict.get("PGDATABASE_UNITTEST") {
| Some(database) =>
  let sql = Sql.connect(~options={database, max: 1})
  Prexit.prexit(() => sql->Sql.end)
  sql
| None =>
  Js.Console.error("PGDATABASE_UNITTEST env variable must be set")
  NodeJs.Process.process->NodeJs.Process.exitWithCode(1)
  assert(false)
}

NTest.afterAsync(() => sql->Sql.end)

let myShopContext = ShopContext.make(sql, "my-shop")
let theirShopContext = ShopContext.make(sql, "their-shop")

let createShopAccounts = async () => {
  let accounts = [
    {
      "shop": "my-shop",
      "title": "my-shop",
    },
    {
      "shop": "their-shop",
      "title": "their-shop",
    },
  ]

  let _ = await sql->(
    Sql.query`
    INSERT INTO shop_accounts
      ${sql->Sql.arrayOfObjects(accounts)}
  `
  )
}

let wipeAllData = async () => {
  let _ = await sql->(
    Sql.query`
    TRUNCATE
      materials,
      shop_accounts
    CASCADE
    `
  )
}

MaterialDb_test.res 

open NTest

beforeEachAsync(TestDb.createShopAccounts)
afterEachAsync(TestDb.wipeAllData)

describe("Material DB", () => {
  let exampleMaterial1: MaterialDto.t = {
    id: "0529ba20-48d2-4614-beaf-24326941592a",
    sku: "SKU001",
    color: Null,
    thickness_mm: Null,
    price_tier1_ncu_m2: 300.0,
    price_tier2_ncu_m2: 200.0,
    price_tier3_ncu_m2: 100.0,
    number_of_layers: Null,
    opacity: Null,
    real_thickness_min_mm: Null,
    real_thickness_max_mm: Null,
  }

  let exampleMaterial2: MaterialDto.t = {
    ...exampleMaterial1,
    id: "01829885-efd8-4940-a82f-9f13e263cba6",
    sku: "SKU002",
  }

  testAsync("returns [] for findAll when no materials created", async () => {
    let materials = await MaterialDb.findAll(TestDb.myShopContext)
    Assert.equal(materials->Array.length, 0)
  })

  testAsync("can find material by id after insert", async () => {
    let id = await MaterialDb.create(TestDb.myShopContext, exampleMaterial1)
    let material = await MaterialDb.findById(TestDb.myShopContext, id)
    Assert.ok(material->Option.isSome)
  })

  testAsync("can find material by sku after insert", async () => {
    let _ = await MaterialDb.create(TestDb.myShopContext, exampleMaterial1)
    let material = await MaterialDb.findBySku(TestDb.myShopContext, "SKU001")
    Assert.ok(material->Option.isSome)
  })

  testAsync("cannot find material of another shop", async () => {
    let id = await MaterialDb.create(TestDb.myShopContext, exampleMaterial1)
    let material = await MaterialDb.findById(TestDb.theirShopContext, id)
    Assert.ok(material->Option.isNone)
  })

  testAsync("updates material partially", async () => {
    let id = await MaterialDb.create(TestDb.myShopContext, exampleMaterial1)

    let () = await MaterialDb.update(
      TestDb.myShopContext,
      id,
      {
        sku: "SKU111",
        thickness_mm: Null,
      },
    )

    let material = await MaterialDb.findById(TestDb.myShopContext, id)

    switch material {
    | Some(material) =>
      Assert.equal(material.sku, "SKU111")
      Assert.equal(material.thickness_mm, Null)
      Assert.equal(material.price_tier1_ncu_m2, 300.0)

    | None => Assert.fail(~message="Material not found")
    }
  })

  testAsync("deletes precisely", async () => {
    let id1 = await MaterialDb.create(TestDb.myShopContext, exampleMaterial1)
    let id2 = await MaterialDb.create(TestDb.myShopContext, exampleMaterial2)

    let _ = await MaterialDb.delete(TestDb.myShopContext, id1)
    let _ = await MaterialDb.delete(TestDb.theirShopContext, id2) // should have no effect

    let material1 = await MaterialDb.findById(TestDb.myShopContext, id1)
    let material2 = await MaterialDb.findById(TestDb.myShopContext, id2)

    Assert.ok(material1->Option.isNone)
    Assert.ok(material2->Option.isSome)
  })
})

MaterialDto.res 

// ...

type t = {
  id: string,
  sku: string,
  color: Null.t<string>,
  thickness_mm: Null.t<float>,
  price_tier1_ncu_m2: float,
  price_tier2_ncu_m2: float,
  price_tier3_ncu_m2: float,
  number_of_layers: Null.t<int>,
  opacity: Null.t<float>,
  real_thickness_min_mm: Null.t<float>,
  real_thickness_max_mm: Null.t<float>,
}

module Patch = {
  // NOTE: keep in sync with `t` above. Omit `id`, make other fields optional
  type t = {
    sku?: string,
    color?: Null.t<string>,
    thickness_mm?: Null.t<float>,
    price_tier1_ncu_m2?: float,
    price_tier2_ncu_m2?: float,
    price_tier3_ncu_m2?: float,
    number_of_layers?: Null.t<int>,
    opacity?: Null.t<float>,
    real_thickness_min_mm?: Null.t<float>,
    real_thickness_max_mm?: Null.t<float>,
  }
}

// ...

Hope you can pick some ideas from this. Enjoy.

1 Like
2

Now about some observations of where ReScript might be improved for better DX

Properly bind with @taggedTemplate

Currently the @taggedTemplate works correctly only for functions with two arguments.
When applied to a function with three arguments where the first one is actually the function to call, the generated JS code is just incorrect.
It would be great if the following:

@send @taggedTemplate
external query: (sql, array<string>, array<part>) => Promise.t<array<'row>> = "%identity"

…would translate to:

whateverFunction.on.the.FirstPosition`actually the content of the ${template} string passed as is`

Partial records

In TypeScript I can:

type record = {
  a: string,
  b: number,
  c: boolean,
}

type partialRecord = Partial<record>

This is super-helpful to express concept of an update “patch” applied to some entity (PUT/PATCH in REST terms) where I can update precisely only 3 out of 100 fields of the entity.
I would cry with happy tears if some day ReScript gets the same built-in:

// Fantasy

type record = {
  a: string,
  b: float,
  c: bool,
}

type partialRecord = partial<record>

// Equivalent to

type partialRecord = {
  a?: string,
  b?: float,
  c?: bool,
}

Probably it’s too desperate to dream of, but keyof, Pick and Omit from TypeScript can make ReScript to the next level as well:

// Fantasy

type record = {
  id: Uuid.t,
  a: string,
  b: float,
  c: bool,
}

type recordWithoutSomeFields = omit<record, [#id | #a]>
type recordWithSomeFields = pick<record, [#b | #c]>
type keys = keyof<record>  // equivalent to type keys = [#id | #a | #b | #c]

Easier coercing without Obj.magic

When dealing with frontend, backend logic, database access at once it’s often required to switch back and forth between records, objects and JSON.t directly, without repeating/reconstructing 100 fields again and again. It would help a lot if I can do the following:

type record = {
  a: string,
  b: float,
  c: bool,
}

let a = "some string"
let b = 1234.0
let c = true

let recordObject = record :> {..}  // this can be a no-op, right?
let recordObject2 = record :> {"a": string, "b": float}  // can be type-checked
let recordBack = {"a": a, "b": b, "c": c} :> record // compiles only if shape matches


let myJson = {a, b, c} :> JSON.t
let myJson2 = {"a": a} :> JSON.t

Whew, enough dreaming. I hope some day ReScript on backend might become as fluent as crafting ReScript React components one after another.

1 Like
3

Sorry to hijack the thread, but a side note and not entirely on topic: pgtyped-rescript exists if you’re interested in raw Postgres SQL with type safety in ReScript: GitHub - zth/pgtyped-rescript: pgTyped ReScript - Typesafe SQL in ReScript

pgtyped-rescript will eventually be superseded by a more powerful SQL type generator I’ve been working on that’s a fork/layer on top off of GitHub - wsporto/typesql: TypeSQL - Generate Typescript API from raw SQL. Supports PostgresSQL, MySQL, Sqlite, LibSQL (Turso) and D1 (Cloudflare). That project will be able to handle MySQL and SQLite as well in addition to Postgres for type safety. And it has more advanced analysis than pgtyped-rescript.

2 Likes
4

Thanks for pointing out! I saw pgtyped-rescript when I was looking around. TBH, I was confused by it a little because I have no if the SQL parser works properly not only for simple CRUD-requests, but for multi-level-nested monsters with JOINs after JOINs and WITH sub-queries on top of it. From my practical experience (in TypeScript) I see that Postgres.js can handle this because it is dumb and straightforward. I decided not to take the risk with pgtyped at this point.

However, it looks promising. In perspective, the approach with code generation can indeed fit ReScript better than anything else. I’m really looking forward at its development.

5

It uses the actual database to get types, so for the flat regular rows it handles them very well, regardless of how complicated the query is (since it’s the db that can tell you what the return types of a query row will be). What it doesn’t handle well (or at all occasionally) is producing types from returned JSON structures, like with json_build_object etc. But the new lib I’m working on will handle (most) of that as well, thanks to Typesql (which is super cool).

2 Likes
6

Also not quite related but if you want to try something new you can use Bun’s SQL API with rescript-bun; here’s an example. (Under the hood the bindings do something very similar to what you did)

Bun’s SQL API is heavily inspired by Postgres.js, so they look and feel almost identical with the advantage that Bun has alot more bindings already written in a single package.

Give it a try, you might like it