Note: In order to use this module, run nimble install db_connector.

A higher level SQLite database wrapper. This interface is implemented for other databases too.

Basic usage

The basic flow of using this module is:

  1. Open database connection
  2. Execute SQL query
  3. Close database connection

Parameter substitution

All db_* modules support the same form of parameter substitution. That is, using the ? (question mark) to signify the place where a value should be placed. For example:

  1. sql"INSERT INTO my_table (colA, colB, colC) VALUES (?, ?, ?)"

Opening a connection to a database

  1. import db_connector/db_sqlite
  3. # user, password, database name can be empty.
  4. # These params are not used on db_sqlite module.
  5. let db = open("mytest.db", "", "", "")
  6. db.close()

Creating a table

  1. db.exec(sql"DROP TABLE IF EXISTS my_table")
  2. db.exec(sql"""CREATE TABLE my_table (
  3.                  id   INTEGER,
  4.                  name VARCHAR(50) NOT NULL
  5.               )""")

Inserting data

  1. db.exec(sql"INSERT INTO my_table (id, name) VALUES (0, ?)",
  2.         "Jack")

Larger example

  1. import db_connector/db_sqlite
  2. import std/math
  4. let db = open("mytest.db", "", "", "")
  6. db.exec(sql"DROP TABLE IF EXISTS my_table")
  7. db.exec(sql"""CREATE TABLE my_table (
  8.                  id    INTEGER PRIMARY KEY,
  9.                  name  VARCHAR(50) NOT NULL,
  10.                  i     INT(11),
  11.                  f     DECIMAL(18, 10)
  12.               )""")
  14. db.exec(sql"BEGIN")
  15. for i in 1..1000:
  16.   db.exec(sql"INSERT INTO my_table (name, i, f) VALUES (?, ?, ?)",
  17.           "Item#" & $i, i, sqrt(i.float))
  18. db.exec(sql"COMMIT")
  20. for x in db.fastRows(sql"SELECT * FROM my_table"):
  21.   echo x
  23. let id = db.tryInsertId(sql"""INSERT INTO my_table (name, i, f)
  24.                               VALUES (?, ?, ?)""",
  25.                         "Item#1001", 1001, sqrt(1001.0))
  26. echo "Inserted item: ", db.getValue(sql"SELECT name FROM my_table WHERE id=?", id)
  28. db.close()

Storing binary data example

  1. import std/random
  3. ## Generate random float datas
  4. var orig = newSeq[float64](150)
  5. randomize()
  6. for x in orig.mitems:
  7.   x = rand(1.0)/10.0
  9. let db = open("mysqlite.db", "", "", "")
  10. block: ## Create database
  11.   ## Binary datas needs to be of type BLOB in SQLite
  12.   let createTableStr = sql"""CREATE TABLE test(
  13.     id INTEGER NOT NULL PRIMARY KEY,
  14.     data BLOB
  15.   )
  16.   """
  17.   db.exec(createTableStr)
  19. block: ## Insert data
  20.   var id = 1
  21.   ## Data needs to be converted to seq[byte] to be interpreted as binary by bindParams
  22.   var dbuf = newSeq[byte](orig.len*sizeof(float64))
  23.   copyMem(unsafeAddr(dbuf[0]), unsafeAddr(orig[0]), dbuf.len)
  25.   ## Use prepared statement to insert binary data into database
  26.   var insertStmt = db.prepare("INSERT INTO test (id, data) VALUES (?, ?)")
  27.   insertStmt.bindParams(id, dbuf)
  28.   let bres = db.tryExec(insertStmt)
  29.   ## Check insert
  30.   doAssert(bres)
  31.   # Destroy statement
  32.   finalize(insertStmt)
  34. block: ## Use getValue to select data
  35.   var dataTest = db.getValue(sql"SELECT data FROM test WHERE id = ?", 1)
  36.   ## Calculate sequence size from buffer size
  37.   let seqSize = int(dataTest.len*sizeof(byte)/sizeof(float64))
  38.   ## Copy binary string data in dataTest into a seq
  39.   var res: seq[float64] = newSeq[float64](seqSize)
  40.   copyMem(unsafeAddr(res[0]), addr(dataTest[0]), dataTest.len)
  42.   ## Check datas obtained is identical
  43.   doAssert res == orig
  45. db.close()

