Rows

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:

var
  userFoo = newUser("foo@foo.foo")
  userBar = newUser("bar@bar.bar")
  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:

import std/with

with dbConn:
  insert aliceAndBob
  insert userBar
  insert sam

echo()
INSERT INTO "User" (email) VALUES(?) <- @['foo@foo.foo']
INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Alice', 1]
Object ID is not 0, skipping insertion. Type: User, ID: 1
INSERT INTO "Customer" (name, user) VALUES(?, ?) <- @['Bob', 1]
INSERT INTO "User" (email) VALUES(?) <- @['bar@bar.bar']
Object ID is not 0, skipping insertion. Type: User, ID: 2
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.

Select Row

Select in general

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:

var customerBar = newCustomer()
dbConn.select(customerBar, "User.email = ?", "bar@bar.bar")

echo()
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 = ? <- ['bar@bar.bar']

Let's examine how Norm populated customerBar:

echo customerBar[]
(name: some("Sam"), user: ..., id: 3)
echo customerBar.user[]
(email: "bar@bar.bar", id: 2)

If you pass a sequence to select, you'll get many rows:

var customersFoo = @[newCustomer()]
dbConn.select(customersFoo, "User.email = ?", "foo@foo.foo")

echo()
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 = ? <- ['foo@foo.foo']

The generated query is similar to the previous one, but the result is populated objects, not one:

for customer in customersFoo:
  echo customer[]
  echo customer.user[]
(name: some("Alice"), user: ..., id: 1)
(email: "foo@foo.foo", id: 1)
(name: some("Bob"), user: ..., id: 2)
(email: "foo@foo.foo", id: 1)

If you query relationships that are nested, such as when customers can have pets and you want to query all pets of all customers of users with a specific email address, you will need to concatenate the foreign-key fields, separeted by a _ in your query.

import norm/model

type Pet* = ref object of Model
  name*: string
  owner*: Customer

func newPet*(name = "", owner = newCustomer()): Pet =
  Pet(name: name, owner: owner)

dbConn.createTables(newPet())

var fluffi: Pet = newPet("Fluffi", bob)
dbConn.insert(fluffi)


var petsFoo = @[newPet()]
dbConn.select(petsFoo, "owner_user.email LIKE ?", "foo%")

for pet in petsFoo:
  echo pet[]
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))
CREATE TABLE IF NOT EXISTS "Pet"(name TEXT NOT NULL, owner INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY(owner) REFERENCES "Customer"(id))
Object ID is not 0, skipping insertion. Type: Customer, ID: 2
INSERT INTO "Pet" (name, owner) VALUES(?, ?) <- @['Fluffi', 2]
SELECT "Pet".name, "Pet".owner, "owner".name, "owner".user, "owner_user".email, "owner_user".id, "owner".id, "Pet".id FROM "Pet" LEFT JOIN "Customer" AS "owner" ON "Pet".owner = "owner".id LEFT JOIN "User" AS "owner_user" ON "owner".user = "owner_user".id WHERE owner_user.email LIKE ? <- ['foo%']
(name: "Fluffi", owner: ..., id: 1)

Selecting Many-To-One/One-To-Many relationships

Imagine you had a Many-To-One relationship between two models, like we have with Customer being the many-model and User being the one-model, where one user can have many customers.

If you have a user and wanted to query all of their customers, you couldn't do so by just making a query for the user, as that model doesn't have a "seq[Customer]" field that norm could resolve.

You could query the users for a given customer separately using the mechanisms of a general select statement.

However, you can also query them separately using a convenience proc selectOneToMany to do all of that work for you.

