PG Persistence

PG Persistence makes a Viaduct GraphQL schema the starting point for PostgreSQL persistence. Wire up DbClient to PostgreSQL's pg_graphql interface, define your persistent types and relationships in GraphQL, and delegate reads and writes from your resolvers. The library handles the routine database mapping and data access from there.

At build time, the plugin derives tables, columns, and relationships from the schema, applies schema-adjacent persistence policy, and generates PostgreSQL SQL and pg_graphql metadata. At runtime, DbClient turns resolver selections into database GraphQL requests and converts results into Viaduct's generated types. Its mutation API accepts converted Viaduct inputs and builds the declared payload from the returned node IDs. You supply the connection configuration, apply the generated SQL through your migration process, and write the resolver delegation, authorization, and business logic.

flowchart LR
    subgraph viaduct["Viaduct application"]
        resolvers["Resolvers"]
        client["PG Persistence<br/>DbClient"]
        resolvers <--> client
    end
    subgraph postgres["PostgreSQL"]
        pg_graphql["pg_graphql extension"]
        tables[("Application tables")]
        pg_graphql <-->|SQL| tables
    end
    client <-->|GraphQL| pg_graphql

PG Persistence connects Viaduct resolvers to the database GraphQL API. The pg_graphql extension runs inside PostgreSQL and executes queries and mutations against application tables.

For an explanation of the generated database model and runtime behavior, see ARCHITECTURE.md. This guide is also available on Slate (Airbnb access required).

Install

For a complete application, follow the OAuth provider sample walkthrough. It explains how the sample was built, how to define its GraphQL schema, generate fresh-install SQL and Liquibase review files, and start it locally. The sample has separate database and computed Viaduct tenants and generates its database from SDL and schema-adjacent persistence policy.

Apply PG Persistence to each Viaduct module that owns database nodes. The module must already apply the Viaduct module plugin and its Kotlin/KSP setup. For a single-project application:

// settings.gradle.kts
pluginManagement {
    repositories {
        maven("https://central.sonatype.com/repository/maven-snapshots/")
        gradlePluginPortal()
    }
}

dependencyResolutionManagement {
    repositories {
        maven("https://central.sonatype.com/repository/maven-snapshots/")
        mavenCentral()
    }
}

// build.gradle.kts
plugins {
    id("com.airbnb.viaduct.application-gradle-plugin") version "<viaduct-version>"
    id("com.airbnb.viaduct.module-gradle-plugin") version "<viaduct-version>"
    id("dev.viaduct.pg-persistence") version "0.1.0-viaduct3-SNAPSHOT"
}

dependencies {
    implementation("dev.viaduct.persistence:runtime:0.1.0-viaduct3-SNAPSHOT")
}

The runtime is a Maven dependency of the application. The snapshot repository above provides the current 0.1.0-viaduct3-SNAPSHOT; released versions are available from Maven Central.

This compatibility branch targets Viaduct 3.0.0-rc.2, Kotlin 2.3.21, and KSP 2.3.7, with persistence version 0.1.0-viaduct3-SNAPSHOT. Select that version for both the Gradle plugin and every persistence module. Applications should pin the timestamped snapshot build after publication.

Viaduct 3 removed public selection metadata methods; the runtime now obtains root-type metadata through InternalSelectionSet and engine selections through EngineSelectionSetProvider. These internal APIs require matching Viaduct and persistence builds. The older 0.1.0-SNAPSHOT targets Viaduct 2.1 and must not be combined with Viaduct 3. The same adapter has also passed local tests against Treehouse master ec7fad1 (a Viaduct 4 source build). VIADUCT_MAVEN_REPOSITORY can select an isolated source-built Viaduct Maven repository for exact-master compatibility checks.

Plugin tests put the matching Kotlin standard library and reflection runtime ahead of the Kotlin 2.0 jars bundled with Gradle 8's gradleApi() dependency. Otherwise, Kotlin 2.3 coroutine debug metadata fails during exception recovery with assertions enabled and can stall a test worker. The existing retry regressions exercise that path with assertions enabled.

For a multi-project application, apply PG Persistence to the database-owning modules, not just the application project. Each module supplies its schema to the application's normal assembleViaductCentralSchema task.

Define Persistent Types

An object that implements Viaduct's Node interface is persistent by default. Implement its node resolver as a concrete Kotlin class annotated with @Resolver and extending NodeResolvers.<Type>. When the node has no SDL @resolver declaration, PG Persistence supplies @resolver(isSelective: true) automatically. You can omit the node's SDL annotation and isSelective argument; the Kotlin @Resolver annotation remains required. Viaduct then provides ctx.ownedSelections() for request-dependent database reads. Overriding batchResolve also supplies isBatching: true automatically.

The plugin discovers annotated node implementations in the module's Kotlin main sources before resolver-base generation. It writes their declarations to build/generated/viaduct-persistence-schema-contributions/pg-persistence.graphqls and registers them through Viaduct's ViaductSchemaContributions extension. Source schema files are unchanged. The plugin does not generate resolver implementations or add declarations for unimplemented or unannotated classes.

An explicit SDL @resolver declaration takes precedence over these defaults. For persistent nodes, explicitly declaring @resolver without isSelective: true, or with isSelective: false, still fails validation. Missing selective metadata after generated contributions are applied also fails validation. Types with types.<Type>.excluded: true in persistence policy are not subject to this check.

A modern @connection field with arguments reachable from a persistent node must still declare its own SDL @resolver, including when nested inside a stored object. Each field invocation then receives its own arguments, so aliases can request different pages. Automatic node declarations do not change field resolver requirements.

Object fields, lists, and connections describe relationships. A connection between persistent types remains a stored relationship when its field declares a resolver:

type Group implements Node {
  id: ID!
  name: String
  members: [GroupMember]
}

type GroupMember implements Node {
  id: ID!
  group: Group
  person: Person
}

type Person implements Node {
  id: ID!
  displayName: String
}

An ID field with @idOf stores a reference without requiring an object field:

type Person implements Node {
  id: ID!
  groupId: ID @idOf(type: "Group")
}

The scalar ID field may also accompany a matching object field. These fields use the same foreign key column:

