Tables

Create Tables

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

In the inim session, enter this code:

import std/[options, logging]
import norm/[model, sqlite]

addHandler newConsoleLogger(fmtStr = "")

logging allows you 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:

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:

func newUser*(email = ""): User =
  User(email: email)

func newCustomer*(name = none string, user = newUser()): Customer =
  Customer(name: name, user: user)

Now, we are ready to open a connection to the database:

let dbConn* = open(":memory:", "", "", "")

And here is the actual table creation:

dbConn.createTables(newCustomer())
echo()

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 why the proc is called createTables and not createTable.

Caveats

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.

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.