### Select Database Rows with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to retrieve single or multiple rows from a database using Norm. Includes examples for filtering, ordering, limiting, and handling missing records.

```nim
var anotherCustomer = newCustomer()
dbConn.select(anotherCustomer, "User.email = ?", "alice@example.com")

try:
  var missing = newCustomer()
  dbConn.select(missing, "Customer.name = ?", "NonExistent")
except NotFoundError:
  echo "Customer not found"

var foundCustomers = @[newCustomer()]
dbConn.select(foundCustomers, "User.email = ?", "alice@example.com")

var topCustomers = @[newCustomer()]
dbConn.select(topCustomers, "1 ORDER BY name DESC LIMIT 2")

let allCustomers = dbConn.select(Customer, "1")

var allRows = @[newCustomer()]
dbConn.selectAll(allRows)
```

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

### Aggregate Queries in Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Provides examples for performing aggregate operations such as counting rows, summing column values, and checking for the existence of records.

```nim
echo dbConn.count(Order)
echo dbConn.count(Order, "*", false, "product = ?", "Apple")
echo dbConn.count(Order, "product", dist = true)
echo dbConn.sum(Order, "quantity")
echo dbConn.exists(Order, "product = ?", "Apple")
```

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

### One-to-Many Relationships in Nim with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates querying one-to-many relationships in Nim using Norm's `selectOneToMany` function. This method efficiently retrieves related records by specifying the parent model and a collection for the child records, optionally providing the foreign key field name. It includes setup for `Author` and `Book` models and examples of selecting books for a given author.

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

type
  Author* = ref object of Model
    name*: string

  Book* = ref object of Model
    title*: string
    author*: Author

func newAuthor(name = ""): Author = Author(name: name)
func newBook(title = "", author = newAuthor()): Book = Book(title: title, author: author)

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(newBook())

var author = newAuthor("Stephen King")
var books = @[
  newBook("The Shining", author),
  newBook("It", author),
  newBook("Carrie", author)
]
dbConn.insert(author)
for b in books.mitems:
  dbConn.insert(b)

# Select all books for an author
var authorBooks = @[newBook()]
dbConn.selectOneToMany(author, authorBooks, "author")  # Field name on Book

for book in authorBooks:
  echo book.title  # The Shining, It, Carrie

# With inferred field name (when only one FK exists)
var inferredBooks = @[newBook()]
dbConn.selectOneToMany(author, inferredBooks)
```

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

### Many-to-Many Relationships in Nim with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Shows how to query many-to-many relationships in Nim using Norm's `selectManyToMany` function. This requires a join model (e.g., `Enrollment`) and utilizes the `selectManyToMany` function with the parent model, a join collection, and a target collection, optionally specifying the join and target field names. Examples include retrieving all courses for a student.

```nim
import norm/[model, sqlite]
import std/with

type
  Student* = ref object of Model
    name*: string

  Course* = ref object of Model
    title*: string

  Enrollment* = ref object of Model  # Join table
    student*: Student
    course*: Course

func newStudent(name = ""): Student = Student(name: name)
func newCourse(title = ""): Course = Course(title: title)
func newEnrollment(student = newStudent(), course = newCourse()): Enrollment =
  Enrollment(student: student, course: course)

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(newStudent())
dbConn.createTables(newCourse())
dbConn.createTables(newEnrollment())

var
  alice = newStudent("Alice")
  bob = newStudent("Bob")
  math = newCourse("Mathematics")
  physics = newCourse("Physics")

with dbConn:
  insert alice
  insert bob
  insert math
  insert physics
  insert newEnrollment(alice, math)
  insert newEnrollment(alice, physics)
  insert newEnrollment(bob, math)

# Get all courses for Alice
var enrollments = @[newEnrollment()]
var aliceCourses = @[newCourse()]
dbConn.selectManyToMany(alice, enrollments, aliceCourses, "student", "course")

for course in aliceCourses:
  echo course.title  # Mathematics, Physics