type Person implements Node {
  id: ID!
  group: Group
  groupId: ID @idOf(type: "Group")
}

The @idOf target must match the object field's type.

Supported stored fields are:

GraphQL field	PostgreSQL representation
`ID`	UUID
`String`	Text
`Date`	Date
`DateTime`	Timestamp with time zone
`Time`	Time
`Boolean`	Boolean
`Byte`, `Short`, `Int`, `Long`	Matching integer type
`Float`	Double precision
`BigDecimal`, `BigInteger`	Numeric
`JSON`	JSONB
Enum	Text
Persistent object, list, or connection	Relationship
Union or interface of persistent nodes	Reference to one concrete node; lists and connections may mix types

List fields are supported only when their elements are persistent Node types. Nested lists and lists of scalar, enum, or arbitrary non-persistent object values are not supported. Resolver-backed fields that are not relationships between persistent types are not stored.

Unions and interfaces are supported in reads, mutation payloads, and stored relationships. Every concrete target of a stored relationship must be an included persistent Node. See Using unions and interfaces for selection, mutation, and mixed collection examples.

SQL functions

Use the library's PgGraphqlClient for application-owned SQL functions exposed by pg_graphql. For a function returning JSON or JSONB, executeJson decodes pg_graphql's string-encoded JSON result with a Kotlin serializer. It also handles request-variable encoding:

import dev.viaduct.persistence.runtime.db.executeJson

val users = client.executeJson(
    document = "query { getAllUsersJson }",
    responseKey = "getAllUsersJson",
    deserializer = ListSerializer(UserRecord.serializer()),
    headers = authenticatedHeaders,
)

For other GraphQL return types, use execute. Its PgGraphqlObject overload accepts ordinary field values as variables. SQL-function definitions and their authorization checks belong to the application; no code for sending HTTP requests or reading the GraphQL response's data and errors needs to be copied into it.

Configure Persistence Policy

Optional persistence policy belongs in src/main/viaduct/pg-persistence.yaml:

types:
  ExternalProfile:
    excluded: true
  Group:
    semanticNotNull: true
    fields:
      members:
        relationship:
          storage: targetForeignKey
  Person:
    indexes:
      - [region]
    unique:
      - [displayName]
    fields:
      displayName:
        semanticNotNull: true
  GroupMember:
    unique:
      - [person, group]
  ExternalGroup:
    fields:
      discordServerRoles:
        relationship:
          inverseField: server

Optional module-level databaseSchema: orders places that module's tables in an explicit PostgreSQL schema in the same database. Omission keeps public placement. Referenced types and extensions retain their defining module's placement, and association tables follow their canonical owner. See tenant database schemas for assembly, visibility, and migration requirements.
types.<Type>.excluded excludes an occasional Node. For a large externally backed schema, use a separate Viaduct tenant module without this plugin.
types.<Type>.unique lists unique keys using GraphQL field names. Each inner list is one key: [displayName] is a single-field key and [person, group] is a composite key. Add more inner lists for independent keys.
types.<Type>.indexes lists nonunique secondary indexes using GraphQL field names, for example [[region], [region, displayName]]. Composite field order is preserved; repeated identical definitions are deduplicated.
Type- or field-level semanticNotNull: true requires stored values while leaving public GraphQL field nullability intact.
Field relationship storage: targetForeignKey stores a collection relationship as a foreign key on the contained node's table. It must identify a persistent collection and cannot be used when the connection has stored edge fields.
Field relationship inverseField resolves a collection whose contained node type has more than one object field referring back to the collection's declaring type. The value names the exact object field on the contained type.

Unique-key fields must be stored scalars or to-one relationships, including fields declared in type extensions. Collections, resolver-backed fields, unknown fields, id, internalId, and empty or repeated field names within a key are rejected. Equivalent keys are normalized and deduplicated. Nullable fields follow PostgreSQL's normal unique-constraint behavior: null values are distinct. Constraints participate in the Hibernate snapshot/diff workflow; adding one to an existing database requires reviewing existing duplicates and the generated migration.

Secondary indexes accept the same stored fields as unique keys and permit duplicate and null values. They are included in fresh-schema DDL and Hibernate/Liquibase snapshots and diffs. Review and apply the generated migration before using an index on an existing database. Property lookups produce ordinary filters; PostgreSQL chooses the available index according to query selectivity and statistics.

For example, if DiscordServerRoleGroup has both externalGroup: ExternalGroup and server: ExternalGroup, the schema alone cannot determine which field stores ExternalGroup.discordServerRoles. This entry selects server:

types:
  ExternalGroup:
    fields:
      discordServerRoles:
        relationship:
          inverseField: server

Unknown keys, types, fields, invalid values, contradictory policies, and entries that have no effect fail generation. The filename and location are conventional and currently cannot be overridden. The former persistence.yaml filename and feature-first shape fail with migration instructions.

Place module-owned data in PostgreSQL schemas

In each owning module's src/main/viaduct/pg-persistence.yaml, opt in explicitly:

databaseSchema: orders

Apply the plugin to the owning modules and the assembling application. Modules publish tracked ownership and policy metadata alongside their Viaduct schema partitions; central generation aggregates it without compiling resolvers. Only original object definitions claim ownership. References and extensions retain the defining module's placement, and association tables follow their canonical relationship owner. Conflicting ownership, placement or semantic policies fail generation. When module placement metadata is present, missing ownership for a persisted original type also fails generation. Modules that keep public publish explicit ownership metadata through the same plugin. Existing public-only consumers retain their default behavior. The assembling application records module participation separately from placement metadata. Types defined by external modules without the plugin remain outside generated storage; they need no per-type exclusions. Missing artifacts from participating persistence modules still fail generation.

Omitting the setting retains public; several modules may deliberately share a schema. Names must match [a-z_][a-z0-9_]{0,62} and cannot start with pg_. Schema names are never inferred from tenant names or packages. Public GraphQL names remain unchanged and must be distinct across visible schemas, even for same-named physical tables.

Placement survives semantic-model serialization, qualified Hibernate mappings, SQL, foreign keys, and Liquibase snapshots/diffs. It does not grant access, enable RLS, configure runtime visibility, move existing data, or create separate databases or roles. See Tenant PostgreSQL schemas for the complete contract and local verification evidence.

