0
# Migration Operations
1

2
Schema modification operations available in migration scripts through the `alembic.op` module. These operations provide a high-level interface for database schema changes including tables, columns, constraints, indexes, and data manipulation.
3

4
## Core Imports
5

6
```python
7
from alembic import op
8
import sqlalchemy as sa
9
```
10

11
## Capabilities
12

13
### Table Operations
14

15
Create, drop, and modify database tables.
16

17
```python { .api }
18
def create_table(table_name, *columns, if_not_exists=None, **kw):
19
    """
20
    Create a new table.
21
    
22
    Args:
23
        table_name (str): Name of the table to create
24
        *columns: SQLAlchemy Column objects
25
        if_not_exists (bool): Add IF NOT EXISTS clause (v1.16.0+)
26
        **kw: Additional table options (schema, mysql_engine, etc.)
27
    
28
    Returns:
29
        Table: SQLAlchemy Table object
30
    """
31

32
def drop_table(table_name, if_exists=None, **kw):
33
    """
34
    Drop an existing table.
35
    
36
    Args:
37
        table_name (str): Name of the table to drop
38
        if_exists (bool): Add IF EXISTS clause (v1.16.0+)
39
        **kw: Additional options (schema, etc.)
40
        
41
    Returns:
42
        None
43
    """
44

45
def rename_table(old_table_name, new_table_name, schema=None):
46
    """
47
    Rename a table.
48
    
49
    Args:
50
        old_table_name (str): Current table name
51
        new_table_name (str): New table name
52
        schema (str): Schema name if applicable
53
        
54
    Returns:
55
        None
56
    """
57
```
58

59
**Usage Examples**:
60
```python
61
# Create table with columns
62
op.create_table('user',
63
    sa.Column('id', sa.Integer, primary_key=True),
64
    sa.Column('name', sa.String(50), nullable=False),
65
    sa.Column('email', sa.String(120), unique=True),
66
    sa.Column('created_at', sa.DateTime, default=sa.func.now())
67
)
68

69
# Drop table
70
op.drop_table('old_table')
71

72
# Rename table
73
op.rename_table('user', 'users')
74
```
75

76
### Column Operations
77

78
Add, remove, and modify table columns.
79

80
```python { .api }
81
def add_column(table_name, column, if_not_exists=None, **kw):
82
    """
83
    Add a column to an existing table.
84
    
85
    Args:
86
        table_name (str): Name of the table
87
        column (Column): SQLAlchemy Column object to add
88
        if_not_exists (bool): Add IF NOT EXISTS clause (v1.16.0+)
89
        **kw: Additional options (schema, etc.)
90
    """
91

92
def drop_column(table_name, column_name, **kw):
93
    """
94
    Drop a column from a table.
95
    
96
    Args:
97
        table_name (str): Name of the table
98
        column_name (str): Name of the column to drop
99
        **kw: Additional options (schema, etc.)
100
    """
101

102
def alter_column(table_name, column_name, nullable=None, comment=False, server_default=False, new_column_name=None, type_=None, **kw):
103
    """
104
    Alter properties of an existing column.
105
    
106
    Args:
107
        table_name (str): Name of the table
108
        column_name (str): Name of the column to alter
109
        nullable (bool): Change nullable constraint
110
        comment (str|False): Change column comment (False=no change, None=remove)
111
        server_default (str|False): Change server default (False=no change, None=remove)
112
        new_column_name (str): Rename the column
113
        type_ (TypeEngine): Change column type
114
        **kw: Additional options (schema, etc.)
115
    """
116
```
117

118
**Usage Examples**:
119
```python
120
# Add column
121
op.add_column('user', sa.Column('phone', sa.String(20)))
122

123
# Drop column
124
op.drop_column('user', 'phone')
125

126
# Alter column - change type and nullable
127
op.alter_column('user', 'email', type_=sa.String(200), nullable=True)
128

129
# Rename column
130
op.alter_column('user', 'name', new_column_name='full_name')
131

132
# Change server default
133
op.alter_column('user', 'created_at', server_default=sa.text('CURRENT_TIMESTAMP'))
134
```
135

136
### Constraint Operations
137

138
Create and drop various types of database constraints.
139

