Alembic

https://github.com/sqlalchemy/alembic
Admin
A database migrations tool for SQLAlchemy.
Tokens:130,893
Snippets:1,017
Trust Score:-
License:MIT
Update:1 week ago
Show doc for...
Tokens:
Raw
### Publish plugin with pyproject.toml entry point

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/plugins.rst

Declare the plugin entry point in pyproject.toml so the plugin can be installed via pip. The value is the module containing the setup() function.

```toml
[project.entry-points."alembic.plugins"]
mycompany.plugin_name = "mycompany.alembic_plugin"
```

--------------------------------

### Full example: produce_migrations and invoke operations

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Complete example that sets up an engine, creates tables, defines metadata, produces migrations with produce_migrations, and invokes the operations directly. It includes the traversal logic from the previous snippet and is based on the example at autogenerate.compare_metadata.

```python
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table

from alembic.autogenerate import produce_migrations
from alembic.migration import MigrationContext
from alembic.operations import Operations
from alembic.operations.ops import ModifyTableOps


engine = create_engine("sqlite://", echo=True)

with engine.connect() as conn:
    conn.execute(
        """
        create table foo (
            id integer not null primary key,
            old_data varchar(50),
            x integer
        )"""
    )

    conn.execute(
        """
        create table bar (
            data varchar(50)
        )"""
    )

metadata = MetaData()
Table(
    "foo",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("data", Integer),
    Column("x", Integer, nullable=False),
)
Table("bat", metadata, Column("info", String(100)))

mc = MigrationContext.configure(engine.connect())

migrations = produce_migrations(mc, metadata)

operations = Operations(mc)

use_batch = engine.name == "sqlite"

stack = [migrations.upgrade_ops]
while stack:
    elem = stack.pop(0)

    if use_batch and isinstance(elem, ModifyTableOps):
        with operations.batch_alter_table(
            elem.table_name, schema=elem.schema
        ) as batch_ops:
            for table_elem in elem.ops:
                batch_ops.invoke(table_elem)

    elif hasattr(elem, "ops"):
        stack.extend(elem.ops)
    else:
        operations.invoke(elem)
```

--------------------------------

### Install the local project in editable mode

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/front.rst

Installs the local project into the same virtual environment, optionally using editable mode. Assumes the project has a pyproject.toml or setup.py.

```bash
$ pip install -e .
```

--------------------------------

### Retrieve start version from local file in offline mode

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/offline.rst

Customize env.py to retrieve the starting version from a local file (version.txt) in offline mode. This treats the local file like the alembic_version table, allowing migrations to start from a previously recorded version.

```python
if context.is_offline_mode():
    version_file = os.path.join(os.path.dirname(config.config_file_name), "version.txt")
    if os.path.exists(version_file):
        current_version = open(version_file).read()
    else:
        current_version = None
    context.configure(dialect_name=engine.name, starting_rev=current_version)
    context.run_migrations()
    end_version = context.get_revision_argument()
    if end_version and end_version != current_version:
        open(version_file, 'w').write(end_version)
```

--------------------------------

### Define plugin setup() function

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/plugins.rst

Define the required setup() function in a plugin module. It receives a Plugin instance and is called when the plugin is loaded, either via entry points or manually via Plugin.setup_plugin_from_module. Use it to register custom operations and autogenerate comparators.

```python
from alembic import op
from alembic.operations import Operations
from alembic.runtime.plugins import Plugin
from alembic.util import DispatchPriority

def setup(plugin: Plugin) -> None:
    """Setup function called by Alembic when loading the plugin."""

    # Register custom operations
    Operations.register_operation("create_view")(CreateViewOp)
    Operations.implementation_for(CreateViewOp)(create_view_impl)

    # Register autogenerate comparison functions
    plugin.add_autogenerate_comparator(
        _compare_views,
        "view",
        qualifier="default",
        priority=DispatchPriority.MEDIUM,
    )
```

--------------------------------

### List available Alembic templates with alembic list_templates

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Run this command to list all available environment templates. The output shows the generic, pyproject, async, and multidb templates, along with a usage example for the init command.

```bash
$ alembic list_templates
```

--------------------------------

### Install Alembic with pip

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/front.rst

Installs Alembic into the active virtual environment, adding the alembic command.

```bash
$ pip install alembic
```

--------------------------------

### Example output of the URL generation script

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Shows the expected output when running the script with the given inputs. Demonstrates the escaped URL for make_url() and the percent-replaced URL for a ConfigParser file.

```text
$ python alembic_pw_script.py
database driver? postgresql+psycopg2
username? scott
password? P@ssw%rd
host? localhost
port? 5432
database? testdb
The correctly escaped string that can be passed to SQLAlchemy make_url() and create_engine() is:

    'postgresql+psycopg2://scott:P%40ssw%25rd@localhost:5432/testdb'

The SQLAlchemy URL that can be placed in a ConfigParser file such as alembic.ini is:

      sqlalchemy.url = postgresql+psycopg2://scott:P%%40ssw%%25rd@localhost:5432/testdb
```

