SQLAlchemy Diff

1.1.1 · active · verified Thu Apr 16

SQLAlchemy-diff is a Python library that provides a tool for comparing database schemas using SQLAlchemy. It helps identify differences between two database schemas, reporting on tables, columns, primary keys, foreign keys, indexes, unique constraints, check constraints, and enums. The current version is 1.1.1, and it maintains a release cadence of updates for compatibility and bug fixes, with major versions introducing breaking changes as seen with v1.0.0.

Common errors

```
ModuleNotFoundError: No module named 'sqlalchemydiff'
```
cause The `sqlalchemy-diff` package is not installed in the current Python environment.

fix Install the package using pip: `pip install sqlalchemy-diff`
```
AttributeError: module 'sqlalchemydiff' has no attribute 'compare'
```
cause Attempting to use the old `compare` function directly from the `sqlalchemydiff` module, which was removed in version 1.0.0. The API changed to use the `Comparer` class.

fix Update your import statement to `from sqlalchemydiff.comparer import Comparer` and initialize the comparer as `comparer = Comparer(engine_one, engine_two)`.
```
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) fe_sendauth: no password supplied (or similar DB connection error)
```
cause The database connection URI is incorrect, credentials are invalid, or the target database server is not accessible or running.

fix Verify the database connection URI (host, port, username, password, database name) is correct and ensure the database server is running and reachable.
```
TypeError: Comparer() missing 1 required positional argument: 'engine_two'
```
cause The `Comparer` class requires two SQLAlchemy engine instances (representing the two schemas to compare) during its initialization.

fix Ensure you provide both `engine_one` and `engine_two` when instantiating `Comparer`, for example: `comparer = Comparer(engine_one, engine_two)`.

Warnings

breaking Version 1.0.0 introduced a complete rewrite of the library, fundamentally changing the API. Code written for pre-1.0.0 versions will break and require significant refactoring.
Fix: Review the official documentation and quickstart for v1.0.0+ to update imports and class usage from functions.
breaking As of version 1.0.4, `sqlalchemy-diff` requires Python 3.10 or higher. Running on older Python versions (e.g., 3.9) will result in installation or runtime errors.
Fix: Upgrade your Python environment to 3.10 or newer.
deprecated The `sqlalchemy-utils` dependency was removed in v1.1.0. If your project indirectly relied on features from `sqlalchemy-utils` via `sqlalchemy-diff`, you might need to add `sqlalchemy-utils` as a direct dependency to your project.
Fix: If needed, explicitly install `sqlalchemy-utils` in your project: `pip install sqlalchemy-utils`.
gotcha When manually creating `sqlalchemy.engine.Engine` instances and passing them to the `Comparer` constructor, you are responsible for managing their lifecycle, including calling `engine.dispose()` to close database connections. If using `Comparer.from_params()`, engine disposal is handled automatically if `dispose_engines=True` (which is the default behavior for `from_params`).
Fix: Ensure `engine.dispose()` is called on manually created engines, or use `Comparer.from_params()` when possible.

Install

pip install sqlalchemy-diff Install latest version

Imports

create_engine
```
from sqlalchemy import create_engine
```
Comparer
wrong:
```
from sqlalchemydiff import compare
```
correct:
```
from sqlalchemydiff.comparer import Comparer
```
The `compare` function was available in pre-1.0.0 versions but was replaced by the `Comparer` class in v1.0.0 and later.

Quickstart

This quickstart demonstrates how to compare two SQLAlchemy database schemas using in-memory SQLite databases. It initializes two `MetaData` objects, creates corresponding tables in two separate engines, and then uses the `Comparer` class to identify differences. The output will detail discrepancies found, such as a changed column name. It also comments on `Comparer.from_params` for convenience with automatic engine disposal.

from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table
from sqlalchemydiff.comparer import Comparer

# Define schema for database one (source)
metadata_one = MetaData()
Table(
    'users',
    metadata_one,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('email', String(100), unique=True)
)

# Define schema for database two (target) with a difference
metadata_two = MetaData()
Table(
    'users',
    metadata_two,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('address', String(200)) # Changed column
)

# Create in-memory SQLite engines for demonstration
engine_one = create_engine('sqlite:///:memory:')
engine_two = create_engine('sqlite:///:memory:')

# Create tables in memory
metadata_one.create_all(engine_one)
metadata_two.create_all(engine_two)

# Compare the schemas
comparer = Comparer(engine_one, engine_two)
result = comparer.compare(one_alias='Source DB', two_alias='Target DB')

if result.is_match:
    print('Schemas are identical!')
else:
    print('Schemas differ! Differences:')
    for error in result.errors:
        print(f"  - {error.description} (Table: {error.table_name}, Column: {error.column_name or 'N/A'}) [Severity: {error.severity}]")

# Example using from_params for automatic engine disposal
# (Requires actual database URIs to connect)
# try:
#     comparer_from_params = Comparer.from_params(
#         'sqlite:///:memory:',
#         'sqlite:///:memory:',
#         dispose_engines=True
#     )
#     result_from_params = comparer_from_params.compare()
#     print('\nComparison using from_params:')
#     if result_from_params.is_match:
#         print('Schemas are identical (from_params)!')
#     else:
#         print('Schemas differ (from_params)!')
#         for error in result_from_params.errors:
#             print(f"  - {error.description}")
# except Exception as e:
#     print(f"Error with from_params example: {e}")

view raw JSON →