Note

This module does not implement any ORM features such as mapping the types from the schema. Instead, a seq[string] is returned for each row.

The reasoning is as follows:

  1. it’s close to what many DBs offer natively (char**)
  2. it hides the number of types that the DB supports (int? int64? decimal up to 10 places? geo coords?)
  3. it’s convenient when all you do is to forward the data to somewhere else (echo, log, put the data into a new query)

See also

Imports

sqlite3, db_common, dbutils

Types

  1. DbConn = PSqlite3

Encapsulates a database connection.

  1. InstantRow = PStmt

A handle that can be used to get a row’s column text on demand.

  1. Row = seq[string]

A row of a dataset. NULL database values will be converted to an empty string.

  1. SqlPrepared = distinct PStmt

a identifier for the prepared queries

Procs

  1. proc `[]`(row: InstantRow; col: int32): string {.inline, ...raises: [], tags: [],
  2.     forbids: [].}

Returns text for given column of the row.

See also:

  1. proc bindNull(ps: SqlPrepared; paramIdx: int) {....raises: [DbError], tags: [],
  2.     forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: float64) {.
  2.     ...raises: [DbError], tags: [], forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: int) {....raises: [DbError],
  2.     tags: [], forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: int32) {....raises: [DbError],
  2.     tags: [], forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: int64) {....raises: [DbError],
  2.     tags: [], forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: openArray[byte]; copy = true) {.
  2.     ...raises: [DbError], tags: [], forbids: [].}
  1. proc bindParam(ps: SqlPrepared; paramIdx: int; val: string; copy = true) {.
  2.     ...raises: [DbError], tags: [], forbids: [].}
  1. proc close(db: DbConn) {....tags: [DbEffect], raises: [DbError], forbids: [].}

Closes the database connection.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. db.close()
  1. proc dbError(db: DbConn) {.noreturn, ...raises: [DbError], tags: [], forbids: [].}

Raises a DbError exception.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. if not db.tryExec(sql"SELECT * FROM not_exist_table"):
  3.   dbError(db)
  4. db.close()
  1. proc dbQuote(s: string): string {....raises: [], tags: [], forbids: [].}

Escapes the ‘ (single quote) char to ‘’. Because single quote is used for defining VARCHAR in SQL.

Example:

  1. doAssert dbQuote("'") == "''''"
  2. doAssert dbQuote("A Foobar's pen.") == "'A Foobar''s pen.'"
  1. proc exec(db: DbConn; query: SqlQuery; args: varargs[string, `$`]) {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}

Executes the query and raises a DbError exception if not successful.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. try:
  3.   db.exec(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
  4.           1, "item#1")
  5. except:
  6.   stderr.writeLine(getCurrentExceptionMsg())
  7. finally:
  8.   db.close()
  1. proc execAffectedRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): int64 {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}

Executes the query (typically “UPDATE”) and returns the number of affected rows.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. doAssert db.execAffectedRows(sql"UPDATE my_table SET name = 'TEST'") == 2
  11. db.close()
  1. proc execAffectedRows(db: DbConn; stmtName: SqlPrepared): int64 {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}
  1. proc finalize(sqlPrepared: SqlPrepared) {.discardable, ...raises: [], tags: [],
  2.     forbids: [].}
  1. proc getAllRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): seq[
  2.     Row] {....tags: [ReadDbEffect], raises: [DbError], forbids: [].}

Executes the query and returns the whole result dataset.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. doAssert db.getAllRows(sql"SELECT id, name FROM my_table") == @[Row(@["1", "item#1"]), Row(@["2", "item#2"])]
  10. db.close()
  1. proc getAllRows(db: DbConn; stmtName: SqlPrepared): seq[Row] {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}
  1. proc getRow(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): Row {.
  2.     ...tags: [ReadDbEffect], raises: [DbError], forbids: [].}

