What it is
SQLAlchemy is the library a Python program uses to work with a database without hand-writing SQL for every step. Alembic is its companion for migrations: it changes the table structure step by step without losing the data already collected.
How we use it
The IT help desk bot has 20 tables and 16 Alembic migrations, and the online store applies its migrations automatically when the API starts. Taking a ticket locks the row in the database, so two administrators cannot take the same one.
Where it helps a business
- A bot or store accumulates orders, tickets and payments while features keep being added, so the database structure will change too.
- Several administrators work with the same records at the same time.
How we use it
- IT help desk: MariaDB, 20 tables and 16 Alembic migrations. Taking a ticket locks the row, so two administrators cannot take the same one.
- Online store: async SQLAlchemy 2, with Alembic migrations applied automatically when the API starts.
- ToshaMusic: migrations are checked against the models so the schema and the code never drift apart.
- Electronic queue and finance assistant: async SQLAlchemy on PostgreSQL, with tables created at startup.
Common problems
- No migrations means rollback only from a copy. In the queue and the finance assistant the schema is created at startup, and the limitations honestly say that a version is rolled back from a database copy.
- Synchronous access in an async bot slows down every user, so the drivers are asynchronous: aiomysql, asyncpg.
When you do not need it
A bot with one or two tables in SQLite is fine with plain queries; a separate layer and migrations are overkill there.