Query annotations

Every query in a repository file needs a name annotation. A repository file can also declare the generated class name.

Repository annotation

Place this before the first query in a *.sql file:

-- repo_name: UsersRepo

nORM uses that name for the generated repository class.

Query annotation

Place this immediately before each SQL statement:

-- name: get_user :one
SELECT * FROM users
WHERE id = :id;

Format:

-- name: <query_name> :<command>

• query_name becomes the generated method name (get_user -> get_user).
• command controls return shape and execution behavior.

Commands

Command	Generated behavior (Python)
:one	Return at most one row, model, or scalar
:many	Return a list of rows, models, or scalars
:exec	Execute without returning row data
:execrows	Execute and return affected row count (cur.rowcount)
:execlastid	Execute and return last insert id (dialect-specific)

:one

Use :one for single-row reads and INSERT/UPDATE/DELETE ... RETURNING statements where one row is expected.

-- name: get_author :one
SELECT * FROM authors
WHERE id = :id;

Generated signature:

python

async def get_author(self, id: int) -> Author | None: ...

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

:many

Use :many for multi-row reads and RETURNING statements where multiple rows are valid.

-- name: list_authors :many
SELECT * FROM authors;

Generated signature:

python

async def list_authors(self) -> list[Author]: ...

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

:exec

Use :exec when callers do not need returned rows.

-- name: delete_author :exec
DELETE FROM authors
WHERE id = :id;

Generated signature:

python

async def delete_author(self, id: int) -> None: ...

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

:execrows

Use :execrows when callers need the number of affected rows.

-- name: update_many :execrows
UPDATE authors
SET name = :name, rating = :rating
RETURNING *;

Generated signature and body:

python

async def update_many(
    self,
    name: str,
    rating: int | None,
) -> int:
    query = """
        UPDATE authors SET name = %(name)s, rating = %(rating)s
        RETURNING authors.id AS id, authors.name AS name, authors.rating AS rating
    """

    params = {
        "name": name,
        "rating": rating,
    }

    async with self.db.cursor() as cur:
        await cur.execute(query, params)

        result = cur.rowcount

    return result

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

:execlastid

Use :execlastid for inserts where the caller needs the auto-generated id. Support depends on the target dialect.

-- name: create_author :execlastid
INSERT INTO authors (name) VALUES (:name);

Scalar returns

When a query returns exactly one column - in SELECT, or in INSERT / UPDATE / DELETE ... RETURNING — nORM generates a scalar return type instead of a row model:

-- name: count_authors :one
SELECT COUNT(*) AS count FROM authors
WHERE id = :id;

python

async def count_authors(self, id: int) -> int | None: ...

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

Row schemas

When a query does not map to a single table model, nORM generates a <QueryName>Row model:

-- name: list_author_summaries :many
SELECT name, rating FROM authors;

python

class ListAuthorSummariesRow(BaseModel):
    name: str
    rating: int | None

async def list_author_summaries(self) -> list[ListAuthorSummariesRow]: ...

example.go

// coming soon

example.rs

// coming soon

example.ts

// coming soon

Documenting queries

Lines starting with -- between the name annotation and the SQL body become method docstrings in generated code. See Query comments.

Related

• Fetching records
• Inserting records
• Updating records
• Deleting records
• Macros