Multi-Tenancy Architecture

Note

This page covers features that require PostgreSQL. Some capabilities (JSONB, Row-Level Security, materialized views, pg_trgm) are not available in MySQL, SQLite, or SQL Server. See the database guides for alternatives.

FraiseQL has native multi-tenancy support. Configure a strategy in fraiseql.toml and FraiseQL handles tenant routing, context injection, and data isolation automatically — no manual session-setting code required.

Caution

[multitenancy] TOML configuration is unverified. The [multitenancy] namespace shown throughout this page is not listed as a confirmed-valid fraiseql.toml section. The PostgreSQL patterns (RLS, schema-per-tenant, database-per-tenant), the middleware examples, and the security guidance are all accurate. The TOML configuration blocks are aspirational.

For guaranteed-correct configuration, see the TOML Config Reference.

Choose a Strategy

Strategy	Isolation	Cost	Best For
Row-Level (row)	Rows filtered by policy	Lowest	High-volume SaaS
Schema-per-Tenant (schema)	Separate PostgreSQL schemas	Medium	Mid-market SaaS
Database-per-Tenant (database)	Dedicated database per tenant	Highest	Enterprise / regulated

---

1. Row-Level Security

All tenants share one database and one schema. PostgreSQL Row-Level Security policies enforce data isolation at the database layer.

Configure FraiseQL

fraiseql.toml

[multitenancy]
enabled       = true
strategy      = "row"
tenant_header = "X-Tenant-ID"

FraiseQL reads the X-Tenant-ID header from each request and sets app.tenant_id as a PostgreSQL session variable automatically. Your RLS policies reference that variable.

Set Up PostgreSQL RLS

-- 1. Add tenant_id column (if not already present)
ALTER TABLE tb_post ADD COLUMN tenant_id UUID NOT NULL;
ALTER TABLE tb_user ADD COLUMN tenant_id UUID NOT NULL;

-- 2. Create RLS policies
CREATE POLICY post_tenant_isolation ON tb_post
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY user_tenant_isolation ON tb_user
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- 3. Enable RLS
ALTER TABLE tb_post ENABLE ROW LEVEL SECURITY;
ALTER TABLE tb_user ENABLE ROW LEVEL SECURITY;

FraiseQL sets app.tenant_id before each query — your policies fire automatically. No application code needed.

Define Types

Python

import fraiseql
from uuid import UUID

@fraiseql.type
class Post:
    id: UUID
    tenant_id: UUID
    title: str
    content: str

@fraiseql.query(sql_source="v_post")
def posts(limit: int = 20) -> list[Post]:
    """Returns posts for the current tenant (enforced by RLS)."""
    pass

@fraiseql.mutation
def create_post(info, title: str, content: str) -> Post:
    tenant_id = info.context["tenant_id"]
    # INSERT INTO tb_post (tenant_id, title, content) VALUES ($1, $2, $3)
    ...

TypeScript

import { fraiseqlType, fraiseqlQuery, fraiseqlMutation } from 'fraiseql';

@fraiseqlType()
class Post {
  id: string;
  tenantId: string;
  title: string;
  content: string;
}

@fraiseqlQuery({ sqlSource: 'v_post' })
function posts(limit = 20): Post[] {
  // Returns posts for the current tenant (enforced by RLS)
}

Extract Tenant from JWT

When your tenant ID comes from a JWT claim rather than a raw header, use middleware:

Python

@fraiseql.middleware
def extract_tenant(request, next):
    """Extract tenant_id from JWT and make it available to resolvers."""
    tenant_id = request.auth.claims.get("tenant_id")
    if not tenant_id:
        raise PermissionError("Missing tenant context")
    request.context["tenant_id"] = tenant_id
    return next(request)

TypeScript

import { fraiseqlMiddleware } from 'fraiseql';

fraiseqlMiddleware(async (request, next) => {
  const tenantId = request.auth.claims?.tenant_id;
  if (!tenantId) throw new Error('Missing tenant context');
  request.context.tenantId = tenantId;
  return next(request);
});

---

2. Schema-per-Tenant

Each tenant gets a dedicated PostgreSQL schema. Tables are identical across schemas; the search path routes queries to the correct one.

Configure FraiseQL

fraiseql.toml

[multitenancy]
enabled       = true
strategy      = "schema"
tenant_header = "X-Tenant-ID"

FraiseQL sets search_path = tenant_{id} for each request automatically.

Set Up Schemas

-- Create schema for a new tenant
CREATE SCHEMA tenant_abc123;

