Sophie wiki

This guide covers Sophie Bot's database migration system using Beanie ODM.

Overview

Sophie uses Beanie's built-in migration system to manage database schema evolution. Migrations are versioned, reversible Python scripts that transform data between schema versions.

How It Works

Migration files are stored in
sophie_bot/db/migrations/
Each migration has a timestamp prefix:
YYYYMMDD_HHMMSS_description.py
Migrations define
Forward
and
Backward
classes for apply/rollback
Applied migrations are tracked in the
migration_states
collection
Migrations run automatically on startup for beta instance

Creating a Migration

Quick Start

This creates a file like:

sophie_bot/db/migrations/20240125_120000_add_user_preferences.py

Manual Creation

Create file:
sophie_bot/db/migrations/20240125_120000_add_user_preferences.py
Define old and new model structures if needed
Implement Forward and Backward migration classes
Test migration locally
Add tests to
tests/test_migrations.py

Migration Examples

Example 1: Add New Field

Example 2: Rename Field

Example 3: Convert Data Type

Example 4: Bulk Migration with Free Fall

Migration Types

Iterative Migration

When to use:

Most document transformations
When you need to transform data field-by-field
Simple field additions, renames, or type conversions

Pros:

Beanie handles document loading and saving
Memory efficient (processes one document at a time)
Can be resumed if interrupted
Good for large collections

Cons:

Limited control over database session
Can be slower for bulk operations
Hard to track progress

Free Fall Migration

When to use:

Bulk operations (rebuilding indexes, collections)
When you need full database control
Complex transformations requiring multiple collections
Performance-critical operations

Pros:

Full control over database session
Can use batch processing
Better performance for bulk operations
Can track progress manually

Cons:

More complex to implement
Must handle document loading/saving manually
Risk of memory issues with large collections

Running Migrations

Automatic (Recommended for Beta)

Migrations run automatically on startup when

instance_name=beta

:

Manual

Run all pending migrations:

Or using Python directly:

Check Status

Example output:

Rollback

Rollback a specific migration:

Or using Python:

Configuration

Configure migrations in

config.py

:

Transaction Support

MongoDB transactions provide atomicity for migrations, ensuring either all changes succeed or none do.

Enabling Transactions

Configure MongoDB replica set (required for transactions)
Enable replica set in config:

When to Use Transactions

Use transactions for:

Multi-collection migrations
Critical data transformations
Rollback safety is essential
Collections with relational dependencies

Avoid transactions for:

Very large collections (millions of documents)
Simple field additions
Non-critical migrations
When replica set is not available

Transaction Limitations

MongoDB transactions have limits:

Document size: 16MB per document
Transaction size: 16MB total
Operation count: Varies by MongoDB version
Execution time: 60 seconds default

For large collections, use free fall migration without transactions.

Handling Large Collections

Batching Strategy

For collections with millions of documents, implement batching:

Progress Monitoring

Add progress logging for long-running migrations:

Performance Tips

Use appropriate batch size: 1000-10000 typically works well
Create indexes before migration: Improves query performance
Drop indexes before bulk inserts: Faster for large insertions
Monitor memory usage: Adjust batch size if memory issues occur
Run during maintenance: Reduces impact on users

Testing

Running Tests

Or:

Writing Tests

Add tests to

tests/test_migrations.py

:

Deployment Workflow

Create migration on development branch
Test locally:
make migrate_up
Add tests to
tests/test_migrations.py
Test in staging (beta instance)
Verify status:
make migrate_status
Open merge request with migration details
CI runs tests automatically
Merge to main/beta
Deploy to beta - migrations run automatically
Verify in beta
Deploy to stable if needed

Troubleshooting

Migration Failed Mid-Execution

Symptoms:

Migration error in logs
Some documents migrated, some not
Migration state may or may not be recorded

Solutions:

Check logs for specific error details
Manually fix affected documents if needed
Re-run migration - it will skip already-applied steps
Consider rolling back if state is inconsistent

Migration Too Slow

Symptoms:

Migration taking hours to complete
High CPU/memory usage
Database slow to respond

Solutions:

Use
@free_fall_migration()
for better control
Implement batching with
migration_batch_size
Create appropriate indexes before migration
Consider running during maintenance window
Monitor and optimize database performance

Need to Roll Back

Procedure:

Stop application to prevent conflicts
Identify migration to rollback:
Run rollback:
Verify rollback with status check
Fix migration code if issue was in migration
Re-apply migration if needed

Important:

Rollbacks modify data - test in staging first
Rollbacks may take time for large collections
Monitor logs during rollback
Have data backup if possible

Transaction Errors

Symptoms:

Transaction numbers do not match
error
Transaction is not in progress
error
Migration fails with transaction-related error

Solutions:

Check MongoDB is running as replica set
Verify
mongo_use_replica_set = True
in config
Disable transactions for large collections:
Use free fall migration instead

Database Migrations

Overview

How It Works

Creating a Migration

Migration Examples

Migration Types

Running Migrations

Configuration

Transaction Support

Handling Large Collections

Testing

Deployment Workflow

Troubleshooting

Best Practices

References

Migration Checklist