akron 0.2.4

Universal, framework-independent ORM for Python.

Akron

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.

---

✨ 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

pip install akron

Basic Usage

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": "[e-mail]", 
    "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": "[e-mail]", "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": "[e-mail]"})

# 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

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="[e-mail]", 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": "[e-mail]"})

Django-like Foreign Keys

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="[e-mail]")
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

# 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

# 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": "[e-mail]"})
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

# 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": "[e-mail]"})
    db.insert("profiles", {"user_id": user_id, "bio": "New user profile"})
    # All operations committed together or rolled back on error

Performance and Indexing

# 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": "[e-mail]"}):
    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

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="[e-mail]", 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

# 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:

{
  "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

# 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

# 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

# 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)

# 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": "[e-mail]"}'
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

# 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

# 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

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

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

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="[e-mail]")
    User.insert(test_db, user)
    
    users = User.find(test_db, {"email": "[e-mail]"})
    assert len(users) == 1
    assert users[0].name == "Test User"

Running Tests

# 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

# 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

# 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 for detailed version history and updates.

---

📚 Quick Reference

Essential Operations Cheat Sheet

# 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": "[e-mail]"})
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

# 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 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

---

Made with ❤️ by the Akron team

⭐ Star us on GitHub if you find Akron useful! ⭐

---

🔄 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

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'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't use transactions for simple, single-step reads or writes.