I wonder would it be possible to write sql string formatter similar to fmt that would properly escape SQL values?
sql"select * from users where id = {1} and is_blocked = {false}"
# => (query: "select * from users where id = ?", values: @["1", "false"])
Example, playground:
type SQL = tuple[query: string, values: seq[string]]
proc to_sql_string_value(v: int | string | bool): string = $v
template sql(code: untyped): SQL =
# Some magic that produces result below
#
# For each found value the `value.to_sql_string_value` called to convert it
# to string `@[1.to_sql_string_value, false.to_sql_string_value]`
("select * from users where id = ?", @["1", "false"])
echo sql"select * from users where id = {1} and is_blocked = {false}"
Of course it would be possible to write formatter similar to fmt - after all it is just a simple macrom that parses argument string and generates some trivial code with string concatenation.
You can check out https://github.com/nim-lang/Nim/blob/f25243140baa00db4a663d453a262e41f8d9073f/lib/pure/strformat.nim#L564 for implementation if you would prefer to keep the same semantics.
Also - have you seen https://github.com/moigagoo/norm#create-tables? From what I can tell norm allows to interact with databases using nim types.
Thanks, I don't know how macros works, but somehow with try and error I did it it works! :)
P.S.
As for Norm, yes, saw it, nice library, but I prefer to use plain SQL, as I already know it and don't need to learn Nim SQL-like API for generating schema or queries.
Thanks, but that's exactly why I don't want to use Nim DSL for SQL, you need to remember to write selectdistinct "somecolumn" instead of SELECT DISTINCT somecolumn and lots of similar quirks.
So, instead of just SQL you to learn and remember x2 - the SQL itself and its copy in Nim DSL. Because you would have to write SQL anyway, for complex queries or working in SQL console etc.
P.S.
I actually wrote my own driver for PostgreSQL, example
...
let users = db.table(User, "users")
var jim = User(id: 1, name: "Jim", age: 30)
users.save jim
assert users.find(sql"age = {31}") == @[jim]
assert users.find_by_id(1) == jim.some
assert users[1] == jim
assert users.count(sql"age = {31}") == 1
Norm doesn’t offer an SQL query constructor yet, so your conditions are plain old vanilla SQL.
To safely insert values, use placeholders (? for SQLite, $1 for Postgres) with a list of values.
That is a basic default feature of the core of language, names can have spaces in Nim, therefore that is perfectly valid in Nim, example https://play.nim-lang.org/#ix=2Wwy
But having said that..., make it happen, I am waiting your Pull Request to make it better and more close to SQL as possible, I will merge, thats what I want to, what you say is literally the original design.
The idea is that people that knows SQL, can write SQL directly in the DSL, and people that dont know SQL, can use any SQL WYSIWYG and paste the results into the DSL.
:)
can use any SQL and paste the results into the DSL
I didn't realised it's so close, from the examples it wasn't obvious. And type-safe SQL is definitely a cool feature. Nice to know Nim macros is so advanced that it's possible to write macros to validate SQL types and columns.