CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/pypi-sqlmodel

SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness.

Overall
score

85%

Overview
Eval results
Files

data-types.mddocs/

Data Types

SQLModel provides a comprehensive SQL data type system by re-exporting SQLAlchemy's type system while adding its own enhancements. This includes both SQL standard types and database-specific types, with automatic Python type mapping and validation through Pydantic integration.

Capabilities

SQLModel Custom Types

SQLModel-specific type extensions that enhance the SQLAlchemy type system.

class AutoString(TypeDecorator):
    """
    Automatic string type that adapts to different databases.
    
    Automatically selects appropriate string type based on the database:
    - VARCHAR for most databases
    - TEXT for longer strings when needed
    - Handles Unicode properly across databases
    """

Usage Example:

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # AutoString automatically handles string type selection
    description: str = Field(sa_type=AutoString)

Numeric Types

SQL numeric types for integers, decimals, and floating-point numbers.

# SQL standard type constants
BIGINT: TypeEngine           # 64-bit integer
INTEGER: TypeEngine          # 32-bit integer  
INT: TypeEngine             # Alias for INTEGER
SMALLINT: TypeEngine        # 16-bit integer
NUMERIC: TypeEngine         # Exact decimal numbers
DECIMAL: TypeEngine         # Alias for NUMERIC
FLOAT: TypeEngine           # Floating-point numbers
REAL: TypeEngine            # Single precision float
DOUBLE: TypeEngine          # Double precision float
DOUBLE_PRECISION: TypeEngine # Alias for DOUBLE

# Python-oriented type classes
class BigInteger(TypeEngine):
    """64-bit integer type with Python int mapping."""

class Integer(TypeEngine):
    """32-bit integer type with Python int mapping."""

class SmallInteger(TypeEngine):
    """16-bit integer type with Python int mapping."""

class Numeric(TypeEngine):
    """
    Exact decimal type with configurable precision and scale.
    Maps to Python Decimal for exact arithmetic.
    """

class Float(TypeEngine):
    """
    Floating-point type with optional precision.
    Maps to Python float.
    """

class Double(TypeEngine):
    """Double precision floating-point type."""

Usage Examples:

from decimal import Decimal

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # Integer types
    quantity: int = Field(sa_type=Integer)
    small_count: int = Field(sa_type=SmallInteger)  
    large_id: int = Field(sa_type=BigInteger)
    
    # Decimal for precise money calculations
    price: Decimal = Field(sa_type=Numeric(precision=10, scale=2))
    discount_rate: float = Field(sa_type=Float(precision=2))

String and Text Types

SQL string types for character data with various length constraints.

# SQL standard string constants
CHAR: TypeEngine            # Fixed-length character string
VARCHAR: TypeEngine         # Variable-length character string
TEXT: TypeEngine            # Large text data
NCHAR: TypeEngine           # Fixed-length Unicode string
NVARCHAR: TypeEngine        # Variable-length Unicode string  
CLOB: TypeEngine            # Character Large Object

# Python-oriented string classes
class String(TypeEngine):
    """
    Variable-length string type.
    
    Parameters:
        length: Maximum string length (None for unlimited)
        collation: Database collation to use
    """

class Text(TypeEngine):
    """
    Large text type for long strings.
    Typically used for content that exceeds VARCHAR limits.
    """

class Unicode(TypeEngine):
    """
    Unicode string type ensuring proper Unicode handling.
    """

class UnicodeText(TypeEngine):
    """
    Large Unicode text type.
    """

Usage Examples:

