CODEWITHSUNDEEP
nim-lang
Johannes Hoff
Johannes Hoff

Reputation: 3901

How to construct a `select ... in` SQL query in nim?

I'm using nim and db_sqlite to fetch some rows with certain _ids from a database table. For example:

for row in db.fastRows("SELECT * FROM t WHERE _id IN (?, ?)", @["1", "2"]):
  echo row

This works as expected, however, the sequence at the end is constructed dynamically at runtime, which means I need a variable amount of ? in the query. I end up creating a sequence with question marks, joining them, interpolating the string and turning it into a database query:

var qs : seq[string]
for id in ids:
  qs.add("?")
let query_string = """SELECT * FROM t WHERE _id IN ($1)""" % join(qs, ",")
let query = SqlQuery(query_string)
for row in db.fastRows(query, ids):
  echo row

Is there a better way to construct a select ... in query in nim? Ideally one with just one ? in the SqlQuery.

(For what it's worth, the current behavior is similar to other languages I've used)

Upvotes: 1

Views: 361

Answers (1)

shirleyquirk
shirleyquirk

Reputation: 1598

you could do the replacement manually, here's one way using strformat and map

import strformat,db_sqlite,sequtils,strutils
#assuming ids is a seq[string] here
let query = sql(&"SELECT * FROM t WHERE _id IN ({ids.map(dbQuote).join(\",\")})")
for row in db.fastRows(query):
  echo row

Upvotes: 3

Related Questions