Function Shapes

SQL functions in FraiseQL return data that maps to GraphQL types. This guide covers patterns for designing function return types.

Canonical Return Type: mutation_response

All FraiseQL mutation functions return mutation_response, a composite type that carries status, the affected entity, and optional cascade information:

-- mutation_response fields:
-- status        TEXT    -- 'success', 'failed:not_found', 'conflict:duplicate', etc.
-- message       TEXT    -- Human-readable description
-- entity_id     TEXT    -- ID of the primary affected entity (UUID as text)
-- entity_type   TEXT    -- GraphQL type name (e.g. 'User')
-- entity        JSONB   -- Full entity data (fetched from v_*)
-- updated_fields TEXT[] -- Which fields changed (for partial updates)
-- cascade       JSONB   -- Side-effect entities (see Cascade section below)
-- metadata      JSONB   -- Arbitrary extra data

CREATE FUNCTION fn_create_user(
    user_email TEXT,
    user_name TEXT
) RETURNS mutation_response AS $$
DECLARE
    v_id UUID;
BEGIN
    INSERT INTO tb_user (email, name, identifier)
    VALUES (user_email, user_name, user_email)
    RETURNING id INTO v_id;

    PERFORM sync_tv_user();

    RETURN ROW(
        'success', 'User created', v_id, 'User',
        (SELECT data FROM v_user WHERE id = v_id),
        NULL, NULL, NULL
    )::mutation_response;
END;
$$ LANGUAGE plpgsql;

@fraiseql.mutation(sql_source="fn_create_user", operation="CREATE")
def create_user(email: str, name: str) -> User:
    pass

For delete operations, set entity to NULL (the entity no longer exists):

