Alembic Migrations for Existing Typer CLI

Attempting to Reduce Cognitive Overload

I've used Alembic for database migrations in the past, but almost every time I start a new project, I have to look through the (actually pretty good) Alembic documentation to remember exactly what it is that I need to do again. While I eventually get things working, I wanted to see if I could decrease my cognitive load when working on a CLI application that uses a SQLite database.

Unchained?

One of the awesome things about the Django ORM is how the migrations are baked in to the framework. You basically just need to remember two commands: python manage.py makemigrations and python manage.py migrate.

There is not much fuss to it. As long as you remember those two things, you're golden.

If you don't have Django at your disposal, you're likely using SQLAlchemy as an ORM. But on top of that, you're going to need Alembic to handle your migrations. Here's what you need to get started.

• Download and install the package
• If you've installed to a virtual environment (and you should!), also make sure it's activated to run alembic commands
• Run alembic init alembic (the last parameter is the name of the directory, in this case "alembic")
• Look at all those files! That's your migration environment. Take note of alembic.ini and env.py
• Take a look at your alembic.ini file and try to remember if there's any need to modify it (like location of migrations)
• Modify the env.py to include the target metadata (from SQLAlchemy)
• Create your migration script using alembic revision --autogenerate -m "first migration" to auto-generate a migration file (otherwise you'll need to manually create migration data)
• And then finally, alembic upgrade head, which will finally generate the tables based off of your database models

PHEW!

It's no wonder a lot of folks might opt into using Django in the first place. But perhaps you find yourself working on a small or specialized CLI app and you want to pepper in some database operations. Then what?

Inspiration

In a recent project, I was using the Litestar framework and I noticed that it cleverly incorporates Alembic commands into its own CLI. It does this through an extension of the excellent Advanced Alchemy SQLAlchemy wrapper.

I liked the idea of having all the Alembic commands within the same CLI structure. That way you can access --help and other information through one interface. In addition, it provides a few helpers to make the migration process detailed above not so daunting.

After doing some poking around, I found that Alembic has API access to its commands.

This means that you can run the commands programmatically (from within your application), which is how Litestar (through advanced-alchemy) can access those commands and incorporate them into its own Click CLI.

But What About Me?

I thought I could maybe copy/paste the relevant code, but unfortunately, I was already knee-deep in using Typer. But seeing as to how Typer is based on Click, I should be able to do it, right?

Typer Subcommands

The first thing I needed to do was to create subcommand groups with Typer. This would allow me to segregate my database migrations command in their own module. Incorporating this subcommand group in Typer is fairly straightforward.

Let's say I already have a Typer CLI app like so:

import typer

app = typer.Typer(name="myapp")


@app.command()
def hello(name: str):
    print(f"Hello, {name}!")

I can create a new app somewhere else:

import typer

db_app = typer.Typer()

@app.command()
def create(item: str):
    # do some database stuff
    print(f"{item} has been created!")

I can now incorporate that "second" app into the first one by adding it like this:

import typer

from someplace import db_app

app = typer.Typer(name="myapp")
app.add_typer(db_app, name="database")

@app.command()
def hello(name: str):
    print(f"Hello, {name}!")

That "second" app is now a subcommand group of the parent Typer app.

Depending on how you structure your CLI, you can now run your CLI command directly in the terminal by invoking the app name. That would end up looking a little something like this:

$ myapp hello Fellow
Hello, Fellow!

But also... now with a subcommand!

$ myapp database create thing
thing has been created!

Alembic Commands

With the structure above, I created a separate file that serves as the subcommands for my main CLI app. In this new file, I attempt to invoke the Alembic commands referenced above.

The way that this is accomplished with advanced-alchemy is through a special class called AlembicCommands.

Without getting too caught up in the weeds, I do want to touch on configurations that are specific to SQLAlchemy and Alembic. Again, advanced-alchemy provides helper configuration classes to make things like taking care of database engine/session details a little easier.

Note: I realize that at this point, the complexity factor has spiked a bit. These are details that someone working with the Django ORM will rarely have to worry about.

On the other hand, once some of these complex items are relegated to providing a few details via configuration settings, the promise is that your experience will be exponentially improved in the long run.

So presuming you've set up your configuration (or rely on sensible defaults), the AlembicCommands class provides access to the Alembic commands referenced above.

This now means that you can use the AlembicCommands class in your CLI seamlessly.

First, here is what a pared down version of the class looks like:

from alembic import command as migration_command
# other imports...

class AlembicCommands:
    def __init__(self) -> None:
        self.sqlalchemy_config = sqlalchemy_config
        self.config = self._get_alembic_command_config()

    def upgrade(
        self,
        revision: str = "head",
        sql: bool = False,
        tag: str | None = None,
    ) -> None:

        return migration_command.upgrade(
            config=self.config, revision=revision, tag=tag, sql=sql
        )

    # More stuff here
    ...

    def revision(
        self,
        message: str | None = None,
        autogenerate: bool = False,
        sql: bool = False,
        head: str = "head",
        # And other params...
    ) -> Script | list[Script | None] | None:

        return migration_command.revision(
            config=self.config,
            message=message,
            autogenerate=autogenerate,
            sql=sql,
            head=head,
            # And other params...
        )

    # ETC ...

Here again is the link to the full class

What's Going On?

You'll recognize two of the more common Alembic commands.

But first, let's review... Presuming you've already initialized your migration environment with alembic init alembic—if you want to create your first migration file with Alembic, and then create the tables based on your models, you would need to run the following commands:

$ alembic revision --autogenerate -m "first migration"
# Some stuff about the revision
$ alembic upgrade head
# Running upgrade -> blah blah

The AlembicCommands class is meant to capture the same parameters that are needed to invoke the actual Alembic commands. This is good, because now we can insert our own logic, or even change the invocation command altogether!

Let's See It Then

So, first things first, I create this new Typer app that will serve as my subcommand group.

import typer

app = typer.Typer(name="database", help="Database commands.")

Let's start with the second statement above (alembic upgrade head).

I want to use the AlembicCommands class in order to capture input from the user, and then these parameters (if any) will be passed on directly to the Alembic commands themselves.

Here's what the Typer command ends up looking like (help text has been removed for brevity):

from somewhere.in.my.app import sqlalchemy_config
...

@app.command(name="upgrade", help="Upgrade database.")
def upgrade_database(
    revision: Annotated[str, typer.Option("--revision")] = "head",
    sql: Annotated[bool, typer.Option("--sql", is_flag=True) = False,
    tag: Annotated[
        str | None,
        typer.Option(None, "--tag"),
    ] = False
) -> None:
    """Upgrade the database to the latest revision."""

    from advanced_alchemy.alembic.commands import AlembicCommands

    print("Starting database upgrade process")

    alembic_commands = AlembicCommands(sqlalchemy_config=sqlalchemy_config)
    alembic_commands.upgrade(revision=revision, sql=sql, tag=tag)

Note: One thing to notice here is that sqlalchemy_config is defined elsewhere in my app. It contains info about the database engine and session. (That's for another post altogether...)

But the cool thing is that I can now use the Typer app's command name parameter to whatever I want. Also, I can set the default for the revision parameter to "head" so I don't have to type it out all the time.

With my database subcommand CLI app attached to myapp, the command is now:

$ myapp database upgrade

Well, that's ... not much of a gain. (Remember, the old command is alembic upgrade head). I still have three words to type...

However, I'm using myapp for all my other CLI commands, so that's already in my memory, and the database subcommand can be changed as well. For example, if I change this line app.add_typer(db_app, name="database") to app.add_typer(db_app, name="db"), the command is shorter:

myapp db upgrade

Or, let's say that I have muscle memory from using Django a lot, and I'd rather use migrate instead of upgrade. I can rename my command to: @app.command(name="migrate", help="Upgrade database.") and now I have:

myapp db migrate

Huzzah!

This is even more powerful when looking at the initial revision command:

@app.command(name="makemigrations" help="Make database migrations.")
def make_migrations(
    message: Annotated[str, typer.Option("-m")] = "",
    autogenerate: Annotated[bool, typer.Option("--autogenerate")] = True,
    head: Annotated[str, typer.Option("--head")] = "head",
    # All the params! You get the idea
) -> None:
    if message is None:
        message = "autogenerated"

    alembic_commands = AlembicCommands(sqlalchemy_config=async_config)

    alembic_commands.revision(
        message=message,
        autogenerate=autogenerate,
        head=head,
        # All the params...
        )

You'd want to make sure all the params needed for the alembic_commands.revision method are defined, but you get the idea. Here, the revision command from Alembic is passed a default message if one is not given ("autogenerated"), and the command name has been changed to be more Django-like.

With this setup, your CLI commands now look like this:

$ myapp db makemigrations
# Some stuff about the revision
$ myapp db migrate
# Running upgrade -> blah blah

Now that's more like it! 😎

Like I mentioned before, this exact same thing is implemented for Litestar's CLI, except using Click. My implementation merely translated most/all of those Click commands into their Typer counterparts.

In addition, like the Litestar counterpart, I also added a drop-all command to conveniently drop all tables with a simple command. This is super helpful during development.

Takeaway

Now it's no small task to recreate all the Typer commands, even when utilizing the existing advanced-alchemy library for examples/inspiration.

There are still some pain points that I pointed out above with installation/initialization of Alembic that can slow you down. Some of this can also be abated. For example, you could add logic in the makemigrations command to check to see if an Alembic project has previously been initialized, and if not, to go ahead and either run the init command, or prompt the user for more info.

Of course, this would be moot if you had to implement all of this every time you start a new project. Ideally, all of this gets packaged up, either within advanced-alchemy, which already includes Alembic as a dependency, or with a new package that is built similarly.

All of the decisions for the user could be relegated to a settings file (such as, where should migrations live, where is your metadata stored, etc...). Then, using sensible defaults, the experience for the end user could be much more streamlined and as close to the Django ORM as possible, all without batteries!

Initially, I actually didn't think I'd get this far, but I'm already using these commands in my own CLI and it feels way more ergonomic. I don't have the code up on Github yet, but if/when I do, I will update this post with a link.

If you have feedback/questions/comments, reach out on Mastodon.

Cheers!