Just provide the "one"-side of the relationship (user), a seq of the "many-model" (seq[Customer]) to populate as before and the name of the field on the "many-model" ("user" as that's the name of field on Customer pointing to User) that points to the "one-model" (User).

If your "many-model" (Customer) only has a single field pointing to the one model (User) you can even forego providing the field-name, norm will infer it for you!

# With explicitly provided field name
var customersFoo2 = @[newCustomer()]
dbConn.selectOneToMany(userFoo, customersFoo2, "user")

for customer in customersFoo2:
  echo customer[]

# With inferred field name
var customersFoo3 = @[newCustomer()]
dbConn.selectOneToMany(userFoo, customersFoo3)
  
for customer in customersFoo3:
  echo customer[]
SELECT "Customer".name, "Customer".user, "user".email, "user".id, "Customer".id FROM "Customer" LEFT JOIN "User" AS "user" ON "Customer".user = "user".id WHERE "Customer".user = ? <- [1]
(name: some("Alice"), user: ..., id: 1)
(name: some("Bob"), user: ..., id: 2)
SELECT "Customer".name, "Customer".user, "user".email, "user".id, "Customer".id FROM "Customer" LEFT JOIN "User" AS "user" ON "Customer".user = "user".id WHERE "Customer".user = ? <- [1]
(name: some("Alice"), user: ..., id: 1)
(name: some("Bob"), user: ..., id: 2)

An additional benefit of using this selectOneToMany is that with it, norm will validate whether this query is correct at compile time!

In the first approach, if Customer doesn't have a field called "user" or if that field does not have any model-type that points to the "User"-table, nor an fk-pragma to any such type, then the code will throw an error with a helpful message at compile-time.

In the second approach, if Customer doesn't have any field of type "User" or any other model-type that points to the same table as "User", it will also not compile while throwing a helpful error message.

Selecting Many-To-Many relationships

Imagine if you had a Many-To-Many relationship between two models (e.g. Users and Groups) that is recorded on an "join-model" (e.g. UserGroup), where one user can be in many groups and a group can have many users.

If you have a user and want to query all of its groups, you can do so via the general select statement mechanism.

Similarly to selectOneToMany there is a helper proc selectManyToMany here for convenience.

Just provide the side whose model entry you have (e.g. User or Group), a seq of the join-model (e.g. UserGroup), a seq of the entries your trying to query (e.g. seq[Group] or seq[User]), the field name on the join-model pointing to the model entry you have (e.g. "user" or "group") and the field name on the join-model pointing to the model of the entries you're trying to query (e.g. "group" or "user").

As before, if your join-model (e.g. UserGroup) only has a single field pointing to each of the two many models (e.g. User and Group), you can forego the field names and let norm infer them for you.

type
  Group* = ref object of Model
    name*: string
  
  UserGroup* = ref object of Model
    user*: User
    membershipGroup*: Group

func newGroup*(name = ""): Group = Group(name: name)

func newUserGroup*(user = newUser(), group = newGroup()): UserGroup = UserGroup(user: user, membershipGroup: group)

dbConn.createTables(newGroup())
dbConn.createTables(newUser())
dbConn.createTables(newUserGroup())

var
  groupFoo = newGroup("groupFoo")
  groupBar = newGroup("groupBar")

  userFooGroupFooMembership = newUserGroup(userFoo, groupFoo)
  userBarGroupFooMembership = newUserGroup(userBar, groupFoo)
  userFooGroupBarMembership = newUserGroup(userFoo, groupBar)

with dbConn:
  insert groupFoo
  insert groupBar
  insert userFooGroupFooMembership
  insert userBarGroupFooMembership
  insert userFooGroupBarMembership

# With explicitly provided fieldnames
var userFooGroupMemberships: seq[UserGroup] = @[newUserGroup()]
var userFooGroups: seq[Group] = @[newGroup()]
dbConn.selectManyToMany(userFoo, userFooGroupMemberships, userFooGroups, "user", "membershipGroup")

for group in userFooGroups:
  echo group[]

# With inferred field names
var userFooGroupMemberships2: seq[UserGroup] = @[newUserGroup()]
var userFooGroups2: seq[Group] = @[newGroup()]
dbConn.selectManyToMany(userFoo, userFooGroupMemberships2, userFooGroups2)

for group in userFooGroups2:
  echo group[]
CREATE TABLE IF NOT EXISTS "Group"(name TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "User"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "Group"(name TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
CREATE TABLE IF NOT EXISTS "UserGroup"(user INTEGER NOT NULL, membershipGroup INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY, FOREIGN KEY(user) REFERENCES "User"(id), FOREIGN KEY(membershipGroup) REFERENCES "Group"(id))
INSERT INTO "Group" (name) VALUES(?) <- @['groupFoo']
INSERT INTO "Group" (name) VALUES(?) <- @['groupBar']
Object ID is not 0, skipping insertion. Type: User, ID: 1
Object ID is not 0, skipping insertion. Type: Group, ID: 1
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[1, 1]
Object ID is not 0, skipping insertion. Type: User, ID: 2
Object ID is not 0, skipping insertion. Type: Group, ID: 1
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[2, 1]
Object ID is not 0, skipping insertion. Type: User, ID: 1
Object ID is not 0, skipping insertion. Type: Group, ID: 2
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[1, 2]
SELECT "UserGroup".user, "user".email, "user".id, "UserGroup".membershipGroup, "membershipGroup".name, "membershipGroup".id, "UserGroup".id FROM "UserGroup" LEFT JOIN "User" AS "user" ON "UserGroup".user = "user".id LEFT JOIN "Group" AS "membershipGroup" ON "UserGroup".membershipGroup = "membershipGroup".id WHERE "UserGroup".user = ? <- [1]
(name: "groupFoo", id: 1)
(name: "groupBar", id: 2)
SELECT "UserGroup".user, "user".email, "user".id, "UserGroup".membershipGroup, "membershipGroup".name, "membershipGroup".id, "UserGroup".id FROM "UserGroup" LEFT JOIN "User" AS "user" ON "UserGroup".user = "user".id LEFT JOIN "Group" AS "membershipGroup" ON "UserGroup".membershipGroup = "membershipGroup".id WHERE "UserGroup".user = ? <- [1]
(name: "groupFoo", id: 1)
(name: "groupBar", id: 2)

Count Rows

Selecting rows is expensive if many rows are fetched. Knowing the number of rows you have before doing the actual select is useful.

To count the rows without fetching them, use count:

echo dbConn.count(Customer)
SELECT COUNT( *) FROM "Customer" WHERE 1 <- []
3

To count only unique records, use dist = true in conjunction with the column name you want to check for uniqueness:

echo dbConn.count(Customer, "user", dist = true)
SELECT COUNT(DISTINCT user) FROM "Customer" WHERE 1 <- []
2

You can also count rows matching condition:

echo dbConn.count(Customer, "*", dist = false, "name LIKE ?", "alice")
SELECT COUNT( *) FROM "Customer" WHERE name LIKE ? <- ['alice']
1

Sum Column Values

To get a sum of column values, use sum proc:

import norm/model

type Chair = ref object of Model
  legCount: Natural

func newChair(legCount = 0): Chair = Chair(legCount: legCount)

dbConn.createTables(newChair())

var
  threeLeggedChair = newChair(3)
  fourLeggedChair = newChair(4)
  anotherFourLeggedChair = newChair(4)

dbConn.insert(threeLeggedChair)
dbConn.insert(fourLeggedChair)
dbConn.insert(anotherFourLeggedChair)

echo dbConn.sum(Chair, "legCount")
echo dbConn.sum(Chair, "legCount", dist = true)
echo dbConn.sum(Chair, "legCount", dist = false, "legCount > ?", 3)
CREATE TABLE IF NOT EXISTS "Chair"(legCount INTEGER NOT NULL, id INTEGER NOT NULL PRIMARY KEY)
INSERT INTO "Chair" (legCount) VALUES(?) <- @[3]
INSERT INTO "Chair" (legCount) VALUES(?) <- @[4]
INSERT INTO "Chair" (legCount) VALUES(?) <- @[4]
SELECT SUM( legCount) FROM "Chair" WHERE 1 <- []
11.0
SELECT SUM(DISTINCT legCount) FROM "Chair" WHERE 1 <- []
7.0
SELECT SUM( legCount) FROM "Chair" WHERE legCount > ? <- [3]
8.0

Check If Row Exists

If you need to check if a row selected by a given condition exists, use exists proc:

echo dbConn.exists(Customer, "name = ?", "Alice")
SELECT EXISTS(SELECT NULL FROM "Customer" WHERE name = ?) <- ['Alice']
true

Update Rows

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

customerBar.name = some "Saaam"
dbConn.update(customerBar)

echo()
UPDATE "User" SET email = ? WHERE id = 2 <- @['bar@bar.bar']
UPDATE "Customer" SET name = ?, user = ? WHERE id = 3 <- @['Saaam', 2]

Since customer references a user, to update a customer, we also need to update its user. Norm handles that automatically by generating two queries.

Updating rows in bulk is also possible:

for customer in customersFoo:
  customer.name = some ("Mega" & get(customer.name))

dbConn.update(customersFoo)

echo()
UPDATE "User" SET email = ? WHERE id = 1 <- @['foo@foo.foo']
UPDATE "Customer" SET name = ?, user = ? WHERE id = 1 <- @['MegaAlice', 1]
UPDATE "User" SET email = ? WHERE id = 1 <- @['foo@foo.foo']
UPDATE "Customer" SET name = ?, user = ? WHERE id = 2 <- @['MegaBob', 1]

For each object in customersFoo, a pair of queries are generated.

Delete Rows

To delete a row, call delete on an object:

dbConn.delete(sam)

echo()
DELETE FROM "Customer" WHERE id = 3

After deletion, the object becomes nil:

echo sam.isNil
true