COMP0016 Team 15

Alembic provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine.

We chose to use Alembic because it makes maintaining and developing our database much easier. We simply define our database schema as Python objects and Alembic generates auto-revisions which will automatically update the schema of our database. Not only that, but if we mess up, we can revert our database to a previous schema with one command, how cool is that!

We define our tables in api.models.py as SQLAlchemy Models, here is an example with tables users, datasets, and models. Here we can see the 3 tables and their fields, and how SQLAlchemy allows for easy definition of primary key's, field types, and relations:

Once we've defined our tables, we simply run:

alembic revision --autogenerate -m "Initial tables: users, models, and datasets"

and alembic autogenerates a revision in alembic.versions by finding the models in api.models.py pointed to in alembic.env.py:

Here we see two functions, upgrade() and downgrade(), which will either add or remove the new tables.

To apply the changes to the database, we just run:

alembic upgrade head

Alembic connects to our PostgreSQL database via the database URL defined in alembic.alembic.ini, and our database now has our 3 user tables in its schema, and rows are ready to be added! When we want to add new tables, fields, or relations, we just define it in api.models.py and run the same commands again.

Now lets say that we made a mistake in our definition of our models and the database schema doesnt look how we want it to, we simply run:

alembic downgrade -1

Now our database is back in its previous state. Phew! How easy was that?