Manual Foreign Key Handling

Norm handles foreign keys automatically if you have a field of type Model. However, it has a downside: to fill up an object from the DB, Norm always fetches all related objects along with the original one, potentially generating a heavy JOIN query.

To work around that limitation, you can declare and handle foreign keys manually, with fk pragma:

  norm / [model, sqlite, pragmas]

  Product = ref object of Model
    name: string
    price: float

  Consumer = ref object of Model
    email: string
    productId {.fk: Product.}: int64

proc newProduct(): Product =
  Product(name: "", price: 0.0)

proc newConsumer(email = ""; productId = 0'i64): Consumer =
  Consumer(email: email, productId: productId)

When using fk pragma, foreign key must be handled manually, so createTables needs to be called for both Models:

let db = open(":memory:", "", "", "")
db.exec sql"PRAGMA foreign_keys = ON"

INSERT statements can now be done using only id. This allows for more flexibility at the cost of more manual queries:

var cheese = Product(name: "Cheese", price: 13.3)
var bob = newConsumer("",

If an invalid ID is passed, Norm will raise a DbError exception:

  let badProductId = 133
  var paul = newConsumer("", badProductId)
except DbError:
  echo getCurrentExceptionMsg()
FOREIGN KEY constraint failed

select queries will only return the id referenced and not the associated fields:

var consumer = newConsumer(), "email = $1", "")
doAssert( == "")
var product = newProduct(), "id = $1", consumer.productId)
doAssert( == "Cheese")
doAssert(product.price == 13.3)