fraiseql-confiture 0.3.5

PostgreSQL migrations, sweetly done 🍓

Confiture 🍓

PostgreSQL migrations, sweetly done

Confiture is the official migration tool for FraiseQL, designed with a build-from-scratch philosophy and 4 migration strategies to handle every scenario from local development to zero-downtime production deployments.

Part of the FraiseQL ecosystem - While Confiture works standalone for any PostgreSQL project, it's designed to integrate seamlessly with FraiseQL's GraphQL-first approach.

---

🍓 Part of the FraiseQL Ecosystem

confiture accelerates PostgreSQL schema evolution across the FraiseQL stack:

Server Stack (PostgreSQL + Python/Rust)

Tool	Purpose	Status	Performance Gain
pg_tviews	Incremental materialized views	Beta	100-500× faster
jsonb_delta	JSONB surgical updates	Stable	2-7× faster
pgGit	Database version control	Stable	Git for databases
confiture	PostgreSQL migrations	Beta	300-600× faster (theoretical)
fraiseql	GraphQL framework	Stable	7-10× faster
fraiseql-data	Seed data generation	Phase 6	Auto-dependency resolution

Client Libraries (TypeScript/JavaScript)

Library	Purpose	Framework Support
graphql-cascade	Automatic cache invalidation	Apollo, React Query, Relay, URQL

How confiture fits:

• Build from DDL → Fresh DB in <1s for fraiseql GraphQL testing
• pgGit automatically tracks confiture migrations
• Manage pg_tviews schema evolution with 4 migration strategies
• fraiseql-data seeds the schema confiture built

Intended workflow:

# Build schema from DDL files
confiture build --env test

# Seed test data
fraiseql-data add tb_user --count 100

# Run GraphQL tests
pytest

---

Why Confiture?

The Problem with Migration History

Traditional migration tools (Alembic, Django migrations, Flyway) use migration history replay: every time you build a database, the tool executes every migration file in order. This works, but it's slow and brittle:

• Slow: Fresh database builds take 5-10 minutes (replaying hundreds of operations)
• Brittle: One broken migration breaks everything - your database history is fragile
• Complicated: Developers maintain two things: current schema AND migration history
• Messy: Technical debt accumulates as migrations pile up over months/years

The Confiture Approach

Confiture flips the model: DDL source files are the single source of truth. To build a database:

1. Read all .sql files in db/schema/
2. Execute them once (in order)
3. Done ✅

No migration history to replay. No accumulated technical debt. Just your actual, current schema. Fresh databases in <1 second.

Intended Advantages Over Alembic

Feature	Confiture	Alembic	Notes
Fresh DB setup	Direct DDL execution	Migration replay	Theoretically faster
Zero-downtime migrations	Via FDW (planned)	Not built-in	Not yet production-tested
Production data sync	Built-in (with PII anonymization)	Not available	Not yet production-tested
Schema diffs	Auto-generated	Manual	Implemented
Conceptual simplicity	DDL-first	Migration-first	Different philosophy

What's Implemented

• ✅ 4 migration strategies (Build from DDL, ALTER, Production Sync, FDW)
• ✅ Python + optional Rust extension
• ✅ PII anonymization strategies
• ✅ Comprehensive test suite (3,200+ tests)
• ⚠️ Not yet used in production - Beta software

---

The Four Mediums

1️⃣ Build from DDL

confiture build --env production

Build fresh database from db/schema/ DDL files in <1 second.

2️⃣ Incremental Migrations (ALTER)

confiture migrate up

Apply migrations to existing database (simple schema changes).

3️⃣ Production Data Sync

confiture sync --from production --anonymize users.email

Copy production data to local/staging with PII anonymization.

4️⃣ Schema-to-Schema Migration (Zero-Downtime)

confiture migrate schema-to-schema --strategy fdw

Complex migrations via FDW with 0-5 second downtime.

---

Quick Start

Installation

pip install fraiseql-confiture

# Or with FraiseQL integration
pip install fraiseql-confiture[fraiseql]

Initialize Project

confiture init

Creates:

db/
├── schema/           # DDL: CREATE TABLE, views, functions
│   ├── 00_common/
│   ├── 10_tables/
│   └── 20_views/
├── seeds/            # INSERT: Environment-specific test data
│   ├── common/
│   ├── development/
│   └── test/
├── migrations/       # Generated migration files
└── environments/     # Environment configurations
    ├── local.yaml
    ├── test.yaml
    └── production.yaml

Build Schema

# Build local database
confiture build --env local

# Build production schema
confiture build --env production

Create Migration

# Edit schema
vim db/schema/10_tables/users.sql

# Generate migration
confiture migrate generate --name "add_user_bio"

# Apply migration
confiture migrate up

Test Migrations Before Applying (Dry-Run)

Analyze migrations without executing them:

# Analyze pending migrations
confiture migrate up --dry-run

# Test in SAVEPOINT (guaranteed rollback)
confiture migrate up --dry-run-execute

# Save analysis to file
confiture migrate up --dry-run --format json --output report.json

# Analyze rollback impact
confiture migrate down --dry-run --steps 2

