postgres

A fork of db_postgres, Nim's standard library higher level PostgreSQL database wrapper.

This is a work in progress, many procs are missing.

Parameter substitution

Unlike ndb/sqlite, you have to use $1, $2, $3, ... instead of ? as a parameter placeholders.

sql"INSERT INTO myTable (colA, colB, colC) VALUES ($1, $2, $3)"

Examples

Opening a connection to a database

import db_postgres
let db = open("localhost", "user", "password", "dbname")
db.close()

Creating a table

db.exec(sql"DROP TABLE IF EXISTS myTable")
db.exec(sql("""CREATE TABLE myTable (
                 id integer,
                 name varchar(50) not null)"""))

Inserting data

db.exec(sql"INSERT INTO myTable (id, name) VALUES (0, $1)",
        "Dominik")

See also

Types

DbConn = PPGconn
Encapsulates a database connection.
RowOld = seq[string]
A row of a dataset. NULL database values will be converted to nil.
Row = seq[DbValue]
A row of a dataset.
InstantRow = object
  res: PPGresult
  line: int
A handle that can be used to get a row's column text on demand.
SqlPrepared = distinct string
A identifier for the prepared queries.
DbValueKind = enum
  dvkBool, dvkInt, dvkFloat, dvkString, dvkTimestamptz, dvkOther, dvkNull
DbValueTypes = bool | int64 | float64 | string | DateTime | DbOther | DbNull
Possible value types
DbOther = object
  oid*: Oid
  value*: string
DbNull = object
NULL value.
DbValue = object
  case kind*: DbValueKind
  of dvkBool:
      b*: bool

  of dvkInt:
      i*: int64

  of dvkFloat:
      f*: float64

  of dvkString:
      s*: string

  of dvkTimestamptz:
      t*: DateTime

  of dvkOther:
      o: DbOther

  of dvkNull:
      nil

  

Procs

proc dbValue(v: DbValue): DbValue {...}{.raises: [], tags: [].}
Return v as is.
proc dbValue(v: int | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32): DbValue
Wrap integer value.
proc dbValue(v: float64): DbValue {...}{.raises: [], tags: [].}
Wrap float value.
proc dbValue(v: string): DbValue {...}{.raises: [], tags: [].}
Wrap string value.
proc dbValue(v: bool): DbValue {...}{.raises: [], tags: [].}
Wrap bool value.
proc dbValue(v: DateTime): DbValue {...}{.raises: [], tags: [].}
Wrap DateTime value.
proc dbValue(v: DbNull | type(nil)): DbValue
Wrap NULL value. Caveat: dbValue(nil) doesn't compile on Nim 0.19.x, see https://github.com/nim-lang/Nim/pull/9231.
proc dbValue[T](v: Option[T]): DbValue
Wrap value of type T or NULL.
proc `==`(a: DbValue; b: DbValue): bool {...}{.raises: [], tags: [].}
Compare two DB values.
proc dbError(db: DbConn) {...}{.noreturn, raises: [DbError], tags: [].}
Raises a DbError exception.
proc dbQuote(s: string): string {...}{.raises: [], tags: [].}
DB quotes the string. Escaping values to generate SQL queries is not recommended, bind values using the $1 instead.
proc `$`(v: DbValue): string {...}{.raises: [], tags: [].}
proc exec(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]) {...}{.
    tags: [ReadDbEffect, WriteDbEffect], raises: [DbError].}
Executes the query and raises DbError if not successful.
proc getRow(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]): Option[Row] {...}{.
    tags: [ReadDbEffect], raises: [DbError, ValueError].}
Retrieves a single row.
proc getAllRows(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]): seq[Row] {...}{.
    tags: [ReadDbEffect], raises: [DbError, ValueError].}
Executes the query and returns the whole result dataset.
proc insertID(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]): int64 {...}{.
    tags: [WriteDbEffect], raises: [DbError, DbError, ValueError].}
executes the query (typically "INSERT") and returns the generated ID for the row. For Postgre this adds RETURNING id to the query, so it only works if your primary key is named id.
proc close(db: DbConn) {...}{.tags: [DbEffect], raises: [].}
closes the database connection.
proc open(connection, user, password, database: string): DbConn {...}{.tags: [DbEffect],
    raises: [DbError].}

opens a database connection. Raises EDb if the connection could not be established.

Clients can also use Postgres keyword/value connection strings to connect.

Example:

con = open("", "", "", "host=localhost port=5432 dbname=mydb")

See http://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING for more information.

proc setEncoding(connection: DbConn; encoding: string): bool {...}{.tags: [DbEffect],
    raises: [].}
sets the encoding of a database connection, returns true for success, false for failure.

Iterators

iterator rows(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]): Row {...}{.
    tags: [ReadDbEffect], raises: [DbError, ValueError].}
Executes the query and iterates over the result dataset.
iterator fastRows(db: DbConn; query: SqlQuery; args: varargs[DbValue, dbValue]): Row {...}{.
    tags: [ReadDbEffect], deprecated: "use rows() instead.",
    raises: [DbError, ValueError].}
Deprecated: use rows() instead.

Templates

template `?`(v: typed): DbValue
Shortcut for dbValue.