--------------------------------

### Example alembic.ini configuration file

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

The all-in-one .ini file created by the generic template. It contains source code configuration and database configuration directives. Key options include script_location, prepend_sys_path, and commented examples for file_template, timezone, and version_locations.

```ini
# A generic, single database configuration.

[alembic]
# path to migration scripts.
# this is typically a path given in POSIX (e.g. forward slashes)
# format, relative to the token %(here)s which refers to the location of this
# ini file
script_location = %(here)s/alembic

# template used to generate migration file names; The default value is %%(rev)s_%%(slug)s
# Uncomment the line below if you want the files to be prepended with date and time
# file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d%%(minute).2d-%%(rev)s_%%(slug)s
# Or organize into date-based subdirectories (requires recursive_version_locations = true)
# file_template = %%(year)d/%%(month).2d/%%(day).2d_%%(hour).2d%%(minute).2d_%%(second).2d_%%(rev)s_%%(slug)s

# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .

# timezone to use when rendering the date within the migration file
# as well as the filename.
# If specified, requires the python>=3.9 or backports.zoneinfo library and tzdata library.
# Any required deps can installed by adding `alembic[tz]` to the pip requirements
# string value is passed to ZoneInfo()
# leave blank for localtime
# timezone =

# max length of characters to apply to the
# "slug" field
# truncate_slug_length = 40

# set to 'true' to run the environment during
# the 'revision' command, regardless of autogenerate
# revision_environment = false

# set to 'true' to allow .pyc and .pyo files without
# a source .py file to be detected as revisions in the
# versions/ directory
# sourceless = false

# version location specification; This defaults
# to <script_location>/versions.  When using multiple version
# directories, initial revisions must be specified with --version-path.
# the special token `%(here)s` is available which indicates the absolute path
# to this configuration file.
#
# The path separator used here should be the separator specified by "version_path_separator" below.
# version_locations = %(here)s/bar:%(here)s/bat:%(here)s/alembic/versions
```

--------------------------------

### Install a third-party Alembic plugin with pip

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/plugins.rst

Install a third-party Alembic plugin distributed as a Python package. Plugins using Python's entry point system are automatically discovered and loaded by Alembic at startup.

```bash
pip install mycompany-alembic-plugin
```

--------------------------------

### Run alembic upgrade head

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Command to run all migrations up to the head revision, with example output showing the upgrade progress.

```bash
$ alembic upgrade head
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running upgrade None -> 1975ea83b712
```

--------------------------------

### Generated migration file for added account table

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Example of a generated migration file for a new 'account' table. It includes upgrade() and downgrade() functions with op.create_table and op.drop_table, and must be reviewed and adjusted before running.

```python
"""empty message

Revision ID: 27c6a30d7c24
Revises: None
Create Date: 2011-11-08 11:40:27.089406

"""

# revision identifiers, used by Alembic.
revision = '27c6a30d7c24'
down_revision = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
    'account',
    sa.Column('id', sa.Integer()),
    sa.Column('name', sa.String(length=50), nullable=False),
    sa.Column('description', sa.VARCHAR(200)),
    sa.Column('last_transaction_date', sa.DateTime()),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("account")
    ### end Alembic commands ###
```

--------------------------------

### SQLite batch migration generated SQL

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/batch.rst

Example SQL generated by the batch context on SQLite for the previous batch_alter_table example. It shows the temporary table creation, data copy, drop, and rename steps.

```sql
CREATE TABLE _alembic_batch_temp (
  id INTEGER NOT NULL,
  foo INTEGER,
  PRIMARY KEY (id)
);
INSERT INTO _alembic_batch_temp (id) SELECT some_table.id FROM some_table;
DROP TABLE some_table;
ALTER TABLE _alembic_batch_temp RENAME TO some_table;
```

--------------------------------

### Generate SQL script with start:end version range

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/offline.rst

Specify a start:end version range in offline mode to generate a script starting from a particular revision. The start:end syntax is only allowed in offline mode; in online mode the alembic_version table is used.

```shell
$ alembic upgrade 1975ea83b712:ae1027a6acf --sql > migration.sql
```

--------------------------------

### Generated migration file with conditional data upgrades

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

An example migration file created from the modified template, showing the structure with schema_upgrades and data_upgrades functions that check for the 'data' argument.

