duckup

Database migrations for DuckDB written in Python. Use as CLI tool or import as library.

• No dependencies other than DuckDB.
• Simple to use.

Installation

pip install duckup

CLI usage

Duckup provides a command line tool (named... duckup) that allows you to create, list, upgrade and downgrade migrations.

If you worked with Django you can think of duckup CLI as an analogue of makemigrations and migrate commands.

Using CLI is not mandatory, you can use duckup as a library in your Python project, create migration files manually and use the duckup library to programmatically apply your migrations.

Directory: default migrations directory name is migrations, relative to the current working directory. Custom migrations directory can be specified using --dir flag. If directory does not exist, it will be created.

Database: database file must be specified as a positional argument. It can be a relative or absolute path to the DuckDB database file you want to migrate.

Versioning: migrations are versioned using a number. The first migration is version 1, the second is version 2, etc. Actual version is stored in the migrations table in the database. You can change the name of the table using --table flag.

Some examples:

Create a new migration in default or custom migrations directory

duckup create my_migration
duckup create my_migration --dir db_migrations

Upgrade database to latest or to specific version with default or custom version table name

duckup upgrade mydatabase.duckdb
duckup upgrade mydatabase.duckdb --table migrations_table
duckup upgrade mydatabase.duckdb 42

Downgrade database to initial state or to specific version with default or custom version table name

duckup downgrade mydatabase.duckdb 0
duckup downgrade mydatabase.duckdb 0 --table migrations_table
duckup downgrade mydatabase.duckdb 42

List all migrations in default or custom migrations directory

duckup list
duckup list --dir db_migrations

Refer to duckup --help for full usage information.

Use in your Python project

Very basic example:

import duckdb
import duckup

conn = duckdb.connect("my_database.duckdb")

try:
    duckup.upgrade(conn, "migrations_dir")

except duckup.MigrationError as e:
    print(f"Migration error: {e}")
except Exception as e:
    print(f"Unexpected error: {e}")
else:
    print("Migration successful")

For more detailed example see examples/example_migration.py.

Migration files

Naming convention:

<version>_<name>.py

Example:

001_create_users.py

Version is a number that is incremented for each migration. Name is a human readable name for the migration (use underscores to separate words, CLI does not take care of that automatically). When creating a migration using CLI, version is automatically generated. If you are creating a migration manually, you need to take care of naming it properly.

Each migration file contains upgrade and downgrade handlers which are a functions that take a DB connection instance as an argument and perform the necessary operations to upgrade or downgrade the database. It's up to you to decide what to put in there.

"""
create_users migration.
"""

from duckdb import DuckDBPyConnection


def upgrade(conn: DuckDBPyConnection) -> None:
    conn.execute("CREATE TABLE users (id INTEGER, name VARCHAR)")


def downgrade(conn: DuckDBPyConnection) -> None:
    conn.execute("DROP TABLE users")

Migrations are executed in the order of the version number.

Migrations are applied inside a transaction. If any instruction fails, the transaction is rolled back and the migration is considered as not applied.

Logging

Duckup library utilises the standard Python logging interface. By default, log messages are at the INFO level and include:

• Migration files found
• Current database version
• Target version for upgrade/downgrade
• Each migration being applied (upgrade or downgrade)
• Successful completion of migrations

Debug logging

When debug logging is enabled, you'll get much more detailed information:

• Full details of each migration including names and versions
• Source code for each migration being executed
• Execution time measurements for each migration
• Transaction details
• Explanations when migrations are skipped
• Detailed error information when migrations fail

CLI verbosity options

When using the CLI, you can control the verbosity level:

duckup --verbose upgrade mydatabase.db     # More detailed logs (DEBUG level)
duckup --quiet upgrade mydatabase.db       # Only show errors (ERROR level)

Default verbosity level for CLI is INFO.

Customizing logging in your code

When using Duckup as a library, you can customize logging behavior as usual with Python's logging module.

logging.getLogger("duckup").setLevel(logging.DEBUG)

Contribution

Development setup

As 3.9 is the minimum supported by the library version of Python, it is recommended to use it during development to avoid backward compatibility issues introduced by newer versions.

Poetry is used for dependency management and virtual environment creation. It is recommended to use it for library development.

poetry env use 3.9
poetry install
poetry run ./duckup/cli.py

Pytest is used to run unittests.

poetry run pytest

MyPy and Flake8 are used for linting.

poetry run mypy duckup tests
poetry run flake8 duckup tests

Black is used for code formatting.
isort is used for sorting imports.

License

This project is licensed under the terms of the MIT license.