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:

import norm/[model, sqlite, pragmas]


type
  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.createTables(newProduct())
db.createTables(newConsumer())

echo()

CREATE TABLE IF NOT EXISTS "Product"(name TEXT NOT NULL, price FLOAT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "Consumer"(email TEXT NOT NULL, productId INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY (productId) REFERENCES "Product"(id))

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.30)
db.insert(cheese)

var bob = newConsumer("bob@mail.org", cheese.id)
db.insert(bob)

echo()

INSERT INTO "Product" (name, price) VALUES(?, ?) <- @['Cheese', 13.3]
INSERT INTO "Consumer" (email, productId) VALUES(?, ?) <- @['bob@mail.org', 1]

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

try:
  let badProductId = 133
  var paul = newConsumer("paul@mail.org", badProductId)
  db.insert(paul)

except DbError:
  echo getCurrentExceptionMsg()

INSERT INTO "Consumer" (email, productId) VALUES(?, ?) <- @['paul@mail.org', 133]

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

var consumer = newConsumer()
db.select(consumer, "email = $1", "bob@mail.org")
doAssert(consumer.email == "bob@mail.org")

var product = newProduct()
db.select(product, "id = $1", consumer.productId)
doAssert(product.name == "Cheese")
doAssert(product.price == 13.30)

echo()

SELECT "Consumer".email, "Consumer".productId, "Consumer".id FROM "Consumer"  WHERE email = $1 LIMIT 1 <- ['bob@mail.org']
SELECT "Product".name, "Product".price, "Product".id FROM "Product"  WHERE id = $1 LIMIT 1 <- [1]

Please note that Sqlite enforces foreign key constraints only when you use getDb() or a Norm connection pool. Sqlite does not enable checking for foreign key constraints by default (See chapter 2 here), but Norm does so automatically for every connection that Norm creates.

To enable foreign key constraints on a connection you created yourself with open, execute the SQL command PRAGMA foreign_keys=on; with it.