Skip to content
FraiseQL v2.0.0-alpha

CQRS Pattern

FraiseQL uses Command Query Responsibility Segregation (CQRS) at the database level. You separate read and write operations into distinct structures that you design and own: normalized tables (tb_*) for writes, JSONB views (v_*) for reads.

Why CQRS?

Section titled “Why CQRS?”

Traditional APIs couple reads and writes to the same tables. A single tb_user table must serve both fast reads and safe writes — and it does neither well:

The problem: one table serves conflicting needs

graph LR
    API[API Layer] --> T[(tb_user table)]
    T --> R[Read: SELECT *]
    T --> W[Write: INSERT/UPDATE]
    R -.->|conflicts| W

FraiseQL separates these concerns at compile time. Write tables handle normalization and constraints. Read views handle denormalization and speed. An auto-sync bridge keeps them consistent:

The solution: separated at compile time

graph LR
    W[Write Path] --> TB[(tb_* Tables)]
    TB --> S[sync_tv_*()]
    S --> TV[(tv_* Projections)]
    TV --> V[v_* Views]
    V --> R[Read Path]

What this separation buys you

Section titled “What this separation buys you”
  • Adding a GraphQL field = add a column to the view. No migration needed, no type regeneration.
  • Renaming a field = rename in the view, not in the table. No data change, no FK cascade.
  • A slow query = add an index or rewrite the view. The API surface stays stable.
  • A FraiseQL upgrade = recompile. Your SQL doesn’t change.
  • A table schema change = invisible to API clients, who only see what the view exposes.

The cost: you write views. The payoff: the API is decoupled from the storage schema, and you can evolve both independently.

CQRS Separation

Section titled “CQRS Separation”

Here is the full picture — normalized tables on the write side, denormalized JSONB views on the read side, connected by an auto-sync bridge:

graph LR
    M[GraphQL Mutation] --> FN[fn_* Function]
    FN --> TB[(tb_* Write Tables)]
    TB --> SYNC[sync_tv_*()]
    SYNC --> TV[(tv_* Materialized)]
    TV --> V[v_* Read Views]
    V --> Q[GraphQL Query]

Naming Conventions

Section titled “Naming Conventions”

FraiseQL expects strict prefixes to identify each layer:

Write Side (Tables)

