ormin ORM

ormin is a lightweight, prepared SQL statement generator for Nim. It provides a Nim-based DSL for queries to prevent runtime syntax errors and SQL injections.

Key features include:

• Compile-time query checking: Validates types, table names, and column names at compile time.
• Automatic join generation: Automatically computes natural joins based on table relations.
• High performance: Generates prepared statements that match the speed of low-level hand-written API calls.
• First-class JSON support: Allows direct use of JSON without explicit row-to-JSON conversions.

Ormin provides several high-level abstractions for building networked applications:

• Protocol DSL: Use the protocol macro to define paired server/client handlers that communicate via JSON. It uses keywords like recv, broadcast, and send. Every server block must have a corresponding client block.
• JSON Dispatcher: Use createDispatcher to map textual commands to Nim procedures.
• WebSocket Server: Use serverws to run a small WebSocket server that supports broadcasting messages to specific receivers via the serve procedure.

Inline query blocks resolve the db identifier from the current lexical scope. This allows you to pass a specific DbConn into a procedure to override a global connection, enabling complex database logic within scoped procedures.

proc loadUser(db: DbConn; userId: int): User =
  let row = query:
    select user(id, name, email)
    where id == ?userId
    limit 1

  User(id: row.id, name: row.name, email: row.email)

To use ormin, follow these two steps:

1. Generate a model from SQL: Place your schema in an .sql file and use importModel. This runs ormin_importer to include the generated Nim code. Alternatively, use includeStatic = true to build the model metadata directly from the SQL file at compile time.
2. Create a database connection: Ormin requires a global connection named db to issue queries. You must use the driver matching your backend (SQLite or PostgreSQL) and open the connection using Nim's database modules.

You can perform upsert operations using onconflict combined with either donothing() (to ignore duplicates) or doupdate(...) (to update existing rows).

Note: A plain INSERT ... VALUES does not support a where clause. To use a conditional update during an upsert, you must use the onconflict(...)+doupdate(...)+where ... pattern.

# Upsert on conflict (SQLite/PostgreSQL)
query:
  insert tb_nullable(id = ?id, note = ?note)
  onconflict(id)
  doupdate(note = ?note)

# Conditional upsert update
query:
  insert tb_nullable(id = ?id, note = ?note)
  onconflict(id)
  doupdate(note = ?note)
  where note != ?note

# Ignore duplicates
query:
  insert tb_nullable(id = ?id, note = ?note)
  onconflict(id)
  donothing()

To use SQLite, import the ormin module, call importModel with DbBackend.sqlite, and initialize a global db connection.

Note: Ormin handles quoted table names in dropTable. If you need the legacy behavior (using normalized lookup names instead of preserved SQL identifiers), use the compile flag -d:orminLegacySqliteDropNames.

import ../ormin
importModel(DbBackend.sqlite, "model_sqlite")
let db {.global.} = open(":memory:", "", "", "")

To use PostgreSQL, import the postgres module along with ormin, call importModel with DbBackend.postgre, and initialize a global db connection using your database credentials.

import postgres
import ../ormin
importModel(DbBackend.postgre, "model_postgre")
let db {.global.} = open("localhost", "user", "password", "dbname")

Execute the full test suite using the Nimble package manager.

nimble test

Use createIter to transform a query into a Nim iterator. This allows you to yield rows lazily, which is more memory-efficient for large result sets. The iterator accepts parameters matching the ? or % placeholders in the query.

createIter postsIter:
  select post(id, title)
  where author == ?userId

for row in db.postsIter(userId):
  echo row.title