Retrieves a single row. If the query doesn’t return any rows, this proc will return a Row with empty strings for each column.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. doAssert db.getRow(sql"SELECT id, name FROM my_table"
  10.                    ) == Row(@["1", "item#1"])
  11. doAssert db.getRow(sql"SELECT id, name FROM my_table WHERE id = ?",
  12.                    2) == Row(@["2", "item#2"])
  14. # Returns empty.
  15. doAssert db.getRow(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
  16.                    3, "item#3") == @[]
  17. doAssert db.getRow(sql"DELETE FROM my_table WHERE id = ?", 3) == @[]
  18. doAssert db.getRow(sql"UPDATE my_table SET name = 'ITEM#1' WHERE id = ?",
  19.                    1) == @[]
  20. db.close()
  1. proc getValue(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): string {.
  2.     ...tags: [ReadDbEffect], raises: [DbError], forbids: [].}

Executes the query and returns the first column of the first row of the result dataset. Returns “” if the dataset contains no rows or the database value is NULL.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. doAssert db.getValue(sql"SELECT name FROM my_table WHERE id = ?",
  10.                      2) == "item#2"
  11. doAssert db.getValue(sql"SELECT id, name FROM my_table") == "1"
  12. doAssert db.getValue(sql"SELECT name, id FROM my_table") == "item#1"
  14. db.close()
  1. proc getValue(db: DbConn; stmtName: SqlPrepared): string {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [], forbids: [].}
  1. proc insert(db: DbConn; query: SqlQuery; pkName: string;
  2.             args: varargs[string, `$`]): int64 {....tags: [WriteDbEffect],
  3.     raises: [DbError], forbids: [].}

same as insertId

  1. proc insertID(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): int64 {.
  2.     ...tags: [WriteDbEffect], raises: [DbError], forbids: [].}

Executes the query (typically “INSERT”) and returns the generated ID for the row.

Raises a DbError exception when failed to insert row. For Postgre this adds RETURNING id to the query, so it only works if your primary key is named id.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
  4. for i in 0..2:
  5.   let id = db.insertID(sql"INSERT INTO my_table (id, name) VALUES (?, ?)", i, "item#" & $i)
  6.   echo "LoopIndex = ", i, ", InsertID = ", id
  8. # Output:
  9. # LoopIndex = 0, InsertID = 1
  10. # LoopIndex = 1, InsertID = 2
  11. # LoopIndex = 2, InsertID = 3
  13. db.close()
  1. proc len(row: InstantRow): int32 {.inline, ...raises: [], tags: [], forbids: [].}

Returns number of columns in a row.

See also:

  1. proc open(connection, user, password, database: string): DbConn {.
  2.     ...tags: [DbEffect], raises: [DbError], forbids: [].}

Opens a database connection. Raises a DbError exception if the connection could not be established.

Note: Only the connection parameter is used for sqlite.

Examples:

  1. try:
  2.   let db = open("mytest.db", "", "", "")
  3.   ## do something...
  4.   ## db.getAllRows(sql"SELECT * FROM my_table")
  5.   db.close()
  6. except:
  7.   stderr.writeLine(getCurrentExceptionMsg())
  1. proc prepare(db: DbConn; q: string): SqlPrepared {....raises: [DbError], tags: [],
  2.     forbids: [].}

Creates a new SqlPrepared statement.

  1. proc setEncoding(connection: DbConn; encoding: string): bool {....tags: [DbEffect],
  2.     raises: [DbError], forbids: [].}

Sets the encoding of a database connection, returns true for success, false for failure.

Note: The encoding cannot be changed once it’s been set. According to SQLite3 documentation, any attempt to change the encoding after the database is created will be silently ignored.

  1. proc tryExec(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): bool {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}

Tries to execute the query and returns true if successful, false otherwise.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. if not db.tryExec(sql"SELECT * FROM my_table"):
  3.   dbError(db)
  4. db.close()
  1. proc tryExec(db: DbConn; stmtName: SqlPrepared): bool {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [], forbids: [].}
  1. proc tryInsert(db: DbConn; query: SqlQuery; pkName: string;
  2.                args: varargs[string, `$`]): int64 {....tags: [WriteDbEffect],
  3.     raises: [DbError], forbids: [].}

same as tryInsertID

  1. proc tryInsertID(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): int64 {.
  2.     ...tags: [WriteDbEffect], raises: [DbError], forbids: [].}

Executes the query (typically “INSERT”) and returns the generated ID for the row or -1 in case of an error.