CREATE FUNCTION fn_delete_user(p_id UUID) RETURNS mutation_response AS $$
BEGIN
    DELETE FROM tb_user WHERE id = p_id;

    IF NOT FOUND THEN
        RETURN ROW('failed:not_found', 'User not found', p_id, 'User',
            NULL, NULL, NULL, NULL)::mutation_response;
    END IF;

    PERFORM sync_tv_user();
    RETURN ROW('success', 'User deleted', p_id, 'User',
        NULL, NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;

@fraiseql.mutation(sql_source="fn_delete_user", operation="DELETE")
def delete_user(id: fraiseql.ID) -> bool:
    pass

Note

Status strings that trigger error handling are: anything starting with failed:, anything starting with conflict:, and the exact string "error". Standard success statuses are 'success', 'new', 'updated', 'deleted', and 'noop:*' for no-op outcomes.

Legacy Return Shapes (v1)

The following patterns worked in FraiseQL v1 and are documented here for reference when migrating older schemas. New code should use mutation_response.

Returning Count

For bulk operations returning a count (v1 pattern):

CREATE FUNCTION fn_archive_old_posts(days_old INT) RETURNS INTEGER AS $$
DECLARE
    archived_count INTEGER;
BEGIN
    WITH archived AS (
        UPDATE tb_post
        SET is_archived = true
        WHERE created_at < NOW() - (days_old || ' days')::INTERVAL
        AND is_archived = false
        RETURNING id
    )
    SELECT COUNT(*) INTO archived_count FROM archived;

    PERFORM sync_tv_post();
    RETURN archived_count;
END;
$$ LANGUAGE plpgsql;

@fraiseql.mutation(sql_source="fn_archive_old_posts", operation="UPDATE")
def archive_old_posts(days_old: int) -> int:
    """Returns count of archived posts."""
    pass

GraphQL Cascade: Advanced Mutations with Side Effects

When mutations affect multiple entities or modify related data, use GraphQL Cascade to automatically update client caches and maintain consistency.

GraphQL Cascade is the recommended pattern for production mutations because it:

• Returns all affected entities in a single response
• Provides automatic cache invalidation hints to clients
• Tracks side effects across related entities
• Eliminates the need for follow-up queries
• Feeds entity-level cache invalidation on the server side

Enabling Cascade

Enable cascade globally in TOML or per-mutation on the decorator. The SQL function pattern is the same either way — the toggle controls whether clients see the cascade field in GraphQL responses.

Global (TOML):

[cascade]
enabled = true

Per-mutation (decorator):

@fraiseql.mutation(sql_source="fn_create_post", operation="CREATE", cascade=True)
def create_post(input: CreatePostInput) -> Post:
    pass

Per-mutation cascade=True or cascade=False overrides the global TOML setting. When cascade is enabled for a mutation, the compiler adds a cascade: Cascade field to its success response type.

Note

Even when cascade is disabled for clients, the server still uses cascade data internally for cache invalidation when the SQL function returns it. The toggle only controls GraphQL response exposure.

When to Use Cascade

Use Cascade when your mutation:

• Affects multiple entities (create post + update author stats)
• Has computed side effects (transfer funds between accounts)
• Updates related data (add tag to post + update tag count)
• Needs automatic cache invalidation hints

-- Example: Creating a post updates author's post_count
-- This affects 2 entities: Post (created) and User (updated)
-- Cascade tracks both changes for client cache updates

Cascade PostgreSQL Function Pattern

Build the cascade JSONB inline using jsonb_build_object and jsonb_build_array. This example creates a post and also updates the author’s post count — two entities affected, reported via cascade:

CREATE OR REPLACE FUNCTION fn_create_post(
    p_title   TEXT,
    p_content TEXT,
    p_author_id UUID
) RETURNS mutation_response LANGUAGE plpgsql AS $$
DECLARE
    v_id        UUID;
    v_post_data JSONB;
BEGIN
    -- Insert and get the new post's id
    INSERT INTO tb_post (identifier, title, content, fk_author)
    VALUES (
        'post_' || to_char(now(), 'YYYYMMDDHH24MISS'),
        p_title,
        p_content,
        (SELECT pk_user FROM tb_user WHERE id = p_author_id)
    )
    RETURNING id INTO v_id;

    -- Update author's post count (side effect)
    UPDATE tb_user SET post_count = post_count + 1
    WHERE id = p_author_id;

    -- Refresh projection tables
    PERFORM sync_tv_post_single(v_id);

    -- Fetch entity data for the response
    SELECT data INTO v_post_data FROM v_post WHERE id = v_id;

    -- Return mutation_response with cascade payload
    RETURN ROW(
        'success',
        'Post created',
        v_id,
        'Post',
        v_post_data,
        NULL,
        jsonb_build_object(
            'updated', jsonb_build_array(
                jsonb_build_object(
                    '__typename', 'Post',
                    'id',         v_id,
                    'operation',  'CREATED',
                    'entity',     v_post_data
                ),
                jsonb_build_object(
                    '__typename', 'User',
                    'id',         p_author_id,
                    'operation',  'UPDATED',
                    'entity',     (SELECT data FROM v_user WHERE id = p_author_id)
                )
            ),
            'deleted',       '[]'::jsonb,
            'invalidations', jsonb_build_array(
                jsonb_build_object('query_name', 'posts', 'strategy', 'INVALIDATE')
            ),
            'metadata', jsonb_build_object(
                'timestamp',     now(),
                'affectedCount', 2,
                'depth',         1
            )
        ),
        NULL
    )::mutation_response;
END;
$$;

GraphQL Response with Cascade

{
  "data": {
    "createPost": {
      "post": {
        "id": "550e8400-e29b-41d4-a716-446655440000",
        "title": "Hello World",
        "content": "...",
        "authorId": "6ba7b810-9dad-11d1-80b4-00c04fd430c8"
      },
      "message": "Post created successfully",
      "cascade": {
        "updated": [
          {
            "__typename": "Post",
            "id": "550e8400-e29b-41d4-a716-446655440000",
            "operation": "CREATED",
            "entity": {
              "id": "550e8400-e29b-41d4-a716-446655440000",
              "title": "Hello World",
              "content": "..."
            }
          },
          {
            "__typename": "User",
            "id": "6ba7b810-9dad-11d1-80b4-00c04fd430c8",
            "operation": "UPDATED",
            "entity": {
              "id": "6ba7b810-9dad-11d1-80b4-00c04fd430c8",
              "name": "Alice",
              "postCount": 5
            }
          }
        ],
        "deleted": [],
        "invalidations": [
          {
            "query_name": "posts",
            "strategy": "INVALIDATE"
          }
        ],
        "metadata": {
          "timestamp": "2025-01-15T10:30:00Z",
          "affectedCount": 2,
          "depth": 1,
          "transactionId": "123456789"
        }
      }
    }
  }
}

Cascade Structure Reference

Field	Type	Description
updated	Array	Entities created or updated with id, operation (CREATED or UPDATED), entity (full data), and __typename
deleted	Array	Entities deleted with id, operation (‘DELETED’), entity (entity state at deletion), and __typename
invalidations	Array	Cache invalidation hints with query_name and strategy (INVALIDATE/REFETCH/REMOVE)
metadata	Object	Operation metadata with timestamp (ISO 8601), affectedCount, depth, and optional transactionId

Note

SQL functions return __typename for each cascade entity. This helps the GraphQL executor resolve the correct type. GraphQL automatically includes this field in responses even though it’s not defined in the type schema.

Cascade Helper Functions

The cascade JSONB structure is built inline using jsonb_build_object. You can define your own helper functions to DRY up common patterns:

-- Example user-defined helper: wrap a created entity for the cascade payload
CREATE OR REPLACE FUNCTION cascade_entity_created(
    p_typename TEXT,
    p_id UUID,
    p_data JSONB
) RETURNS JSONB LANGUAGE sql AS $$
    SELECT jsonb_build_object(
        '__typename', p_typename,
        'id',         p_id,
        'operation',  'CREATED',
        'entity',     p_data
    );
$$;

-- Usage inside a mutation function:
-- updated := jsonb_build_array(cascade_entity_created('Post', v_post_id, v_post_data));

Client Integration with Cascade

Apollo Client:

const result = await client.mutate({
  mutation: CREATE_POST,
  variables: { input }
});

const cascade = result.data.createPost.cascade;
if (cascade) {
  // Apply entity updates to cache
  for (const update of cascade.updated) {
    client.cache.writeFragment({
      id: client.cache.identify({
        __typename: update.__typename,
        id: update.id
      }),
      fragment: gql`fragment _ on ${update.__typename} { id }`,
      data: update.entity
    });
  }

  // Apply invalidations
  for (const hint of cascade.invalidations) {
    if (hint.strategy === 'INVALIDATE') {
      client.cache.evict({ fieldName: hint.query_name });
    }
  }
}

Relay:

commitMutation(environment, {
  mutation: CREATE_POST,
  variables: { input },
  onCompleted: (response) => {
    const cascade = response.createPost.cascade;
    if (cascade) {
      cascade.updated.forEach(update => {
        environment.getStore().publish({
          __typename: update.__typename,
          id: update.id
        }, update.entity);
      });
    }
  }
});

---

Mutation Return Shape Requirements

All v2 mutation functions return mutation_response. FraiseQL also supports JSONB returns for custom response shapes.

Return Shape Reference

Return Type	FraiseQL Behavior	Use Case
mutation_response	Canonical v2 return; extracts entity from entity JSONB field	All mutations
JSONB	Maps directly to a custom GraphQL result type	Rich responses with computed fields
INTEGER	Returns numeric count (v1 legacy)	Bulk operations

JSONB Returns (Custom Shape)

When returning JSONB, the structure must exactly match your GraphQL type:

@fraiseql.type
class MutationResult:
    success: bool
    message: str
    data: User | None

@fraiseql.mutation(sql_source="fn_create_user_with_log")
def create_user_with_log(email: str) -> MutationResult:
    pass

Required JSONB structure:

{
  "success": true,
  "message": "User created",
  "data": {
    "id": "550e8400...",
    "name": "Alice"
  }
}

Requirements:

• Top-level keys match GraphQL type fields (case-sensitive)
• Nested objects match nested GraphQL types
• Arrays have correct element type
• Null values only for nullable fields

Common mistake:

-- DON'T: Missing fields
RETURN jsonb_build_object(
    'success', true
    -- Missing 'message' and 'data'!
);

-- DO: Complete object
RETURN jsonb_build_object(
    'success', true,
    'message', 'User created',
    'data', jsonb_build_object(
        'id', new_user_id,
        'name', user_name,
        'email', user_email
    )
);

Composite Return Types

Returning JSONB

Return rich data as JSONB:

CREATE FUNCTION fn_create_order(
    customer_id UUID,
    items JSONB
) RETURNS JSONB AS $$
DECLARE
    new_order_id UUID;
    order_total DECIMAL;
    result JSONB;
BEGIN
    -- Create order and calculate total
    INSERT INTO tb_order (fk_user, total, status)
    VALUES (
        (SELECT pk_user FROM tb_user WHERE id = customer_id),
        (SELECT SUM((item->>'price')::DECIMAL * (item->>'quantity')::INT)
         FROM jsonb_array_elements(items) AS item),
        'pending'
    )
    RETURNING id, total INTO new_order_id, order_total;

    -- Build result
    result := jsonb_build_object(
        'order_id', new_order_id,
        'total', order_total,
        'item_count', jsonb_array_length(items),
        'status', 'pending'
    );

    PERFORM sync_tv_order();
    RETURN result;
END;
$$ LANGUAGE plpgsql;

@fraiseql.type
class CreateOrderResult:
    order_id: ID
    total: Decimal
    item_count: int
    status: str

@fraiseql.mutation(sql_source="fn_create_order", operation="CREATE")
def create_order(customer_id: ID, items: list[OrderItemInput]) -> CreateOrderResult:
    pass

Returning Table Type

Use RETURNS TABLE for structured data:

CREATE FUNCTION fn_transfer_funds(
    from_account UUID,
    to_account UUID,
    amount DECIMAL
) RETURNS TABLE (
    success BOOLEAN,
    from_balance DECIMAL,
    to_balance DECIMAL,
    transfer_id UUID
) AS $$
DECLARE
    new_transfer_id UUID;
    new_from_balance DECIMAL;
    new_to_balance DECIMAL;
BEGIN
    -- Debit source account
    UPDATE tb_account
    SET balance = balance - amount
    WHERE id = from_account
    RETURNING balance INTO new_from_balance;

    -- Credit destination account
    UPDATE tb_account
    SET balance = balance + amount
    WHERE id = to_account
    RETURNING balance INTO new_to_balance;

    -- Record transfer
    INSERT INTO tb_transfer (from_account, to_account, amount)
    VALUES (from_account, to_account, amount)
    RETURNING id INTO new_transfer_id;

    RETURN QUERY SELECT
        true AS success,
        new_from_balance AS from_balance,
        new_to_balance AS to_balance,
        new_transfer_id AS transfer_id;
END;
$$ LANGUAGE plpgsql;

@fraiseql.type
class TransferResult:
    success: bool
    from_balance: Decimal
    to_balance: Decimal
    transfer_id: ID

@fraiseql.mutation(sql_source="fn_transfer_funds", operation="CREATE")
def transfer_funds(
    from_account: ID,
    to_account: ID,
    amount: Decimal
) -> TransferResult:
    pass

Multiple Entity Returns

All mutation functions return mutation_response — a single composite type that carries the operation outcome, affected entity data, and cascaded changes. For bulk operations, the created entities are embedded in the entity JSONB field as an array under data.items.

Bulk Create Pattern

CREATE FUNCTION fn_bulk_create_users(
    users_data JSONB
)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
    user_record JSONB;
    created_ids JSONB := '[]'::JSONB;
    new_id      UUID;
BEGIN
    FOR user_record IN SELECT * FROM jsonb_array_elements(users_data)
    LOOP
        INSERT INTO tb_user (email, name, identifier)
        VALUES (
            user_record->>'email',
            user_record->>'name',
            user_record->>'email'
        )
        RETURNING id INTO new_id;

        created_ids := created_ids || jsonb_build_array(new_id::TEXT);
    END LOOP;

    RETURN ROW(
        'created',
        format('%s users created', jsonb_array_length(users_data)),
        NULL,                                          -- entity_id (N/A for bulk)
        'User',                                        -- entity_type
        jsonb_build_object('items', created_ids),      -- entity: bulk results here
        NULL::TEXT[],                                  -- updated_fields
        NULL::JSONB,                                   -- cascade
        NULL::JSONB                                    -- metadata
    )::mutation_response;
END;
$$;

@fraiseql.mutation(sql_source="fn_bulk_create_users", operation="CREATE")
def bulk_create_users(users: list[CreateUserInput]) -> MutationResult:
    """Bulk-create users. Created IDs available in result.entity['items']."""
    pass

The response entity.items array contains the UUIDs of all created records. Clients unpack the array from the GraphQL MutationResult.entity field:

Error Handling Shapes

Result Types

Return success/error information:

CREATE FUNCTION fn_login(
    email TEXT,
    password TEXT
) RETURNS JSONB AS $$
DECLARE
    user_record RECORD;
    token TEXT;
BEGIN
    -- Find user
    SELECT * INTO user_record
    FROM tb_user
    WHERE tb_user.email = fn_login.email;

    IF NOT FOUND THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'USER_NOT_FOUND',
            'message', 'No user with that email'
        );
    END IF;

    -- Verify password
    IF NOT verify_password(password, user_record.password_hash) THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'INVALID_PASSWORD',
            'message', 'Incorrect password'
        );
    END IF;

    -- Generate token
    token := generate_jwt(user_record.id);

    RETURN jsonb_build_object(
        'success', true,
        'token', token,
        'user_id', user_record.id
    );
