Norm

https://moigagoo.github.io/norm/
Admin
Norm is an object-driven, framework-agnostic ORM for Nim that supports SQLite and PostgreSQL...
Tokens:30,252
Snippets:274
Trust Score:8.3
Update:11 months ago
Show doc for...
Tokens:
Raw
### Transaction SQL Output Example

Source: https://moigagoo.github.io/norm/transactions

This SQL output illustrates the operations performed during a successful transaction. It shows the `BEGIN`, `INSERT` statements, and the final `COMMIT`.

```sql
BEGIN
INSERT INTO "User" (email) VALUES(?) <- @['11@example.com']
INSERT INTO "User" (email) VALUES(?) <- @['12@example.com']
INSERT INTO "User" (email) VALUES(?) <- @['13@example.com']
COMMIT
```

--------------------------------

### Get database connection

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Creates a DbConn from DB_HOST environment variable. May raise DbError and requires database read/write permissions.

```Nim
proc getDb(): DbConn {....raises: [DbError], tags: [DbEffect, ReadEnvEffect,
    ReadDbEffect, WriteDbEffect], forbids: [].}
```

--------------------------------

### Get Database Connection (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Establishes and returns a `DbConn` object using database credentials configured in environment variables (DB_HOST, DB_USER, DB_PASS, DB_NAME).

```nim
proc getDb(): DbConn {....raises: [DbError], tags: [DbEffect, ReadEnvEffect],
                       forbids: [].}
```

--------------------------------

### SQL Count Rows Equivalent

Source: https://moigagoo.github.io/norm/tutorial/rows

Provides the SQL equivalents for the Norm `count` procedure examples. Shows how to perform total row counts, distinct counts, and conditional counts using standard SQL syntax.

```sql
SELECT COUNT( *) FROM "Customer" WHERE 1 <- []
```

```sql
SELECT COUNT(DISTINCT user) FROM "Customer" WHERE 1 <- []
```

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

--------------------------------

### Creating DB Connection with getDb in Nim

Source: https://moigagoo.github.io/norm/config

The getDb proc establishes a database connection using environment variables such as DB_HOST, DB_USER, DB_PASS, and DB_NAME. It requires importing std/os, std/options, and norm/sqlite, with putEnv for setting variables. This example initializes an in-memory database, creates tables for User and Customer models, inserts a customer record, and selects it by email; limitations include reliance on predefined env vars and SQLite-specific syntax.

```Nim
import std/[os, options]

import norm/sqlite


putEnv("DB_HOST", ":memory:")

let db = getDb()

var
  customerFoo = newCustomer(some "Alice", newUser("foo@foo.foo"))
  customerBar = newCustomer()

db.createTables(customerBar)
db.insert(customerFoo)
db.select(customerBar, "User.email = ?", "foo@foo.foo")

echo customerBar[]
```

--------------------------------

### Manual Rollback SQL Output Example

Source: https://moigagoo.github.io/norm/transactions

This SQL output shows the operations during a manually rolled-back transaction. It includes the `BEGIN` and `INSERT` statements up to the point of rollback, followed by `ROLLBACK`.

```sql
BEGIN
INSERT INTO "User" (email) VALUES(?) <- @['14@example.com']
INSERT INTO "User" (email) VALUES(?) <- @['15@example.com']
ROLLBACK
Rollback transaction.
```

--------------------------------

### Using withDb Template for DB Operations in Nim

Source: https://moigagoo.github.io/norm/config

The withDb template simplifies database interactions by automatically handling connection creation and closure without explicit db variable management. It depends on the same imports as getDb and uses environment variables for configuration. This example creates tables, inserts a customer, and performs a select query within the template block; it shares limitations with getDb but offers convenience for short-lived operations.

```Nim
var
  customerSpam = newCustomer(some "Bob", newUser("bar@bar.bar"))
  customerEggs = newCustomer()

withDb:
  db.createTables(customerEggs)
  db.insert(customerSpam)
  db.select(customerEggs, "User.email = ?", "bar@bar.bar")

echo customerBar[]
```

--------------------------------

### SQL Sum Column Values Equivalent

Source: https://moigagoo.github.io/norm/tutorial/rows

Presents the SQL equivalents for the Norm `sum` procedure examples. Demonstrates how to calculate total sums, distinct sums, and conditional sums of column values using SQL.

```sql
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 <- []
```

```sql
SELECT SUM(DISTINCT legCount) FROM "Chair" WHERE 1 <- []
```

```sql
SELECT SUM( legCount) FROM "Chair" WHERE legCount > ? <- [3]
```

--------------------------------

### Creating and Using Norm Connection Pool in Nim

Source: https://moigagoo.github.io/norm/pool

This snippet demonstrates creating a connection pool using newPool and using it with the withDb context to create tables. It depends on norm/model, norm/sqlite, and norm/pool modules, and sets environment variables for database configuration. Inputs include pool size and DbConn type; outputs are managed database connections for operations like createTables. Limitation: Requires --mm:orc and environment setup for DB connections.

