# std/db_sqlite

Dark Mode

- Manual
- Standard library
- Index
- Compiler docs
- Fusion docs
- devel, stable

Search:
Source Edit

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:

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

## Opening a connection to a database

    import std/db_sqlite

    # user, password, database name can be empty.
    # These params are not used on db_sqlite module.
    let db = open("mytest.db", "", "", "")
    db.close()

## Creating a table

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

## Inserting data

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

## Larger example

    import std/[db_sqlite, math]

    let db = open("mytest.db", "", "", "")

    db.exec(sql"DROP TABLE IF EXISTS my_table")
    db.exec(sql"""CREATE TABLE my_table (
                     id    INTEGER PRIMARY KEY,
                     name  VARCHAR(50) NOT NULL,
                     i     INT(11),
                     f     DECIMAL(18, 10)
                  )""")

    db.exec(sql"BEGIN")
    for i in 1..1000:
      db.exec(sql"INSERT INTO my_table (name, i, f) VALUES (?, ?, ?)",
              "Item#" & $i, i, sqrt(i.float))
    db.exec(sql"COMMIT")

    for x in db.fastRows(sql"SELECT * FROM my_table"):
      echo x

    let id = db.tryInsertId(sql"""INSERT INTO my_table (name, i, f)
                                  VALUES (?, ?, ?)""",
                            "Item#1001", 1001, sqrt(1001.0))
    echo "Inserted item: ", db.getValue(sql"SELECT name FROM my_table WHERE id=?", id)

    db.close()

## Storing binary data example

    import std/random

    ## Generate random float datas
    var orig = newSeq[float64](150)
    randomize()
    for x in orig.mitems:
      x = rand(1.0)/10.0

    let db = open("mysqlite.db", "", "", "")
    block: ## Create database
      ## Binary datas needs to be of type BLOB in SQLite
      let createTableStr = sql"""CREATE TABLE test(
        id INTEGER NOT NULL PRIMARY KEY,
        data BLOB
      )
      """
      db.exec(createTableStr)

    block: ## Insert data
      var id = 1
      ## Data needs to be converted to seq[byte] to be interpreted as binary by bindParams
      var dbuf = newSeq[byte](orig.len*sizeof(float64))
      copyMem(unsafeAddr(dbuf[0]), unsafeAddr(orig[0]), dbuf.len)

      ## Use prepared statement to insert binary data into database
      var insertStmt = db.prepare("INSERT INTO test (id, data) VALUES (?, ?)")
      insertStmt.bindParams(id, dbuf)
      let bres = db.tryExec(insertStmt)
      ## Check insert
      doAssert(bres)
      # Destroy statement
      finalize(insertStmt)

    block: ## Use getValue to select data
      var dataTest = db.getValue(sql"SELECT data FROM test WHERE id = ?", 1)
      ## Calculate sequence size from buffer size
      let seqSize = int(dataTest.len*sizeof(byte)/sizeof(float64))
      ## Copy binary string data in dataTest into a seq
      var res: seq[float64] = newSeq[float64](seqSize)
      copyMem(unsafeAddr(res[0]), addr(dataTest[0]), dataTest.len)

      ## Check datas obtained is identical
      doAssert res == orig

    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

- db_odbc module for ODBC database wrapper
- db_mysql module for MySQL database wrapper
- db_postgres module for PostgreSQL database wrapper

# Imports

sqlite3, macros, db_common, since, dbutils

# Types

    DbConn = PSqlite3

  Encapsulates a database connection. Source Edit

    InstantRow = PStmt

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

    Row = seq[string]

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

    SqlPrepared = distinct PStmt

  a identifier for the prepared queries Source Edit

