# DuckDB Migration Support Design

**Date:** 2026-04-26  
**Status:** Approved  
**Type:** Feature Addition

## Overview

Add DuckDB migration support to the prax-migrate event sourcing system. DuckDB is an in-process analytical database (OLAP) that uses SQL with PostgreSQL compatibility. This design extends the existing migration system to support DuckDB's unique features: extensions, analytical types, and both file-based and in-memory databases.

## Goals

1. **DuckDB SQL Generator** - Generate up.sql/down.sql for DuckDB schema changes
2. **Extension Support** - Track and manage DuckDB extensions (parquet, json, httpfs, etc.)
3. **Analytical Types** - Support DuckDB-specific types (LIST, STRUCT, MAP, UNION, ENUM)
4. **In-Memory Support** - Support both persistent file databases and in-memory databases
5. **Event Sourcing Integration** - Work seamlessly with existing event sourcing architecture

## Non-Goals

- Modifying the event sourcing architecture (already implemented in v0.7.0)
- Supporting DuckDB's Python/R integration (schema only)
- Automatic performance optimization (indexes, partitioning)
- Cross-database migration (DuckDB ↔ PostgreSQL)

## Background

DuckDB is an embedded analytical database designed for OLAP workloads:
- **In-process**: Runs embedded like SQLite, no server required
- **Columnar storage**: Optimized for analytical queries (aggregations, window functions)
- **PostgreSQL compatible**: Uses PostgreSQL wire protocol and similar SQL syntax
- **Extension system**: Modular extensions for formats (Parquet, JSON), protocols (httpfs)
- **Rich types**: LIST, STRUCT, MAP, UNION types for complex analytical data

The prax-duckdb crate already provides connection pooling and query execution. This design adds schema migration support.

## Architecture

### Location

All implementation goes in `prax-migrate/src/sql.rs` as a new `DuckDbSqlGenerator` struct, following the existing pattern used by PostgreSQL, MySQL, SQLite, and MSSQL generators.

### Generator Structure

```rust
/// SQL generator for DuckDB.
pub struct DuckDbSqlGenerator;

impl DuckDbSqlGenerator {
    /// Generate SQL for a schema diff.
    pub fn generate(&self, diff: &SchemaDiff) -> MigrationSql {
        // Returns MigrationSql { up: Vec<String>, down: Vec<String>, warnings: Vec<String> }
    }
    
    // Private methods for each operation
    fn install_extension(&self, name: &str) -> String { ... }
    fn create_table(&self, model: &ModelDiff) -> String { ... }
    fn create_index(&self, index: &IndexDiff) -> String { ... }
    fn create_view(&self, view: &ViewDiff) -> String { ... }
    fn create_enum(&self, enum_diff: &EnumDiff) -> String { ... }
    // ... drop/alter methods
}
```

### Integration Points

**No changes required to:**
- Event sourcing system (event types, state projection, event store)
- Migration engine (planning, applying, rollback)
- Bootstrap system (V1 → V2 migration)
- CLI commands

**New code:**
- `DuckDbSqlGenerator` implementation (~500 lines in sql.rs)
- Unit tests in sql.rs
- Integration tests in tests/duckdb_migration.rs (~300 lines)

## Components

### Extension Management

DuckDB extensions must be installed and loaded before use. Common extensions:
- `parquet` - Read/write Parquet files
- `json` - JSON support
- `httpfs` - Read files from HTTP/S3
- `fts` - Full-text search
- `icu` - International character support

**SQL Generation:**

```sql
-- up.sql
INSTALL parquet;
LOAD parquet;

-- down.sql (best-effort, extensions often persist)
-- No standard way to uninstall
```

**Schema Representation:**

Extensions are tracked in the `SchemaDiff::create_extensions` and `drop_extensions` fields (already exists for PostgreSQL extensions).

**Note:** Extensions persist across database sessions in file-based databases. In-memory databases require re-installation each session.

### Table Creation

**Primary Key Syntax:**

DuckDB uses `GENERATED BY DEFAULT AS IDENTITY` instead of sequences:

```sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    email VARCHAR NOT NULL,
    tags VARCHAR[] -- Array type
);
```

**Constraints:**

DuckDB supports:
- `PRIMARY KEY`
- `NOT NULL`
- `UNIQUE`
- `CHECK`
- `FOREIGN KEY` (defined but not enforced by default)

**Foreign Key Warning:**

When generating foreign keys, add warning:
> "Foreign keys defined but not enforced unless SET check_fk_violation = 'error'"

### Analytical Types

**Type Mappings:**