```nim
import norm/[model, sqlite, pool]

type
  Product = ref object of Model
    name: string
    price: float

proc newProduct(): Product =
  Product(name: "", price: 0.0)

putEnv("DB_HOST", ":memory:")

var connPool = newPool[DbConn](10)

withDb(connPool):
  db.createTables(newProduct())
```

--------------------------------

### Nim: Get Model Columns

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Retrieves a sequence of column names for a Model instance. An optional 'force' parameter can include fields with the 'ro' pragma.

```nim
func cols[T: Model](obj: T; force = false): seq[string]
```

--------------------------------

### Define Model Relations in Norm

Source: https://moigagoo.github.io/norm/models

Shows how to define relationships between models in Norm by using fields that subtype Model. In this example, Customer relates to User through the user field.

```Nim
type
  User* = ref object of Model
    email: string

  Customer* = ref object of Model
    name: string
    user: User
```

--------------------------------

### Run Queries in Transaction Block (Nim)

Source: https://moigagoo.github.io/norm/transactions

Executes a block of code as a single database transaction. If the block completes successfully, the transaction is committed. If an exception occurs, the transaction is automatically rolled back. This example demonstrates inserting new users within a transaction.

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

echo()
```

--------------------------------

### Sort Query Results with ORDER BY in Norm (Nim)

Source: https://moigagoo.github.io/norm/tutorial/rowsAdvanced

Illustrates sorting query results using SQL's ORDER BY clause in descending order. Shows how to sort Customer records by name and verify the correct order using assertions. The example builds on previous data to demonstrate sorted retrieval.

```nim
var sortedCustomersFoo = @[newCustomer()]

dbConn.select(sortedCustomersFoo, "User.email = ? ORDER BY name DESC", "foo@foo.foo")

assert sortedCustomersFoo[0].name.get() == "Bob"
assert sortedCustomersFoo[1].name.get() == "Alice"

echo()
```

```sql
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 = ? ORDER BY name DESC <- ['foo@foo.foo']
```

--------------------------------

### Nim: Get Join Information for Model Fields

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Generates information needed for constructing SQL JOIN statements for a Model instance's fields. It returns the table name, alias, and linked column names.

```nim
func joinGroups[T: Model](obj: T; flds: seq[string] = @[]): seq[tuple[tbl, tAls, lFld, rFld: string]]
```

--------------------------------

### Fetch Many-to-Many Relationship (Single Entry, Auto FK)

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Fetches the many-to-many relationship for a single entry `queryStartEntry`, returning a sequence of related entries from `queryEndEntries`. The join model's foreign key fields are inferred. This convenience procedure assumes the join model has exactly one field pointing to each of the start and end models.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2])
```

--------------------------------

### Fetch Many-to-Many Relationship (Multiple Entries, Auto FK)

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Fetches the many-to-many relationship for all entries in `queryStartEntries` and stores them in `queryEndEntries`. The join model's foreign key fields are inferred. This is a convenience procedure and assumes the join model has exactly one field pointing to each of the start and end models.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]])
```

--------------------------------

### Select Many-to-Many Relationships (PostgreSQL, SQLite)

Source: https://moigagoo.github.io/norm/apidocs/theindex

Fetches records involved in a many-to-many relationship. This procedure supports specifying join tables and foreign key columns for precise querying. It can handle single or multiple starting entries and populate results into a sequence or a table.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntry: M1; joinModelEntries: var seq[J]; queryEndEntries: var seq[M2])
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntry: M1; joinModelEntries: var seq[J]; queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string; fkColumnFromJoinToManyEnd: static string)
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntries: seq[M1]; joinModelEntries: var seq[J]; queryEndEntries: var Table[int64, seq[M2]])
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntries: seq[M1]; joinModelEntries: var seq[J]; queryEndEntries: var Table[int64, seq[M2]]; fkColumnFromJoinToManyStart: static string; fkColumnFromJoinToManyEnd: static string)
```

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntry: M1; joinModelEntries: var seq[J]; queryEndEntries: var seq[M2])
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntry: M1; joinModelEntries: var seq[J]; queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string; fkColumnFromJoinToManyEnd: static string)
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntries: seq[M1]; joinModelEntries: var seq[J]; queryEndEntries: var Table[int64, seq[M2]])
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn; queryStartEntries: seq[M1]; joinModelEntries: var seq[J]; queryEndEntries: var Table[int64, seq[M2]]; fkColumnFromJoinToManyStart: static string; fkColumnFromJoinToManyEnd: static string)
```

--------------------------------

### Define Base Model in Norm

Source: https://moigagoo.github.io/norm/models

Defines a base model in Norm by inheriting from the Model root object. This example shows a simple BaseUser model with an email field. Norm uses this definition to generate corresponding SQL table schemas.

```Nim
import norm/model

type
  BaseUser* = ref object of Model
    email: string
