Python SQLAlchemy: How to Define Column and Data Types in SQLAlchemy in Python

Learn how to properly define SQLAlchemy column types using modern Mapped and mapped_column syntax, avoid common pitfalls like floating-point currency errors, and implement nullable fields, server-side defaults, and constraints for robust database models.

Choosing the correct data types for your database columns is essential for maintaining data integrity and optimizing query performance. SQLAlchemy offers a wide array of types that map Python objects to SQL storage, and the modern SQLAlchemy 2.0 Declarative style makes these mappings safer and more expressive through Python type hints.

This guide covers the most common column types, explains how to use the modern Mapped and mapped_column syntax, and walks through practical patterns like nullable fields, server-side defaults, and common mistakes to avoid.

Common Core Types

SQLAlchemy provides a set of generic types that work across all major databases, including PostgreSQL, MySQL, and SQLite. These types automatically translate to the appropriate SQL type for your specific database engine:

Type	Python Type	SQL Equivalent	Typical Use Case
`Integer`	`int`	`INTEGER`	Primary keys, counters, quantities
`String(n)`	`str`	`VARCHAR(n)`	Names, emails, short text with a length limit
`Text`	`str`	`TEXT`	Descriptions, blog posts, unlimited-length text
`Numeric(p, s)`	`Decimal`	`NUMERIC(p, s)`	Currency, precise calculations
`Float`	`float`	`FLOAT`	Scientific measurements, approximate values
`Boolean`	`bool`	`BOOLEAN`	Flags, toggles, true/false states
`DateTime`	`datetime`	`DATETIME` / `TIMESTAMP`	Timestamps, scheduling
`Date`	`date`	`DATE`	Birthdays, calendar dates
`LargeBinary`	`bytes`	`BLOB`	Files, images, binary data

Modern 2.0 Syntax: Mapped and mapped_column

SQLAlchemy 2.0 introduced a declarative style that uses Python type hints (Mapped[]) to define the relationship between Python types and database columns. This approach enables IDEs to provide better autocompletion, type checking, and error detection:

from datetime import datetime
from decimal import Decimal
from sqlalchemy import String, Numeric, DateTime, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)

    title: Mapped[str] = mapped_column(String(100))

    description: Mapped[str] = mapped_column(Text)

    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))

    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow
    )

The Mapped[int] annotation tells both SQLAlchemy and your IDE what Python type to expect, while mapped_column(...) specifies the SQL-level details like the column type, constraints, and defaults.

Handling Nullable and Optional Columns

By default, columns defined with Mapped[str] are non-nullable. To allow NULL values, use Optional from Python's typing module:

from typing import Optional
from datetime import datetime
from sqlalchemy import String, DateTime
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)

    email: Mapped[str] = mapped_column(String(255))

    phone: Mapped[Optional[str]] = mapped_column(String(20), default=None)

    last_login: Mapped[Optional[datetime]] = mapped_column(DateTime, default=None)

user = User(email="alice@example.com")

user_with_phone = User(email="bob@example.com", phone="555-0123")

Common Mistake: Using Float for Currency

One of the most frequent data type errors is using Float for monetary values. Floating-point arithmetic introduces tiny rounding errors that accumulate over time:

from sqlalchemy import Float, Numeric
from decimal import Decimal
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class InvoiceBad(Base):
    __tablename__ = "invoices_bad"

    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[float] = mapped_column(Float)

print(0.1 + 0.2)  # 0.30000000000000004

Output:

0.30000000000000004

class InvoiceGood(Base):
    __tablename__ = "invoices_good"

    id: Mapped[int] = mapped_column(primary_key=True)
    amount: Mapped[Decimal] = mapped_column(Numeric(10, 2))

from decimal import Decimal
print(Decimal("0.1") + Decimal("0.2"))  # 0.3

Output:

0.3

Server-Side Defaults vs Python Defaults

SQLAlchemy supports two kinds of default values, and the distinction matters in production environments:

default: Runs in your Python application before the data is sent to the database.
server_default: Creates a default rule inside the SQL table definition itself, so the database generates the value.

from datetime import datetime
from sqlalchemy import DateTime, String, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class AuditLog(Base):
    __tablename__ = "audit_logs"

    id: Mapped[int] = mapped_column(primary_key=True)
    action: Mapped[str] = mapped_column(String(100))

    created_by_python: Mapped[datetime] = mapped_column(
        DateTime, default=datetime.utcnow
    )

    created_by_db: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now()
    )

Adding Constraints and Indexes

Columns often need additional constraints beyond just their data type. You can add uniqueness, indexing, and check constraints directly in mapped_column:

from sqlalchemy import String, Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = "employees"

    id: Mapped[int] = mapped_column(primary_key=True)

    email: Mapped[str] = mapped_column(String(255), unique=True)

    department: Mapped[str] = mapped_column(String(100), index=True)

    status: Mapped[str] = mapped_column(String(20), default="active")

    employee_number: Mapped[int] = mapped_column(
        Integer, unique=True, name="emp_no"
    )

Complete Practical Example

Here is a realistic model that combines multiple type choices, defaults, and constraints:

from datetime import datetime
from decimal import Decimal
from typing import Optional
from sqlalchemy import String, Text, Numeric, DateTime, Boolean, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Order(Base):
    __tablename__ = "orders"

    id: Mapped[int] = mapped_column(primary_key=True)
    order_number: Mapped[str] = mapped_column(String(50), unique=True, index=True)
    customer_email: Mapped[str] = mapped_column(String(255))
    total: Mapped[Decimal] = mapped_column(Numeric(12, 2))
    notes: Mapped[Optional[str]] = mapped_column(Text, default=None)
    is_paid: Mapped[bool] = mapped_column(Boolean, default=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime, server_default=func.now()
    )
    updated_at: Mapped[Optional[datetime]] = mapped_column(DateTime, default=None)

order = Order(
    order_number="ORD-2024-001",
    customer_email="alice@example.com",
    total=Decimal("149.99"),
    notes="Rush delivery requested"
)

Quick Reference

Goal	Type Choice	Example
Auto-incrementing ID	`Integer` + `primary_key=True`	`mapped_column(primary_key=True)`
Short text with limit	`String(n)`	`mapped_column(String(255))`
Long text, no limit	`Text`	`mapped_column(Text)`
Money and financials	`Numeric(p, s)`	`mapped_column(Numeric(10, 2))`
True/false flag	`Boolean`	`mapped_column(Boolean, default=False)`
Timestamp (DB clock)	`DateTime` + `server_default`	`mapped_column(DateTime, server_default=func.now())`
Optional/nullable field	`Optional[type]`	`Mapped[Optional[str]]`
Binary file storage	`LargeBinary`	`mapped_column(LargeBinary)`

Summary

Defining column types correctly in SQLAlchemy prevents data corruption, improves query performance, and catches bugs early through type checking.

Use the Mapped and mapped_column syntax from SQLAlchemy 2.0 for type-safe models that work well with modern IDEs.
Always use Numeric instead of Float for currency.
Prefer server_default over default for timestamps in multi-server environments.
Use Optional to explicitly mark columns that accept NULL, and add unique and index constraints directly in your column definitions to enforce data integrity at the database level.