akron 0.3.1
Universal, framework-independent ORM for Python.
License: MIT
Requires Python: >=3.7
Keywords: orm database sql nosql sqlite mysql postgres mongodb

<p align="center">
	<img src="https://res.cloudinary.com/dclp2h92a/image/upload/v1756577773/ChatGPT_Image_Aug_30_2025_11_01_26_PM_i6o5k7.png" alt="Akron ORM Logo" width="180"/>
</p>

# AkronORM

**Universal, framework-independent ORM for Python** 🚀

Akron is a modern Python ORM that provides a unified interface for working with multiple databases. Whether you're using SQLite, MySQL, PostgreSQL, or MongoDB, Akron gives you the same clean, type-safe API across all platforms.

[![PyPI version](https://badge.fury.io/py/akron.svg)](https://pypi.org/project/akron/)
[![Python 3.7+](https://img.shields.io/badge/python-3.7+-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

---

## ✨ Key Features

### Core Capabilities
- **🔄 Universal Database Support** - One API for SQLite, MySQL, PostgreSQL, and MongoDB
- **🛡️ Type Safety** - Full Pydantic integration for type-safe models and validation
- **🔧 Zero Configuration** - Works out of the box with simple connection strings
- **� Framework Independent** - Works with any Python framework or standalone scripts

### Advanced ORM Features
- **📊 Advanced Querying** - QueryBuilder with filtering, sorting, pagination, and joins
- **🔍 Smart Operators** - Support for `gt`, `lt`, `in`, `like`, `isnull` and more
- **📈 Aggregations** - Built-in `sum`, `count`, `avg`, `min`, `max` with GROUP BY
- **🔄 Transactions** - Context managers and manual transaction control
- **⚡ Bulk Operations** - Efficient bulk insert, update, and delete operations
- **🗂️ Indexing** - Create and manage database indexes for performance
- **🔗 Relationships** - Foreign key constraints and multi-table operations
- **�️ Raw SQL** - Execute custom SQL when needed
- **💾 Serialization** - Convert results to JSON and dictionaries

### Developer Experience
- **📦 Schema Management** - Declarative schema with automatic migrations via `akron.json`
- **⚡ CLI Tools** - Modern command-line interface (`akron db init`, `migrate`, etc.)
- **🧪 Well Tested** - Comprehensive test coverage across all database drivers
- **� Rich Documentation** - Complete guides and examples for all features

---

## 🚀 Quick Start

### Installation

```bash
pip install akron
```

### Basic Usage

```python
from akron import Akron

# Initialize database connection
db = Akron("sqlite:///example.db")

# Create table with relationships
db.create_table("users", {
    "id": "int",
    "name": "str", 
    "email": "str",
    "age": "int",
    "active": "bool"
})

db.create_table("posts", {
    "id": "int",
    "title": "str",
    "content": "str",
    "user_id": "int->users.id",  # Foreign key to users table
    "published": "bool"
})

# Insert data
user_id = db.insert("users", {
    "name": "Alice Johnson",
    "email": "alice@example.com", 
    "age": 28,
    "active": True
})

# Bulk insert
post_ids = db.bulk_insert("posts", [
    {"title": "Hello World", "content": "My first post", "user_id": user_id, "published": True},
    {"title": "Python Tips", "content": "Some useful tips", "user_id": user_id, "published": False}
])

# Advanced querying with QueryBuilder
published_posts = db.query("posts").where(
    published=True,
    user_id=user_id
).order_by("-created_at").limit(10).all()

# Find with operators
young_users = db.query("users").where(age__lt=30, active=True).all()

# Aggregations
user_stats = db.aggregate("posts", {
    "post_count": "count",
    "avg_views": "avg"
}, group_by=["user_id"])

# Transactions
with db.transaction():
    new_user_id = db.insert("users", {"name": "Bob", "email": "bob@example.com", "age": 25, "active": True})
    db.insert("posts", {"title": "Bob's First Post", "user_id": new_user_id, "published": True})

# Count and existence checks
total_users = db.count("users")
has_admin = db.exists("users", {"email": "admin@example.com"})

# Raw SQL for complex queries
user_post_stats = db.raw("""
    SELECT u.name, COUNT(p.id) as post_count
    FROM users u LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.name
    ORDER BY post_count DESC
""")

# Close connection
db.close()
```

### Type-Safe Models with Pydantic

```python
from pydantic import BaseModel
from akron import Akron
from akron.models import ModelMixin
from typing import Optional

class User(BaseModel, ModelMixin):
    id: Optional[int] = None
    name: str
    email: str
    age: int
    is_active: bool = True

# Initialize database
db = Akron("sqlite:///users.db")

# Create table from model
User.create_table(db)

# Insert with type safety
new_user = User(name="Bob Smith", email="bob@example.com", age=25)
user_id = User.insert(db, new_user)

# Query with automatic deserialization
users = User.find(db)  # Returns List[User]
active_users = User.find(db, {"is_active": True})

# Update and delete
User.update(db, {"id": user_id}, {"age": 26})
User.delete(db, {"email": "bob@example.com"})
```

### Django-like Foreign Keys

```python
from pydantic import BaseModel
from akron.models import ModelMixin
from typing import Optional

class User(BaseModel, ModelMixin):
    id: Optional[int] = None
    name: str
    email: str

class Post(BaseModel, ModelMixin):
    id: Optional[int] = None
    title: str
    content: str
    author_id: int
    category_id: Optional[int] = None
    published: bool = False
    
    # 🔥 Django-like foreign key definitions - Simple and clean!
    __foreign_keys__ = {
        'author_id': 'users.id',
        'category_id': 'categories.id'
    }

class Comment(BaseModel, ModelMixin):
    id: Optional[int] = None
    post_id: int
    author_name: str
    content: str
    
    __foreign_keys__ = {
        'post_id': 'posts.id'
    }

# Create tables with automatic foreign key constraints
db = Akron("sqlite:///blog.db")
User.create_table(db)    # Creates users table
Post.create_table(db)    # Creates posts table with foreign keys to users
Comment.create_table(db) # Creates comments table with foreign key to posts

# Use normally with full type safety and relationship validation
user = User(name="Alice", email="alice@example.com")
user_id = User.insert(db, user)

post = Post(title="Hello World", content="...", author_id=user_id, published=True)
Post.insert(db, post)
```

---

## 🗄️ Database Support

| Database    | Connection String Example | CRUD | Foreign Keys | Migrations | CLI Support |
|-------------|---------------------------|------|--------------|------------|-------------|
| **SQLite**  | `sqlite:///path/to/db.db` | ✅   | ✅           | ✅         | ✅          |
| **MySQL**   | `mysql://user:pass@host:port/dbname` | ✅   | ✅           | ✅         | ✅          |
| **PostgreSQL** | `postgres://user:pass@host:port/dbname` | ✅   | ✅           | ✅         | ✅          |
| **MongoDB** | `mongodb://host:port/dbname` | ✅   | ❌*          | Schemaless | ✅          |

*MongoDB doesn't support traditional foreign keys but maintains the same API for document references.

### Connection Examples

```python
# SQLite (file-based)
db = Akron("sqlite:///myapp.db")

# SQLite (in-memory)
db = Akron("sqlite:///:memory:")

# MySQL
db = Akron("mysql://username:password@localhost:3306/mydatabase")

# PostgreSQL
db = Akron("postgres://username:password@localhost:5432/mydatabase")

# MongoDB
db = Akron("mongodb://localhost:27017/mydatabase")
```

---

## 🔗 Advanced Features

### Foreign Key Relationships

```python
# Create tables with foreign key relationships
db.create_table("users", {
    "id": "int",
    "name": "str",
    "email": "str"
})

db.create_table("orders", {
    "id": "int",
    "user_id": "int->users.id",  # Foreign key syntax
    "product_name": "str",
    "amount": "float",
    "status": "str"
})

# Insert related data
user_id = db.insert("users", {"name": "Alice", "email": "alice@example.com"})
order_id = db.insert("orders", {
    "user_id": user_id,
    "product_name": "Laptop",
    "amount": 999.99,
    "status": "pending"
})

# Query with relationships
user_orders = db.find("orders", {"user_id": user_id})
```

### Complex Queries with QueryBuilder

```python
# Advanced filtering with operators
results = db.query("users").where(
    age__gte=25,           # age >= 25
    name__like="John%",    # name starts with "John" 
    active=True            # active = True
).order_by("-created_at").limit(10).all()

# Pagination made simple
page_1 = db.query("posts").paginate(page=1, per_page=20)
page_2 = db.query("posts").paginate(page=2, per_page=20)

# Joins and aggregations
user_stats = db.query("users").join(
    "posts", on="users.id = posts.user_id"
).select([
    "users.name",
    "COUNT(posts.id) as post_count"
]).group_by("users.id").all()

# Bulk operations for performance
db.bulk_insert("products", [
    {"name": "Product 1", "price": 19.99},
    {"name": "Product 2", "price": 29.99},
    {"name": "Product 3", "price": 39.99}
])

# Atomic transactions
with db.transaction():
    user_id = db.insert("users", {"name": "Charlie", "email": "charlie@example.com"})
    db.insert("profiles", {"user_id": user_id, "bio": "New user profile"})
    # All operations committed together or rolled back on error
```

### Performance and Indexing

```python
# Create indexes for faster queries
db.create_index("users", ["email"])        # Single column index
db.create_index("orders", ["user_id", "status"])  # Composite index

# Performance optimization with exists()
if db.exists("users", {"email": "admin@example.com"}):
    print("Admin user found")

# Count records efficiently
total_active_users = db.count("users", {"active": True})

# Raw SQL for complex operations
results = db.raw("""
    SELECT category, AVG(price) as avg_price, COUNT(*) as item_count
    FROM products 
    WHERE active = 1
    GROUP BY category
    HAVING COUNT(*) > 5
    ORDER BY avg_price DESC
""")
```

### Pydantic Model Relationships

```python
class User(BaseModel, ModelMixin):
    id: int
    name: str
    email: str
    age: int

class Order(BaseModel, ModelMixin):
    id: int
    user_id: int  # Foreign key reference
    product_name: str
    amount: float
    status: str

# Create tables
User.create_table(db)
Order.create_table(db)

# Work with related models
user = User(id=1, name="Charlie", email="charlie@example.com", age=30)
User.insert(db, user)

order = Order(id=1, user_id=1, product_name="Book", amount=24.99, status="shipped")
Order.insert(db, order)

# Query relationships
user_orders = Order.find(db, {"user_id": 1})
```

---

## 🔄 Schema Management (Prisma-like)

Akron now supports Prisma-like schema management with `akron.json` configuration files:

### Initialize a New Project

```bash
# Initialize with SQLite (default)
akron db init

# Initialize with specific database
akron db init --provider postgresql --url "postgres://user:pass@localhost:5432/mydb"
akron db init --provider mysql --url "mysql://user:pass@localhost:3306/mydb"
akron db init --provider mongodb --url "mongodb://localhost:27017/mydb"
```

### Define Your Schema

Edit the generated `akron.json` file:

```json
{
  "database": {
    "provider": "sqlite",
    "url": "sqlite:///app.db"
  },
  "tables": {
    "users": {
      "columns": {
        "id": {
          "type": "int",
          "primary_key": true,
          "auto_increment": true
        },
        "email": {
          "type": "str",
          "unique": true,
          "nullable": false
        },
        "username": {
          "type": "str",
          "unique": true,
          "nullable": false,
          "max_length": 50
        },
        "created_at": {
          "type": "datetime",
          "default": "CURRENT_TIMESTAMP"
        }
      }
    },
    "posts": {
      "columns": {
        "id": {
          "type": "int",
          "primary_key": true,
          "auto_increment": true
        },
        "title": {
          "type": "str",
          "nullable": false,
          "max_length": 200
        },
        "content": {
          "type": "text",
          "nullable": true
        },
        "author_id": {
          "type": "int",
          "nullable": false
        },
        "published": {
          "type": "bool",
          "default": false
        }
      },
      "foreign_keys": {
        "author_id": {
          "references": "users",
          "column": "id",
          "on_delete": "CASCADE"
        }
      }
    }
  }
}
```

### Generate and Apply Migrations

```bash
# Generate migrations from schema changes
akron db makemigrations --name "add_user_posts"

# Preview what will be migrated
akron db migrate --dry-run

# Apply migrations
akron db migrate

# Check migration status
akron db status
```

### Schema Management Workflow

1. **Initialize**: `akron db init` creates `akron.json` and `.akron/` directory
2. **Define**: Edit `akron.json` to define your database schema
3. **Generate**: `akron db makemigrations` creates migration files
4. **Apply**: `akron db migrate` applies pending migrations
5. **Monitor**: `akron db status` shows current state

## 🔄 Legacy Migration System

For backward compatibility, Akron still supports the original migration commands:

### Auto-Generate Migrations

```bash
# Create a migration for schema changes
akron makemigrations users --db sqlite:///app.db --schema '{"id": "int", "name": "str", "email": "str", "created_at": "str"}'

# Apply migrations
akron migrate users --db sqlite:///app.db

# View migration history
akron showmigrations users --db sqlite:///app.db
```

### Migration Features

- **Automatic Schema Diffing** - Compares current vs target schema
- **Migration File Generation** - Creates JSON migration files in `migrations/` directory
- **Version Tracking** - Maintains migration history in `_akron_migrations` table
- **Rollback Support** - Track applied migrations for potential rollbacks

---

## 🛠️ CLI Commands

Akron provides two command interfaces: **modern schema management** and **legacy commands**.

### Modern Schema Management Commands

```bash
# Initialize a new Akron project
akron db init                                      # SQLite default
akron db init --provider postgresql --url "..."   # PostgreSQL
akron db init --provider mysql --url "..."        # MySQL
akron db init --provider mongodb --url "..."      # MongoDB

# Generate migrations from schema changes
akron db makemigrations                            # Auto-named migration
akron db makemigrations --name "add_user_table"   # Custom name

# Apply migrations
akron db migrate                                   # Apply all pending
akron db migrate --dry-run                        # Preview changes

# Check status
akron db status                                    # Show schema and migration status

# Reset database (planned)
akron db reset --force                            # Drop all and reapply
```

### Legacy Commands (Backward Compatibility)

```bash
# Table Management
akron create-table users --db sqlite:///app.db --schema '{"id": "int", "name": "str"}'
akron drop-table users --db sqlite:///app.db
akron inspect-schema users --db sqlite:///app.db

# Data Management
akron seed users --db sqlite:///app.db --data '{"name": "John", "email": "john@example.com"}'
akron raw-sql --db sqlite:///app.db --sql "SELECT COUNT(*) FROM users"

# Legacy Migration Commands
akron makemigrations orders --db mysql://user:pass@localhost/shop --schema '{"id": "int"}'
akron migrate orders --db mysql://user:pass@localhost/shop
akron showmigrations orders --db mysql://user:pass@localhost/shop
```

---

## 📊 Type System

Akron provides a flexible type system that maps Python types to database-specific types:

### Supported Python Types

| Python Type | SQL Databases | MongoDB |
|-------------|---------------|---------|
| `int`       | INTEGER       | Number  |
| `str`       | VARCHAR/TEXT  | String  |
| `float`     | REAL/DOUBLE   | Number  |
| `bool`      | BOOLEAN       | Boolean |

### Custom Type Mapping

```python
# Define custom field types in Pydantic models
from pydantic import Field
from datetime import datetime

class User(BaseModel, ModelMixin):
    id: int
    name: str
    email: str = Field(..., max_length=255)
    age: int = Field(..., ge=0, le=150)
    created_at: str  # Store as ISO string
    is_premium: bool = False
```

---

## 🔧 Configuration & Best Practices

### Connection Pooling

```python
# For production use, consider connection pooling
class DatabaseManager:
    def __init__(self, db_url: str):
        self.db_url = db_url
        self._db = None
    
    def get_db(self):
        if self._db is None:
            self._db = Akron(self.db_url)
        return self._db
    
    def close(self):
        if self._db:
            self._db.close()
            self._db = None

# Usage
db_manager = DatabaseManager("sqlite:///app.db")
db = db_manager.get_db()
```

### Error Handling

```python
from akron.exceptions import AkronError, TableNotFoundError, SchemaError

try:
    db = Akron("sqlite:///myapp.db")
    users = db.find("nonexistent_table")
except TableNotFoundError:
    print("Table doesn't exist - creating it...")
    db.create_table("users", {"id": "int", "name": "str"})
except AkronError as e:
    print(f"Database error: {e}")
```

### Environment-Based Configuration

```python
import os
from akron import Akron

# Use environment variables for database configuration
DATABASE_URL = os.getenv(
    "DATABASE_URL", 
    "sqlite:///default.db"  # fallback for development
)

db = Akron(DATABASE_URL)
```

---

## 🧪 Testing

Akron includes comprehensive test coverage and provides utilities for testing:

### Test Database Setup

```python
import pytest
from akron import Akron
from akron.models import ModelMixin
from pydantic import BaseModel

class User(BaseModel, ModelMixin):
    id: int
    name: str
    email: str

@pytest.fixture
def test_db():
    # Use in-memory database for testing
    db = Akron("sqlite:///:memory:")
    User.create_table(db)
    yield db
    db.close()

def test_user_creation(test_db):
    user = User(id=1, name="Test User", email="test@example.com")
    User.insert(test_db, user)
    
    users = User.find(test_db, {"email": "test@example.com"})
    assert len(users) == 1
    assert users[0].name == "Test User"
```

### Running Tests

```bash
# Run the full test suite
pytest tests/

# Run specific database tests
pytest tests/test_sqlite_pytest.py
pytest tests/test_mysql_pytest.py
```

---

## 📈 Performance Considerations

### Batch Operations

```python
# For large datasets, consider batch insertions
users_data = [
    {"name": f"User {i}", "email": f"user{i}@example.com", "age": 20 + i}
    for i in range(1000)
]

for user_data in users_data:
    db.insert("users", user_data)
```

### Connection Management

```python
# Always close connections in production
try:
    db = Akron("mysql://user:pass@localhost/prod_db")
    # ... database operations ...
finally:
    db.close()

# Or use context managers (if implementing __enter__/__exit__)
```

---

## 🔍 Examples

Check out the `examples/` directory for more comprehensive examples:

- `basic_crud.py` - Basic CRUD operations
- `sqlite_multi_table.py` - Multi-table relationships with SQLite
- `postgres_multi_table.py` - PostgreSQL with foreign keys

---

## 🆚 Version Information

- **Current Version**: v0.1.5
- **Python Requirements**: Python 3.7+
- **Dependencies**: 
  - `pydantic` - Type safety and validation
  - `mysql-connector-python` - MySQL support
  - `psycopg2` - PostgreSQL support  
  - `pymongo` - MongoDB support

### Changelog

See [CHANGELOG.md](CHANGELOG.md) for detailed version history and updates.

---

## 📚 Quick Reference

### Essential Operations Cheat Sheet

```python
# Basic CRUD
db.insert("table", {"field": "value"})
db.find("table", {"field": "value"})  
db.update("table", {"id": 1}, {"field": "new_value"})
db.delete("table", {"field": "value"})

# Advanced Querying  
db.query("table").where(age__gte=25, active=True).order_by("-created_at").limit(10).all()
db.query("table").paginate(page=1, per_page=20)

# Aggregations
db.count("table", {"active": True})
db.aggregate("table", {"total": "sum", "avg_price": "avg"})

# Bulk Operations
db.bulk_insert("table", [{"name": "A"}, {"name": "B"}])
db.bulk_update("table", {"active": False}, {"status": "inactive"})

# Transactions
with db.transaction():
    # Multiple operations here

# Performance
db.exists("table", {"email": "user@example.com"})
db.create_index("table", ["email", "status"])
db.raw("SELECT * FROM table WHERE custom_condition")
```

### QueryBuilder Operators

| Operator | Example | SQL Equivalent |
|----------|---------|----------------|
| `field` | `where(age=25)` | `WHERE age = 25` |
| `field__gt` | `where(age__gt=18)` | `WHERE age > 18` |
| `field__gte` | `where(age__gte=18)` | `WHERE age >= 18` |
| `field__lt` | `where(age__lt=65)` | `WHERE age < 65` |
| `field__lte` | `where(age__lte=65)` | `WHERE age <= 65` |
| `field__in` | `where(status__in=["active", "pending"])` | `WHERE status IN (...)` |
| `field__like` | `where(name__like="John%")` | `WHERE name LIKE 'John%'` |
| `field__isnull` | `where(deleted_at__isnull=True)` | `WHERE deleted_at IS NULL` |

---

## 🤝 Contributing

We welcome contributions! Please feel free to:

1. **Report Issues** - Found a bug? Let us know!
2. **Feature Requests** - Have an idea? We'd love to hear it!
3. **Pull Requests** - Code contributions are always welcome!

### Development Setup

```bash
# Clone the repository
git clone https://github.com/Akash-nath29/akron.git
cd akron

# Install development dependencies
pip install -e .
pip install pytest

# Run tests
pytest tests/
```

---

## 📄 License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

---

## 🔗 Links

- **PyPI Package**: https://pypi.org/project/akron/
- **GitHub Repository**: https://github.com/Akash-nath29/akron
- **Documentation**: https://akron-website.vercel.app/docs
- **Issues & Support**: https://github.com/Akash-nath29/akron/issues

---

<div align="center">

**Made with ❤️ by the Akron team**

⭐ **Star us on GitHub if you find Akron useful!** ⭐

</div>

---

## 🔄 Transactions & Atomic Operations

**Transactions** let you group multiple database operations into a single, all-or-nothing unit. This means either *all* changes succeed together, or *none* are applied if something fails. This is essential for keeping your data safe and consistent.

### Why Use Transactions?
- **Data Integrity:** Prevents partial updates and keeps your database consistent.
- **Automatic Rollback:** If any operation fails, all changes are undone automatically.
- **Business Logic:** Ensures complex operations (like money transfers, order processing) are atomic.

### Example Usage
```python
with db.transaction():
    user_id = db.insert("users", {"name": "Alice"})
    db.insert("profiles", {"user_id": user_id})
    # If any step fails, all changes are rolled back!
```

### Real-World Scenarios
- **Money Transfer:** Deduct from one account, add to another, log the transaction. If any step fails, no money is lost.
- **Order Processing:** Charge customer, reduce inventory, create order record. If payment fails, inventory isn&apos;t reduced.
- **User Registration:** Create account, profile, send email. If any step fails, no partial user is created.

### How It Works
1. Akron starts a transaction when you enter the `with db.transaction()` block.
2. If all operations succeed, changes are committed.
3. If any operation fails, Akron automatically rolls back all changes.

### Best Practices
- Use transactions for any set of operations that must succeed together.
- Don&apos;t use transactions for simple, single-step reads or writes.