# Procs

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

  Returns text for given column of the row.

  See also:

  - instantRows iterator example code
  Source Edit

    proc bindNull(ps: SqlPrepared; paramIdx: int) {....raises: [DbError], tags: [].}

  Sets the bindparam at the specified paramIndex to null (default behaviour by sqlite). Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: float64) {.
        ...raises: [DbError], tags: [].}

  Binds a 64bit float to the specified paramIndex. Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: int) {....raises: [DbError],
        tags: [].}

  Binds a int to the specified paramIndex. Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: int32) {....raises: [DbError],
        tags: [].}

  Binds a int32 to the specified paramIndex. Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: int64) {....raises: [DbError],
        tags: [].}

  Binds a int64 to the specified paramIndex. Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: openArray[byte]; copy = true) {.
        ...raises: [Exception, DbError], tags: [].}

  binds a blob to the specified paramIndex. if copy is true then SQLite makes its own private copy of the data immediately Source Edit

    proc bindParam(ps: SqlPrepared; paramIdx: int; val: string; copy = true) {.
        ...raises: [Exception, DbError], tags: [].}

  Binds a string to the specified paramIndex. if copy is true then SQLite makes its own private copy of the data immediately Source Edit

    proc close(db: DbConn) {....tags: [DbEffect], raises: [DbError].}

  Closes the database connection.

  Examples:

      let db = open("mytest.db", "", "", "")
      db.close()

  Source Edit

    proc dbError(db: DbConn) {.noreturn, ...raises: [DbError], tags: [].}

  Raises a `DbError` exception.

  Examples:

      let db = open("mytest.db", "", "", "")
      if not db.tryExec(sql"SELECT * FROM not_exist_table"):
        dbError(db)
      db.close()

  Source Edit

    proc dbQuote(s: string): string {....raises: [], tags: [].}

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

  Example:

      doAssert dbQuote("'") == "''''"
      doAssert dbQuote("A Foobar's pen.") == "'A Foobar''s pen.'"

  Source Edit

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

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

  Examples:

      let db = open("mytest.db", "", "", "")
      try:
        db.exec(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
                1, "item#1")
      except:
        stderr.writeLine(getCurrentExceptionMsg())
      finally:
        db.close()

  Source Edit

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

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

  Examples:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      doAssert db.execAffectedRows(sql"UPDATE my_table SET name = 'TEST'") == 2

      db.close()

  Source Edit

    proc execAffectedRows(db: DbConn; stmtName: SqlPrepared): int64 {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}

  Source Edit

    proc finalize(sqlPrepared: SqlPrepared) {.discardable, ...raises: [], tags: [].}

  Source Edit

    proc getAllRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): seq[
        Row] {....tags: [ReadDbEffect], raises: [DbError].}

  Executes the query and returns the whole result dataset.

  Examples:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      doAssert db.getAllRows(sql"SELECT id, name FROM my_table") == @[Row(@["1", "item#1"]), Row(@["2", "item#2"])]
      db.close()

  Source Edit

    proc getAllRows(db: DbConn; stmtName: SqlPrepared): seq[Row] {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}

  Source Edit

    proc getRow(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): Row {.
        ...tags: [ReadDbEffect], raises: [DbError].}

  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:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      doAssert db.getRow(sql"SELECT id, name FROM my_table"
                         ) == Row(@["1", "item#1"])
      doAssert db.getRow(sql"SELECT id, name FROM my_table WHERE id = ?",
                         2) == Row(@["2", "item#2"])

      # Returns empty.
      doAssert db.getRow(sql"INSERT INTO my_table (id, name) VALUES (?, ?)",
                         3, "item#3") == @[]
      doAssert db.getRow(sql"DELETE FROM my_table WHERE id = ?", 3) == @[]
      doAssert db.getRow(sql"UPDATE my_table SET name = 'ITEM#1' WHERE id = ?",
                         1) == @[]
      db.close()

  Source Edit

    proc getValue(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): string {.
        ...tags: [ReadDbEffect], raises: [DbError].}

  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:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      doAssert db.getValue(sql"SELECT name FROM my_table WHERE id = ?",
                           2) == "item#2"
      doAssert db.getValue(sql"SELECT id, name FROM my_table") == "1"
      doAssert db.getValue(sql"SELECT name, id FROM my_table") == "item#1"

      db.close()

  Source Edit

    proc getValue(db: DbConn; stmtName: SqlPrepared): string {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [].}

  Source Edit

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

  same as insertId Source Edit

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

  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:

      let db = open("mytest.db", "", "", "")
      db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")

      for i in 0..2:
        let id = db.insertID(sql"INSERT INTO my_table (id, name) VALUES (?, ?)", i, "item#" & $i)
        echo "LoopIndex = ", i, ", InsertID = ", id

      # Output:
      # LoopIndex = 0, InsertID = 1
      # LoopIndex = 1, InsertID = 2
      # LoopIndex = 2, InsertID = 3

      db.close()

  Source Edit

    proc len(row: InstantRow): int32 {.inline, ...raises: [], tags: [].}

  Returns number of columns in a row.

  See also:

  - instantRows iterator example code
  Source Edit

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

  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:

      try:
        let db = open("mytest.db", "", "", "")
        ## do something...
        ## db.getAllRows(sql"SELECT * FROM my_table")
        db.close()
      except:
        stderr.writeLine(getCurrentExceptionMsg())

  Source Edit

    proc prepare(db: DbConn; q: string): SqlPrepared {....raises: [DbError], tags: [].}

  Creates a new `SqlPrepared` statement. Source Edit

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

  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.

  Source Edit

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

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

  Examples:

      let db = open("mytest.db", "", "", "")
      if not db.tryExec(sql"SELECT * FROM my_table"):
        dbError(db)
      db.close()

  Source Edit

    proc tryExec(db: DbConn; stmtName: SqlPrepared): bool {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [].}

  Source Edit

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

  same as tryInsertID Source Edit

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

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

  Examples:

      let db = open("mytest.db", "", "", "")
      db.exec(sql"CREATE TABLE my_table (id INTEGER, name VARCHAR(50) NOT NULL)")

      doAssert db.tryInsertID(sql"INSERT INTO not_exist_table (id, name) VALUES (?, ?)",
                              1, "item#1") == -1
      db.close()

  Source Edit

    proc unsafeColumnAt(row: InstantRow; index: int32): cstring {.inline,
        ...raises: [], tags: [].}

  Returns cstring for given column of the row.

  See also:

  - instantRows iterator example code
  Source Edit