Generate and Apply Database Changes

./gradlew buildViaductEffectiveModel

Review the files under:

build/generated/viaduct-effective-model/META-INF/
  postgresql-migration.sql
  postgresql-prerequisites.sql
  postgresql-repeatable.sql
  pg-graphql-metadata.sql
  pg-graphql.sql
  hibernate-create.sql
  schema-create.sql

Adapt postgresql-migration.sql into the application's migration system. Apply pg-graphql-metadata.sql after the relational schema exists. pg-graphql.sql is a convenience bundle for a fresh schema, not a repeatable production migration. The plugin never applies database changes automatically.

schema-create.sql includes PostgreSQL prerequisites, all Hibernate table/constraint DDL, and the PostgreSQL and pg_graphql overlays in installation order. Use it only for a fresh application database with pg_graphql installed; it is not an upgrade or repeatable migration. GraphQL strings use PostgreSQL text, and scalar lists use native PostgreSQL array columns. Provider installation, roles, and application-specific authorization policies remain separate from the generated application structure.

Compare with an Existing Database

viaductPgPersistence {
    schemaDiffUrl.set(providers.environmentVariable("SCHEMA_DIFF_DATABASE_URL"))
    schemaDiffUser.set(providers.environmentVariable("SCHEMA_DIFF_DATABASE_USER"))
    schemaDiffPassword.set(providers.environmentVariable("SCHEMA_DIFF_DATABASE_PASSWORD"))
}

./gradlew hibernateSchemaDiff

The conservative diff uses a narrow SQL allowlist: new tables, non-unique indexes, nullable columns of recognized built-in types without defaults, and non-empty metadata comments. Type conversions, tightened nullability, new constraints, and unfamiliar SQL go to the review file. Review both outputs; “conservative” does not guarantee that a migration is safe for every application's data or traffic.

Generated foreign-key SQL checks the complete existing definition, including source and target columns, update/delete actions, match mode, deferrability, and validation. An exact match is left in place. A conflicting definition fails with instructions to apply a reviewed migration first; the generated SQL does not silently replace it.

Review build/schema-diff/hibernate-review.postgresql.sql and build/schema-diff/hibernate-destructive-review.postgresql.sql. Renames, removals, data backfills, and database-owned constraints remain manual migration work.

Move existing tables between schemas

Changing databaseSchema is not a data migration. PostgreSQL diff generation stops when a destination table is missing but a same-named table exists in another non-system schema, rather than treating the destination as a new empty table. The guard is conservative and cannot infer whether the same-named table is related.

Review and apply an explicit migration through the application's migration runner:

CREATE SCHEMA IF NOT EXISTS orders;
ALTER TABLE public.purchase SET SCHEMA orders;

Review text-bodied function references, search paths, grants, RLS, API exposure and rollout compatibility as part of that migration. Regenerate the diff afterward. The plugin does not author or apply table moves automatically. Qualified snapshot identities preserve same-named tables in different schemas independently.

Configure `DbClient`

val dbClient = DbClient(
    httpClient = httpClient,
    endpoint = postgresGraphqlEndpoint,
    requestHeaders = DbRequestHeaders { context ->
        mapOf("Authorization" to "Bearer ${accessTokenFor(context)}")
    },
)

The endpoint and headers depend on the service exposing pg_graphql. Supabase normally uses https://<project>.supabase.co/graphql/v1 and expects both Authorization and apikey headers.

Use JDBC instead of HTTP

Add the optional JDBC artifact and a PostgreSQL driver:

dependencies {
    implementation("dev.viaduct.persistence:jdbc:0.1.0-SNAPSHOT")
    runtimeOnly("org.postgresql:postgresql:42.7.5")
}

Supply your application's DataSource:

val executor = JdbcPgGraphqlExecutor(dataSource)
val dbClient = DbClient(executor)

The same DbClient read, typed mutation, and transaction APIs work over either transport. With a DataSource, each request executes pg_graphql in a JDBC transaction and closes its connection afterward. For a connection pool, closing the connection handle returns it to the pool; the application closes the pool at shutdown. JDBC occupies the calling thread while waiting for the database. For example, use Kotlin's Dispatchers.IO from a selective Group node resolver to provide threads for blocking I/O:

import kotlinx.coroutines.Dispatchers
import kotlinx.coroutines.withContext

val group = withContext(Dispatchers.IO) {
    dbClient.fetchByInternalId(ctx, "groupCollection", ctx.id.internalID)
}

HTTP headers are not automatically applied to JDBC. See JDBC transport configuration for caller-owned transactions, request setup, and failure handling.

Expose the authorized schema set for a transaction

Supply the complete authorized schema set for the transaction, rather than switching to the currently resolving tenant's schema:

val executor = JdbcPgGraphqlExecutor(
    dataSource,
    JdbcSchemaVisibility(listOf("public", "people", "orders")),
)
val dbClient = DbClient(executor)

JdbcSchemaVisibility copies, deduplicates, sorts and quotes identifiers, then binds a transaction-local search path. It requires an open transaction, does not add public implicitly, and does not commit, roll back or close the connection. Settings disappear when the transaction ends. Use restricted database credentials: visibility does not grant schema/table privileges or replace RLS. Headers are rejected by default; an explicit trusted identity callback can establish the role/JWT before schema setup.

For HTTP, the gateway must establish equivalent schema visibility and caller identity before graphql.resolve, using trusted pre-request setup or an invoker-rights wrapper. A client-supplied schema header is not authorization.

Cross-schema reads and mutations use the existing transaction contracts within one database. Live regressions cover relationships, commit/rollback, retry/recovery, DBOS replay, cancellation and pooled-connection isolation. Separate REST writes remain separate transactions. See Tenant PostgreSQL schemas for the fixtures and their verification limits.

JDBC timeouts

JDBC GraphQL statements have a 30-second query timeout and a 30-second network timeout by default. Choose positive finite limits with JdbcTimeouts:

val executor = JdbcPgGraphqlExecutor(
    dataSource,
    timeouts = JdbcTimeouts(querySeconds = 10, networkMillis = 10_000),
)

Existing tighter driver limits are preserved. Caller-owned connections regain their previous network timeout after execution. DBOS statements and connections also use 30-second maximums, including saved-result lookup and recording calls.