END;
$$ LANGUAGE plpgsql;

@fraiseql.type
class LoginResult:
    success: bool
    error: str | None
    message: str | None
    token: str | None
    user: User | None

@fraiseql.mutation(sql_source="fn_login", operation="CREATE")
def login(email: str, password: str) -> LoginResult:
    pass

Union Return Types

@fraiseql.type
class LoginSuccess:
    token: str
    user: User

@fraiseql.type
class LoginError:
    code: str
    message: str

@fraiseql.union(members=[LoginSuccess, LoginError])
class LoginResult:
    pass

@fraiseql.mutation(sql_source="fn_login", operation="CREATE")
def login(email: str, password: str) -> LoginResult:
    pass

Pagination Shapes

Offset Pagination

CREATE FUNCTION fn_search_products(
    query TEXT,
    lim INT DEFAULT 20,
    off INT DEFAULT 0
) RETURNS JSONB AS $$
DECLARE
    total_count INT;
    items JSONB;
BEGIN
    -- Count total
    SELECT COUNT(*) INTO total_count
    FROM tb_product
    WHERE name ILIKE '%' || query || '%'
       OR description ILIKE '%' || query || '%';

    -- Get page
    SELECT COALESCE(jsonb_agg(data ORDER BY score DESC), '[]')
    INTO items
    FROM (
        SELECT
            v.data,
            ts_rank(to_tsvector(p.name || ' ' || p.description),
                    plainto_tsquery(query)) AS score
        FROM tb_product p
        JOIN v_product v ON v.id = p.id
        WHERE p.name ILIKE '%' || query || '%'
           OR p.description ILIKE '%' || query || '%'
        ORDER BY score DESC
        LIMIT lim OFFSET off
    ) sub;

    RETURN jsonb_build_object(
        'items', items,
        'total_count', total_count,
        'has_more', (off + lim) < total_count
    );