```

--------------------------------

### Handle Invalid Foreign Key IDs with Norm's DbError

Source: https://moigagoo.github.io/norm/fk

Illustrates error handling when attempting to insert a record with an invalid foreign key ID using Norm. The example shows a `try-except` block that catches the `DbError` exception raised by Norm when an invalid `productId` is provided during the insertion of a `Consumer`, and prints the exception message.

```nim
try:
  let badProductId = 133
  var paul = newConsumer("paul@mail.org", badProductId)
  db.insert(paul)

except DbError:
  echo getCurrentExceptionMsg()
```

--------------------------------

### Fetch Many-to-Many Relationship (Single Entry, Explicit FK)

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Fetches the many-to-many relationship for a single entry `queryStartEntry`, returning a sequence of related entries from `queryEndEntries`. Requires explicit specification of the foreign key field names in the join model (`fkColumnFromJoinToManyStart`, `fkColumnFromJoinToManyEnd`) that link to the start and end models. This procedure will not compile if the specified fields do not correctly point to the models.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
```

--------------------------------

### Select Many-to-Many Relationships in Nim ORM

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

These procedures facilitate fetching many-to-many relationships between models, populating sequences or tables with related entries. They require the Norm ORM library, a database connection, starting model entries, a join model, and optionally explicit foreign key field names. Outputs include sequences or tables mapping IDs to related model sequences; they will only compile if the join model has appropriate fields pointing to the involved tables, and inference works only with unique field matches.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]])
```

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]];
    fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
```

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2])
```

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntry: M1; joinModelEntries: var seq[J];
    queryEndEntries: var seq[M2]; fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
```

--------------------------------

### Get Pool Size

Source: https://moigagoo.github.io/norm/apidocs/norm/pool

This function returns the current size of the connection pool as a Natural (unsigned integer).

```Nim
func size(pool: Pool): Natural

```

--------------------------------

### Setting Up Imports and Logging for Norm in Nim

Source: https://moigagoo.github.io/norm/tutorial/tables

This snippet imports standard and Norm modules required for database operations and sets up console logging to display generated SQL queries. It depends on the norm and std libraries. No inputs or outputs are produced directly, but logging enables query visibility during model creation.

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

addHandler newConsoleLogger(fmtStr = "")

```

--------------------------------

### Insert Data with Manual Foreign Keys in Norm

Source: https://moigagoo.github.io/norm/fk

Demonstrates inserting data into tables with manually handled foreign keys using Norm. It shows how to create and insert a `Product` and then create and insert a `Consumer`, referencing the `Product`'s `id`. This method allows for more flexibility by directly using IDs during insertion.

```nim
var cheese = Product(name: "Cheese", price: 13.30)
db.insert(cheese)

var bob = newConsumer("bob@mail.org", cheese.id)
db.insert(bob)

echo()
```

--------------------------------

### Select Data with Norm Using Manual Foreign Keys

Source: https://moigagoo.github.io/norm/fk

Demonstrates how to perform `select` queries on models with manually handled foreign keys using Norm. It shows retrieving a `Consumer` by email and then a `Product` using the `consumer.productId`. The assertions verify that the correct data is fetched, including associated fields for the `Product`.

```nim
var consumer = newConsumer()
db.select(consumer, "email = $1", "bob@mail.org")
doAssert(consumer.email == "bob@mail.org")

var product = newProduct()
db.select(product, "id = $1", consumer.productId)
doAssert(product.name == "Cheese")
doAssert(product.price == 13.30)

echo()
```

--------------------------------

### Get Default Size of Pool

Source: https://moigagoo.github.io/norm/apidocs/norm/pool

This function retrieves the default size of the connection pool. It returns a Natural (unsigned integer) representing the initial or current size of the pool.

```Nim
func defaultSize(pool: Pool): Natural

```

--------------------------------

### Nim: Get Column Name for Model Field

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Functions to retrieve the column name associated with a Model field. Overloads exist for both the type description and an instance of the model.

```nim
func col(T: typedesc[Model]; fld: string): string

func col[T: Model](obj: T; fld: string): string
```

--------------------------------

### Nim: Get Model Table Name

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Retrieves the table name for a Model. This is derived from the 'tableName' pragma or the type name itself. If a schema name is set, it is prepended to the table name.

```nim
func table(T: typedesc[Model]): string
```

--------------------------------

### Database Connection Wrapper (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Provides a convenient wrapper for database operations. It automatically obtains a `DbConn` using `getDb`, assigns it to the `db` variable, executes the provided code within a `try` block, and ensures the database connection is closed afterward.

```nim
template withDb(body: untyped): untyped
```

--------------------------------

### Create Database Tables in SQL

Source: https://moigagoo.github.io/norm/tutorial/rowsManyToX

SQL statements to create tables for User, Group, and UserGroup with appropriate primary and foreign key constraints. Sets up the schema for many-to-many relationships.

```SQL
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 "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))
```

--------------------------------

### Creating Initializer Functions for Norm Models in Nim

Source: https://moigagoo.github.io/norm/tutorial/tables

These functions initialize User and Customer instances with default values, ensuring Model fields are properly set to avoid nil issues during table creation. They take optional parameters for email and name, returning new instances. Purpose is to facilitate model instantiation; outputs are initialized objects ready for database operations. No major limitations if used as shown.

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

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

```