```python
"""rev one

Revision ID: 3ba2b522d10d
Revises: None
Create Date: 2014-03-04 18:05:36.992867

"""

# revision identifiers, used by Alembic.
revision = '3ba2b522d10d'
down_revision = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy import String, Column
from sqlalchemy.sql import table, column

from alembic import context

def upgrade():
    schema_upgrades()
    if context.get_x_argument(as_dictionary=True).get('data', None):
        data_upgrades()
```

--------------------------------

### Autogenerated batch migration example

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/batch.rst

Example of autogenerated migration code when render_as_batch is enabled. The batch_alter_table directive is used by default only for SQLite; other backends behave normally.

```python
def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('address', schema=None) as batch_op:
        batch_op.add_column(sa.Column('street', sa.String(length=50), nullable=True))
```

--------------------------------

### Create a new branch revision with --branch-label and --version-path

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/branches.rst

Creates a new revision file in the 'model/networking' directory, starting a branch from 'base' with the label 'networking'. The directory is created automatically if it doesn't exist. Output shows the generated file path.

```bash
$ alembic revision -m "create networking branch" --head=base --branch-label=networking --version-path=model/networking
  Creating directory /path/to/foo/model/networking ... done
  Generating /path/to/foo/model/networking/3cac04ae8714_create_networking_branch.py ... done
```

--------------------------------

### Configure SQLAlchemy logging with pytest command line

Source: https://github.com/sqlalchemy/alembic/blob/main/README.unittests.rst

Command line example to direct SQLAlchemy log targets to the console using pytest flags. The -s flag ensures standard out is not suppressed.

```bash
$ ./pytest test/orm/test_unitofwork.py -s \
  --log-debug=sqlalchemy.pool --log-info=sqlalchemy.engine
```

--------------------------------

### Create ReplaceableObject instances for view and stored procedure

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Example usage of ReplaceableObject in a migration script, assuming PostgreSQL syntax. Shows a view and a stored procedure definition.

```python
customer_view = ReplaceableObject(
    "customer_view",
    "SELECT name, order_count FROM customer WHERE order_count > 0"
)

add_customer_sp = ReplaceableObject(
    "add_customer_sp(name varchar, order_count integer)",
    """
    RETURNS integer AS $$
    BEGIN
        insert into customer (name, order_count)
        VALUES (in_name, in_order_count);
    END;
    $$ LANGUAGE plpgsql;
    """
)
```

--------------------------------

### Add branch label to migration file

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/branches.rst

Example of adding a branch label 'shoppingcart' to the migration file 27c6a30d7c24_add_shopping_cart_table.py using the 'branch_labels' identifier.

```python
"""add shopping cart table

"""
```

--------------------------------

### Extend CommandLine with custom command 'frobnicate'

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Creates a custom Alembic CLI by instantiating CommandLine and registering a custom command function. The function must accept a Config object as the first argument and a docstring is recommended for help output. The example shows the full script and the resulting CLI help and command execution.

```python
# myalembic.py

from alembic.config import CommandLine, Config


def frobnicate(config: Config, revision: str) -> None:
    """Frobnicates according to the frobnication specification.

    :param config: a :class:`.Config` instance
    :param revision: the revision to frobnicate
    """

    config.print_stdout(f"Revision {revision} successfully frobnicated.")


def main():
    cli = CommandLine()
    cli.register_command(frobnicate)
    cli.main()

if __name__ == "__main__":
    main()
```

```text
$ python -m myalembic -h
...
positional arguments:
  {branches,check,current,downgrade,edit,ensure_version,heads,history,init,list_templates,merge,revision,show,stamp,upgrade,frobnicate}
    ...
    frobnicate          Frobnicates according to the frobnication specification.

$ python -m myalembic frobnicate -h
usage: myalembic.py frobnicate [-h] revision

positional arguments:
revision    revision identifier

optional arguments:
-h, --help  show this help message and exit

$ python -m myalembic frobnicate 42
Revision 42 successfully frobnicated.
```

--------------------------------

### Update env.py for async DBAPI with async_engine_from_config

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Replace run_migrations_online in env.py to use an async engine. The example shows the required imports and the async run function that uses async_engine_from_config and asyncio.run.

```python
import asyncio

from sqlalchemy.ext.asyncio import async_engine_from_config

# ... no change required to the rest of the code


def do_run_migrations(connection):
    context.configure(connection=connection, target_metadata=target_metadata)

    with context.begin_transaction():
        context.run_migrations()


async def run_async_migrations():
    """In this scenario we need to create an Engine
    and associate a connection with the context.

    """

    connectable = async_engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    async with connectable.connect() as connection:
        await connection.run_sync(do_run_migrations)

    await connectable.dispose()


def run_migrations_online():
    """Run migrations in 'online' mode."""

    asyncio.run(run_async_migrations())
```

--------------------------------

### Customize run_migrations_offline for multiple databases

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/offline.rst

