Ergonomic bindings to database drivers are so frustrating

1

in JS (might be a little pseudo)

  let sql = `insert into something (id, name, parentId) values (?,?,?)`
  await conn.execute(sql, [1, "blah", 2])

Its that damn array. It can contain numbers, strings, undefined, null, whatever.

One trick I found is that mysql drivers dont care if its a string or number, it will do the conversion automatically (not sure about postgres), so we can pass numbers and strings as a array.

But if we want strings and undefines things get ugly (undefines translate to NULL by the driver)

Best I came up with is

@unboxed type intOrStr = I(int) | S(string)
type maybeIntOrStr = option<intOrStr>
type multiTypeArray = array<maybeIntOrStr>

and a function to generate it like this, (if ~z can be undefined (NULL in the database))

let f = (~x: int, ~y: string, ~z: option<intOrStr>=None): multiTypeArray => {
  [Some(I(x)), Some(S(y)), z]
}

let r = f(~x=1, ~y="two")
Console.log(r) //=> [ 1, 'two', undefined ]
let r = f(~x=1, ~y="two", ~z=Some(I(100)))
Console.log(r) //=> [ 1, 'two', 100 ]

gross

Am I missing something? Is there some kind of variant decorator combo like

@unboxed type intOrStrOrUndefined = I(int) | S(string) | Undefined
2

Yes, exactly that, just add @as(undefined) to the undefined case and you’re good to go, it’ll be unrefined at runtime.

Off topic but related: https://github.com/zth/pgtyped-rescript/blob/rescript/RESCRIPT.md type safe SQL in Rescript.

1 Like
3

Ok so I’ve got this

@unboxed type queryParamTypes = I(int) | S(string) | @as(undefined) Undefined

let f = (~x: int, ~y: string, ~z=Undefined) => {
  [I(x), S(y), z]
}

let r = f(~x=1, ~y="two")
Console.log(r) //=> [ 1, 'two', undefined ]
let r = f(~x=1, ~y="two", ~z=Undefined)
Console.log(r) //=> [ 1, 'two', undefined ]
let r = f(~x=1, ~y="two", ~z=I(100))
Console.log(r) //=> [ 1, 'two', 100 ]

Its pretty good. A little awkward, especially at the [I(x), S(y), z] part. Is that the ergonomic its gonna get?

4

You could also use tuples to allow heterogenous types, or you could also use tagged templates starting from 11.1 to generate your queries.

1 Like
5

I tried tuples but I didn’t know a way to writing a binding for a variable length tuple parameter, only an array worked, in this case I kludged it by using a string array.

i.e.

@module("mysql2/promise") external make: {..} => t = "createPool"
@send external query: (Pool.t, string, array<string>) => 'a = "query"

Even if I got the binding right, I’d have to send the created tuple immediately to the binding and not be able to build up a few layers around the driver, like I use Mysql.insert below

let create = async (~db: Mysql.Pool.t, ~userId: int, ~pageId: int, ~folderId: int) => {
  let sql = `insert into bookmarks (userId, pageId, folderId) values (?,?,?)`
  let args = [userId, pageId, folderId]->iatos // iatos is utility function that converts array<int> to array<string>
  // let args = (userId, pageId, folderId)
  let results = await Mysql.insert(~db, ~sql, ~args)
  results.insertId
}
6

Why would you need a function to generate parameters?
You could probably make them without it, right? ReScript Playground
If you don’t mind wrapping the values in variants, I think it is a pretty decent solution.

7

For variable tuple length, the react bindings just define a different function for each length which I think works pretty well

@send external query: (Pool.t, string, 'a) => 'b = "query"
@send external query2: (Pool.t, string, ('a, 'b)) => 'c = "query"
@send external query3: (Pool.t, string, ('a, 'b, 'c)) => 'd = "query"
@send external query4: (Pool.t, string, ('a, 'b, 'c, 'd)) => 'e = "query"
2 Likes
8

agreed this is a good solution… however, you have to immediately use query3(…) and cant put layers between, afaik, for example, what I have now is

super simple bindings layer

@unboxed type paramTypes = I(int) | S(string) | @as(undefined) Null
@send external query: (Pool.t, string, array<paramTypes>) => 'a = "query"

next is “API” layer, for dealing with how annoying the mysql API is. Inserts returns [resultSetHeader, metadata],
select return [rows, metadata] (even if its a limit 1). selectAll should always return an array, however selectFirst should return an Option, I can hide all that in this layer.

let insert = async (~db: Pool.t, ~sql: string, ~args: array<paramTypes>) => {
  let rows = await query(db, sql, args)
  let result: resultSetHeader = Array.getUnsafe(rows, 0)
  result
}

let selectFirst = async (~db: Pool.t, ~sql: string, ~args: array<paramTypes>) => {
  let rows = await query(db, sql, args)
  let result = Array.getUnsafe(rows, 0)
  result[0]
}

let selectAll = async (~db: Pool.t, ~sql: string, ~args: array<paramTypes>) => {
  let rows = await query(db, sql, args)
  let result = Array.getUnsafe(rows, 0)
  result
}

let update = async (~db: Pool.t, ~sql: string, ~args: array<paramTypes>) => {
  let rows = await query(db, sql, args)
  let result: resultSetHeader = Array.getUnsafe(rows, 0)
  result
}

At the top we have the “Data Access Layer” (which will have many more functions than the above API layer), I can do things like this without worrying about meta data, etc

let find = async (~db: Mysql.Pool.t, ~userId: int, ~id: int): option<t> => {
  let sql = `select * from folders where userId=? and id=? limit 1`
  let args = [Mysql.I(userId), Mysql.I(id)]
  let maybeFolder = await Mysql.selectFirst(~db, ~sql, ~args)
  maybeFolder
}

let findAll = async (~db: Mysql.Pool.t, ~userId: int): array<t> => {
  let sql = `select * from folders where userId=?`
  let args = [Mysql.I(userId)]
  await Mysql.selectAll(~db, ~sql, ~args)
}

I expect 50+ DAL layers like above. I think its a benefit to put most of the complicated driver nonsense (I’ve never used that metadata from a SQL select once in my life). The API layer so far only needs insert, selectAll, selectFirst, update, delete and exists ( and “delete” is just an alias for update)

I suppose I could do variations like findAll3(~db, ~sql, ~userId), which would be a lot of boilerplate, or even pass the queryX function ( I think this is allowed, haven’t tried it ) findAll(~db, ~sql, ~userId, ~query=query3)