norm/postgres

Types

RollbackError = object of CatchableError
  

Raised when transaction is manually rollbacked.

Do not raise manually, use rollback proc.

NotFoundError = object of KeyError

Consts

dbHostEnv = "DB_HOST"
dbUserEnv = "DB_USER"
dbPassEnv = "DB_PASS"
dbNameEnv = "DB_NAME"

Procs

proc getDb(): DbConn {...}{.raises: [DbError], tags: [DbEffect, ReadEnvEffect].}
Create a DbConn from DB_HOST, DB_USER, DB_PASS, and DB_NAME environment variables.
proc dropDb() {...}{.raises: [DbError, ValueError],
                tags: [DbEffect, ReadEnvEffect, ReadDbEffect, WriteDbEffect].}
Drop the database defined in environment variables.
proc createTables[T: Model](dbConn; obj: T)
Create tables for Model and its Model fields.
proc insert[T: Model](dbConn; obj: var T; force = false)

Insert rows for Model instance and its Model fields, updating their id fields.

By default, if the inserted object's id is not 0, the object is considered already inserted and is not inserted again. You can force new insertion with force = true.

proc insert[T: Model](dbConn; objs: var openArray[T]; force = false)
Insert rows for each Model instance in open array.
proc select[T: Model](dbConn; obj: var T; cond: string;
                      params: varargs[DbValue, dbValue])

Populate a Model instance and its Model fields from DB.

cond is condition for WHERE clause but with extra features:

  • use ? placeholders and put the actual values in params
  • use table, col, and fCol procs instead of hardcoded table and column names
proc select[T: Model](dbConn; objs: var seq[T]; cond: string;
                      params: varargs[DbValue, dbValue])

Populate a sequence of Model instances from DB.

objs must have at least one item.

proc selectAll[T: Model](dbConn; objs: var seq[T])

Populate a sequence of Model instances from DB, fetching all rows in the matching table.

objs must have at least one item.

Warning: this is a dangerous operation because you don't control how many rows will be fetched.

proc count(dbConn; T: typedesc[Model]; col = "*"; dist = false; cond = "TRUE";
           params: varargs[DbValue, dbValue]): int64

Count rows matching condition without fetching them.

To count rows with non-NULL values in a particular column, pass the column name in col param.

To count only unique column values, use dist = true (stands for “distinct.”)

proc update[T: Model](dbConn; obj: var T)
Update rows for Model instance and its Model fields.
proc update[T: Model](dbConn; objs: var openArray[T])
Update rows for each Model instance in open array.
proc delete[T: Model](dbConn; obj: var T)
Delete rows for Model instance and its Model fields.
proc delete[T: Model](dbConn; objs: var openArray[T])
Delete rows for each Model instance in open array.
proc rollback() {...}{.raises: RollbackError, tags: [].}
Rollback transaction by raising RollbackError.

Templates

template withDb(body: untyped): untyped

Wrapper for DB operations.

Creates a DbConn with getDb as db variable, runs your code in a try block, and closes db afterward.

template transaction(dbConn; body: untyped): untyped

Wrap code in DB transaction.

If an exception is raised, the transaction is rollbacked.

To rollback manually, call rollback.