Examples:

  1. let db = open("mytest.db", "", "", "")
  2. db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")
  4. doAssert db.tryInsertID(sql"INSERT INTO not_exist_table (id, name) VALUES (?, ?)",
  5.                         1, "item#1") == -1
  6. db.close()
  1. proc unsafeColumnAt(row: InstantRow; index: int32): cstring {.inline,
  2.     ...raises: [], tags: [], forbids: [].}

Returns cstring for given column of the row.

See also:

Iterators

  1. iterator fastRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): Row {.
  2.     ...tags: [ReadDbEffect], raises: [DbError, DbError], forbids: [].}

Executes the query and iterates over the result dataset.

This is very fast, but potentially dangerous. Use this iterator only if you require ALL the rows.

Note: Breaking the fastRows() iterator during a loop will cause the next database query to raise a DbError exception unable to close due to ….

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. for row in db.fastRows(sql"SELECT id, name FROM my_table"):
  10.   echo row
  12. # Output:
  13. # @["1", "item#1"]
  14. # @["2", "item#2"]
  16. db.close()
  1. iterator fastRows(db: DbConn; stmtName: SqlPrepared): Row {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}
  1. iterator instantRows(db: DbConn; columns: var DbColumns; query: SqlQuery;
  2.                      args: varargs[string, `$`]): InstantRow {.
  3.     ...tags: [ReadDbEffect], raises: [DbError, DbError], forbids: [].}

Similar to instantRows iterator, but sets information about columns to columns.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. var columns: DbColumns
  10. for row in db.instantRows(columns, sql"SELECT * FROM my_table"):
  11.   discard
  12. echo columns[0]
  14. # Output:
  15. # (name: "id", tableName: "my_table", typ: (kind: dbNull,
  16. # notNull: false, name: "INTEGER", size: 0, maxReprLen: 0, precision: 0,
  17. # scale: 0, min: 0, max: 0, validValues: @[]), primaryKey: false,
  18. # foreignKey: false)
  20. db.close()
  1. iterator instantRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): InstantRow {.
  2.     ...tags: [ReadDbEffect], raises: [DbError, DbError], forbids: [].}

Similar to fastRows iterator but returns a handle that can be used to get column text on demand using []. Returned handle is valid only within the iterator body.

Examples:

  1.  let db = open("mytest.db", "", "", "")
  3.  # Records of my_table:
  4.  # | id | name     |
  5.  # |----|----------|
  6.  # |  1 | item#1   |
  7.  # |  2 | item#2   |
  9.  for row in db.instantRows(sql"SELECT * FROM my_table"):
  10.    echo "id:" & row[0]
  11.    echo "name:" & row[1]
  12.    echo "length:" & $len(row)
  14.  # Output:
  15.  # id:1
  16.  # name:item#1
  17.  # length:2
  18.  # id:2
  19.  # name:item#2
  20.  # length:2
  22.  db.close()
  1. iterator instantRows(db: DbConn; stmtName: SqlPrepared): InstantRow {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}
  1. iterator rows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): Row {.
  2.     ...tags: [ReadDbEffect], raises: [DbError], forbids: [].}

Similar to fastRows iterator, but slower and safe.

Examples:

  1. let db = open("mytest.db", "", "", "")
  3. # Records of my_table:
  4. # | id | name     |
  5. # |----|----------|
  6. # |  1 | item#1   |
  7. # |  2 | item#2   |
  9. for row in db.rows(sql"SELECT id, name FROM my_table"):
  10.   echo row
  12. ## Output:
  13. ## @["1", "item#1"]
  14. ## @["2", "item#2"]
  16. db.close()
  1. iterator rows(db: DbConn; stmtName: SqlPrepared): Row {.
  2.     ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError], forbids: [].}

Macros

  1. macro bindParams(ps: SqlPrepared; params: varargs[untyped]): untyped

Templates

  1. template dbBindParamError(paramIdx: int; val: varargs[untyped])

Raises a DbError exception.

  1. template exec(db: DbConn; stmtName: SqlPrepared; args: varargs[typed]): untyped

Exports

DbTypeKind, sql, DbType, SqlQuery, DbColumn, ReadDbEffect, DbError, WriteDbEffect, dbError, DbColumns, DbEffect