Custom Datatypes

By default, Norm can deal with the following Nim types:

  • bool
  • int/int8/16/32/64
  • uint/uint8/16/32/64
  • string
  • DbBlob
  • DateTime
  • Model

It does so by interacting with the database through a package called lowdb. With lowdb, Norm converts these Nim types into the DbValue type which it then can convert to and from the various database-types.

More specifically, Norm does this by specifying 3 procs. These procs specify which database-type to convert DbValue to, how to convert the Nim type to DbValue and how to convert DbValue to the Nim type.

If you want to have Norm Models that contain your own custom types, you need to define these 3 procs for your Nim type:

  • dbType(T: typedesc[YourType]) -> string maps your Nim type to a database type (e.g. func dbType*(T: typedesc[string]): string = "TEXT" for SQLite)
  • dbValue(YourType) -> DbValue converts an instance of YourType to a DbValue instance.
  • to(DbValue, T: typedesc[YourType]) -> T converts a DbValue instance to an instance of YourType.

Say for example we wanted to have an enum field on a model, that is stored as a string in our database:

type CreatureFamily = enum
  BEAST = 1
  ANGEL = 2
  DEMON = 3
  HUMANOID = 4

type Creature* = ref object of Model
    name*: string
    family*: CreatureFamily

func dbType*(T: typedesc[CreatureFamily]): string = "TEXT"
func dbValue*(val: CreatureFamily): DbValue = dbValue($val)
proc to*(dbVal: DbValue, T: typedesc[CreatureFamily]): T = parseEnum[CreatureFamily](dbVal.s)

putEnv("DB_HOST", ":memory:")
let db = getDb()
var human = Creature(name: "Karl", family: CreatureFamily.HUMANOID)

db.createTables(human)
db.insert(human)

let rows = db.getAllRows(sql "SELECT name, family, id FROM creature")
echo $rows # @[@['Karl', 'HUMANOID', 1]]

var human2 = Creature()
db.select(human2, "id = ?", human.id)

assert human2.family == CreatureFamily.HUMANOID
CREATE TABLE IF NOT EXISTS "Creature"(name TEXT NOT NULL, family TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
INSERT INTO "Creature" (name, family) VALUES(?, ?) <- @['Karl', 'HUMANOID']
@[@['Karl', 'HUMANOID', 1]]
SELECT "Creature".name, "Creature".family, "Creature".id FROM "Creature"  WHERE id = ? LIMIT 1 <- [1]

Changing the above to store the enums as integers in your database is similarly easy. All you'd need to do is change the 3 procs so that they store the enum in a different way.

type CreatureFamily2 = distinct CreatureFamily

func dbType*(T: typedesc[CreatureFamily2]): string = "INTEGER"
func dbValue*(val: CreatureFamily2): DbValue = dbValue(val.int)
proc to*(dbVal: DbValue, T: typedesc[CreatureFamily2]): CreatureFamily2 = dbVal.i.CreatureFamily2