Section titled “Write Side (Tables)”
PrefixPurposeExample
tb_Base table for writestb_user, tb_post, tb_order
-- Write table: normalized, constrained
CREATE TABLE tb_user (
    -- Trinity Identifiers
    pk_user BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT UNIQUE NOT NULL,


    -- Normalized data
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    bio TEXT,


    -- Metadata
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Read Side (Views)

Section titled “Read Side (Views)”
PrefixPurposeExample
v_View for readsv_user, v_post
tv_Table-backed view (materialized)tv_user_profile
-- Read view: denormalized, JSONB response
CREATE OR REPLACE VIEW v_user AS
SELECT
    u.id,
    jsonb_build_object(
        'id', u.id::text,
        'identifier', u.identifier,
        'email', u.email,
        'name', u.name,
        'bio', u.bio,
        'created_at', u.created_at,
        'updated_at', u.updated_at
    ) AS data
FROM tb_user u;

Materialized Read Tables

Section titled “Materialized Read Tables”

For high-traffic reads, use table-backed views (tv_):

-- Materialized projection table
CREATE TABLE tv_user (
    id UUID PRIMARY KEY,
    data JSONB
);


-- Sync function
CREATE OR REPLACE FUNCTION sync_tv_user() RETURNS VOID AS $$
BEGIN
    DELETE FROM tv_user;
    INSERT INTO tv_user (id, data)
    SELECT id, data FROM v_user;
END;
$$ LANGUAGE plpgsql;

The Data Column Pattern

Section titled “The Data Column Pattern”

Every read view exposes a data column containing the complete GraphQL response:

SELECT data FROM v_user WHERE id = '...';

Returns:

{
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "identifier": "john.doe@example.com",
    "email": "john.doe@example.com",
    "name": "John Doe",
    "bio": "Software engineer",
    "created_at": "2024-01-15T10:30:00Z",
    "updated_at": "2024-01-15T10:30:00Z"
}

Why JSONB?

  • Single column contains complete response
  • No runtime JSON serialization needed
  • PostgreSQL optimizes JSONB operations
  • Nested objects compose naturally

Trinity Identifier Pattern

Section titled “Trinity Identifier Pattern”

Each write table uses three identifier columns:

ColumnTypePurpose
pk_BIGINTInternal primary key (never exposed)
idUUIDExternal identifier (exposed in API)
identifierTEXTHuman-readable slug/code
CREATE TABLE tb_post (
    -- Trinity Identifiers
    pk_post INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT UNIQUE NOT NULL,


    -- Foreign key uses internal pk
    fk_user INTEGER NOT NULL REFERENCES tb_user(pk_user),


    -- Post data...
);

Why three identifiers?

  1. pk_ (Internal): Fast integer JOINs, never changes, never exposed
  2. id (External): Secure UUID for API access, no enumeration attacks
  3. identifier (Human): Readable slugs for URLs, user-facing references

Foreign Keys

Section titled “Foreign Keys”

Foreign keys always reference the internal pk_ column:

-- Foreign key pattern
fk_user INTEGER NOT NULL REFERENCES tb_user(pk_user)

Naming convention: fk_{entity} references pk_{entity}

This gives you:

  • Fast integer JOINs internally
  • UUID security externally
  • No exposure of database internals

Write Operations

Section titled “Write Operations”

Writes go through SQL functions that:

  1. Validate input
  2. Insert/update tb_ tables
  3. Sync tv_ projections
CREATE OR REPLACE FUNCTION create_user(
    user_email TEXT,
    user_name TEXT,
    user_bio TEXT DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
    new_user_id UUID;
BEGIN
    -- Validation
    IF user_email IS NULL OR user_name IS NULL THEN
        RAISE EXCEPTION 'Email and name are required';
    END IF;


    -- Check duplicate
    IF EXISTS (SELECT 1 FROM tb_user WHERE email = user_email) THEN
        RAISE EXCEPTION 'User with email % already exists', user_email;
    END IF;


    -- Create user
    INSERT INTO tb_user (email, name, bio, identifier)
    VALUES (user_email, user_name, user_bio, user_email)
    RETURNING id INTO new_user_id;


    -- Sync projection
    PERFORM sync_tv_user();


    RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;

Read Operations

Section titled “Read Operations”

Reads query v_ or tv_ views directly:

-- Single entity
SELECT data FROM v_user WHERE id = $1;


-- List with pagination
SELECT data FROM v_user
ORDER BY data->>'created_at' DESC
LIMIT $1 OFFSET $2;


-- With filtering
SELECT data FROM v_user
WHERE data->>'is_active' = 'true'
LIMIT 10;

Complete Example

Section titled “Complete Example”

Here’s the full CQRS structure for a blog post:

-- WRITE: Base table
CREATE TABLE tb_post (
    pk_post INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    identifier TEXT UNIQUE NOT NULL,
    fk_user INTEGER NOT NULL REFERENCES tb_user(pk_user),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    is_published BOOLEAN DEFAULT false,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);


-- READ: View with nested author
CREATE OR REPLACE VIEW v_post AS
SELECT
    p.id,
    jsonb_build_object(
        'id', p.id::text,
        'identifier', p.identifier,
        'title', p.title,
        'slug', p.slug,
        'content', p.content,
        'is_published', p.is_published,
        'published_at', p.published_at,
        'created_at', p.created_at,
        'updated_at', p.updated_at,
        'author', vu.data
    ) AS data
FROM tb_post p
JOIN tb_user u ON u.pk_user = p.fk_user
JOIN v_user vu ON vu.id = u.id;


-- READ: Materialized projection
CREATE TABLE tv_post (
    id UUID PRIMARY KEY,
    data JSONB
);

When to Use tv_ Tables

Section titled “When to Use tv_ Tables”

Use tv_ (table-backed views) when:

  • High read frequency: The view is queried thousands of times per second
  • Complex JOINs: The view involves multiple table joins
  • Stable data: Data changes infrequently
  • Large datasets: The underlying tables have millions of rows

Stick with v_ views when:

  • Data changes frequently
  • Real-time accuracy is required
  • The view is simple (1-2 table JOINs)

Python Integration

Section titled “Python Integration”

Define your GraphQL types with decorators that map to your CQRS structure:

import fraiseql


@fraiseql.type
class User:
    """Maps to v_user / tv_user"""
    id: str
    identifier: str
    email: str
    name: str
    bio: str | None
    created_at: str
    updated_at: str


@fraiseql.query(sql_source="v_user")
def users(limit: int = 10, offset: int = 0) -> list[User]:
    """Read from v_user view"""
    pass


@fraiseql.mutation(sql_source="fn_create_user", operation="CREATE")
def create_user(name: str, email: str) -> User:
    """Write through create_user function"""
    pass

Verify It Works

Section titled “Verify It Works”

  1. Create the write table and read view:

    -- PostgreSQL example
    CREATE TABLE tb_test_item (
        pk_item INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
        id UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
        name TEXT NOT NULL,
        value INTEGER NOT NULL
    );
    

    CREATE OR REPLACE VIEW v_test_item AS
    SELECT
        id,
        jsonb_build_object(
            'id', id::text,
            'name', name,
            'value', value
        ) AS data
    FROM tb_test_item;

  2. Create a write function:

    CREATE OR REPLACE FUNCTION fn_create_test_item(p_input jsonb)
    RETURNS SETOF v_test_item
    LANGUAGE plpgsql AS $$
    DECLARE
        v_item_id UUID;
    BEGIN
        INSERT INTO tb_test_item (name, value)
        VALUES (p_input->>'name', (p_input->>'value')::INTEGER)
        RETURNING id INTO v_item_id;
    

        RETURN QUERY SELECT * FROM v_test_item WHERE id = v_item_id;
    END;
    $$;

  3. Test the write path:

    Terminal window
    curl -X POST http://localhost:8080/graphql \
      -H "Content-Type: application/json" \
      -d '{
        "query": "mutation { createTestItem(input: { name: \"Test\", value: 42 }) { id name value } }"
      }'

    Expected response:

    {
      "data": {
        "createTestItem": {
          "id": "550e8400-e29b-41d4-a716-446655440000",
          "name": "Test",
          "value": 42
        }
      }
    }

  4. Verify data in write table:

    SELECT * FROM tb_test_item;
    -- Should show the normalized row

  5. Verify data in read view:

    SELECT data FROM v_test_item WHERE id = '550e8400-...';
    -- Should return JSONB: {"id": "...", "name": "Test", "value": 42}

  6. Test the read query:

    Terminal window
    curl -X POST http://localhost:8080/graphql \
      -H "Content-Type: application/json" \
      -d '{"query": "{ testItems { id name value } }"}'

    Expected response:

    {
      "data": {
        "testItems": [
          {
            "id": "550e8400-e29b-41d4-a716-446655440000",
            "name": "Test",
            "value": 42
          }
        ]
      }
    }

Troubleshooting

Section titled “Troubleshooting”

View Returns No Data

Section titled “View Returns No Data”

  1. Check view definition:

    -- PostgreSQL
    \d+ v_test_item
    

    -- Check the query plan
    EXPLAIN ANALYZE SELECT data FROM v_test_item;

  2. Verify the data column:

    SELECT data FROM v_test_item LIMIT 1;
    -- Should return valid JSON/JSONB, not NULL

Write Function Fails

Section titled “Write Function Fails”

  1. Test function directly in SQL:

    SELECT * FROM fn_create_test_item('{"name": "Test", "value": 42}'::jsonb);

  2. Check for constraint violations:

    -- Check if UUID already exists
    SELECT id FROM tb_test_item WHERE id = 'conflicting-uuid';

Performance Issues

Section titled “Performance Issues”

  1. Check for missing indexes on tb_* tables:

    -- For UUID lookups
    CREATE INDEX idx_tb_item_id ON tb_test_item(id);
    

    -- For foreign keys
    CREATE INDEX idx_tb_item_fk ON tb_test_item(fk_other);

  2. Analyze query plans:

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT data FROM v_test_item
    WHERE data->>'name' = 'Test';

Next Steps

Section titled “Next Steps”