# db_sqlite

- Manual
- Standard library
- Index

Search:
Group by:

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 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 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()

# See also

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

# Imports

strutils, sqlite3, db_common

# Types

    DbConn = PSqlite3

  Encapsulates a database connection. Source Edit

    Row = seq[string]

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

    InstantRow = Pstmt

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

# Procs

    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.

  Examples:

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

  Source Edit

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

  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 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 `[]`(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 len(row: InstantRow): int32 {...}{.inline, raises: [], tags: [].}

  Returns number of columns in a row.

  See also:

  - instantRows iterator example code
  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 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 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 tryInsertID(db: DbConn; query: SqlQuery; args: varargs[string, `$`]): int64 {...}{.
        tags: [WriteDbEffect], raises: [].}

  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 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 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 close(db: DbConn) {...}{.tags: [DbEffect], raises: [DbError].}

  Closes the database connection.

  Examples:

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

  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 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

# 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 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; 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 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

# Exports

dbSerial, dbUser5, dbLine, dbBool, DbTypeKind, dbUInt, dbGeometry, dbInet, dbUser4, sql, dbUser3, dbPolygon, DbType, dbSet, dbFloat, dbMacAddress, dbVarchar, dbBit, dbArray, dbUser1, dbLseg, SqlQuery, db_common, DbColumn, dbBox, ReadDbEffect, dbNull, DbError, dbDate, dbUuid, dbJson, dbUrl, dbDecimal, dbXml, dbUnknown, WriteDbEffect, dbBlob, dbEnum, dbError, dbTime, DbColumns, dbPoint, DbEffect, dbTimeInterval, dbPath, dbTimestamp, dbCircle, dbInt, dbDatetime, dbComposite, dbFixedChar, dbUser2