0
# Data Types
1

2
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.
3

4
## Capabilities
5

6
### SQLModel Custom Types
7

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

10
```python { .api }
11
class AutoString(TypeDecorator):
12
    """
13
    Automatic string type that adapts to different databases.
14
    
15
    Automatically selects appropriate string type based on the database:
16
    - VARCHAR for most databases
17
    - TEXT for longer strings when needed
18
    - Handles Unicode properly across databases
19
    """
20
```
21

22
**Usage Example:**
23
```python
24
class Hero(SQLModel, table=True):
25
    id: Optional[int] = Field(default=None, primary_key=True)
26
    # AutoString automatically handles string type selection
27
    description: str = Field(sa_type=AutoString)
28
```
29

30
### Numeric Types
31

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

34
```python { .api }
35
# SQL standard type constants
36
BIGINT: TypeEngine           # 64-bit integer
37
INTEGER: TypeEngine          # 32-bit integer  
38
INT: TypeEngine             # Alias for INTEGER
39
SMALLINT: TypeEngine        # 16-bit integer
40
NUMERIC: TypeEngine         # Exact decimal numbers
41
DECIMAL: TypeEngine         # Alias for NUMERIC
42
FLOAT: TypeEngine           # Floating-point numbers
43
REAL: TypeEngine            # Single precision float
44
DOUBLE: TypeEngine          # Double precision float
45
DOUBLE_PRECISION: TypeEngine # Alias for DOUBLE
46

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

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

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

57
class Numeric(TypeEngine):
58
    """
59
    Exact decimal type with configurable precision and scale.
60
    Maps to Python Decimal for exact arithmetic.
61
    """
62

63
class Float(TypeEngine):
64
    """
65
    Floating-point type with optional precision.
66
    Maps to Python float.
67
    """
68

69
class Double(TypeEngine):
70
    """Double precision floating-point type."""
71
```
72

73
**Usage Examples:**
74
```python
75
from decimal import Decimal
76

77
class Product(SQLModel, table=True):
78
    id: Optional[int] = Field(default=None, primary_key=True)
79
    
80
    # Integer types
81
    quantity: int = Field(sa_type=Integer)
82
    small_count: int = Field(sa_type=SmallInteger)  
83
    large_id: int = Field(sa_type=BigInteger)
84
    
85
    # Decimal for precise money calculations
86
    price: Decimal = Field(sa_type=Numeric(precision=10, scale=2))
87
    discount_rate: float = Field(sa_type=Float(precision=2))
88
```
89

90
### String and Text Types
91

92
SQL string types for character data with various length constraints.
93

94
```python { .api }
95
# SQL standard string constants
96
CHAR: TypeEngine            # Fixed-length character string
97
VARCHAR: TypeEngine         # Variable-length character string
98
TEXT: TypeEngine            # Large text data
99
NCHAR: TypeEngine           # Fixed-length Unicode string
100
NVARCHAR: TypeEngine        # Variable-length Unicode string  
101
CLOB: TypeEngine            # Character Large Object
102

103
# Python-oriented string classes
104
class String(TypeEngine):
105
    """
106
    Variable-length string type.
107
    
108
    Parameters:
109
        length: Maximum string length (None for unlimited)
110
        collation: Database collation to use
111
    """
112

113
class Text(TypeEngine):
114
    """
115
    Large text type for long strings.
116
    Typically used for content that exceeds VARCHAR limits.
117
    """
118

119
class Unicode(TypeEngine):
120
    """
121
    Unicode string type ensuring proper Unicode handling.
122
    """
123

124
class UnicodeText(TypeEngine):
125
    """
126
    Large Unicode text type.
127
    """
128
```
129

130
**Usage Examples:**
131
```python
132
class Article(SQLModel, table=True):
133
    id: Optional[int] = Field(default=None, primary_key=True)
134
    
135
    # String types with length limits
136
    title: str = Field(sa_type=String(200))
137
    slug: str = Field(sa_type=VARCHAR(100), unique=True)
138
    
139
    # Large text content
140
    content: str = Field(sa_type=Text)
141
    
142
    # Unicode support
143
    title_unicode: str = Field(sa_type=Unicode(200))
144
    content_unicode: str = Field(sa_type=UnicodeText)
145
    
146
    # Fixed-length strings
147
    status_code: str = Field(sa_type=CHAR(3))
148
```
149