--------------------------------

### Nim: Get Model Schema Name

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Retrieves the schema name for a Model, as defined by the 'schemaName' pragma. Returns 'none(string)' if not explicitly set, which indicates the default schema. This is ignored in SQLite.

```nim
func schema(T: typedesc[Model]): Option[string]
```

--------------------------------

### Nim: Get Fully Qualified Column Name

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Functions to obtain the fully qualified column name, optionally including a table alias. Overloads support both type descriptions and model instances.

```nim
func fCol(T: typedesc[Model]; fld, tAls: string): string

func fCol(T: typedesc[Model]; fld: string): string

func fCol[T: Model](obj: T; fld, tAls: string): string

func fCol[T: Model](obj: T; fld: string): string
```

--------------------------------

### Set Custom Table Name in Norm

Source: https://moigagoo.github.io/norm/models

Shows how to override the default table name using the tableName pragma. This example maps the Thing model to a table named "ThingTable".

```Nim
type
  Thing* {.tableName: "ThingTable".} = ref object of Model
    attr: string
```

--------------------------------

### Model Inheritance in Norm

Source: https://moigagoo.github.io/norm/models

Example of model inheritance in Norm. NamedUser inherits from BaseUser, extending it with an additional name field. Norm handles inherited fields seamlessly in SQL schema generation.

```Nim
type
  NamedUser* = ref object of BaseUser
    name: string
```

--------------------------------

### Nim: Recursively Get Fully Qualified Column Names

Source: https://moigagoo.github.io/norm/apidocs/norm/model

Recursively retrieves fully qualified column names for a Model instance and any nested Model fields. This is useful for complex queries involving related tables.

```nim
func rfCols[T: Model](obj: T; flds: seq[string] = @[]): seq[string]
```

--------------------------------

### Convert Model to SQLite Row (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/private/sqlite/rowutils

Converts a Model instance into a lowdb.sqlite.Row instance. The 'force' parameter, when true, ensures that fields with the 'ro' pragma are included in the conversion. Imports: dbtypes, ../dot, ../utils, ../../model, ../../pragmas.

```nim
proc toRow[T: Model](obj: T; force = false): Row

```

--------------------------------

### Populate Object from SQLite Row (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/private/sqlite/rowutils

Populates a ref object instance from a lowdb.sqlite.Row instance. Handles nested ref object fields using the same row instance. Imports: dbtypes, ../dot, ../utils, ../../model, ../../pragmas.

```nim
proc fromRow[T: ref object](obj: var T; row: Row)

```

--------------------------------

### Manual Connection Manipulation in Norm Pool in Nim

Source: https://moigagoo.github.io/norm/pool

This code shows borrowing a connection from the pool using pop, performing an insert, and returning it with add. It relies on an existing pool instance and a Model type like Product. Inputs are the pool and model data; outputs include database insertions. Limitation: Manual handling requires explicit add to avoid leaks, and pool must be pre-created.

```nim
let dbConn = connPool.pop()

var product = newProduct()

product.name = "Table"
product.price = 123.45

dbConn.insert(product)

connPool.add(dbConn)
```

--------------------------------

### Insert nested models and select by nested field using Norm (Nim)

Source: https://moigagoo.github.io/norm/changelog

Demonstrates creating Person instances with nested Pet and Toy objects, inserting them into the database, and retrieving rows where the nested toy price exceeds a threshold. Requires a configured Norm DB connection and the model definitions for Person, Pet, and Toy.

```Nim
test "Get rows, nested models":
  var
    inpPersons = @[
      newPerson("Alice", newPet("cat", newToy(123.45))),
      newPerson("Bob", newPet("dog", newToy(456.78))),
      newPerson("Charlie", newPet("frog", newToy(99.99))),
    ]
    outPersons = @[newPerson()]

  for inpPerson in inpPersons.mitems:
    dbConn.insert(inpPerson)

  # Querying by """pet_favToy".price" to indicate that we want to match specifically by `Person.pet.favToy`:
  dbConn.select(outPersons, """"pet_favToy".price > $1""", 100.00)

  check outPersons === inpPersons[0..^2]
```

--------------------------------

### Create Tables for Manually Handled Foreign Keys in Norm

Source: https://moigagoo.github.io/norm/fk

Illustrates the process of creating database tables for models with manually handled foreign keys using Norm. It shows the necessity of calling `createTables` for each `Model` involved in the foreign key relationship, including `Product` and `Consumer`. This ensures the necessary table structures and constraints are established in the SQLite database.

```nim
let db = open(":memory:", "", "", "")

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

echo()
```

--------------------------------

### Define Enum as Integer Datatype for Norm.js (Nim)

Source: https://moigagoo.github.io/norm/customDatatypes

This snippet shows how to modify the previous example to store an enum (`CreatureFamily2`) as an integer in the database using Norm.js. It changes the `dbType` to 'INTEGER' and adjusts `dbValue` and `to` procedures to handle integer conversions.

