Skip to content

Canonical Code Examples

This document contains the definitive versions of common code examples used throughout FraiseQL documentation. Instead of duplicating these examples, other documentation pages link here.


Database Schema Examples

User Table

Complete example with Trinity identifiers:

CREATE TABLE tb_user (
    -- Trinity identifiers (see: https://docs.fraiseql.com/core/concepts-glossary.md#trinity-identifiers)
    pk_user INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- Internal (fast JOINs)
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,         -- Public API
    identifier TEXT UNIQUE,                                     -- Human-readable slug

    -- User data
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    bio TEXT,
    avatar_url TEXT,

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX idx_user_email ON tb_user(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_user_identifier ON tb_user(identifier) WHERE deleted_at IS NULL;
CREATE INDEX idx_user_created ON tb_user(created_at DESC) WHERE deleted_at IS NULL;

Referenced from:


Post Table

Blog post example with foreign keys:

CREATE TABLE tb_post (
    -- Trinity identifiers
    pk_post INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT UNIQUE,  -- URL slug like "my-first-post"

    -- Foreign keys (use pk_* for performance)
    user_id INT NOT NULL REFERENCES tb_user(pk_user),

    -- Content
    title TEXT NOT NULL,
    content TEXT,
    status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    published_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ
);

-- Indexes
CREATE INDEX idx_post_user ON tb_post(user_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_post_status ON tb_post(status, created_at DESC) WHERE deleted_at IS NULL;
CREATE INDEX idx_post_identifier ON tb_post(identifier) WHERE deleted_at IS NULL;

Referenced from:


PostgreSQL Function Examples

A class mutation calls its function with the input as one jsonb argument (its keys in snake_case), after any context_params, and reads the mutation_response it returns. mutation_response and the mutation_* helpers come from migrations/trinity/005_add_mutation_response.sql. The mutation classes that call these functions are below.

Create User Function

Complete example with validation and error handling:

CREATE OR REPLACE FUNCTION create_user(input jsonb)
RETURNS mutation_response AS $$
DECLARE
    v_user_id UUID;
BEGIN
    -- Validation: Email format
    IF input->>'email' !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RETURN mutation_validation_error('Invalid email format', 'email');
    END IF;

    -- Validation: Name length
    IF length(coalesce(input->>'name', '')) < 2 THEN
        RETURN mutation_validation_error('Name must be at least 2 characters', 'name');
    END IF;

    -- Insert user
    INSERT INTO tb_user (name, email, identifier)
    VALUES (input->>'name', input->>'email', input->>'identifier')
    RETURNING id INTO v_user_id;

    -- Audit log (optional)
    INSERT INTO audit_log (action, details)
    VALUES ('user_created', jsonb_build_object('user_id', v_user_id));

    -- Return success with user data
    RETURN mutation_created(
        'User created successfully',
        (SELECT data FROM v_user WHERE id = v_user_id),
        'User'
    );
EXCEPTION
    WHEN unique_violation THEN
        RETURN mutation_conflict('Email or identifier already exists');
END;
$$ LANGUAGE plpgsql;

Referenced from:


Update User Function

Example updating only the fields the input sets:

CREATE OR REPLACE FUNCTION update_user(input jsonb)
RETURNS mutation_response AS $$
DECLARE
    v_user_id UUID := (input->>'id')::uuid;
BEGIN
    -- Update only provided fields
    UPDATE tb_user
    SET
        name = coalesce(input->>'name', name),
        bio = coalesce(input->>'bio', bio),
        avatar_url = coalesce(input->>'avatar_url', avatar_url),
        updated_at = NOW()
    WHERE id = v_user_id
      AND deleted_at IS NULL;

    IF NOT FOUND THEN
        RETURN mutation_not_found('User', v_user_id::text);
    END IF;

    RETURN mutation_updated(
        'User updated successfully',
        (SELECT data FROM v_user WHERE id = v_user_id),
        ARRAY(SELECT jsonb_object_keys(jsonb_strip_nulls(input - 'id'))),
        'User'
    );
END;
$$ LANGUAGE plpgsql;

Delete User Function (Soft Delete)

Example with cascade considerations:

CREATE OR REPLACE FUNCTION delete_user(input jsonb)
RETURNS mutation_response AS $$
DECLARE
    v_user_id UUID := (input->>'id')::uuid;
BEGIN
    -- Soft delete
    UPDATE tb_user
    SET deleted_at = NOW()
    WHERE id = v_user_id
      AND deleted_at IS NULL;

    IF NOT FOUND THEN
        RETURN mutation_not_found('User', v_user_id::text);
    END IF;

    RETURN mutation_deleted('User deleted successfully', v_user_id::text, 'User');
END;
$$ LANGUAGE plpgsql;

Mutation Classes

The class mutations that call the functions above (each calls the function named after it in snake_case: CreateUser calls create_user):

import fraiseql
from fraiseql.types import ID

@fraiseql.input
class CreateUserInput:
    name: str
    email: str
    identifier: str | None = None

@fraiseql.input
class UpdateUserInput:
    id: ID
    name: str | None = None
    bio: str | None = None
    avatar_url: str | None = None

@fraiseql.input
class DeleteUserInput:
    id: ID

@fraiseql.success
class CreateUserSuccess:
    user: User

@fraiseql.success
class UpdateUserSuccess:
    user: User

@fraiseql.success
class DeleteUserSuccess:
    # Filled from the mutation_response's entity_id. @fraiseql.success adds id
    # only to a type with an entity field, and this one has none.
    id: ID | None = None

@fraiseql.error
class UserError:
    field: str | None = None  # From the validation error's metadata

@fraiseql.mutation
class CreateUser:
    input: CreateUserInput
    success: CreateUserSuccess
    error: UserError

@fraiseql.mutation
class UpdateUser:
    input: UpdateUserInput
    success: UpdateUserSuccess
    error: UserError

@fraiseql.mutation
class DeleteUser:
    input: DeleteUserInput
    success: DeleteUserSuccess
    error: UserError

GraphQL Type Examples

User Type

Complete example with Trinity identifiers:

import fraiseql
from fraiseql.types import ID
from datetime import datetime

@fraiseql.type(sql_source="v_user")
class User:
    """User account with Trinity identifiers."""
    # Trinity identifiers (only public ones exposed)
    id: ID          # Public API identifier
    identifier: str   # Human-readable slug
    # pk_user is NOT exposed (internal only)

    # User data
    name: str
    email: str
    bio: str | None
    avatar_url: str | None

    # Timestamps
    created_at: datetime
    updated_at: datetime

Referenced from:


Post Type with Relations

Example with nested data:

import fraiseql
from fraiseql.types import ID
from datetime import datetime

@fraiseql.type(sql_source="v_post")
class Post:
    """Blog post with author relation."""
    # Trinity identifiers
    id: ID
    identifier: str

    # Content
    title: str
    content: str | None
    status: str

    # Relations (embedded in JSONB or via dataloader)
    author: User

    # Timestamps
    created_at: datetime
    updated_at: datetime
    published_at: datetime | None

GraphQL Query Examples

Basic Query

Fetch all users:

query {
  users {
    id
    name
    email
  }
}

Query with WHERE Filters

Filter by status:

query {
  posts(where: {
    status: { eq: "published" }
    created_at: { gte: "2024-01-01" }
  }) {
    id
    title
    author {
      name
    }
  }
}

Query with Pagination

Relay-style cursor pagination:

query {
  posts(first: 10, after: "cursor123") {
    nodes {
      id
      title
    }
    pageInfo {
      hasNextPage
      endCursor
    }
    totalCount
  }
}

Mutation Example

Create user mutation:

mutation {
  createUser(input: {
    name: "Alice"
    email: "alice@example.com"
    identifier: "alice"
  }) {
    __typename
    ... on CreateUserSuccess {
      user {
        id
        name
        email
      }
      message
    }
    ... on UserError {
      message
      code
      field
    }
  }
}

View Examples

Basic JSONB View

Simple view without relations:

CREATE VIEW v_user AS
SELECT
    id,  -- Direct column for WHERE filtering
    jsonb_build_object(
        'id', id,
        'identifier', identifier,
        'name', name,
        'email', email,
        'bio', bio,
        'avatar_url', avatar_url,
        'created_at', created_at,
        'updated_at', updated_at
    ) as data
FROM tb_user
WHERE deleted_at IS NULL;

Referenced from:


JSONB View with Relations

View with embedded nested data:

CREATE VIEW v_post_with_author AS
SELECT
    p.id,
    jsonb_build_object(
        'id', p.id,
        'identifier', p.identifier,
        'title', p.title,
        'content', p.content,
        'status', p.status,
        'author', (
            SELECT jsonb_build_object(
                'id', u.id,
                'name', u.name,
                'email', u.email
            )
            FROM tb_user u
            WHERE u.pk_user = p.user_id
        ),
        'created_at', p.created_at,
        'published_at', p.published_at
    ) as data
FROM tb_post p
WHERE p.deleted_at IS NULL;

Projection Table (tv_*)

High-performance cached table:

-- Projection table (regular table with cached JSONB)
CREATE TABLE tv_user (
    id UUID PRIMARY KEY,
    data JSONB NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sync function (must be called explicitly)
CREATE FUNCTION fn_sync_tv_user(p_id UUID) RETURNS VOID AS $$
BEGIN
    INSERT INTO tv_user (id, data)
    SELECT id, data FROM v_user WHERE id = p_id
    ON CONFLICT (id) DO UPDATE SET
        data = EXCLUDED.data,
        updated_at = NOW();
END;
$$ LANGUAGE plpgsql;

-- Usage in mutation (explicit sync required)
CREATE FUNCTION create_user(input jsonb) RETURNS mutation_response AS $$
DECLARE v_user_id UUID;
BEGIN
    INSERT INTO tb_user (...) VALUES (...) RETURNING id INTO v_user_id;

    -- ✅ Explicitly sync to projection table
    PERFORM fn_sync_tv_user(v_user_id);

    RETURN mutation_created(
        'User created', (SELECT data FROM tv_user WHERE id = v_user_id), 'User'
    );
END;
$$ LANGUAGE plpgsql;

Referenced from:


Usage Guidelines

When referencing these examples:

✅ DO:

  • Link to this page instead of copying
  • Use relative links: [example](../examples/canonical-examples.md#user-table)
  • Mention which section you're referring to

❌ DON'T:

  • Copy/paste entire examples into your documentation
  • Modify examples inline (create variations in your own doc instead)
  • Create new "canonical" examples elsewhere

If you need a variation:

  1. Link to the canonical example first
  2. Show only the difference:
Based on the [canonical User table](../examples/canonical-examples.md#user-table),
add a `role` column:

```sql
ALTER TABLE tb_user ADD COLUMN role TEXT DEFAULT 'user';
```

Maintaining This File

Guidelines for contributors:

  • Keep examples complete and runnable
  • Include comments explaining non-obvious parts
  • Show best practices (error handling, validation, etc.)
  • Update "Referenced from" sections when adding new links
  • Test examples before committing
  • Follow FraiseQL terminology guide

When to add a new example:

  • When you find the same pattern in 3+ documentation files
  • When creating a new tutorial that needs a standard example
  • When a common question needs a definitive answer

When NOT to add:

  • One-off examples specific to a single guide
  • Variations of existing examples (link to original + show diff)
  • Deprecated patterns

Last Updated: 2025-12-30 Maintained By: FraiseQL documentation team