🔧 Database Version Migration and Upgrade Instructions

To change the database version smoothly and seamlessly, we use Alembic. This tool allows us to manage database schema changes in a consistent, version-controlled manner. As a result, users who have built their own PEPhub instances can easily upgrade to the latest version without losing any data or needing to manually interact with the database.

👷 Database schema change - for database developers

If you are changing database schema in db_utils.py file and schema in the database changed, you should percied wiht the following steps:

0. Set up the database URL in `alembic.ini`:

sqlalchemy.url = postgresql+psycopg://user:password@localhost/dbname

By default, it is set to testing database. Credentials are in README.md in test folder of the repository.

1. Create a new migration script:

When you modify your SQLAlchemy models, follow these steps to keep the database schema in sync:

a. Modify Models: Update your SQLAlchemy models in your code. b. Generate Migration:

alembic revision --autogenerate -m "Describe your change"

2. Edit the migration script:

Open the newly created migration script and edit the upgrade() and downgrade() functions to define the changes you want to make to the database schema. - The upgrade() function should contain the code to apply the changes. - The downgrade() function should contain the code to revert the changes.

3. Apply the Migration

Run the migration to apply it to your database. Create small Python script with connection to the database with pepdbagent, and parameter run_migrations=True:

from pepdbagent import PEPDatabaseAgent

db_agent = PEPDatabaseAgent(
   host="localhost",
   port=5432,
   database="pep-db",
   user=None,
   password=None,
   run_migrations=True,
)

This will run all migrations of the database, including the one you just created.

Version Control

Each migration script has a unique identifier and tracks schema changes. Always commit these scripts to version control.

🧙‍♂️ Database schema change - for PEPhub users

If you are changing database schema in db_utils.py file and schema in the database changed, you should run the following script before connecting to the database:

from pepdbagent import PEPDatabaseAgent

db_agent = PEPDatabaseAgent(
   host="localhost",
   port=5432,
   database="pep-db",
   user=None,
   password=None,
   run_migrations=True,
)