The application must configure finite pool acquisition, driver connection/socket, and PostgreSQL statement/lock timeouts. These bounds also cover setup SQL and transaction-control calls outside the GraphQL statement. A generic DataSource does not expose a pool acquisition deadline. For example, use a finite HikariCP connectionTimeout, PostgreSQL driver connectTimeout and socketTimeout, and PostgreSQL statement_timeout and lock_timeout settings.

The DBOS retry deadline limits starting further attempts; it is not an end-to-end execution deadline. Cancellation or timeout is not proof of rollback: a commit may already have succeeded.

Resolve Persistent Nodes

For the Group type above, add this mutation to src/main/viaduct/schema/Group.graphqls:

input AddGroupInput {
  name: String!
}

type AddGroupPayload {
  group: Group
}

extend type Mutation {
  addGroup(input: AddGroupInput!): AddGroupPayload @resolver
}

Here is the complete src/main/kotlin/com/example/groups/GroupResolvers.kt file for a module whose configured package is com.example.groups, using the default viaduct.api.grts package. The application's resolver factory supplies the configured DbClient to both constructors.

package com.example.groups

import com.example.groups.resolverbases.MutationResolvers
import com.example.groups.resolverbases.NodeResolvers
import dev.viaduct.persistence.runtime.db.DbClient
import dev.viaduct.persistence.runtime.db.toPgGraphqlInsert
import viaduct.api.grts.AddGroupPayload
import viaduct.api.grts.Group
import viaduct.api.resolver.Resolver

@Resolver
class GroupNodeResolver(
    private val dbClient: DbClient,
) : NodeResolvers.Group() {
    override suspend fun resolve(ctx: Context): Group =
        dbClient.fetchByInternalId(
            ctx = ctx,
            collectionField = "groupCollection",
            id = ctx.id.internalID,
        )
}

@Resolver
class AddGroupResolver(
    private val dbClient: DbClient,
) : MutationResolvers.AddGroup() {
    override suspend fun resolve(ctx: Context): AddGroupPayload {
        val insert = ctx.arguments.input.toPgGraphqlInsert()
        return dbClient.entity<Group>().insert(ctx, insert)
    }
}

The mutation inserts the input and builds a payload containing a reference to the new group. Selecting the group's fields invokes GroupNodeResolver, which fetches the requested data. NodeResolvers, MutationResolvers, and the result types are generated from the schema.

The node helpers derive selections from the generated selective node context. Callers do not pass either selection set. Owned selections determine scalar database reads; requested selections identify persisted relationships returned as Viaduct node references. Modern connection fields remain with their own connection resolvers, including aliases with independent paging arguments.

Other common operations are:

fetch returns a generated Viaduct result type for an explicit DbRead.
fetchResult returns a DbResult<T> containing a generated Viaduct result and any GraphQL errors returned by pg_graphql.
fetchJsonResult returns the equivalent DbResult<JsonObject>.
fetchNode returns a generated node result and attaches requested node references.
fetchByInternalId returns one generated node result selected by its provider UUID.
fetchByInternalIds returns generated node results keyed by provider UUID and throws if any node is missing or erroneous.
fetchByInternalIdsResult(contexts, collectionField) returns independently successful or erroneous FieldValue entries keyed by their original selective node contexts. It groups compatible owned and requested selections automatically.
fetchByInternalIdsResult(ctx, collectionField, ids) is the lower-level form for UUIDs that are already known to share one compatible owned selection.
fetchConnection returns the generated Viaduct connection from a ConnectionFieldExecutionContext; see Resolve Connections.

Return Batch Node Results

For a batch node resolver, pass all contexts directly. fetchByInternalIdsResult groups contexts by their owned-selection document and field-argument variable values, fetches compatible contexts together, and returns one Viaduct FieldValue per original context. Request headers still come from one representative context because a Viaduct node batch is scoped to one request.

return dbClient.fetchByInternalIdsResult(
    contexts = contexts,
    collectionField = "groupCollection",
)

Found nodes remain successful when another UUID is absent. A missing row becomes an error value with code MISSING_ROW, and a pg_graphql error associated with one returned edge becomes an error value for that node. Viaduct uses the original resolver context to place the error at the application GraphQL response path. Errors that cannot be associated with an edge are thrown rather than discarded. Until Viaduct provides a supported way to put an error value on an individual GRT field, a field error fails its node while preserving the other nodes in the batch.

When a null node has no UUID, identifiable successful nodes are preserved and unresolved IDs are read individually. The client does not infer node identity from provider order. Recovery reads can add requests to a batch.

Single and batch reads share semantic non-null validation. An unexplained null in a required stored field produces SEMANTIC_NON_NULL_VIOLATION; a null already explained by an upstream error keeps that error. A batch violation fails its node while preserving successful nodes. Cancellation and fatal failures propagate rather than becoming individual node error values.

Resolve Connections

Paging uses Viaduct's modern OSS @connection and @edge types. DbClient.fetchConnection accepts the generated ConnectionFieldExecutionContext and returns the generated connection directly. There is no separate persistence page, edge, PageInfo, request, or cursor type in the public API.

For a root collection of the Group nodes above, declare:

type GroupEdge @edge {
  cursor: String!
  node: Group!
}

type GroupConnection @connection {
  edges: [GroupEdge!]!
  pageInfo: PageInfo!
}

extend type Query {
  groups(first: Int, after: String, last: Int, before: String): GroupConnection!
    @resolver(isSelective: true)
}

PageInfo is supplied by Viaduct. Using the same module package as the node resolver example:

package com.example.groups

import com.example.groups.resolverbases.QueryResolvers
import dev.viaduct.persistence.runtime.db.DbClient
import dev.viaduct.persistence.runtime.db.DbRead
import dev.viaduct.persistence.runtime.db.DbRoot
import viaduct.api.grts.GroupConnection
import viaduct.api.resolver.Resolver
import viaduct.apiannotations.ExperimentalApi

@OptIn(ExperimentalApi::class)
@Resolver
class GroupsResolver(private val dbClient: DbClient) : QueryResolvers.Groups() {
    override suspend fun resolve(ctx: Context): GroupConnection =
        dbClient.fetchConnection(
            ctx = ctx,
            dbRead = DbRead(DbRoot("groupCollection")),
            selections = ctx.selections(),
        )
}

