DOCSEARCHbyAlgolia
Submit Docs

Alembic Documentation

repository·main·Indexed 26 days ago

https://github.com/sqlalchemy/alembic

A database migration tool for SQLAlchemy that manages schema changes through migration scripts. It features autogeneration of scripts by comparing Python models to the database, support for non-linear versioning via a DAG model, transactional DDL, and batch migrations for SQLite. The tool provides a CLI for operations such as upgrade, downgrade, stamp, and merge, as well as a Config class for programmatic control via .ini or pyproject.toml files.

Tokens
2.9K
Snippets
4
Records
26
Agent score
88%

What's inside Alembic

  1. Overview of Alembic database migrations

    main

    Alembic is a database migrations tool designed for SQLAlchemy applications. It provides a system for managing database schema changes through 'migration scripts' that can 'upgrade' a database to a new version or 'downgrade' it by reversing the steps.

    Key capabilities include:

    • ALTER statement emission: Automatically generates DDL to change table structures and other constructs.
    • Transactional DDL support: For databases like PostgreSQL and Microsoft SQL Server, migrations can run within a transaction, allowing for automatic rollback upon failure.
    • Minimalist script construction: Use high-level commands like alter_column(), rename_table(), and add_constraint() to perform operations without recreating full SQLAlchemy Table structures.
    • Autogeneration: The --autogenerate feature inspects the current database state via SQLAlchemy's schema inspection and compares it to your Python models to generate 'candidate' migration scripts.
    • SQL script support: Migrations can be output as textual SQL scripts for environments where direct DDL execution is restricted.
    • Non-linear versioning: Uses a directed-acyclic graph (DAG) model for versioning, allowing for branches, multiple roots, and mergepoints using UUID identifiers.
    • SQLite support via Batch migrations: Accommodates SQLite's limited ALTER capabilities using a 'batch' migration concept, which uses a 'move-and-copy' workflow to apply multiple changes to a table.

  2. Set up MSSQL using Docker for testing

    main

    Use these commands to set up an MSSQL container for testing. Note that when using this Docker configuration, the connection URL is mssql+pymssql://scott:tiger^5HHH@127.0.0.1:1433/test. You can run this with pytest using the --db docker_mssql flag.

    # pull image
docker pull mcr.microsoft.com/mssql/server:2019-CU1-ubuntu-16.04

# run container
docker run --rm -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 127.0.0.1:1433:1433 -d --name mssql mcr.microsoft.com/mssql/server:2019-CU2-ubuntu-16.04

# configure database
sleep 20
docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -Q "sp_configure 'contained database authentication', 1; RECONFIGURE; CREATE DATABASE test CONTAINMENT = PARTIAL; ALTER DATABASE test SET ALLOW_SNAPSHOT_ISOLATION ON; ALTER DATABASE test SET READ_COMMITTED_SNAPSHOT ON; CREATE LOGIN scott WITH PASSWORD = 'tiger^5HHH'; ALTER SERVER ROLE sysadmin ADD MEMBER scott;"
docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema"
docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'yourStrong(!)Password' -d test -Q "CREATE SCHEMA test_schema_2"

# stop and remove
docker stop mssql

  3. Configure PostgreSQL for SQLAlchemy/Alembic testing

    main

    To run the full test suite on PostgreSQL, ensure the database is empty and configured with specific settings for unicode, HSTORE, and full-text search.

    Requirements:

    • Use UTF8 encoding for JSONB unicode testing.
    • Enable the hstore extension for HSTORE tests.
    • Set the default text search configuration to English to prevent .match() failures.
    • Set max_prepared_transactions to a non-zero value in postgresql.conf for two-phase transaction support.

  4. Configure MSSQL for SQLAlchemy/Alembic testing

    main

    To prevent deadlocks during tests involving multiple connections, you must enable Snapshot Isolation and set the default cursor isolation at the database level. This requires MSSQL 2005 or greater.

    ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

  7. Configure Oracle for SQLAlchemy/Alembic testing

    main
    Testing on Oracle requires a user named test_schema in addition to the default user. The primary database user must have permissions to create and drop tables, synonyms, and constraints within test_schema. For full functionality (including the REFERENCES role), the user should be granted the DBA role.

  8. Configure SQLAlchemy/Alembic logging with pytest

    main

    SQLAlchemy uses Python's standard logging package. You can direct logs to the console by using pytest flags. Use the -s flag to ensure standard output is not suppressed.

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

  9. Run Alembic tests using Tox

    main

    Use tox to run the full test suite. For specific environments, you can target specific Python versions and database backends using the -e flag.

    • Basic run: Runs against a single Python interpreter.
    • Specific Python/SQLite: Runs against a specific version using an in-memory SQLite database.
    • SQLite + PostgreSQL: Runs SQLite tests and also runs "backend" tests against PostgreSQL.
    • MySQL backend only: Runs only the "backend" tests against a MySQL database.

  10. Run Alembic tests using pytest

    main

    Running pytest directly provides more control over database selection and test subsets. You can use the -n flag (via pytest-xdist) to run tests in parallel with multiple processes, which significantly speeds up execution.

    To run tests against a specific database URL, use the --dburi flag. To run tests against multiple specific backends simultaneously, use the --db flag.

  11. Configure database URLs for testing

    main

    The test suite uses built-in database tags to map to pre-set URLs. You can view all available tags and their default URLs using pytest --dbs.

    To use your own database, you can either:

    1. Use an existing tag with --db <name> if your database is reachable at the default URL.
    2. Override existing tags or define new ones by creating a test.cfg file with a [db] section.

    Note: When using multiprocessing, pyodbc URLs must use a hostname/database name combination rather than a DSN name so the suite can generate per-process URLs.