SQL Queries (.nornsql)

.nornsql files define named SQL queries and commands. Import them into your .norn tests and run them with run sql ... while keeping connection wiring outside the test file itself.

Basic .nornsql File

connection appDb

query ListUsersByStatus(status)
select Id, Email, Status
from Users
where Status = :status
end query

command UpdateUserStatus(id, status)
update Users
set Status = :status
where Id = :id
end command

Use query for row-returning operations and command for writes or non-row work. SQL parameters always use named :paramName placeholders.

Built-in Connection Setup

The SQL file references a connection alias. That alias is resolved through norn.sql.json. For the built-in adapters, that is the only JSON config file you need.

{
  "version": 1,
  "connections": {
    "appDb": {
      "adapter": "sqlserver",
      "profile": "appDb"
    }
  }
}

Built-in adapters currently supported out of the box are postgres, sqlserver, and sqlserver-windows. Environment-specific values stay in .nornenv, and for the built-in path that means a single connection string.

[env:prelive]
secret connectionString appDb = ENC[Server=sql01.company.local;Database=CustomerDb;User ID=norn_runner;Password=...;Encrypt=true;TrustServerCertificate=false;]

For PostgreSQL, the same built-in path works with "adapter": "postgres". Define connectionString appDb = ... in .nornenv and Norn passes it straight to the database driver.

For Windows / Trusted SQL Server authentication, use "adapter": "sqlserver-windows". That path uses PowerShell and expects an integrated-auth connection string.

[env:work]
connectionString appDb = Driver={ODBC Driver 18 for SQL Server};Server=sql01.company.local;Database=CustomerDb;Trusted_Connection=Yes;Encrypt=yes;TrustServerCertificate=yes;

Importing and Running SQL

import "./db/customers.nornsql"

test sequence VerifyCustomerState
    var activeUsers = run sql ListUsersByStatus("Active")
    assert activeUsers[0].Email exists

    var updateResult = run sql UpdateUserStatus(42, "Disabled")
    assert updateResult.affectedRows == 1
end sequence

Query results return arrays of row objects. Command results return execution metadata such as affectedRows.

Custom Adapters

If you need a database Norn does not support out of the box, or a custom auth/runtime path, add an optional norn.adapters.json file and point your connection at that custom adapter ID.

{
  "version": 1,
  "adapters": {
    "custom-db": {
      "command": ["node", "./tools/adapters/custom-db.js"]
    }
  }
}

Rules to Remember

• Import .nornsql files directly from the .norn file that uses them.
• Each .nornsql file declares one connection alias at the top.
• SQL files stay flat in v1: no namespaces and no .nornsql-to-.nornsql imports.
• run sql calls can use positional or named arguments.
• The built-in path is .nornsql + norn.sql.json + .nornenv.
• For built-in adapters, .nornenv should define connectionString <profile> = ....
• Use norn.adapters.json only for custom adapters.
• Norn ships built-in postgres, sqlserver, and sqlserver-windows support out of the box.

VS Code Support

In VS Code, .nornsql files get syntax highlighting, diagnostics, import completion, and a sidebar shortcut that can scaffold starter SQL files in the workspace root.