END;
$$ LANGUAGE plpgsql;

@fraiseql.type
class SearchResult:
    items: list[Product]
    total_count: int
    has_more: bool

@fraiseql.query
def search_products(
    query: str,
    limit: int = 20,
    offset: int = 0
) -> SearchResult:
    return fraiseql.config(sql_source="fn_search_products")

Best Practices

Keep Functions Focused

-- Good: Single responsibility, each returns mutation_response
fn_create_user(email, name) → mutation_response
fn_add_user_role(user_id, role) → mutation_response
fn_set_user_avatar(user_id, url) → mutation_response

-- Avoid: Too many responsibilities
fn_create_user_with_role_and_avatar_and_profile(...) → mutation_response

Sync Projections

Always sync affected projection tables:

-- After any write operation
PERFORM sync_tv_user();
PERFORM sync_tv_post();  -- If posts affected

Handle Errors Gracefully

-- Use RAISE EXCEPTION for errors
IF user_email IS NULL THEN
    RAISE EXCEPTION 'Email is required';
END IF;

-- Or return error objects for recoverable errors
IF balance < amount THEN
    RETURN jsonb_build_object(
        'success', false,
        'error', 'INSUFFICIENT_FUNDS'
    );
END IF;

Debugging Mutation Return Shapes

Common Return Shape Errors