-- Create tables in that schema
CREATE TABLE tenant_abc123.tb_post (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title      TEXT NOT NULL,
    content    TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Grant permissions to the application role
GRANT USAGE ON SCHEMA tenant_abc123 TO fraiseql_app;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA tenant_abc123 TO fraiseql_app;

Provisioning New Tenants

Python

@fraiseql.mutation
@requires_scope("admin:tenants")
async def provision_tenant(info, name: str, plan: str) -> Tenant:
    """Create schema, tables, and register the new tenant."""
    tenant_id = generate_id()
    # 1. Create schema via admin connection
    await info.context["admin_db"].execute(
        f"CREATE SCHEMA tenant_{tenant_id}"
    )
    # 2. Run migrations into new schema
    await run_migrations(schema=f"tenant_{tenant_id}")
    # 3. Register in tenants table
    return await create_tenant_record(tenant_id, name, plan)

TypeScript

import { mutation, requiresScope } from 'fraiseql';

@mutation({ sqlFunction: 'fn_provision_tenant' })
@requiresScope('admin:tenants')
async function provisionTenant(name: string, plan: string): Promise<Tenant> {
  const tenantId = generateId();
  // 1. Create schema via admin connection
  await adminDb.query(`CREATE SCHEMA tenant_${tenantId}`);
  // 2. Run migrations into new schema
  await runMigrations({ schema: `tenant_${tenantId}` });
  // 3. Register in tenants table
  return createTenantRecord(tenantId, name, plan);
}

Go

package mutations

import (
    "context"
    "fmt"

    "github.com/fraiseql/fraiseql-go"
)

func ProvisionTenant(ctx context.Context, name, plan string) (*Tenant, error) {
    tenantID := generateID()
    // 1. Create schema via admin connection
    if _, err := adminDB.ExecContext(ctx,
        fmt.Sprintf("CREATE SCHEMA tenant_%s", tenantID)); err != nil {
        return nil, err
    }
    // 2. Run migrations into new schema
    if err := runMigrations(ctx, fmt.Sprintf("tenant_%s", tenantID)); err != nil {
        return nil, err
    }
    // 3. Register in tenants table
    return createTenantRecord(ctx, tenantID, name, plan)
}

Tip

Keep a public.tenants table as a registry. FraiseQL looks up tenant schemas from there when routing requests.

---

3. Database-per-Tenant

Each tenant has a dedicated PostgreSQL database. FraiseQL resolves the correct connection string from a routing table.

Configure FraiseQL

fraiseql.toml

[multitenancy]
enabled       = true
strategy      = "database"
tenant_header = "X-Tenant-ID"

[multitenancy.routing]
# FraiseQL queries this table to resolve tenant → database URL
registry_url = "${REGISTRY_DATABASE_URL}"
registry_table = "tenant_databases"

Routing Table Schema

-- In your central registry database
CREATE TABLE tenant_databases (
    tenant_id    UUID PRIMARY KEY,
    database_url TEXT NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

FraiseQL caches the routing table in memory and refreshes it on a configurable interval.

Provisioning New Databases

Python

@fraiseql.mutation
@requires_scope("admin:tenants")
async def provision_tenant(info, name: str, plan: str) -> Tenant:
    """Provision a new tenant database and register it."""
    tenant_id = generate_id()
    db_name = f"fraiseql_tenant_{tenant_id}"

    # 1. Create and migrate new database
    await create_database(db_name)
    await run_migrations(database=db_name)

    # 2. Register in routing table
    db_url = f"postgresql://app:{SECRET}@{HOST}/{db_name}"
    await registry_db.execute(
        "INSERT INTO tenant_databases (tenant_id, database_url) VALUES ($1, $2)",
        tenant_id, db_url,
    )

    return Tenant(id=tenant_id, name=name, plan=plan)

TypeScript

import { mutation, requiresScope } from 'fraiseql';

@mutation({ sqlFunction: 'fn_provision_tenant_db' })
@requiresScope('admin:tenants')
async function provisionTenant(name: string, plan: string): Promise<Tenant> {
  const tenantId = generateId();
  const dbName = `fraiseql_tenant_${tenantId}`;

  // 1. Create and migrate new database
  await createDatabase(dbName);
  await runMigrations({ database: dbName });

  // 2. Register in routing table
  const dbUrl = `postgresql://app:${SECRET}@${HOST}/${dbName}`;
  await registryDb.query(
    'INSERT INTO tenant_databases (tenant_id, database_url) VALUES ($1, $2)',
    [tenantId, dbUrl],
  );

  return { id: tenantId, name, plan };
}

Go

package mutations

import (
    "context"
    "fmt"
)

func ProvisionTenantDB(ctx context.Context, name, plan string) (*Tenant, error) {
    tenantID := generateID()
    dbName := fmt.Sprintf("fraiseql_tenant_%s", tenantID)

    // 1. Create and migrate new database
    if err := createDatabase(ctx, dbName); err != nil {
        return nil, err
    }
    if err := runMigrations(ctx, dbName); err != nil {
        return nil, err
    }

    // 2. Register in routing table
    dbURL := fmt.Sprintf("postgresql://app:%s@%s/%s", secret, host, dbName)
    _, err := registryDB.ExecContext(ctx,
        "INSERT INTO tenant_databases (tenant_id, database_url) VALUES ($1, $2)",
        tenantID, dbURL,
    )
    if err != nil {
        return nil, err
    }

    return &Tenant{ID: tenantID, Name: name, Plan: plan}, nil
}

---

Verify Tenant Isolation

Before deploying, confirm that your RLS policies actually prevent cross-tenant access. A simple curl test is the fastest way to check:

# Request as Tenant A — should return only Tenant A's posts
curl -s http://localhost:8080/graphql \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TENANT_A_JWT" \
  -d '{"query":"{ posts { id title } }"}' | jq .

# Request as Tenant B — should return only Tenant B's posts, never Tenant A's
curl -s http://localhost:8080/graphql \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TENANT_B_JWT" \
  -d '{"query":"{ posts { id title } }"}' | jq .

The two responses must contain disjoint sets of IDs. If any ID appears in both responses, your RLS policy is not enforcing isolation correctly.

Test isolation in code

Write a test that seeds data for two tenants and asserts neither can see the other’s records:

Python

import pytest
import fraiseql

@pytest.mark.asyncio
async def test_tenant_isolation(test_client, tenant_a_token, tenant_b_token):
    # Seed a post for Tenant A
    await test_client.mutate(
        "mutation { createPost(title: \"A's post\", content: \"...\") { id } }",
        headers={"Authorization": f"Bearer {tenant_a_token}"},
    )

    # Query as Tenant B — must not see Tenant A's post
    result = await test_client.query(
        "{ posts { id title } }",
        headers={"Authorization": f"Bearer {tenant_b_token}"},
    )
    titles = [p["title"] for p in result["posts"]]
    assert "A's post" not in titles

TypeScript

import { describe, it, expect } from 'vitest';
import { createTestClient } from 'fraiseql/testing';

describe('tenant isolation', () => {
  it('prevents Tenant B from seeing Tenant A data', async () => {
    const client = createTestClient();

    // Seed a post for Tenant A
    await client.mutate(
      `mutation { createPost(title: "A's post", content: "...") { id } }`,
      { headers: { Authorization: `Bearer ${TENANT_A_TOKEN}` } },
    );

    // Query as Tenant B — must not see Tenant A's post
    const { posts } = await client.query(
      `{ posts { id title } }`,
      { headers: { Authorization: `Bearer ${TENANT_B_TOKEN}` } },
    );
    const titles = posts.map((p: { title: string }) => p.title);
    expect(titles).not.toContain("A's post");
  });
});

What happens if the tenant ID is missing

If a request arrives without a valid tenant context — no JWT, or a JWT missing the tenant_id claim — FraiseQL returns a 401 Unauthorized response before the query reaches PostgreSQL:

{
  "errors": [
    {
      "message": "Missing tenant context",
      "extensions": { "code": "UNAUTHORIZED" }
    }
  ]
}

This is enforced by the middleware. If the middleware raises PermissionError, FraiseQL converts it to a GraphQL error and stops execution. The database is never queried.

---

Security Best Practices

1. Derive Tenant from JWT, Not User Input

Caution

Never trust a tenant_id value supplied directly by the client in a GraphQL argument or request header. Any user can forge a header value. Derive the tenant ID exclusively from a server-verified JWT claim — FraiseQL validates the JWT signature before your middleware runs.

Python

# Good: tenant_id comes from server-verified JWT
@fraiseql.middleware
def extract_tenant(request, next):
    tenant_id = request.auth.claims.get("tenant_id")  # JWT verified by FraiseQL
    request.context["tenant_id"] = tenant_id
    return next(request)

# Bad: tenant_id comes from a GraphQL argument (user-controlled)
@fraiseql.query
def posts(info, tenant_id: str) -> list[Post]:  # Don't do this
    ...

TypeScript

import { fraiseqlMiddleware } from 'fraiseql';

// Good: tenantId comes from server-verified JWT
fraiseqlMiddleware(async (request, next) => {
  const tenantId = request.auth.claims?.tenant_id; // JWT verified by FraiseQL
  request.context.tenantId = tenantId;
  return next(request);
});

// Bad: tenantId comes from a GraphQL argument (user-controlled)
// @query() function posts(tenantId: string): Post[] {} // Don't do this

Go

// Good: tenantID comes from server-verified JWT
func ExtractTenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        claims := fraiseql.ClaimsFromContext(r.Context())
        tenantID, _ := claims["tenant_id"].(string) // JWT verified by FraiseQL
        ctx := context.WithValue(r.Context(), fraiseql.TenantIDKey, tenantID)
        next.ServeHTTP(w, r.WithContext(ctx))
    })
}
// Bad: tenantID comes from a GraphQL argument (user-controlled) — don't do this

