Mutations

Note

FraiseQL mutations call database functions (fn_*). The SQL syntax on this page uses PostgreSQL. For MySQL stored procedures, SQLite triggers, or SQL Server stored procedures, see your database guide.

Mutations are write operations in FraiseQL. They call PostgreSQL fn_* functions that write to tb_* tables and return a mutation_response composite row — keeping writes and reads cleanly separated through the CQRS pattern.

Mutation Flow

graph LR
    A[GraphQL Mutation] --> B[fn_* PostgreSQL Function]
    B --> C[Write to tb_* Tables]
    C --> D[sync_tv_*() refresh]
    D --> E[Return mutation_response(entity JSONB from v_* view)]

The SQL function does everything: validates input, writes data, optionally syncs projection tables, and returns shaped JSONB from a v_* view. FraiseQL never assembles the response in application code.

Mutations are transport-agnostic at the database level. Whether the client sends a GraphQL mutation, a REST POST, or a gRPC unary call, FraiseQL routes the write to the same fn_* PostgreSQL function. The observer system fires on the database commit — it does not distinguish which transport initiated the mutation.

---

Defining Mutations

Python

import fraiseql

@fraiseql.mutation(sql_source="fn_create_post", operation="CREATE")
def create_post(input: CreatePostInput) -> Post:
    """Create a new blog post. Calls fn_create_post($1::jsonb)."""
    pass

@fraiseql.mutation(sql_source="fn_update_post", operation="UPDATE")
def update_post(id: str, input: UpdatePostInput) -> Post:
    """Update an existing post."""
    pass

@fraiseql.mutation(sql_source="fn_delete_post", operation="DELETE")
def delete_post(id: str) -> bool:
    """Delete a post. Returns true if the row was removed."""
    pass

TypeScript

import { Mutation } from 'fraiseql';

@Mutation()
function createPost(input: CreatePostInput): Post {
  /** Create a new blog post. Calls fn_create_post($1::jsonb). */
}

@Mutation()
function updatePost(id: string, input: UpdatePostInput): Post {
  /** Update an existing post. */
}

@Mutation()
function deletePost(id: string): boolean {
  /** Delete a post. Returns true if the row was removed. */
}

---

PostgreSQL Function Backing

Every mutation maps to a PostgreSQL fn_* function. FraiseQL calls the function with a single jsonb argument and expects the function to return a mutation_response composite row.

PostgreSQL

CREATE OR REPLACE FUNCTION fn_create_post(p_input jsonb)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
  v_post_id UUID;
BEGIN
  INSERT INTO tb_post (title, content, fk_user)
  VALUES (
    p_input->>'title',
    p_input->>'content',
    (SELECT pk_user FROM tb_user WHERE id = (p_input->>'authorId')::UUID)
  )
  RETURNING id INTO v_post_id;

  RETURN ROW('success', 'Post created', v_post_id::text, 'Post',
      (SELECT data FROM v_post WHERE id = v_post_id),
      NULL, NULL, NULL)::mutation_response;
END;
$$;

MySQL

-- MySQL equivalent
CREATE PROCEDURE fn_create_post(IN p_input JSON)
BEGIN
  DECLARE v_post_id CHAR(36);
  SET v_post_id = UUID();

  INSERT INTO tb_post (id, title, content, fk_user)
  VALUES (
    v_post_id,
    JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.title')),
    JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.content')),
    JSON_UNQUOTE(JSON_EXTRACT(p_input, '$.authorId'))
  );

  SELECT * FROM v_post WHERE id = v_post_id;
END;

Note

The function returns a mutation_response composite row. FraiseQL reads the entity JSONB column (on success) or the metadata JSONB column (on error) to build the GraphQL response. The mutation_response type is defined in your database migrations and has the fields: status, message, entity_id, entity_type, entity (JSONB), updated_fields, cascade, and metadata.

---

Input Types

Group related mutation arguments into a dedicated input type rather than using flat argument lists.

Python

@fraiseql.input
class CreatePostInput:
    title: str
    content: str
    author_id: str
    tags: list[str] | None = None