| Prax Schema Type | DuckDB Type | Example |
|------------------|-------------|---------|
| `Int` | `INTEGER` | `age INTEGER` |
| `BigInt` | `BIGINT` | `id BIGINT` |
| `String` | `VARCHAR` | `name VARCHAR` |
| `Text` | `VARCHAR` | `description VARCHAR` |
| `Boolean` | `BOOLEAN` | `active BOOLEAN` |
| `DateTime` | `TIMESTAMP WITH TIME ZONE` | `created_at TIMESTAMPTZ` |
| `Json` | `JSON` | `metadata JSON` |
| `Decimal(p,s)` | `DECIMAL(p,s)` | `price DECIMAL(10,2)` |
| `List<T>` | `T[]` | `tags VARCHAR[]` |

**Complex Types:**

DuckDB supports structured types not in Prax schema yet:
- `STRUCT(field1 type1, field2 type2)` - Record type
- `MAP(key_type, value_type)` - Key-value pairs
- `UNION(type1, type2, ...)` - Tagged union

**Implementation Note:** Generate these as `VARCHAR` with a warning if encountered in schema:
> "DuckDB STRUCT/MAP/UNION types not yet supported in Prax schema - using VARCHAR"

This allows forward compatibility when Prax schema adds these types.

### Enum Support

DuckDB supports PostgreSQL-compatible enums:

```sql
-- up.sql
CREATE TYPE status AS ENUM ('pending', 'active', 'archived');

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    status status NOT NULL
);

-- down.sql
DROP TABLE orders;
DROP TYPE status;
```

**Alter Enum:**

DuckDB supports adding enum values (like PostgreSQL):

```sql
ALTER TYPE status ADD VALUE 'cancelled';
```

Cannot remove values or rename enum types (same limitations as PostgreSQL).

### Index Generation

DuckDB supports indexes for filtering but columnar storage makes them less critical than row-store databases:

```sql
-- up.sql
CREATE INDEX idx_users_email ON users(email);

-- down.sql
DROP INDEX idx_users_email;
```

**Unique Indexes:**

```sql
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
```

**Note:** DuckDB's query optimizer automatically uses columnar scans for analytical queries, so indexes are primarily useful for point lookups and joins.

### View Generation

DuckDB supports views but **not materialized views**:

```sql
-- up.sql
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

-- down.sql
DROP VIEW active_users;
```

**Materialized View Warning:**

If schema includes materialized views:
> "DuckDB does not support materialized views - generating regular view instead"

### In-Memory Database Support

DuckDB supports in-memory databases via connection string `:memory:`. The migration system will:

1. Create `_prax_migrations` event log table in memory
2. Apply migrations normally
3. Event log persists only for current session
4. Useful for testing, ephemeral analytics, CI/CD

**Detection:**

Check connection string for `:memory:` to determine if database is in-memory. No special handling required - event log works the same way.

### Data Loss Warnings

Generate warnings for operations that lose data:

**Drop Table:**
> "Dropping table 'users' - all data will be lost and cannot be recovered"

**Drop Column:**
> "Dropping column 'email' from table 'users' - data in this column will be lost"

**Type Change:**
> "Changing column 'age' type in table 'users' - reverse migration may fail if data is incompatible"

**Materialized View:**
> "DuckDB does not support materialized views - generating regular view instead"

**Foreign Keys:**
> "Foreign keys defined but not enforced unless SET check_fk_violation = 'error'"

## SQL Generation

### Statement Order (up.sql)

1. **Install and load extensions**
2. **Create enums** (might be referenced by tables)
3. **Create tables**
4. **Create indexes**
5. **Create views**
6. **Add foreign keys** (if not inline in table definition)

### Statement Order (down.sql)

1. **Drop views**
2. **Drop indexes**
3. **Drop foreign keys**
4. **Drop tables**
5. **Drop enums**
6. **Unload/uninstall extensions** (best-effort, no standard way)

### Example Output

**Prax Schema Change:**

```prax
model User {
  id      BigInt  @id @auto
  email   String  @unique
  tags    List<String>
  created DateTime @default(now())
}
```

**Generated up.sql:**

```sql
-- Extensions
INSTALL json;
LOAD json;

-- Tables
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    email VARCHAR NOT NULL,
    tags VARCHAR[] NOT NULL DEFAULT [],
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
```

**Generated down.sql:**

```sql
-- Indexes
DROP INDEX IF EXISTS idx_users_email_unique;

-- Tables
DROP TABLE IF EXISTS users;
```

**Warnings:**

```
(none for creation - no data loss)
```

## Testing Strategy

### Unit Tests

Location: `prax-migrate/src/sql.rs` (inline with DuckDbSqlGenerator)

**Test Coverage:**
- Extension installation SQL generation
- Table creation with IDENTITY primary keys
- Analytical type mappings (LIST → array)
- Enum creation and alteration
- Index creation (standard and unique)
- View creation
- Data-loss warning generation
- Foreign key warnings

**Example Test:**

