# Transmutation
A comprehensive database migration and schema alteration tool built on SQLAlchemy and Alembic.
[](https://www.python.org/downloads/)
[](https://opensource.org/licenses/MIT)
## Features
- **Comprehensive Operations**: Column, table, index, and constraint operations
- **Reversible Migrations**: Built-in migration system with automatic rollback
- **Type Safety**: Full type hints for Python 3.8+
- **Database Support**: SQLite, PostgreSQL, MySQL, and more via SQLAlchemy
- **Simple API**: Easy-to-use functions for common operations
- **Advanced Features**: Batch operations, transaction management, custom SQL
- **Validation**: Automatic validation of operations before execution
- **Error Handling**: Comprehensive exception hierarchy for precise error handling
## Installation
```bash
pip install transmutation
```
## Quick Start
```python
from sqlalchemy import create_engine
import transmutation as tm
# Create engine
engine = create_engine('sqlite:///mydb.db')
# Direct operations
tm.add_column('users', 'email', str, engine, nullable=False)
tm.create_index('idx_email', 'users', 'email', engine, unique=True)
tm.rename_column('users', 'name', 'full_name', engine)
# Using the migration system for rollback capability
migration = tm.Migration(engine)
migration.add_column('users', 'phone', str)
migration.create_unique_constraint('uq_email', 'users', 'email')
migration.upgrade() # Apply changes
# If something goes wrong, rollback
migration.downgrade()
```
## Core Operations
### Column Operations
#### Add Column
```python
import transmutation as tm
# Basic column addition
tm.add_column('users', 'email', str, engine)
# With constraints
tm.add_column('users', 'age', int, engine, nullable=False, default=0)
# With server default
tm.add_column('users', 'created_at', 'datetime', engine,
server_default='CURRENT_TIMESTAMP')
```
#### Rename Column
```python
tm.rename_column('users', 'name', 'full_name', engine)
```
#### Drop Column
```python
tm.drop_column('users', 'middle_name', engine)
```
#### Alter Column
```python
# Change column properties
tm.alter_column('users', 'email', engine, nullable=False)
# Change type
tm.alter_column('users', 'age', engine, type_=int)
# Rename and modify
tm.alter_column('users', 'name', engine,
new_column_name='full_name', nullable=False)
```
### Table Operations
#### Create Table
```python
from sqlalchemy import Column, Integer, String
columns = [
Column('id', Integer, primary_key=True),
Column('name', String(50), nullable=False),
Column('email', String(100), unique=True)
]
tm.create_table('users', columns, engine)
```
#### Rename Table
```python
tm.rename_table('old_users', 'users', engine)
```
#### Copy Table
```python
from fullmetalalchemy.features import get_table
source_table = get_table('users', engine)
tm.copy_table(source_table, 'users_backup', engine)
# Copy structure only (no data)
tm.copy_table(source_table, 'users_template', engine, copy_data=False)
```
#### Drop Table
```python
tm.drop_table('old_table', engine, if_exists=True)
# With cascade
tm.drop_table('parent_table', engine, cascade=True)
```
#### Truncate Table
```python
tm.truncate_table('temp_data', engine)
```
#### Create Table from SELECT
```python
from sqlalchemy import select
from fullmetalalchemy.features import get_table
users = get_table('users', engine)
query = select(users).where(users.c.active == True)
tm.create_table_as('active_users', query, engine)
```
### Index Operations
#### Create Index
```python
# Single column index
tm.create_index('idx_email', 'users', 'email', engine)
# Composite index
tm.create_index('idx_name', 'users', ['last_name', 'first_name'], engine)
# Unique index
tm.create_unique_index('idx_unique_email', 'users', 'email', engine)
# Or using create_index with unique=True
tm.create_index('idx_email', 'users', 'email', engine, unique=True)
```
#### Drop Index
```python
tm.drop_index('idx_email', 'users', engine)
# Skip if doesn't exist
tm.drop_index('idx_email', 'users', engine, if_exists=True)
```
### Constraint Operations
#### Foreign Keys
```python
# Create foreign key
tm.create_foreign_key(
'fk_user_address',
'addresses', # source table
'user_id', # source column
'users', # referenced table
'id', # referenced column
engine,
ondelete='CASCADE'
)
# Composite foreign key
tm.create_foreign_key(
'fk_user_role',
'user_roles',
['user_id', 'role_id'],
'roles',
['user_id', 'id'],
engine
)
```
#### Unique Constraints
```python
# Single column
tm.create_unique_constraint('uq_email', 'users', 'email', engine)
# Multiple columns
tm.create_unique_constraint('uq_user_role', 'user_roles',
['user_id', 'role_id'], engine)
```
#### Check Constraints
```python
tm.create_check_constraint('ck_age_positive', 'users', 'age > 0', engine)
tm.create_check_constraint('ck_email_format', 'users',
"email LIKE '%@%'", engine)
```
#### Primary Keys
```python
# Create primary key (on table without one)
tm.create_primary_key('users', 'id', engine)
# Composite primary key
tm.create_primary_keys('user_roles', ['user_id', 'role_id'], engine)
# Replace existing primary key
from fullmetalalchemy.features import get_table
table = get_table('users', engine)
tm.replace_primary_key(table, 'uuid', engine)
# Replace with composite key
tm.replace_primary_keys(table, ['tenant_id', 'user_id'], engine)
```
#### Drop Constraints
```python
# Drop any constraint by name and type
tm.drop_constraint('fk_user_address', 'addresses', engine,
type_='foreignkey')
tm.drop_constraint('uq_email', 'users', engine, type_='unique')
tm.drop_constraint('ck_age_positive', 'users', engine, type_='check')
```
## Migration System
The Migration class provides a powerful way to manage schema changes with rollback capability.
### Basic Usage
```python
from sqlalchemy import create_engine
import transmutation as tm
engine = create_engine('sqlite:///mydb.db')
migration = tm.Migration(engine)
# Queue operations
migration.add_column('users', 'email', str, nullable=False)
migration.create_index('idx_email', 'users', 'email', unique=True)
migration.add_column('users', 'phone', str)
# Apply all changes
migration.upgrade()
# If something went wrong, rollback
migration.downgrade()
```
### Batch Operations with Auto-Rollback
```python
migration = tm.Migration(engine)
try:
with migration.batch_operations():
migration.add_column('users', 'email', str)
migration.create_index('idx_email', 'users', 'email')
migration.create_foreign_key('fk_user', 'posts', 'user_id',
'users', 'id')
# Changes are automatically applied on successful exit
except tm.MigrationError as e:
print(f"Migration failed and was rolled back: {e}")
```
### All Migration Methods
```python
migration = tm.Migration(engine)
# Column operations
migration.add_column('table', 'col', str)
migration.drop_column('table', 'col')
migration.rename_column('table', 'old', 'new')
migration.alter_column('table', 'col', nullable=False)
# Table operations
migration.create_table('table', columns)
migration.drop_table('table')
migration.rename_table('old', 'new')
migration.copy_table(table, 'new_table')
# Index operations
migration.create_index('idx', 'table', 'col')
migration.drop_index('idx', 'table', 'col')
migration.create_unique_index('idx', 'table', 'col')
# Constraint operations
migration.create_foreign_key('fk', 'source', 'col', 'ref', 'ref_col')
migration.create_unique_constraint('uq', 'table', 'col')
migration.create_check_constraint('ck', 'table', 'condition')
# Execute custom SQL
migration.execute_sql("UPDATE users SET active = 1")
# Apply changes
migration.upgrade()
# Check status
print(f"Pending: {migration.pending_operations()}")
print(f"Applied: {migration.applied_operations()}")
# Rollback
migration.downgrade()
# Clear queue without executing
migration.clear()
```
### Transaction Management
```python
# Auto-rollback enabled by default
migration = tm.Migration(engine, auto_transaction=True)
# Disable auto-rollback for manual control
migration = tm.Migration(engine, auto_transaction=False)
```
## Advanced Usage
### Schema Support
All operations support schema specification for databases that use schemas (PostgreSQL):
```python
# PostgreSQL schema support
tm.add_column('users', 'email', str, engine, schema='public')
tm.create_index('idx_email', 'users', 'email', engine, schema='public')
```
### Custom SQL Execution
```python
migration = tm.Migration(engine)
# Execute custom SQL
result = migration.execute_sql(
"UPDATE users SET status = 'active' WHERE last_login > :date",
date='2024-01-01'
)
```
### Validation
Transmutation automatically validates operations before execution:
```python
try:
tm.add_column('nonexistent_table', 'col', str, engine)
except tm.ValidationError as e:
print(f"Validation failed: {e}")
```
### Working with Alteration Classes
For advanced use cases, you can work directly with Alteration classes:
```python
from transmutation.alteration import AddColumn, CreateIndex
# Create alterations manually
add_col = AddColumn('users', 'email', str, engine)
create_idx = CreateIndex('idx_email', 'users', 'email', engine, unique=True)
# Apply
table = add_col.upgrade()
table = create_idx.upgrade()
# Rollback
create_idx.downgrade()
add_col.downgrade()
```
## Error Handling
Transmutation provides specific exceptions for different error types:
```python
import transmutation as tm
try:
tm.add_column('users', 'email', str, engine)
except tm.ColumnError as e:
print(f"Column operation failed: {e}")
except tm.ValidationError as e:
print(f"Validation failed: {e}")
except tm.TransmutationError as e:
print(f"General error: {e}")
# Migration-specific errors
try:
migration.upgrade()
except tm.MigrationError as e:
print(f"Migration failed: {e}")
except tm.RollbackError as e:
print(f"Rollback failed: {e}")
```
### Available Exceptions
- `TransmutationError` - Base exception for all transmutation errors
- `MigrationError` - Migration operation failed
- `ColumnError` - Column operation failed
- `TableError` - Table operation failed
- `ConstraintError` - Constraint operation failed
- `IndexError` - Index operation failed
- `ValidationError` - Validation failed before operation
- `RollbackError` - Rollback operation failed
## Database Support
Transmutation works with any database supported by SQLAlchemy and Alembic:
- **SQLite**: Full support with no external dependencies
- **PostgreSQL**: Full support with schema capabilities
- **MySQL/MariaDB**: Full support
- **Oracle**: Supported via SQLAlchemy
- **Microsoft SQL Server**: Supported via SQLAlchemy
### SQLite
SQLite is the default and requires no additional setup. Foreign keys can be enabled:
```python
from sqlalchemy import create_engine, event
engine = create_engine('sqlite:///mydb.db')
@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_conn, connection_record):
cursor = dbapi_conn.cursor()
cursor.execute("PRAGMA foreign_keys=ON")
cursor.close()
```
### PostgreSQL
Full schema support:
```python
tm.create_table('users', columns, engine, schema='myschema')
```
## API Organization
Transmutation provides a well-organized API with operations grouped by category:
```python
import transmutation as tm
# Or import specific modules
from transmutation import column, table, index, constraint
from transmutation import Migration
# Column operations
from transmutation.column import add_column, rename_column, drop_column, alter_column
# Table operations
from transmutation.table import create_table, drop_table, rename_table, copy_table
# Index operations
from transmutation.index import create_index, drop_index, create_unique_index
# Constraint operations
from transmutation.constraint import (
create_foreign_key,
create_unique_constraint,
create_check_constraint,
drop_constraint
)
```
## Examples
### Complete Migration Example
```python
from sqlalchemy import create_engine, Column, Integer, String
import transmutation as tm
# Setup
engine = create_engine('sqlite:///myapp.db')
# Create a new table
columns = [
Column('id', Integer, primary_key=True),
Column('username', String(50), nullable=False),
Column('email', String(100))
]
tm.create_table('users', columns, engine)
# Add indexes
tm.create_unique_index('idx_username', 'users', 'username', engine)
tm.create_index('idx_email', 'users', 'email', engine)
# Add constraints
tm.create_check_constraint('ck_username_length', 'users',
'LENGTH(username) >= 3', engine)
# Modify existing table
tm.add_column('users', 'created_at', 'datetime', engine,
server_default='CURRENT_TIMESTAMP')
tm.alter_column('users', 'email', engine, nullable=False)
```
### Using Migration for Complex Changes
```python
from sqlalchemy import create_engine
import transmutation as tm
engine = create_engine('sqlite:///myapp.db')
migration = tm.Migration(engine)
# Queue multiple related changes
migration.add_column('users', 'status', str, default='active')
migration.create_index('idx_status', 'users', 'status')
migration.add_column('users', 'last_login', 'datetime')
# Create posts table with foreign key to users
from sqlalchemy import Column, Integer, String, Text
posts_columns = [
Column('id', Integer, primary_key=True),
Column('user_id', Integer, nullable=False),
Column('title', String(200), nullable=False),
Column('content', Text)
]
migration.create_table('posts', posts_columns)
migration.create_foreign_key('fk_post_user', 'posts', 'user_id',
'users', 'id', ondelete='CASCADE')
migration.create_index('idx_user_posts', 'posts', 'user_id')
# Apply all changes atomically
migration.upgrade()
# If anything goes wrong:
# migration.downgrade()
```
### Data Migration with Custom SQL
```python
migration = tm.Migration(engine)
# Add new column
migration.add_column('users', 'full_name', str)
# Populate it with data using custom SQL
migration.execute_sql(
"UPDATE users SET full_name = name || ' ' || surname WHERE surname IS NOT NULL"
)
migration.execute_sql(
"UPDATE users SET full_name = name WHERE surname IS NULL"
)
# Drop old columns
migration.drop_column('users', 'name')
migration.drop_column('users', 'surname')
# Apply all changes
migration.upgrade()
```
## Development
### Setup
```bash
# Clone repository
git clone https://github.com/odosmatthews/transmutation.git
cd transmutation
# Install development dependencies
pip install -e ".[dev]"
```
### Running Tests
```bash
# All tests
pytest
# Specific test file
pytest tests/test_alter.py
# With coverage report
pytest --cov=transmutation --cov-report=html
# Open coverage report
open htmlcov/index.html
```
### Code Quality
```bash
# Run type checking
mypy src
# Run linter
ruff check src tests
# Auto-fix linting issues
ruff check --fix src tests
# Format code
black src tests
# Sort imports
isort src tests
```
## Best Practices
### 1. Use Migration for Complex Changes
For multiple related changes, use the Migration system:
```python
# Good - atomic, reversible
migration = tm.Migration(engine)
migration.add_column('users', 'email', str)
migration.create_index('idx_email', 'users', 'email')
migration.upgrade()
# Less ideal - individual operations
tm.add_column('users', 'email', str, engine)
tm.create_index('idx_email', 'users', 'email', engine)
```
### 2. Always Handle Errors
```python
try:
migration.upgrade()
except tm.MigrationError as e:
logger.error(f"Migration failed: {e}")
migration.downgrade()
raise
```
### 3. Use Validation
Transmutation validates operations automatically, but you can also validate manually:
```python
from transmutation.utils import validate_table_exists, validate_column_exists
validate_table_exists('users', engine)
validate_column_exists('users', 'email', engine)
```
### 4. Test Your Migrations
Always test migrations in a development environment first:
```python
def test_migration():
# Setup test database
engine = create_engine('sqlite:///:memory:')
# Run migration
migration = tm.Migration(engine)
migration.add_column('users', 'new_col', str)
migration.upgrade()
# Verify
from fullmetalalchemy.features import get_table
table = get_table('users', engine)
assert 'new_col' in table.columns
# Test rollback
migration.downgrade()
table = get_table('users', engine)
assert 'new_col' not in table.columns
```
## Contributing
Contributions are welcome! Please feel free to submit a Pull Request.
1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request
### Development Guidelines
- Add tests for new features
- Maintain test coverage above 65%
- Follow type hints for all functions
- Add comprehensive docstrings
- Run linters before committing
## License
This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
## Acknowledgments
Built on top of:
- [SQLAlchemy](https://www.sqlalchemy.org/) - The Python SQL toolkit
- [Alembic](https://alembic.sqlalchemy.org/) - Database migration tool
- [fullmetalalchemy](https://github.com/kajuberdut/fullmetalalchemy) - SQLAlchemy utilities
## Support
For issues, questions, or contributions, please visit the GitHub repository.
Raw data
{
"_id": null,
"home_page": null,
"name": "transmutation",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": null,
"keywords": "database, migration, schema, alembic, sqlalchemy",
"author": "Odos Matthews",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/d6/b8/02955ad0b4b186af6dac2afa9dbead86225ad43889649781c3f651649b6f/transmutation-1.0.0.tar.gz",
"platform": "unix",
"description": "# Transmutation\n\nA comprehensive database migration and schema alteration tool built on SQLAlchemy and Alembic.\n\n[](https://www.python.org/downloads/)\n[](https://opensource.org/licenses/MIT)\n\n## Features\n\n- **Comprehensive Operations**: Column, table, index, and constraint operations\n- **Reversible Migrations**: Built-in migration system with automatic rollback\n- **Type Safety**: Full type hints for Python 3.8+\n- **Database Support**: SQLite, PostgreSQL, MySQL, and more via SQLAlchemy\n- **Simple API**: Easy-to-use functions for common operations\n- **Advanced Features**: Batch operations, transaction management, custom SQL\n- **Validation**: Automatic validation of operations before execution\n- **Error Handling**: Comprehensive exception hierarchy for precise error handling\n\n## Installation\n\n```bash\npip install transmutation\n```\n\n## Quick Start\n\n```python\nfrom sqlalchemy import create_engine\nimport transmutation as tm\n\n# Create engine\nengine = create_engine('sqlite:///mydb.db')\n\n# Direct operations\ntm.add_column('users', 'email', str, engine, nullable=False)\ntm.create_index('idx_email', 'users', 'email', engine, unique=True)\ntm.rename_column('users', 'name', 'full_name', engine)\n\n# Using the migration system for rollback capability\nmigration = tm.Migration(engine)\nmigration.add_column('users', 'phone', str)\nmigration.create_unique_constraint('uq_email', 'users', 'email')\nmigration.upgrade() # Apply changes\n\n# If something goes wrong, rollback\nmigration.downgrade()\n```\n\n## Core Operations\n\n### Column Operations\n\n#### Add Column\n\n```python\nimport transmutation as tm\n\n# Basic column addition\ntm.add_column('users', 'email', str, engine)\n\n# With constraints\ntm.add_column('users', 'age', int, engine, nullable=False, default=0)\n\n# With server default\ntm.add_column('users', 'created_at', 'datetime', engine, \n server_default='CURRENT_TIMESTAMP')\n```\n\n#### Rename Column\n\n```python\ntm.rename_column('users', 'name', 'full_name', engine)\n```\n\n#### Drop Column\n\n```python\ntm.drop_column('users', 'middle_name', engine)\n```\n\n#### Alter Column\n\n```python\n# Change column properties\ntm.alter_column('users', 'email', engine, nullable=False)\n\n# Change type\ntm.alter_column('users', 'age', engine, type_=int)\n\n# Rename and modify\ntm.alter_column('users', 'name', engine, \n new_column_name='full_name', nullable=False)\n```\n\n### Table Operations\n\n#### Create Table\n\n```python\nfrom sqlalchemy import Column, Integer, String\n\ncolumns = [\n Column('id', Integer, primary_key=True),\n Column('name', String(50), nullable=False),\n Column('email', String(100), unique=True)\n]\n\ntm.create_table('users', columns, engine)\n```\n\n#### Rename Table\n\n```python\ntm.rename_table('old_users', 'users', engine)\n```\n\n#### Copy Table\n\n```python\nfrom fullmetalalchemy.features import get_table\n\nsource_table = get_table('users', engine)\ntm.copy_table(source_table, 'users_backup', engine)\n\n# Copy structure only (no data)\ntm.copy_table(source_table, 'users_template', engine, copy_data=False)\n```\n\n#### Drop Table\n\n```python\ntm.drop_table('old_table', engine, if_exists=True)\n\n# With cascade\ntm.drop_table('parent_table', engine, cascade=True)\n```\n\n#### Truncate Table\n\n```python\ntm.truncate_table('temp_data', engine)\n```\n\n#### Create Table from SELECT\n\n```python\nfrom sqlalchemy import select\nfrom fullmetalalchemy.features import get_table\n\nusers = get_table('users', engine)\nquery = select(users).where(users.c.active == True)\n\ntm.create_table_as('active_users', query, engine)\n```\n\n### Index Operations\n\n#### Create Index\n\n```python\n# Single column index\ntm.create_index('idx_email', 'users', 'email', engine)\n\n# Composite index\ntm.create_index('idx_name', 'users', ['last_name', 'first_name'], engine)\n\n# Unique index\ntm.create_unique_index('idx_unique_email', 'users', 'email', engine)\n\n# Or using create_index with unique=True\ntm.create_index('idx_email', 'users', 'email', engine, unique=True)\n```\n\n#### Drop Index\n\n```python\ntm.drop_index('idx_email', 'users', engine)\n\n# Skip if doesn't exist\ntm.drop_index('idx_email', 'users', engine, if_exists=True)\n```\n\n### Constraint Operations\n\n#### Foreign Keys\n\n```python\n# Create foreign key\ntm.create_foreign_key(\n 'fk_user_address',\n 'addresses', # source table\n 'user_id', # source column\n 'users', # referenced table\n 'id', # referenced column\n engine,\n ondelete='CASCADE'\n)\n\n# Composite foreign key\ntm.create_foreign_key(\n 'fk_user_role',\n 'user_roles',\n ['user_id', 'role_id'],\n 'roles',\n ['user_id', 'id'],\n engine\n)\n```\n\n#### Unique Constraints\n\n```python\n# Single column\ntm.create_unique_constraint('uq_email', 'users', 'email', engine)\n\n# Multiple columns\ntm.create_unique_constraint('uq_user_role', 'user_roles', \n ['user_id', 'role_id'], engine)\n```\n\n#### Check Constraints\n\n```python\ntm.create_check_constraint('ck_age_positive', 'users', 'age > 0', engine)\n\ntm.create_check_constraint('ck_email_format', 'users', \n \"email LIKE '%@%'\", engine)\n```\n\n#### Primary Keys\n\n```python\n# Create primary key (on table without one)\ntm.create_primary_key('users', 'id', engine)\n\n# Composite primary key\ntm.create_primary_keys('user_roles', ['user_id', 'role_id'], engine)\n\n# Replace existing primary key\nfrom fullmetalalchemy.features import get_table\ntable = get_table('users', engine)\ntm.replace_primary_key(table, 'uuid', engine)\n\n# Replace with composite key\ntm.replace_primary_keys(table, ['tenant_id', 'user_id'], engine)\n```\n\n#### Drop Constraints\n\n```python\n# Drop any constraint by name and type\ntm.drop_constraint('fk_user_address', 'addresses', engine, \n type_='foreignkey')\n\ntm.drop_constraint('uq_email', 'users', engine, type_='unique')\n\ntm.drop_constraint('ck_age_positive', 'users', engine, type_='check')\n```\n\n## Migration System\n\nThe Migration class provides a powerful way to manage schema changes with rollback capability.\n\n### Basic Usage\n\n```python\nfrom sqlalchemy import create_engine\nimport transmutation as tm\n\nengine = create_engine('sqlite:///mydb.db')\nmigration = tm.Migration(engine)\n\n# Queue operations\nmigration.add_column('users', 'email', str, nullable=False)\nmigration.create_index('idx_email', 'users', 'email', unique=True)\nmigration.add_column('users', 'phone', str)\n\n# Apply all changes\nmigration.upgrade()\n\n# If something went wrong, rollback\nmigration.downgrade()\n```\n\n### Batch Operations with Auto-Rollback\n\n```python\nmigration = tm.Migration(engine)\n\ntry:\n with migration.batch_operations():\n migration.add_column('users', 'email', str)\n migration.create_index('idx_email', 'users', 'email')\n migration.create_foreign_key('fk_user', 'posts', 'user_id', \n 'users', 'id')\n # Changes are automatically applied on successful exit\nexcept tm.MigrationError as e:\n print(f\"Migration failed and was rolled back: {e}\")\n```\n\n### All Migration Methods\n\n```python\nmigration = tm.Migration(engine)\n\n# Column operations\nmigration.add_column('table', 'col', str)\nmigration.drop_column('table', 'col')\nmigration.rename_column('table', 'old', 'new')\nmigration.alter_column('table', 'col', nullable=False)\n\n# Table operations\nmigration.create_table('table', columns)\nmigration.drop_table('table')\nmigration.rename_table('old', 'new')\nmigration.copy_table(table, 'new_table')\n\n# Index operations\nmigration.create_index('idx', 'table', 'col')\nmigration.drop_index('idx', 'table', 'col')\nmigration.create_unique_index('idx', 'table', 'col')\n\n# Constraint operations\nmigration.create_foreign_key('fk', 'source', 'col', 'ref', 'ref_col')\nmigration.create_unique_constraint('uq', 'table', 'col')\nmigration.create_check_constraint('ck', 'table', 'condition')\n\n# Execute custom SQL\nmigration.execute_sql(\"UPDATE users SET active = 1\")\n\n# Apply changes\nmigration.upgrade()\n\n# Check status\nprint(f\"Pending: {migration.pending_operations()}\")\nprint(f\"Applied: {migration.applied_operations()}\")\n\n# Rollback\nmigration.downgrade()\n\n# Clear queue without executing\nmigration.clear()\n```\n\n### Transaction Management\n\n```python\n# Auto-rollback enabled by default\nmigration = tm.Migration(engine, auto_transaction=True)\n\n# Disable auto-rollback for manual control\nmigration = tm.Migration(engine, auto_transaction=False)\n```\n\n## Advanced Usage\n\n### Schema Support\n\nAll operations support schema specification for databases that use schemas (PostgreSQL):\n\n```python\n# PostgreSQL schema support\ntm.add_column('users', 'email', str, engine, schema='public')\ntm.create_index('idx_email', 'users', 'email', engine, schema='public')\n```\n\n### Custom SQL Execution\n\n```python\nmigration = tm.Migration(engine)\n\n# Execute custom SQL\nresult = migration.execute_sql(\n \"UPDATE users SET status = 'active' WHERE last_login > :date\",\n date='2024-01-01'\n)\n```\n\n### Validation\n\nTransmutation automatically validates operations before execution:\n\n```python\ntry:\n tm.add_column('nonexistent_table', 'col', str, engine)\nexcept tm.ValidationError as e:\n print(f\"Validation failed: {e}\")\n```\n\n### Working with Alteration Classes\n\nFor advanced use cases, you can work directly with Alteration classes:\n\n```python\nfrom transmutation.alteration import AddColumn, CreateIndex\n\n# Create alterations manually\nadd_col = AddColumn('users', 'email', str, engine)\ncreate_idx = CreateIndex('idx_email', 'users', 'email', engine, unique=True)\n\n# Apply\ntable = add_col.upgrade()\ntable = create_idx.upgrade()\n\n# Rollback\ncreate_idx.downgrade()\nadd_col.downgrade()\n```\n\n## Error Handling\n\nTransmutation provides specific exceptions for different error types:\n\n```python\nimport transmutation as tm\n\ntry:\n tm.add_column('users', 'email', str, engine)\nexcept tm.ColumnError as e:\n print(f\"Column operation failed: {e}\")\nexcept tm.ValidationError as e:\n print(f\"Validation failed: {e}\")\nexcept tm.TransmutationError as e:\n print(f\"General error: {e}\")\n\n# Migration-specific errors\ntry:\n migration.upgrade()\nexcept tm.MigrationError as e:\n print(f\"Migration failed: {e}\")\nexcept tm.RollbackError as e:\n print(f\"Rollback failed: {e}\")\n```\n\n### Available Exceptions\n\n- `TransmutationError` - Base exception for all transmutation errors\n- `MigrationError` - Migration operation failed\n- `ColumnError` - Column operation failed\n- `TableError` - Table operation failed\n- `ConstraintError` - Constraint operation failed\n- `IndexError` - Index operation failed\n- `ValidationError` - Validation failed before operation\n- `RollbackError` - Rollback operation failed\n\n## Database Support\n\nTransmutation works with any database supported by SQLAlchemy and Alembic:\n\n- **SQLite**: Full support with no external dependencies\n- **PostgreSQL**: Full support with schema capabilities\n- **MySQL/MariaDB**: Full support\n- **Oracle**: Supported via SQLAlchemy\n- **Microsoft SQL Server**: Supported via SQLAlchemy\n\n### SQLite\n\nSQLite is the default and requires no additional setup. Foreign keys can be enabled:\n\n```python\nfrom sqlalchemy import create_engine, event\n\nengine = create_engine('sqlite:///mydb.db')\n\n@event.listens_for(engine, \"connect\")\ndef set_sqlite_pragma(dbapi_conn, connection_record):\n cursor = dbapi_conn.cursor()\n cursor.execute(\"PRAGMA foreign_keys=ON\")\n cursor.close()\n```\n\n### PostgreSQL\n\nFull schema support:\n\n```python\ntm.create_table('users', columns, engine, schema='myschema')\n```\n\n## API Organization\n\nTransmutation provides a well-organized API with operations grouped by category:\n\n```python\nimport transmutation as tm\n\n# Or import specific modules\nfrom transmutation import column, table, index, constraint\nfrom transmutation import Migration\n\n# Column operations\nfrom transmutation.column import add_column, rename_column, drop_column, alter_column\n\n# Table operations \nfrom transmutation.table import create_table, drop_table, rename_table, copy_table\n\n# Index operations\nfrom transmutation.index import create_index, drop_index, create_unique_index\n\n# Constraint operations\nfrom transmutation.constraint import (\n create_foreign_key,\n create_unique_constraint,\n create_check_constraint,\n drop_constraint\n)\n```\n\n## Examples\n\n### Complete Migration Example\n\n```python\nfrom sqlalchemy import create_engine, Column, Integer, String\nimport transmutation as tm\n\n# Setup\nengine = create_engine('sqlite:///myapp.db')\n\n# Create a new table\ncolumns = [\n Column('id', Integer, primary_key=True),\n Column('username', String(50), nullable=False),\n Column('email', String(100))\n]\ntm.create_table('users', columns, engine)\n\n# Add indexes\ntm.create_unique_index('idx_username', 'users', 'username', engine)\ntm.create_index('idx_email', 'users', 'email', engine)\n\n# Add constraints\ntm.create_check_constraint('ck_username_length', 'users', \n 'LENGTH(username) >= 3', engine)\n\n# Modify existing table\ntm.add_column('users', 'created_at', 'datetime', engine,\n server_default='CURRENT_TIMESTAMP')\ntm.alter_column('users', 'email', engine, nullable=False)\n```\n\n### Using Migration for Complex Changes\n\n```python\nfrom sqlalchemy import create_engine\nimport transmutation as tm\n\nengine = create_engine('sqlite:///myapp.db')\nmigration = tm.Migration(engine)\n\n# Queue multiple related changes\nmigration.add_column('users', 'status', str, default='active')\nmigration.create_index('idx_status', 'users', 'status')\nmigration.add_column('users', 'last_login', 'datetime')\n\n# Create posts table with foreign key to users\nfrom sqlalchemy import Column, Integer, String, Text\nposts_columns = [\n Column('id', Integer, primary_key=True),\n Column('user_id', Integer, nullable=False),\n Column('title', String(200), nullable=False),\n Column('content', Text)\n]\nmigration.create_table('posts', posts_columns)\nmigration.create_foreign_key('fk_post_user', 'posts', 'user_id', \n 'users', 'id', ondelete='CASCADE')\nmigration.create_index('idx_user_posts', 'posts', 'user_id')\n\n# Apply all changes atomically\nmigration.upgrade()\n\n# If anything goes wrong:\n# migration.downgrade()\n```\n\n### Data Migration with Custom SQL\n\n```python\nmigration = tm.Migration(engine)\n\n# Add new column\nmigration.add_column('users', 'full_name', str)\n\n# Populate it with data using custom SQL\nmigration.execute_sql(\n \"UPDATE users SET full_name = name || ' ' || surname WHERE surname IS NOT NULL\"\n)\nmigration.execute_sql(\n \"UPDATE users SET full_name = name WHERE surname IS NULL\"\n)\n\n# Drop old columns\nmigration.drop_column('users', 'name')\nmigration.drop_column('users', 'surname')\n\n# Apply all changes\nmigration.upgrade()\n```\n\n## Development\n\n### Setup\n\n```bash\n# Clone repository\ngit clone https://github.com/odosmatthews/transmutation.git\ncd transmutation\n\n# Install development dependencies\npip install -e \".[dev]\"\n```\n\n### Running Tests\n\n```bash\n# All tests\npytest\n\n# Specific test file\npytest tests/test_alter.py\n\n# With coverage report\npytest --cov=transmutation --cov-report=html\n\n# Open coverage report\nopen htmlcov/index.html\n```\n\n### Code Quality\n\n```bash\n# Run type checking\nmypy src\n\n# Run linter\nruff check src tests\n\n# Auto-fix linting issues\nruff check --fix src tests\n\n# Format code\nblack src tests\n\n# Sort imports\nisort src tests\n```\n\n## Best Practices\n\n### 1. Use Migration for Complex Changes\n\nFor multiple related changes, use the Migration system:\n\n```python\n# Good - atomic, reversible\nmigration = tm.Migration(engine)\nmigration.add_column('users', 'email', str)\nmigration.create_index('idx_email', 'users', 'email')\nmigration.upgrade()\n\n# Less ideal - individual operations\ntm.add_column('users', 'email', str, engine)\ntm.create_index('idx_email', 'users', 'email', engine)\n```\n\n### 2. Always Handle Errors\n\n```python\ntry:\n migration.upgrade()\nexcept tm.MigrationError as e:\n logger.error(f\"Migration failed: {e}\")\n migration.downgrade()\n raise\n```\n\n### 3. Use Validation\n\nTransmutation validates operations automatically, but you can also validate manually:\n\n```python\nfrom transmutation.utils import validate_table_exists, validate_column_exists\n\nvalidate_table_exists('users', engine)\nvalidate_column_exists('users', 'email', engine)\n```\n\n### 4. Test Your Migrations\n\nAlways test migrations in a development environment first:\n\n```python\ndef test_migration():\n # Setup test database\n engine = create_engine('sqlite:///:memory:')\n \n # Run migration\n migration = tm.Migration(engine)\n migration.add_column('users', 'new_col', str)\n migration.upgrade()\n \n # Verify\n from fullmetalalchemy.features import get_table\n table = get_table('users', engine)\n assert 'new_col' in table.columns\n \n # Test rollback\n migration.downgrade()\n table = get_table('users', engine)\n assert 'new_col' not in table.columns\n```\n\n## Contributing\n\nContributions are welcome! Please feel free to submit a Pull Request.\n\n1. Fork the repository\n2. Create your feature branch (`git checkout -b feature/amazing-feature`)\n3. Commit your changes (`git commit -m 'Add amazing feature'`)\n4. Push to the branch (`git push origin feature/amazing-feature`)\n5. Open a Pull Request\n\n### Development Guidelines\n\n- Add tests for new features\n- Maintain test coverage above 65%\n- Follow type hints for all functions\n- Add comprehensive docstrings\n- Run linters before committing\n\n## License\n\nThis project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.\n\n## Acknowledgments\n\nBuilt on top of:\n- [SQLAlchemy](https://www.sqlalchemy.org/) - The Python SQL toolkit\n- [Alembic](https://alembic.sqlalchemy.org/) - Database migration tool\n- [fullmetalalchemy](https://github.com/kajuberdut/fullmetalalchemy) - SQLAlchemy utilities\n\n## Support\n\nFor issues, questions, or contributions, please visit the GitHub repository.\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Comprehensive database migration and schema alteration tool using SQLAlchemy and Alembic",
"version": "1.0.0",
"project_urls": {
"Homepage": "https://github.com/odosmatthews/transmutation",
"Repository": "https://github.com/odosmatthews/transmutation"
},
"split_keywords": [
"database",
" migration",
" schema",
" alembic",
" sqlalchemy"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "f5514a464b33c931b190b2591829c1f2659e8d65337602987f4c7b0e085ae3a4",
"md5": "d6ad72b71ea5e7832d6133e825b581fa",
"sha256": "7362fd6dbd344ce877a803c3ab9ea9e41d11f01711697980e6802df5159dde6c"
},
"downloads": -1,
"filename": "transmutation-1.0.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "d6ad72b71ea5e7832d6133e825b581fa",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 24134,
"upload_time": "2025-10-19T14:49:35",
"upload_time_iso_8601": "2025-10-19T14:49:35.689571Z",
"url": "https://files.pythonhosted.org/packages/f5/51/4a464b33c931b190b2591829c1f2659e8d65337602987f4c7b0e085ae3a4/transmutation-1.0.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "d6b802955ad0b4b186af6dac2afa9dbead86225ad43889649781c3f651649b6f",
"md5": "e8c1f5f8a88a6976a397ff2cd39c2503",
"sha256": "3c13a5e6efced25c84081f826a7078f0c1bd1392aa3fa63e726ec308cbe8ee57"
},
"downloads": -1,
"filename": "transmutation-1.0.0.tar.gz",
"has_sig": false,
"md5_digest": "e8c1f5f8a88a6976a397ff2cd39c2503",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 28415,
"upload_time": "2025-10-19T14:49:36",
"upload_time_iso_8601": "2025-10-19T14:49:36.962089Z",
"url": "https://files.pythonhosted.org/packages/d6/b8/02955ad0b4b186af6dac2afa9dbead86225ad43889649781c3f651649b6f/transmutation-1.0.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-10-19 14:49:36",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "odosmatthews",
"github_project": "transmutation",
"github_not_found": true,
"lcname": "transmutation"
}
JSON