2. Validate Tenant Exists

Python

@fraiseql.middleware
async def validate_tenant(request, next):
    tenant_id = request.auth.claims.get("tenant_id")
    tenant = await get_tenant(tenant_id)
    if not tenant or tenant.suspended:
        raise PermissionError(f"Tenant not found or suspended: {tenant_id}")
    request.context["tenant_id"] = tenant_id
    return next(request)

TypeScript

fraiseqlMiddleware(async (request, next) => {
  const tenantId = request.auth.claims?.tenant_id;
  const tenant = await getTenant(tenantId);
  if (!tenant || tenant.suspended) {
    throw new Error(`Tenant not found or suspended: ${tenantId}`);
  }
  request.context.tenantId = tenantId;
  return next(request);
});

Go

func ValidateTenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        tenantID := fraiseql.TenantIDFromContext(r.Context())
        tenant, err := getTenant(r.Context(), tenantID)
        if err != nil || tenant == nil || tenant.Suspended {
            http.Error(w, fmt.Sprintf("tenant not found or suspended: %s", tenantID),
                http.StatusForbidden)
            return
        }
        next.ServeHTTP(w, r)
    })
}

3. Audit Tenant Data Access

Python

@fraiseql.middleware
async def audit_tenant_access(request, next):
    result = await next(request)
    await audit_log.record(
        tenant_id=request.context.get("tenant_id"),
        user_id=request.auth.claims.get("sub"),
        operation=request.operation_name,
        timestamp=datetime.utcnow(),
    )
    return result

