src/norm/sqlite

    Dark Mode
Search:
Group by:

Types

ConflictPolicy = enum
  cpRaise, cpIgnore, cpReplace
NotFoundError = object of KeyError
RollbackError = object of CatchableError
  

Raised when transaction is manually rollbacked.

Do not raise manually, use rollback proc.

Consts

dbHostEnv = "DB_HOST"

Procs

proc count(dbConn; T: typedesc[Model]; col = "*"; dist = false; cond = "1";
           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 createTables[T: Model](dbConn; obj: T)
Create tables for Model and its Model fields.
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 dropDb() {....raises: [OSError], tags: [WriteDirEffect, ReadEnvEffect].}
Remove the DB file defined in environment variable.
proc exists(dbConn; T: typedesc[Model]; cond = "1";
            params: varargs[DbValue, dbValue]): bool
Check if a row exists in the table.
proc getDb(): DbConn {....raises: [DbError], tags: [DbEffect, ReadEnvEffect].}
Create a DbConn from DB_HOST environment variable.
proc insert[T: Model](dbConn; obj: var T; force = false;
                      conflictPolicy = cpRaise)

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.

conflictPolicy determines how the proc reacts to insertion conflicts. cpRaise means raise a DbError, cpIgnore means ignore the conflict and do not insert the conflicting row, cpReplace means overwrite the older row with the newer one.

proc insert[T: Model](dbConn; objs: var openArray[T]; force = false;
                      conflictPolicy = cpRaise)
Insert rows for each Model instance in open array.
proc rollback() {....raises: RollbackError, tags: [].}
Rollback transaction by raising RollbackError.
proc select[T: Model](dbConn; obj: var T; cond: string;
                      params: varargs[DbValue, dbValue]) {.
    ...raises: {NotFoundError, ValueError, DbError, LoggingError}.}

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]) {.
    ...raises: {ValueError, DbError, LoggingError}.}

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 selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2])
A convenience proc. Fetches the many-to-many relationship for the entry queryStartEntry and returns a seq of all entries connected to queryStartEntry in queryEndEntries. Requires to also be passed the model connecting the many-to-many relationship via joinModelEntriesin order to fetch the relationship. The fields on joinModelEntries to use for these queries are inferred. Will only compile if the joinModel has exactly one field pointing to the table of queryStartEntry as well as exactly one field pointing to the table of queryEndEntries. Specify the parameters fkColumnFromJoinToManyStart and fkColumnFromJoinToManyEnd if that is not the case.
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
Fetches the many-to-many relationship for the entry queryStartEntry and returns a seq of all entries connected to queryStartEntry in queryEndEntries. Requires to also be passed the model connecting the many-to-many relationship via joinModelEntriesin order to fetch the relationship. Also requires the field on the joinModel that points to the table of queryStartEntry via the parameter fkColumnFromJoinToManyStart. Also requires the field field on the joinModel that points to the table of queryEndEntries via the parameter fkColumnFromJoinToManyEnd. Will not compile if the specified fields on the joinModel do not properly point to the tables of queryStartEntry and queryEndEntries.
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntry: O;
    relatedEntries: var seq[M])
A convenience proc. Fetches all entries of a "many" side from a one-to-many relationship between the model of oneEntry and the model of relatedEntries. The field used to fetch the relatedEntries is automatically inferred as long as the relatedEntries model has only one field pointing to the model of oneEntry. Will not compile if relatedEntries has multiple fields that point to the model of oneEntry. Specify the foreignKeyFieldName parameter in such a case.
proc selectOneToMany[O: Model; M: Model](dbConn; oneEntry: O;
    relatedEntries: var seq[M]; foreignKeyFieldName: static string)
Fetches all entries of a "many" side from a one-to-many relationship between the model of oneEntry and the model of relatedEntries. It is ensured at compile time that the field specified here is a valid foreign key field on oneEntry pointing to the table of the relatedEntries-model.
proc sum(dbConn; T: typedesc[Model]; col: string; dist = false; cond = "1";
         params: varargs[DbValue, dbValue]): float

Sum column values matching condition without fetching them.

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

Templates

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.

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.