Database API Reference¶
Complete reference for FraiseQL database operations and repository methods.
Overview¶
FraiseQL provides a high-performance database API through the FraiseQLRepository class, which is automatically available in GraphQL resolvers via info.context["db"].
import fraiseql
from fraiseql.types import ID
@fraiseql.query
async def user(info, id: ID) -> User:
db = info.context["db"]
# field_name auto-inferred as "user" from function name
return await db.find_one("v_user", mandatory_filters={"id": id})
Note: FraiseQL has two repository classes:
FraiseQLRepository(modern, recommended) andCQRSRepository(legacy). See Repository Classes Comparison for details on when to use each.
Accessing the Database¶
In Resolvers:
db = info.context["db"] # FraiseQLRepository instance
Repository Instance: Automatically injected into GraphQL context by FraiseQL
Query Methods¶
find()¶
Purpose: Find multiple records with automatic GraphQL field selection
Signature:
async def find(
view_name: str,
field_name: str | None = None,
**kwargs: Any
) -> Any
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| view_name | str | Yes | Database view or table name (e.g., "v_user") |
| field_name | str | None | No | GraphQL field name for response wrapping. Auto-inferred from info.field_name (your resolver function name). Only specify if you need to override. |
| **kwargs | Any | No | Query parameters: where, limit, offset, order_by |
order_by takes a dict ({"created_at": "DESC"}), a list of dicts or of
(field, direction) tuples, an OrderBySet, or the GraphQL orderBy input; these
read JSONB fields. It also takes a string of the view's column names,
"created_at DESC, id": each name (optionally qualified, t.col, or double-quoted for a
mixed-case column) may be followed by ASC/DESC and NULLS FIRST/NULLS LAST. The
names are quoted into the SQL; any other string (an expression, a JSONB path such as
data->>'name', a column position) raises ValueError.
Note: The
infoparameter is automatically injected from the GraphQL context. Thefield_nameis also automatically extracted frominfo.field_name, so you typically don't need to specify it!
Modern API (recommended):
@fraiseql.query
async def users(info, limit: int = 100) -> list[User]:
db = info.context["db"]
# field_name auto-inferred from function name "users"
return await db.find("v_user", limit=limit)
Explicit field_name (only if needed):
# Only specify field_name if it differs from the function name
return await db.find("v_user", "userList", limit=limit)
Returns: The result is handled automatically by the framework. In your resolver, annotate the return type with your GraphQL type (e.g., list[User]).
Examples:
# Simple query (field_name auto-inferred from function name)
@fraiseql.query
async def users(info, limit: int = 100) -> list[User]:
db = info.context["db"]
return await db.find("v_user", limit=limit)
# With filter
@fraiseql.query
async def active_users(info) -> list[User]:
db = info.context["db"]
return await db.find("v_user", where={"is_active": True})
# With pagination
@fraiseql.query
async def paginated_users(info, page: int = 1, page_size: int = 20) -> list[User]:
db = info.context["db"]
offset = (page - 1) * page_size
return await db.find("v_user", limit=page_size, offset=offset)
# With ordering: a list of {field: direction} dicts. ("created_at", "DESC")
# tuples and bare field names (ascending) work too; a direction other than
# ASC/DESC raises.
@fraiseql.query
async def recent_users(info, limit: int = 10) -> list[User]:
db = info.context["db"]
return await db.find(
"v_user",
order_by=[{"created_at": "DESC"}],
limit=limit
)
# Complex filter (dict-based)
@fraiseql.query
async def search_users(info, name: str) -> list[User]:
db = info.context["db"]
return await db.find(
"v_user",
where={
"name": {"icontains": name},
"created_at": {"gte": "2025-01-01"},
}
)
# Using typed WhereInput (auto-generated)
@fraiseql.query
async def filtered_users(info, where: UserWhereInput | None = None) -> list[User]:
db = info.context["db"]
return await db.find("v_user", where=where)
Filter Operators (dict-based): each field maps to {operator: value}
| Operator | Description | Example |
|---|---|---|
| (plain value) | Exact match | {"status": "active"} |
eq |
Equals | {"age": {"eq": 25}} |
neq |
Not equals | {"status": {"neq": "deleted"}} |
gt |
Greater than | {"age": {"gt": 18}} |
gte |
Greater than or equal | {"age": {"gte": 18}} |
lt |
Less than | {"age": {"lt": 65}} |
lte |
Less than or equal | {"age": {"lte": 65}} |
in |
In list | {"status": {"in": ["active", "pending"]}} |
contains |
Contains substring (case-sensitive) | {"name": {"contains": "John"}} |
icontains |
Contains substring (case-insensitive) | {"name": {"icontains": "john"}} |
startswith |
Starts with | {"email": {"startswith": "admin"}} |
endswith |
Ends with | {"email": {"endswith": "@example.com"}} |
isnull |
Is null | {"deleted_at": {"isnull": True}} |
Django-style suffixes ("age__gte") are not operators: such a key raises a
ValueError that names the supported form, {"age": {"gte": 18}} (unless the view
has a column, or its type a field, really named age__gte). See
Filter Operators for the full list.
find_one()¶
Purpose: Find a single record with automatic GraphQL field selection
Signature:
async def find_one(
view_name: str,
field_name: str | None = None,
**kwargs: Any
) -> Any | None
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| view_name | str | Yes | Database view or table name (e.g., "v_user") |
| field_name | str | None | No | GraphQL field name for response wrapping. Auto-inferred from info.field_name. Only specify if needed. |
| **kwargs | Any | No | Filter conditions (e.g., id=user_id, where={...}) |
Note: Both
infoandfield_nameare automatically handled. You typically only need to specifyview_nameand filter conditions.
Returns: The result is handled automatically by the framework. In your resolver, annotate the return type with your GraphQL type (e.g., User | None). Returns None if not found.
Examples:
from fraiseql.types import ID
# Find by ID (field_name auto-inferred from function name "user")
@fraiseql.query
async def user(info, id: ID) -> User | None:
db = info.context["db"]
return await db.find_one("v_user", mandatory_filters={"id": id})
# Using where parameter
@fraiseql.query
async def user_by_email(info, email: str) -> User | None:
db = info.context["db"]
return await db.find_one("v_user", where={"email": email})
# Find with complex filter
@fraiseql.query
async def active_user_by_email(info, email: str) -> User | None:
db = info.context["db"]
return await db.find_one(
"v_user",
where={"email": email, "is_active": True}
)
# Handle not found case
@fraiseql.query
async def get_user(info, id: ID) -> User:
db = info.context["db"]
user = await db.find_one("v_user", mandatory_filters={"id": id})
if user is None:
raise GraphQLError("User not found")
return user
count()¶
Purpose: Count records matching filter criteria
Signature:
async def count(
view_name: str,
**kwargs: Any
) -> int
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| view_name | str | Yes | Database view or table name |
| where | dict | WhereType | None | No | Filter conditions |
| **kwargs | Any | No | Additional filter conditions (e.g., tenant_id) |
Returns: Integer count of matching records
Examples:
# Count all users
total = await db.count("v_users")
# Returns: 1523
# Count with filter
active_count = await db.count(
"v_users",
where={"status": {"eq": "active"}}
)
# Returns: 842
# Count with tenant_id
tenant_users = await db.count(
"v_users",
tenant_id="tenant-123"
)
# Returns: 67
# Count with complex filters
electronics_count = await db.count(
"v_products",
where={
"price": {"gt": 100, "lt": 500},
"category": {"eq": "electronics"},
"in_stock": {"eq": True}
}
)
# Returns: 23
# In GraphQL resolver
@fraiseql.query
async def users_count(info, where: UserWhereInput | None = None) -> int:
"""Count users with optional filtering."""
db = info.context["db"]
return await db.count("v_users", where=where)
@fraiseql.query
async def tenant_stats(info) -> TenantStats:
"""Get statistics for current tenant."""
db = info.context["db"]
tenant_id = info.context["tenant_id"]
return TenantStats(
total_users=await db.count("v_users", tenant_id=tenant_id),
active_users=await db.count(
"v_users",
tenant_id=tenant_id,
where={"status": {"eq": "active"}}
),
total_orders=await db.count("v_orders", tenant_id=tenant_id),
)
Performance:
- Uses optimized
COUNT(*)SQL query - Returns plain
int(notRustResponseBytes) - Supports same filter syntax as
find() - Efficient for large datasets
Note: Unlike find() and find_one(), count() returns a plain Python int instead of RustResponseBytes because count is a simple scalar value.
Pagination¶
FraiseQLRepository pages with limit and offset on find(), and count() gives
the total for the same filter. Order by a stable key so pages don't overlap:
import fraiseql
@fraiseql.query
async def active_users(info, page: int = 1, page_size: int = 20) -> list[User]:
db = info.context["db"]
return await db.find(
"v_user",
where={"is_active": {"eq": True}},
order_by=[{"created_at": "DESC"}, {"id": "ASC"}],
limit=page_size,
offset=(page - 1) * page_size,
)
@fraiseql.query
async def active_user_count(info) -> int:
db = info.context["db"]
return await db.count("v_user", where={"is_active": {"eq": True}})
List queries also get limit and offset GraphQL arguments automatically (see
Auto-Wired Query Parameters),
so db.find("v_user") in a resolver that doesn't declare them already pages as
the client asks.
paginate()¶
Cursor pagination, Relay style: first/after page forward, last/before page
back. @fraiseql.connection calls it for you; call it yourself to page a view in
your own resolver.
Signature:
async def paginate(
view_name: str,
*,
first: int | None = None,
after: str | None = None,
last: int | None = None,
before: str | None = None,
filters: Any = None,
order_by: str = "id",
order_direction: str = "ASC",
include_total: bool = True,
info: Any = None,
**kwargs: Any,
) -> dict[str, Any]
Parameters:
| Parameter | Description |
|---|---|
first / after |
Rows after the after cursor (20 when neither first nor last is given) |
last / before |
Rows before the before cursor. first and last can't both be given |
filters |
A where filter (a dict or a WhereInput), for the page and the total |
order_by |
The field rows are ordered by and cursors are made from; its values must be unique |
order_direction |
"ASC" or "DESC" |
include_total |
Count the rows filters matches (one count() query) |
info |
GraphQL resolve info, passed to find() |
**kwargs |
Passed to find() and count(), e.g. mandatory_filters |
Returns: {"edges": [{"node": row, "cursor": str}], "page_info": {...},
"total_count": int | None}. page_info has has_next_page, has_previous_page,
start_cursor, end_cursor and total_count; each row is keyed as GraphQL answers
it (camelCase). create_connection(result, NodeType) (from
fraiseql.types.generic) turns the result into a typed Connection.
page = await db.paginate(
"v_user",
first=20,
after=cursor, # end_cursor of the previous page, or None
filters={"is_active": {"eq": True}},
order_by="id",
)
next_cursor = page["page_info"]["end_cursor"]
Cursors are opaque: base64 JSON of the row's order_by value.
Mutation Patterns¶
FraiseQL follows the CQRS (Command Query Responsibility Segregation) pattern. Mutations are handled by PostgreSQL functions, not direct repository methods.
Creating Records¶
Use execute_function() with a PostgreSQL function, then fetch the result with find_one():
import fraiseql
@fraiseql.mutation
async def create_user(info, input: CreateUserInput) -> User:
db = info.context["db"]
# Execute PostgreSQL function to create record
result = await db.execute_function("fn_create_user", {
"name": input.name,
"email": input.email
})
# Fetch created record via Rust pipeline
# field_name auto-inferred from function name "create_user" → "user"
return await db.find_one("v_user", mandatory_filters={"id": result["id"]})
PostgreSQL Function:
See canonical fn_create_user() for a complete example with validation and error handling.
Simple version:
CREATE OR REPLACE FUNCTION fn_create_user(input jsonb) RETURNS jsonb AS $$
BEGIN
RETURN jsonb_build_object('id',
(INSERT INTO tb_user (name, email)
VALUES (input->>'name', input->>'email')
RETURNING id)
);
END;
$$ LANGUAGE plpgsql;
Updating Records¶
from fraiseql.types import ID
@fraiseql.mutation
async def update_user(info, id: ID, input: UpdateUserInput) -> User:
db = info.context["db"]
# Execute PostgreSQL function to update record
result = await db.execute_function("fn_update_user", {
"id": id,
**input.__dict__
})
# Fetch updated record
return await db.find_one("v_user", mandatory_filters={"id": id})
Deleting Records¶
from fraiseql.types import ID
@fraiseql.mutation
async def delete_user(info, id: ID) -> bool:
db = info.context["db"]
# Execute PostgreSQL function to delete record
result = await db.execute_function("fn_delete_user", {"id": id})
return result.get("success", False)
PostgreSQL Function Execution¶
execute_function()¶
Purpose: Execute a PostgreSQL function with JSONB input
Signature:
async def execute_function(
function_name: str,
input_data: dict[str, Any]
) -> dict[str, Any]
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| function_name | str | Yes | Fully qualified function name (e.g., 'graphql.create_user') |
| input_data | dict | Yes | Dictionary to pass as JSONB to the function |
Returns: The function's result as a dict. It runs
SELECT * FROM function_name(%s::jsonb): for a function RETURNS jsonb, the JSON
object it returns; for a function with OUT parameters, RETURNS TABLE or a
composite type, its first row, keyed by column. {} when it returns no row. (A
jsonb array or scalar stays under a key named after the function.)
Examples:
# Execute mutation function
result = await db.execute_function(
"graphql.create_user",
{"name": "John", "email": "john@example.com"}
)
# With schema prefix
result = await db.execute_function(
"auth.register_user",
{"email": "user@example.com", "password": "secret"}
)
PostgreSQL Function Format:
CREATE OR REPLACE FUNCTION graphql.create_user(input jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
BEGIN
-- Function implementation
RETURN jsonb_build_object(
'success', true,
'data', ...
);
END;
$$;
execute_function_with_context()¶
Purpose: Execute a PostgreSQL function with context parameters
Signature:
async def execute_function_with_context(
function_name: str,
context_args: list[Any],
input_data: dict[str, Any]
) -> dict[str, Any]
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| function_name | str | Yes | Fully qualified function name |
| context_args | list | Yes | List of context arguments (e.g., [tenant_id, user_id]) |
| input_data | dict | Yes | Dictionary to pass as JSONB |
Returns: Dictionary result from the function
Examples:
# With tenant isolation
result = await db.execute_function_with_context(
"app.create_location",
[tenant_id, user_id],
{"name": "Office", "address": "123 Main St"}
)
# Function signature in PostgreSQL
# CREATE FUNCTION app.create_location(
# p_tenant_id uuid,
# p_user_id uuid,
# input jsonb
# ) RETURNS jsonb
Note: Automatically called by class-based @fraiseql.mutation decorator with context_params
Raw SQL Execution¶
run()¶
Purpose: Execute a SQL statement on a pool connection, with the request's session variables and query timeout applied
Signature:
async def run(query: DatabaseQuery) -> list[dict[str, object]]
DatabaseQuery (from fraiseql.db) holds the statement, its parameters, and whether
to fetch rows:
| Field | Type | Default | Description |
|---|---|---|---|
| statement | psycopg.sql.SQL | Composed |
- | The SQL, with psycopg %(name)s (or %s) placeholders |
| params | Mapping | - | Parameter values ({} for none) |
| fetch_result | bool | True | Return the rows; False returns [] |
Returns: List of rows as dictionaries (column name to value)
Examples:
from datetime import UTC, datetime
from psycopg.sql import SQL
from fraiseql.db import DatabaseQuery
# Simple query
rows = await db.run(DatabaseQuery(SQL("SELECT id, email FROM tb_user"), {}))
# With parameters
rows = await db.run(
DatabaseQuery(
SQL("SELECT id, email FROM tb_user WHERE id = %(id)s"),
{"id": user_id},
)
)
# Aggregation
rows = await db.run(
DatabaseQuery(
SQL(
"""
SELECT
count(*) AS total_users,
count(*) FILTER (WHERE is_active) AS active_users
FROM tb_user
WHERE created_at > %(since)s
"""
),
{"since": datetime(2025, 1, 1, tzinfo=UTC)},
)
)
stats = rows[0] # {"total_users": ..., "active_users": ...}
The rows are plain dicts, not RustResponseBytes: use run() for internal reads and
statistics, and find() / find_one() for what a resolver returns. For a single
aggregate over a view, count(), sum(), avg(), min(), max() and
aggregate() build the SQL for you.
Security: Pass values as parameters, never by formatting them into the SQL string
Transaction Methods¶
run_in_transaction()¶
Purpose: Run operations within a database transaction
Signature:
async def run_in_transaction(
func: Callable[..., Awaitable[T]],
*args,
**kwargs
) -> T
Parameters:
| Parameter | Type | Required | Description |
|---|---|---|---|
| func | Callable | Yes | Async function to execute in transaction |
| *args | Any | No | Arguments to pass to func |
| **kwargs | Any | No | Keyword arguments to pass to func |
Returns: Result of the function
Examples:
import fraiseql
async def transfer_funds(conn, source_id, dest_id, amount):
# Deduct from source
await conn.execute(
"UPDATE accounts SET balance = balance - %s WHERE id = %s",
(amount, source_id),
)
# Add to destination
await conn.execute(
"UPDATE accounts SET balance = balance + %s WHERE id = %s",
(amount, dest_id),
)
return True
# Execute in transaction
@fraiseql.mutation
async def transfer(info, input: TransferInput) -> bool:
db = info.context["db"]
return await db.run_in_transaction(
transfer_funds,
input.source_id,
input.dest_id,
input.amount
)
Note: Transaction is automatically rolled back on exception
Connection Pool¶
get_pool()¶
Purpose: Access the underlying connection pool
Signature:
def get_pool() -> AsyncConnectionPool
Returns: psycopg AsyncConnectionPool instance
Example:
pool = db.get_pool()
print(f"Pool size: {pool.max_size}")
Context and Session Variables¶
Automatic Session Variable Injection:
FraiseQL automatically sets PostgreSQL session variables from GraphQL context on every request. This is a powerful feature for multi-tenant applications and row-level security.
Automatically Set Variables:
| Session Variable | Source | Type | Purpose |
|---|---|---|---|
app.tenant_id |
info.context["tenant_id"] |
UUID | Multi-tenant isolation |
app.contact_id |
info.context["contact_id"] or info.context["user"] |
UUID | User identification |
How It Works:
- You provide context in your FastAPI app:
async def get_context(request: Request) -> dict:
return {
"tenant_id": extract_tenant_from_jwt(request),
"contact_id": extract_user_from_jwt(request)
}
app = create_fraiseql_app(
config=config,
context_getter=get_context,
# ... other params
)
- FraiseQL automatically executes before each database operation:
SET LOCAL app.tenant_id = '<tenant_id_from_context>';
SET LOCAL app.contact_id = '<contact_id_from_context>';
- Your PostgreSQL functions can access these variables:
SELECT current_setting('app.tenant_id')::uuid;
SELECT current_setting('app.contact_id')::uuid;
Using Session Variables in PostgreSQL¶
In Views (Multi-Tenant Data Filtering):
-- View that automatically filters by tenant
CREATE VIEW v_order AS
SELECT
id,
tenant_id,
customer_id,
data
FROM tb_order
WHERE tenant_id = current_setting('app.tenant_id')::uuid;
Now all queries to v_order automatically see only their tenant's data:
import fraiseql
@fraiseql.query
async def orders(info) -> list[Order]:
db = info.context["db"]
# Automatically filtered by tenant_id from context!
return await db.find("v_order")
In Functions (Audit Logging):
CREATE FUNCTION graphql.create_order(input jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $$
DECLARE
v_tenant_id uuid;
v_user_id uuid;
v_order_id uuid;
BEGIN
-- Get session variables
v_tenant_id := current_setting('app.tenant_id')::uuid;
v_user_id := current_setting('app.contact_id')::uuid;
-- Insert with automatic tenant_id and created_by
INSERT INTO tb_order (tenant_id, data)
VALUES (
v_tenant_id,
jsonb_set(
input,
'{created_by}',
to_jsonb(v_user_id)
)
)
RETURNING id INTO v_order_id;
RETURN jsonb_build_object(
'success', true,
'id', v_order_id
);
END;
$$;
In Row-Level Security Policies:
-- Enable RLS on table
ALTER TABLE tb_document ENABLE ROW LEVEL SECURITY;
-- Policy: Users can only see their tenant's documents
CREATE POLICY tenant_isolation_policy ON tb_document
FOR ALL
TO PUBLIC
USING (tenant_id = current_setting('app.tenant_id')::uuid);
-- Policy: Users can only modify documents they created
CREATE POLICY user_modification_policy ON tb_document
FOR UPDATE
TO PUBLIC
USING (
tenant_id = current_setting('app.tenant_id')::uuid
AND (data->>'created_by')::uuid = current_setting('app.contact_id')::uuid
);
In Triggers (Automatic Audit Fields):
CREATE FUNCTION fn_set_audit_fields()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- Automatically set created_by on insert
IF (TG_OP = 'INSERT') THEN
NEW.data := jsonb_set(
NEW.data,
'{created_by}',
to_jsonb(current_setting('app.contact_id')::uuid)
);
END IF;
-- Automatically set updated_by on update
IF (TG_OP = 'UPDATE') THEN
NEW.data := jsonb_set(
NEW.data,
'{updated_by}',
to_jsonb(current_setting('app.contact_id')::uuid)
);
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_set_audit_fields
BEFORE INSERT OR UPDATE ON tb_order
FOR EACH ROW
EXECUTE FUNCTION fn_set_audit_fields();
Complete Multi-Tenant Example¶
1. Context Provider (Python):
from fastapi import Request
import jwt
async def get_context(request: Request) -> dict:
"""Extract tenant and user from JWT."""
auth_header = request.headers.get("authorization", "")
if not auth_header.startswith("Bearer "):
return {} # Anonymous request
token = auth_header.replace("Bearer ", "")
decoded = jwt.decode(token, options={"verify_signature": False})
return {
"tenant_id": decoded.get("tenant_id"),
"contact_id": decoded.get("user_id")
}
2. Database View (SQL):
CREATE VIEW v_product AS
SELECT
id,
tenant_id,
data->>'name' as name,
(data->>'price')::decimal as price,
data
FROM tb_product
WHERE tenant_id = current_setting('app.tenant_id')::uuid;
3. GraphQL Query (Python):
import fraiseql
@fraiseql.query
async def products(info) -> list[Product]:
"""Get products for current tenant.
Automatically filtered by tenant_id from JWT token.
No need to pass tenant_id explicitly!
"""
db = info.context["db"]
return await db.find("v_product")
4. Result:
- User from Tenant A sees only Tenant A's products
- User from Tenant B sees only Tenant B's products
- No tenant_id filtering needed in application code
Error Handling¶
If session variables are not set (e.g., unauthenticated request):
-- Handle missing session variable gracefully
CREATE VIEW v_public_product AS
SELECT *
FROM tb_product
WHERE
CASE
WHEN current_setting('app.tenant_id', true) IS NULL
THEN is_public = true -- Show only public products
ELSE tenant_id = current_setting('app.tenant_id')::uuid
END;
Custom Session Variables¶
You can add custom session variables by including them in context:
async def get_context(request: Request) -> dict:
return {
"tenant_id": extract_tenant(request),
"contact_id": extract_user(request),
"user_role": extract_role(request), # Custom variable
}
Access in PostgreSQL (note: FraiseQL only auto-sets app.tenant_id and app.contact_id, so you'll need to set others manually if needed):
-- In your function
SELECT current_setting('app.tenant_id')::uuid; -- Auto-set by FraiseQL
SELECT current_setting('app.contact_id')::uuid; -- Auto-set by FraiseQL
Best Practices¶
- Always use session variables for tenant isolation - Don't pass tenant_id as query parameters
- Combine with RLS policies - Defense in depth for security
- Set variables at transaction scope - FraiseQL uses
SET LOCALautomatically - Handle missing variables gracefully - Use
current_setting('var', true)to avoid errors - Don't use session variables for high-cardinality data - They're perfect for tenant/user context, not for dynamic query data
Performance Modes¶
Repository Modes:
FraiseQL repository operates in two modes:
- Production Mode (default)
- Returns raw dictionaries
- Optimized JSON passthrough
-
Minimal object instantiation
-
Development Mode
- Full type instantiation
- Enhanced debugging
- Slower but more developer-friendly
Mode Selection:
# Explicit mode setting
context = {
"db": repository,
"mode": "production" # or "development"
}
Best Practices¶
Query Optimization:
# Use specific fields instead of SELECT *
users = await db.find("v_user", where={"is_active": True}, limit=100)
# Page through large datasets
users = await db.find("v_user", order_by=[{"id": "ASC"}], limit=50, offset=100)
# Use database views for complex queries
# Create view: CREATE VIEW v_user_stats AS SELECT ...
stats = await db.find("v_user_stats")
Error Handling:
import fraiseql
from fraiseql.types import ID
@fraiseql.query
async def get_user(info, id: ID) -> User | None:
try:
db = info.context["db"]
return await db.find_one("v_user", mandatory_filters={"id": id})
except Exception as e:
logger.error(f"Failed to fetch user {id}: {e}")
raise GraphQLError("Failed to fetch user")
Security:
# Always pass values as parameters
rows = await db.run(
DatabaseQuery(SQL("SELECT id FROM tb_user WHERE email = %(email)s"), {"email": email})
)
# NEVER do this (SQL injection risk):
# rows = await db.run(DatabaseQuery(SQL(f"SELECT id FROM tb_user WHERE email = '{email}'"), {}))
Transactions:
# Use transactions for multi-step operations
async def complex_operation(conn, data):
# All operations succeed or all fail
await conn.execute("INSERT INTO table1 ...")
await conn.execute("UPDATE table2 ...")
await conn.execute("DELETE FROM table3 ...")
result = await db.run_in_transaction(complex_operation, data)
See Also¶
- Queries and Mutations - Using database in resolvers
- Configuration - Database configuration options
- PostgreSQL Functions - Writing database functions