# With inferred field names
var enrollments2 = @[newEnrollment()]
var courses2 = @[newCourse()]
dbConn.selectManyToMany(alice, enrollments2, courses2)
```

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

### Nim Connection Pooling with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Illustrates setting up and using connection pools for efficient database connection management in multi-threaded Nim applications with Norm. It covers creating pools with default or custom exhaustion policies, manual connection management (pop/add), and utility functions like size, reset, and close.

```nim
import std/os
import norm/[sqlite, pool]

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

# Create pool with default size
var dbPool = newPool[DbConn](10)  # 10 connections

# Use pool with withDb template
dbPool.withDb:
  db.createTables(newUser())
  var user = newUser("alice@example.com")
  db.insert(user)

# Pool exhaustion policy
var strictPool = newPool[DbConn](2, poolExhaustedPolicy = pepRaise)
var flexiblePool = newPool[DbConn](2, poolExhaustedPolicy = pepExtend)

# Manual pool management
let conn = dbPool.pop()
try:
  conn.createTables(newUser())
finally:
  dbPool.add(conn)  # Return connection to pool

# Pool utilities
echo dbPool.size  # Current pool size
echo dbPool.defaultSize  # Original pool size
dbPool.reset()  # Reset to default size
dbPool.close()  # Close all connections
```

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

### Manual Foreign Keys with fk Pragma in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Explains how to define manual foreign keys in Nim using the `fk` pragma with integer fields in Norm models. This approach provides more control and avoids automatic JOINs, requiring separate table creation and manual fetching of related data. It demonstrates defining models, creating tables, inserting data with FKs, and selecting related records.

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

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

  Order* = ref object of Model
    customerEmail*: string
    productId* {.fk: Product.}: int64  # Manual FK

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

proc newOrder(email = "", productId = 0'i64): Order =
  Order(customerEmail: email, productId: productId)

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(newProduct())
dbConn.createTables(newOrder())  # Must create separately with fk pragma

# Insert with manual foreign key
var product = newProduct("Widget", 9.99)
dbConn.insert(product)

var order = newOrder("alice@example.com", product.id)
dbConn.insert(order)

# Select - related data must be fetched manually
var foundOrder = newOrder()
dbConn.select(foundOrder, "customerEmail = ?", "alice@example.com")

var foundProduct = newProduct()
dbConn.select(foundProduct, "id = ?", foundOrder.productId)
echo foundProduct.name  # Widget
```

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

### Manage Database Connections

Source: https://context7.com/moigagoo/norm/llms.txt

Use the withDb template to manage database connections via environment variables, ensuring connections are opened and closed correctly.

```nim
import std/os
import norm/sqlite

putEnv("DB_HOST", "myapp.db")

withDb:
  db.createTables(newUser())
  var user = newUser("alice@example.com")
  db.insert(user)
```

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

### Execute Raw SQL Queries with Custom Result Types in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to use `rawSelect` to execute raw SQL queries and parse results into custom Nim objects, bypassing ORM model definitions for complex queries. Requires the 'norm/model', 'norm/sqlite', and 'norm/pragmas' modules.

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

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

  # Custom result type (not a Model)
  CategoryStats* = ref object
    category*: string
    totalProducts*: int
    avgPrice*: float

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(Product())

var products = @[
  Product(name: "Apple", category: "Fruit", price: 1.50),
  Product(name: "Banana", category: "Fruit", price: 0.75),
  Product(name: "Carrot", category: "Vegetable", price: 0.50)
]
for p in products.mitems:
  dbConn.insert(p)

# Raw SQL with custom result type
let statsQuery = """
  SELECT category, COUNT(*) as totalProducts, AVG(price) as avgPrice
  FROM \"Product\"
  GROUP BY category
  ORDER BY category
"""

var stats = @[CategoryStats()]
dbConn.rawSelect(statsQuery, stats)

for stat in stats:
  echo stat.category, ": ", stat.totalProducts, " products, avg $", stat.avgPrice

# Raw select single row
var fruitStats = CategoryStats()
dbConn.rawSelect(statsQuery & " LIMIT 1", fruitStats)
```

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

### PostgreSQL Upsert Operations with Conflict Policies in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to perform insert operations with different conflict policies (ignore and replace) in PostgreSQL using the Norm library in Nim. It sets up environment variables for database connection, defines a User model, creates tables, and then inserts user data, handling potential conflicts.

```nim
import std/os
import norm/postgres

