Function Shapes

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

Basic Return Types

Returning IDs

The simplest pattern — return the ID of the created/modified entity:

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

    PERFORM sync_tv_user();
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

@fraiseql.mutation(sql_source="fn_create_user", operation="CREATE")
def create_user(email: str, name: str) -> User:
    """Returns User - FraiseQL fetches from v_user using returned ID."""
    pass

FraiseQL automatically:

1. Calls the function to get the UUID
2. Queries v_user with that UUID
3. Returns the full User object

Returning Boolean

For simple success/failure:

CREATE FUNCTION fn_delete_user(user_id UUID) RETURNS BOOLEAN AS $$
BEGIN
    DELETE FROM tb_user WHERE id = user_id;
    PERFORM sync_tv_user();
    RETURN FOUND;  -- true if row was deleted
END;
$$ LANGUAGE plpgsql;

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

Returning Count

For bulk operations:

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

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

V2 Format (Recommended for new code):

CREATE OR REPLACE FUNCTION graphql.create_post(input jsonb)
RETURNS mutation_response AS $$
DECLARE
    v_post_id uuid;
    v_author_id uuid;
    v_post_data jsonb;
    v_author_data jsonb;
    v_cascade_data jsonb;
BEGIN
    -- Create post
    v_post_id := gen_random_uuid();
    INSERT INTO posts (id, title, content, author_id, created_at)
    VALUES (v_post_id, input->>'title', input->>'content',
            (input->>'author_id')::uuid, now());

    v_author_id := (input->>'author_id')::uuid;

    -- Update author stats (side effect)
    UPDATE users SET post_count = post_count + 1 WHERE id = v_author_id;

    -- Fetch complete entity data
    SELECT jsonb_build_object('id', id, 'title', title, 'content', content,
                              'author_id', author_id, 'created_at', created_at)
    INTO v_post_data FROM posts WHERE id = v_post_id;

    SELECT jsonb_build_object('id', id, 'name', name, 'email', email,
                              'post_count', post_count)
    INTO v_author_data FROM users WHERE id = v_author_id;

    -- Build cascade using helper functions
    v_cascade_data := cascade_merge(
        cascade_entity_created('Post', v_post_id, v_post_data),
        cascade_entity_update('User', v_author_id, v_author_data)
    );

    -- Add invalidations (hints for list queries)
    v_cascade_data := cascade_merge(
        v_cascade_data,
        cascade_invalidate_cache(ARRAY['posts', 'user_posts'], 'INVALIDATE')
    );

    -- Add metadata
    v_cascade_data := cascade_merge(
        v_cascade_data,
        cascade_metadata(2, 1, NULL)
    );

    -- Return success with cascade
    RETURN mutation_created(
        'Post created successfully',
        v_post_data,
        'Post',
        v_cascade_data
    );
END;
$$ LANGUAGE plpgsql;

Legacy Format (v1.4):

CREATE OR REPLACE FUNCTION graphql.create_post(input jsonb)
RETURNS jsonb AS $$
DECLARE
    v_post_id uuid;
    v_author_id uuid;
BEGIN
    -- Create post
    INSERT INTO tb_post (title, content, author_id)
    VALUES (input->>'title', input->>'content', (input->>'author_id')::uuid)
    RETURNING id INTO v_post_id;

    v_author_id := (input->>'author_id')::uuid;

    -- Update author stats
    UPDATE tb_user SET post_count = post_count + 1 WHERE id = v_author_id;

    -- Return with cascade metadata
    RETURN jsonb_build_object(
        'success', true,
        'data', jsonb_build_object('id', v_post_id, 'title', input->>'title'),
        '_cascade', jsonb_build_object(
            'updated', jsonb_build_array(
                -- MUST include __typename in CASCADE data
                jsonb_build_object(
                    '__typename', 'Post',
                    'id', v_post_id,
                    'operation', 'CREATED',
                    'entity', (SELECT data FROM v_post WHERE id = v_post_id)
                ),
                jsonb_build_object(
                    '__typename', 'User',
                    'id', v_author_id,
                    'operation', 'UPDATED',
                    'entity', (SELECT data FROM v_user WHERE id = v_author_id)
                )
            ),
            'deleted', '[]'::jsonb,
            'invalidations', jsonb_build_array(
                jsonb_build_object(
                    'query_name', 'posts',
                    'strategy', 'INVALIDATE',
                    'scope', 'PREFIX'
                )
            ),
            'metadata', jsonb_build_object(
                'timestamp', now(),
                'affected_count', 2,
                'depth', 1,
                'transaction_id', txid_current()::text
            )
        )
    );
