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 the sql.connections section in norn.config.json.

{
  "version": 1,
  "sql": {
    "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;]

If several environments share the same shape, define the connection string once and fill in the small parts from the active environment. Imported .nornenv files work for these values too.

var appDb_encrypt = true
var appDb_trustServerCertificate = false

import "./shared/db.nornenv"

connectionString appDb = Server={{appDb_server}};Database={{appDb_database}};User ID={{appDb_user}};Password={{appDb_password}};Encrypt={{appDb_encrypt}};TrustServerCertificate={{appDb_trustServerCertificate}};

[env:dev]
var appDb_server = localhost
var appDb_database = CustomerDev
var appDb_user = norn_dev
secret appDb_password = dev-password

[env:prelive]
var appDb_server = sql01.company.local
var appDb_database = CustomerDb
var appDb_user = norn_runner
secret appDb_password = ENC[...]

A connection string declared outside any [env:...] section can reference variables from the selected environment. If you declare a connection string inside an [env:...] section, that value can only reference common variables.

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 a custom adapter under sql.adapters and point your connection at that custom adapter ID.

{
  "version": 1,
  "sql": {
    "connections": {
      "appDb": {
        "adapter": "custom-db",
        "profile": "appDb"
      }
    },
    "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.config.json + .nornenv.
• For built-in adapters, .nornenv should define connectionString <profile> = ....
• .nornenv connection strings can use {{name}} templates for reusable environment-specific parts.
• Use sql.adapters 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.