Gatabase Sugar

Syntax Sugar for Gatabase using template.

include or import after importing db_sqlite or db_postgres to use it on your code.

import db_sqlite
include gatabase/sugar

import db_postgres
include gatabase/sugar

All Gatabase sugar is always optional. The templates are very efficient, no stdlib imports, no object heap alloc, no string formatting, just primitives, no more than 1 variable used.

Templates

template createTable(name: static string; code: untyped): SqlQuery

Create a new database table name with fields from code, returns 1 SqlQuery. Works with Postgres and Sqlite. SqlQuery is pretty-printed when not built for release.

import db_sqlite
include gatabase/sugar
let myTable = createTable "kitten": [
  "age"    := 1,
  "sex"    := 'f',
  "name"   := "unnamed",
  "rank"   := 3.14,
  "weight" := int,
  "color"  := char,
  "owner"  := string,
  "food"   := float,
]

Generates the SQL Query:

CREATE TABLE IF NOT EXISTS kitten(
  id      INTEGER      PRIMARY KEY,
  age     INTEGER      NOT NULL      DEFAULT 1,
  sex     VARCHAR(1)   NOT NULL      DEFAULT 'f',
  name    TEXT         NOT NULL      DEFAULT 'unnamed',
  rank    REAL         NOT NULL      DEFAULT 3.14,
  weight  INTEGER,
  color   VARCHAR(1),
  owner   TEXT,
  food    REAL,
);

More examples:

https://github.com/juancarlospaco/nim-gatabase/blob/master/examples/database_fields_example.nim#L1

template dropTable(db; name: string): bool

Alias for tryExec(db, sql("DROP TABLE IF EXISTS ?"), name). Requires a db of DbConn type. Works with Postgres and Sqlite. Deleted tables can not be restored, be careful.

template withSqlite(path: static[string]; initTableSql: static[string]; closeOnQuit: static[bool]; closeOnCtrlC: static[bool]; code: untyped): untyped

Open, run initTableSql and Auto-Close a SQLite database.

path path to SQLite database file.
initTableSql SQL query string to initialize the database, create table if not exists alike.
closeOnQuit if true then addQuitProc(db.close()) is set.
closeOnCtrlC if true then setControlCHook(db.close()) is set.

import db_sqlite
include gatabase/sugar
const exampleTable = """
  create table if not exists person(
    id      integer primary key,
    name    text,
    active  bool,
    rank    float
); """

withSqlite(":memory:", exampleTable, false):  ## This is just an example.
  db.exec(sql"insert into person(name, active, rank) values('pepe', true, 42.0)")

template withPostgres(host, user, password, dbname: string; initTableSql: static[string]; closeOnQuit: static[bool]; closeOnCtrlC: static[bool]; code: untyped): untyped

Open, run initTableSql and Auto-Close a Postgres database. See withSqlite for an example.

host host of Postgres Server, string type, must not be empty string.
user user of Postgres Server, string type, must not be empty string.
password password of Postgres Server, string type, must not be empty string.
dbname database name of Postgres Server, string type, must not be empty string.
initTableSql SQL query string to initialize the database, create table if not exists alike.
closeOnQuit if true then addQuitProc(db.close()) is set.
closeOnCtrlC if true then setControlCHook(db.close()) is set.

template `.`(indx: int; data: Row): int

9.row convenience alias for strutils.parseInt(row[9]) (row is Row type).

template `.`(indx: char; data: Row): char

'9'.row convenience alias for char(row[strutils.parseInt($indx)][0]) (row is Row type).

template `.`(indx: uint; data: Row): uint

9'u.row convenience alias for uint(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: cint; data: Row): cint

cint(9).row convenience alias for cint(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: int8; data: Row): int8

9'i8.row convenience alias for int8(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: byte; data: Row): byte

byte(9).row convenience alias for byte(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: int16; data: Row): int16

9'i16.row convenience alias for int16(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: int32; data: Row): int32

9'i32.row convenience alias for int32(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: int64; data: Row): int64

9'i64.row convenience alias for int64(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: uint8; data: Row): uint8

9'u8.row convenience alias for uint8(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: uint16; data: Row): uint16

9'u16.row convenience alias for uint16(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: uint32; data: Row): uint32

9'u32.row convenience alias for uint32(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: uint64; data: Row): uint64

9'u64.row convenience alias for uint64(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: float; data: Row): float

9.0.row convenience alias for strutils.parseFloat(row[int(9)]) (row is Row type).

template `.`(indx: Natural; data: Row): Natural

Natural(9).row convenience alias for Natural(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: cstring; data: Row): cstring

cstring("9").row convenience alias for cstring(row[9]) (row is Row type).

template `.`(indx: Positive; data: Row): Positive

Positive(9).row convenience alias for Positive(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: BiggestInt; data: Row): BiggestInt

BiggestInt(9).row convenience alias for BiggestInt(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: BiggestUInt; data: Row): BiggestUInt

BiggestUInt(9).row convenience alias for BiggestUInt(strutils.parseInt(row[9])) (row is Row type).

template `.`(indx: float32; data: Row): float32

9.0'f32.row convenience alias for float32(strutils.parseFloat(row[int(9)])) (row is Row type).