Alembic

Alembic is a migration tool for SQLAlchemy. It is a flexible tool for managing database migrations. Below, we’ll walk through the process of setting up Alembic in a project and running your first migration.

We’ll start with an example project that has Alembic already set up, and you can follow along with the steps below. Alternatively, you can run these steps in your own project.

Running an Example Migration

1. Clone the example project

   git clone https://github.com/niledatabase/niledatabase
   cd niledatabase/examples/migrations/alembic
   

2. Set up the environment and install dependencies

   virtualenv .venv
   source .venv/bin/activate 
   # install alembic, sqlalchemy, psycopg2-binary and dotenv
   pip install -r requirements.txt
   # alembic init alembic # run this if you install alembic in your own project
   

3. Create a Migration Script

   In the example project, we have a migration script already created. You can find it in the ./alembic/versions directory.

   Here’s how to create a new migration script:

   alembic revision -m "create account table"
   

   This will generate a new file: ./alembic/versions/13379be60997_create_account_table.py. Now we want to edit the file and add the migration script to it:

   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')
   

4. Connect to Database

   To connect Alembic to the right database, create a .env file and add the connection string there:

   DATABASE_URL=postgresql://user:password@host:5432/dbname
   

   You can get your connection string from the Nile database home page.

5. Run the Migration

   alembic upgrade head
   

   Thats it! You can connect to your database and see the new table.

   If you see an error that the table already exists, double check that you didn’t accidentally create a second migration with the accounts table on top of the existing one. If this happened to you, you can delete the new migration file (or alternatively, modify the table name).

Generating Migrations

Alembic can also generate migrations for you. This is useful if you want to create a migration for a new table.

1. Create a new migration

   The example project has a models.py file that defines two models: Todo and Tenant.

   To generate a migration for these models, you can use the following command:

   alembic revision --autogenerate -m "create todo and tenant models"
   

   Alembic autogeneration compares your database state with models.py and will drop any tables not defined there. Since Nile has a built-in Tenants table that can’t be dropped, we include the Tenant model in models.py to prevent this.

2. Run the generated migration file

   You can then edit the generated migration file (if needed) and run it like as we did before:

   alembic upgrade head
   

Next Steps

This is the most basic use-case of Alembic. There is a lot more to it, which you can learn from the official Alembic documentation.