putEnv("DB_HOST", "localhost")
putEnv("DB_USER", "postgres")
putEnv("DB_PASS", "password")
putEnv("DB_NAME", "myapp")

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

withDb:
  db.createTables(User())

  var user = User(email: "alice@example.com")

  # Insert with conflict policy
  db.insert(user, conflictPolicy = cpIgnore)  # Ignore if exists

  user.id = 0  # Reset ID
  db.insert(user, conflictPolicy = cpReplace)  # Replace if exists
```

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

### Define Indexes and Unique Constraints with Pragmas in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Shows how to define database indexes and unique constraints using pragmas like `index`, `uniqueIndex`, `unique`, and `uniqueGroup` in Nim's Norm ORM. These pragmas enhance query performance and ensure data integrity.

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

type
  Person* = ref object of Model
    email* {.uniqueIndex: "idx_person_email".}: string
    firstName* {.index: "idx_person_name".}: string
    lastName* {.index: "idx_person_name".}: string  # Composite index

  Order* = ref object of Model
    orderNumber* {.unique.}: string
    customerId* {.index: "idx_order_customer".}: int64
    productId* {.index: "idx_order_product".}: int64

  # Unique group - combination must be unique
  CartItem* = ref object of Model
    cartId* {.uniqueGroup.}: int64
    productId* {.uniqueGroup.}: int64  # Same product can't be in cart twice
    quantity*: int

# Generated indexes:
# CREATE UNIQUE INDEX IF NOT EXISTS idx_person_email ON "Person"(email)
# CREATE INDEX IF NOT EXISTS idx_person_name ON "Person"(firstName, lastName)
```

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

### Customize Table and Schema Names using Pragmas in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Illustrates how to customize table and PostgreSQL schema names using pragmas like `tableName` and `schemaName` within Nim's Norm ORM. This allows for flexible naming conventions independent of Nim type names. Supports SQLite and PostgreSQL.

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

type
  # Custom table name
  UserAccount* {.tableName: "user_accounts".} = ref object of Model
    email*: string

  # PostgreSQL schema (ignored in SQLite)
  AdminUser* {.schemaName: "admin", tableName: "users".} = ref object of Model
    username*: string

  # Read-only model - can only select, not insert/update/delete
  UserSummary* {.readOnly, tableName: "user_accounts".} = ref object of Model
    email*: string

# Generated SQL for UserAccount:
# CREATE TABLE IF NOT EXISTS "user_accounts"(email TEXT NOT NULL, id INTEGER NOT NULL PRIMARY KEY)

# Generated SQL for AdminUser (PostgreSQL):
# CREATE SCHEMA IF NOT EXISTS "admin"
# CREATE TABLE IF NOT EXISTS "admin"."users"(username TEXT NOT NULL, id BIGSERIAL PRIMARY KEY)
```

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

### Create Database Tables

Source: https://context7.com/moigagoo/norm/llms.txt

The createTables procedure generates and executes SQL schema statements based on model definitions, including handling foreign key dependencies automatically.

```nim
import norm/sqlite

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(newCustomer())
```

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

### Manage Database Transactions

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to wrap multiple database operations in a transaction block to ensure atomicity, where any exception triggers an automatic rollback.

```nim
dbConn.transaction:
  for i in 1..5:
    var user = newUser($i & "@example.com")
    dbConn.insert(user)

try:
  dbConn.transaction:
    for i in 6..10:
      var user = newUser($i & "@example.com")
      dbConn.insert(user)
      if i == 8:
        raise newException(ValueError, "Something went wrong")
except ValueError:
  echo "Transaction rolled back"
```

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

### Control Foreign Key Behavior with onDelete Pragma in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to control foreign key deletion behavior using the `onDelete` pragma in Nim's Norm ORM. Options include `CASCADE` (delete related records) and `SET NULL` (set foreign key to NULL).

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

type
  Department* = ref object of Model
    name*: string

  Employee* = ref object of Model
    name*: string
    department* {.onDelete: "CASCADE".}: Department  # Delete employees when department deleted

  Project* = ref object of Model
    name*: string
    lead* {.onDelete: "SET NULL".}: Option[Employee]  # Set to NULL when employee deleted

# Generated SQL:
# FOREIGN KEY(department) REFERENCES "Department"(id) ON DELETE CASCADE
# FOREIGN KEY(lead) REFERENCES "Employee"(id) ON DELETE SET NULL
```

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