For more details, see Dry-Run Guide.

---

Documentation

User Guides

Core Concepts:

• Build from DDL - Execute DDL files directly
• Incremental Migrations - ALTER-based changes
• Production Data Sync - Copy and anonymize data
• Zero-Downtime Migrations - Schema-to-schema via FDW
• Migration Decision Tree - Choose the right strategy

Advanced:

• Dry-Run Mode - Test migrations before applying
• Migration Hooks - Execute custom logic before/after migrations
• Anonymization - PII data masking strategies
• Compliance - HIPAA, SOX, PCI-DSS, GDPR
• Integrations - Slack, GitHub Actions, monitoring

API Reference

• CLI Reference - All commands documented
• Configuration - Environment configuration
• Schema Builder API - Building schemas programmatically
• Migrator API - Migration execution
• Syncer API - Production data sync
• Hook API - Migration lifecycle hooks
• Anonymization API - PII data masking
• Linting API - Schema validation rules

Examples

• Examples Overview - Complete examples
• Basic Migration - Learn the fundamentals
• FraiseQL Integration - GraphQL workflow
• Zero-Downtime - FDW-based migration
• Production Sync - PII anonymization

---

Features

Core Migration System (Implemented)

• ✅ Build from DDL (Medium 1) - Execute DDL files directly
• ✅ Incremental migrations (Medium 2) - ALTER-based changes
• ✅ Production data sync (Medium 3) - Copy with PII anonymization
• ✅ Zero-downtime migrations (Medium 4) - Schema-to-schema via FDW

Additional Features (Implemented)

• ✅ Optional Rust extension for performance
• ✅ Schema diff detection with auto-generation
• ✅ CLI with rich terminal output
• ✅ Multi-environment configuration
• ✅ Migration hooks (pre/post execution)
• ✅ Schema linting with multiple rules
• ✅ PII anonymization strategies
• ✅ Dry-run mode for testing

Documentation (Comprehensive)

• ✅ User guides for all 4 migration strategies
• ✅ API reference documentation
• ✅ Integration guides (Slack, GitHub Actions, monitoring)
• ✅ Compliance guides (HIPAA, SOX, PCI-DSS, GDPR)

---

Comparison

Feature	Alembic	pgroll	Confiture
Philosophy	Migration replay	Multi-version schema	Build-from-DDL
Fresh DB setup	Minutes	Minutes	<1 second
Zero-downtime	❌ No	✅ Yes	✅ Yes (FDW)
Production sync	❌ No	❌ No	✅ Built-in
Language	Python	Go	Python + Rust

---

Current Version

v0.3.5

⚠️ Beta Software: Confiture has not yet been used in production. While the codebase includes comprehensive tests and documentation, real-world usage may reveal issues. Use with caution in production environments.

What's Implemented

• ✅ All 4 migration strategies (Build from DDL, ALTER, Production Sync, FDW)
• ✅ Comprehensive test suite (3,200+ tests passing)
• ✅ Documentation and guides
• ✅ Python 3.11, 3.12, 3.13 support
• ✅ Optional Rust extension
• ✅ Migration hooks, schema linting, anonymization strategies

What's NOT Validated

• ❌ Production usage (never deployed to production)
• ❌ Performance claims (benchmarks only, not real-world)
• ❌ Edge cases and failure recovery (not battle-tested)
• ❌ Large-scale data migrations (theoretical only)

---

Contributing

Contributions welcome! We'd love your help making Confiture even better.

Quick Start:

# Clone repository
git clone https://github.com/fraiseql/confiture.git
cd confiture

# Install dependencies (includes Rust build)
uv sync --all-extras

# Build Rust extension
uv run maturin develop

# Run tests
uv run pytest --cov=confiture

# Format code
uv run ruff format .

# Type checking
uv run mypy python/confiture/

Resources:

• CONTRIBUTING.md - Contributing guidelines
• CLAUDE.md - AI-assisted development guide
• PHASES.md - Detailed roadmap

What to contribute:

• 🐛 Bug fixes
• ✨ New features
• 📖 Documentation improvements
• 💡 New examples
• 🧪 Test coverage improvements

---

Author

Vibe-engineered by Lionel Hamayon 🍓

Confiture was crafted with care as the migration tool for the FraiseQL ecosystem, combining the elegance of Python with the performance of Rust, and the sweetness of strawberry jam.

---

License

MIT License - see LICENSE for details.

Copyright (c) 2025 Lionel Hamayon

---

Acknowledgments

• Inspired by printoptim_backend's build-from-scratch approach
• Built for FraiseQL GraphQL framework
• Influenced by pgroll, Alembic, and Reshape
• Developed with AI-assisted vibe engineering ✨

---

FraiseQL Ecosystem

Confiture is part of the FraiseQL family:

• FraiseQL - Modern GraphQL framework for Python
• Confiture - PostgreSQL migration tool (you are here)

---

Making jam from strawberries, one migration at a time. 🍓→🍯

Vibe-engineered with ❤️ by Lionel Hamayon

Documentation • GitHub • PyPI