```nim
type CreatureFamily2 = distinct CreatureFamily

func dbType*(T: typedesc[CreatureFamily2]): string = "INTEGER"
func dbValue*(val: CreatureFamily2): DbValue = dbValue(val.int)
proc to*(dbVal: DbValue, T: typedesc[CreatureFamily2]): CreatureFamily2 = dbVal.i.CreatureFamily2
```

--------------------------------

### Define Models with Manual Foreign Keys using Nim and Norm

Source: https://moigagoo.github.io/norm/fk

Demonstrates defining Nim objects `Product` and `Consumer` that inherit from Norm's `Model`. The `Consumer` model uses the `fk` pragma to declare a manual foreign key relationship with `Product`, storing the product's ID as an `int64`. This approach requires manual management of foreign key relationships.

```nim
import norm/[model, sqlite, pragmas]

type
  Product = ref object of Model
    name: string
    price: float

  Consumer = ref object of Model
    email: string
    productId {.fk: Product.}: int64

proc newProduct(): Product =
  Product(name: "", price: 0.0)

proc newConsumer(email = "", productId = 0'i64): Consumer =
  Consumer(email: email, productId: productId)
```

--------------------------------

### Execution of SQL Queries

Source: https://moigagoo.github.io/norm/apidocs/theindex

A procedure `execExpectTuplesOk` for executing SQL queries and expecting a successful tuple result. It takes a database connection, query, and parameters.

```nim
proc execExpectTuplesOk(db: DbConn; query: SqlQuery; args: seq[DbValue])
```

--------------------------------

### SQL SELECT Statements for Norm Data with Manual Foreign Keys

Source: https://moigagoo.github.io/norm/fk

Presents the SQL `SELECT` queries generated by Norm when retrieving data for `Consumer` and `Product` models with manual foreign key handling. It shows the specific columns selected and the `WHERE` clauses used, including referencing the `productId` to fetch related product details.

```sql
SELECT "Consumer".email, "Consumer".productId, "Consumer".id FROM "Consumer"  WHERE email = $1 LIMIT 1 <- ['bob@mail.org']
SELECT "Product".name, "Product".price, "Product".id FROM "Product"  WHERE id = $1 LIMIT 1 <- [1]
```

--------------------------------

### Insert Data into SQL Tables

Source: https://moigagoo.github.io/norm/tutorial/rowsManyToX

SQL INSERT statements to populate Group and User tables, followed by establishing relationships in UserGroup. Shows parameterized queries with values being bound.

```SQL
INSERT INTO "Group" (name) VALUES(?) <- @['groupFoo']
INSERT INTO "Group" (name) VALUES(?) <- @['groupBar']
INSERT INTO "User" (email) VALUES(?) <- @['foo@foo.foo']
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[1, 1]
INSERT INTO "User" (email) VALUES(?) <- @['bar@bar.bar']
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[2, 1]
INSERT INTO "UserGroup" (user, membershipGroup) VALUES(?, ?) <- @[1, 2]
```

--------------------------------

### Define Enum as String Datatype for Norm.js (Nim)

Source: https://moigagoo.github.io/norm/customDatatypes

This snippet demonstrates how to define a custom enum datatype (`CreatureFamily`) for use with Norm.js. It maps the enum to a 'TEXT' database type and provides procedures for converting between the Nim enum, its string representation, and `DbValue`. It also includes example usage for creating, inserting, and selecting data.

```nim
type CreatureFamily = enum
  BEAST = 1
  ANGEL = 2
  DEMON = 3
  HUMANOID = 4

type Creature* = ref object of Model
    name*: string
    family*: CreatureFamily

func dbType*(T: typedesc[CreatureFamily]): string = "TEXT"
func dbValue*(val: CreatureFamily): DbValue = dbValue($val)
proc to*(dbVal: DbValue, T: typedesc[CreatureFamily]): T = parseEnum[CreatureFamily](dbVal.s)

putEnv("DB_HOST", ":memory:")
let db = getDb()
var human = Creature(name: "Karl", family: CreatureFamily.HUMANOID)

db.createTables(human)
db.insert(human)

let rows = db.getAllRows(sql "SELECT name, family, id FROM creature")
echo $rows # @[@['Karl', 'HUMANOID', 1]]

var human2 = Creature()
db.select(human2, "id = ?", human.id)

assert human2.family == CreatureFamily.HUMANOID
```

--------------------------------

### Opening SQLite Connection and Creating Tables in Norm Nim

Source: https://moigagoo.github.io/norm/tutorial/tables

This opens an in-memory SQLite database connection and calls createTables on a Customer instance, automatically generating schemas for dependent models like User with foreign keys and nullable columns for Option fields. It depends on the open function from norm/sqlite and a valid model instance. Inputs include connection parameters and model; outputs are SQL CREATE statements executed on the database. Limitation: Ensures all referenced models' tables are created first; id field is auto-managed.

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

dbConn.createTables(newCustomer())
echo()

