Norm: A Nim ORM

.. image:: https://travis-ci.com/moigagoo/norm.svg?branch=develop :alt: Build Status :target: https://travis-ci.com/moigagoo/norm

.. image:: https://raw.githubusercontent.com/yglukhov/nimble-tag/master/nimble.png :alt: Nimble :target: https://nimble.directory/pkg/norm

Norm is an object-driven, framework-agnostic ORM for Nim that supports SQLite and PostgreSQL.

• Repo <https://github.com/moigagoo/norm>__

  • Issues <https://github.com/moigagoo/norm/issues>__
  • Pull requests <https://github.com/moigagoo/norm/pulls>__

• Sample app <https://github.com/moigagoo/shopapp>__

• API index <https://norm.nim.town/theindex.html>__

• Test results <https://norm.nim.town/testresults.html>__

• Changelog <https://github.com/moigagoo/norm/blob/develop/changelog.rst>__

Norm works best with Norman <https://norman.nim.town>__.

Installation

Install Norm with Nimble <https://github.com/nim-lang/nimble>_:

.. code-block::

$ nimble install -y norm

Add Norm to your .nimble file:

.. code-block:: nim

requires "norm"

Tutorial

Before going further, install inim <https://github.com/inim-repl/INim>_ with nimble:

.. code-block::

$ 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 <https://brew.sh/>. On Windows, use scoop <https://scoop.sh/>.

Then, start a new inim session:

.. code-block::

$ inim -d:normDebug

Models

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:

.. code-block:: nim

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:

.. code-block::

CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)

Inherited models are just inherited objects:

.. code-block:: nim

type
  Customer = ref object of User
    name: string

To create relations between models, define fields subtyped from Model:

.. code-block:: nim

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.

.. code-block:: nim

type
  User = ref object of Model
    email: string
    name {.unique.}: string

Norm will generate the following table schema:

.. code-block::

CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, name TEXT NOT NULL UNIQUE, id INTEGER NOT NULL PRIMARY KEY)

Create Tables

Let's create some tables and examine the queries generated by Norm.

In the inim session, enter this code:

.. code-block:: nim

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:

.. code-block:: nim

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:

.. code-block:: nim

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:

.. code-block:: nim

nim> let dbConn = open(":memory:", "", "", "")

And here is the actual table creation:

.. code-block:: nim

nim> dbConn.createTables(newCustomer())

After running this last line, you'll see the generated queries in stdout (formatting added to improve readability):

.. code-block::

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 with ``Model`` fields so that these fields are not ``nil``. 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 in ``User`` 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.**

Insert Rows

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:

.. code-block:: nim

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:

.. code-block:: nim

nim> import std/with
nim> with dbConn:
....   insert aliceAndBob
....   insert userBar
....   insert sam

Let's examine the queries:

.. code-block::

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.

Select Rows

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:

.. code-block:: nim

nim> var customerBar = newCustomer()
nim> dbConn.select(customerBar, "User.email = ?", "[email protected]")

This is the SQL query generated by this select call:

.. code-block::

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:

.. code-block:: nim

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:

.. code-block:: nim

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:

.. code-block:: nim

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)

Update Rows

To update a row, you just update the object and call update on it:

.. code-block:: nim

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:

.. code-block::

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:

.. code-block:: nim

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:

.. code-block::

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]

Delete Rows

To delete a row, call delete on an object:

.. code-block:: nim

nim> dbConn.delete(sam)

That gives you, quite expectedly:

.. code-block::

DELETE FROM "Customer" WHERE id = 3

After deletion, the object becomes nil:

.. code-block:: nim

nim> echo sam.isNil
true

Fancy Syntax

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

.. code-block:: nim

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:

.. code-block:: nim

nim> discard @[newUser()].dup:
....   dbConn.select("email LIKE ?", "[email protected]")
....   dbConn.delete

Transactions

To run queries in a transaction, wrap the code in a transaction block:

.. code-block:: nim

nim> dbConn.transaction:
....   for i in 11..13:
....     discard newUser($i & "@example.com").dup:
....       dbConn.insert

This produces the following SQL:

.. code-block::

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:

.. code-block:: nim

nim> dbConn.transaction:
....   for i in 14..16:
....     discard newUser($i & "@example.com").dup:
....       dbConn.insert
....
....     if i == 15:
....       rollback()

Read Configuration from Environment Variables

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:

.. code-block:: nim

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:

.. code-block:: nim

nim> withDb:
....   var customerBar = newCustomer()
....   db.select(customerBar, "User.email = ?", "[email protected]")

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:

.. code-block:: nim

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 Models:

.. code-block:: nim

let db = open("", "", "", "")

db.createTables(newProduct())
db.createTables(newConsumer())

Norm will generate the following table schema:

.. code-block::

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:

.. code-block:: nim

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 :

.. code-block::

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:

.. code-block:: nim

let badProductId = 133
var bob = newConsumer("Paul", badProductId)
db.insert(bob)

Output:

.. code-block::

Error: unhandled exception: FOREIGN KEY constraint failed [DbError]

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

.. code-block:: nim

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:

.. code-block::

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]

Debugging SQL Queries

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):

.. code-block:: nim

import logging var consoleLog = newConsoleLogger() addHandler(consoleLog)

Contributing

Any contributions are welcome: pull requests, code reviews, documentation improvements, bug reports, and feature requests.

• See the issues on GitHub <http://github.com/moigagoo/norm/issues>__.

• Run the tests before and after you change the code.

  The recommended way to run the tests is with Docker Compose:

  .. code-block::

  $ 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.

❤ Contributors ❤

Norm would not be where it is today without the efforts of these fine folks: https://github.com/moigagoo/norm/graphs/contributors <https://github.com/moigagoo/norm/graphs/contributors>__.