150
### Binary Data Types
151

152
SQL binary types for storing binary data and byte sequences.
153

154
```python { .api }
155
# SQL binary constants
156
BINARY: TypeEngine          # Fixed-length binary data
157
VARBINARY: TypeEngine       # Variable-length binary data
158
BLOB: TypeEngine            # Binary Large Object
159

160
# Python-oriented binary class
161
class LargeBinary(TypeEngine):
162
    """
163
    Binary data type for storing byte sequences.
164
    Maps to Python bytes objects.
165
    """
166
```
167

168
**Usage Examples:**
169
```python
170
class Document(SQLModel, table=True):
171
    id: Optional[int] = Field(default=None, primary_key=True)
172
    filename: str
173
    
174
    # Binary content storage
175
    content: bytes = Field(sa_type=LargeBinary)
176
    
177
    # Fixed-size binary data
178
    checksum: bytes = Field(sa_type=BINARY(32))  # SHA-256 hash
179
```
180

181
### Date and Time Types
182

183
SQL temporal types for dates, times, and timestamps.
184

185
```python { .api }
186
# SQL temporal constants
187
DATE: TypeEngine            # Date only (year, month, day)
188
TIME: TypeEngine            # Time only (hour, minute, second)
189
DATETIME: TypeEngine        # Date and time combined
190
TIMESTAMP: TypeEngine       # Timestamp with timezone info
191

192
# Python-oriented temporal classes
193
class Date(TypeEngine):
194
    """
195
    Date type mapping to Python datetime.date objects.
196
    """
197

198
class Time(TypeEngine):
199
    """
200
    Time type mapping to Python datetime.time objects.
201
    
202
    Parameters:
203
        timezone: Whether to include timezone information
204
    """
205

206
class DateTime(TypeEngine):
207
    """
208
    DateTime type mapping to Python datetime.datetime objects.
209
    
210
    Parameters:
211
        timezone: Whether to include timezone information
212
    """
213

214
class Interval(TypeEngine):
215
    """
216
    Time interval type mapping to Python datetime.timedelta objects.
217
    """
218
```
219

220
**Usage Examples:**
221
```python
222
from datetime import date, time, datetime, timedelta
223

224
class Event(SQLModel, table=True):
225
    id: Optional[int] = Field(default=None, primary_key=True)
226
    
227
    # Date and time fields
228
    event_date: date = Field(sa_type=Date)
229
    start_time: time = Field(sa_type=Time)
230
    created_at: datetime = Field(sa_type=DateTime(timezone=True))
231
    
232
    # Duration
233
    duration: timedelta = Field(sa_type=Interval)
234
    
235
    # Auto-timestamps
236
    updated_at: datetime = Field(
237
        default_factory=datetime.utcnow,
238
        sa_column_kwargs={"onupdate": datetime.utcnow}
239
    )
240
```
241

242
### Boolean Type
243

244
SQL boolean type for true/false values.
245

246
```python { .api }
247
# SQL boolean constant
248
BOOLEAN: TypeEngine         # Boolean true/false values
249

250
# Python-oriented boolean class
251
class Boolean(TypeEngine):
252
    """
253
    Boolean type mapping to Python bool objects.
254
    
255
    Handles database-specific boolean representations.
256
    """
257
```
258

259
**Usage Examples:**
260
```python
261
class User(SQLModel, table=True):
262
    id: Optional[int] = Field(default=None, primary_key=True)
263
    username: str
264
    
265
    # Boolean flags
266
    is_active: bool = Field(default=True, sa_type=Boolean)
267
    is_admin: bool = Field(default=False)
268
    email_verified: bool = Field(default=False, sa_type=BOOLEAN)
269
```
270

271
### JSON and Structured Data Types
272

273
Types for storing structured data as JSON.
274

275
```python { .api }
276
JSON: TypeEngine            # JSON data type
277
```
278

279
**Usage Examples:**
280
```python
281
from typing import Dict, Any
282

283
class UserPreferences(SQLModel, table=True):
284
    id: Optional[int] = Field(default=None, primary_key=True)
285
    user_id: int = Field(foreign_key="user.id")
286
    
287
    # JSON data storage
288
    preferences: Dict[str, Any] = Field(sa_type=JSON)
289
    metadata: dict = Field(default_factory=dict, sa_type=JSON)
290
```
291