Paging arguments come only from ctx.arguments. The runtime calls Viaduct's validate(), requiresTotalCountForOffsetLimit(), and toOffsetLimit(), including its default page size of 20 and direction rules. Viaduct rejects nonpositive page sizes, mixed forward/backward arguments, invalid cursors, and overflowing bounds. The adapter creates each edge cursor with OffsetCursor.fromOffset() and calls the generated connection builder's fromEdges(). The builder sets pageInfo.startCursor and endCursor from the returned edges; both are null for an empty page. hasPreviousPage is true when the resolved offset is greater than zero, and hasNextPage reflects whether matching rows remain after the slice.

For a nested connection, also pass the field's CompositeField reflection descriptor as field and a DbRead identifying one filtered parent with singleViaFilteredCollection = true. Its result type must match the connection selections. The optional filter: PgGraphqlFilter and orderBy: List<PgGraphqlOrder> apply to the connection rows; the root read identifies the parent. Custom edge fields and concrete node references in union/interface connections are preserved.

The database adapter requests pg_graphql offset slices and continues when a provider row cap truncates the requested slice. When Viaduct needs the total count for backward paging without before, the adapter first counts cursor metadata across all matching provider pages, then passes that count to toOffsetLimit(totalCount). Filters and ordering apply to both traversal and slice reads. Provider cursors stay internal; stalled traversal fails instead of hanging, and traversal checks coroutine cancellation between requests.

Generic DbClient reads reject modern connection selections, raw root paging arguments, and paging arguments or cursor/PageInfo selections on structural connections. PgGraphqlClient operations and record projections also reject provider paging arguments and native cursor/PageInfo selections. Its select and selectRecords methods expose no page-size or cursor argument and drain the root collection's provider pages internally. The former fetchUuidIds, fetchUuidConnection, fetchNestedUuidConnections, and public UuidConnectionPage, UuidConnectionEdge, UuidConnectionPageInfo, ConnectionPageRequest, and NestedConnectionPageRequest types are removed.

Viaduct offset cursors encode positions in the ordered results; pg_graphql continuation tokens remain private to the adapter. Keep filters and ordering consistent when continuing a connection. Separate database requests do not share a snapshot, so changing rows can shift offsets between reads.

Reuse Secondary Lookups

Declare a lookup once instead of rebuilding database filters and relationship queries in each resolver. Choose matching rows with by or related, optionally project a referenced node, then execute the lookup as a list or connection:

import dev.viaduct.persistence.runtime.db.DbLookup

object MembershipLookups {
    val byPerson = DbLookup.by(GroupMember.Fields.person)
    val byGroup = DbLookup.related(Group.Fields.members)
    val usersByGroup = byGroup.project(GroupMember.Fields.person)
}

// A list resolver returns all matches as ordinary Viaduct references.
return dbClient.lookup(ctx, MembershipLookups.usersByGroup, ctx.arguments.groupId)

// A selective modern connection resolver uses its generated arguments/selections.
return dbClient.lookupConnection(ctx, MembershipLookups.usersByGroup, ctx.arguments.groupId)

This example assumes Group.members is a modern connection of GroupMember and GroupMember.person is a stored to-one reference. No uniqueness is required: projecting two memberships for the same person returns two references, or two edges with separate Viaduct offset cursors. The lookup descriptor uses existing storage and adds no public GraphQL query field. Declare optional types.<Type>.indexes in persistence policy to generate secondary indexes for property searches, then apply the generated migration.

Unique keys use the same API and return zero or one row when the complete non-null key is matched. Descriptors use the existing generated Hibernate mapping, when available, to reject nonpersisted types and fields before querying. Raw filters and ordering retain provider validation.

Declaring a lookup does not query the database or capture request state. Execution supplies the key and normal request credentials; returned references use the application's node resolvers and authorization checks. lookup reads all matching pages, while lookupConnection uses the resolver's Viaduct paging arguments and offset cursors. Provider cursors remain internal.

