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.

When inserting Norm Model, it is possible to force the id to a given value by setting the id attribute of the Model. In order for the insertion to proceed, it is necessary to specify force=true when inserting:

var userBaz = newUser("baz@baz.baz")
userBaz.id = 156
with dbConn:
  insert(userBaz, force = true)
echo "userBaz.id == 156 ?", (userBaz.id == 156)

echo()

INSERT INTO "User" (email, id) VALUES(?, ?) <- @['baz@baz.baz', 156]
userBaz.id == 156 ?true

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 = ? LIMIT 1 <- ['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)

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 "User" SET email = ? WHERE id = 1 <- @['foo@foo.foo']
UPDATE "Customer" SET
user = CASE id
WHEN 1 THEN ?
WHEN 2 THEN ?
END,
name = CASE id
WHEN 1 THEN ?
WHEN 2 THEN ?
END
WHERE id IN (1, 2) <- @[1, 1, 'MegaAlice', 'MegaBob']

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

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 <- []
2

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 <- []
1

You can also count rows matching condition:

echo dbConn.count(Customer, "*", dist = false, "name LIKE ?", "alice")

SELECT COUNT( *) FROM "Customer" WHERE name LIKE ? <- ['alice']
0

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']
false