Example of customizing run_migrations_offline() in env.py for a multiple database configuration. The configure function accepts an output_buffer parameter to direct migration output to different named files.

```python
from alembic import context
import myapp
import sys

db_1 = myapp.db_1
db_2 = myapp.db_2

def run_migrations_offline():
    """Run migrations *without* a SQL connection."""
```

--------------------------------

### Register custom operation with Operations.register_operation

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/plugins.rst

Registers a custom operation directive that becomes available as op.custom_operation() in migration scripts. Uses Operations.register_operation and Operations.implementation_for. The example is from the replaceable_objects recipe and can be performed in the plugin's setup() function to make the custom operation available globally.

```python
from alembic.operations import Operations, MigrateOperation

class CreateViewOp(MigrateOperation):
    def __init__(self, view_name, select_stmt):
        self.view_name = view_name
        self.select_stmt = select_stmt

@Operations.register_operation("create_view")
class CreateViewOp(CreateViewOp):
    pass

@Operations.implementation_for(CreateViewOp)
def create_view(operations, operation):
    operations.execute(
        f"CREATE VIEW {operation.view_name} AS {operation.select_stmt}"
    )
```

--------------------------------

### Initialize Alembic in a project

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/front.rst

Runs the alembic init command to set up Alembic within the project directory.

```bash
$ alembic init alembic
```

--------------------------------

### Initialize async Alembic environment with template 'async'

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Bootstrap a new Alembic environment that can use async DBAPIs like asyncpg. Run this command to create the script directory with the async template.

```bash
alembic init -t async <script_directory_here>
```

--------------------------------

### Create initial migration for customer table

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Shows the command to create a new migration and the resulting migration file that creates a 'customer' table with columns id, name, and order_count. The downgrade drops the table.

```bash
$ alembic revision -m "create table"
```

```python
"""create table

Revision ID: 3ab8b2dfb055
Revises:
Create Date: 2015-07-27 16:22:44.918507

"""

# revision identifiers, used by Alembic.
revision = '3ab8b2dfb055'
down_revision = None
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table(
        "customer",
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String),
        sa.Column('order_count', sa.Integer),
    )


def downgrade():
    op.drop_table('customer')
```

--------------------------------

### Create Alembic environment with alembic init

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Run these commands to create a new Alembic environment using the generic template. The commands assume a local virtualenv and create a migrations directory named 'alembic'.

```bash
$ cd /path/to/yourproject
$ source /path/to/yourproject/.venv/bin/activate   # assuming a local virtualenv
$ alembic init alembic
```

--------------------------------

### Manually register a plugin with Plugin.setup_plugin_from_module

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/plugins.rst

Manually register a plugin without using entry points, useful for project-specific plugins or during development. Place this in the env.py file.

```python
from alembic.runtime.plugins import Plugin
import myproject.alembic_plugin

# Register the plugin manually
Plugin.setup_plugin_from_module(
    myproject.alembic_plugin,
    "myproject.custom_operations"
)
```

--------------------------------

### Initialize Alembic with pyproject template

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Runs alembic init with the pyproject template to create an Alembic environment and augment an existing pyproject.toml file.

```bash
alembic init --template pyproject alembic
```

--------------------------------

### MySQL schema for users and user_properties tables

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Example database schema (MySQL) with two tables: users and user_properties. The user_properties table has a foreign key to users with ON DELETE CASCADE. This schema is used as input for the Python code generation example.

```sql
CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL,
    KEY `id` (`id`)
);

CREATE TABLE IF NOT EXISTS `user_properties` (
  `users_id` int(11) NOT NULL,
  `property_name` varchar(255) NOT NULL,
  `property_value` mediumtext NOT NULL,
  UNIQUE KEY `property_name_users_id` (`property_name`,`users_id`),
  KEY `users_id` (`users_id`),
  CONSTRAINT `user_properties_ibfk_1` FOREIGN KEY (`users_id`)
  REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

--------------------------------

### PostgreSQL: create HSTORE extension

Source: https://github.com/sqlalchemy/alembic/blob/main/README.unittests.rst

Connects to the test database and creates the HSTORE extension to include HSTORE tests. The output shows the expected connection and extension creation messages.

```sql
postgres=# \c test;
You are now connected to database "test" as user "postgresql".
test=# create extension hstore;
CREATE EXTENSION
```

--------------------------------

### Output of alembic init --template pyproject

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Shows the expected output when running the init command, including the files generated and the message to edit configuration files.

```text
Creating directory /path/to/yourproject/alembic...done
Creating directory /path/to/yourproject/alembic/versions...done
Appending to /path/to/yourproject/pyproject.toml...done
Generating /path/to/yourproject/alembic.ini...done
Generating /path/to/yourproject/alembic/env.py...done
Generating /path/to/yourproject/alembic/README...done
Generating /path/to/yourproject/alembic/script.py.mako...done
Please edit configuration/connection/logging settings in
'/path/to/yourproject/pyproject.toml' and
'/path/to/yourproject/alembic.ini' before proceeding.
```

--------------------------------

### Set tenant schema in env.py using -x flag

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

In env.py, use context.get_x_argument to read the tenant from -xtenant=some_schema. For PostgreSQL, set search_path and commit; for MySQL/MariaDB, use USE. Requires SQLAlchemy text import.

```python
from sqlalchemy import text