TypeScript

fraiseqlMiddleware(async (request, next) => {
  const result = await next(request);
  await auditLog.record({
    tenantId: request.context.tenantId,
    userId: request.auth.claims?.sub,
    operation: request.operationName,
    timestamp: new Date(),
  });
  return result;
});

Go

func AuditTenantMiddleware(next http.Handler) http.Handler {
    return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
        next.ServeHTTP(w, r)
        auditLog.Record(r.Context(), AuditEntry{
            TenantID:  fraiseql.TenantIDFromContext(r.Context()),
            UserID:    fraiseql.UserIDFromContext(r.Context()),
            Operation: r.Header.Get("X-Operation-Name"),
            Timestamp: time.Now().UTC(),
        })
    })
}

---

Performance

Index Tenant Columns

For Row-Level Security, add indexes on tenant_id to avoid full-table scans:

CREATE INDEX idx_post_tenant ON tb_post (tenant_id);
CREATE INDEX idx_user_tenant ON tb_user (tenant_id);

-- Composite indexes for common query patterns
CREATE INDEX idx_post_tenant_created ON tb_post (tenant_id, created_at DESC);

Connection Pooling

Strategy	Pool Recommendation
Row-Level	Single shared pool (e.g., PgBouncer with pool_mode=transaction)
Schema-per-Tenant	Shared pool; SET search_path per transaction
Database-per-Tenant	Pool per database; size to (expected_concurrent_tenants × 2)

---

Complete Example

A working multi-tenant blog platform using row-level security:

schema.py

import fraiseql
from uuid import UUID
from datetime import datetime

@fraiseql.type
class Post:
    id: UUID
    title: str
    content: str
    published: bool
    created_at: datetime

@fraiseql.type
class User:
    id: UUID
    name: str
    email: str

# Queries are scoped to current tenant via RLS automatically
@fraiseql.query(sql_source="v_post")
def posts(limit: int = 20, published: bool | None = None) -> list[Post]:
    pass

@fraiseql.query(sql_source="v_post", id_arg="id")
def post(id: UUID) -> Post | None:
    pass

@fraiseql.mutation
@requires_scope("write:posts")
def create_post(info, title: str, content: str) -> Post:
    tenant_id = info.context["tenant_id"]
    user_id = info.context["user_id"]
    # INSERT INTO tb_post (tenant_id, user_id, title, content) VALUES (...)
    ...

@fraiseql.mutation
@requires_scope("write:posts")
def publish_post(info, id: UUID) -> Post:
    # UPDATE tb_post SET published = true WHERE id = $1
    # RLS ensures the post belongs to the current tenant
    ...

fraiseql.toml

[project]
name = "blog-platform"

[database]
url = "${DATABASE_URL}"

[server]
port = 8080

# Multitenancy strategy (see caution above — namespace unverified)
[multitenancy]
enabled       = true
strategy      = "row"
tenant_header = "X-Tenant-ID"

# JWT authentication via env var
JWT_SECRET=your-256-bit-secret

---

Related Guides

Authentication

JWT, OAuth, and SAML setup

Security

RBAC, input validation, and HTTPS

Performance

Query optimization and connection pooling

SaaS Blog Example

Complete multi-tenant application walkthrough