Backs a field with a database routine rather than a catalog table. Supported today is the table-valued function (PostgreSQL RETURNS TABLE / SETOF): jOOQ generates such a function as a first-class catalog Table<R>, so the field’s element type is bound to the routine’s result and resolves the routine-result columns exactly as a plain table type does, and the rewrite projects Type.$project(…​) against it with the same selection narrowing. That binding comes from the routine, not from a directive: an element type whose chain ends on the routine needs no @table of its own. The difference from a plain catalog read is the row source: instead of the bare Tables.X singleton, the rewrite calls the schema’s generated Routines convenience method with the routine’s IN parameters bound from GraphQL field arguments (argMapping) or from columns of the previous table in the chain (columnMapping).

@routine is the seam for reading from a stored function whose result set parameterises on its inputs, such as an access-control function that returns the rows a caller may see, and, on Mutation, for writing through a stored function that performs the mutation itself (see Writes on Mutation). The routine’s shape (table-valued) is read off its jOOQ kind, not declared.

The directive works at root and at child positions, and it composes with @reference: the directives on a field, read left to right, describe the path your data travels, so the routine’s result table can supply the field’s rows, feed further joins, or sit between catalog tables (see Composing with @reference).

SDL signature

directive @routine(
    name:          String!
    argMapping:    String
    columnMapping: String
) repeatable on FIELD_DEFINITION

Parameters

Name Type Default Description

name

String!

(required)

The database routine name, optionally schema-qualified (schema.routine). Must resolve to a table-valued function in the jOOQ catalog.

argMapping

String

(empty)

Maps routine IN parameters to GraphQL arguments (routineParam: path, comma-separated). The routine parameter name is the jOOQ-generated (camelCased) name, e.g. pEnv for a p_env parameter. The right-hand side is the shared path form: a bare argument name, or a dot-path walking into nested input fields (pInventoryId: input.inventoryId), described once in Binding a parameter to a nested input field. Unmentioned parameters bind to a GraphQL argument of the same name (identity).

columnMapping

String

(empty)

Maps routine IN parameters to columns of the previous table in the chain (routineParam: column_name, comma-separated). At the head of a child chain the previous table is the enclosing type’s table; after @reference hops it is the last hop’s table. A column-bound parameter makes the call correlated: the routine joins as CROSS JOIN LATERAL and re-evaluates per row. Each parameter has exactly one source, so a name may appear in argMapping or columnMapping, never both.

Canonical example

A table-valued access-control function backing a root list field. The element type carries no directive of its own: the routine ends the field’s chain, so its result binds the type. The three TEXT IN parameters bind from GraphQL arguments:

type Tilgang {
    organisasjonskode: Int
    rollekode: String
}

type Query {
    tilganger(env: String!, serviceId: String!, feideId: String!): [Tilgang!]!
        @routine(
            name:       "tilganger_for_feidebruker_med_fs_fiktivt_fnr"
            argMapping: "pEnv: env, pServiceId: serviceId, pFeideId: feideId"
        )
        @defaultOrder(fields: [{name: "organisasjonskode"}, {name: "rollekode"}])
}

The rewrite emits select(Tilgang.$project(…​)).from(Routines.tilgangerForFeidebrukerMedFsFiktivtFnr(env, serviceId, feideId)).where(noCondition()).orderBy(…​).fetch(). A query selecting only organisasjonskode projects only that routine-result column.

The @defaultOrder is required here rather than decorative: the routine result is the chain’s last node and a function result carries no primary key, so nothing can supply a deterministic order unless the schema names it. See The read surface.

Writing @table(name: "tilganger_for_feidebruker_med_fs_fiktivt_fnr") on Tilgang is still accepted and changes nothing, so schemas that carry it keep working. It is worth removing: the two spellings of the routine’s name have to agree, the annotation reads as a claim that the type is a stored table, and it welds the type to one routine, so two functions returning the same row shape cannot share it.

The read surface: filtering, ordering and pagination

A @routine puts a function call in the FROM clause. WHERE and ORDER BY are different clauses of the same statement, so a routine-backed field filters and sorts like any other field: @condition, filter arguments, @defaultOrder and @orderBy all compose, at root and at child positions alike, and they resolve against the terminus of the field’s table chain (the last node, which is the table the field’s element type is bound to). The field’s remaining arguments are unaffected: an argument bound to a routine IN parameter is spent on the call and is never read as a filter.