def run_migrations_online():
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    current_tenant = context.get_x_argument(as_dictionary=True).get("tenant")
    with connectable.connect() as connection:

        if connection.dialect.name == "postgresql":
            # set search path on the connection, which ensures that
            # PostgreSQL will emit all CREATE / ALTER / DROP statements
            # in terms of this schema by default

            connection.execute(text('set search_path to "%s"' % current_tenant))
            # in SQLAlchemy v2+ the search path change needs to be committed
            connection.commit()
        elif connection.dialect.name in ("mysql", "mariadb"):
            # set "USE" on the connection, which ensures that
            # MySQL/MariaDB will emit all CREATE / ALTER / DROP statements
            # in terms of this schema by default

            connection.execute(text('USE %s' % current_tenant))
```

--------------------------------

### Set SQLAlchemy URL in alembic.ini

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Sets the database connection URL in the alembic.ini file. This is the minimal configuration needed for a single database setup.

```ini
sqlalchemy.url = postgresql://scott:tiger@localhost/test
```

--------------------------------

### Upgrade using branch-at syntax

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/branches.rst

Shows the command and output for upgrading to the head of the 'shoppingcart' branch using 'shoppingcart@head' syntax.

```shell
$ alembic upgrade shoppingcart@head
INFO  [alembic.migration] Running upgrade 1975ea83b712 -> 27c6a30d7c24, add shopping cart table
```

--------------------------------

### Oracle: Create tables with unnamed constraints

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/naming.rst

Shows creating the same tables on Oracle. The foreign key constraint gets an unpredictable name like 'SYS_C0029334'.

```sql
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> CREATE TABLE user_account (id INTEGER PRIMARY KEY);

Table created.

SQL> CREATE TABLE user_order (
  2     id INTEGER PRIMARY KEY,
  3     user_account_id INTEGER REFERENCES user_account(id));

Table created.

SQL> SELECT constraint_name FROM all_constraints WHERE
  2     table_name='USER_ORDER' AND constraint_type in ('R');

CONSTRAINT_NAME
-----------------------------------------------------
SYS_C0029334
```

--------------------------------

### Run full test suite with tox

Source: https://github.com/sqlalchemy/alembic/blob/main/README.unittests.rst

Basic command to run the entire test suite using tox.

```bash
tox
```

--------------------------------

### Output of alembic list_templates

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Expected output from 'alembic list_templates'. It lists available templates and shows how to use them with the init command, e.g., 'alembic init --template generic ./scripts'.

```text
Available templates:

generic - Generic single-database configuration.
pyproject - pep-621 compliant configuration that includes pyproject.toml
async - Generic single-database configuration with an async dbapi.
multidb - Rudimentary multi-database configuration.

Templates are used via the 'init' command, e.g.:

  alembic init --template generic ./scripts
