Configuration from Environment

In a real-life project, you want to keep your DB configuration separate from the code. Common pattern is to put it in environment variables, probably in a .env file that's processed during the app startup.

Norm's getDb proc lets you create a DB connection using DB_HOST, DB_USER, DB_PASS, and DB_NAME environment variables:

import std/[os, options]

import norm/sqlite


putEnv("DB_HOST", ":memory:")

let db = getDb()

var
  customerFoo = newCustomer(some "Alice", newUser("foo@foo.foo"))
  customerBar = newCustomer()

db.createTables(customerBar)
db.insert(customerFoo)
db.select(customerBar, "User.email = ?", "foo@foo.foo")

echo customerBar[]

CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "Customer"(name TEXT, user INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY(user) REFERENCES "User"(id))
INSERT INTO "User" (email) VALUES(?) <- @['foo@foo.foo']
INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Alice', 1]
SELECT "Customer".name, "Customer".user, "user".email, "user".id, "Customer".id FROM "Customer" LEFT JOIN "User" AS "user" ON "Customer".user = "user".id WHERE User.email = ? LIMIT 1 <- ['foo@foo.foo']
(name: some("Alice"), user: ..., id: 1)

withDb template is even handier as it lets you run code without explicitly creating or closing a DB connection:

var
  customerSpam = newCustomer(some "Bob", newUser("bar@bar.bar"))
  customerEggs = newCustomer()

withDb:
  db.createTables(customerEggs)
  db.insert(customerSpam)
  db.select(customerEggs, "User.email = ?", "bar@bar.bar")

echo customerBar[]

CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "Customer"(name TEXT, user INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY(user) REFERENCES "User"(id))
INSERT INTO "User" (email) VALUES(?) <- @['bar@bar.bar']
INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Bob', 1]
SELECT "Customer".name, "Customer".user, "user".email, "user".id, "Customer".id FROM "Customer" LEFT JOIN "User" AS "user" ON "Customer".user = "user".id WHERE User.email = ? LIMIT 1 <- ['bar@bar.bar']
(name: some("Alice"), user: ..., id: 1)