Skip to content

SEAORM_MIGRATION.md

Latest commit

 

History

History
177 lines (137 loc) · 5.85 KB

SEAORM_MIGRATION.md

File metadata and controls

177 lines (137 loc) · 5.85 KB

SeaORM Migration Status

Overview

This document tracks the migration from SQLx to SeaORM, following b00t gospel: "use existing crates, don't write your own database interface".

Completed Work

✅ Migration Infrastructure (Commit: 40227c5)

SeaORM Dependencies:

  • Added sea-orm 1.1 with full feature set (sqlx-postgres, runtime-tokio-rustls, macros, type support)
  • Added sea-orm-migration 1.1 for schema management
  • Marked SQLx as legacy (being phased out)

Migration System:

  • Created migration/ crate with 22 table migrations covering core schema
  • Built postgres_to_seaorm.py tool for automated migration generation
    • Parses PostgreSQL CREATE TABLE statements
    • Generates SeaORM migration files with up/down methods
    • Handles all PostgreSQL types (integers, strings, timestamps, decimals, JSONB, UUIDs)
    • Auto-generates migration registry

Entities Generated:

  • entity/ crate with core entity definitions:
    • customers (9 fields): Customer data with Argon2 password hashing
    • products (14 fields): Product catalog with Decimal pricing
    • orders (10 fields): Order lifecycle tracking

✅ Service Layer Implementations (WIP: c8f6598)

Created modern SeaORM-based service layers (not yet integrated):

Customer Service (crates/customer/src/lib_seaorm.rs):

  • Full CRUD operations using SeaORM query builder
  • Password hashing with Argon2id
  • Methods: create, find_by_id, find_by_email, update, delete, verify_password, set_password

Product Service (crates/product/src/lib_seaorm.rs):

  • Product catalog management with pagination
  • Price update tracking
  • Methods: create, find_by_id, find_by_product_id, list, update, delete, update_price, mark_sold

Order Service (crates/order/src/lib_seaorm.rs):

  • Order lifecycle management
  • Customer and pool filtering
  • Methods: create, find_by_id, find_by_orderid, find_by_cartid, list_by_customer, list_by_pool, update, mark_paid, mark_shipped, delete

Current Status

⚠️ Not Yet Integrated

The new SeaORM services exist in parallel with the old SQLx code:

  • Old: lib.rs (uses SQLx query! macros - requires DATABASE_URL at compile time)
  • New: lib_seaorm.rs (uses SeaORM - compiles without database)

Old code still being built, causing compilation failures without DATABASE_URL.

Next Steps

1. Complete Integration (High Priority)

Replace SQLx-based lib.rs files with SeaORM versions:

# For each crate (customer, product, order):
cd crates/customer
mv lib.rs lib_sqlx.rs.bak
mv lib_seaorm.rs lib.rs
# Update Cargo.toml to remove sqlx dependency
# Rebuild and verify

2. Update API Layer

Refactor crates/api/src/routes/*.rs to use new service layers:

  • Replace PgPool with DatabaseConnection
  • Use service methods instead of direct SQL queries
  • Update route handlers

3. Migration Workflow

Create justfile commands for SeaORM workflow:

# Run migrations
migrate-up:
    cd commercerack-rust
    cargo run --package migration -- up

# Rollback migrations
migrate-down:
    cd commercerack-rust
    cargo run --package migration -- down

# Generate entities from database
generate-entities:
    cd commercerack-rust
    sea-orm-cli generate entity \
        --database-url "$DATABASE_URL" \
        --output-dir entity/src \
        --with-serde both

4. Testing

Once database is available:

  • Run migrations: just migrate-up
  • Generate fresh entities: just generate-entities
  • Run integration tests
  • Verify all 11 tests pass (previous count)

5. Cleanup

After successful migration:

  • Remove SQLx dependency from all crates
  • Delete lib_sqlx.rs.bak backup files
  • Delete old SQL migration files in migrations/
  • Update documentation

Database Schema

22 Core Tables Migrated:

  1. zusers (36 columns) - User accounts
  2. customers (27 columns) - Customer records
  3. customer_addrs (13 columns) - Customer addresses
  4. customer_notes (6 columns) - Customer notes
  5. products (22 columns) - Product catalog
  6. product_relations (7 columns) - Product relationships
  7. sku_lookup (38 columns) - SKU data
  8. orders (35 columns) - Order records
  9. order_events (8 columns) - Order event log
  10. order_counters (5 columns) - Order ID generation
  11. inventory_detail (37 columns) - Inventory tracking
  12. inventory_log (10 columns) - Inventory history
  13. amazon_docs (11 columns) - Amazon document tracking
  14. amazon_document_contents (7 columns) - Amazon doc content
  15. amazon_orders (17 columns) - Amazon order sync
  16. amazon_order_events (9 columns) - Amazon event log
  17. batch_jobs (29 columns) - Batch processing
  18. batch_parameters (10 columns) - Batch config
  19. campaigns (20 columns) - Email campaigns
  20. campaign_recipients (15 columns) - Campaign targets
  21. projects (16 columns) - Virtual store projects
  22. checkouts (8 columns) - Checkout assist tracking

Remaining: 127 tables to be migrated in subsequent phases

Migration Benefits

Compile-Time Safety:

  • SeaORM entities provide compile-time type checking
  • No need for DATABASE_URL during compilation
  • Catch schema mismatches at build time

Developer Experience:

  • Modern async/await API
  • Active Record pattern for simple operations
  • Query builder for complex queries
  • Automatic connection pooling

Production Ready:

  • Battle-tested ORM used by major Rust projects
  • Excellent documentation and community support
  • Supports PostgreSQL, MySQL, SQLite

Architecture Decision

Following b00t gospel, we chose SeaORM over:

  • ❌ Raw SQL (error-prone, no type safety)
  • ❌ SQLx (compile-time checks require live DB)
  • ❌ Custom database layer (reinventing the wheel)
  • ✅ SeaORM (battle-tested, compile-time safe, async-first)

References