# Iterators

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

  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:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      for row in db.fastRows(sql"SELECT id, name FROM my_table"):
        echo row

      # Output:
      # @["1", "item#1"]
      # @["2", "item#2"]

      db.close()

  Source Edit

    iterator fastRows(db: DbConn; stmtName: SqlPrepared): Row {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}

  Source Edit

    iterator instantRows(db: DbConn; columns: var DbColumns; query: SqlQuery;
                         args: varargs[string, `$`]): InstantRow {.
        ...tags: [ReadDbEffect], raises: [DbError, DbError].}

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

  Examples:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      var columns: DbColumns
      for row in db.instantRows(columns, sql"SELECT * FROM my_table"):
        discard
      echo columns[0]

      # Output:
      # (name: "id", tableName: "my_table", typ: (kind: dbNull,
      # notNull: false, name: "INTEGER", size: 0, maxReprLen: 0, precision: 0,
      # scale: 0, min: 0, max: 0, validValues: @[]), primaryKey: false,
      # foreignKey: false)

      db.close()

  Source Edit

    iterator instantRows(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): InstantRow {.
        ...tags: [ReadDbEffect], raises: [DbError, DbError].}

  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:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      for row in db.instantRows(sql"SELECT * FROM my_table"):
        echo "id:" & row[0]
        echo "name:" & row[1]
        echo "length:" & $len(row)

      # Output:
      # id:1
      # name:item#1
      # length:2
      # id:2
      # name:item#2
      # length:2

      db.close()

  Source Edit

    iterator instantRows(db: DbConn; stmtName: SqlPrepared): InstantRow {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}

  Source Edit

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

  Similar to fastRows iterator, but slower and safe.

  Examples:

      let db = open("mytest.db", "", "", "")

      # Records of my_table:
      # | id | name     |
      # |----|----------|
      # |  1 | item#1   |
      # |  2 | item#2   |

      for row in db.rows(sql"SELECT id, name FROM my_table"):
        echo row

      ## Output:
      ## @["1", "item#1"]
      ## @["2", "item#2"]

      db.close()

  Source Edit

    iterator rows(db: DbConn; stmtName: SqlPrepared): Row {.
        ...tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}

  Source Edit

# Macros

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

  Source Edit

# Templates

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

  Raises a `DbError` exception. Source Edit

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

  Source Edit

# Exports

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