```

--------------------------------

### Select All Database Rows (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Fetches all rows from a table and populates a sequence of Model instances. Use with caution as it can retrieve a large number of rows.

```nim
proc selectAll[T: Model](dbConn: DbConn; objs: var seq[T])
```

--------------------------------

### Schema and Table Creation

Source: https://moigagoo.github.io/norm/apidocs/theindex

Procedures for creating database schemas and tables, supporting both PostgreSQL and SQLite. These functions are crucial for initializing database structures based on defined models.

```nim
proc createSchema[T: Model](dbConn: DbConn; obj: T)
proc createTables[T: Model](dbConn: DbConn; obj: T)
```

--------------------------------

### Create SQLite Connection Pool

Source: https://moigagoo.github.io/norm/apidocs/norm/pool

Creates an SQLite connection pool of specified size. Handles connection exhaustion with either `pepRaise` (throws error) or `pepExtend` (adds connection).

```Nim
proc newPool[T: sqlite.DbConn](defaultSize: Natural; getDbProc: proc (): sqlite.DbConn {. closure.} = sqlite.getDb; poolExhaustedPolicy = pepRaise): Pool[T]

```

--------------------------------

### Fetch Many-to-Many Relationship (Multiple Entries, Explicit FK)

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Fetches the many-to-many relationship for all entries in `queryStartEntries`, storing results in `queryEndEntries`. Requires explicit specification of the foreign key field names in the join model (`fkColumnFromJoinToManyStart`, `fkColumnFromJoinToManyEnd`) that link to the start and end models, respectively. This procedure will not compile if the specified fields do not correctly point to the models.

```nim
proc selectManyToMany[M1: Model; J: Model; M2: Model](dbConn: DbConn;
    queryStartEntries: seq[M1]; joinModelEntries: var seq[J];
    queryEndEntries: var Table[int64, seq[M2]];
    fkColumnFromJoinToManyStart: static string;
    fkColumnFromJoinToManyEnd: static string)
```

--------------------------------

### SQL INSERT Statements for Norm Data with Manual Foreign Keys

Source: https://moigagoo.github.io/norm/fk

Displays the generated SQL `INSERT` statements when adding data to `Product` and `Consumer` tables using Norm with manual foreign key handling. It shows the parameterized queries for inserting product details and consumer information, including the `productId`.

```sql
INSERT INTO "Product" (name, price) VALUES(?, ?) <- @['Cheese', 13.3]
INSERT INTO "Consumer" (email, productId) VALUES(?, ?) <- @['bob@mail.org', 1]
```

--------------------------------

### Create Postgres Connection Pool

Source: https://moigagoo.github.io/norm/apidocs/norm/pool

Creates a PostgreSQL connection pool of specified size.  Handles connection exhaustion with either `pepRaise` (throws error) or `pepExtend` (adds connection).

```Nim
proc newPool[T: postgres.DbConn](defaultSize: Natural; getDbProc = postgres.getDb; poolExhaustedPolicy = pepRaise): Pool[T]

```

--------------------------------

### Populate ref object sequence from raw SQL

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Populates a sequence of ref object instances from database using raw SQL query. Sequence must have at least one item. Columns must match field order in objects. May raise ValueError, DbError, or LoggingError.

```Nim
proc rawSelect[T: ref object](dbConn: DbConn; qry: string; objs: var seq[T];
                              params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}
```

--------------------------------

### SQL Schema for Norm Models with Manual Foreign Keys

Source: https://moigagoo.github.io/norm/fk

Presents the generated SQL `CREATE TABLE` statements for `Product` and `Consumer` models when using Norm with manual foreign key handling. It highlights the `id INTEGER NOT NULL PRIMARY KEY` for both tables and the explicit `FOREIGN KEY (productId) REFERENCES "Product"(id)` constraint on the `Consumer` table.

```sql
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))
```

--------------------------------

### Raw SQL Select for Ref Object (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Executes a raw SQL query to populate a ref object instance or a sequence of ref object instances from the database. The query columns must match the object fields in order.

```nim
proc rawSelect[T: ref object](dbConn: DbConn; qry: string; obj: var T;
                              params: varargs[DbValue, dbValue]) {. raises: {ValueError, DbError, LoggingError}.}
```

```nim
proc rawSelect[T: ref object](dbConn: DbConn; qry: string; objs: var seq[T];
                              params: varargs[DbValue, dbValue]) {. raises: {ValueError, DbError, LoggingError}.}
```

--------------------------------

### Define Model with Indexes and Create Tables using Norm (Nim & SQL)

Source: https://moigagoo.github.io/norm/indexes

This snippet shows how to declare a Nim model with unique and non‑unique indexes using Norm pragmas, open a SQLite connection, and generate the corresponding SQL statements. The Nim code depends on the Norm library and an SQLite driver. The output SQL creates the Person table and its indexes.

```Nim
import norm/[model, pragmas]

type
  Person* = ref object of Model
    email* {.uniqueIndex: "Person_emails"}: string
    firstName* {.index: "Person_names"}: string
    lastName* {.index: "Person_names"}: string