140
```python { .api }
141
def create_primary_key(constraint_name, table_name, columns, schema=None):
142
    """
143
    Create a primary key constraint.
144
    
145
    Args:
146
        constraint_name (str): Name of the constraint
147
        table_name (str): Name of the table
148
        columns (list): List of column names
149
        schema (str): Schema name if applicable
150
        
151
    Returns:
152
        None
153
    """
154

155
def create_foreign_key(constraint_name, source_table, referent_table, local_cols, remote_cols, onupdate=None, ondelete=None, deferrable=None, initially=None, match=None, source_schema=None, referent_schema=None, **dialect_kw):
156
    """
157
    Create a foreign key constraint.
158
    
159
    Args:
160
        constraint_name (str): Name of the constraint
161
        source_table (str): Source table name
162
        referent_table (str): Referenced table name
163
        local_cols (list): Local column names
164
        remote_cols (list): Referenced column names
165
        onupdate (str): ON UPDATE action
166
        ondelete (str): ON DELETE action
167
        deferrable (bool): Whether constraint is deferrable
168
        initially (str): Initial constraint state ('DEFERRED' or 'IMMEDIATE')
169
        match (str): MATCH clause ('FULL', 'PARTIAL', 'SIMPLE')
170
        source_schema (str): Source table schema
171
        referent_schema (str): Referenced table schema
172
        **dialect_kw: Additional dialect-specific options
173
        
174
    Returns:
175
        None
176
    """
177

178
def create_check_constraint(constraint_name, table_name, condition, schema=None, **kw):
179
    """
180
    Create a check constraint.
181
    
182
    Args:
183
        constraint_name (str): Name of the constraint
184
        table_name (str): Name of the table
185
        condition (str|ClauseElement): Check condition
186
        schema (str): Schema name if applicable
187
        **kw: Additional options
188
        
189
    Returns:
190
        None
191
    """
192

193
def create_unique_constraint(constraint_name, table_name, columns, schema=None, **kw):
194
    """
195
    Create a unique constraint.
196
    
197
    Args:
198
        constraint_name (str): Name of the constraint
199
        table_name (str): Name of the table
200
        columns (list): List of column names
201
        schema (str): Schema name if applicable
202
        **kw: Additional options
203
        
204
    Returns:
205
        Any: Constraint object
206
    """
207

208
def create_exclude_constraint(constraint_name, table_name, *elements, where=None, schema=None, deferrable=None, initially=None, using=None, **kw):
209
    """
210
    Create an exclude constraint (PostgreSQL).
211
    
212
    Args:
213
        constraint_name (str): Name of the constraint
214
        table_name (str): Name of the table
215
        *elements: Column/expression and operator pairs
216
        where (str): WHERE clause for partial constraint
217
        schema (str): Schema name if applicable
218
        deferrable (bool): Whether constraint is deferrable
219
        initially (str): Initial constraint checking mode
220
        using (str): Index method to use
221
        **kw: Additional options
222
        
223
    Returns:
224
        Optional[Table]: Table object (if created)
225
    """
226

227
def drop_constraint(constraint_name, table_name, type_=None, if_exists=None, schema=None):
228
    """
229
    Drop a constraint.
230
    
231
    Args:
232
        constraint_name (str): Name of the constraint to drop
233
        table_name (str): Name of the table
234
        type_ (str): Constraint type ('foreignkey', 'primary', 'unique', 'check')
235
        if_exists (bool): Add IF EXISTS clause (v1.16.0+)
236
        schema (str): Schema name if applicable
237
        
238
    Returns:
239
        None
240
    """
241
```
242

243
**Usage Examples**:
244
```python
245
# Create primary key
246
op.create_primary_key('pk_user', 'user', ['id'])
247

248
# Create foreign key
249
op.create_foreign_key('fk_post_user', 'post', 'user', ['user_id'], ['id'], ondelete='CASCADE')
250

251
# Create unique constraint
252
op.create_unique_constraint('uq_user_email', 'user', ['email'])
253

254
# Create check constraint
255
op.create_check_constraint('ck_user_age', 'user', 'age >= 0')
256

257
# Drop constraint
258
op.drop_constraint('fk_old_constraint', 'table_name', type_='foreignkey')
259
```
260

261
### Index Operations
262

263
Create and drop database indexes for query optimization.
264

265
```python { .api }
266
def create_index(index_name, table_name, columns, schema=None, unique=False, if_not_exists=None, **kw):
267
    """
268
    Create an index.
269
    
270
    Args:
271
        index_name (str): Name of the index
272
        table_name (str): Name of the table
273
        columns (list): List of column names or expressions
274
        schema (str): Schema name if applicable
275
        unique (bool): Whether index should be unique
276
        if_not_exists (bool): Add IF NOT EXISTS clause (v1.12.0+)
277
        **kw: Database-specific index options
278
        
279
    Returns:
280
        None
281
    """
282

283
def drop_index(index_name, table_name=None, schema=None, if_exists=None, **kw):
284
    """
285
    Drop an index.
286
    
287
    Args:
288
        index_name (str): Name of the index to drop
289
        table_name (str): Name of the table (optional, some databases require)
290
        schema (str): Schema name if applicable
291
        if_exists (bool): Add IF EXISTS clause (v1.12.0+)
292
        **kw: Additional options
293
        
294
    Returns:
295
        None
296
    """
297
```
298