```

--------------------------------

### Define custom database URL in test.cfg

Source: https://github.com/sqlalchemy/alembic/blob/main/README.unittests.rst

Create a test.cfg file with a [db] section to define your own database URLs, then use them with --db.

```ini
# test.cfg file
[db]
my_postgresql=postgresql://username:pass@hostname/dbname
```

--------------------------------

### PostgreSQL: Create tables with unnamed constraints

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/naming.rst

Shows creating two tables on PostgreSQL without explicit constraint names. The foreign key constraint gets a database-generated name.

```sql
test=> CREATE TABLE user_account (id INTEGER PRIMARY KEY);
CREATE TABLE
test=> CREATE TABLE user_order (
test(>   id INTEGER PRIMARY KEY,
test(>   user_account_id INTEGER REFERENCES user_account(id));
CREATE TABLE
```

--------------------------------

### Check if database schema is up to date with migrations

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Uses MigrationContext.get_current_heads and ScriptDirectory.get_heads to compare current heads with migration heads, accommodating branched revision trees. Useful for test or installation suites.

```python
from alembic import config, script
from alembic.runtime import migration
from sqlalchemy import engine


def check_current_head(alembic_cfg, connectable):
    # type: (config.Config, engine.Engine) -> bool
    directory = script.ScriptDirectory.from_config(alembic_cfg)
    with connectable.begin() as connection:
        context = migration.MigrationContext.configure(connection)
        return set(context.get_current_heads()) == set(directory.get_heads())

e = engine.create_engine("mysql://scott:tiger@localhost/test", echo=True)
cfg = config.Config("alembic.ini")
print(check_current_head(cfg, e))
```

--------------------------------

### Run migration with alembic upgrade and view output

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Executes the migration to revision 28af9800143f, showing the SQL statements for creating the customer table, customer_view, and add_customer_sp function. The output includes Alembic and SQLAlchemy logging.

```bash
$ alembic upgrade 28af9800143
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
INFO  [alembic.runtime.migration] Running upgrade  -> 3ab8b2dfb055, create table
INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE customer (
    id SERIAL NOT NULL,
    name VARCHAR,
    order_count INTEGER,
    PRIMARY KEY (id)
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('3ab8b2dfb055')
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [alembic.runtime.migration] Running upgrade 3ab8b2dfb055 -> 28af9800143f, create views/sp
INFO  [sqlalchemy.engine.base.Engine] CREATE VIEW customer_view AS SELECT name, order_count FROM customer WHERE order_count > 0
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] CREATE FUNCTION add_customer_sp(name varchar, order_count integer)
    RETURNS integer AS $$
    BEGIN
        insert into customer (name, order_count)
        VALUES (in_name, in_order_count);
    END;
    $$ LANGUAGE plpgsql;

INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] UPDATE alembic_version SET version_num='28af9800143f' WHERE alembic_version.version_num = '3ab8b2dfb055'
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] COMMIT
```

--------------------------------

### Create and drop table with op.create_table and op.drop_table

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Defines upgrade() and downgrade() functions to create and drop an 'account' table using Alembic directives.

```python
def upgrade():
    op.create_table(
        'account',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('name', sa.String(50), nullable=False),
        sa.Column('description', sa.Unicode(200)),
    )

def downgrade():
    op.drop_table('account')
```

--------------------------------

### Create database and stamp with head revision

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Use SQLAlchemy's create_all to build the full schema, then load the Alembic configuration and stamp the version table with the head revision. This avoids running all migration files for new databases.

```python
# inside of a "create the database" script, first create
# tables:
my_metadata.create_all(engine)

# then, load the Alembic configuration and generate the
# version table, "stamping" it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config("/path/to/yourapp/alembic.ini")
command.stamp(alembic_cfg, "head")
```

--------------------------------

### Integrate naming convention with DeclarativeBase and env.py

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/naming.rst

Shows how to set up a naming convention on a DeclarativeBase's MetaData and then configure Alembic's env.py to use it as target_metadata for autogenerate support.

```python
# in your application's model:
from sqlalchemy import MetaData
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    metadata = MetaData(naming_convention={
        "ix": "ix_%(column_0_label)s",
        "uq": "uq_%(table_name)s_%(column_0_name)s",
        "ck": "ck_%(table_name)s_`%(constraint_name)s`",
        "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
        "pk": "pk_%(table_name)s"
    })
```

```python
# .. in your Alembic env.py:

# add your model's MetaData object here
# for 'autogenerate' support
from myapp import mymodel
target_metadata = mymodel.Base.metadata

# ...

def run_migrations_online():

    # ...

    context.configure(
                connection=connection,
                target_metadata=target_metadata
                )
```

--------------------------------

### Output of alembic init

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Expected output from running 'alembic init alembic'. It creates the alembic directory, versions subdirectory, and several files including alembic.ini and env.py. The output reminds to edit configuration settings before proceeding.

```text
Creating directory /path/to/yourproject/alembic...done
Creating directory /path/to/yourproject/alembic/versions...done
Generating /path/to/yourproject/alembic.ini...done
Generating /path/to/yourproject/alembic/env.py...done
Generating /path/to/yourproject/alembic/README...done
Generating /path/to/yourproject/alembic/script.py.mako...done
Please edit configuration/connection/logging settings in
'/path/to/yourproject/alembic.ini' before proceeding.
```

--------------------------------

### View history range from three revisions ago to current

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

View a relative range starting from three revisions ago up to the current migration. Note the required syntax without spaces due to an argparse bug.

```shell
$ alembic history -r-3:current
```

--------------------------------

### Include default schema with include_name hook

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Filters schema names during autogenerate using the include_name hook, but also includes the default schema by comparing to None. This is a variation of the previous example that includes the default schema.

```python
def include_name(name, type_, parent_names):
    if type_ == "schema":
        # this **will* include the default schema
        return name in [None, "schema_one", "schema_two"]
    else:
        return True

context.configure(
    # ...
    include_schemas = True,
    include_name = include_name
)
```

--------------------------------

### Share a connection across multiple Alembic commands

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/commands.rst

Use the Config.attributes dictionary to share a connection across multiple commands within a transaction. This requires env.py to consume the 'connection' attribute, as shown in the connection_sharing example.

```python
with engine.begin() as connection:
    alembic_cfg.attributes['connection'] = connection
    command.upgrade(alembic_cfg, "head")
