The goal is to have an API with persistent storage in Postgres running on containers Docker.
Version used for this build
Define a .env file used in the docker-compose.yml
POSTGRES_USER=postgres
POSTGRES_PASSWORD=<password>
POSTGRES_DB=<database>
PGADMIN_DEFAULT_EMAIL=<valid email>
PGADMIN_DEFAULT_PASSWORD=<password>
To avoid permission denied
PGDATA: /var/lib/pg_data
version: "3"
services:
database:
image: 'postgres:latest'
ports:
- 5432:5432
env_file:
- .env
environment:
PGDATA: /var/lib/pg_data
networks:
- postgres-network
volumes:
- ./db-data/:/var/lib/pg_data
pgadmin:
image: dpage/pgadmin4
ports:
- 5433:80
env_file:
- .env
environment:
SESSION_DB_PATH: /var/lib/pg_admin
depends_on:
- database
networks:
- postgres-network
volumes:
- ./pgadmin-data/:/var/lib/pg_admin
networks:
postgres-network:
driver: bridge
Define a script to prepare the envirnment and start containers
#!/bin/bash
# ----------------------------------------------
# DEVOPS --
# Script to deploy a postgres container through
# docker-compose
# ----------------------------------------------
# Required DATABASE_PASSWORD
if [ -z "$DATABASE_PASSWORD" ]
then
echo "\$DATABASE_PASSWORD is not set"
echo "export DATABASE_PASSWORD=<password>"
exit 0
fi
# Required PGADMIN_DEFAULT_PASSWORD
if [ -z "$PGADMIN_DEFAULT_PASSWORD" ]
then
echo "\$PGADMIN_DEFAULT_PASSWORD is not set"
echo "export PGADMIN_DEFAULT_PASSWORD=<password>"
exit 0
fi
# Required directory and UID:GUID
PGADMIN_DIR="pgadmin-data"
PGADMIN_PERM=5050
# Check directory for pgadmin
if [ ! -d ${PGADMIN_DIR} ]
then
echo "Create directory ${PGADMIN_DIR}"
mkdir ${PGADMIN_DIR}
fi
# Check permission on pgadmin
if [ "$(ls -n |grep -w ${PGADMIN_DIR}|cut -d" " -f3,4)" != "${PGADMIN_PERM} ${PGADMIN_PERM}" ]
then
echo "Set permission ${PGADMIN_PERM}:${PGADMIN_PERM} on ${PGADMIN_DIR}"
sudo chown -R ${PGADMIN_PERM}:${PGADMIN_PERM} ${PGADMIN_DIR}
fi
# Start Container
/usr/bin/docker-compose up
pip install alembic
Create an environment using the “generic” template:
alembic init alembic
Fillout with appropriate information
sqlalchemy.url = postgresql://<user>:<password>@<IP>/<database>
# e.g
sqlalchemy.url = postgresql://postgres:SuperSecret@172.18.0.2/mind_map_db
Alembic can view the status of the database and compare against the table metadata in the application, generating the “obvious” migrations based on a comparison.
This is achieved using the --autogenerate option to the alembic revision command, which places so-called candidate migrations into our new migrations file. We review and modify these by hand as needed, then proceed normally.
To use autogenerate, we first need to modify our env.py so that it gets access to a table metadata object that contains the target.
Suppose our application has a Base = declarative_base() in mind_map_models.py.
e.g:
from sqlalchemy.orm import declarative_base
Base = declarative_base()
This base contains a MetaData object which contains Table objects defining our database.
from src.mind_map_models import MindMapAppModel, Base
Base.metadata.tables
Result:
FacadeDict({'app': Table('app', MetaData(), Column('id', String(length=25), table=<app>, primary_key=True, nullable=False), schema=None), 'leaf': Table('leaf', MetaData(), Column('id', Integer(), table=<leaf>, primary_key=True, nullable=False), Column('app_id', String(length=25), ForeignKey('app.id'), table=<leaf>), Column('path', Unicode(length=100), table=<leaf>), Column('text', Unicode(length=255), table=<leaf>), schema=None)})
The env.py sample script used in the generic template already has a variable declaration near the top for our convenience, where we replace None with our MetaData.
Starting with:
target_metadata = None
Change to:
from src.mind_map_models import Base
target_metadata = Base.metadata
Create a new revision, using alembic revision:
alembic revision --autogenerate -m "Init app and leaf table"
Result:
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.autogenerate.compare] Detected added table 'app'
INFO [alembic.autogenerate.compare] Detected added table 'leaf'
Generating /home/vagrant/app/dev-mind-map/alembic/versions/dfe0f32b898c_init_app_and_leaf_table.py ... done
A new file dfe0f32b898c_init_app_and_leaf_table.py is generated. Looking inside the file:
Result:
"""Init app and leaf table
Revision ID: dfe0f32b898c
Revises:
Create Date: 2022-03-19 04:28:31.029209
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'dfe0f32b898c'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table('app',
sa.Column('id', sa.String(length=25), nullable=False),
sa.PrimaryKeyConstraint('id')
)
op.create_table('leaf',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('app_id', sa.String(length=25), nullable=True),
sa.Column('path', sa.Unicode(length=100), nullable=True),
sa.Column('text', sa.Unicode(length=255), nullable=True),
sa.ForeignKeyConstraint(['app_id'], ['app.id'], ),
sa.PrimaryKeyConstraint('id')
)
# ### end Alembic commands ###
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_table('leaf')
op.drop_table('app')
# ### end Alembic commands ###
alembic upgrade head
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> dfe0f32b898c, Init app and leaf table