END;
$$ LANGUAGE plpgsql;

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": [
          {
            "queryName": "posts",
            "strategy": "INVALIDATE",
            "scope": "PREFIX"
          }
        ],
        "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 queryName, strategy (INVALIDATE/REFETCH/REMOVE), and scope
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

PostgreSQL helper functions (v1.7+) simplify cascade construction:

-- Create an entity
SELECT cascade_entity_created('Post', post_id, post_data);

-- Update an entity
SELECT cascade_entity_update('User', user_id, user_data);

-- Delete an entity
SELECT cascade_entity_deleted('Comment', comment_id);

-- Add cache invalidations
SELECT cascade_invalidate_cache(ARRAY['posts', 'user_posts'], 'INVALIDATE');

-- Add metadata
SELECT cascade_metadata(affected_count, depth, transaction_id);

-- Merge multiple cascade objects
SELECT cascade_merge(cascade1, cascade2);

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.queryName });
    }
  }
}

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

When designing database function return types, understand how FraiseQL interprets each shape:

Return Shape Rules

Return Type	FraiseQL Behavior	Use Case	Requirements
UUID/ID	Fetches full entity from view	Most common: create/update operations	View (v_*) must exist; return must be valid ID from table
BOOLEAN	Returns success/failure	Delete operations, toggles	true = success, false = failure
INTEGER	Returns numeric count/value	Bulk operations, computed values	Any non-null integer
JSONB	Maps directly to GraphQL type	Rich responses with computed fields	Must match GraphQL type shape exactly
TABLE (composite)	First row maps to return type	Multiple related values	All columns must match GraphQL type

ID-Based Returns (Most Common)

When you return an ID from a mutation, FraiseQL performs a two-step process:

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

Requirements:

• Return type must be a valid ID from the table
• The ID must exist in the view (v_*)
• Projection table must be synced: PERFORM sync_tv_user()

Common mistake:

-- DON'T: Return non-existent ID
INSERT INTO tb_user (email) VALUES ($1)
RETURNING some_other_id;  -- Not an ID from tb_user!

-- DO: Return actual inserted ID
INSERT INTO tb_user (email) VALUES ($1)
RETURNING id;  -- Actual row ID

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

Return Multiple IDs

CREATE FUNCTION fn_bulk_create_users(
    users_data JSONB
) RETURNS UUID[] AS $$
DECLARE
    user_record JSONB;
    new_ids UUID[] := '{}';
    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;

        new_ids := array_append(new_ids, new_id);
    END LOOP;

    PERFORM sync_tv_user();
    RETURN new_ids;
END;
$$ LANGUAGE plpgsql;

@fraiseql.mutation(sql_source="fn_bulk_create_users", operation="CREATE")
def bulk_create_users(users: list[CreateUserInput]) -> list[User]:
    """Returns list of created Users."""
    pass

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(sql_source="fn_search_products")
def search_products(
    query: str,
    limit: int = 20,
    offset: int = 0
) -> SearchResult:
    pass

Best Practices

Keep Functions Focused

-- Good: Single responsibility
fn_create_user(email, name) → UUID
fn_add_user_role(user_id, role) → BOOLEAN
fn_set_user_avatar(user_id, url) → BOOLEAN

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

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.intermediate.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. Run migrations
fraiseql migrate apply

# 3. Test mutation execution
fraiseql test mutations/create_user.test.graphql

# 4. Verify return shape
fraiseql test --verbose mutations/  # Shows actual JSON returned

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

Next Steps

Mutations

Mutations — Mutation patterns

CQRS

CQRS — Understanding the data flow

Schema Design

Schema Design — Overall design patterns