This readme is generated with Nim to Markdown
SQL builder
SQL builder for INSERT
, UPDATE
, SELECT
and DELETE
queries.
The builder will check for NULL values and build a query with them.
After Nim's update to 0.19.0, the check for NULL values has been removed
due to the removal of nil
. This library's main goal is to allow the
user, to insert NULL values into the database again.
This packages uses Nim's standard packages, e.g. db_postgres, proc to escape qoutes.
NULL values
A NULL value
The global var dbNullVal
represents a NULL value. Use dbNullVal
in your args, if you need to insert/update to a NULL value.
Insert value or NULL
The global proc dbValOrNull()
will check, if it's contain a value
or is empty. If it contains a value, the value will be used in the args,
otherwise a NULL value (dbNullVal
) will be used.
dbValOrNull()
accepts both strings and int.
Executing DB commands
The examples below support the various DB commands such as exec
,
tryExec
, insertID
, tryInsertID
, etc.
Examples (NULL values)
All the examples uses a table named: myTable
and they use the WHERE argument on: name
.
Update string & int
Version 1
Required if NULL values could be expected
let a = genArgs("em@em.com", 20, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, int
# ==> UPDATE myTable SET email = ?, age = ? WHERE name = ?
Version 2
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"]), "em@em.com", 20, "John")
# ==> string, int
# ==> UPDATE myTable SET email = ?, age = ? WHERE name = ?
Update NULL & int
let a = genArgs("", 20, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> NULL, int
# ==> UPDATE myTable SET email = NULL, age = ? WHERE name = ?
Update string & NULL
a = genArgs("aa@aa.aa", dbNullVal, "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, NULL
# ==> UPDATE myTable SET email = ?, age = NULL WHERE name = ?
Error: Update string & NULL
An empty string, "", will be inserted into the database as NULL.
Empty string cannot be used for an INTEGER column. You therefore
need to use the dbValOrNull()
or dbNullVal
for int-values
.
a = genArgs("aa@aa.aa", "", "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> string, ERROR
# ==> UPDATE myTable SET email = ?, age = ? WHERE name = ?
# ==> To insert a NULL into a int-field, it is required to use dbValOrNull()
# or dbNullVal, it is only possible to pass and empty string.
Update NULL & NULL
let cc = ""
a = genArgs(dbValOrNull(cc), dbValOrNull(cc), "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> NULL, NULL
# ==> UPDATE myTable SET email = NULL, age = NULL WHERE name = ?
Update unknow value - maybe NULL
a = genArgs(dbValOrNull(stringVar), dbValOrNull(intVar), "John")
exec(db, sqlUpdate("myTable", ["email", "age"], ["name"], a.query), a.args)
# ==> NULL, NULL -or- STRING, INT
Examples (INSERT)
Insert without NULL
exec(db, sqlInsert("myTable", ["email", "age"]), "em@em.com" , 20)
# OR
insertID(db, sqlInsert("myTable", ["email", "age"]), "em@em.com", 20)
# ==> INSERT INTO myTable (email, age) VALUES (?, ?)
Insert with NULL
let a = genArgs("em@em.com", dbNullVal)
exec(db, sqlInsert("myTable", ["email", "age"], a.query), a.args)
# OR
insertID(db, sqlInsert("myTable", ["email", "age"], a.query), a.args)
# ==> INSERT INTO myTable (email) VALUES (?)
Examples (SELECT)
Select without NULL
getValue(db, sqlSelect("myTable",
["email", "age"], [""], ["name ="], "", "", ""), "John")
# SELECT email, age FROM myTable WHERE name = ?
getValue(db, sqlSelect("myTable",
["myTable.email", "myTable.age", "company.name"],
["company ON company.email = myTable.email"],
["myTable.name =", "myTable.age ="], "", "", ""),
"John", "20")
# SELECT myTable.email, myTable.age, company.name
# FROM myTable
# LEFT JOIN company ON company.email = myTable.email
# WHERE myTable.name = ? AND myTable.age = ?
getAllRows(db, sqlSelect("myTable",
["myTable.email", "myTable.age", "company.name"],
["company ON company.email = myTable.email"],
["company.name ="], "20,22,24", "myTable.age", "ORDER BY myTable.email"),
"BigBiz")
# SELECT myTable.email, myTable.age, company.name
# FROM myTable LEFT JOIN company ON company.email = myTable.email
# WHERE company.name = ? AND myTable.age IN (20,22,24)
# ORDER BY myTable.email
Select with NULL
let a = genArgs(dbNullVal)
getValue(db, sqlSelect("myTable",
["email", "age"], [""], ["name ="], "", "", "", a.query), a.args)
# SELECT email, age FROM myTable WHERE name = NULL
let a = genArgs("John", dbNullVal)
getValue(db, sqlSelect("myTable",
["myTable.email", "myTable.age", "company.name"],
["company ON company.email = myTable.email"],
["myTable.name =", "myTable.age ="], "", "", "", a.query), a.args)
# SELECT myTable.email, myTable.age, company.name
# FROM myTable
# LEFT JOIN company ON company.email = myTable.email
# WHERE myTable.name = ? AND myTable.age = NULL
let a = genArgs(dbNullVal)
getAllRows(db, sqlSelect("myTable",
["myTable.email", "myTable.age", "company.name"],
["company ON company.email = myTable.email"],
["company.name ="], "20,22,24", "myTable.age", "ORDER BY myTable.email", a.query),
a.args)
# SELECT myTable.email, myTable.age, company.name
# FROM myTable LEFT JOIN company ON company.email = myTable.email
# WHERE company.name = NULL AND myTable.age IN (20,22,24)
# ORDER BY myTable.email
Credit
Inspiration for builder: Nim Forum
Imports
import strutils, db_postgres
Types
Procs
proc argType*
proc argType*(v: ArgObj): ArgObj =
Checks if a ArgObj
is NULL and return
dbNullVal
. If it's not NULL, the passed
ArgObj
is returned.
proc argType*
proc argType*(v: string | int): ArgObj =
Transforms a string or int to a ArgObj
proc dbValOrNull*
proc dbValOrNull*(v: string | int): ArgObj =
Return NULL obj if len() == 0, else return value obj
proc sqlInsert*
proc sqlInsert*(table: string, data: varargs[string], args: ArgsContainer.query): SqlQuery =
SQL builder for INSERT queries Checks for NULL values
proc sqlInsert*
proc sqlInsert*(table: string, data: varargs[string]): SqlQuery =
SQL builder for INSERT queries Does NOT check for NULL values
proc sqlUpdate*
proc sqlUpdate*(table: string, data: varargs[string], where: varargs[string], args: ArgsContainer.query): SqlQuery =
SQL builder for UPDATE queries Checks for NULL values
proc sqlUpdate*
proc sqlUpdate*(table: string, data: varargs[string], where: varargs[string]): SqlQuery =
SQL builder for UPDATE queries Does NOT check for NULL values
proc sqlDelete*
proc sqlDelete*(table: string, where: varargs[string]): SqlQuery =
SQL builder for DELETE queries Does NOT check for NULL values
proc sqlDelete*
proc sqlDelete*(table: string, where: varargs[string], args: ArgsContainer.query): SqlQuery =
SQL builder for DELETE queries Checks for NULL values
proc sqlSelect*
proc sqlSelect*(table: string, data: varargs[string], left: varargs[string], whereC: varargs[string], access: string, accessC: string, user: string): SqlQuery =
SQL builder for SELECT queries Does NOT check for NULL values
proc sqlSelect*
proc sqlSelect*(table: string, data: varargs[string], left: varargs[string], whereC: varargs[string], access: string, accessC: string, user: string, args: ArgsContainer.query): SqlQuery =
SQL builder for SELECT queries Checks for NULL values
Templates
template genArgs*[T]
template genArgs*[T](arguments: varargs[T, argType]): ArgsContainer =
Create argument container for query and passed args
Other
ArgObj*
ArgObj* = object
Argument object
ArgsContainer
ArgsContainer = object
Argument container used for queries and args
var dbNullVal*
var dbNullVal*: ArgObj
Global NULL value