Configure Alembic database migrations

Okay, we have setup the ORM and now we need database migrations. We are going to use Alembic (opens new window)

First lets add our first model in the users/models.py file:

from app.main import db


class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.BigInteger(), primary_key=True)
    nickname = db.Column(db.Unicode(), default="unnamed")

Then inside the root folder, i.e backend run:

$ poetry run alembic init migrations
Creating directory /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations ...  done
Creating directory /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations/versions ...  done
Generating /home/shako/REPOS/Learning_FastAPI/Djackets/backend/alembic.ini ...  done
Generating /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations/env.py ...  done
Generating /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations/script.py.mako ...  done
Generating /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations/README ...  done
Please edit configuration/connection/logging settings in '/home/shako/REPOS/Learning_FastAPI/Djackets/backend/alembic.ini' before proceeding.

After this command our structure will be as following:

❯ tree -I '__pycache__'
.
├── alembic.ini
├── app
│   ├── core
│   │   ├── config.py
│   │   └── __init__.py
│   ├── database.py
│   ├── __init__.py
│   └── main.py
├── docker-compose.yaml
├── Dockerfile
├── LICENSE
├── migrations
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
│       └── 43774c187998_add_users_table.py
├── poetry.lock
├── pyproject.toml
├── README.md
├── tests
│   └── __init__.py
└── users
    ├── api
    │   ├── __init__.py
    │   └── v1.py
    ├── crud.py
    ├── __init__.py
    ├── models.py
    └── schemas.py

7 directories, 23 files

Great, now we need to update migrations/env.py file. Find the line where target_metadata = None and comment it. Then we need to add code portions below:

# target_metadata = None

from app.core.config import settings
from app.main import db
from users.models import User

config.set_main_option('sqlalchemy.url', settings.DATABASE_URI)
target_metadata = db

As we have import User model here it will be noticed by alembic's --autogenerate:

❯ poetry run alembic revision --autogenerate -m 'add users table'
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'users'
Generating /home/shako/REPOS/Learning_FastAPI/Djackets/backend/migrations/versions/43774c187998_add_users_table.py ...  done

You can see the generated python file inside the migrations/versions folder and it has this upgrade() function:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users',
    sa.Column('id', sa.BigInteger(), nullable=False),
    sa.Column('nickname', sa.Unicode(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###

How about applygin our migration file?

❯ poetry run alembic upgrade head
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 43774c187998, add users table

Checking from Postgres:

❯ psql -U postgres -d ecommerce

ecommerce=# \dt
              List of relations
 Schema |      Name       | Type  |   Owner   
--------+-----------------+-------+-----------
 public | alembic_version | table | ecommerce
 public | users           | table | ecommerce
(2 rows)

As you see we have this users table created. Nice 😃

The code changes for this episode -> episode-3 (opens new window)

NEXT -> Creating Pydantic schemas