Query constructor

[moigagoo]

Norm's select procs work with vanilla WHERE conditions, which you have to write manually. The problem is you have to know which table and column names correspond to which type and attribute names. This is error-prone, fragile in the long run, and all together ugly.

We need a query constructor that would allow the programmer to compose queries in terms of Nim types and object fields.

Instead of db.select(pets, """"Pet".species LIKE $1""", "dog"), I want something like db.select(pets, qry(Pet.species ~ "$1"), "dog") or db.select(pets, qry(Pet.species.like "$1"), "dog") or even db.select(pets, qry(Pet.species ~ "dog"))

[Clonkk]

I don't know if this helps, but in my projects i've often used helper procs that look like this :

template newWith[T: Model](t: typedesc[T], dbConn: DbConn, field: static[string], val: untyped) : T =
  var res = new(t)
  let qry = $(t)& "." & field & "=?"
  dbConn.select(res, qry, val)
  res

var dog = Pet.newWith(dbConn, "species", "dog")

If you're not afraid of a bit more "macro" heavy style you can even do :

macro newWith[T: Model](dbConn: DbConn, model: var T, field: untyped, val: untyped) =
  let t = getTypeInst(model)
  result = newStmtList()
  result.add newCall("new", model)
  let errstr = &"Error {model}.{field} does not compile"
  let qry = $(t) & "." & $field & "=?"
  result.add quote do:
    when compiles(`model`.`field`):
      dbConn.select(`model`, `qry`, `val`)
    else:
      {.error: `errstr`.}
  # echo result.repr


var dog : Pet
newWith(dbConn, dog, species, "dog")

Which will check that dog.species compiles and raise a CT error otherwise.

Many variation of API are possible with dog.species parsed as one argument, 2 argument, as static string etc. and all operation & check performed at CT.

Penny for your thoughts :)