Custom Types

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

All custom types include:

• Automatic dialect-specific implementations

• Python type annotation support

• Consistent behavior across database backends

• Integration with SQLAlchemy’s type system

Type Categories

Category	Types
Basic Types	GUID, DateTimeUTC, JsonB, BigIntIdentity
Security Types	EncryptedString, EncryptedText, PasswordHash
File Storage	FileObject, FileObjectList, StoredObject
Collections	MutableList

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

Detailed Documentation

• Basic Types
• Security Types
• File Storage
• Advanced Types

See Also

• Modeling - Using types in models

• Storage Backends - Storage backend configuration

• types - API reference for all types