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:
- Link to the canonical example first
- 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