Norm is an object-driven, framework-agnostic ORM for Nim that supports SQLite and PostgreSQL.
Install Norm with Nimble:
$ nimble install -y norm
Add Norm to your .nimble file:
requires "norm"
Before going further, install inim with nimble:
$ nimble install -y inim
Also, make sure you have SQLite installed. On most Linux distributions, it should be preinstalled. To install SQLite in macOS, use brew. On Windows, use scoop.
Then, start a new inim session:
$ inim -d:normDebug
A model is an abstraction for a unit of your app's business logic. For example, in an online shop, the models might be Product, Customer, and Discount. Sometimes, models are created for entities that are not visible for the end user, but that are necessary from the architecture point of view: User, CartItem, or Permission.
Models can relate to each each with one-to-one, one-to-many, many-to-many relations. For example, a CartItem can have many Discounts, whereas as a single Discount can be applied to many Products.
Models can also inherit from each other. For example, Customer may inherit from User.
In Norm, Models are ref objects inherited from Model
root object:
import norm/model
type
User = ref object of Model
email: string
From a model definition, Norm deduces SQL queries to create tables and insert, select, update, and delete rows. Norm converts Nim objects to rows, their fields to columns, and their types to SQL types and vice versa.
For example, for a model definition like the one above, Norm generates the following table schema:
CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
Inherited models are just inherited objects:
type
Customer = ref object of User
name: string
To create relations between models, define fields subtyped from Model
:
type
User = ref object of Model
email: string
Customer = ref object of Model
name: string
user: User
To add a UNIQUE
constraint to a field, use {.unique.}
pragma.
UNIQUE
constraint ensures all values in a column or a group of columns are distinct from one another.
type
User = ref object of Model
email: string
name {.unique.}: string
Norm will generate the following table schema:
CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, name TEXT NOT NULL UNIQUE, id INTEGER NOT NULL PRIMARY KEY)
Let's create some tables and examine the queries generated by Norm.
In the inim session, enter this code:
nim> import logging; addHandler newConsoleLogger(fmtStr = "")
nim> import options
nim> import norm/[model, sqlite]
logging
allows us to see the generated queries, options
is necessary to support Option
fields, norm/model
provides Model
type to inherit your models from, and norm/sqlite
is the SQLite backend, which implements the actual SQL generation and conversion between Nim objects and SQL rows.
Then, define the types:
nim> type
.... User = ref object of Model
.... email: string
.... Customer = ref object of Model
.... name: Option[string]
.... user: User
These are your models. It's a good habit to define init procs for your types, so let's do so:
nim> func newUser(email = ""): User =
.... User(email: email)
nim> func newCustomer(name = none string, user = newUser()): Customer =
.... Customer(name: name, user: user)
Now, we are ready to open a connection to the database:
nim> let dbConn = open(":memory:", "", "", "")
And here is the actual table creation:
nim> dbConn.createTables(newCustomer())
After running this last line, you'll see the generated queries in stdout (formatting added to improve readability):
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) )
createTables
proc takes a model instance and generates a table schema for it. For each of the instance's fields, a column is generated. If a field is itself a Model
, a foreign key is added. Option
fields are nullable, non-Option
ones are NOT NULL
.
Note that a single createTables
call generated two table schemas. That's because model Customer
refers to User
, and therefore its table can't be created without the table for User
existing beforehand. Norm makes sure all dependency tables are created before creating the one that createTables
was actually called with. That's actually why the proc is called createTables
and not createTable
.
Make sure to instantiate models withModel
fields so that these fields are notnil
. Otherwise, Norm won't be able to create a table schema for them.
To keep the code more explicit, feel free to call both dbConn.createTables(newUser())
and dbConn.createTables(newCustomer())
. The worst thing to happen is the same query being called twice, but since they both have a IF NOT EXISTS
constraint, the table will be created only once.
Note that
id
column is created despite not being present inUser
definition. That's because it's a special read-only field maintained automatically by Norm. It represents row id in the database.Do not define id field or manually update its value.
To insert rows, use insert
procs. There is a variant that takes a single model instance or a sequence of them.
Instances passed to insert
must be mutable for Norm to be able to update their id
fields.
In your inim session, run:
nim> var
.... userFoo = newUser("[email protected]")
.... userBar = newUser("[email protected]")
.... alice = newCustomer(some "Alice", userFoo)
.... bob = newCustomer(some "Bob", userFoo)
.... sam = newCustomer(some "Sam", userBar)
.... aliceAndBob = [alice, bob]
Those are the objects we'll insert as rows in the database:
nim> import std/with
nim> with dbConn:
.... insert aliceAndBob
.... insert userBar
.... insert sam
Let's examine the queries:
INSERT INTO "User" (email) VALUES(?) <- @['[email protected]'] INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Alice', 1] INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Bob', 1] INSERT INTO "User" (email) VALUES(?) <- @['[email protected]'] INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Sam', 2]
When Norm attempts to insert alice
, it detects that userFoo
that it referenced in it has not been inserted yet, so there's no id
to store as foreign key. So, Norm inserts userFoo
automatically and then uses its new id
(in this case, 1) as the foreign key value.
With bob
, there's no need to do that since userFoo
is already in the database.
You can insert dependency models explicitly to make the code more verbose, as seen with userBar
and sam
.
To select a rows with Norm, you instantiate a model that serves as a container for the selected data and call select
.
One curious thing about select
is that its result depends not only on the condition you pass but also on the container. If the container has Model
fields that are not None
, Norm will select the related rows in a single JOIN
query giving you a fully populated model object. However, if the container has a none Model
field, it is just ignored.
In other words, Norm will automatically handle the "n+1" problem.
Let's see how that works:
nim> var customerBar = newCustomer()
nim> dbConn.select(customerBar, "User.email = ?", "[email protected]")
This is the SQL query generated by this select
call:
SELECT "Customer".name, "User".email, "User".id, "Customer".id FROM "Customer" JOIN "User" ON "Customer".user = "User".id WHERE User.email = ? <- ['[email protected]']
Let's examine how Norm populated customerBar
:
nim> echo customerBar[]
(name: Some("Sam"), user: ..., id: 3)
nim> echo customerBar.user[]
(email: "[email protected]", id: 2)
If you pass a sequence to select
, you'll get many rows:
nim> var customersFoo = @[newCustomer()]
nim> dbConn.select(customersFoo, "User.email = ?", "[email protected]")
The generated query is similar to the previous one, but the result is populated objects, not one:
nim> for customer in customersFoo:
.... echo customer[]
.... echo customer.user[]
....
(name: Some("Alice"), user: ..., id: 1)
(email: "[email protected]", id: 1)
(name: Some("Bob"), user: ..., id: 2)
(email: "[email protected]", id: 1)
To update a row, you just update the object and call update
on it:
nim> customerBar.name = some "Saaam"
nim> dbConn.update(customerBar)
Since customer references a user, to update a customer, we also need to update its user. Norm handles that automatically by generating two queries:
UPDATE "User" SET email = ? WHERE id = 2 <- @['[email protected]'] UPDATE "Customer" SET name = ?, user = ? WHERE id = 3 <- @['Saaam', 2]
Updating rows in bulk is also possible:
nim> for customer in customersFoo:
.... customer.name = some (get(customer.name) & get(customer.name))
....
nim> dbConn.update(customersFoo)
For each object in customersFoo
, a pair of queries are generated:
UPDATE "User" SET email = ? WHERE id = 1 <- @['[email protected]'] UPDATE "Customer" SET name = ?, user = ? WHERE id = 1 <- @['AliceAlice', 1] UPDATE "User" SET email = ? WHERE id = 1 <- @['[email protected]'] UPDATE "Customer" SET name = ?, user = ? WHERE id = 2 <- @['BobBob', 1]
To delete a row, call delete
on an object:
nim> dbConn.delete(sam)
That gives you, quite expectedly:
DELETE FROM "Customer" WHERE id = 3
After deletion, the object becomes nil
:
nim> echo sam.isNil
true
To avoid creating intermediate containers here and there, use Nim's dup
macro to create mutable objects on the fly.
For example, here's how you insert ten rows without having to create ten stale objects
nim> for i in 1..10:
.... discard newUser($i & "@example.com").dup:
.... dbConn.insert
dup
lets you call multiple procs, which gives a pleasant interface for row filter and bulk manipulation:
nim> discard @[newUser()].dup:
.... dbConn.select("email LIKE ?", "[email protected]")
.... dbConn.delete
To run queries in a transaction, wrap the code in a transaction
block:
nim> dbConn.transaction:
.... for i in 11..13:
.... discard newUser($i & "@example.com").dup:
.... dbConn.insert
This produces the following SQL:
BEGIN INSERT INTO "User" (email) VALUES(?) <- @['[email protected]'] INSERT INTO "User" (email) VALUES(?) <- @['[email protected]'] INSERT INTO "User" (email) VALUES(?) <- @['[email protected]'] COMMIT
If something goes wrong inside a transaction block, i.e. an exception is raised, the transaction is rollbacked.
To rollback a transaction manually, call rollback
proc:
nim> dbConn.transaction:
.... for i in 14..16:
.... discard newUser($i & "@example.com").dup:
.... dbConn.insert
....
.... if i == 15:
.... rollback()
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:
nim> import os
nim> putEnv("DB_HOST", ":memory:")
nim> let db = getDb()
nim> var customerBar = newCustomer()
nim> db.select(customerBar, "User.email = ?", "[email protected]")
withDb
template is even handier as it lets you run code without explicitly creating or closing a DB connection:
nim> withDb:
.... var customerBar = newCustomer()
.... db.select(customerBar, "User.email = ?", "[email protected]")
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:
type
Product = ref object of Model
name: string
price: float
Consumer = ref object of Model
email: string
productId {.fk: Product.}: int
proc newProduct(): Product =
Product(name: "", price: 0.0)
proc newConsumer(email: string = "", productId: int = 0): Consumer =
Consumer(email: email, productId: productId)
When using fk
pragma, foreign key must be handled manually, so createTables
needs to be called for both ``Model``s:
let db = open("", "", "", "")
db.createTables(newProduct())
db.createTables(newConsumer())
Norm will generate the following table schema:
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("[email protected]", cheese.id)
db.insert(bob)
On insert
, Norm will generate the following queries :
DEBUG INSERT INTO "Product" (name, price) VALUES(?, ?) <- @['Cheese', 13.3] DEBUG INSERT INTO "Consumer" (email, productId) VALUES(?, ?) <- @['[email protected]', 1]
If an invalid ID is passed, Norm will raise a DbError
exception:
let badProductId = 133
var bob = newConsumer("Paul", badProductId)
db.insert(bob)
Output:
Error: unhandled exception: FOREIGN KEY constraint failed [DbError]
select
queries will only return the id
referenced and not the associated fields:
var consumer = newConsumer()
db.select(consumer, "name = $1", "Bob")
doAssert(consumer.name == "Bob")
var product = newProduct()
db.select(product, "id = $1", consumer.productId)
doAssert(product.name == "Cheese")
doAssert(product.price == 13.30)
Norm will generate the following query:
DEBUG SELECT "Consumer".name, "Consumer".productId, "Consumer".id FROM "Consumer" WHERE name = $1 <- ['Bob'] DEBUG SELECT "Product".name, "Product".price, "Product".id FROM "Product" WHERE id = $1 <- [1]
To enable the logging of SQL queries, define normDebug
either by compiling with -d:normDebug
, or by adding switch("define", "normDebug")
to config.nims
Once normDebug
is defined, simply add a logger on debug level (see https://nim-lang.org/docs/logging.html for more info):
import logging
var consoleLog = newConsoleLogger()
addHandler(consoleLog)
Any contributions are welcome: pull requests, code reviews, documentation improvements, bug reports, and feature requests.
See the issues on GitHub.
Run the tests before and after you change the code.
The recommended way to run the tests is with Docker Compose:
$ docker-compose run --rm tests # run all test suites $ docker-compose run --rm test tests/tmodel.nim # run a single test suite
Use camelCase instead of snake_case.
New procs must have a documentation comment. If you modify an existing proc, update the comment.
Apart from the code that implements a feature or fixes a bug, PRs are required to ship necessary tests and a changelog updates.
Norm would not be where it is today without the efforts of these fine folks: https://github.com/moigagoo/norm/graphs/contributors.