Portable Column Types#
Advanced Alchemy provides custom SQLAlchemy TypeDecorator implementations that compile to dialect-appropriate SQL types across SQLite, PostgreSQL, CockroachDB, MySQL, MariaDB, Oracle, DuckDB, SQL Server, and Spanner while preserving consistent Python types.
Common Column Types at a Glance#
Column Type |
Description |
|---|---|
|
Converts datetimes to UTC before saving, returns timezone-aware UTC datetimes on read, and supports configurable fractional-second precision ( |
|
Stores Python |
|
Compiles to 64-bit |
|
Compiles to binary JSON on PostgreSQL, CockroachDB ( |
|
Stores float vector embeddings using native vector columns on PostgreSQL ( |
Oracle JSON storage#
JsonB and ORA_JSONB use native JSON on Oracle 21c and later
with SQLAlchemy 2.1 or later. Older combinations use a BLOB with an
IS JSON check. On Oracle versions before 21c, JSON documents must be
objects or arrays at the top level. Python None maps to SQL NULL.
Use ORA_JSONB(use_blob=True) to map an existing Oracle BLOB column
when upgrading to a combination that supports native JSON. If earlier
writes double-encoded a value, Oracle stores it as a JSON string. Repair
those rows explicitly using the expected application data shape.
Examples by Type#
from datetime import datetime
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntBase, UUIDBase, create_registry
from advanced_alchemy.types import DateTimeUTC, JsonB
class AuditEvent(BigIntBase):
__tablename__ = "audit_event"
occurred_at: Mapped[datetime] = mapped_column(DateTimeUTC(fsp=6))
metadata_payload: Mapped[dict[str, str]] = mapped_column(JsonB)
class PrecisionBase(UUIDBase):
__abstract__ = True
registry = create_registry(custom_annotation_map={datetime: DateTimeUTC(fsp=6)})
from uuid import UUID
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import DefaultBase
from advanced_alchemy.types import BigIntIdentity, GUID
class LedgerEntry(DefaultBase):
__tablename__ = "ledger_entry"
id: Mapped[int] = mapped_column(BigIntIdentity, primary_key=True)
external_ref: Mapped[UUID] = mapped_column(GUID, index=True)
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import UUIDv7Base
from advanced_alchemy.types import Vector
class DocumentChunk(UUIDv7Base):
__tablename__ = "document_chunk"
embedding: Mapped[list[float]] = mapped_column(Vector(dim=3))
Note
On MySQL/MariaDB and Oracle, SQLAlchemy’s default DateTime(timezone=True) stores whole seconds only. Pass DateTimeUTC(fsp=6) (or set custom_annotation_map={datetime: DateTimeUTC(fsp=6)} via create_registry()) to preserve microseconds, or pass DateTimeUTC(fsp=0) to keep legacy whole-second DDL without migration churn. In v2.0, fsp=6 becomes the default.
Storing Python Enums#
By default, SQLAlchemy’s Enum persists member names (such as "ACTIVE"). Pass values_callable when your database schema stores member values (such as "active"), and set native_enum=False for portable VARCHAR constraints across backends:
import enum
import sqlalchemy as sa
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntBase
class JobState(str, enum.Enum):
PENDING = "pending"
RUNNING = "running"
class BackgroundJob(BigIntBase):
__tablename__ = "types_background_job"
state: Mapped[JobState] = mapped_column(
sa.Enum(JobState, values_callable=lambda obj: [item.value for item in obj], native_enum=False),
default=JobState.PENDING,
)
import enum
import sqlalchemy as sa
from sqlalchemy.orm import Mapped, mapped_column
from advanced_alchemy.base import BigIntBase
class JobState(enum.StrEnum):
PENDING = "pending"
RUNNING = "running"
class BackgroundJob(BigIntBase):
__tablename__ = "types_background_job"
state: Mapped[JobState] = mapped_column(
sa.Enum(JobState, values_callable=lambda obj: [item.value for item in obj], native_enum=False),
default=JobState.PENDING,
)