# dbPorter - Database Migration Tool
A powerful, flexible database migration tool built with Python that supports both YAML-based declarative migrations and Python-based programmatic migrations. This tool provides comprehensive schema management capabilities with automatic rollback support and metadata preservation.
## 🚀 Features
- **Dual Migration Support**: YAML-based declarative migrations and Python-based programmatic migrations
- **Migration Graph (DAG)**: Support for branching migrations with dependency management like Alembic
- **Automatic Rollback**: Intelligent rollback system with metadata preservation for safe reversions
- **Schema Auto-Generation**: Automatically detect and generate migrations from schema differences
- **Enhanced Metadata**: Captures column metadata for accurate rollback operations
- **Table Rename Support**: Handle table renames with mapping configuration
- **Dry-Run Capability**: Preview migration changes before applying them
- **Comprehensive Logging**: Track all applied migrations with detailed payload information
- **Multiple Database Support**: Works with any SQLAlchemy-supported database
- **Parallel Development**: Support for multiple developers working on migrations simultaneously
- **Conflict Detection**: Automatic detection and resolution of migration conflicts
- **Schema Inspection**: Comprehensive database and migration status inspection with visual indicators
- **Sync Validation**: Automatic detection of database schema sync issues
## 📋 Prerequisites
- **Python 3.8+** (Python 3.9+ recommended)
- **Core Dependencies**: SQLAlchemy, PyYAML, Typer, python-dotenv, tabulate
- **Database Driver**: Choose based on your database:
- **SQLite**: No additional driver needed (included with Python)
- **PostgreSQL**: `psycopg2-binary` or `psycopg2`
- **MySQL**: `PyMySQL` or `mysqlclient`
- **SQL Server**: `pyodbc` or `pymssql`
- **Oracle**: `cx-Oracle`
## 🛠️ Installation
1. **Clone the repository**
```bash
git clone <repository-url>
cd dbPorter
```
2. **Install dependencies**
**Option A: Automated installation (recommended)**
```bash
# For SQLite (default)
python install.py
# For PostgreSQL
python install.py --database postgresql
# For MySQL
python install.py --database mysql
# For development
python install.py --dev
```
**Option B: Manual installation**
```bash
# Full installation (recommended)
pip install -r requirements.txt
# Minimal installation (SQLite only)
pip install -r requirements-minimal.txt
# Development installation
pip install -r requirements-dev.txt
```
**Option C: Quick installation**
```bash
pip install sqlalchemy pyyaml typer python-dotenv tabulate
```
3. **Set up database configuration (optional)**
The tool auto-discovers database URLs from multiple sources:
**Option A: Environment Variables**
```bash
export DB_URL="sqlite:///your_database.db"
# or for PostgreSQL: postgresql://user:password@localhost/dbname
# or for MySQL: mysql://user:password@localhost/dbname
```
**Option B: .env file**
Create a `.env` file in the project root:
```env
DB_URL=sqlite:///your_database.db
DATABASE_URL=postgresql://user:password@localhost/dbname
```
**Option C: Configuration files**
The tool also looks for database URLs in:
- `config.py` (DATABASE_URL variable)
- `settings.py` (DB_URL variable)
- `database.py` (database_url variable)
**Option D: Default fallback**
If no database URL is found, defaults to `sqlite:///migrate.db`
## 🏗️ Project Structure
```
dbPorter/
├── main.py # Entry point
├── commands.py # CLI commands implementation
├── models.py # SQLAlchemy metadata definitions
├── migrations/ # Migration files directory
│ ├── *.yml # YAML migration files
│ └── *.py # Python migration files
├── src/ # Core migration logic
│ ├── applier.py # Migration application logic
│ ├── db.py # Database connection and metadata
│ ├── executors.py # SQL operation executors
│ ├── migration_loader.py # Migration file parsing
│ └── planner.py # Migration planning logic
└── utils/ # Utility functions
└── utils.py # Helper functions
```
## 🚀 Quick Start
1. **Initialize the migration system**
```bash
python main.py init-db
```
2. **Define your schema in a models file**
```python
# models.py, schema.py, database.py, or any Python file
from sqlalchemy import Table, Column, Integer, String, MetaData
metadata = MetaData()
users = Table(
"users", metadata,
Column("id", Integer, primary_key=True),
Column("name", String(100), nullable=False),
Column("email", String(255), unique=True)
)
```
3. **Auto-generate your first migration**
```bash
python main.py autogenerate -m "Initial schema"
```
4. **Apply the migration**
```bash
python main.py apply
```
## 📖 Command Reference
### Database Initialization
```bash
# Initialize migration metadata table
python main.py init-db [--db DATABASE_URL]
```
### Migration Planning
```bash
# Plan a migration (dry-run)
python main.py plan [MIGRATION_FILE] [--rename-map RENAME_MAP_FILE]
# Examples:
python main.py plan # Use latest migration
python main.py plan migrations/20250101120000_add_users.yml
python main.py plan --rename-map custom_renames.yml
```
### Migration Application
```bash
# Apply migrations
python main.py apply [MIGRATION_FILE] [OPTIONS]
# Options:
# --db DATABASE_URL Database connection string
# --rename-map FILE Table rename mapping file
# --dry-run Show what would be done without executing
# --latest Use the latest migration file
# Examples:
python main.py apply # Apply latest migration
python main.py apply --latest # Explicitly use latest
python main.py apply --dry-run # Preview changes
python main.py apply migrations/20250101120000_add_users.yml
```
### Migration Rollback
```bash
# Rollback the last applied migration
python main.py rollback [--db DATABASE_URL]
# Examples:
python main.py rollback
python main.py rollback --db "postgresql://user:pass@localhost/db"
```
### Auto-Generation
```bash
# Auto-generate migration from schema differences
python main.py autogenerate [--db DATABASE_URL] [-m MESSAGE]
# Examples:
python main.py autogenerate
python main.py autogenerate -m "Add user profile table"
python main.py autogenerate --db "sqlite:///mydb.db"
```
### Migration Registration
```bash
# Register existing migration with timestamp
python main.py revision MIGRATION_FILE
# Example:
python main.py revision my_migration.yml
# Creates: migrations/20250101120000_my_migration.yml
```
### Models Discovery
```bash
# Discover and validate models files
python main.py discover-models [--models-file PATH]
# Examples:
python main.py discover-models # Auto-discover models file
python main.py discover-models --models-file "my_schema.py"
```
### Database Discovery
```bash
# Discover and validate database configuration
python main.py discover-db [--db URL]
# Examples:
python main.py discover-db # Auto-discover database URL
python main.py discover-db --db "postgresql://user:pass@localhost/db"
```
### One-Time Database Configuration
**Configure once, use everywhere!** Set up your database connection once during `init-db`, and all future commands will automatically use the same configuration.
#### **Step 1: Configure Database (One Time Only)**
```bash
# PostgreSQL (most common)
python main.py init-db --host localhost --port 5432 --user myuser --password mypass --database mydb --type postgresql
# MySQL
python main.py init-db --host localhost --port 3306 --user myuser --password mypass --database mydb --type mysql
# SQLite (no credentials needed)
python main.py init-db --database myapp.db --type sqlite
# Using complete URL
python main.py init-db --db "postgresql://user:pass@localhost/db"
```
#### **Step 2: Use Commands Without Database Parameters**
```bash
# All these commands automatically use the saved configuration!
python main.py apply
python main.py rollback
python main.py autogenerate -m "Add new table"
python main.py plan
```
#### **Configuration Management**
```bash
# Show current configuration
python main.py show-config
# Reset configuration (go back to auto-discovery)
python main.py reset-config
```
**Benefits:**
- ✅ **Configure Once**: Set database connection once during `init-db`
- ✅ **Use Everywhere**: All commands automatically use saved configuration
- ✅ **Override When Needed**: Can still override with command-line arguments
- ✅ **Secure**: Credentials stored in local config file (not in code)
- ✅ **Flexible**: Easy to change configuration anytime
## 🌳 Migration Graph (DAG) System
**Advanced dependency management for complex migration scenarios!** The tool now supports Directed Acyclic Graphs (DAG) for managing migration dependencies, enabling parallel development and complex branching scenarios.
### **Key DAG Features:**
#### **1. Dependency Management**
```yaml
# Migration with dependencies
version: '20250113120000'
description: Add user authentication system
branch: feature-auth
dependencies: ['20250113100000'] # Depends on previous migration
revision_id: 'abc12345'
changes:
- add_table:
name: users
columns:
- name: id
type: INTEGER
primary_key: true
- name: username
type: VARCHAR(50)
unique: true
```
#### **2. Branch Support**
```bash
# Create a new migration branch
python main.py create-branch feature-auth
# Create a branch from specific migration
python main.py create-branch feature-payments --base 20250113100000
```
#### **3. Parallel Development**
```bash
# Developer A works on auth branch
python main.py autogenerate -m "Add user table" --branch feature-auth
# Developer B works on payments branch
python main.py autogenerate -m "Add payment table" --branch feature-payments
# Both can work simultaneously without conflicts
```
#### **4. Merge Branches**
```bash
# Merge two branches when ready
python main.py merge-branches feature-auth feature-payments -m "Merge auth and payments"
```
### **DAG Commands:**
| Command | Description | Example |
|---------|-------------|---------|
| `graph` | Show migration dependency graph | `python main.py graph` |
| `validate-migration` | Validate migration for conflicts | `python main.py validate-migration migrations/20250113_add_users.yml` |
| `create-branch` | Create new migration branch | `python main.py create-branch feature-auth` |
| `merge-branches` | Merge two branches | `python main.py merge-branches feature-auth feature-payments` |
| `status` | Comprehensive migration & schema status | `python main.py status` |
| `status-quick` | Quick status overview | `python main.py status-quick` |
### **DAG Workflow Example:**
#### **Scenario: Multiple Developers Working in Parallel**
```bash
# 1. Initial setup
python main.py init-db --host localhost --user myuser --password mypass --database mydb --type postgresql
# 2. Developer A creates auth branch
python main.py create-branch feature-auth
python main.py autogenerate -m "Add users table" --branch feature-auth
python main.py apply migrations/20250113120000_add_users_table.yml
# 3. Developer B creates payments branch (from same base)
python main.py create-branch feature-payments
python main.py autogenerate -m "Add payments table" --branch feature-payments
python main.py apply migrations/20250113130000_add_payments_table.yml
# 4. Check migration graph
python main.py graph
# Output:
# Migration Graph:
# ==================================================
#
# Branch: main
# --------------------
# 20250113100000: Initial schema
# Dependencies: none
# Revision ID: def45678
#
# Branch: feature-auth
# --------------------
# 20250113120000: Add users table
# Dependencies: 20250113100000
# Revision ID: abc12345
#
# Branch: feature-payments
# --------------------
# 20250113130000: Add payments table
# Dependencies: 20250113100000
# Revision ID: ghi78901
# 5. Merge branches when ready
python main.py merge-branches feature-auth feature-payments -m "Merge auth and payments"
python main.py apply migrations/20250113140000_merge_feature_auth_feature_payments.yml
```
### **DAG Benefits:**
- ✅ **Parallel Development**: Multiple developers can work on migrations simultaneously
- ✅ **Dependency Tracking**: Clear dependency relationships between migrations
- ✅ **Conflict Detection**: Automatic detection of migration conflicts
- ✅ **Branch Management**: Easy creation and merging of migration branches
- ✅ **Graph Visualization**: Visual representation of migration dependencies
- ✅ **Cycle Detection**: Prevents circular dependencies
- ✅ **Backward Compatible**: Existing linear migrations continue to work
## 🔍 Schema Inspection Utilities
**Comprehensive database and migration status inspection!** The tool provides powerful commands to inspect your database schema and migration status with clear visual indicators.
### **Status Commands:**
#### **1. Comprehensive Status (`status`)**
```bash
# Full status report with schema validation
python main.py status
# Verbose mode with detailed information
python main.py status --verbose
# Check against specific models file
python main.py status --models-file custom_models.py
# Skip sync checking for faster execution
python main.py status --no-check-sync
```
**Output Example:**
```
🔍 Migration & Schema Status Report
============================================================
📊 MIGRATION STATUS
------------------------------
✅ Applied migrations: 6
⏳ Pending migrations: 0
🔄 DATABASE SYNC STATUS
------------------------------
Status: ⚠️ Out of Sync
• Extra tables: migration_log
🔗 DEPENDENCY HEALTH
------------------------------
✅ DAG is valid - no cycles detected
✅ No dependency conflicts
🎯 CURRENT STATE
------------------------------
Current heads: 20250913122220, 20250913122712
📋 SUMMARY
------------------------------
✅ All migrations applied - database is up to date
⚠️ Database schema may be out of sync - consider running 'python main.py autogenerate'
```
#### **2. Quick Status (`status-quick`)**
```bash
# Quick overview for CI/CD pipelines
python main.py status-quick
```
**Output Example:**
```
✅ All migrations applied
📊 Database has 5 tables
🎯 Current heads: 20250913122220, 20250913122712
```
### **Status Indicators:**
| Indicator | Meaning | Action Required |
|-----------|---------|-----------------|
| ✅ **Applied** | Migration successfully applied to database | None |
| ⏳ **Pending** | Migration exists but not applied | Run `python main.py apply` |
| ⚠️ **Out of Sync** | Database schema differs from models | Run `python main.py autogenerate` |
| ❌ **Error** | Migration or database error | Check logs and fix issues |
| ❓ **Unknown** | Cannot determine status | Check database connection |
### **Schema Validation Features:**
- **Table Comparison**: Compares current database tables with target models
- **Missing Tables**: Identifies tables that should exist but don't
- **Extra Tables**: Identifies tables that exist but aren't in models
- **Dependency Health**: Validates migration dependency graph
- **Conflict Detection**: Identifies migration conflicts and circular dependencies
- **Branch Summary**: Shows migration status by branch
### **Use Cases:**
#### **Development Workflow:**
```bash
# Check status before starting work
python main.py status
# Quick check during development
python main.py status-quick
# Detailed inspection when debugging
python main.py status --verbose
```
#### **CI/CD Pipeline:**
```bash
# Quick status check in CI
python main.py status-quick
# Full validation in staging
python main.py status --check-sync
```
#### **Production Monitoring:**
```bash
# Monitor migration status
python main.py status --no-check-sync
# Validate against production models
python main.py status --models-file production_models.py
```
## 📝 Migration File Formats
### YAML Migrations
YAML migrations use a declarative format for schema changes:
```yaml
version: '20250101120000'
description: Add users table with indexes
changes:
- create_table:
table: users
columns:
- name: id
type: INTEGER
nullable: false
primary_key: true
- name: name
type: VARCHAR(100)
nullable: false
- name: email
type: VARCHAR(255)
nullable: true
unique: true
- add_index:
table: users
name: idx_users_email
columns: [email]
- add_column:
table: users
column: created_at
type: DATETIME
nullable: true
- drop_column:
table: users
column: old_field
```
### Python Migrations
Python migrations provide full programmatic control:
```python
def upgrade(engine):
"""Apply the migration."""
with engine.connect() as conn:
conn.execute(text("""
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE
)
"""))
def downgrade(engine):
"""Rollback the migration."""
with engine.connect() as conn:
conn.execute(text("DROP TABLE users"))
```
## 🔄 Supported Operations
### Table Operations
- `create_table` / `drop_table`
- `rename_table`
### Column Operations
- `add_column` / `drop_column`
- `alter_column` (type changes, nullable modifications)
### Index Operations
- `add_index` / `drop_index`
### Advanced Features
- **Metadata Preservation**: All operations store metadata for accurate rollback
- **Table Rename Mapping**: Handle table renames with `rename_map.yml`
- **Enhanced Rollback**: Automatic reversal of all supported operations
- **Schema Validation**: Compare current schema with target schema
## ⚙️ Configuration
### 🔐 Programmatic Configuration (Security-Conscious)
For organizations that prefer not to store database credentials in files, you can set the database URL programmatically:
```python
# In your application code
from dbPorter import set_database_url
# Set database URL directly in code
set_database_url("postgresql://user:password@localhost:5432/mydatabase")
# Now all migration commands will use this URL automatically
# dbporter status
# dbporter apply migrations/20250101_add_users.yml
```
**Benefits:**
- ✅ **No credential files**: Database URL not stored on disk
- ✅ **Environment variables**: Can use `os.getenv("DATABASE_URL")`
- ✅ **Application integration**: Works with existing app configuration
- ✅ **CI/CD friendly**: Perfect for automated deployments
- ✅ **Multi-tenant ready**: Dynamic database URLs per tenant
**Example with environment variables:**
```python
import os
from dbPorter import set_database_url
# From environment variable
db_url = os.getenv("DATABASE_URL")
if db_url:
set_database_url(db_url)
else:
raise ValueError("DATABASE_URL environment variable not set")
```
### 🔄 Configuration Priority
The tool uses the following priority order for database configuration:
1. **Command-line arguments** (highest priority)
```bash
dbporter status --db "postgresql://user:pass@host:port/db"
```
2. **Programmatic configuration** (security-conscious)
```python
from dbPorter import set_database_url
set_database_url("postgresql://user:pass@host:port/db")
```
3. **Saved configuration** (traditional)
```bash
dbporter init-db --host localhost --user myuser --password mypass
```
4. **Environment variables** (fallback)
```bash
export DB_URL="postgresql://user:pass@host:port/db"
dbporter status
```
5. **Auto-discovery** (lowest priority)
- Automatically detects database from common patterns
### 📁 File-Based Configuration (Traditional)
### Environment Variables
Create a `.env` file in your project root:
```env
# Database connection
DB_URL=sqlite:///your_database.db
# For PostgreSQL
# DB_URL=postgresql://username:password@localhost:5432/database_name
# For MySQL
# DB_URL=mysql://username:password@localhost:3306/database_name
```
### Table Rename Mapping
Create `rename_map.yml` to handle table renames:
```yaml
table_renames:
old_table_name: new_table_name
legacy_users: users
```
## 🔒 Safety Features
- **Transaction Support**: All migrations run in database transactions
- **Rollback Capability**: Every migration can be safely rolled back
- **Metadata Preservation**: Column types, constraints, and indexes are preserved
- **Dry-Run Mode**: Preview changes before applying
- **Migration Logging**: Complete audit trail of all applied migrations
## 🐛 Troubleshooting
### Common Issues
1. **Migration not found**
```bash
# Ensure migration file exists and is properly formatted
python main.py plan migrations/your_migration.yml
```
2. **Database connection failed**
```bash
# Check your DB_URL in .env file
python main.py init-db --db "sqlite:///test.db"
```
3. **Rollback failed**
```bash
# Check migration log for the last applied migration
# Ensure database is in a consistent state
```
### Debug Mode
Enable verbose logging by modifying the commands to include debug output:
```python
# Add logging configuration in your migration files
import logging
logging.basicConfig(level=logging.DEBUG)
```
## 🤝 Contributing
1. Fork the repository
2. Create a feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request
## 📄 License
This project is licensed under the MIT License - see the LICENSE file for details.
## 🙏 Acknowledgments
- Built with [SQLAlchemy](https://www.sqlalchemy.org/) for database abstraction
- CLI powered by [Typer](https://typer.tiangolo.com/)
- YAML support via [PyYAML](https://pyyaml.org/)
---
**Need help?** Check the command help with `python main.py --help` or `python main.py [command] --help`
Raw data
{
"_id": null,
"home_page": "https://github.com/karan-kap00r/dbPorter",
"name": "dbporter",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "Karan Kapoor <karankapoor0062@gmail.com>",
"keywords": "database, migration, schema, sqlalchemy, yaml, dag, rollback, postgresql, mysql, sqlite, oracle, sqlserver, version-control, database-versioning, schema-management",
"author": "Karan Kapoor",
"author_email": "Karan Kapoor <karankapoor0062@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/9e/8b/8e0c413513342c0e6cca292dab998998cb657f984ea5f3db66d34d70d37b/dbporter-0.1.6.tar.gz",
"platform": "any",
"description": "# dbPorter - Database Migration Tool\n\nA powerful, flexible database migration tool built with Python that supports both YAML-based declarative migrations and Python-based programmatic migrations. This tool provides comprehensive schema management capabilities with automatic rollback support and metadata preservation.\n\n## \ud83d\ude80 Features\n\n- **Dual Migration Support**: YAML-based declarative migrations and Python-based programmatic migrations\n- **Migration Graph (DAG)**: Support for branching migrations with dependency management like Alembic\n- **Automatic Rollback**: Intelligent rollback system with metadata preservation for safe reversions\n- **Schema Auto-Generation**: Automatically detect and generate migrations from schema differences\n- **Enhanced Metadata**: Captures column metadata for accurate rollback operations\n- **Table Rename Support**: Handle table renames with mapping configuration\n- **Dry-Run Capability**: Preview migration changes before applying them\n- **Comprehensive Logging**: Track all applied migrations with detailed payload information\n- **Multiple Database Support**: Works with any SQLAlchemy-supported database\n- **Parallel Development**: Support for multiple developers working on migrations simultaneously\n- **Conflict Detection**: Automatic detection and resolution of migration conflicts\n- **Schema Inspection**: Comprehensive database and migration status inspection with visual indicators\n- **Sync Validation**: Automatic detection of database schema sync issues\n\n## \ud83d\udccb Prerequisites\n\n- **Python 3.8+** (Python 3.9+ recommended)\n- **Core Dependencies**: SQLAlchemy, PyYAML, Typer, python-dotenv, tabulate\n- **Database Driver**: Choose based on your database:\n - **SQLite**: No additional driver needed (included with Python)\n - **PostgreSQL**: `psycopg2-binary` or `psycopg2`\n - **MySQL**: `PyMySQL` or `mysqlclient`\n - **SQL Server**: `pyodbc` or `pymssql`\n - **Oracle**: `cx-Oracle`\n\n## \ud83d\udee0\ufe0f Installation\n\n1. **Clone the repository**\n ```bash\n git clone <repository-url>\n cd dbPorter\n ```\n\n2. **Install dependencies**\n \n **Option A: Automated installation (recommended)**\n ```bash\n # For SQLite (default)\n python install.py\n \n # For PostgreSQL\n python install.py --database postgresql\n \n # For MySQL\n python install.py --database mysql\n \n # For development\n python install.py --dev\n ```\n \n **Option B: Manual installation**\n ```bash\n # Full installation (recommended)\n pip install -r requirements.txt\n \n # Minimal installation (SQLite only)\n pip install -r requirements-minimal.txt\n \n # Development installation\n pip install -r requirements-dev.txt\n ```\n \n **Option C: Quick installation**\n ```bash\n pip install sqlalchemy pyyaml typer python-dotenv tabulate\n ```\n\n3. **Set up database configuration (optional)**\n The tool auto-discovers database URLs from multiple sources:\n \n **Option A: Environment Variables**\n ```bash\n export DB_URL=\"sqlite:///your_database.db\"\n # or for PostgreSQL: postgresql://user:password@localhost/dbname\n # or for MySQL: mysql://user:password@localhost/dbname\n ```\n \n **Option B: .env file**\n Create a `.env` file in the project root:\n ```env\n DB_URL=sqlite:///your_database.db\n DATABASE_URL=postgresql://user:password@localhost/dbname\n ```\n \n **Option C: Configuration files**\n The tool also looks for database URLs in:\n - `config.py` (DATABASE_URL variable)\n - `settings.py` (DB_URL variable)\n - `database.py` (database_url variable)\n \n **Option D: Default fallback**\n If no database URL is found, defaults to `sqlite:///migrate.db`\n\n## \ud83c\udfd7\ufe0f Project Structure\n\n```\ndbPorter/\n\u251c\u2500\u2500 main.py # Entry point\n\u251c\u2500\u2500 commands.py # CLI commands implementation\n\u251c\u2500\u2500 models.py # SQLAlchemy metadata definitions\n\u251c\u2500\u2500 migrations/ # Migration files directory\n\u2502 \u251c\u2500\u2500 *.yml # YAML migration files\n\u2502 \u2514\u2500\u2500 *.py # Python migration files\n\u251c\u2500\u2500 src/ # Core migration logic\n\u2502 \u251c\u2500\u2500 applier.py # Migration application logic\n\u2502 \u251c\u2500\u2500 db.py # Database connection and metadata\n\u2502 \u251c\u2500\u2500 executors.py # SQL operation executors\n\u2502 \u251c\u2500\u2500 migration_loader.py # Migration file parsing\n\u2502 \u2514\u2500\u2500 planner.py # Migration planning logic\n\u2514\u2500\u2500 utils/ # Utility functions\n \u2514\u2500\u2500 utils.py # Helper functions\n```\n\n## \ud83d\ude80 Quick Start\n\n1. **Initialize the migration system**\n ```bash\n python main.py init-db\n ```\n\n2. **Define your schema in a models file**\n ```python\n # models.py, schema.py, database.py, or any Python file\n from sqlalchemy import Table, Column, Integer, String, MetaData\n \n metadata = MetaData()\n \n users = Table(\n \"users\", metadata,\n Column(\"id\", Integer, primary_key=True),\n Column(\"name\", String(100), nullable=False),\n Column(\"email\", String(255), unique=True)\n )\n ```\n\n3. **Auto-generate your first migration**\n ```bash\n python main.py autogenerate -m \"Initial schema\"\n ```\n\n4. **Apply the migration**\n ```bash\n python main.py apply\n ```\n\n## \ud83d\udcd6 Command Reference\n\n### Database Initialization\n\n```bash\n# Initialize migration metadata table\npython main.py init-db [--db DATABASE_URL]\n```\n\n### Migration Planning\n\n```bash\n# Plan a migration (dry-run)\npython main.py plan [MIGRATION_FILE] [--rename-map RENAME_MAP_FILE]\n\n# Examples:\npython main.py plan # Use latest migration\npython main.py plan migrations/20250101120000_add_users.yml\npython main.py plan --rename-map custom_renames.yml\n```\n\n### Migration Application\n\n```bash\n# Apply migrations\npython main.py apply [MIGRATION_FILE] [OPTIONS]\n\n# Options:\n# --db DATABASE_URL Database connection string\n# --rename-map FILE Table rename mapping file\n# --dry-run Show what would be done without executing\n# --latest Use the latest migration file\n\n# Examples:\npython main.py apply # Apply latest migration\npython main.py apply --latest # Explicitly use latest\npython main.py apply --dry-run # Preview changes\npython main.py apply migrations/20250101120000_add_users.yml\n```\n\n### Migration Rollback\n\n```bash\n# Rollback the last applied migration\npython main.py rollback [--db DATABASE_URL]\n\n# Examples:\npython main.py rollback\npython main.py rollback --db \"postgresql://user:pass@localhost/db\"\n```\n\n### Auto-Generation\n\n```bash\n# Auto-generate migration from schema differences\npython main.py autogenerate [--db DATABASE_URL] [-m MESSAGE]\n\n# Examples:\npython main.py autogenerate\npython main.py autogenerate -m \"Add user profile table\"\npython main.py autogenerate --db \"sqlite:///mydb.db\"\n```\n\n### Migration Registration\n\n```bash\n# Register existing migration with timestamp\npython main.py revision MIGRATION_FILE\n\n# Example:\npython main.py revision my_migration.yml\n# Creates: migrations/20250101120000_my_migration.yml\n```\n\n### Models Discovery\n\n```bash\n# Discover and validate models files\npython main.py discover-models [--models-file PATH]\n\n# Examples:\npython main.py discover-models # Auto-discover models file\npython main.py discover-models --models-file \"my_schema.py\"\n```\n\n### Database Discovery\n\n```bash\n# Discover and validate database configuration\npython main.py discover-db [--db URL]\n\n# Examples:\npython main.py discover-db # Auto-discover database URL\npython main.py discover-db --db \"postgresql://user:pass@localhost/db\"\n```\n\n### One-Time Database Configuration\n\n**Configure once, use everywhere!** Set up your database connection once during `init-db`, and all future commands will automatically use the same configuration.\n\n#### **Step 1: Configure Database (One Time Only)**\n```bash\n# PostgreSQL (most common)\npython main.py init-db --host localhost --port 5432 --user myuser --password mypass --database mydb --type postgresql\n\n# MySQL\npython main.py init-db --host localhost --port 3306 --user myuser --password mypass --database mydb --type mysql\n\n# SQLite (no credentials needed)\npython main.py init-db --database myapp.db --type sqlite\n\n# Using complete URL\npython main.py init-db --db \"postgresql://user:pass@localhost/db\"\n```\n\n#### **Step 2: Use Commands Without Database Parameters**\n```bash\n# All these commands automatically use the saved configuration!\npython main.py apply\npython main.py rollback\npython main.py autogenerate -m \"Add new table\"\npython main.py plan\n```\n\n#### **Configuration Management**\n```bash\n# Show current configuration\npython main.py show-config\n\n# Reset configuration (go back to auto-discovery)\npython main.py reset-config\n```\n\n**Benefits:**\n- \u2705 **Configure Once**: Set database connection once during `init-db`\n- \u2705 **Use Everywhere**: All commands automatically use saved configuration\n- \u2705 **Override When Needed**: Can still override with command-line arguments\n- \u2705 **Secure**: Credentials stored in local config file (not in code)\n- \u2705 **Flexible**: Easy to change configuration anytime\n\n## \ud83c\udf33 Migration Graph (DAG) System\n\n**Advanced dependency management for complex migration scenarios!** The tool now supports Directed Acyclic Graphs (DAG) for managing migration dependencies, enabling parallel development and complex branching scenarios.\n\n### **Key DAG Features:**\n\n#### **1. Dependency Management**\n```yaml\n# Migration with dependencies\nversion: '20250113120000'\ndescription: Add user authentication system\nbranch: feature-auth\ndependencies: ['20250113100000'] # Depends on previous migration\nrevision_id: 'abc12345'\nchanges:\n - add_table:\n name: users\n columns:\n - name: id\n type: INTEGER\n primary_key: true\n - name: username\n type: VARCHAR(50)\n unique: true\n```\n\n#### **2. Branch Support**\n```bash\n# Create a new migration branch\npython main.py create-branch feature-auth\n\n# Create a branch from specific migration\npython main.py create-branch feature-payments --base 20250113100000\n```\n\n#### **3. Parallel Development**\n```bash\n# Developer A works on auth branch\npython main.py autogenerate -m \"Add user table\" --branch feature-auth\n\n# Developer B works on payments branch \npython main.py autogenerate -m \"Add payment table\" --branch feature-payments\n\n# Both can work simultaneously without conflicts\n```\n\n#### **4. Merge Branches**\n```bash\n# Merge two branches when ready\npython main.py merge-branches feature-auth feature-payments -m \"Merge auth and payments\"\n```\n\n### **DAG Commands:**\n\n| Command | Description | Example |\n|---------|-------------|---------|\n| `graph` | Show migration dependency graph | `python main.py graph` |\n| `validate-migration` | Validate migration for conflicts | `python main.py validate-migration migrations/20250113_add_users.yml` |\n| `create-branch` | Create new migration branch | `python main.py create-branch feature-auth` |\n| `merge-branches` | Merge two branches | `python main.py merge-branches feature-auth feature-payments` |\n| `status` | Comprehensive migration & schema status | `python main.py status` |\n| `status-quick` | Quick status overview | `python main.py status-quick` |\n\n### **DAG Workflow Example:**\n\n#### **Scenario: Multiple Developers Working in Parallel**\n\n```bash\n# 1. Initial setup\npython main.py init-db --host localhost --user myuser --password mypass --database mydb --type postgresql\n\n# 2. Developer A creates auth branch\npython main.py create-branch feature-auth\npython main.py autogenerate -m \"Add users table\" --branch feature-auth\npython main.py apply migrations/20250113120000_add_users_table.yml\n\n# 3. Developer B creates payments branch (from same base)\npython main.py create-branch feature-payments\npython main.py autogenerate -m \"Add payments table\" --branch feature-payments\npython main.py apply migrations/20250113130000_add_payments_table.yml\n\n# 4. Check migration graph\npython main.py graph\n# Output:\n# Migration Graph:\n# ==================================================\n# \n# Branch: main\n# --------------------\n# 20250113100000: Initial schema\n# Dependencies: none\n# Revision ID: def45678\n# \n# Branch: feature-auth\n# --------------------\n# 20250113120000: Add users table\n# Dependencies: 20250113100000\n# Revision ID: abc12345\n# \n# Branch: feature-payments\n# --------------------\n# 20250113130000: Add payments table\n# Dependencies: 20250113100000\n# Revision ID: ghi78901\n\n# 5. Merge branches when ready\npython main.py merge-branches feature-auth feature-payments -m \"Merge auth and payments\"\npython main.py apply migrations/20250113140000_merge_feature_auth_feature_payments.yml\n```\n\n### **DAG Benefits:**\n\n- \u2705 **Parallel Development**: Multiple developers can work on migrations simultaneously\n- \u2705 **Dependency Tracking**: Clear dependency relationships between migrations\n- \u2705 **Conflict Detection**: Automatic detection of migration conflicts\n- \u2705 **Branch Management**: Easy creation and merging of migration branches\n- \u2705 **Graph Visualization**: Visual representation of migration dependencies\n- \u2705 **Cycle Detection**: Prevents circular dependencies\n- \u2705 **Backward Compatible**: Existing linear migrations continue to work\n\n## \ud83d\udd0d Schema Inspection Utilities\n\n**Comprehensive database and migration status inspection!** The tool provides powerful commands to inspect your database schema and migration status with clear visual indicators.\n\n### **Status Commands:**\n\n#### **1. Comprehensive Status (`status`)**\n```bash\n# Full status report with schema validation\npython main.py status\n\n# Verbose mode with detailed information\npython main.py status --verbose\n\n# Check against specific models file\npython main.py status --models-file custom_models.py\n\n# Skip sync checking for faster execution\npython main.py status --no-check-sync\n```\n\n**Output Example:**\n```\n\ud83d\udd0d Migration & Schema Status Report\n============================================================\n\n\ud83d\udcca MIGRATION STATUS\n------------------------------\n\u2705 Applied migrations: 6\n\u23f3 Pending migrations: 0\n\n\ud83d\udd04 DATABASE SYNC STATUS\n------------------------------\nStatus: \u26a0\ufe0f Out of Sync\n \u2022 Extra tables: migration_log\n\n\ud83d\udd17 DEPENDENCY HEALTH\n------------------------------\n\u2705 DAG is valid - no cycles detected\n\u2705 No dependency conflicts\n\n\ud83c\udfaf CURRENT STATE\n------------------------------\nCurrent heads: 20250913122220, 20250913122712\n\n\ud83d\udccb SUMMARY\n------------------------------\n\u2705 All migrations applied - database is up to date\n\u26a0\ufe0f Database schema may be out of sync - consider running 'python main.py autogenerate'\n```\n\n#### **2. Quick Status (`status-quick`)**\n```bash\n# Quick overview for CI/CD pipelines\npython main.py status-quick\n```\n\n**Output Example:**\n```\n\u2705 All migrations applied\n\ud83d\udcca Database has 5 tables\n\ud83c\udfaf Current heads: 20250913122220, 20250913122712\n```\n\n### **Status Indicators:**\n\n| Indicator | Meaning | Action Required |\n|-----------|---------|-----------------|\n| \u2705 **Applied** | Migration successfully applied to database | None |\n| \u23f3 **Pending** | Migration exists but not applied | Run `python main.py apply` |\n| \u26a0\ufe0f **Out of Sync** | Database schema differs from models | Run `python main.py autogenerate` |\n| \u274c **Error** | Migration or database error | Check logs and fix issues |\n| \u2753 **Unknown** | Cannot determine status | Check database connection |\n\n### **Schema Validation Features:**\n\n- **Table Comparison**: Compares current database tables with target models\n- **Missing Tables**: Identifies tables that should exist but don't\n- **Extra Tables**: Identifies tables that exist but aren't in models\n- **Dependency Health**: Validates migration dependency graph\n- **Conflict Detection**: Identifies migration conflicts and circular dependencies\n- **Branch Summary**: Shows migration status by branch\n\n### **Use Cases:**\n\n#### **Development Workflow:**\n```bash\n# Check status before starting work\npython main.py status\n\n# Quick check during development\npython main.py status-quick\n\n# Detailed inspection when debugging\npython main.py status --verbose\n```\n\n#### **CI/CD Pipeline:**\n```bash\n# Quick status check in CI\npython main.py status-quick\n\n# Full validation in staging\npython main.py status --check-sync\n```\n\n#### **Production Monitoring:**\n```bash\n# Monitor migration status\npython main.py status --no-check-sync\n\n# Validate against production models\npython main.py status --models-file production_models.py\n```\n\n## \ud83d\udcdd Migration File Formats\n\n### YAML Migrations\n\nYAML migrations use a declarative format for schema changes:\n\n```yaml\nversion: '20250101120000'\ndescription: Add users table with indexes\nchanges:\n- create_table:\n table: users\n columns:\n - name: id\n type: INTEGER\n nullable: false\n primary_key: true\n - name: name\n type: VARCHAR(100)\n nullable: false\n - name: email\n type: VARCHAR(255)\n nullable: true\n unique: true\n\n- add_index:\n table: users\n name: idx_users_email\n columns: [email]\n\n- add_column:\n table: users\n column: created_at\n type: DATETIME\n nullable: true\n\n- drop_column:\n table: users\n column: old_field\n```\n\n### Python Migrations\n\nPython migrations provide full programmatic control:\n\n```python\ndef upgrade(engine):\n \"\"\"Apply the migration.\"\"\"\n with engine.connect() as conn:\n conn.execute(text(\"\"\"\n CREATE TABLE users (\n id INTEGER PRIMARY KEY,\n name VARCHAR(100) NOT NULL,\n email VARCHAR(255) UNIQUE\n )\n \"\"\"))\n\ndef downgrade(engine):\n \"\"\"Rollback the migration.\"\"\"\n with engine.connect() as conn:\n conn.execute(text(\"DROP TABLE users\"))\n```\n\n## \ud83d\udd04 Supported Operations\n\n### Table Operations\n- `create_table` / `drop_table`\n- `rename_table`\n\n### Column Operations\n- `add_column` / `drop_column`\n- `alter_column` (type changes, nullable modifications)\n\n### Index Operations\n- `add_index` / `drop_index`\n\n### Advanced Features\n- **Metadata Preservation**: All operations store metadata for accurate rollback\n- **Table Rename Mapping**: Handle table renames with `rename_map.yml`\n- **Enhanced Rollback**: Automatic reversal of all supported operations\n- **Schema Validation**: Compare current schema with target schema\n\n## \u2699\ufe0f Configuration\n\n### \ud83d\udd10 Programmatic Configuration (Security-Conscious)\n\nFor organizations that prefer not to store database credentials in files, you can set the database URL programmatically:\n\n```python\n# In your application code\nfrom dbPorter import set_database_url\n\n# Set database URL directly in code\nset_database_url(\"postgresql://user:password@localhost:5432/mydatabase\")\n\n# Now all migration commands will use this URL automatically\n# dbporter status\n# dbporter apply migrations/20250101_add_users.yml\n```\n\n**Benefits:**\n- \u2705 **No credential files**: Database URL not stored on disk\n- \u2705 **Environment variables**: Can use `os.getenv(\"DATABASE_URL\")`\n- \u2705 **Application integration**: Works with existing app configuration\n- \u2705 **CI/CD friendly**: Perfect for automated deployments\n- \u2705 **Multi-tenant ready**: Dynamic database URLs per tenant\n\n**Example with environment variables:**\n```python\nimport os\nfrom dbPorter import set_database_url\n\n# From environment variable\ndb_url = os.getenv(\"DATABASE_URL\")\nif db_url:\n set_database_url(db_url)\nelse:\n raise ValueError(\"DATABASE_URL environment variable not set\")\n```\n\n### \ud83d\udd04 Configuration Priority\n\nThe tool uses the following priority order for database configuration:\n\n1. **Command-line arguments** (highest priority)\n ```bash\n dbporter status --db \"postgresql://user:pass@host:port/db\"\n ```\n\n2. **Programmatic configuration** (security-conscious)\n ```python\n from dbPorter import set_database_url\n set_database_url(\"postgresql://user:pass@host:port/db\")\n ```\n\n3. **Saved configuration** (traditional)\n ```bash\n dbporter init-db --host localhost --user myuser --password mypass\n ```\n\n4. **Environment variables** (fallback)\n ```bash\n export DB_URL=\"postgresql://user:pass@host:port/db\"\n dbporter status\n ```\n\n5. **Auto-discovery** (lowest priority)\n - Automatically detects database from common patterns\n\n### \ud83d\udcc1 File-Based Configuration (Traditional)\n\n### Environment Variables\n\nCreate a `.env` file in your project root:\n\n```env\n# Database connection\nDB_URL=sqlite:///your_database.db\n\n# For PostgreSQL\n# DB_URL=postgresql://username:password@localhost:5432/database_name\n\n# For MySQL\n# DB_URL=mysql://username:password@localhost:3306/database_name\n```\n\n### Table Rename Mapping\n\nCreate `rename_map.yml` to handle table renames:\n\n```yaml\ntable_renames:\n old_table_name: new_table_name\n legacy_users: users\n```\n\n## \ud83d\udd12 Safety Features\n\n- **Transaction Support**: All migrations run in database transactions\n- **Rollback Capability**: Every migration can be safely rolled back\n- **Metadata Preservation**: Column types, constraints, and indexes are preserved\n- **Dry-Run Mode**: Preview changes before applying\n- **Migration Logging**: Complete audit trail of all applied migrations\n\n## \ud83d\udc1b Troubleshooting\n\n### Common Issues\n\n1. **Migration not found**\n ```bash\n # Ensure migration file exists and is properly formatted\n python main.py plan migrations/your_migration.yml\n ```\n\n2. **Database connection failed**\n ```bash\n # Check your DB_URL in .env file\n python main.py init-db --db \"sqlite:///test.db\"\n ```\n\n3. **Rollback failed**\n ```bash\n # Check migration log for the last applied migration\n # Ensure database is in a consistent state\n ```\n\n### Debug Mode\n\nEnable verbose logging by modifying the commands to include debug output:\n\n```python\n# Add logging configuration in your migration files\nimport logging\nlogging.basicConfig(level=logging.DEBUG)\n```\n\n## \ud83e\udd1d Contributing\n\n1. Fork the repository\n2. Create a feature branch (`git checkout -b feature/amazing-feature`)\n3. Commit your changes (`git commit -m 'Add some amazing feature'`)\n4. Push to the branch (`git push origin feature/amazing-feature`)\n5. Open a Pull Request\n\n## \ud83d\udcc4 License\n\nThis project is licensed under the MIT License - see the LICENSE file for details.\n\n## \ud83d\ude4f Acknowledgments\n\n- Built with [SQLAlchemy](https://www.sqlalchemy.org/) for database abstraction\n- CLI powered by [Typer](https://typer.tiangolo.com/)\n- YAML support via [PyYAML](https://pyyaml.org/)\n\n---\n\n**Need help?** Check the command help with `python main.py --help` or `python main.py [command] --help`\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "A powerful database migration tool with DAG support, automatic rollback, and schema inspection",
"version": "0.1.6",
"project_urls": {
"Bug Tracker": "https://github.com/karan-kap00r/dbPorter/issues",
"Documentation": "https://github.com/karan-kap00r/dbPorter/blob/main/README.md",
"Homepage": "https://github.com/karan-kap00r/dbPorter",
"Repository": "https://github.com/karan-kap00r/dbPorter.git"
},
"split_keywords": [
"database",
" migration",
" schema",
" sqlalchemy",
" yaml",
" dag",
" rollback",
" postgresql",
" mysql",
" sqlite",
" oracle",
" sqlserver",
" version-control",
" database-versioning",
" schema-management"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "ab5e62d3f4c8c5b4e6ed048c4c806c4c46b8406433766ddc0e9e76991b2dc35b",
"md5": "719d5051c2dd48ddf4ad03fcdf84d185",
"sha256": "abd88008632f4fbffd1fe018a77303d5f3f891523b0b3ee03a500d368f7a1bce"
},
"downloads": -1,
"filename": "dbporter-0.1.6-py3-none-any.whl",
"has_sig": false,
"md5_digest": "719d5051c2dd48ddf4ad03fcdf84d185",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 36091,
"upload_time": "2025-09-14T19:20:13",
"upload_time_iso_8601": "2025-09-14T19:20:13.405504Z",
"url": "https://files.pythonhosted.org/packages/ab/5e/62d3f4c8c5b4e6ed048c4c806c4c46b8406433766ddc0e9e76991b2dc35b/dbporter-0.1.6-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "9e8b8e0c413513342c0e6cca292dab998998cb657f984ea5f3db66d34d70d37b",
"md5": "db38e0193f4f052f9f254b5f7586ae79",
"sha256": "5d94dff58c092b224b80c043fe1c5921bdc59fb878d28f0d93558c3d19e02cb7"
},
"downloads": -1,
"filename": "dbporter-0.1.6.tar.gz",
"has_sig": false,
"md5_digest": "db38e0193f4f052f9f254b5f7586ae79",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 40073,
"upload_time": "2025-09-14T19:20:15",
"upload_time_iso_8601": "2025-09-14T19:20:15.398885Z",
"url": "https://files.pythonhosted.org/packages/9e/8b/8e0c413513342c0e6cca292dab998998cb657f984ea5f3db66d34d70d37b/dbporter-0.1.6.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-09-14 19:20:15",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "karan-kap00r",
"github_project": "dbPorter",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "typer",
"specs": [
[
">=",
"0.9.0"
]
]
},
{
"name": "sqlalchemy",
"specs": [
[
">=",
"2.0.0"
]
]
},
{
"name": "pyyaml",
"specs": [
[
">=",
"6.0"
]
]
},
{
"name": "python-dotenv",
"specs": [
[
">=",
"1.0.0"
]
]
},
{
"name": "tabulate",
"specs": [
[
">=",
"0.9.0"
]
]
},
{
"name": "typing-extensions",
"specs": [
[
">=",
"4.0.0"
]
]
}
],
"lcname": "dbporter"
}
JSON