```rust
#[test]
fn test_duckdb_create_table_with_list_type() {
    let generator = DuckDbSqlGenerator;
    let model = ModelDiff {
        table_name: "products".to_string(),
        fields: vec![
            FieldDiff {
                name: "id".to_string(),
                field_type: "BigInt".to_string(),
                is_required: true,
                is_unique: false,
                default_value: None,
                auto: true,
            },
            FieldDiff {
                name: "tags".to_string(),
                field_type: "List<String>".to_string(),
                is_required: true,
                is_unique: false,
                default_value: Some("[]".to_string()),
                auto: false,
            },
        ],
    };
    
    let sql = generator.create_table(&model);
    
    assert!(sql.contains("id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY"));
    assert!(sql.contains("tags VARCHAR[] NOT NULL DEFAULT []"));
}

#[test]
fn test_duckdb_drop_table_generates_warning() {
    let generator = DuckDbSqlGenerator;
    let diff = SchemaDiff {
        drop_models: vec!["users".to_string()],
        ..Default::default()
    };
    
    let migration = generator.generate(&diff);
    
    assert!(migration.warnings.iter().any(|w| 
        w.contains("Dropping table 'users'") && w.contains("all data will be lost")
    ));
}
```

### Integration Tests

Location: `prax-migrate/tests/duckdb_migration.rs`

**Test Coverage:**
- Full migration workflow: generate → apply → verify → rollback
- In-memory database migrations
- File-based database migrations
- Extension loading (parquet, json)
- Analytical types in actual DuckDB database
- Event sourcing integration (event log creation, queries)
- Multiple apply/rollback cycles

**Example Test:**

```rust
#[tokio::test]
async fn test_duckdb_migration_workflow() {
    // Setup in-memory DuckDB
    let config = DuckDbConfig::in_memory();
    let pool = DuckDbPool::new(config).await.unwrap();
    
    // Create event store
    let event_store = PostgresEventStore::new(pool.clone());
    
    // Initialize migration system
    let engine = MigrationEngine::new(
        MigrationConfig::new().migrations_dir("./test_migrations"),
        event_store,
    );
    engine.initialize().await.unwrap();
    
    // Parse schema
    let schema = parse_schema(r#"
        model User {
            id BigInt @id @auto
            email String @unique
            tags List<String>
        }
    "#).unwrap();
    
    // Generate and apply migration
    let plan = engine.plan(&schema).await.unwrap();
    assert_eq!(plan.pending_migrations.len(), 1);
    
    let result = engine.migrate().await.unwrap();
    assert_eq!(result.applied_count, 1);
    
    // Verify table exists
    let conn = pool.get().await.unwrap();
    let row: (i64,) = conn
        .query_one("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'users'", &[])
        .await
        .unwrap();
    assert_eq!(row.0, 1);
    
    // Rollback
    let rollback = engine.rollback().await.unwrap();
    assert_eq!(rollback.rolled_back_count, 1);
    
    // Verify table dropped
    let row: (i64,) = conn
        .query_one("SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'users'", &[])
        .await
        .unwrap();
    assert_eq!(row.0, 0);
}

#[tokio::test]
async fn test_duckdb_extension_installation() {
    let config = DuckDbConfig::in_memory();
    let pool = DuckDbPool::new(config).await.unwrap();
    
    let schema = parse_schema(r#"
        datasource db {
            provider = "duckdb"
            extensions = ["parquet", "json"]
        }
        
        model Analytics {
            id BigInt @id @auto
        }
    "#).unwrap();
    
    // Apply migration
    let engine = setup_engine(pool.clone()).await;
    engine.migrate_to_schema(&schema).await.unwrap();
    
    // Verify extensions loaded
    let conn = pool.get().await.unwrap();
    let row: (bool,) = conn
        .query_one("SELECT loaded FROM duckdb_extensions() WHERE extension_name = 'parquet'", &[])
        .await
        .unwrap();
    assert!(row.0);
}
```

### Property-Based Tests

Use `proptest` to verify invariants:

**Test 1: Valid SQL Generation**

Generate random `SchemaDiff` → generate SQL → parse with DuckDB parser → should not error

```rust
#[proptest]
fn test_duckdb_generates_valid_sql(diff: SchemaDiff) {
    let generator = DuckDbSqlGenerator;
    let migration = generator.generate(&diff);
    
    // Each SQL statement should be valid DuckDB syntax
    for sql in &migration.up {
        assert!(is_valid_duckdb_sql(sql));
    }
}
```

**Test 2: Warning Generation**

Any diff with `drop_models` or `drop_fields` → must generate warnings

```rust
#[proptest]
fn test_duckdb_drop_operations_generate_warnings(diff: SchemaDiff) {
    let generator = DuckDbSqlGenerator;
    let migration = generator.generate(&diff);
    
    if !diff.drop_models.is_empty() || diff.alter_models.iter().any(|a| !a.drop_fields.is_empty()) {
        assert!(!migration.warnings.is_empty());
    }
}
```