### Select Rows from Database

Source: https://context7.com/moigagoo/norm/llms.txt

The select procedure populates model instances from the database based on specific conditions, automatically resolving relationships via JOINs.

```nim
import std/options
import norm/sqlite

var foundCustomer = newCustomer()
dbConn.select(foundCustomer, "Customer.name = ?", "Alice")
echo foundCustomer.name
echo foundCustomer.user.email
```

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

### Manual Transaction Rollback in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Demonstrates how to explicitly trigger a rollback within a database transaction using the `rollback()` function. This is useful for custom error handling or conditional transaction cancellation. It requires a database connection object and a try-except block to catch potential `RollbackError`.

```nim
try:
  dbConn.transaction:
    var user = newUser("temp@example.com")
    dbConn.insert(user)
    rollback()  # Explicit rollback
except RollbackError:
  echo "Manually rolled back"
```

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

### Implement Custom Data Types with Norm in Nim

Source: https://context7.com/moigagoo/norm/llms.txt

Explains how to extend Norm to support custom Nim data types by implementing `dbType`, `dbValue`, and `to` procedures. This allows mapping custom types like enums to database types (e.g., TEXT or INTEGER).

```nim
import std/strutils
import norm/[model, sqlite]

type
  Priority = enum
    Low = 1
    Medium = 2
    High = 3

  Task* = ref object of Model
    title*: string
    priority*: Priority

# Map Priority enum to database TEXT type
func dbType*(T: typedesc[Priority]): string = "TEXT"

# Convert Priority to DbValue for storage
func dbValue*(val: Priority): DbValue = dbValue($val)

# Convert DbValue back to Priority
proc to*(dbVal: DbValue, T: typedesc[Priority]): Priority =
  parseEnum[Priority](dbVal.s)

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(Task())

var task = Task(title: "Review code", priority: High)
dbConn.insert(task)

var found = Task()
dbConn.select(found, "id = ?", task.id)
echo found.priority  # High

# Store enum as integer instead
type
  Status = enum
    Pending = 0
    InProgress = 1
    Complete = 2

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

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

### Insert Rows into Database

Source: https://context7.com/moigagoo/norm/llms.txt

The insert procedure persists model instances to the database. It automatically handles related objects and supports forced ID insertion.

```nim
import std/[options, with]
import norm/sqlite

let dbConn = open(":memory:", "", "", "")
dbConn.createTables(newCustomer())

var customerAlice = newCustomer(some "Alice", newUser("alice@example.com"))
dbConn.insert(customerAlice)

var customers = [customerAlice]
with dbConn:
  insert customers
```

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

### Update Database Rows with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Shows how to modify existing records in the database. Norm automatically handles updates to related models and supports bulk updates for efficiency.

```nim
customer.name = some "Alice Smith"
customer.user.email = "alice.smith@example.com"
dbConn.update(customer)

var customers = @[newCustomer()]
dbConn.select(customers, "1")
for c in customers:
  c.name = some("Updated " & c.name.get(""))
dbConn.update(customers)
```

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

### Define Models in Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Models are defined as ref objects inheriting from the Model base type. Norm supports optional fields using Option types and unique constraints via pragmas.

```nim
import std/options
import norm/[model, pragmas]

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

  Customer* = ref object of Model
    name*: Option[string]
    user*: User

  Product* = ref object of Model
    sku* {.unique.}: string
    name*: string
    price*: float

func newUser*(email = ""): User = User(email: email)
func newCustomer*(name = none string, user = newUser()): Customer = Customer(name: name, user: user)
func newProduct*(sku = "", name = "", price = 0.0): Product = Product(sku: sku, name: name, price: price)
```

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

### Delete Database Rows with Norm

Source: https://context7.com/moigagoo/norm/llms.txt

Explains the process of removing rows from the database. Deleting an object sets its reference to nil.

```nim
dbConn.delete(customer)

var customers = @[newCustomer()]
dbConn.select(customers, "1")
dbConn.delete(customers)
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.