@fraiseql.input
class UpdatePostInput:
    title: str | None = None
    content: str | None = None
    tags: list[str] | None = None
    # Only provided fields are updated

TypeScript

import { Input } from 'fraiseql';

@Input()
class CreatePostInput {
  title: string;
  content: string;
  authorId: string;
  tags?: string[];
}

@Input()
class UpdatePostInput {
  title?: string;
  content?: string;
  tags?: string[];
  // Only provided fields are updated
}

---

Return Values

Single Entity

Return the created or updated entity by querying the view inside the function and embedding it in mutation_response:

-- Return the newly created post via mutation_response
RETURN ROW('success', 'Post created', v_post_id::text, 'Post',
    (SELECT data FROM v_post WHERE id = v_post_id),
    NULL, NULL, NULL)::mutation_response;

Python

@fraiseql.mutation(sql_source="fn_create_post")
def create_post(input: CreatePostInput) -> Post:
    pass

TypeScript

@Mutation()
function createPost(input: CreatePostInput): Post {}

Boolean (Success/Failure)

For operations where you only need confirmation:

-- In the function body
DELETE FROM tb_post WHERE id = (p_input->>'id')::UUID;
IF NOT FOUND THEN
    RETURN ROW('failed:not_found', 'Post not found', NULL, 'Post',
        NULL, NULL, NULL, NULL)::mutation_response;
END IF;
RETURN ROW('success', 'Post deleted', (p_input->>'id'), 'Post',
    NULL, NULL, NULL, NULL)::mutation_response;

Python

@fraiseql.mutation(sql_source="fn_delete_post")
def delete_post(id: str) -> bool:
    pass

TypeScript

@Mutation()
function deletePost(id: string): boolean {}

List Return

For batch operations that affect multiple records:

CREATE OR REPLACE FUNCTION fn_publish_posts(p_input jsonb)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
  v_ids UUID[];
BEGIN
  v_ids := ARRAY(SELECT jsonb_array_elements_text(p_input->'postIds'))::UUID[];

  UPDATE tb_post
  SET is_published = true, published_at = NOW()
  WHERE id = ANY(v_ids)
  AND is_published = false;

  RETURN ROW('success', 'Posts published', NULL, 'Post',
      (SELECT jsonb_agg(data) FROM v_post WHERE id = ANY(v_ids)),
      NULL, NULL, NULL)::mutation_response;
END;
$$;

Python

@fraiseql.mutation(sql_source="fn_publish_posts")
def publish_posts(post_ids: list[str]) -> list[Post]:
    """Publish multiple posts. Returns each updated post."""
    pass

TypeScript

@Mutation()
function publishPosts(postIds: string[]): Post[] {
  /** Publish multiple posts. Returns each updated post. */
}

---

Authentication and Authorization

Protect mutations using role-based access control via the requires_role parameter on @fraiseql.mutation, or restrict individual fields with fraiseql.field(requires_scope=...). Authentication token validation is configured at the server level via environment variables (JWT_SECRET, JWT_ALGORITHM).

Python

import fraiseql
from typing import Annotated
from fraiseql.scalars import Decimal

# Require any authenticated user (role check)
@fraiseql.mutation(sql_source="fn_create_post", operation="CREATE", requires_role="user")
def create_post(input: CreatePostInput) -> Post:
    pass

# Require a specific role for elevated operations
@fraiseql.mutation(sql_source="fn_ban_user", operation="CUSTOM", requires_role="admin")
def ban_user(user_id: str, reason: str) -> bool:
    pass

# Restrict a sensitive field to a specific scope
@fraiseql.type
class User:
    id: str
    name: str
    salary: Annotated[Decimal, fraiseql.field(requires_scope="hr:read")]

TypeScript

import { Mutation } from 'fraiseql';

// Role-based protection is configured in fraiseql.toml [security] section
// See [[security.policies]] and [[security.rules]] for role enforcement.
@Mutation()
function createPost(input: CreatePostInput): Post {}

@Mutation()
function banUser(userId: string, reason: string): boolean {}

Note

There are no @fraiseql.authenticated or @fraiseql.requires_scope standalone decorators. Authentication is enforced at the server level via JWT configuration. Authorization uses requires_role= on @fraiseql.mutation or @fraiseql.type, and fraiseql.field(requires_scope="...") for field-level control.

