Custom Types¶

Advanced Alchemy provides SQLAlchemy custom types that adapt to different database backends, ensure data integrity, and provide Python-native type annotations.

Learning Path¶

Follow this progression to master Advanced Alchemy types:

1. Basic Types

GUID, DateTimeUTC, JsonB, and identity types

Basic Types

2. Security Types

EncryptedString and PasswordHash

Security Types

3. File Storage

FileObject and cloud storage integration

File Storage

4. Advanced Types

Mutable collections and custom types

Advanced Types

Prerequisites¶

Understanding of Basics
Python 3.9+
SQLAlchemy 2.0+

Overview¶

Advanced Alchemy custom types provide:

Automatic dialect-specific implementations
Python type annotation support
Consistent behavior across database backends
Integration with SQLAlchemy’s type system

Quick Reference¶

Python 3.9+

from typing import Optional
from datetime import datetime
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import UUIDAuditBase
from advanced_alchemy.types import (
    DateTimeUTC,
    JsonB,
    EncryptedString,
    FileObject,
    StoredObject,
)

class User(UUIDAuditBase):
    __tablename__ = "users"

    # UUID primary key (handled by UUIDAuditBase)
    email: "Mapped[str]" = mapped_column(unique=True)

    # UTC timezone-aware datetime
    last_login: "Mapped[Optional[datetime]]" = mapped_column(DateTimeUTC)

    # Efficient JSON storage
    preferences: "Mapped[dict]" = mapped_column(JsonB)

    # Encrypted password
    password: "Mapped[str]" = mapped_column(
        EncryptedString(key="encryption-key")
    )

    # File storage
    avatar: "Mapped[Optional[FileObject]]" = mapped_column(
        StoredObject(backend="s3")
    )

Python 3.10+

from datetime import datetime
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import UUIDAuditBase
from advanced_alchemy.types import (
    DateTimeUTC,
    JsonB,
    EncryptedString,
    FileObject,
    StoredObject,
)

class User(UUIDAuditBase):
    __tablename__ = "users"

    # UUID primary key (handled by UUIDAuditBase)
    email: "Mapped[str]" = mapped_column(unique=True)

    # UTC timezone-aware datetime
    last_login: "Mapped[datetime | None]" = mapped_column(DateTimeUTC)

    # Efficient JSON storage
    preferences: "Mapped[dict]" = mapped_column(JsonB)

    # Encrypted password
    password: "Mapped[str]" = mapped_column(
        EncryptedString(key="encryption-key")
    )

    # File storage
    avatar: "Mapped[FileObject | None]" = mapped_column(
        StoredObject(backend="s3")
    )

Database Compatibility¶

Advanced Alchemy custom types work across all supported database backends:

Type	PostgreSQL	SQLite	Oracle	MySQL	Others
GUID	UUID	BINARY(16)	RAW(16)	BINARY(16)	BINARY(16)
DateTimeUTC	TIMESTAMP	DATETIME	TIMESTAMP	DATETIME	DATETIME
JsonB	JSONB	JSON	BLOB+CHECK	JSON	JSON
BigIntIdentity	BIGINT	INTEGER	NUMBER(19)	BIGINT	BIGINT
EncryptedString	VARCHAR	VARCHAR	VARCHAR2	VARCHAR	VARCHAR
FileObject	JSON/JSONB	JSON	BLOB+CHECK	JSON	JSON

Type Selection Guide¶

For UUID primary keys:

Use base classes: UUIDBase, UUIDAuditBase
Manual column: GUID type

For timestamps:

Use base classes with audit columns: UUIDAuditBase, BigIntAuditBase
Manual column: DateTimeUTC type

For JSON data:

PostgreSQL/Oracle/CockroachDB: JsonB uses native binary JSON
Other databases: Standard JSON type

For sensitive data:

Passwords: PasswordHash with automatic hashing
Encrypted fields: EncryptedString or EncryptedText

For file storage:

Cloud storage: StoredObject with S3/GCS/Azure backends
Local storage: StoredObject with local filesystem backend

Next Steps¶

Basic Types - GUID, DateTimeUTC, JsonB, and identity types
Security Types - EncryptedString and PasswordHash
File Storage - FileObject and cloud storage integration
Advanced Types - Mutable collections and custom types