0
# SQLModel
1
2
SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It is designed to be intuitive, easy to use, highly compatible, and robust. SQLModel is based on Python type annotations, and powered by Pydantic and SQLAlchemy.
3
4
The key innovation of SQLModel is that it allows you to define a single class that serves as both a SQLAlchemy table model and a Pydantic validation model, eliminating code duplication and ensuring consistency across database operations and API interfaces.
5
6
## Package Information
7
8
- **Package Name**: sqlmodel
9
- **Package Type**: pypi
10
- **Language**: Python
11
- **Installation**: `pip install sqlmodel`
12
- **Dependencies**: SQLAlchemy >=2.0.14,<2.1.0, pydantic >=1.10.13,<3.0.0
13
14
## Core Imports
15
16
```python
17
from sqlmodel import SQLModel, Field, Relationship, Session, create_engine, select
18
```
19
20
Additional commonly used imports:
21
22
```python
23
# SQL operations and expressions
24
from sqlmodel import and_, or_, not_, func, text, Column, Index, ForeignKey
25
26
# Data types
27
from sqlmodel import String, Integer, Boolean, DateTime, Float, Text
28
29
# Engine and connection management
30
from sqlmodel import create_mock_engine, MetaData, inspect
31
```
32
33
## Basic Usage
34
35
```python
36
from typing import Optional
37
from sqlmodel import Field, Session, SQLModel, create_engine, select
38
39
# Define a model that works as both SQLAlchemy table and Pydantic model
40
class Hero(SQLModel, table=True):
41
id: Optional[int] = Field(default=None, primary_key=True)
42
name: str
43
secret_name: str
44
age: Optional[int] = None
45
46
# Create database engine and tables
47
engine = create_engine("sqlite:///database.db")
48
SQLModel.metadata.create_all(engine)
49
50
# Create and save data
51
hero = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
52
with Session(engine) as session:
53
session.add(hero)
54
session.commit()
55
56
# Query data with type safety
57
with Session(engine) as session:
58
statement = select(Hero).where(Hero.name == "Spider-Boy")
59
result = session.exec(statement).first()
60
print(result) # Hero object with full type support
61
```
62
63
## Architecture
64
65
SQLModel combines three key technologies:
66
67
- **Pydantic**: Provides data validation, serialization, and JSON schema generation
68
- **SQLAlchemy**: Handles database operations, ORM functionality, and SQL generation
69
- **Type Annotations**: Enables editor support with autocompletion and type checking
70
71
The SQLModel class serves as a bridge between these systems, allowing a single model definition to work seamlessly with both database operations and API serialization, particularly in FastAPI applications.
72
73
## Capabilities
74
75
### Model Definition
76
77
Core functionality for defining database models that work with both SQLAlchemy and Pydantic, including field definitions and relationships.
78
79
```python { .api }
80
class SQLModel(BaseModel, metaclass=SQLModelMetaclass): ...
81
82
def Field(
83
default: Any = ...,
84
*,
85
primary_key: bool = False,
86
foreign_key: Optional[str] = None,
87
unique: bool = False,
88
nullable: Optional[bool] = None,
89
index: Optional[bool] = None,
90
**kwargs
91
) -> Any: ...
92
93
def Relationship(
94
*,
95
back_populates: Optional[str] = None,
96
cascade_delete: bool = False,
97
**kwargs
98
) -> Any: ...
99
```
100
101
[Model Definition](./model-definition.md)
102
103
### Session Management
104
105
Database session handling with enhanced typing support for better type inference with SQLModel objects.
106
107
```python { .api }
108
class Session(SQLAlchemySession):
109
def exec(self, statement: Select[T]) -> Result[T]: ...
110
```
111
112
[Session Management](./session-management.md)
113
114
### SQL Operations
115
116
Query building and execution with type-safe SELECT statements and SQL expression functions.
117
118
```python { .api }
119
def select(*entities) -> Union[Select, SelectOfScalar]: ...
120
121
def and_(*clauses) -> BooleanClauseList: ...
122
def or_(*clauses) -> BooleanClauseList: ...
123
def not_(clause) -> UnaryExpression: ...
124
```
125
126
[SQL Operations](./sql-operations.md)
127
128
### Database Engine and Connection
129
130
Database engine creation and connection management, re-exported from SQLAlchemy with full compatibility.
131
132
```python { .api }
133
def create_engine(url: str, **kwargs) -> Engine: ...
134
def create_mock_engine(dialect: str, executor: Callable) -> MockConnection: ...
135
```
136
137
[Database Engine](./database-engine.md)
138
139
### Schema Definition
140
141
Database schema components including tables, columns, constraints, and indexes, providing full SQLAlchemy schema definition capabilities.
142
143
```python { .api }
144
class MetaData: ...
145
class Table: ...
146
class Column: ...
147
class Index: ...
148
class ForeignKey: ...
149
```
150
151
[Schema Definition](./schema-definition.md)
152
153
### Data Types
154
155
Comprehensive SQL data type system with Python type mapping, including both SQLAlchemy standard types and SQLModel extensions.
156
157
```python { .api }
158
class AutoString(TypeDecorator): ...
159
160
# Standard SQLAlchemy types
161
Integer: TypeEngine
162
String: TypeEngine
163
DateTime: TypeEngine
164
Boolean: TypeEngine
165
```
166
167
[Data Types](./data-types.md)
168
169
## Types
170
171
```python { .api }
172
class FieldInfo(PydanticFieldInfo):
173
primary_key: bool
174
nullable: Optional[bool]
175
foreign_key: Any
176
unique: bool
177
index: Optional[bool]
178
sa_type: Any
179
sa_column: Optional[Column]
180
181
class RelationshipInfo:
182
back_populates: Optional[str]
183
cascade_delete: bool
184
passive_deletes: Union[bool, Literal["all"]]
185
link_model: Optional[Any]
186
187
class SQLModelMetaclass(ModelMetaclass, DeclarativeMeta): ...
188
```