Declare secondary indexes (PR #36)

PR #36 adds non-unique secondary indexes alongside unique keys:

types:
  Person:
    unique: [[username]]
    indexes: [[region], [region, username]]

The example assumes stored username and region fields. Each inner list declares one index. Composite order is preserved, exact duplicates are deduplicated, and names are deterministic. Indexes support stored scalars, nullable fields, to-one references, @idOf aliases and fields from type extensions. Invalid or nonpersisted fields fail generation. Hibernate metadata supplies physical columns to fresh DDL and Liquibase snapshots/diffs; existing databases require a reviewed migration.

DbLookup.by and composite DbLookup.where conditions use the normal PostgreSQL planner. Declaring a lookup does not create an index; the indexes policy does. See the PR's usage guide.

See Secondary lookups for scalar/composite conditions, ordering, authorization, uniqueness, and validation details.

Resolve Mutations

Convert the Viaduct input into a pg_graphql value, then pass that value to the selected persistent node type. The resolver context supplies the payload type:

override suspend fun resolve(ctx: Context): AddGroupMemberPayload {
    val insert = ctx.arguments.input.toPgGraphqlInsert()
    return dbClient.entity<GroupMember>().insert(ctx, insert)
}

The same API supports updates, deletes, and batches:

dbClient.entity<Group>().update(ctx, ctx.arguments.input.toPgGraphqlUpdate<Group>())
dbClient.entity<GroupMember>().delete(ctx, ctx.arguments.input.toPgGraphqlDelete<GroupMember>())

dbClient.entity<GroupMember>().insertBatch(ctx, ctx.arguments.inputs.map { it.toPgGraphqlInsert() })
dbClient.entity<GroupMember>().updateBatch(ctx, ctx.arguments.inputs.map { it.toPgGraphqlUpdate<GroupMember>() })
dbClient.entity<GroupMember>().deleteBatch(ctx, ctx.arguments.inputs.map { it.toPgGraphqlDelete<GroupMember>() })

These are alternative calls for different mutation resolvers. updateBatch and deleteBatch execute one request per input. With HTTP or a JDBC DataSource, earlier writes remain committed if a later request fails. For all-or-nothing changes, use the batch methods on the transaction's entity<T>() inside dbClient.transaction(ctx) { ... }. A caller-owned JDBC connection instead leaves commit and rollback to its owner. insertBatch uses one list insert request.

The conversion functions convert typed global IDs. The client creates returned node references, fills the payload field whose type can represent the selected node type, and initializes userErrors to an empty list. That field can use the node's type directly or a union or interface that includes it. If several fields could hold the result, select one with entityField. If the resolver returns a union or interface and several concrete payload types could hold the result, select the concrete payload type with payloadType. These are optional arguments, validated before writing. See abstract mutation payloads.

Mutation limitations

The entity API provides pg_graphql insert, update, and delete operations for one persistent node type at a time. Each call writes only the table represented by entity<T>(). It does not inspect an input object for other persistent node types and does not turn nested objects into additional database operations.

For example, an insert<Group> can write the Group's scalar fields and foreign-key ID fields. An input shaped like this does not also insert the people:

input AddGroupInput {
  name: String!
  members: [AddPersonInput!]
}

pg_graphql's GroupInsertInput has no nested insert operation for members, so passing that field causes a pg_graphql input error. To refer to an existing node, pass the corresponding typed ID field:

input AddGroupMemberInput {
  groupId: ID! @idOf(type: "Group")
  personId: ID! @idOf(type: "Person")
}

To create the Group, Person, and GroupMember together, the resolver performs three inserts and passes client-created UUIDs into GroupMember. Use a transaction to commit those inserts together.

Use transactions

Use dbClient.transaction(ctx) { ... } to group mutations in one database transaction. The same API works with or without DBOS; choose the implementation when configuring DbClient. Without DBOS, use the HTTP or JDBC client configured above. No additional dependency is needed.

For example, add this mutation and its input types alongside the Group, GroupMember, and Person types defined above:

input NewGroupInput {
  uuidId: String!
  name: String!
}

input NewGroupMemberInput {
  uuidId: String!
  groupId: ID! @idOf(type: "Group")
  personId: ID! @idOf(type: "Person")
}

input CreateGroupWithMemberInput {
  group: NewGroupInput!
  membership: NewGroupMemberInput!
}

extend type Mutation {
  createGroupWithMember(input: CreateGroupWithMemberInput!): Boolean! @resolver
}

Supply client-created UUID strings for both uuidId fields. membership.groupId is a Group GlobalID whose internalID equals group.uuidId; it refers to the group this transaction will create. membership.personId identifies an existing Person. In Kotlin, the group reference can be constructed with ctx.globalIDFor(Group.Reflection, input.group.uuidId). The explicit input conversion sends its internal UUID to pg_graphql, not the encoded GlobalID.

The resolver converts the two input GRTs separately, then inserts both in one transaction:

override suspend fun resolve(ctx: Context): Boolean {
    val input = ctx.arguments.input
    val group = input.group.toPgGraphqlInsert()
    val membership = input.membership.toPgGraphqlInsert()

    dbClient.transaction(ctx) {
        entity<Group>().insert(group)
        entity<GroupMember>().insert(membership)
    }

    return true
}

toPgGraphqlInsert() converts generated Viaduct input GRTs, not output objects constructed with Group.Builder. Each converted input must contain fields accepted by its table's pg_graphql insert input, including membership.personId for the existing person. Typed ID fields use @idOf.

For the default buffered implementation, only operations called through the transaction participate: use entity<T>() inside the lambda, or transaction.entity<T>() with explicit transaction control. DBOS executes immediately, so ordinary reads and mutations on the same DbClient automatically join while the block is active. This includes work reached through a composed ctx.mutation(...) call. Automatic propagation through ctx.mutation(...) is DBOS-only; it does not apply to the default buffered implementation or to a transaction created with beginTransaction(ctx).

To use DBOS, add dev.viaduct.persistence:dbos at the same version as runtime, plus a PostgreSQL JDBC driver, and configure the client once. DBOS brings Kotlin standard library 2.4.0; Kotlin applications must use a compatible compiler (the integration is tested with Kotlin 2.4.0). Applications using only the runtime or plain JDBC modules do not inherit this requirement.

val transactions = DbosTransactions(dbos, dataSource)
val dbClient = DbClient(
    executor = JdbcPgGraphqlExecutor(dataSource),
    transactions = transactions,
)

Run the transaction block from a registered DBOS workflow. Configuring the client does not make an ordinary resolver invocation a workflow. See DBOS transactions for workflow registration, calling application code from a workflow, and recovery requirements.

DbosTransactions uses a dedicated pool of blocking transaction-owner threads, with suspending admission and a default parallelism of 16. The transaction body retains its caller's resolver dispatcher and cancellation job; active DBOS workflow/step context is carried into the callback. Set owner parallelism in relation to database connection capacity. Keep the adapter for the service lifetime and call transactions.close() during shutdown after active transactions finish. The application remains responsible for closing its connection pool.

Nested DbClient transaction execution is rejected across clients and across immediate, buffered, and identified/retryable modes. This includes standalone buffered commits and prepared-request resumption inside an active transaction. Creating an unsent buffer is allowed; committing it inside an active transaction is not. Pass the outer DbTransactionScope to reusable helpers, or run independent transactions sequentially. Ordinary reads and writes that join the active DBOS transaction do not start a nested transaction.

The composed mutation participates in DBOS's normal transaction lifecycle:

val created = dbClient.transaction(ctx) {
    val result =
        ctx.mutation(CreateGroupMutation, mapOf("input" to ctx.arguments.input))
            .getCreateGroupOrThrow()
    require(isValid(result)) // Throwing rolls back the composed mutation and all other writes.
    result
}.value

Successful completion commits. An exception or cancellation observed before commit rolls back the complete transaction; cancellation detected after commit cannot undo it. On a retryable database failure before commit, DBOS rolls back and reruns the entire block, including ctx.mutation(...). Composed resolvers must therefore avoid non-transactional side effects and use stable inputs and IDs across attempts. An application-requested rollback does not itself request a retry.

With either implementation, successful completion commits the mutations together, and a failed block does not commit them. The exception is plain JDBC with a caller-owned Connection: the caller must still commit or roll back that connection. Keep external service calls outside the block; they cannot be rolled back, and DBOS may rerun the block after a retryable database failure.

The buffered implementation still requires all mutation inputs up front, including IDs needed by related inserts, and exposes results after commit through operation handles. DBOS operations return their real results immediately, so a composed resolver can consume them inside the block. Return an operation handle when code must remain compatible with both implementations:

val committed = dbClient.transaction(ctx) {
    entity<Group>().insert(ctx.arguments.input.group.toPgGraphqlInsert())
    entity<GroupMember>().insert(ctx.arguments.input.membership.toPgGraphqlInsert())
}
val membership = committed.result[committed.value]

The last expression returns the membership insert's handle. membership is its pg_graphql mutation payload, containing affectedCount and records { uuidId }, not a GroupMember GRT. Return a list of handles to retrieve several operation results. Handles only work with their own transaction's results.

Begin, commit, or abort explicitly

Without DBOS, use beginTransaction(ctx) when application code needs explicit transaction control. This buffered handle cannot propagate into ctx.mutation(...); use the scoped DBOS transaction(ctx) { ... } API when composed resolvers must share a transaction. The same group and membership inputs can be inserted as follows:

val transaction = dbClient.beginTransaction(ctx)
val membershipHandle = try {
    transaction.entity<Group>().insert(ctx.arguments.input.group.toPgGraphqlInsert())
    transaction.entity<GroupMember>().insert(ctx.arguments.input.membership.toPgGraphqlInsert())
} catch (failure: Exception) {
    transaction.abort()
    throw failure
}

val result = transaction.commit()
val membership = result[membershipHandle]

commit() completes the transaction and returns its operation results, or throws on failure. To cancel before committing, call abort() instead:

val transaction = dbClient.beginTransaction(ctx)
transaction.entity<Group>().insert(ctx.arguments.input.group.toPgGraphqlInsert())
transaction.abort()

Nothing is written in this example. abort() discards unsent operations; it cannot undo a request already sent or a completed commit. A completed or aborted transaction cannot be reused, and an empty transaction cannot be committed. With a caller-owned JDBC Connection, commit() sends the operations but the caller must still call connection.commit() or connection.rollback(); abort() does neither. DBOS does not support these explicit-control methods; use the lambda form.

Inspect commit errors

Use commitResult() instead of commit() to receive a DbResult<DbTransactionResult>:

val transaction = dbClient.beginTransaction(ctx)
val handle = transaction.entity<Group>().insert(ctx.arguments.input.group.toPgGraphqlInsert())
val outcome = transaction.commitResult()
val errors = outcome.errors
val group = outcome.data?.get(handle)

Inspect errors before treating group as a successful write. HTTP may return partial data with errors; that data does not prove a commit. A JDBC DataSource discards rolled-back mutation data. With a caller-owned JDBC connection, mutation errors throw even from commitResult(), and the caller must roll back. Transport and decoding failures can also throw. Call either commit() or commitResult(), not both. See mutation results and connection ownership for error handling, and transaction implementation details for how the default implementation works.

Transaction mutation operations

Both the lambda scope and an explicit transaction expose these operations through entity<T>():

Operation	Input	Result handle
`insert(value)`	`input.toPgGraphqlInsert()`	One handle
`insertBatch(values)`	Several converted insert inputs	One handle for the batch
`update(value)`	`input.toPgGraphqlUpdate<T>()`	One handle
`updateBatch(values)`	Several converted update inputs	One handle per input
`delete(value)`	`input.toPgGraphqlDelete<T>()`	One handle
`deleteBatch(values)`	Several converted delete inputs	One handle per input

Each operation targets one selected node type; batches do not write a mixed object graph automatically. For a generated association table without an application GRT, both forms also accept insert(entity, value), update(entity, value), and delete(entity, value) directly, using PgGraphqlEntity and the corresponding pg_graphql input value.

Mutation inputs and payloads

An update sends only fields present in the Viaduct input after removing its selected identifier. An omitted field is not changed; an explicitly supplied null is sent as null. Delete removes only the selected rows. Any cascading delete behavior comes from application-owned database constraints, not recursive behavior in PG Persistence. The entity API does not provide upsert.

Insert conversion accepts a generated Viaduct input whose supplied fields are valid for pg_graphql's insert input for the selected node. Update and delete conversion require that input to contain an ID field whose @idOf target is the type selected by entity<T>(). The ID must be inside the input; the entity API does not inspect a separate mutation argument. The client converts that GlobalID to the row's uuidId filter and excludes the selected field from the values sent by update.

The client automatically uses the matching @idOf field when exactly one exists. No match fails because the operation cannot identify a row. If more than one field matches, the operation fails rather than guessing; select the identifying field explicitly:

val update = ctx.arguments.input.toPgGraphqlUpdate<Group>(identifierField = "groupId")
dbClient.entity<Group>().update(ctx, update)

The explicit field must exist in the input and have @idOf(type: "Group"). Convert each input separately before a batch update or delete. Each conversion can select a different identifier field; the batch accepts already-converted operations, not a shared identifier-field argument. Every operation must still target the selected node type. Batch update/delete execute one pg_graphql operation per input; batch insert sends all inputs in one operation.

Insert and update payloads must identify one field whose type can represent the selected node, either automatically when only one field matches or explicitly with entityField. Delete payloads may omit that field. When present, delete payloads contain references with the deleted IDs, not snapshots of the deleted rows; other fields cannot be fetched from those rows afterward. The entity API initializes userErrors to an empty list but does not convert pg_graphql errors into application userErrors; operations throw on those errors. Use the lower-level PgGraphqlMutationClient for explicit filters, returned database records, partial data, or structured pg_graphql errors.

Use PgGraphqlMutationClient directly when a resolver needs the records returned by pg_graphql instead of having DbClient.entity<T>() build the resolver's mutation payload.

Use `PgGraphqlMutationClient` directly

The lower-level client executes pg_graphql collection mutations without building a Viaduct payload:

val mutations = PgGraphqlMutationClient(httpClient, postgresGraphqlEndpoint)
val group = PgGraphqlEntity("Group")

val inserted = mutations.insert(
    entity = group,
    objectValue = ctx.arguments.input.toPgGraphqlInsert(),
    selection = "affectedCount records { uuidId name }",
    headers = mapOf("Authorization" to "Bearer $accessToken"),
)

This lower-level client accepts pg_graphql JSON. Convert a Viaduct input separately or construct the pg_graphql values directly. Typed GlobalIDs are converted by toPgGraphqlInsert.

Update and delete accept explicit pg_graphql filters. atMost is required, must be greater than zero, and limits how many matching rows pg_graphql may change:

val filter = buildJsonObject {
    put("uuidId", buildJsonObject { put("eq", groupId.internalID) })
}

val updated = mutations.update(
    entity = group,
    set = buildJsonObject { put("name", "New name") },
    filter = filter,
    atMost = 1,
    selection = "affectedCount records { uuidId name }",
    headers = requestHeaders,
)

val deleted = mutations.delete(
    entity = group,
    filter = filter,
    atMost = 1,
    headers = requestHeaders,
)

The selection is the selection inside the pg_graphql mutation payload; its default is affectedCount. Methods without Result throw UpstreamGraphqlException when pg_graphql returns errors. Use insertResult, updateResult, or deleteResult to receive a DbResult, subject to the connection-ownership rules below. Headers are supplied per call; unlike DbClient, this client does not derive them from an execution context.

Mutation results and connection ownership

For insertResult, updateResult, deleteResult, and an ordinary buffered transaction's commitResult(), a GraphQL error is handled as follows:

Connection mechanism	Behavior when pg_graphql returns mutation errors
HTTP	Returns the data and errors supplied by pg_graphql. Partial response data is not proof that writes committed.
JDBC with a `DataSource`	Rolls back the request and returns errors with `data = null`; rolled-back mutation data is discarded.
JDBC with a caller-owned `Connection`	Throws `UpstreamGraphqlException`, even from a `Result` method. The caller must roll back the enclosing transaction.

Methods without the Result suffix throw on GraphQL errors in all three cases. Query methods such as fetchResult and fetchJsonResult preserve successful query fields alongside their errors with either transport. SQL, connection, and malformed-response failures may still throw from Result methods.

Retry a transaction safely

This recovery feature uses HTTP; selecting JDBC alone does not provide durable retry or recovery. After enabling retryable transactions, give the transaction a stable operation ID. Using the generated inputs from the transaction example above:

val input = ctx.arguments.input
val group = input.group.toPgGraphqlInsert()
val membership = input.membership.toPgGraphqlInsert()

dbClient.transaction(ctx, operationId = requestId) {
    entity<Group>().insert(group)
    entity<GroupMember>().insert(membership)
}

requestId is an application-supplied identifier for this logical operation, reused on retries. The library resends the same GraphQL document and variables without rerunning the lambda or resolver. A repeated operation returns its saved database result. Reusing the ID with different commands fails. Keep UUIDs and other input values unchanged.

To save a request before sending it, use beginTransaction(ctx, operationId), add operations, then call prepare().encode():

val transaction = dbClient.beginTransaction(ctx, operationId = requestId)
transaction.entity<Group>().insert(ctx.arguments.input.group.toPgGraphqlInsert())
transaction.entity<GroupMember>().insert(ctx.arguments.input.membership.toPgGraphqlInsert())
val saved = transaction.prepare().encode()
// Persist saved in application-controlled storage before committing.
val result = transaction.commit()

prepare() freezes the operations; do not add more afterward. The application must store saved somewhere that survives process restarts. To resume that saved request after a failure or restart:

val result = dbClient.resumeTransaction(ctx, DbPreparedTransaction.decode(saved))

To look up an already committed operation without submitting it:

val recovered = dbClient.lookupTransaction(ctx, operationId = requestId)

Recovery uses fresh request headers and checks that the authenticated transaction scope still matches. The scope is an application-supplied identifier derived from authentication, such as a tenant and caller ID. It separates operation IDs belonging to different tenants or callers, so one cannot recover another's result. See the identity callback configuration.

lookupTransaction(ctx, operationId) returns the saved request and result, or null if no committed record is visible. Null does not prove rollback: an earlier request may still be running. DbTransactionException.outcome distinguishes an unknown outcome from a confirmed commit whose result could not be decoded. Preserve its prepared request when recovering; do not generate a new ID. Cancellation also does not prove rollback. Prepare and persist first if recovery after cancellation or process exit is required.

See transaction implementation details for concurrency, permissions, and retention limitations.

Gradle Tasks

Task	Use
`validateViaductPgPersistenceSchema`	Validate schema and policy compatibility
`generateViaductPgPersistenceModel`	Generate persistence metadata
`buildViaductEffectiveModel`	Generate PostgreSQL and pg_graphql SQL
`hibernateSchemaSnapshot`	Write a database-model snapshot
`hibernateSchemaDiff`	Compare the generated model with PostgreSQL

Custom Configuration

Most applications should use the generated defaults. For custom naming strategies, Hibernate metadata customization, schema-directory changes, or replacing the generated Hibernate XML mappings entirely, see Custom configuration.

Requirements

A Gradle build that provides assembleViaductCentralSchema
PostgreSQL when applying generated SQL
pgcrypto and pg_graphql for the complete PostgreSQL/GraphQL integration

Applications do not need to be implemented in Kotlin or run on the JVM to use the generated PostgreSQL schema and pg_graphql API. The Gradle plugin and Viaduct runtime integration are JVM tools, but that is not a requirement of the database interface.

License

PG Persistence is licensed under the Apache License, Version 2.0.

Name		Name	Last commit message	Last commit date
.github		.github
config/spotbugs		config/spotbugs
dbos		dbos
docs		docs
examples/oauth-provider-sample		examples/oauth-provider-sample
gradle		gradle
jdbc		jdbc
plugin		plugin
runtime		runtime
signing		signing
.gitignore		.gitignore
ARCHITECTURE.md		ARCHITECTURE.md
LICENSE		LICENSE
README.md		README.md
build.gradle.kts		build.gradle.kts
gradle.properties		gradle.properties
gradlew		gradlew
gradlew.bat		gradlew.bat
settings.gradle.kts		settings.gradle.kts

Latest commit

History

Folders and files

Repository files navigation