Injecting JWT Claims into Mutation Parameters

Use inject= to pass verified JWT claims directly to the backing SQL function as server-side parameters. The injected values are invisible to GraphQL clients and cannot be overridden.

@fraiseql.mutation(
    sql_source="fn_create_post",
    operation="CREATE",
    inject={"created_by": "jwt:sub"},
)
def create_post(title: str, content: str) -> Post:
    """Create a post. The author is derived from the JWT, not from client input."""
    pass

The SQL function receives the injected parameter after all client-supplied arguments:

CREATE FUNCTION fn_create_post(
    p_input jsonb,       -- client arguments
    p_created_by TEXT    -- injected from jwt:sub — must be declared last
) RETURNS mutation_response ...

Caution

Injected parameters must be declared last in the SQL function signature. FraiseQL appends them after client arguments — declaring them in the wrong position silently passes values to the wrong parameters.

See Server-Side Injection for the full reference, including session variables, automatic timestamps, and HTTP header injection.

---

Error Handling in PostgreSQL Functions

Use RAISE EXCEPTION to communicate errors back to the GraphQL client. FraiseQL maps these to structured GraphQL errors.

CREATE OR REPLACE FUNCTION fn_update_post(p_input jsonb)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
  v_post_id UUID := (p_input->>'id')::UUID;
BEGIN
  -- Confirm the post exists
  IF NOT EXISTS (SELECT 1 FROM tb_post WHERE id = v_post_id) THEN
    RAISE EXCEPTION 'Post not found'
      USING HINT = 'NOT_FOUND', DETAIL = v_post_id::text;
  END IF;

  -- Confirm the caller owns the post
  IF NOT EXISTS (
    SELECT 1 FROM tb_post
    WHERE id = v_post_id
    AND fk_user = (SELECT pk_user FROM tb_user WHERE id = (p_input->>'callerId')::UUID)
  ) THEN
    RAISE EXCEPTION 'Not authorised to edit this post'
      USING HINT = 'FORBIDDEN';
  END IF;

  UPDATE tb_post
  SET
    title   = COALESCE(p_input->>'title', title),
    content = COALESCE(p_input->>'content', content),
    updated_at = NOW()
  WHERE id = v_post_id;

  RETURN ROW('success', 'Post updated', v_post_id::text, 'Post',
      (SELECT data FROM v_post WHERE id = v_post_id),
      NULL, NULL, NULL)::mutation_response;
END;
$$;

The HINT value surfaces in the GraphQL error extensions so clients can handle error codes programmatically:

{
  "errors": [{
    "message": "Post not found",
    "extensions": { "hint": "NOT_FOUND" },
    "path": ["updatePost"]
  }]
}

---

Complete Mutation Lifecycle

Here is the full end-to-end cycle for createPost — from GraphQL request to JSON response.

GraphQL request:

mutation CreatePost {
  createPost(input: {
    title: "Hello World"
    content: "My first post"
    authorId: "550e8400-e29b-41d4-a716-446655440000"
  }) {
    id
    title
    isPublished
    author { username }
  }
}

What happens internally:

1. FraiseQL validates the authenticated token against the configured JWT settings.
2. FraiseQL calls fn_create_post('{"title":"Hello World","content":"My first post","authorId":"550e8400-e29b-41d4-a716-446655440000"}'::jsonb).
3. The function inserts into tb_post, then returns ROW('success', ..., (SELECT data FROM v_post WHERE id = v_post_id), ...)::mutation_response.
4. FraiseQL reads the entity JSONB column from the mutation_response row and shapes the GraphQL response. If the mutation has cascade enabled and the function returned cascade data, it is included in the response and fed into the cache invalidation pipeline.

JSON response:

{
  "data": {
    "createPost": {
      "id": "550e8400-e29b-41d4-a716-446655440001",
      "title": "Hello World",
      "isPublished": false,
      "author": { "username": "alice" }
    }
  }
}

---

Projection Sync

When a mutation changes data that feeds a materialized projection table (tv_*), the function must explicitly refresh it. Regular views update automatically; materialized tables do not.