```

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

dbConn.createTables(Person())

echo()
```

```SQL
CREATE TABLE IF NOT EXISTS "Person"(email TEXT NOT NULL, firstName TEXT NOT NULL, lastName TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY);
CREATE INDEX IF NOT EXISTS Person_names ON "Person"(firstName, lastName);
CREATE UNIQUE INDEX IF NOT EXISTS Person_emails ON "Person"(email);
```

--------------------------------

### Querying Nested Relationships with Norm

Source: https://moigagoo.github.io/norm/tutorial/rows

Demonstrates querying nested relationships (Pet -> Customer -> User) by concatenating foreign-key fields with underscores in the query condition.

```nim
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[]
```

--------------------------------

### Fetch related Producer, Product, and Employee data using Norm in Nim

Source: https://moigagoo.github.io/norm/tutorial/rowCaveats

The snippet defines model types for Producer, Product, and Employee, creates an in-memory SQLite database, inserts sample records, and performs three separate select queries to retrieve related data. The results are combined into a ProducerContainer object and printed as JSON. Requires the Norm library and std/json module.

```Nim
import std/json\nimport norm/[model, sqlite]\n\ntype Producer = ref object of Model\n    name: string\n\nproc newProducer(name = \"\"): Producer = Producer(name: name)\n  \ntype Product = ref object of Model\n    name: string\n    producedBy: Producer\n\nproc newProduct(name = \"\", producedBy = newProducer()): Product = \n  result = Product(name: name, producedBy: producedBy)\n\nlet dbConn = open(\":memory:\", \"\", \"\", \"\")\n\ndbConn.createTables(newProducer())\ndbConn.createTables(newProduct())\n\nvar alex = newProducer(\"Alex\")\ndbConn.insert(alex)\nvar firstClassSpaghetti = newProduct(\"The best spaghetti\", alex)\ndbConn.insert(firstClassSpaghetti)\n\ntype Employee = ref object of Model\n    name: string\n    employer: Producer\n  \nproc newEmployee(name = \"\", employer = newProducer()): Employee =\n  result = Employee(name: name, employer: employer)\n\ndbConn.createTables(newEmployee())\nvar steff = newEmployee(\"Steff\", alex)\ndbConn.insert(steff)\n\ntype ProducerContainer = object\n  producer: Producer\n  products: seq[Product]\n  employees: seq[Employee]\n\nvar producer: Producer = newProducer()\nvar products: seq[Product] = @[newProduct()]\nvar employees: seq[Employee] = @[newEmployee()]\n\ndbConn.select(producer, \"Producer.id = ?\", alex.id)\ndbConn.select(products, \"producedBy = ?\", alex.id)\ndbConn.select(employees, \"employer = ?\", alex.id)\n\nlet producerContainer = ProducerContainer(\n  producer producer, \n  products: products,\n  employees: employees\n)\n\necho %*producerContainer
```

--------------------------------

### Populate ref object from raw SQL

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Populates a ref object instance and its ref object fields from database using raw SQL query. Columns must match field order. Raises NotFoundError if query returns nothing. May raise ValueError, DbError, or LoggingError.

```Nim
proc rawSelect[T: ref object](dbConn: DbConn; qry: string; obj: var T;
                              params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}
```

--------------------------------

### Convert Model to Postgres Row

Source: https://moigagoo.github.io/norm/apidocs/norm/private/postgres/rowutils

Converts a Model instance into a lowdb.postgres.Row instance. If the 'force' parameter is true, fields marked with the 'ro' pragma will not be skipped during conversion. This is essential for preparing data for database insertion or updates.

```nim
proc toRow[T: Model](obj: T; force = false): Row
```

--------------------------------

### Sum Column Values in Norm

Source: https://moigagoo.github.io/norm/tutorial/rows

Illustrates how to sum column values using the `sum` procedure in Norm. Covers summing all values, distinct values, and values meeting a specific condition. Requires importing `norm/model` and defining a data model.

```nim
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)
```

--------------------------------

### Select Model instance from database

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Populates a Model instance and its Model fields from database. Supports WHERE clause conditions with parameterized queries using ? placeholders. Uses table, col, and fCol procedures instead of hardcoded names. May raise NotFoundError, ValueError, DbError, or LoggingError.

```Nim
proc select[T: Model](dbConn: DbConn; obj: var T; cond: string;
                      params: varargs[DbValue, dbValue]) {.
    ...raises: {NotFoundError, ValueError, DbError, LoggingError}.}
```

--------------------------------

### Create Database Schema (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Creates the database schema for a given Model. This procedure is used to define the structure of database tables based on the provided Model definition.

```nim
proc createSchema[T: Model](dbConn: DbConn; obj: T)
```

--------------------------------

### Create Database Tables (Nim)

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

Creates database tables for a Model and any associated Model fields. This is typically called after `createSchema` to materialize the defined structure in the database.

```nim
proc createTables[T: Model](dbConn: DbConn; obj: T)
```

--------------------------------

