| name | postgres-migration |
| description | Creates an Alembic migration script for PostgreSQL database schema changes. Use this skill when database models in `packages/postgres-database/src/simcore_postgres_database/models/` have changed (column additions, type changes, schema structure modifications, etc.). |
When to Use This Skill
Run this workflow whenever you make changes to the SQLAlchemy models in packages/postgres-database/src/simcore_postgres_database/models/. The resulting migration script ensures that production deployments can migrate all existing database data to the new schema.
During a development session, you may generate 2–3 smaller migration scripts (one per logical change) rather than one large script.
Prerequisites
- Changes have been made to model files in
packages/postgres-database/src/simcore_postgres_database/models/
- You are in the workspace root directory
- Docker is running (for the reference PostgreSQL database)
Step 1: Check and Activate the Virtual Environment
From the workspace root, check if the .venv is already active:
which python
If it shows a path containing .venv, it is already active. Otherwise, activate the venv in the repository's root path:
source .venv/bin/activate
Step 2: Stop Any Existing PostgreSQL Database
Before setting up a fresh reference database, stop any running PostgreSQL container:
cd packages/postgres-database
make pg-down
Step 3: Set Up the Reference Database
Navigate to the packages/postgres-database directory and initialize the reference database:
cd packages/postgres-database
make setup-commit
This command:
- Installs the
sc-pg CLI tool
- Spins up a fresh PostgreSQL Docker container with the current schema
- Outputs logs with the Adminer URL (e.g.,
http://127.0.0.1:18080/?pgsql=postgres&username=test&db=test&ns=public)
Review the output logs to confirm the database is ready. In case of failure, read hints in the log.
Step 4: Generate the Migration Script
Run the sc-pg review command with a short, descriptive message of your schema changes. This message is used as part of the migration filename:
sc-pg review -m "add user_id column to projects table"
This command:
- Compares the current database state with your modified SQLAlchemy models
- Generates a new Alembic migration script in
packages/postgres-database/src/simcore_postgres_database/migration/
Example output path:
packages/postgres-database/src/simcore_postgres_database/migration/versions/abc123def456_add_user_id_column_to_projects_table.py
Step 5: Validate the Migration Script
Inspect the generated migration file to verify it captures all your schema changes.
Open the file and check:
- All new/modified columns are included
- Data types match your model changes
- Constraints (if any) are correct
- Relationship changes are reflected (if applicable)
In rare cases, the Alembic auto-generation may not capture complex changes. If you find missing or incorrect statements, manually edit the migration file to correct them.
Step 6: Clean Up
Stop the reference PostgreSQL database:
make pg-down
Scope
This skill completes with the generation and validation of the migration script. The responsibility for the following actions lies outside this skill:
- Committing the migration file to version control
- Running the migration in staging/production environments
- Testing the migration against production-like data
- Verifying backward compatibility
Common Issues
| Issue | Solution |
|---|
sc-pg command not found | Ensure make setup-commit ran successfully and the .venv is active |
| Migration file not generated | Check the logs from sc-pg review for errors; verify model changes were saved to disk |
| Migration missing some changes | Manually edit the migration file (Python code) to add missing operations (see Step 5) |
| Adminer URL not accessible | Check Docker logs with docker logs <postgres_container_id> or look at make setup-commit output |
Last updated: 2026-03-10
