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

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)

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