0
# Schema Definition
1
2
SQLModel re-exports SQLAlchemy's comprehensive schema definition system, providing all the tools needed to define database schemas including tables, columns, constraints, and indexes. These components work seamlessly with SQLModel's declarative model system.
3
4
## Capabilities
5
6
### Metadata and Schema Management
7
8
Core classes for managing database schema metadata.
9
10
```python { .api }
11
class MetaData:
12
"""
13
Collection of table definitions and schema information.
14
15
Serves as a registry for all table definitions and provides
16
methods for creating/dropping tables and managing schema.
17
"""
18
19
def create_all(self, bind: Engine, checkfirst: bool = True) -> None:
20
"""Create all tables defined in this metadata."""
21
22
def drop_all(self, bind: Engine, checkfirst: bool = True) -> None:
23
"""Drop all tables defined in this metadata."""
24
25
def reflect(self, bind: Engine, schema: Optional[str] = None) -> None:
26
"""Load table definitions from existing database."""
27
28
class Table:
29
"""
30
Represents a database table with columns and constraints.
31
32
Usually not used directly with SQLModel, as table definitions
33
are created automatically from SQLModel class definitions.
34
"""
35
36
BLANK_SCHEMA: str
37
"""Constant representing a blank schema name."""
38
```
39
40
**Usage Examples:**
41
```python
42
# Metadata is automatically created by SQLModel
43
from sqlmodel import SQLModel
44
45
# Access the shared metadata
46
metadata = SQLModel.metadata
47
48
# Create all tables
49
metadata.create_all(engine)
50
51
# Drop all tables
52
metadata.drop_all(engine)
53
54
# Reflect existing database schema
55
metadata.reflect(engine)
56
for table_name in metadata.tables:
57
print(f"Table: {table_name}")
58
```
59
60
### Column Definition
61
62
Classes for defining table columns with various properties and constraints.
63
64
```python { .api }
65
class Column:
66
"""
67
Represents a database table column.
68
69
Usually not used directly with SQLModel, as columns are
70
defined through Field() specifications in model classes.
71
"""
72
73
class ColumnDefault:
74
"""Represents a default value for a column."""
75
76
class DefaultClause:
77
"""SQL expression used as a column default."""
78
79
class Computed:
80
"""
81
Computed column expression (calculated by database).
82
83
Represents columns whose values are computed by the database
84
based on other columns or expressions.
85
"""
86
87
class FetchedValue:
88
"""
89
Marker for values that are fetched after INSERT/UPDATE.
90
91
Used for columns that have their values generated by
92
the database (like auto-incrementing IDs or timestamps).
93
"""
94
95
class Identity:
96
"""
97
Identity column specification for auto-incrementing values.
98
99
Modern alternative to sequences, supported by newer databases.
100
"""
101
```
102
103
**Usage Examples:**
104
```python
105
# These are typically used indirectly through Field() in SQLModel
106
class Hero(SQLModel, table=True):
107
# Primary key with auto-increment
108
id: Optional[int] = Field(default=None, primary_key=True)
109
110
# Column with computed value (database-calculated)
111
full_name: Optional[str] = Field(
112
sa_column=Column(String, Computed("first_name || ' ' || last_name"))
113
)
114
115
# Column with default value
116
created_at: datetime = Field(
117
default_factory=datetime.utcnow,
118
sa_column_kwargs={"server_default": func.now()}
119
)
120
```
121
122
### Constraints
123
124
Classes for defining various database constraints to enforce data integrity.
125
126
```python { .api }
127
class Constraint:
128
"""Base class for all database constraints."""
129
130
class CheckConstraint(Constraint):
131
"""
132
Check constraint that enforces a boolean condition.
133
134
Ensures that all rows satisfy a specified condition.
135
"""
136
137
class ForeignKey:
138
"""
139
Foreign key reference to another table's column.
140
141
Creates a link between tables and enforces referential integrity.
142
"""
143
144
class ForeignKeyConstraint(Constraint):
145
"""
146
Multi-column foreign key constraint.
147
148
References multiple columns in another table.
149
"""
150
151
class PrimaryKeyConstraint(Constraint):
152
"""
153
Primary key constraint for one or more columns.
154
155
Ensures uniqueness and non-null values for primary key columns.
156
"""
157
158
class UniqueConstraint(Constraint):
159
"""
160
Unique constraint ensuring distinct values.
161
162
Prevents duplicate values across specified columns.
163
"""
164
```
165
166
**Usage Examples:**
167
```python
168
# Foreign key relationships
169
class Team(SQLModel, table=True):
170
id: Optional[int] = Field(default=None, primary_key=True)
171
name: str = Field(unique=True) # Unique constraint
172
173
class Hero(SQLModel, table=True):
174
id: Optional[int] = Field(default=None, primary_key=True)
175
name: str
176
team_id: Optional[int] = Field(
177
default=None,
178
foreign_key="team.id" # Foreign key constraint
179
)
180
181
# Check constraint (using sa_column_kwargs)
182
age: Optional[int] = Field(
183
default=None,
184
sa_column_kwargs={
185
"check": "age >= 0 AND age <= 200" # Age must be reasonable
186
}
187
)
188
189
# Composite unique constraint (table-level)
190
class UserProfile(SQLModel, table=True):
191
__table_args__ = (
192
UniqueConstraint("user_id", "profile_type", name="unique_user_profile"),
193
)
194
195
id: Optional[int] = Field(default=None, primary_key=True)
196
user_id: int
197
profile_type: str
198
data: dict
199
```
200
201
### Indexes
202
203
Index definition for improving query performance.
204
205
```python { .api }
206
class Index:
207
"""
208
Database index for improving query performance.
209
210
Can be created on one or more columns to speed up
211
searches and sorting operations.
212
"""
213
```
214
215
**Usage Examples:**
216
```python
217
# Single column index
218
class Hero(SQLModel, table=True):
219
id: Optional[int] = Field(default=None, primary_key=True)
220
name: str = Field(index=True) # Creates index on name column
221
email: str = Field(unique=True) # Unique constraint also creates index
222
223
# Composite index (table-level)
224
class SearchLog(SQLModel, table=True):
225
__table_args__ = (
226
Index("idx_user_timestamp", "user_id", "timestamp"),
227
)
228
229
id: Optional[int] = Field(default=None, primary_key=True)
230
user_id: int
231
search_term: str
232
timestamp: datetime
233
```
234
235
### Sequences
236
237
Sequence objects for generating unique numeric values.
238
239
```python { .api }
240
class Sequence:
241
"""
242
Database sequence for generating unique numeric values.
243
244
Used primarily for auto-incrementing primary keys
245
in databases that support sequences (PostgreSQL, Oracle).
246
"""
247
```
248
249
**Usage Examples:**
250
```python
251
# Explicit sequence usage (advanced)
252
hero_id_seq = Sequence('hero_id_seq', start=1000)
253
254
class Hero(SQLModel, table=True):
255
id: Optional[int] = Field(
256
default=None,
257
primary_key=True,
258
sa_column_kwargs={"server_default": hero_id_seq.next_value()}
259
)
260
name: str
261
```
262
263
### DDL and Schema Operations
264
265
Data Definition Language support for schema operations.
266
267
```python { .api }
268
class DDL:
269
"""
270
Raw DDL (Data Definition Language) statement.
271
272
Allows execution of custom SQL DDL commands
273
for advanced schema operations.
274
"""
275
```
276
277
**Usage Examples:**
278
```python
279
# Custom DDL operations
280
from sqlalchemy import event, DDL
281
282
# Execute custom SQL after table creation
283
custom_ddl = DDL("""
284
CREATE TRIGGER update_hero_timestamp
285
BEFORE UPDATE ON hero
286
FOR EACH ROW
287
EXECUTE FUNCTION update_timestamp()
288
""")
289
290
# Attach DDL to table creation
291
event.listen(Hero.__table__, 'after_create', custom_ddl)
292
```
293
294
### Advanced Schema Patterns
295
296
**Table Inheritance:**
297
```python
298
# Single table inheritance
299
class Person(SQLModel, table=True):
300
id: Optional[int] = Field(default=None, primary_key=True)
301
name: str
302
type: str = Field() # Discriminator column
303
304
__mapper_args__ = {
305
"polymorphic_identity": "person",
306
"polymorphic_on": "type"
307
}
308
309
class Hero(Person, table=False): # Inherits from Person table
310
secret_name: Optional[str] = None
311
312
__mapper_args__ = {
313
"polymorphic_identity": "hero"
314
}
315
```
316
317
**Composite Primary Keys:**
318
```python
319
class HeroTeamAssignment(SQLModel, table=True):
320
hero_id: int = Field(foreign_key="hero.id", primary_key=True)
321
team_id: int = Field(foreign_key="team.id", primary_key=True)
322
role: str
323
start_date: date
324
```
325
326
**Schema-Qualified Tables:**
327
```python
328
class AuditLog(SQLModel, table=True):
329
__tablename__ = "audit_log"
330
__table_args__ = {"schema": "audit"}
331
332
id: Optional[int] = Field(default=None, primary_key=True)
333
table_name: str
334
operation: str
335
timestamp: datetime
336
user_id: int
337
```
338
339
### Integration with SQLModel Metadata
340
341
SQLModel automatically manages schema creation through its metadata system:
342
343
```python
344
# All SQLModel classes share the same metadata
345
assert Hero.metadata is SQLModel.metadata
346
assert Team.metadata is SQLModel.metadata
347
348
# Create all tables at once
349
SQLModel.metadata.create_all(engine)
350
351
# Drop all tables
352
SQLModel.metadata.drop_all(engine)
353
354
# Get information about defined tables
355
for table_name, table in SQLModel.metadata.tables.items():
356
print(f"Table: {table_name}")
357
for column in table.columns:
358
print(f" Column: {column.name} ({column.type})")
359
```