## Implementation Plan

### Phase 1: SQL Generator (Core)

1. Add `DuckDbSqlGenerator` struct to sql.rs
2. Implement `generate()` method with statement ordering
3. Implement table creation with IDENTITY primary keys
4. Implement basic type mappings (Int, String, DateTime, etc.)
5. Implement index generation
6. Add unit tests for basic operations

### Phase 2: Analytical Types & Extensions

1. Implement LIST type mapping (T → T[])
2. Implement enum support
3. Implement extension installation/loading
4. Add warnings for unsupported types (STRUCT, MAP, UNION)
5. Add unit tests for analytical types and extensions

### Phase 3: Views & Constraints

1. Implement view creation
2. Add materialized view warning
3. Implement foreign key generation with warning
4. Add ALTER operations (add/drop columns, change types)
5. Add unit tests for views and constraints

### Phase 4: Integration Testing

1. Create integration test file (`tests/duckdb_migration.rs`)
2. Test in-memory database workflow
3. Test file-based database workflow
4. Test extension loading
5. Test event sourcing integration
6. Test multiple apply/rollback cycles

### Phase 5: Documentation & Examples

1. Add doc comments to DuckDbSqlGenerator
2. Add example migration in `examples/duckdb_migration.rs`
3. Update prax-migrate README with DuckDB support
4. Add migration guide for DuckDB users

## Dependencies

**No new dependencies required.** DuckDB support is already provided by:
- `prax-duckdb` crate (connection pooling, query execution)
- `duckdb` crate (underlying DuckDB bindings)

The migration system only generates SQL strings — no direct DuckDB API calls.

## Migration Path

**For Existing Users:**

No migration required. DuckDB support is purely additive:
- Existing PostgreSQL/MySQL/SQLite/MSSQL migrations unchanged
- Event sourcing system unchanged
- CLI commands unchanged

**For New DuckDB Users:**

1. Add `prax-duckdb` dependency
2. Configure datasource: `provider = "duckdb"`
3. Run `prax migrate dev` as usual
4. Migration system auto-detects DuckDB and uses DuckDbSqlGenerator

## Edge Cases

### Extension Already Installed

**Scenario:** User manually installed extension, then schema adds it

**Handling:** Use `INSTALL IF NOT EXISTS` syntax (if supported), or catch/ignore duplicate extension error

### In-Memory Database Restarts

**Scenario:** In-memory database loses all state including _prax_migrations table

**Handling:** Event log is ephemeral — this is expected behavior. Document that in-memory migrations are per-session only.

### Foreign Key Violations

**Scenario:** Foreign keys defined but SET check_fk_violation not configured

**Handling:** Warning in migration output, user must configure DuckDB setting if they want enforcement

### Complex Types (STRUCT/MAP/UNION)

**Scenario:** Schema contains types not yet supported by Prax

**Handling:** Generate VARCHAR with warning, allow forward compatibility when Prax adds support

### Materialized Views

**Scenario:** Schema includes materialized views

**Handling:** Generate regular view with warning that materialization not supported

## Success Criteria

**Functional:**
- ✅ Generate valid DuckDB SQL from schema diffs
- ✅ Support extensions (install/load)
- ✅ Support analytical types (LIST)
- ✅ Support in-memory and file-based databases
- ✅ Integrate with event sourcing system
- ✅ Generate appropriate warnings for data loss

**Testing:**
- ✅ All unit tests pass
- ✅ All integration tests pass
- ✅ Property-based tests verify invariants
- ✅ Manual testing with real DuckDB database

**Documentation:**
- ✅ Doc comments on all public methods
- ✅ Example migration code
- ✅ README updated

**Performance:**
- ✅ SQL generation completes in <100ms for typical schemas
- ✅ No regression in existing generator performance

## Future Work

**Not in this design, but possible later:**

1. **STRUCT/MAP/UNION types** - When Prax schema adds support
2. **Partitioning support** - DuckDB supports table partitioning
3. **Performance optimization** - Auto-suggest indexes based on query patterns
4. **Cross-database migration** - Import PostgreSQL schema to DuckDB
5. **Module refactoring** - Split sql.rs into separate modules when it exceeds 3000 lines

## Summary

This design adds DuckDB migration support to prax-migrate by:

1. Implementing `DuckDbSqlGenerator` following the existing pattern
2. Supporting DuckDB-specific features (extensions, analytical types, IDENTITY)
3. Integrating seamlessly with event sourcing architecture
4. Providing comprehensive testing (unit, integration, property-based)
5. Maintaining backward compatibility with existing migrations

The implementation is ~800 lines total (500 generator + 300 tests), fully contained in prax-migrate with no architectural changes required.
