I use the database on postqres and backends on python and other services. I need to transfer database structure using alembic.
Structure of the program:
docker-compose.yml:
version: "3.9"
services:
db:
container_name: postgresql_db
image: postgres
restart: always
ports:
- "5432:5432"
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=password
- POSTGRES_DB=carslist
pgadmin:
container_name: pgadmin
image: dpage/pgadmin4
environment:
- PGADMIN_DEFAULT_EMAIL=admin@admin.com
- PGADMIN_DEFAULT_PASSWORD=admin
ports:
- "5050:80"
depends_on:
- db
server:
build: ./backend
restart: always
ports:
- "8000:8000"
depends_on:
- db
app:
container_name: app
build: .
restart: always
ports:
- "3000:3000"
depends_on:
- server
server(backend) dockerfile:
FROM python:3.9
WORKDIR /app
COPY requirements.txt /app/requirements.txt
#RUN pip3 install --upgrade pip
RUN pip3 install -r requirements.txt
COPY . .
EXPOSE 8000
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]
env.py:
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
import os, sys
from dotenv import load_dotenv
from app.database import DATABASE_URL
from app.base import Base
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
load_dotenv(os.path.join(BASE_DIR, ".env"))
sys.path.append(BASE_DIR)
config = context.config
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])
if config.config_file_name is not None:
fileConfig(config.config_file_name)
target_metadata = Base.metadata
def run_migrations_offline() -> None:
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
include_schemas=True
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online() -> None:
#configuration['sqlalchemy.url'] = DATABASE_URL
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool
)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
version_table_schema=target_metadata.schema,
include_schemas=True
)
with context.begin_transaction():
context.execute('SET search_path TO public')
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
alembic.ini:
# A generic, single database configuration.
[alembic]
# path to migration scripts
script_location = migratefile
prepend_sys_path = .
version_path_separator = os
sqlalchemy.url =
[post_write_hooks]
# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic
[handlers]
keys = console
[formatters]
keys = generic
[logger_root]
level = WARN
handlers = console
qualname =
[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine
[logger_alembic]
level = INFO
handlers =
qualname = alembic
[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic
[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S
After docker-compose is started ("docker-compose build" and "docker-compose up"), I run the process in the server container to execute the command:
docker-compose run server alembic revision --autogenerate -m "firstmigrate"
Get the following:
Then I execute the command:
docker-compose run server alembic upgrade head
Get the following:
The migration file in versions did not create and the table did not appear in the database. I looked at the files in the container itself, and there was no migration file either. So what was written as "generated" is actually nowhere.
After migration, only the empty alembic_version table appeared in the database.
models.py:
import datetime as _dt
import sqlalchemy as _sql
import sqlalchemy.ext.declarative as _declarative
import sqlalchemy.orm as _orm
import app.database as _database
class Car(_database.Base):
__tablename__ = "cars"
license_plate = _sql.Column(_sql.String(50), primary_key=True)
model = _sql.Column(_sql.String(250), default=None)
owner = _sql.Column(_sql.Integer, default=None)
vehicle_mileage = _sql.Column(_sql.Integer, default=None)
database.py:
import sqlalchemy as _sql
import sqlalchemy.ext.declarative as _declarative
import sqlalchemy.orm as _orm
from dotenv import load_dotenv
load_dotenv(".env")
DATABASE_URL = "postgresql+psycopg2://postgres:password@db:5432/carslist"
engine = _sql.create_engine(DATABASE_URL)
SessionLocal = _orm.sessionmaker(autocommit=False,
autoflush=False,
bind=engine)
Base = _declarative.declarative_base()