E-Commerce Platforms with FraiseQL
Complex product relationships composed efficiently. Serve web, mobile, and internal APIs from a single source of truth.
E-Commerce Complexity
Modern e-commerce requires serving:
- Product catalog - Categories, variants, attributes, images
- Inventory - Real-time stock levels, warehouse locations
- Pricing - Dynamic pricing, discounts, promotions
- Reviews & ratings - User-generated content with aggregations
- Recommendations - Related products, often-bought-together
- Mobile-optimized responses - Minimal payload, predictable performance
FraiseQL: Single Source of Truth
Complete Product View
Compose the entire product entity in one view:
CREATE VIEW v_product_complete AS
SELECT
jsonb_build_object(
'id', p.id::text,
'name', p.name,
'description', p.description,
'category', jsonb_build_object(
'id', c.id::text,
'name', c.name
),
'variants', (
SELECT jsonb_agg(jsonb_build_object(
'id', v.id::text,
'sku', v.sku,
'color', v.color,
'size', v.size,
'price', v.price,
'stock', (SELECT quantity FROM tb_inventory WHERE variant_id = v.id LIMIT 1)
))
FROM tb_variant v
WHERE v.product_id = p.id
),
'images', (
SELECT jsonb_agg(jsonb_build_object(
'url', i.url,
'alt', i.alt_text
))
FROM tb_image i
WHERE i.product_id = p.id
ORDER BY i.display_order
),
'reviews', jsonb_build_object(
'average_rating', (SELECT avg(rating) FROM tb_review WHERE product_id = p.id),
'count', (SELECT count(*) FROM tb_review WHERE product_id = p.id),
'items', (
SELECT jsonb_agg(jsonb_build_object(
'rating', r.rating,
'title', r.title,
'text', r.text,
'author', r.author_name,
'helpful_count', r.helpful_count
))
FROM tb_review r
WHERE r.product_id = p.id
ORDER BY r.created_at DESC
LIMIT 10
)
),
'related_products', (
SELECT jsonb_agg(jsonb_build_object(
'id', rp.id::text,
'name', rp.name,
'price', (SELECT min(price) FROM tb_variant WHERE product_id = rp.id)
))
FROM tb_product_relation pr
JOIN tb_product rp ON pr.related_product_id = rp.id
WHERE pr.product_id = p.id
LIMIT 5
)
) AS data
FROM tb_product p
LEFT JOIN tb_category c ON p.category_id = c.id;Inventory & Pricing
CREATE VIEW v_variant_with_inventory AS
SELECT
jsonb_build_object(
'variant_id', v.id::text,
'sku', v.sku,
'inventory', jsonb_build_object(
'total', sum(i.quantity) FILTER (WHERE i.status = 'available'),
'by_warehouse', (
SELECT jsonb_object_agg(w.name, i.quantity)
FROM tb_inventory i
JOIN tb_warehouse w ON i.warehouse_id = w.id
WHERE i.variant_id = v.id
)
),
'pricing', jsonb_build_object(
'base_price', v.price,
'discount', (
SELECT CASE
WHEN d.type = 'percentage' THEN v.price * (d.value / 100.0)
ELSE d.value
END
FROM tb_discount d
WHERE d.variant_id = v.id
AND NOW() BETWEEN d.start_date AND d.end_date
LIMIT 1
),
'final_price', v.price - COALESCE((
SELECT CASE
WHEN d.type = 'percentage' THEN v.price * (d.value / 100.0)
ELSE d.value
END
FROM tb_discount d
WHERE d.variant_id = v.id
AND NOW() BETWEEN d.start_date AND d.end_date
LIMIT 1
), 0)
)
) AS data
FROM tb_variant v
GROUP BY v.id, v.sku, v.price;Python Types
@fraiseql.type
class Category:
id: ID
name: str
@fraiseql.type
class Variant:
id: ID
sku: str
color: str
size: str
price: float
stock: int
@fraiseql.type
class Image:
url: str
alt: str
@fraiseql.type
class Review:
rating: int
title: str
text: str
author: str
helpful_count: int
@fraiseql.type
class Product:
id: ID
name: str
description: str
category: Category
variants: list[Variant]
images: list[Image]
reviews: ReviewSummaryReal-World Example: Product Listing
Request: Show product with all details
Mobile app requests product page data (web/iOS/Android all use same API)
Single Query Response:
@fraiseql.query(sql_source="v_product_complete")
def product(product_id: ID) -> Product:
pass
# Returns:
# {
# "id": "...",
# "name": "Wireless Headphones",
# "variants": [
# { "id": "...", "color": "black", "stock": 45 },
# { "id": "...", "color": "white", "stock": 12 }
# ],
# "images": [{ "url": "...", "alt": "..." }],
# "reviews": {
# "average_rating": 4.5,
# "count": 234,
# "items": [...]
# },
# "related_products": [...]
# }Result: One database query. Complete product data for all clients (web, mobile, internal dashboards).
Performance Advantages
Mobile-First
Minimal payload. All data in one response. Reduces network latency.
~50-100ms per request
Inventory Consistency
Real-time stock levels from the database. No cache misses.
100% accurate
Scalable Variants
Thousands of variants per product. Single query still fast.
Linear complexity
Personalization Ready
Add user preferences to views. Personalized catalogs per user.
Custom data per request
Advanced Patterns
Personalized Product Feed
CREATE VIEW v_personalized_products AS
SELECT
jsonb_build_object(
'product_id', p.id::text,
'relevance_score', (
SELECT count(*) FROM tb_user_view
WHERE product_id = p.id
AND user_id = current_setting('app.user_id')::uuid
),
'product_data', jsonb_build_object(
'id', p.id::text,
'name', p.name,
'price', min(v.price)
)
) AS data
FROM tb_product p
JOIN tb_variant v ON p.id = v.product_id
GROUP BY p.id
ORDER BY relevance_score DESC;Cross-Sell Recommendations
CREATE VIEW v_often_bought_together AS
SELECT
p1.id::text AS product_id,
jsonb_build_object(
'product_id', p1.id::text,
'recommendations', (
SELECT jsonb_agg(jsonb_build_object(
'id', p2.id::text,
'name', p2.name,
'frequently_bought_together_count', count(*)
))
FROM tb_order_item oi1
JOIN tb_order_item oi2 ON oi1.order_id = oi2.order_id
JOIN tb_product p2 ON oi2.product_id = p2.id
WHERE oi1.product_id = p1.id
AND p2.id != p1.id
GROUP BY p2.id
ORDER BY count(*) DESC
LIMIT 5
)
) AS data
FROM tb_product p1;Related Resources
Ready to Build Your Store?
Start building scalable e-commerce APIs with FraiseQL today.