### Select Model instance sequence from database

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Populates a sequence of Model instances from database. Sequence must have at least one item. Supports parameterized WHERE clause conditions. May raise ValueError, DbError, or LoggingError.

```Nim
proc select[T: Model](dbConn: DbConn; objs: var seq[T]; cond: string;
                      params: varargs[DbValue, dbValue]) {.
    ...raises: {ValueError, DbError, LoggingError}.}
```

--------------------------------

### Select All Records in Nim ORM

Source: https://moigagoo.github.io/norm/apidocs/norm/postgres

This procedure fetches all rows from a database table matching the given model type and populates a sequence of model instances. It depends on the Norm ORM library and requires a database connection (DbConn) and a model type (typedesc[T]) that supports instantiation via 'new <Type>'. The output is a sequence of model instances, but it has limitations as it fetches an uncontrolled number of rows, posing performance and memory risks.

```nim
proc selectAll[T: Model](dbConn: DbConn; typ: typedesc[T]): seq[T]
```

--------------------------------

### Accessing Populated Data after Single Select

Source: https://moigagoo.github.io/norm/tutorial/rows

Demonstrates accessing the data from the selected customer object and its populated user object.

```nim
echo customerBar[]
echo customerBar.user[]
```

--------------------------------

### Constructor Proc for newPaddedStringOfCap in Nim

Source: https://moigagoo.github.io/norm/apidocs/norm/types

Creates a new PaddedStringOfCap instance with an optional initial value. It takes a static integer capacity C and an optional string value, outputting a PaddedStringOfCap[C]. Useful for initializing database fields, with default empty string and capacity enforcement.

```Nim
func newPaddedStringOfCap[C: static[int]](val = ""): PaddedStringOfCap[C]

```

--------------------------------

### Constructor Proc for newStringOfCap in Nim

Source: https://moigagoo.github.io/norm/apidocs/norm/types

Constructs a new StringOfCap instance with optional initial string. Requires a static integer C for capacity and optional value, producing StringOfCap[C]. Facilitates safe string handling in databases, with defaults and static type checking.

```Nim
func newStringOfCap[C: static[int]](val = ""): StringOfCap[C]

```

--------------------------------

### Execute raw SQL SELECT query in Norm (Nim)

Source: https://moigagoo.github.io/norm/tutorial/rawSelects

Demonstrates how to execute a raw SQL SELECT query in Norm and parse the results into a custom ref object. This bypasses Norm's SQL generation but still uses its parsing capabilities. Requires norm/model, norm/sqlite, and norm/pragmas modules.

```Nim
import std/json
import norm/[model, sqlite, pragmas]

type Campaign* = ref object of Model
    name* {.unique.}: string

type Creature* = ref object of Model
    name*: string
    campaign* {.fk: Campaign.}: int64

putEnv("DB_HOST", ":memory:")
let db = getDb()
db.createTables(Campaign())
db.createTables(Creature())

# Add entries to Db
var campaign = Campaign(name: "MyCampaign")
db.insert(campaign)

var creature1 = Creature(name: "creature1", campaign: campaign.id)
var creature2 = Creature(name: "creature2", campaign: campaign.id)
db.insert(creature1)
db.insert(creature2)

# Query DB
type CreatureCampaignCount* = ref object
  count*: int

let countCampaignCreaturesQuery: string = """
    SELECT COUNT(*) AS count
    FROM creature
    INNER JOIN campaign ON creature.campaign = campaign.id
    WHERE campaign.name = ?
  """

var countResult = CreatureCampaignCount()
db.rawSelect(countCampaignCreaturesQuery, countResult, campaign.name)

echo countResult.count
```

--------------------------------

### Custom Connection Provider for Norm Pool in Nim

Source: https://moigagoo.github.io/norm/pool

Defines a custom function to provide DbConn instances, overriding default environment-based getDB. Used in newPool constructor for specific database paths. Inputs include the custom function returning DbConn; outputs a pool with custom connections. Limitation: Custom function must handle connection opening correctly, and cleanup like file removal may be needed.

```nim
func myDb: DbConn = open("mydb.db", "", "", "")

var anotherPool = newPool[DbConn](10, myDb)

assert anotherPool.size == 10
assert fileExists("mydb.db")

close anotherPool
removeFile("mydb.db")
```

--------------------------------

### Count Rows in Norm

Source: https://moigagoo.github.io/norm/tutorial/rows

Demonstrates how to count rows in a table using the `count` procedure in Norm. This includes counting all rows, distinct values in a column, and rows matching a specific condition. It avoids fetching actual data, making it efficient.

```nim
echo dbConn.count(Customer)
```

```nim
echo dbConn.count(Customer, "user", dist = true)
```

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

--------------------------------

### Insert multiple Model instances

Source: https://moigagoo.github.io/norm/apidocs/norm/sqlite

Inserts rows for each Model instance in open array. Supports force insertion and conflict handling policies for batch operations.

```Nim
proc insert[T: Model](dbConn: DbConn; objs: var openArray[T]; force = false;
                      conflictPolicy = cpRaise)
```
Tokens:
Raw