Error: “ID not found in view”

Cause: Function returned an ID that doesn’t exist in the table, or projection table wasn’t synced.

-- Fix: Ensure sync is called
INSERT INTO tb_user (...) RETURNING id INTO new_id;
PERFORM sync_tv_user();  -- Must be before RETURN
RETURN new_id;

Error: “JSONB field mismatch”

Cause: JSONB structure doesn’t match GraphQL type, missing required fields, or wrong field names (case-sensitive).

-- Check field names match exactly
-- GraphQL expects: { id, name, email, created_at }
-- JSONB must have: 'id', 'name', 'email', 'created_at'

result := jsonb_build_object(
    'id', user_id,
    'name', user_name,
    'email', user_email,
    'created_at', NOW()
);

Validation with CLI

Before deploying, validate your schema:

fraiseql validate schema.json

This checks:

• Mutation return types exist
• All referenced types are defined
• Function signatures match declared types

Local Testing Checklist

Before deploying mutations, test:

# 1. Schema validation
fraiseql validate schema.json

# 2. Compile schema (checks all types resolve correctly)
fraiseql compile fraiseql.toml --check

# 3. Apply migrations
fraiseql migrate up

# 4. Start server and test mutations manually
fraiseql run --watch

# 5. Check projections synced (run in psql)
SELECT COUNT(*) FROM v_user;  -- Should equal tb_user count

Transport Behaviour

The mutation_response pattern works across all transports. REST translates the response to appropriate HTTP status codes (200 for updates, 201 for creates). gRPC maps the response fields to a protobuf message with the same structure.

Next Steps

Mutations

Mutations — Mutation patterns

CQRS

CQRS — Understanding the data flow

Schema Design

Schema Design — Overall design patterns