Skip to content
FraiseQL v2.0.0-alpha

Adding Mutations

This guide picks up where Your First API left off. You have a working blog API with queries. Now you add the write path: database functions that validate and persist data, and GraphQL mutations that call them.

How Mutations Work in FraiseQL

Section titled “How Mutations Work in FraiseQL”

Each mutation follows the same path:

  1. GraphQL receives the mutation and validates the input type
  2. FraiseQL calls the mapped database function with the input values
  3. The DB function validates, inserts or updates, and returns the row’s id
  4. FraiseQL queries the view by that id and returns the shaped response

Your schema file defines what the mutation accepts. The SQL function enforces invariants. The view resolves the response.

Step 1: Write the Database Functions

Section titled “Step 1: Write the Database Functions”

Create mutation functions in db/schema/03_mutations/. Each function validates its inputs, performs the write, and returns the id so FraiseQL can resolve the response through the existing view.

db/schema/03_mutations/fn_create_user.sql
CREATE OR REPLACE FUNCTION fn_create_user(
    p_username TEXT, p_email TEXT, p_bio TEXT DEFAULT NULL
) RETURNS UUID LANGUAGE plpgsql AS $$
DECLARE v_id UUID;
BEGIN
    /*validate: no duplicate email*/
    IF EXISTS (SELECT 1 FROM tb_user WHERE email = p_email) THEN
        RAISE EXCEPTION 'email already registered: %', p_email;
    END IF;
    INSERT INTO tb_user (identifier, username, email, bio)
    VALUES ('usr_' || lower(regexp_replace(p_username, '[^a-z0-9]', '_', 'gi')),
            p_username, p_email, p_bio)
    RETURNING id INTO v_id;
    RETURN v_id;
END; $$;
db/schema/03_mutations/fn_create_post.sql
CREATE OR REPLACE FUNCTION fn_create_post(
    p_title TEXT, p_content TEXT, p_author_id UUID
) RETURNS UUID LANGUAGE plpgsql AS $$
DECLARE v_fk_user BIGINT; v_slug TEXT; v_id UUID;
BEGIN
    /*validate: author exists*/
    SELECT pk_user INTO v_fk_user FROM tb_user WHERE id = p_author_id;
    IF v_fk_user IS NULL THEN
        RAISE EXCEPTION 'author not found: %', p_author_id;
    END IF;
    v_slug := lower(trim(both '-' FROM regexp_replace(p_title, '[^a-zA-Z0-9]+', '-', 'g')));
    INSERT INTO tb_post (identifier, fk_user, title, slug, content)
    VALUES ('pst_' || v_slug, v_fk_user, p_title, v_slug, p_content)
    RETURNING id INTO v_id;
    RETURN v_id;
END; $$;
db/schema/03_mutations/fn_publish_post.sql
CREATE OR REPLACE FUNCTION fn_publish_post(p_post_id UUID)
RETURNS UUID LANGUAGE plpgsql AS $$
DECLARE v_id UUID;
BEGIN
    UPDATE tb_post
    SET is_published = true, updated_at = NOW()
    WHERE id = p_post_id
    RETURNING id INTO v_id;
    IF v_id IS NULL THEN
        RAISE EXCEPTION 'post not found: %', p_post_id;
    END IF;
    RETURN v_id;
END; $$;

Step 2: Map to GraphQL

Section titled “Step 2: Map to GraphQL”

Add input types and mutation definitions to your schema file. For "custom" mutations, sql_source names the stored procedure. For auto-generated SQL ("create" / "update" / "delete"), sql_source sets the target table name — defaults to the return type lowercased if omitted. The return type always resolves through the existing view.

schema.py
# Add to schema.py — User and Post types are already defined


@fraiseql.input
class CreateUserInput:
    username: str
    email: Email
    bio: str | None = None


@fraiseql.input
class CreatePostInput:
    title: str
    content: str
    author_id: ID


@fraiseql.input
class PublishPostInput:
    post_id: ID


@fraiseql.mutation(sql_source="fn_create_user", operation="custom")
def create_user(input: CreateUserInput) -> User:
    """Create a new user."""
    pass


@fraiseql.mutation(sql_source="fn_create_post", operation="custom")
def create_post(input: CreatePostInput) -> Post:
    """Create a new post."""
    pass


@fraiseql.mutation(sql_source="fn_publish_post", operation="custom")
def publish_post(input: PublishPostInput) -> Post:
    """Publish a post."""
    pass

Step 3: Handle updated_at

Section titled “Step 3: Handle updated_at”

How updated_at is managed depends on which mutation style you use.

For stored-procedure ("custom") mutations, the function body controls the timestamp. Set it explicitly in any mutation that modifies a row:

DatabaseExpression
PostgreSQLupdated_at = NOW()
MySQLHandled automatically via ON UPDATE CURRENT_TIMESTAMP — no action needed
SQL Serverupdated_at = SYSDATETIMEOFFSET()

For auto-generated SQL ("create" / "update") mutations, FraiseQL never writes updated_at automatically regardless of database. Your options are:

  • Add a database trigger on the table that sets updated_at on every write (recommended)
  • Include updated_at as a field in the input type and pass the value from the client

Step 4: Build and Test

Section titled “Step 4: Build and Test”

Rebuild to pick up the new function files:

Terminal window
confiture build --env local
✓ Schema built successfully in 0.5s
  → Created 3 tables, 3 views, 3 functions

Recompile and serve:

Terminal window
fraiseql run

Open the GraphQL Playground at http://localhost:8080/graphql.

createUser

Section titled “createUser”
mutation {
  createUser(input: { username: "alice", email: "alice@example.com" }) {
    id
    username
    email
  }
}
{ "data": { "createUser": { "id": "a1b2c3d4-0001-0001-0001-000000000001", "username": "alice", "email": "alice@example.com" } } }

createPost

Section titled “createPost”
mutation {
  createPost(input: {
    title: "Hello World"
    content: "This is my first post."
    authorId: "a1b2c3d4-0001-0001-0001-000000000001"
  }) {
    id
    title
    slug
    author { username }
  }
}
{ "data": { "createPost": { "id": "b2c3d4e5-0002-0002-0002-000000000002", "title": "Hello World", "slug": "hello-world", "author": { "username": "alice" } } } }

publishPost

Section titled “publishPost”
mutation {
  publishPost(input: { postId: "b2c3d4e5-0002-0002-0002-000000000002" }) {
    id
    title
    isPublished
  }
}
{ "data": { "publishPost": { "id": "b2c3d4e5-0002-0002-0002-000000000002", "title": "Hello World", "isPublished": true } } }

The response data comes from the view — v_post is queried by the id the function returns. The same view that serves read queries also serves mutation responses.

What’s Next

Section titled “What’s Next”