-- Full sync for small tables or bulk operations
CREATE OR REPLACE FUNCTION sync_tv_post() RETURNS VOID
LANGUAGE plpgsql AS $$
BEGIN
  DELETE FROM tv_post;
  INSERT INTO tv_post (id, data) SELECT id, data FROM v_post;
END;
$$;

-- Single-record sync for large tables
CREATE OR REPLACE FUNCTION sync_tv_post_single(p_post_id UUID) RETURNS VOID
LANGUAGE plpgsql AS $$
BEGIN
  DELETE FROM tv_post WHERE id = p_post_id;
  INSERT INTO tv_post (id, data) SELECT id, data FROM v_post WHERE id = p_post_id;
END;
$$;

Call the appropriate sync function at the end of each mutation:

-- Inside fn_create_post, after the INSERT
PERFORM sync_tv_post_single(v_post_id);
PERFORM sync_tv_user_single((p_input->>'authorId')::UUID);  -- user's post count changed

When to use each:

Strategy	Use when
Full sync (sync_tv_*)	Table has fewer than 10k rows, or during bulk operations
Single sync (sync_tv_*_single)	Large tables, single-record mutations

---

Transactions

Every PostgreSQL function runs inside a single transaction. If any statement fails, the entire function rolls back — no partial writes.

CREATE OR REPLACE FUNCTION fn_create_order(p_input jsonb)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
  v_order_id UUID;
  v_order_pk BIGINT;
  v_item     jsonb;
BEGIN
  -- Create the order header
  INSERT INTO tb_order (fk_user, status)
  VALUES (
    (SELECT pk_user FROM tb_user WHERE id = (p_input->>'customerId')::UUID),
    'pending'
  )
  RETURNING pk_order, id INTO v_order_pk, v_order_id;

  -- Insert each line item (all-or-nothing)
  FOR v_item IN SELECT * FROM jsonb_array_elements(p_input->'items') LOOP
    INSERT INTO tb_order_item (fk_order, fk_product, quantity)
    VALUES (
      v_order_pk,
      (SELECT pk_product FROM tb_product WHERE id = (v_item->>'productId')::UUID),
      (v_item->>'quantity')::INTEGER
    );
  END LOOP;

  RETURN ROW('success', 'Order created', v_order_id::text, 'Order',
      (SELECT data FROM v_order WHERE id = v_order_id),
      NULL, NULL, NULL)::mutation_response;
END;
$$;

If any item insert fails (unknown product, stock constraint, etc.), the entire transaction rolls back and the order is not created.

---

Change Data Capture

For real-time features — observers, subscriptions, audit logs — FraiseQL uses PostgreSQL LISTEN/NOTIFY. Mutations can emit notifications directly:

-- Notify after a post is published
CREATE OR REPLACE FUNCTION fn_publish_post(p_input jsonb)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
  v_post_id UUID := (p_input->>'id')::UUID;
BEGIN
  UPDATE tb_post
  SET is_published = true, published_at = NOW()
  WHERE id = v_post_id;

  -- Emit change notification for observers / subscriptions
  PERFORM pg_notify(
    'fraiseql_changes',
    jsonb_build_object(
      'entity', 'Post',
      'operation', 'UPDATE',
      'id', v_post_id
    )::text
  );

  RETURN ROW('success', 'Post published', v_post_id::text, 'Post',
      (SELECT data FROM v_post WHERE id = v_post_id),
      NULL, NULL, NULL)::mutation_response;
END;
$$;

FraiseQL listens on the fraiseql_changes channel and routes payloads to any registered observers. See Observers for the full pattern.

Tip

For large-scale CDC pipelines (audit logs, event sourcing), you can also enable PostgreSQL logical replication with CREATE PUBLICATION fraiseql_changes FOR TABLE tb_post, tb_user. Both approaches work with FraiseQL observers.

---

Next Steps

CQRS Pattern

Understand the write vs. read separation that mutations rely on

Observers

React to mutations with webhooks and side effects

Authentication

Configure token validation and scope enforcement

Testing

Test PostgreSQL functions and the full mutation lifecycle