299
**Usage Examples**:
300
```python
301
# Create simple index
302
op.create_index('ix_user_email', 'user', ['email'])
303

304
# Create unique index
305
op.create_index('ix_user_username', 'user', ['username'], unique=True)
306

307
# Create composite index
308
op.create_index('ix_post_user_date', 'post', ['user_id', 'created_at'])
309

310
# Drop index
311
op.drop_index('ix_old_index', 'user')
312
```
313

314
### Data Operations
315

316
Execute SQL statements and perform bulk data operations.
317

318
```python { .api }
319
def execute(sqltext, execution_options=None):
320
    """
321
    Execute arbitrary SQL.
322
    
323
    Args:
324
        sqltext (str|ClauseElement): SQL statement to execute
325
        execution_options (dict): Execution options
326
    """
327

328
def bulk_insert(table, rows, multiinsert=True):
329
    """
330
    Perform bulk insert operation.
331
    
332
    Args:
333
        table (Table|str): Target table
334
        rows (list): List of dictionaries representing rows
335
        multiinsert (bool): Use multi-row INSERT statements
336
    """
337

338
def inline_literal(value):
339
    """
340
    Create an inline literal value for SQL generation.
341
    
342
    Args:
343
        value: Python value to convert to SQL literal
344
    
345
    Returns:
346
        Literal value for inline SQL
347
    """
348
```
349

350
**Usage Examples**:
351
```python
352
# Execute raw SQL
353
op.execute("UPDATE user SET status = 'active' WHERE created_at > '2023-01-01'")
354

355
# Bulk insert data
356
user_table = sa.table('user',
357
    sa.column('name'),
358
    sa.column('email')
359
)
360
op.bulk_insert(user_table, [
361
    {'name': 'John', 'email': 'john@example.com'},
362
    {'name': 'Jane', 'email': 'jane@example.com'}
363
])
364

365
# Use inline literal
366
op.execute(f"INSERT INTO config (key, value) VALUES ('version', {op.inline_literal('1.0')})")
367
```
368

369
### Batch Operations
370

371
Special operations for databases with limited ALTER support (particularly SQLite).
372

373
```python { .api }
374
def batch_alter_table(table_name, schema=None, recreate='auto', copy_from=None, table_args=(), table_kwargs=None, reflect_args=(), reflect_kwargs=None, naming_convention=None):
375
    """
376
    Context manager for batch table alterations.
377
    
378
    Args:
379
        table_name (str): Name of the table to alter
380
        schema (str): Schema name if applicable
381
        recreate (str): Recreation strategy ('auto', 'always', 'never')
382
        copy_from (Table): Source table to copy from
383
        table_args: Arguments for new table
384
        table_kwargs: Keyword arguments for new table
385
        reflect_args: Arguments for table reflection
386
        reflect_kwargs: Keyword arguments for table reflection
387
        naming_convention (dict): Naming convention for constraints
388
    
389
    Returns:
390
        BatchOperations: Context manager for batch operations
391
    """
392
```
393

394
**Usage Examples**:
395
```python
396
# Batch alter table for SQLite compatibility
397
with op.batch_alter_table('user') as batch_op:
398
    batch_op.add_column(sa.Column('phone', sa.String(20)))
399
    batch_op.alter_column('email', nullable=True)
400
    batch_op.create_unique_constraint('uq_user_phone', ['phone'])
401
    batch_op.drop_column('old_field')
402
```
403

404
### Table Comments
405

406
Add and remove table-level comments.
407

408
```python { .api }
409
def create_table_comment(table_name, comment, schema=None):
410
    """
411
    Create a table comment.
412
    
413
    Args:
414
        table_name (str): Name of the table
415
        comment (str): Comment text
416
        schema (str): Schema name if applicable
417
    """
418

419
def drop_table_comment(table_name, schema=None):
420
    """
421
    Drop a table comment.
422
    
423
    Args:
424
        table_name (str): Name of the table
425
        schema (str): Schema name if applicable
426
    """
427
```
428

429
### Advanced Operations
430

