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: - Core Concepts Glossary - Trinity Pattern Guide - Blog API Tutorial

---

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: - Blog API Tutorial - Database Patterns

---

PostgreSQL Function Examples

Create User Function

Complete example with validation and error handling:

CREATE OR REPLACE FUNCTION fn_create_user(
    p_name TEXT,
    p_email TEXT,
    p_identifier TEXT DEFAULT NULL
) RETURNS JSONB AS $$
DECLARE
    v_user_id UUID;
BEGIN
    -- Validation: Email format
    IF p_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$' THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Invalid email format',
            'code', 'INVALID_EMAIL'
        );
    END IF;

    -- Validation: Name length
    IF LENGTH(p_name) < 2 THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Name must be at least 2 characters',
            'code', 'INVALID_NAME'
        );
    END IF;

    -- Insert user
    INSERT INTO tb_user (name, email, identifier)
    VALUES (p_name, p_email, p_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 jsonb_build_object(
        'success', true,
        'user', (SELECT data FROM v_user WHERE id = v_user_id),
        'message', 'User created successfully'
    );
EXCEPTION
    WHEN unique_violation THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Email or identifier already exists',
            'code', 'DUPLICATE_USER'
        );
    WHEN OTHERS THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', SQLERRM,
            'code', 'DATABASE_ERROR'
        );
END;
$$ LANGUAGE plpgsql;

Referenced from: - Mutation SQL Requirements - Getting Started Quickstart - Blog API Tutorial

---

Update User Function

Example with optimistic locking:

CREATE OR REPLACE FUNCTION fn_update_user(
    p_id UUID,
    p_name TEXT DEFAULT NULL,
    p_bio TEXT DEFAULT NULL,
    p_avatar_url TEXT DEFAULT NULL
) RETURNS JSONB AS $$
DECLARE
    v_updated_rows INT;
BEGIN
    -- Update only provided fields
    UPDATE tb_user
    SET
        name = COALESCE(p_name, name),
        bio = COALESCE(p_bio, bio),
        avatar_url = COALESCE(p_avatar_url, avatar_url),
        updated_at = NOW()
    WHERE id = p_id
      AND deleted_at IS NULL;

    GET DIAGNOSTICS v_updated_rows = ROW_COUNT;

    IF v_updated_rows = 0 THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'User not found',
            'code', 'NOT_FOUND'
        );
    END IF;

    RETURN jsonb_build_object(
        'success', true,
        'user', (SELECT data FROM v_user WHERE id = p_id),
        'message', 'User updated successfully'
    );
END;
$$ LANGUAGE plpgsql;

---

Delete User Function (Soft Delete)

Example with cascade considerations:

CREATE OR REPLACE FUNCTION fn_delete_user(
    p_id UUID
) RETURNS JSONB AS $$
DECLARE
    v_deleted_rows INT;
BEGIN
    -- Soft delete
    UPDATE tb_user
    SET deleted_at = NOW()
    WHERE id = p_id
      AND deleted_at IS NULL;

    GET DIAGNOSTICS v_deleted_rows = ROW_COUNT;

    IF v_deleted_rows = 0 THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'User not found or already deleted',
            'code', 'NOT_FOUND'
        );
    END IF;

    RETURN jsonb_build_object(
        'success', true,
        'message', 'User deleted successfully'
    );
END;
$$ LANGUAGE plpgsql;

---

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: - Core Concepts Glossary - Types & Schema - Getting Started

---

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"
  }) {
    success {
      user {
        id
        name
        email
      }
      message
    }
    failure {
      message
      code
    }
  }
}

---

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: - Core Concepts Glossary - Database API

---

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 fn_create_user(...) RETURNS JSONB 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 (SELECT data FROM tv_user WHERE id = v_user_id);
END;
$$ LANGUAGE plpgsql;

Referenced from: - Core Concepts Glossary - Database Patterns

---

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