292
### UUID Types
293

294
Universal Unique Identifier types.
295

296
```python { .api }
297
# SQL UUID constants
298
UUID: TypeEngine            # UUID data type
299

300
# Python-oriented UUID class
301
class Uuid(TypeEngine):
302
    """
303
    UUID type mapping to Python uuid.UUID objects.
304
    
305
    Handles database-specific UUID storage formats.
306
    """
307
```
308

309
**Usage Examples:**
310
```python
311
import uuid
312
from uuid import UUID as PyUUID
313

314
class Session(SQLModel, table=True):
315
    # UUID primary key
316
    id: PyUUID = Field(
317
        default_factory=uuid.uuid4,
318
        primary_key=True,
319
        sa_type=UUID
320
    )
321
    user_id: int = Field(foreign_key="user.id")
322
    
323
    # UUID token
324
    token: PyUUID = Field(default_factory=uuid.uuid4, sa_type=Uuid)
325
```
326

327
### Specialized Types
328

329
Additional specialized SQL types for specific use cases.
330

331
```python { .api }
332
class Enum(TypeEngine):
333
    """
334
    Enumeration type constraining values to a specific set.
335
    
336
    Parameters:
337
        *enums: Allowed enumeration values
338
        name: Name of the enum type in the database
339
    """
340

341
class PickleType(TypeEngine):
342
    """
343
    Type that serializes Python objects using pickle.
344
    
345
    Stores arbitrary Python objects as binary data.
346
    """
347

348
class TupleType(TypeEngine):
349
    """
350
    Type for storing tuples of values.
351
    """
352

353
class TypeDecorator(TypeEngine):
354
    """
355
    Base class for creating custom types that wrap existing types.
356
    """
357

358
# Array type (PostgreSQL-specific)
359
ARRAY: TypeEngine           # Array of values (PostgreSQL)
360
```
361

362
**Usage Examples:**
363
```python
364
from enum import Enum as PyEnum
365

366
# Enum type
367
class StatusEnum(str, PyEnum):
368
    ACTIVE = "active"
369
    INACTIVE = "inactive" 
370
    PENDING = "pending"
371

372
class User(SQLModel, table=True):
373
    id: Optional[int] = Field(default=None, primary_key=True)
374
    username: str
375
    
376
    # Enum field
377
    status: StatusEnum = Field(
378
        default=StatusEnum.PENDING,
379
        sa_type=Enum(StatusEnum, name="user_status")
380
    )
381
    
382
    # Pickle type for complex objects
383
    settings: dict = Field(default_factory=dict, sa_type=PickleType)
384

385
# PostgreSQL arrays
386
class Tag(SQLModel, table=True):
387
    id: Optional[int] = Field(default=None, primary_key=True)
388
    name: str
389
    
390
class Article(SQLModel, table=True):
391
    id: Optional[int] = Field(default=None, primary_key=True)
392
    title: str
393
    
394
    # Array of tag names (PostgreSQL)
395
    tag_names: List[str] = Field(
396
        default_factory=list,
397
        sa_type=ARRAY(String(50))
398
    )
399
```
400

401
### Type Mapping and Integration
402

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

405
```python
406
class Hero(SQLModel, table=True):
407
    id: Optional[int] = Field(default=None, primary_key=True)
408
    
409
    # Automatic type mapping
410
    name: str                    # -> VARCHAR/TEXT
411
    age: Optional[int] = None    # -> INTEGER, nullable
412
    is_active: bool = True       # -> BOOLEAN
413
    created_at: datetime         # -> DATETIME/TIMESTAMP
414
    
415
    # Explicit type specification
416
    description: str = Field(sa_type=Text)           # Force TEXT type
417
    precise_value: Decimal = Field(sa_type=Numeric(10, 4))  # Exact decimal
418
    binary_data: bytes = Field(sa_type=LargeBinary)  # Binary storage
419
    
420
    # Custom type with validation
421
    email: str = Field(
422
        sa_type=String(320),  # Max email length
423
        regex=r'^[^@]+@[^@]+\.[^@]+$'  # Email validation
424
    )
425
```
426

427
### Database-Specific Considerations
428

429
Different databases have varying type support and behavior:
430

431
```python
432
# PostgreSQL-specific types
433
from sqlalchemy.dialects.postgresql import UUID as PG_UUID, JSONB, ARRAY
434

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

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