class Article(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # String types with length limits
    title: str = Field(sa_type=String(200))
    slug: str = Field(sa_type=VARCHAR(100), unique=True)
    
    # Large text content
    content: str = Field(sa_type=Text)
    
    # Unicode support
    title_unicode: str = Field(sa_type=Unicode(200))
    content_unicode: str = Field(sa_type=UnicodeText)
    
    # Fixed-length strings
    status_code: str = Field(sa_type=CHAR(3))

Binary Data Types

SQL binary types for storing binary data and byte sequences.

# SQL binary constants
BINARY: TypeEngine          # Fixed-length binary data
VARBINARY: TypeEngine       # Variable-length binary data
BLOB: TypeEngine            # Binary Large Object

# Python-oriented binary class
class LargeBinary(TypeEngine):
    """
    Binary data type for storing byte sequences.
    Maps to Python bytes objects.
    """

Usage Examples:

class Document(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    filename: str
    
    # Binary content storage
    content: bytes = Field(sa_type=LargeBinary)
    
    # Fixed-size binary data
    checksum: bytes = Field(sa_type=BINARY(32))  # SHA-256 hash

Date and Time Types

SQL temporal types for dates, times, and timestamps.

# SQL temporal constants
DATE: TypeEngine            # Date only (year, month, day)
TIME: TypeEngine            # Time only (hour, minute, second)
DATETIME: TypeEngine        # Date and time combined
TIMESTAMP: TypeEngine       # Timestamp with timezone info

# Python-oriented temporal classes
class Date(TypeEngine):
    """
    Date type mapping to Python datetime.date objects.
    """

class Time(TypeEngine):
    """
    Time type mapping to Python datetime.time objects.
    
    Parameters:
        timezone: Whether to include timezone information
    """

class DateTime(TypeEngine):
    """
    DateTime type mapping to Python datetime.datetime objects.
    
    Parameters:
        timezone: Whether to include timezone information
    """

class Interval(TypeEngine):
    """
    Time interval type mapping to Python datetime.timedelta objects.
    """

Usage Examples:

from datetime import date, time, datetime, timedelta

class Event(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # Date and time fields
    event_date: date = Field(sa_type=Date)
    start_time: time = Field(sa_type=Time)
    created_at: datetime = Field(sa_type=DateTime(timezone=True))
    
    # Duration
    duration: timedelta = Field(sa_type=Interval)
    
    # Auto-timestamps
    updated_at: datetime = Field(
        default_factory=datetime.utcnow,
        sa_column_kwargs={"onupdate": datetime.utcnow}
    )

Boolean Type

SQL boolean type for true/false values.

# SQL boolean constant
BOOLEAN: TypeEngine         # Boolean true/false values

# Python-oriented boolean class
class Boolean(TypeEngine):
    """
    Boolean type mapping to Python bool objects.
    
    Handles database-specific boolean representations.
    """

Usage Examples:

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str
    
    # Boolean flags
    is_active: bool = Field(default=True, sa_type=Boolean)
    is_admin: bool = Field(default=False)
    email_verified: bool = Field(default=False, sa_type=BOOLEAN)

JSON and Structured Data Types

Types for storing structured data as JSON.

JSON: TypeEngine            # JSON data type

Usage Examples:

from typing import Dict, Any

class UserPreferences(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    
    # JSON data storage
    preferences: Dict[str, Any] = Field(sa_type=JSON)
    metadata: dict = Field(default_factory=dict, sa_type=JSON)

UUID Types

Universal Unique Identifier types.

# SQL UUID constants
UUID: TypeEngine            # UUID data type

# Python-oriented UUID class
class Uuid(TypeEngine):
    """
    UUID type mapping to Python uuid.UUID objects.
    
    Handles database-specific UUID storage formats.
    """

Usage Examples:

import uuid
from uuid import UUID as PyUUID

class Session(SQLModel, table=True):
    # UUID primary key
    id: PyUUID = Field(
        default_factory=uuid.uuid4,
        primary_key=True,
        sa_type=UUID
    )
    user_id: int = Field(foreign_key="user.id")
    
    # UUID token
    token: PyUUID = Field(default_factory=uuid.uuid4, sa_type=Uuid)

Specialized Types

Additional specialized SQL types for specific use cases.

class Enum(TypeEngine):
    """
    Enumeration type constraining values to a specific set.
    
    Parameters:
        *enums: Allowed enumeration values
        name: Name of the enum type in the database
    """

class PickleType(TypeEngine):
    """
    Type that serializes Python objects using pickle.
    
    Stores arbitrary Python objects as binary data.
    """

class TupleType(TypeEngine):
    """
    Type for storing tuples of values.
    """

class TypeDecorator(TypeEngine):
    """
    Base class for creating custom types that wrap existing types.
    """

# Array type (PostgreSQL-specific)
ARRAY: TypeEngine           # Array of values (PostgreSQL)

Usage Examples:

from enum import Enum as PyEnum

# Enum type
class StatusEnum(str, PyEnum):
    ACTIVE = "active"
    INACTIVE = "inactive" 
    PENDING = "pending"

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    username: str
    
    # Enum field
    status: StatusEnum = Field(
        default=StatusEnum.PENDING,
        sa_type=Enum(StatusEnum, name="user_status")
    )
    
    # Pickle type for complex objects
    settings: dict = Field(default_factory=dict, sa_type=PickleType)

# PostgreSQL arrays
class Tag(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    
class Article(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    
    # Array of tag names (PostgreSQL)
    tag_names: List[str] = Field(
        default_factory=list,
        sa_type=ARRAY(String(50))
    )

Type Mapping and Integration

SQLModel automatically maps Python types to appropriate SQL types, but you can override this behavior:

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # Automatic type mapping
    name: str                    # -> VARCHAR/TEXT
    age: Optional[int] = None    # -> INTEGER, nullable
    is_active: bool = True       # -> BOOLEAN
    created_at: datetime         # -> DATETIME/TIMESTAMP
    
    # Explicit type specification
    description: str = Field(sa_type=Text)           # Force TEXT type
    precise_value: Decimal = Field(sa_type=Numeric(10, 4))  # Exact decimal
    binary_data: bytes = Field(sa_type=LargeBinary)  # Binary storage
    
    # Custom type with validation
    email: str = Field(
        sa_type=String(320),  # Max email length
        regex=r'^[^@]+@[^@]+\.[^@]+$'  # Email validation
    )

Database-Specific Considerations

Different databases have varying type support and behavior:

# PostgreSQL-specific types
from sqlalchemy.dialects.postgresql import UUID as PG_UUID, JSONB, ARRAY

class PostgreSQLModel(SQLModel, table=True):
    id: PyUUID = Field(default_factory=uuid.uuid4, primary_key=True, sa_type=PG_UUID)
    data: dict = Field(sa_type=JSONB)  # More efficient than JSON
    tags: List[str] = Field(sa_type=ARRAY(String))

# MySQL-specific considerations  
class MySQLModel(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    # MySQL TEXT types have size limits
    content: str = Field(sa_type=Text)  # 65KB limit
    # Use LONGTEXT for larger content
    large_content: str = Field(sa_type=Text().with_variant(
        mysql.LONGTEXT(), "mysql"
    ))

Install with Tessl CLI

npx tessl i tessl/pypi-sqlmodel

docs

data-types.md

database-engine.md

index.md

model-definition.md

schema-definition.md

session-management.md

sql-operations.md

tile.json