```

--------------------------------

### Complete program to compose URL and show configparser form

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

A complete program that composes a SQLAlchemy URL and shows the correct configparser form for a given set of database connection details, and illustrates how to assert these forms for correctness.

```python
from sqlalchemy import URL, make_url
```

--------------------------------

### Downgrade to base

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/tutorial.rst

Downgrade all the way back to the beginning (base). The output shows the downgrade steps.

```shell
$ alembic downgrade base
INFO  [alembic.context] Context class PostgresqlContext.
INFO  [alembic.context] Will assume transactional DDL.
INFO  [alembic.context] Running downgrade ae1027a6acf -> 1975ea83b712
INFO  [alembic.context] Running downgrade 1975ea83b712 -> None
```

--------------------------------

### Filter tables with include_name (default schema)

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Use this hook to limit autogenerate to tables present in the target MetaData, preventing drop_table operations for extraneous database tables. The example only considers table names in the default schema.

```python
target_metadata = MyModel.metadata

def include_name(name, type_, parent_names):
    if type_ == "table":
        return name in target_metadata.tables
    else:
        return True

context.configure(
    # ...
    target_metadata = target_metadata,
    include_name = include_name,
    include_schemas = False
)
```

--------------------------------

### Replace CreateTableOp implementation with logging

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/operations.rst

Replaces the implementation of CreateTableOp to log each table creation to a metadata table. Place this code in env.py to load before migrations run.

```python
from alembic import op
from alembic.operations import Operations
from alembic.operations.ops import CreateTableOp
from alembic.operations.toimpl import create_table as _create_table
from sqlalchemy import MetaData, Table, Column, String

# Define a metadata table to track table operations
log_table = Table(
    "table_metadata_log",
    MetaData(),
    Column("operation", String),
    Column("table_name", String),
)

@Operations.implementation_for(CreateTableOp, replace=True)
def create_table_with_logging(operations, operation):
    # First, run the original CREATE TABLE implementation
    _create_table(operations, operation)

    # Then, log the operation to the metadata table
    operations.execute(
        log_table.insert().values(
            operation="create",
            table_name=operation.table_name
        )
    )
```

--------------------------------

### Filter tables with include_name and schema support

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Full example of omitting tables with schema support using the pre-built "schema_qualified_table_name" key in parent_names. The hook also filters schemas by name, and include_schemas must be True.

```python
target_metadata = MyModel.metadata

def include_name(name, type_, parent_names):
    if type_ == "schema":
        return name in [None, "schema_one", "schema_two"]
    elif type_ == "table":
        # use schema_qualified_table_name directly
        return (
            parent_names["schema_qualified_table_name"] in
            target_metadata.tables
        )
    else:
        return True

context.configure(
    # ...
    target_metadata = target_metadata,
    include_name = include_name,
    include_schemas = True
)
```

--------------------------------

### Customize revision generation with process_revision_directives

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/api/autogenerate.rst

Shows how to customize autogenerate output by providing a process_revision_directives callback to context.configure in an env.py script. The callback receives a list of MigrationScript directives; this example sets a specific branch head and removes all downgrade operations.

```python
    def process_revision_directives(context, revision, directives):
        script = directives[0]

        # set specific branch
        script.head = "mybranch@head"

        # erase downgrade operations
        script.downgrade_ops.ops[:] = []

    # ...

    def run_migrations_online():

        # ...
        with engine.connect() as connection:

            context.configure(
                connection=connection,
                target_metadata=target_metadata,
                process_revision_directives=process_revision_directives)

            with context.begin_transaction():
                context.run_migrations()
```

--------------------------------

### Data migration with schema and data upgrades/downgrades

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Shows a migration script that separates schema and data operations. The upgrade() and downgrade() functions check for the 'data' argument via context.get_x_argument() and call the appropriate data functions. The schema_upgrades() creates a table, schema_downgrades() drops it, data_upgrades() uses op.bulk_insert() to insert rows, and data_downgrades() deletes them. Invoke with 'alembic -x data=true upgrade head'.

```python
    def downgrade():
        if context.get_x_argument(as_dictionary=True).get('data', None):
            data_downgrades()
        schema_downgrades()

    def schema_upgrades():
        """schema upgrade migrations go here."""
        op.create_table("my_table", Column('data', String))

    def schema_downgrades():
        """schema downgrade migrations go here."""
        op.drop_table("my_table")

    def data_upgrades():
        """Add any optional data upgrade migrations here!"""

        my_table = table('my_table',
            column('data', String),
        )

        op.bulk_insert(my_table,
            [
                {'data': 'data 1'},
                {'data': 'data 2'},
                {'data': 'data 3'},
            ]
        )

    def data_downgrades():
        """Add any optional data downgrade migrations here!"""

        op.execute("delete from my_table")
