Karma Clearing Time (Feat. Alembic)

I've been working on a blog refactoring project for the past few days that I've been putting off for reasons other than lack of time.

First of all, I moved the post list to /posts and put a new landing page on the old /.
The endpoints are now RESTful, with post/{post_id} -> posts/{post_id}, and I've been working on improving the design of the HTML templates.

The biggest improvements were optimizing the Build Kit cache and introducing Discord webhook notifications.

Deployment speed went from 3-4 minutes to 40 seconds, and I was able to stay focused because I didn't have to look at the Actions window to see the logs.

But...
I had originally planned to do a CRUD refactoring today. But when faced with nearly a thousand lines of post code, my eyes naturally drifted downward.

First, let's clean up the model...

Little did I know that this complacent choice would open the gates of hell.

As I cleaned up the model, I realized that there were columns that I thought I would use someday but never did, columns that I couldn't remember the intention of creating, and tables/columns that needed to be added for functionality that I felt was lacking in the current state.

But there was a problem. I looked at the last revision and realized that it was 6 months ago, and I hadn't done any migrations since then. I remembered using Alembic as a tool, so I searched through my memory and found instructions on how to use it.

What is Alembic?

Alembic is a tool that automatically manages database migrations in SQLAlchemy-based projects.

In other words, it's a version control system that makes sure that when you make changes to your model, those changes are reflected in your database.

For example, let's say you add a new column to the users table.
Manually writing ALTER TABLE every time and reflecting it in the deployment would be tedious and error-prone.
Alembic records this process in a version file (migrations/versions/.py) and automatically brings the DB up to date with commands like alembic upgrade head.

In simpler terms, it's a Git-like versioning buddy for your DB, and like Git, it's a hell of a lot like Git, if you mess it up, you're in for a hell of a ride.

The beginning of a billion

I thought about connecting to the DB instance separately and modifying it with manual SQL statements, but there were several columns to delete, new tables to create, and the API instance and DB instance were separated, so I had to reflect them in models.py.

However, my environment was too complex for Alembic. Alembic was running in a Docker container on the API instance, and the DB was running on top of Docker on another instance. A lot has changed in half a year, so I started by checking the connection between Alembic and DB with the following commands

docker exec -it main bash
echo $DATABASE_URL

But what I got back was mysql+pymysql://:@:/.
I read the value from .env, but the environment variable was set to

DATABASE_URL = mysql+pymysql://${DB_USERNAME}:${DB_PASSWORD}@${DB_ENDPOINT}:${DB_PORT}/${DB_NAME}

DB_USERNAME = john
DB_PASSWORD = doe
DB_ENDPOINT = 0.0.0.0
DB_PORT = 0000
DB_NAME = DB

because variable substitution is not natively supported in .env files. Actually, there was nothing wrong with the DB connection itself, but when I thought about it, I realized that I didn't need to write the environment variable like that, so I modified the .env.

I cleaned up the environment like this and tried to migrate from the host environment to Alembic.

docker exec -it main bash
cd /example/app
alembic revision --autogenerate -m "Description"
alembic upgrade head

INFO  [alembic.autogenerate.compare] Detected added column 'posts.series'
INFO  [alembic.migration] 마이그레이션 완료

  Generating /example/app/migrations/versions/00000000_add_series_column.py

  ...  done

It was successful, and the newly added columns were displayed correctly in Workbench.

After checking, I saved and committed my local models.py with the changes and got 502 Bad Gateway, and when I checked the logs, I realized that the API container was broken because the revision file was not found.

main             | FAILED: Can't locate revision identified by '000000000'
main             | ERROR [alembic.util.messaging] Can't locate revision identified by '000000000'

main exited with code 255

I didn't have to look it up, but I had an intuition as to why,
1. the migration we just did was inside the API container, so the revision was also created inside the container, but the revision file was not included in the Git or Docker image.
2. but the revision file doesn't exist in the image that was just overwritten with a new commit.
3. the DB says alembic_version = '000000000', but the file doesn't exist.
4. the file doesn't exist, so the migration is broken, and my Docker-compose is turned on by default after the runtime migration, so the container itself is dead.

To save the server that was only showing 502, I created a dummy with the same name as the revision file and committed it, since the column itself was created anyway.

"""recreate series column"""
revision = '000000000'
down_revision = '111111111'

def upgrade():
    pass

def downgrade():
    pass

However, this is not a fundamental solution, and in the end, the cleanest way to solve this is to create revisions locally, not on the host environment, and only run the alembic upgrade head on the host.

When I ran almebic on the host, I got Command not found: alembic, and this time Poetry was the problem.

I checked with poetry show, and Alembic was installed fine, and grep alembic worked fine, but I got bad interpreter: /MAIN_BLOG/.venv/bin/python: no such file or directory, and the executable in the virtual environment was broken.

I started to feel dizzy, but I persevered.

rm -rf .venv
poetry install --no-root

and reinstalled the dependencies.

Then I tried to migrate to Alembic, which was working again, but...

line 7, in <module>
    from app.database import Base, SQLALCHEMY_DATABASE_URL
ModuleNotFoundError: No module named 'app'

Alembic couldn't find any Python modules in the project.

example/
├── app/
│   ├── alembic.ini
│   ├── migrations/
│   ├── models.py
│   └── ...
├── pyproject.toml
└── ...

I also looked up and found that it's generally recommended to put alembic.ini in the root of the project, so I moved alembic.ini and migrations to root.

Then it spit out another error, this time

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on '0.0.0.0' (timed out)")

It was a DB timeout. But when I looked at that IP, I realized that it was a private IP, not a public IP, and it was inaccessible. I thought it might be a security issue to open public for this, so I looked for another way and came up with port forwarding and tunneled local port 3306 to DB port 3306.

ssh -i ~/.ssh/key.pem -L 3306:0.0.0.0:3306 ubuntu@0.0.0.0

But then I needed to change the DB URL in the .env file that Alembic reads to localhost:3306, which would cause the DB URL that the api reads to point to itself, so I split the env file into two at the same time.

We also modified the docker-compose.yml because the paths to Alembic's configuration files, alembic.ini and /migrations, were moved to root.

After all the modifications were made, I tried to create the revision again locally and confirmed that it was created correctly.
After that, I confirmed that if I proceeded with the commit, the migration would complete successfully via alembic upgrade head in the order that I had set up, and I barely escaped from hell.

Conclusion.

One by one, the decisions that I made without thinking about it piled up and got tangled up, eventually leading to a situation where I needed to do a complete overhaul of containers, Git, environment variables, Dockerfiles, Poetry, Alembic, DB connections, and migration policies.

As always, the choices I made at the time are the ones that end up stabbing me in the back.
This time it happened again, and I asked myself what the hell I was thinking when I made the blog so complicated half a year ago.

But that's not necessarily a bad thing, because this complex structure has given me some real-world troubleshooting experience before I went to work, and I'm definitely getting better at organizing and breaking through it.

And I've realized one thing for sure. **The more you develop, the more you realize that it's all about design. It's not uncommon to spend 10 minutes thinking about something early on and end up spending 10 hours later.

Finally, if I could give one piece of advice to people who want to start a tech blog, it would be to avoid using migration tools like Alembic for small projects.