Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/pvnm4/Social-Media-Backend/llms.txt

Use this file to discover all available pages before exploring further.

The Social Media Backend uses Alembic for database schema migrations, giving you version-controlled, reversible changes to your PostgreSQL schema. Every structural change to the database — creating tables, adding columns, defining foreign keys — is captured in a numbered migration file that can be applied forward or rolled back independently.

​
Migration history

The project ships with 7 migration files in alembic/versions/, applied in the following order:
1

108e0432cce3 — Create posts table

Creates the initial posts table with two columns: a primary key id (INTEGER NOT NULL) and a title column (VARCHAR NOT NULL).
op.create_table('posts',
    sa.Column('id', sa.Integer(), nullable=False, primary_key=True),
    sa.Column('title', sa.String(), nullable=False)
)
2

3f67e4df37c9 — Add content column to posts table

Adds a content column (VARCHAR NOT NULL) to the existing posts table.
op.add_column('posts', sa.Column('content', sa.String(), nullable=False))
3

e92365d5c852 — Add users table

Creates the users table with id (primary key), email (unique, NOT NULL), password (NOT NULL), and created_at (timezone-aware timestamp, defaults to now()).
op.create_table('users',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('email', sa.String(), nullable=False),
    sa.Column('password', sa.String(), nullable=False),
    sa.Column('created_at', sa.TIMESTAMP(timezone=True),
              server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('email')
)
4

4ddcfb76cfd6 — Add foreign key to posts table

Adds an owner_id column (INTEGER NOT NULL) to posts and creates a foreign key constraint post_users_fk that references users.id with ON DELETE CASCADE.
op.add_column('posts', sa.Column('owner_id', sa.Integer(), nullable=False))
op.create_foreign_key(
    'post_users_fk',
    source_table='posts',
    referent_table='users',
    local_cols=['owner_id'],
    remote_cols=['id'],
    ondelete='CASCADE'
)
5

0a1bfb2e9c2d — Add last few columns to posts table

Adds published (BOOLEAN NOT NULL, server default True) and created_at (TIMESTAMP WITH TIME ZONE NOT NULL, server default NOW()) to the posts table.
op.add_column('posts',
    sa.Column('published', sa.Boolean(), nullable=False, server_default='True'))
op.add_column('posts',
    sa.Column('created_at', sa.TIMESTAMP(timezone=True),
              nullable=False, server_default=sa.text('NOW()')))
6

b1c66d590ccf — Generate votes table

Creates the votes table with a composite primary key of user_id and post_id. Both columns are foreign keys with ON DELETE CASCADE — deleting a user or post automatically removes their associated votes.
op.create_table('votes',
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('post_id', sa.Integer(), nullable=False),
    sa.ForeignKeyConstraint(['post_id'], ['posts.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id', 'post_id')
)
7

9560c8ff6ac9 — Add phone_number to users table

Adds an optional phone_number column (VARCHAR, nullable) to the users table. This migration was autogenerated by Alembic from a model change.
op.add_column('users', sa.Column('phone_number', sa.String(), nullable=True))

​
Common commands

Use these Alembic commands from the project root (the directory containing alembic.ini): 
# Apply all pending migrations to the latest revision
alembic upgrade head

# Apply exactly one migration step forward
alembic upgrade +1

# Roll back one migration step
alembic downgrade -1

# Roll back all migrations (drops all managed tables)
alembic downgrade base

# Show the current applied revision
alembic current

# Show the full migration history
alembic history

# Auto-generate a new migration from model changes
alembic revision --autogenerate -m "describe your change"
Run alembic history --verbose to see each revision’s full docstring and the up/down revision chain in one view.

​
Creating new migrations

When you need to change the database schema, follow this workflow:
  1. Modify app/models.py — add, remove, or alter SQLAlchemy model columns or tables.
  2. Generate the migration file: 
    alembic revision --autogenerate -m "add bio column to users"
    Alembic compares Base.metadata (your current models) against the live database schema and writes a new file in alembic/versions/ with upgrade() and downgrade() functions.
  3. Review the generated file — open the new revision in alembic/versions/ and verify that the upgrade() and downgrade() functions match your intentions exactly.
  4. Apply the migration: 
    alembic upgrade head
Always review autogenerated migration files before running them. Alembic’s autogenerate cannot detect every change type — for example, column type changes, server defaults, and check constraints may require manual adjustment. Applying an incorrect migration to production data can cause data loss.

​
How Alembic connects to the database

Alembic uses the same DATABASE_* environment variables as the application. The alembic/env.py file reads those variables via the settings object from app/config.py and constructs the SQLAlchemy connection URL at startup: 
from app.config import settings

config.set_main_option(
    "sqlalchemy.url",
    f"postgresql://{settings.database_username}:{settings.database_password}"
    f"@{settings.database_hostname}:{settings.database_port}/{settings.database_name}"
)
app/config.py uses pydantic-settings with env_file = ".env", so Alembic automatically picks up credentials from your .env file — no separate Alembic configuration is needed: 
class Settings(BaseSettings):
    database_hostname: str
    database_port: str
    database_password: str
    database_username: str
    database_name: str
    # ...

    class Config:
        env_file = ".env"
This means the same .env file you use to run the FastAPI server is also used when you run any alembic command, keeping your local development configuration in one place.
When running migrations in a production or CI environment where a .env file is not present, ensure the DATABASE_HOSTNAME, DATABASE_PORT, DATABASE_PASSWORD, DATABASE_USERNAME, and DATABASE_NAME variables are exported as real environment variables before invoking alembic.

Build docs developers (and LLMs) love

Get started for freeTalk to us