How to bind to parametrized query

1

I got stuck with writing the binding to parametrized query() call from https://node-postgres.com/features/queries.

const text = 'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *'
const values = ['brianc', 'brian.m.carlson@gmail.com']
// callback
client.query(text, values, (err, res) => {
  if (err) {
    console.log(err.stack)
  } else {
    console.log(res.rows[0])
    // { name: 'brianc', email: 'brian.m.carlson@gmail.com' }
  }
})

The input parameters array values is not homogeneous. The array may contain strings or numbers. Perhaps I could type it as array of strings and use strings only for query parameter values.

But returned result res is array of not homogeneous hash map. The hash map key is always string but value may be string or number. How do I type such a structure and bind to it?

1 Like
2

I think I found the way. I just have to use Js.Json.t

3

For the output rows, Js.Json.t is certainly a valid option, but having to decode every row when they are all guaranteed by the database to be the same shape, is a bit of a bummer. I would probably do something like this: https://rescript-lang.org/try?code=LYewJgrgNgpgBAFwJ4Ad4EUIwE5IMIgB2AZgJYDmcAvHAN4BQciq82IA7o3LAnAM4BHKAC5+CbKULl6AX3r1QkWHDxRSMQrxoMmyNIi574KAIbYTwQyzgBjE1CgAjEzYDWAHgDkJgHzU4ABQgKAikRO4AUnwAdACiAB6E0Qg+ADR0AERs7HwZombmSF6+MgCU1H4QhKQIXFww8Qg4hPZiElKifOKSlFR+pubA-hkApKRgGqHIGfWNza2SCKKLFXADFsNjE5o1SDNMDU3YLVBwxFAgJktnF1er60M0o+OTuzNyCuDQGFi4-gEEEgUUSYHD4IhkcjlPp0LgAAT4GjAcEO81O2CqogBakmyXSXXa5HSBRMRVU6k00QeaRUOMpdgczjc7kBkOi2R80Mq1S0cAyAl+ey4PDgGMI-xsdIQ6QefHSDKcLlcXNFVQCkop0pUEIo0UEUBlZgscts9kVblKsnkim+cAAqojsKC-jRnUgAjpmPpsv5aBkWsAYHk2j10hkYMATKQoMGCT05EwRfrhgBJAByAGVYgAlAAqcHTuYA8nAII6+AEAzB0hGo1BygA1ACCABk7bEMwEACQARnSXYATOVs7Fc3bs2n0wBxOAAKnelvoIqaXX8GsmFS4DpwbvZaq4THXmlSB7gAG1ybi41IAhlHBITIQbBlSulL5Tr+Rb-fSI-osBojsbAoD4Ig4XISNo0AkBgBfABdE8mCYAIcGwdJsj4LlTyYPh2BqGwAAsUWwbBYSQpCAB84AzGCYBQlUomiC4v0YhIki6JV6MtcimCotMiHgGFGOYgIMLPLIOFyOCzwABmk-0LCDODuJ4sp6CAA

The key is the usage in the following lines:

module UserQuery = Query({
  type row = {"name": string, "email": string}
  let sql = "INSERT INTO users(name, email) VALUES($1, $2) RETURNING *"
})

let test = client =>
  UserQuery.run(
    client,
    [Client.string("brianc"), Client.string("brian.m.carlson@gmail.com")],
    (err, rows) =>
      switch err {
      | Some(e) => Js.log(Js.Exn.stack(e))
      | None => Js.log(rows["rows"][0]["name"])
      })

The Query functor ties together the SQL query and its result row type in one place, meaning you just need to pay attention to that one place and take care to get the type correct there. The functor takes care of the details of the external binding.

After that you get the UserQuery module which automatically has the correct result row type for the query that it runs. You just need to pass it the input parameters and process the result.

The input parameters are being modelled as an abstract type using the ‘identity’ trick–the same thing that ReasonReact e.g. uses for rendering different types of content values in JSX.

1 Like
4

Thanks for your working example, I think I got it. It was definitely enlightening to see how elegant and simple this could be solved.

1 Like
5

Here’s my implementation without the functor: https://rescript-lang.org/try?code=LYewJgrgNgpgBAYSgSxgOwC5wLxwN4BQccGAngA7wZEkXzkCGATg8DWZXEzAM7QYAeAOQMAfDnw1iTEAHceALjjMWpYWJoBfAjRgAPDDCZoGUODwxNkaAOZKLV2znGMWwCQCIApMjDoMyGQeugZGJmbWGEqRznCurJ4+fpiBpMHE+obGpnAAZlAgDFF5BUWx8e643r7+qcE0AAIARjwAdDzoYHCZYTkAjhBGpEoAFBgANOaW1jaTKgxqFaKTIyDkASBoAgBSbQCiemitGMtcvPzqogCUsRBogTfY4neBngNDwdoEoJCwcACqHSYEkIxA4VBBUjgJmAMHs01s4yhMGADGQUHhjhsWgIX1gWEMFgkIwAxih-I9xKDEOTMK13kxSKTaRM4B4AJIAOQAynsAEoAFTgXIFAHk4BAgTwRjCYJMUWioDcAGoAQQAMv89tyRgASACMk11ACYbny9gL-nzOVyAOJwABUHkmAG0kKg6Q4ZiMPE0rAw0CSPFdJu7-O0ETYfX7kAHWsBWiTmFAeJsGjZUejEyBgMGALpI4gjIxMSbcRQ0j0YVrli6AozHa6xanEHiyQIkgAW3SYwJbcAAPnBuTmYMXKXBdq0ClGpwcjhYGCSANbjq5QoeczbwJ6TtozkblmtyHgugAMedasvXxGI2jv6+0QA

6

It is a bit simpler, but note that the type annotation at the usage site doesn’t actually enforce correct usage. E.g. you could potentially use the same query in different places with different result types. Not saying that you would, but it’s possible. The functor version ties them together so you would enforce that whenever you ran UserQuery.run(...), it would always give you results of the same type. And it just needs to be checked at one place–the functor definition site.

Also I noticed that the API provides a promise version in addition to the callback version. I would recommend using the promise API as it’s a bit easier to get right than callbacks.

3 Likes