```

```bash
alembic -x data=true upgrade head
```

--------------------------------

### Register custom spaces_to_tabs post-write hook in Python

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Register a custom post-write hook as a Python function using the @write_hooks.register decorator. This example rewrites generated code to use tabs instead of four spaces. The function can be placed directly in the env.py file and is configured in alembic.ini under the [alembic] section.

```python
from alembic.script import write_hooks
import re

@write_hooks.register("spaces_to_tabs")
def convert_spaces_to_tabs(filename, options):
    lines = []
    with open(filename) as file_:
        for line in file_:
            lines.append(
                re.sub(
                    r"^(    )+",
                    lambda m: "\t" * (len(m.group(1)) // 4),
                    line
                )
            )
    with open(filename, "w") as to_write:
        to_write.write("".join(lines))
```

--------------------------------

### Configure black and zimports post-write hooks in alembic.ini and pyproject.toml

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Configure Alembic post-write hooks to run the black and zimports formatters on newly generated revision files. The first block shows the alembic.ini style, the second shows the equivalent pyproject.toml configuration. When used, a newly generated revision file is processed first by black, then by zimports.

```ini
black.type = console_scripts
black.entrypoint = black
black.options = -l 79 REVISION_SCRIPT_FILENAME

zimports.type = console_scripts
zimports.entrypoint = zimports
zimports.options = --style google REVISION_SCRIPT_FILENAME
```

```toml
# format using "black", then "zimports"

[[tool.alembic.post_write_hooks]]
name = "black"
type="console_scripts"
entrypoint = "black"
options = "-l 79 REVISION_SCRIPT_FILENAME"

[[tool.alembic.post_write_hooks]]
name = "zimports"
type="console_scripts"
entrypoint = "zimports"
options = "--style google REVISION_SCRIPT_FILENAME"
```

--------------------------------

### Create a virtual environment with venv

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/front.rst

Creates a Python virtual environment in the project directory. After running, the interpreter is available at /path/to/your/project/.venv/bin/python.

```bash
$ cd /path/to/your/project
$ python -m venv .venv
```

--------------------------------

### Run tox with SQLite and PostgreSQL backend tests

Source: https://github.com/sqlalchemy/alembic/blob/main/README.unittests.rst

Runs the test suite with SQLite and also runs backend tests against a PostgreSQL database.

```bash
tox -e py311-sqlite-postgresql
```

--------------------------------

### Rendered migration with user_module_prefix

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Shows the resulting migration rendering after setting user_module_prefix.

```python
Column("my_column", myapp.migration_types.MyCustomType())
```

--------------------------------

### Create migration for views and stored procedures

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Shows the command to create a migration for views and stored procedures, and the migration file that uses custom directives create_view, create_sp, drop_view, and drop_sp. It defines ReplaceableObject instances for a view and a stored procedure.

```bash
$ alembic revision -m "create views/sp"
```

```python
"""create views/sp

Revision ID: 28af9800143f
Revises: 3ab8b2dfb055
Create Date: 2015-07-27 16:24:03.589867

"""

# revision identifiers, used by Alembic.
revision = '28af9800143f'
down_revision = '3ab8b2dfb055'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa

from foo import ReplaceableObject

customer_view = ReplaceableObject(
    "customer_view",
    "SELECT name, order_count FROM customer WHERE order_count > 0"
)

add_customer_sp = ReplaceableObject(
    "add_customer_sp(name varchar, order_count integer)",
    """
    RETURNS integer AS $$
    BEGIN
        insert into customer (name, order_count)
        VALUES (in_name, in_order_count);
    END;
    $$ LANGUAGE plpgsql;
    """
)


def upgrade():
    op.create_view(customer_view)
    op.create_sp(add_customer_sp)


def downgrade():
    op.drop_view(customer_view)
    op.drop_sp(add_customer_sp)
```

--------------------------------

### Run Alembic Revision with Tenant Schema via -x Option

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/cookbook.rst

Command-line invocation to generate a migration for a specific tenant schema. The schema is passed using the -x option, and autogenerate reflects tables using the default_schema_name attribute.

```bash
[]$ alembic -x tenant=some_schema revision -m "rev1" --autogenerate
```

--------------------------------

### Configure multiple post write hooks with black and zimports

Source: https://github.com/sqlalchemy/alembic/blob/main/docs/build/autogenerate.rst

Configures multiple post write hooks that run sequentially: black first, then zimports. The hooks are specified as a comma-separated list.

```ini
[post_write_hooks]

# format using "black", then "zimports"
hooks=black, zimports
```
Tokens:
Raw