One rule governs the ordering, and it is the rule every list field already lives under:

A list result is ordered, by the terminus primary key when there is one and by an authored @defaultOrder when there is not.

A table-valued function’s result table has no primary key, so the two terminus kinds land differently, and that is the whole of the routine-specific story:

  • Catalog terminus (a @reference hop follows the routine): the terminus is an ordinary table, the primary-key fallback applies, and a list field needs no ordering directive at all.

  • Routine terminus (the routine is the last node): there is no key to fall back on, so a list field must name its order over the function’s own result columns with @defaultOrder(fields: […​]), or take one from an @orderBy argument. Omitting it is a build error naming the function; @defaultOrder(primaryKey: true) there is a build error too, and lists the result columns as candidates.

@asConnection follows from the same rule, with no routine-specific caveat. Keyset pagination needs an ordering, which is what the two bullets above are about; given one, the seek predicate is an ordinary WHERE row-value comparison and a function in the FROM is a table expression like any other. A chain with @reference hops paginates too, and its totalCount counts the joined chain rather than the terminus alone.

At a child position, pagination is the one axis that does not compose yet: a child connection rides the batched keyed re-query anchor, which a correlated routine call does not join yet, so @asConnection on a routine-backed child is reported as deferred. On a Mutation field it is a build error, the write’s post-commit re-read being keyed rather than paged.

Child positions: correlated calls

At a child position, columnMapping feeds routine parameters from the enclosing row. The call becomes correlated: SQL evaluates it once per parent row, via CROSS JOIN LATERAL, inside the same statement that fetches the parent (the inline correlated multiset), so there is no per-row round trip.

type Actor @table(name: "actor") {
    films(minLength: Int!): [ActorFilm!]
        @routine(
            name:          "films_for_actor"
            argMapping:    "pMinLength: minLength"
            columnMapping: "pActorId: actor_id"
        )
        @defaultOrder(fields: [{name: "film_id"}])
}

pActorId reads each actor row’s actor_id; pMinLength reads the GraphQL argument. Mixed calls like this emit the routine’s Field-overload surface, so both bindings are typed at the call site.

The @defaultOrder here is not a child-position speciality; see The read surface for the one rule that puts it there.

Composing with @reference: the table chain

A field’s table chain is the concatenation, in written order, of the implicit head (the enclosing type’s table, at child positions) and each directive application’s contribution: @routine contributes its result table as a node, @reference contributes hops. The last node must be the field’s @table type (or the routine’s result table when the routine is last). Order is load-bearing; see the order contract on the @reference page.

Every position falls out of that one rule. In these examples (from the sakila example schema) recentFilms sits on Actor and the other two fields sit on Film:

# Routine then hops: the routine supplies the rows, a hop lands on the film table.
recentFilms(minLength: Int!): [Film!]
    @routine(name: "films_for_actor",
             argMapping: "pMinLength: minLength", columnMapping: "pActorId: actor_id")
    @reference(path: [{table: "film"}])
    @defaultOrder(primaryKey: true)

# Hops then routine: the hop reaches the junction first, so columnMapping binds
# against film_actor (the previous table), not the enclosing Film row.
castFilms(minLength: Int!): [ActorFilm!]
    @reference(path: [{table: "film_actor"}])
    @routine(name: "films_for_actor",
             argMapping: "pMinLength: minLength", columnMapping: "pActorId: actor_id")
    @defaultOrder(fields: [{name: "film_id"}])

# Sandwich: hops in, routine, hop back out to a catalog terminus.
castRecentFilms(minLength: Int!): [Film!]
    @reference(path: [{table: "film_actor"}])
    @routine(name: "films_for_actor",
             argMapping: "pMinLength: minLength", columnMapping: "pActorId: actor_id")
    @reference(path: [{table: "film"}])
    @defaultOrder(primaryKey: true)

A hop out of a routine result has no foreign key to ride (a function result carries no FK metadata), so a {table:} element there keys by name matching: the target table’s primary-key columns must be exposed, by SQL name, among the routine’s result columns. films_for_actor exposes film_id, film’s PK, so the hop above resolves; a condition: element is the escape hatch when names do not line up.

The single name-matched hop is also implicit. A @table-bound child of a routine-result parent needs no @reference at all, because the only thing the directive would supply is the target table name, and the child’s own return type already carries it:

type Brukertilgang @table(name: "mine_tilganger") {   # mine_tilganger is a table-valued function
    tilgangsrolle: Tilgangsrolle                      # Tilgangsrolle is @table(name: "rolle")
}

An explicit @reference(path: [{table: "rolle"}]) stays legal and resolves identically; writing it is optional, not wrong. What has no implicit spelling is a multi-hop path out of a routine result, or a join the name-match cannot key: both still need the directive, and the build error names the missing key column and points at the condition: element rather than at foreign keys.

Binding a parameter to a node id’s key column

An ID field annotated @nodeId carries a base64-encoded node identity on the wire, not the key it encodes. A routine IN parameter almost always wants the key. Open the node id with the key column you want and the binding decodes it for you:

input OpprettFeideApplikasjonInput {
    navn:            String!
    organisasjonId:  ID! @nodeId(typeName: "Organisasjon")
    serviceId:       String!
}

type Mutation {
    opprettFeideApplikasjon(input: OpprettFeideApplikasjonInput!): OpprettFeideApplikasjonPayload
        @routine(
            name:       "opprett_feide_applikasjon"
            argMapping: "pNavn: input.navn, pOrganisasjonskode: input.organisasjonId.organisasjonskode, pServiceId: input.serviceId"
        )
}

organisasjonskode is not a field of any SDL type. It is a key column of the node type the @nodeId names, so the segment means "decode this node id and project that column out of the decoded key". The generated fetcher decodes the id once into `Organisasjon’s own record and reads the named column off it, before the write transaction opens.

This is the same rule the dot always followed: a dot opens the thing at that position, and what it opens into depends on what the thing is. It is documented in full under Binding a parameter to a nested input field, and it works the same way at every directive that accepts an argMapping.

Which columns are openable comes from the node type, not from the routine: whatever @node(keyColumns:) declares, or the table’s key metadata where it does not. Matching is case-insensitive, so the SQL spelling and the generated spelling are one answer.

Where the node type’s key is a single column you may leave the leaf closed: pOrganisasjonskode: input.organisasjonId names that column, there being no other it could name, and the parameter receives it exactly as the spelled-out form does. Naming the column is then a matter of saying out loud what the build would infer, which is worth doing where the reader benefits.

Three things are build errors rather than surprises at runtime:

  • Binding a @nodeId whose key is more than one column without saying which. One binding carries one value, and nothing in pOrganisasjonskode: input.organisasjonId says which of the two it is. The build stops, states the count, and lists the columns you could have named. It says the same where the node type resolves no key columns at all, the remedy there being @node(keyColumns:) on that type.

  • @nodeId without typeName: at this position. A bare @nodeId infers its target from the containing table, and a routine parameter has no containing table, so there is nothing to infer from. Name the type.

  • A key column the parameter cannot take. Projecting an Integer column into a String parameter is a build error naming the column, its type and the parameter’s, rather than a compile error in generated code you did not write. Bind a parameter of the column’s own type, or project a column the parameter can take. The check needs both types: where the parameter’s type cannot be resolved, a routine whose call surface was not captured or a parameter declared int rather than Integer, the build lets it through and your compiler is the backstop.

Writes on Mutation

On a Mutation field, the routine call is the write, and it commits before the follow-up query runs. @routine names a VOLATILE set-returning function, and two return shapes are admitted: with @reference, the field returns the table the chain reaches; without it, the field returns a payload whose data field declares its own path.

The chain form

The chain form is @routine plus at least one @reference hop landing the field’s @table type.

type Rental @table(name: "rental") {
    rentalId: Int! @field(name: "rental_id")
}

type Mutation {
    rentFilm(inventoryId: Int!, customerId: Int!): [Rental!]!
        @routine(
            name:       "rent_film"
            argMapping: "pInventoryId: inventoryId, pCustomerId: customerId"
        )
        @reference(path: [{table: "rental"}])
}

The generated fetcher is two statements with a transaction boundary between them. Statement 1 executes the routine inside the per-mutation-field transaction and captures only the columns the first hop’s key needs from the routine’s result rows (here rental_id); the transaction commits when that statement returns. Statement 2 is a read-only SELECT anchored on the first hop’s table, keyed by the captured values, with any remaining hops joined as in a read chain, projecting the terminus type. The routine never appears in statement 2: re-invoking it would re-execute the write. The field’s return therefore always binds to the post-commit re-read, never to the routine’s own rows, so the response observes committed state without exception.

An SQL error from the routine rolls the transaction back and surfaces on the mutation field like any DML error; a read error in statement 2 cannot undo the already-committed write (the same caveat the DML mutations carry).

The payload carrier

Without @reference, the field returns a payload carrier: a plain Object wrapping exactly one @table-element data field, plus an optional errors-shaped field (the same carrier mold the DML and @service mutations admit).

type RentFilmPayload {
    rental: Rental              # the data field — must be nullable
    errors: [RentFilmError]     # optional typed errors channel
}

type Mutation {
    rentFilmPayload(inventoryId: Int!, customerId: Int!): RentFilmPayload
        @routine(
            name:       "rent_film"
            argMapping: "pInventoryId: inventoryId, pCustomerId: customerId"
        )
}

When the field takes a wrapper input instead of flat arguments, argMapping walks into it. This is the shape a Relay-style mutation has, and it needs no SDL restructuring:

input RentFilmInput {
    inventoryId: Int!
    customerId:  Int!
}

type Mutation {
    rentFilmPayloadNested(input: RentFilmInput!): RentFilmPayload
        @routine(
            name:       "rent_film"
            argMapping: "pInventoryId: input.inventoryId, pCustomerId: input.customerId"
        )
}

The two statements become: the routine call and the key capture commit as statement 1; the data field’s SELECT is the post-commit re-read, owned by the payload field rather than the mutation fetcher. The data field’s path is implicit — the single name-matched hop to its own element’s table: the element table’s primary-key columns must be exposed, by SQL name, among the routine’s result columns (rent_film exposes rental_id, rental’s PK). When a key column is not exposed, the build error names it with a candidate hint; the fix is exposing the column from the routine — a condition: join is no escape hatch here, because a condition has no key tuple to capture.

The errors-shaped field, when present, is the typed error channel: an exception the @error handlers match renders as { rental: null, errors: [<typed error>] } with no field error (see the errors-channel how-to).

The data field must be nullable, and the reason is a second, distinct null-data outcome: the post-commit re-read runs at read time under the caller’s identity, so a read policy (row-level security) can legitimately hide the row the routine just committed. That renders { rental: null, errors: null } as a success, with nothing raised and nothing dispatched; a non-null data field would let non-null propagation null the whole payload and destroy the errors list, so it is rejected at build time.

Two spellings are deliberately not admitted. @routine + @reference on the mutation field with a carrier return is rejected as a directive conflict: the reference path’s seat is the payload’s data field, the field whose rows it fetches, so drop @reference from the mutation field. And @reference on the data field itself (the explicit path declaration, single- and multi-hop alike) is deferred with a pointer to its follow-up; the implicit single name-matched hop is the shipped shape.

Deferred write shapes

Deferred write shapes, each reported with a pointer to the follow-up item: true procedures and scalar or void routines (jOOQ exposes them through a different call surface than table-valued functions), and the hop-less Mutation @routine whose return provides no re-read anchor — void, scalar, OUT-parameter binding, or a non-carrier Object. A chain whose first hop joins by condition: or carries a per-hop condition is likewise deferred: its predicate references the routine’s result, which must not appear in the follow-up query, so no re-read anchor can be derived.

Fetch forms and @splitQuery

A routine-backed child rides the inline correlated multiset by default: one SQL statement, the lateral call re-evaluated per parent row. @splitQuery moves the field to the batched keyed re-query instead: the parent rows' bound columns are collected as DataLoader keys, sent as a VALUES table in one batch query, and the lateral call reads them off that table, so the routine still runs once per distinct input inside a single statement.

Because the batch is keyed by the routine’s column-bound inputs, @splitQuery on an uncorrelated routine child (no columnMapping) is rejected: there is nothing to key the batch on, and every parent would receive identical rows. Drop @splitQuery or bind a parent column.

Constraints

  • Where the chain ends on the routine, the return type needs no @table: the routine’s result binds it. That covers a bare @routine field and a chain whose @reference applications all come before the routine, since hops written before it move where the chain starts and never where it ends. Writing @table(name:) naming the same function stays legal and means the same thing, so existing schemas keep working; what it buys is nothing, and what it costs is that the two names must agree, that the type reads as a stored table, and that the type is welded to one routine. A return the binding cannot attach to (a scalar, an interface, a union) is rejected with @routine could not bind the return type …​ to its result table.

  • Where the chain hops after the routine, the return type must be @table-bound and the chain’s last node must be that table. The landing there is a catalog table, which is a table type in the schema in its own right, so its @table is that type’s own binding rather than a second spelling of the routine’s name. A non-@table return is rejected with @routine with @reference requires a @table-annotated return type; a mismatch between the return and the landing is rejected with @routine could not be resolved — the field’s @table type …​ does not match the routine’s result table …​.

  • Without @reference on a Mutation field, the return may instead be a payload carrier (exactly one nullable @table-element data field plus an optional errors-shaped field). A carrier is not bound to the routine’s result: its rows are what the data field re-reads post-commit, not what the field returns. The carrier + @reference combination is rejected as a directive conflict, and @reference on the carrier’s data field is deferred (see Writes on Mutation).

  • name: must resolve to a table-valued function in the jOOQ catalog. A name that resolves to nothing is rejected with @routine could not be resolved — no table-valued function named …​; a name that resolves to a plain table or view is rejected with …​ resolves to a table or view, not a table-valued function. A name that exists as a database routine but is not table-valued (a procedure, or a scalar or void function) is reported as deferred with a pointer to the follow-up item carrying that call surface; only a genuinely absent name gets the unknown-name rejection.

  • On a root field composing a chain (Query and Mutation alike), @routine must be the first directive application: a root chain has no implicit head, so the routine supplies it. columnMapping is likewise illegal at the head of a root chain (there is no previous table to bind from); bind root routine parameters from GraphQL arguments.

  • argMapping: / columnMapping: parameter names are the jOOQ-generated (camelCased) names. Naming a parameter the routine does not declare is a build error, as is leaving a parameter with nothing bound to it. On the right-hand side, a path whose head is not an argument of the field, or whose later segment names nothing the value at that depth opens into, is a build error listing the candidates; a columnMapping naming a column the previous table does not carry likewise lists that table’s columns. A columnMapping right-hand side has no path form, since a column has no nested fields.

  • An argMapping path must land on a scalar or enum leaf, or on a key column projected out of a node id (see Binding a parameter to a node id’s key column). Binding a routine parameter to a whole input object is a build error naming the input type: a routine IN parameter takes one value, so bind a scalar field inside it. Enum-valued and converted leaves are reported as deferred; the routine call emitter reads argument values directly today. A column-bound parameter’s Java type must match the routine parameter’s, and so must a key column projected out of a node id: a mismatch there is a build error naming both types, resolvable only where the routine’s call surface was captured and the parameter is a reference type. Resolving the parameter names off the generated Routines method requires the consumer to compile their jOOQ sources with -parameters.

  • A @nodeId-carrying leaf’s @nodeId must name typeName: explicitly, a routine parameter having no containing table to infer the node type from. Whether the leaf must be opened with a key column follows from the node type’s key: one column is inferred and the leaf may stay closed, and more than one is a build error stating the count and listing the columns, one binding carrying one value. The parameter receives a key column’s own value either way, never the encoded id. Naming a column the node type does not resolve is a build error suggesting the near miss, naming one whose Java type the parameter cannot take is a build error stating both types, and opening an ID that carries no @nodeId at all is a build error too: what a dot opens is a node id, and an ID that is not one has nothing to open. Exactly one segment may follow a node id, a node id opening into one key column, and opening a list of node ids is reported as deferred: it names the list of that key column across the decoded ids, which parameter binding does not emit yet.

  • One routine node per chain: chains with more than one @routine application do not generate yet and are reported as deferred.

  • @orderBy, @condition and @asConnection compose normally on a routine-backed read (see The read surface). @lookupKey composition is reported as deferred. On a Mutation field, @orderBy and @condition are reported as deferred (the write’s result shape has no filter or order surface to compose onto) and a Connection return is rejected; at a child position @asConnection is reported as deferred.

See also

  • @reference contributes the FK hops of the same chain and documents the directive-order contract.

  • @table is the plain catalog-driven counterpart for a field always backed by the same table.

  • @service is the alternative when the developer supplies the entire fetcher rather than a routine-backed table.

← Reference index