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

Imports

wrappers/libpq

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