431
Advanced operation management and customization.
432

433
```python { .api }
434
def get_bind():
435
    """
436
    Get the current database connection.
437
    
438
    Returns:
439
        Connection: SQLAlchemy connection object
440
    """
441

442
def get_context():
443
    """
444
    Get the current migration context.
445
    
446
    Returns:
447
        MigrationContext: Current migration context
448
    """
449

450
def f(name):
451
    """
452
    Mark a name as having a naming convention applied.
453
    
454
    Args:
455
        name (str): Name to mark for naming convention processing
456
        
457
    Returns:
458
        conv: Naming convention placeholder
459
    """
460

461
def run_async(async_function, *args, **kw_args):
462
    """
463
    Run an async function in migration context (v1.11+).
464
    
465
    Args:
466
        async_function: Async function to execute
467
        *args: Positional arguments
468
        **kw_args: Keyword arguments
469
        
470
    Returns:
471
        Any: Function result
472
    """
473

474
def invoke(operation):
475
    """
476
    Invoke a migration operation programmatically.
477
    
478
    Args:
479
        operation: Operation object to invoke
480
        
481
    Returns:
482
        Any: Operation result
483
    """
484

485
def implementation_for(op_cls):
486
    """
487
    Register an implementation for a custom operation class.
488
    
489
    Args:
490
        op_cls: Operation class to register implementation for
491
        
492
    Returns:
493
        Callable: Decorator function
494
    """
495

496
def register_operation(name, sourcename=None):
497
    """
498
    Register a new custom operation.
499
    
500
    Args:
501
        name (str): Name of the operation
502
        sourcename (str): Source module name
503
        
504
    Returns:
505
        Callable: Decorator function
506
    """
507

508
def invoke(operation):
509
    """
510
    Invoke a migration operation.
511
    
512
    Args:
513
        operation (MigrateOperation): Operation to invoke
514
    """
515

516
def f(name):
517
    """
518
    Create a naming convention reference.
519
    
520
    Args:
521
        name (str): Template name
522
    
523
    Returns:
524
        str: Formatted name according to naming convention
525
    """
526

527
def implementation_for(op_cls):
528
    """
529
    Register operation implementation for custom operations.
530
    
531
    Args:
532
        op_cls: Operation class to register implementation for
533
    
534
    Returns:
535
        Decorator function for implementation registration
536
    """
537

538
def register_operation(name, operation_class):
539
    """
540
    Register a custom operation.
541
    
542
    Args:
543
        name (str): Operation name
544
        operation_class: Custom operation class
545
    """
546

547
def run_async(coro):
548
    """
549
    Run async operation within migration context.
550
    
551
    Args:
552
        coro: Coroutine to execute
553
    
554
    Returns:
555
        Result of coroutine execution
556
    """
557
```
558

559
## Operation Context
560

561
All operations are executed within a migration context that provides:
562

563
- Database connection management
564
- Transaction handling
565
- SQL dialect-specific behavior
566
- Offline SQL generation mode
567
- Custom operation implementations
568

569
## Database-Specific Considerations
570

571
### PostgreSQL
572
- Supports most operations natively
573
- Concurrent index creation: `postgresql_concurrently=True`
574
- Array and JSONB column types supported
575

576
### MySQL
577
- Limited foreign key support in some versions
578
- Engine-specific options: `mysql_engine='InnoDB'`
579
- Charset and collation options available
580

581
### SQLite
582
- Limited ALTER TABLE support
583
- Use `batch_alter_table()` for complex changes
584
- Foreign key constraints require special handling
585

586
### SQL Server
587
- Schema-qualified names supported
588
- Identity column considerations
589
- Specific index options available
590

591
## Error Handling
592

593
Operations may raise:
594
- `OperationalError`: Database-level errors
595
- `ProgrammingError`: SQL syntax or logic errors
596
- `IntegrityError`: Constraint violations
597
- `CommandError`: Alembic-specific operation errors
598

599
## Types
600

601
```python { .api }
602
# Operation result types
603
class Table:
604
    name: str
605
    schema: Optional[str]
606
    columns: List[Column]
607

608
class BatchOperations:
609
    def add_column(self, column): ...
610
    def drop_column(self, column_name): ...
611
    def alter_column(self, column_name, **kw): ...
612
    def create_index(self, index_name, columns, **kw): ...
613
    def create_unique_constraint(self, constraint_name, columns): ...
614
    def create_foreign_key(self, constraint_name, referent_table, local_cols, remote_cols): ...
615
    def drop_constraint(self, constraint_name): ...
616
```