Database Connection Strings

In this project, database connection strings are not stored as static strings. Instead, they are dynamically constructed from individual configuration parameters using Pydantic's computed_field. This approach ensures type safety, validation, and flexibility across different environments.

Dynamic URI Construction

The core of the database configuration resides in the Settings class within backend/app/core/config.py. Rather than requiring a full DATABASE_URL environment variable, the application assembles the URI from specific PostgreSQL components.

Configuration Components

The following attributes in the Settings class define the database connection details:

• POSTGRES_SERVER: The hostname or IP address of the database server.
• POSTGRES_PORT: The port number (defaults to 5432).
• POSTGRES_USER: The database username.
• POSTGRES_PASSWORD: The database password.
• POSTGRES_DB: The specific database name.

The SQLALCHEMY_DATABASE_URI Field

The SQLALCHEMY_DATABASE_URI is defined as a @computed_field. It uses Pydantic's PostgresDsn.build method to generate a valid PostgreSQL connection string:

@computed_field  # type: ignore[prop-decorator]
@property
def SQLALCHEMY_DATABASE_URI(self) -> PostgresDsn:
    return PostgresDsn.build(
        scheme="postgresql+psycopg",
        username=self.POSTGRES_USER,
        password=self.POSTGRES_PASSWORD,
        host=self.POSTGRES_SERVER,
        port=self.POSTGRES_PORT,
        path=self.POSTGRES_DB,
    )

The project explicitly uses the postgresql+psycopg scheme. This indicates that the application relies on the Psycopg 3 driver for database communication.

Application Integration

The computed URI is consumed by two primary systems: the application's SQLAlchemy engine and the Alembic migration tool.

SQLAlchemy Engine Initialization

In backend/app/core/db.py, the SQLALCHEMY_DATABASE_URI is used to initialize the sqlmodel engine. Because the field is a PostgresDsn object, it is explicitly cast to a string when passed to create_engine:

from app.core.config import settings
from sqlmodel import create_engine

engine = create_engine(str(settings.SQLALCHEMY_DATABASE_URI))

This engine is then used throughout the application, including in backend/app/backend_pre_start.py to verify the database connection before the main service starts.

Alembic Migrations

Alembic also uses this dynamic URI to ensure migrations are applied to the correct database. In backend/app/alembic/env.py, the get_url function retrieves the URI from the application settings:

def get_url():
    return str(settings.SQLALCHEMY_DATABASE_URI)

def run_migrations_online() -> None:
    # ...
    configuration = config.get_section(config.config_ini_section)
    if configuration is None:
        raise RuntimeError("Alembic configuration section not found")
    configuration["sqlalchemy.url"] = get_url()
    connectable = engine_from_config(
        configuration,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )
    # ...

By overriding configuration["sqlalchemy.url"] at runtime, the project ensures that Alembic always uses the credentials defined in the environment variables, ignoring any static placeholders in alembic.ini.

Security and Validation

The Settings class includes a safety mechanism to prevent the use of default credentials in sensitive environments. The _enforce_non_default_secrets method (a model_validator) checks the POSTGRES_PASSWORD:

def _check_default_secret(self, var_name: str, value: str | None) -> None:
    if value == "changethis":
        message = (
            f'The value of {var_name} is "changethis", '
            "for security, please change it, at least for deployments."
        )
        if self.ENVIRONMENT == "local":
            warnings.warn(message, stacklevel=1)
        else:
            raise ValueError(message)

@model_validator(mode="after")
def _enforce_non_default_secrets(self) -> Self:
    # ...
    self._check_default_secret("POSTGRES_PASSWORD", self.POSTGRES_PASSWORD)
    # ...
    return self

If ENVIRONMENT is set to staging or production and the POSTGRES_PASSWORD is still the default "changethis", the application will raise a ValueError and fail to start, enforcing secure configuration practices.