Lesson 2: Deploy App with Database 💾¶

Deploy applications with persistent data storage using PostgreSQL or MongoDB with secure, production-ready configuration.

Progress Indicator¶

Module 2 of 4: Deploying Apps
└─ Lesson 2 of 3: App with Database [Current]

Difficulty: Intermediate | Time: 40-50 minutes

Learning Objectives¶

By the end of this lesson, you'll be able to:

• Design multi-container applications with Docker Compose
• Configure PostgreSQL, MongoDB, or MySQL databases
• Set up secure communication between app and database
• Manage persistent data with Docker volumes
• Use environment variables and secrets safely
• Understand network isolation and security

Prerequisites Checklist¶

• Completed Lesson 1: Simple Web App
• Understanding of Dockerfile and docker-compose.yml basics
• Basic SQL or database knowledge helpful (but not required)
• An application that uses a database (or use our example)
• SSH access to server
• Familiarity with environment variables

Part 1: Architecture Overview¶

Single Container vs. Multi-Container¶

Lesson 1 (Single Container):

Internet → Traefik → Container (App) → Localhost (no persistence)

Lesson 2 (Multi-Container):

Internet → Traefik → Container (App) → Container (Database)
                                      ↓
                                  Volumes (persistent disk)

Network Diagram¶

┌────────────────────────────────────────────────────────────┐
│ Server (your EgyGeeks instance)                           │
├─────────────────────────────┬────────────────────────────┤
│ traefik_public (external)   │ internal (private)         │
│                             │                            │
│  ┌──────────────┐           │  ┌──────────────────┐     │
│  │              │◄──────────┼─►│  PostgreSQL      │     │
│  │  App         │           │  │  Port: 5432      │     │
│  │  Port: 3000  │◄──────────┼─►│  (no exposed)    │     │
│  │              │           │  │  [postgres-data] │     │
│  └──────────────┘           │  └──────────────────┘     │
│       ▲                      │                            │
└───────┼──────────────────────┴────────────────────────────┘
        │
    Internet

Key Points:

• traefik_public: App connects here so Traefik routes traffic to it
• internal: Private network only accessible to containers in the stack
• Database not exposed: No direct internet access, only via application
• Volumes: Persist data even if database container is destroyed

Why This Architecture?¶

1. Isolation: Database not exposed to internet
2. Performance: App and database communicate locally (fast)
3. Security: Only application can access database
4. Data Persistence: Database data survives container restart
5. Scalability: Easy to run multiple app instances with same database

Part 2: Choose Your Database¶

PostgreSQL (Recommended)¶

Best for: - Web applications (Django, Rails, Laravel) - Complex queries and transactions - Relational data - Production use

Pros: - Powerful and reliable - Excellent for complex data models - Great tooling - Widely used in production

Cons: - More resource-intensive than SQLite - Overkill for very simple apps

MongoDB (NoSQL)¶

Best for: - Document-based applications - Flexible schemas - Rapid prototyping - Non-relational data

Pros: - Flexible schema - Easy scaling - Good for unstructured data

Cons: - No transactions (older versions) - Requires different thinking about data - Can waste storage (document duplication)

MySQL¶

Best for: - WordPress, traditional web applications - Teams familiar with MySQL

Pros: - Lightweight - Very common - Good compatibility

Cons: - Less advanced than PostgreSQL - Fewer features

Part 3: Create Multi-Container Application¶

Choose your database below:

const express = require('express');
const { Pool } = require('pg');
const app = express();
const PORT = process.env.PORT || 3000;

// Database connection
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

// Middleware
app.use(express.json());

// Health check
app.get('/health', (req, res) => {
  res.json({ status: 'healthy' });
});

// Database health check
app.get('/health/db', async (req, res) => {
  try {
    const result = await pool.query('SELECT NOW()');
    res.json({ status: 'healthy', database: 'connected' });
  } catch (error) {
    res.status(503).json({ status: 'unhealthy', error: error.message });
  }
});

// Example endpoint
app.get('/api/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users');
    res.json(result.rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

{
  "name": "postgres-app",
  "version": "1.0.0",
  "main": "app.js",
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "^4.18.2",
    "pg": "^8.10.0"
  }
}

CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email) VALUES
  ('John Doe', 'john@example.com'),
  ('Jane Smith', 'jane@example.com');

FROM node:18-alpine

WORKDIR /app

COPY package*.json ./
RUN npm install

COPY . .

EXPOSE 3000

HEALTHCHECK --interval=30s --timeout=3s --start-period=5s \
  CMD wget --quiet --tries=1 --spider http://127.0.0.1:3000/health || exit 1

CMD ["npm", "start"]

version: '3.8'

services:
  app:
    build: .
    container_name: postgres-app
    restart: unless-stopped
    environment:
      - PORT=3000
      - DATABASE_URL=postgresql://postgres:${DB_PASSWORD}@db:5432/appdb
    depends_on:
      - db
    labels:
      - traefik.enable=true
      - traefik.http.routers.postgres-app.rule=Host(`app.egygeeks.com`)
      - traefik.http.routers.postgres-app.entrypoints=websecure
      - traefik.http.routers.postgres-app.tls.certresolver=letsencrypt
      - traefik.http.services.postgres-app.loadbalancer.server.port=3000
    networks:
      - traefik_public
      - internal

  db:
    image: postgres:15-alpine
    container_name: postgres-app-db
    restart: unless-stopped
    environment:
      - POSTGRES_DB=appdb
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    networks:
      - internal

volumes:
  postgres-data:

networks:
  traefik_public:
    external: true
  internal:
    driver: bridge

DB_PASSWORD=super-secret-password-123-change-this

from fastapi import FastAPI, HTTPException
from pymongo import MongoClient
from pydantic import BaseModel
import os
import json
from datetime import datetime

app = FastAPI()

# MongoDB connection
MONGO_URL = os.getenv('MONGO_URL', 'mongodb://admin:password@db:27017/')
client = MongoClient(MONGO_URL)
db = client['appdb']
users_collection = db['users']

class User(BaseModel):
    name: str
    email: str

@app.get('/health')
def health():
    return {'status': 'healthy'}

@app.get('/health/db')
def health_db():
    try:
        client.admin.command('ping')
        return {'status': 'healthy', 'database': 'connected'}
    except Exception as e:
        return {'status': 'unhealthy', 'error': str(e)}, 503

@app.get('/api/users')
def get_users():
    users = list(users_collection.find({}, {'_id': 0}))
    return users

@app.post('/api/users')
def create_user(user: User):
    users_collection.insert_one(user.dict())
    return user

if __name__ == '__main__':
    import uvicorn
    uvicorn.run(app, host='0.0.0.0', port=8000)

fastapi==0.104.1
uvicorn==0.24.0
pymongo==4.6.0
pydantic==2.5.0

FROM python:3.11-alpine

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY . .

EXPOSE 8000

HEALTHCHECK --interval=30s --timeout=3s --start-period=5s \
  CMD wget --quiet --tries=1 --spider http://127.0.0.1:8000/health || exit 1

CMD ["python", "app.py"]

version: '3.8'

services:
  app:
    build: .
    container_name: mongo-app
    restart: unless-stopped
    environment:
      - MONGO_URL=mongodb://admin:${DB_PASSWORD}@db:27017/
    depends_on:
      - db
    labels:
      - traefik.enable=true
      - traefik.http.routers.mongo-app.rule=Host(`app.egygeeks.com`)
      - traefik.http.routers.mongo-app.entrypoints=websecure
      - traefik.http.routers.mongo-app.tls.certresolver=letsencrypt
      - traefik.http.services.mongo-app.loadbalancer.server.port=8000
    networks:
      - traefik_public
      - internal

  db:
    image: mongo:7-alpine
    container_name: mongo-app-db
    restart: unless-stopped
    environment:
      - MONGO_INITDB_ROOT_USERNAME=admin
      - MONGO_INITDB_ROOT_PASSWORD=${DB_PASSWORD}
      - MONGO_INITDB_DATABASE=appdb
    volumes:
      - mongo-data:/data/db
    networks:
      - internal

volumes:
  mongo-data:

networks:
  traefik_public:
    external: true
  internal:
    driver: bridge

DB_PASSWORD=super-secret-password-123-change-this

from flask import Flask, jsonify
import psycopg2
from psycopg2.extras import RealDictCursor
import os

app = Flask(__name__)

def get_db_connection():
    conn = psycopg2.connect(
        dbname='appdb',
        user='postgres',
        password=os.environ['DB_PASSWORD'],
        host='db',
        port=5432
    )
    return conn

@app.route('/health')
def health():
    return jsonify({'status': 'healthy'})

@app.route('/health/db')
def health_db():
    try:
        conn = get_db_connection()
        conn.close()
        return jsonify({'status': 'healthy', 'database': 'connected'})
    except Exception as e:
        return jsonify({'status': 'unhealthy', 'error': str(e)}), 503

@app.route('/api/users')
def get_users():
    conn = get_db_connection()
    cur = conn.cursor(cursor_factory=RealDictCursor)
    cur.execute('SELECT * FROM users;')
    users = cur.fetchall()
    cur.close()
    conn.close()
    return jsonify(users)

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)

Flask==2.3.3
psycopg2-binary==2.9.7

FROM python:3.11-alpine

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY . .

EXPOSE 5000

HEALTHCHECK --interval=30s --timeout=3s --start-period=5s \
  CMD wget --quiet --tries=1 --spider http://127.0.0.1:5000/health || exit 1

CMD ["python", "app.py"]

version: '3.8'

services:
  app:
    build: .
    container_name: flask-postgres-app
    restart: unless-stopped
    environment:
      - DB_PASSWORD=${DB_PASSWORD}
    depends_on:
      - db
    labels:
      - traefik.enable=true
      - traefik.http.routers.flask-app.rule=Host(`app.egygeeks.com`)
      - traefik.http.routers.flask-app.entrypoints=websecure
      - traefik.http.routers.flask-app.tls.certresolver=letsencrypt
      - traefik.http.services.flask-app.loadbalancer.server.port=5000
    networks:
      - traefik_public
      - internal

  db:
    image: postgres:15-alpine
    container_name: flask-postgres-db
    restart: unless-stopped
    environment:
      - POSTGRES_DB=appdb
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    networks:
      - internal

volumes:
  postgres-data:

networks:
  traefik_public:
    external: true
  internal:
    driver: bridge

DB_PASSWORD=super-secret-password-123-change-this

Part 4: Understanding Environment Variables and Secrets¶

Why Not Hardcode Passwords?¶

Wrong:

environment:
  - DATABASE_URL=postgresql://postgres:mypassword123@db:5432/mydb

Problems: - Password visible in git history - Same password in dev, staging, production - Easy to accidentally share - Hard to rotate

The Right Way¶

Step 1: Create .env file (on server only)

DB_PASSWORD=super-secure-random-password-here
API_KEY=another-secret-key

Add to .gitignore:

.env
.env.local
*.pem
node_modules/
__pycache__/

Step 2: Reference in docker-compose.yml

services:
  app:
    environment:
      - DATABASE_URL=postgresql://postgres:${DB_PASSWORD}@db:5432/mydb

Docker will substitute ${DB_PASSWORD} from .env file.

Step 3: Deploy with GitHub Actions

Create .env on server after cloning:

ssh user@server
cd /opt/apps/my-app
cat > .env << EOF
DB_PASSWORD=$(openssl rand -base64 32)
EOF

Generating Strong Passwords¶

On server:

# Generate random password
openssl rand -base64 32

# Example output:
# aX7kL9mP2qW5rT8uV3yZ1bC4dE6fG7hI0jK2lM4nO6pQ8rS

Part 5: Volumes and Data Persistence¶

Understanding Volumes¶

Without volumes (data lost on restart):

Container runs → Data stored in container → Container deleted → Data gone

With volumes (data persists):

Container runs → Data stored in volume → Container deleted → Data in volume

Volume Configuration¶

services:
  db:
    volumes:
      - postgres-data:/var/lib/postgresql/data
      # ^ This persists database data

volumes:
  postgres-data:  # Name of the volume

Explanation:

• postgres-data on left = volume name
• /var/lib/postgresql/data on right = container directory
• Docker maps container directory to persistent volume on host
• Data survives container restart/replacement

Backup and Restore¶

Backup database:

docker exec postgres-app-db pg_dump -U postgres appdb > backup.sql

Restore database:

docker exec -i postgres-app-db psql -U postgres appdb < backup.sql

Check volume usage:

docker volume ls
docker volume inspect postgres-data

Part 6: Deployment Steps¶

Step 1: Prepare Files¶

Your project structure:

my-app/
├── Dockerfile
├── docker-compose.yml
├── .env                    ← NOT IN GIT (add to .gitignore)
├── .gitignore
├── app.js (or app.py)
├── package.json (or requirements.txt)
└── init.sql (optional, for database schema)

Step 2: Create .env File on Server¶

SSH into server:

ssh user@your-server
cd /opt/apps/my-app

Generate strong password:

openssl rand -base64 32

Create .env:

cat > .env << 'EOF'
DB_PASSWORD=paste-your-generated-password-here
EOF

chmod 600 .env  # Only owner can read

Step 3: Test Locally First (Recommended)¶

On your computer:

# Create .env for local testing
echo "DB_PASSWORD=test-password" > .env

# Start all services
docker compose up -d

# Wait 10 seconds for database to initialize
sleep 10

# Check status
docker compose ps

# Verify database is ready
docker compose logs db | grep "listening"

# Test application
curl http://localhost:3000/health
curl http://localhost:3000/health/db

# View all logs
docker compose logs

# Cleanup
docker compose down -v  # -v removes volumes

Step 4: Deploy to Server¶

Push to GitHub:

git add .
git commit -m "Add database deployment"
git push

SSH to server:

ssh user@your-server
cd /opt/apps/my-app
git pull origin main

Start services:

docker compose up -d

# Wait for database to be ready (usually 5-10 seconds)
sleep 10

# Check status
docker compose ps

Part 7: Verification Commands¶

Check All Services¶

# List all containers in stack
docker compose ps

# Expected output:
# postgres-app     - running, healthy
# postgres-app-db - running

Verify Database Connection¶

# Test database from app container
docker compose exec app curl http://127.0.0.1:3000/health/db

# Should show: {"status": "healthy", "database": "connected"}

View Database Directly¶

PostgreSQL:

docker compose exec db psql -U postgres -d appdb -c "SELECT * FROM users;"

MongoDB:

docker compose exec db mongosh -u admin -p --authenticationDatabase admin appdb

Monitor Logs¶

# All logs
docker compose logs

# App logs only
docker compose logs app

# Database logs only
docker compose logs db

# Real-time logs
docker compose logs -f

# Last 50 lines
docker compose logs --tail 50

Resource Usage¶

# CPU and memory per container
docker stats

# Disk usage
docker system df

# Volume details
docker volume inspect postgres-data

Part 8: Common Mistakes & Solutions¶

⚠️ Mistake 1: Database Password in docker-compose.yml¶

Wrong:

environment:
  - POSTGRES_PASSWORD=hardcoded-password-123

Correct:

environment:
  - POSTGRES_PASSWORD=${DB_PASSWORD}

With .env:

DB_PASSWORD=strong-random-password

⚠️ Mistake 2: Wrong Connection String¶

Wrong:

DATABASE_URL=postgresql://postgres:password@localhost:5432/mydb

Why: App is in different container, localhost doesn't work.

Correct:

DATABASE_URL=postgresql://postgres:password@db:5432/mydb

Service name db from docker-compose.yml is the hostname.

⚠️ Mistake 3: Exposing Database to Internet¶

Wrong:

db:
  ports:
    - "5432:5432"  # Never do this!

Problem: Database exposed to internet, security risk.

Correct:

db:
  # No ports section
  networks:
    - internal  # Only accessible to app

⚠️ Mistake 4: Missing Volume for Database¶

Wrong:

db:
  image: postgres:15-alpine
  # No volumes!

Problem: Data lost if container restarts.

Correct:

db:
  image: postgres:15-alpine
  volumes:
    - postgres-data:/var/lib/postgresql/data

volumes:
  postgres-data:

⚠️ Mistake 5: App Starts Before Database is Ready¶

Wrong:

services:
  app:
    depends_on:
      - db

Problem: depends_on only waits for container to start, not for database to be ready.

Better:

services:
  app:
    depends_on:
      - db
    # App includes retry logic in startup code

Best (in application code):

# Retry connecting to database
import time
for i in range(30):
    try:
        connection = connect_to_database()
        break
    except:
        if i == 29:
            raise
        time.sleep(1)

⚠️ Mistake 6: Using Same Password Everywhere¶

Wrong:

Development: password123
Staging: password123
Production: password123

Correct:

Development: test-password
Staging: strong-staging-password
Production: different-strong-password (generated with openssl)

Part 9: Scaling Beyond One Instance¶

When you're ready to run multiple app instances with one database:

version: '3.8'

services:
  app1:
    build: .
    environment:
      - DATABASE_URL=postgresql://postgres:${DB_PASSWORD}@db:5432/appdb
    depends_on:
      - db
    # ... labels and networks

  app2:
    build: .
    environment:
      - DATABASE_URL=postgresql://postgres:${DB_PASSWORD}@db:5432/appdb
    depends_on:
      - db
    # ... labels and networks

  db:
    image: postgres:15-alpine
    environment:
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    volumes:
      - postgres-data:/var/lib/postgresql/data

Both app instances share the same database and data.

AI Prompts for This Lesson¶

Designing Database Schema¶

I'm building a [describe your app, e.g., "blog with posts, comments, and users"] and need database schema design.

Requirements:
- Database type: PostgreSQL (or MongoDB/MySQL)
- Main entities: [list your data models, e.g., "users, posts, comments, tags"]
- Relationships: [describe connections, e.g., "users have many posts, posts have many comments"]
- Expected scale: [e.g., "1000 users, 10000 posts"]

Provide:
1. Optimized schema design
2. Index recommendations
3. Migration script (SQL or Mongoose schema)

Debugging Database Connection¶

My application can't connect to the database container.

Database: [PostgreSQL/MongoDB/MySQL]

docker-compose.yml database service:
[paste your database service config]

Application connection string:
[paste your DATABASE_URL or connection code]

Error from `docker compose logs app`:
[paste error]

Error from `docker compose logs db`:
[paste error]

Network configuration:
[paste your networks section from docker-compose.yml]

Help me diagnose and fix the connection issue.

Database Migrations and Schema Changes¶

I need to update my database schema without losing data.

Current schema:
[paste current table definitions or collection structure]

Desired changes:
- [e.g., "Add 'role' column to users table"]
- [e.g., "Create new 'categories' table"]
- [e.g., "Add index on email field"]

Database: [PostgreSQL/MongoDB/MySQL]

Provide:
1. Safe migration steps
2. Rollback strategy
3. Data preservation approach
4. How to run migration in Docker container

Database Backup and Restore¶

I need to set up automated backups for my database.

Database: [PostgreSQL/MongoDB/MySQL]
Container name: [from docker-compose.yml]

Requirements:
- Backup frequency: [daily/weekly]
- Retention: [how long to keep backups]
- Storage location: [local/cloud]

Provide:
1. Backup command for my database type
2. Automated backup script
3. Restore procedure
4. How to verify backup integrity

Performance Issues and Optimization¶

My database queries are slow and affecting application performance.

Database: [PostgreSQL/MongoDB/MySQL]

Slow query example:
[paste your query]

Table/Collection structure:
[paste schema]

Current resource usage (from `docker stats`):
[paste output]

Number of records: [approximate count]

Help me:
1. Identify bottlenecks
2. Add appropriate indexes
3. Optimize the query
4. Adjust Docker resource limits if needed

Environment Variables and Secrets¶

I need to properly handle database credentials in my deployment.

Current setup:
[paste relevant docker-compose.yml sections]

Questions:
- How to generate secure passwords?
- How to use .env file correctly?
- How to pass credentials to application?
- How to rotate passwords without downtime?

Database: [PostgreSQL/MongoDB/MySQL]
Deployment: [Docker Compose on single server]

Data Persistence and Volume Issues¶

My database data disappeared after container restart.

docker-compose.yml volumes section:
[paste volumes configuration]

Output of `docker volume ls`:
[paste output]

Output of `docker compose ps`:
[paste output]

Database: [PostgreSQL/MongoDB/MySQL]

Help me:
1. Understand what went wrong
2. Configure volumes correctly
3. Prevent data loss in future
4. Recover data if possible

Real Examples¶

See complete examples in egygeeks-docs repository:

• Node.js + PostgreSQL: /examples/nodejs-postgres/
• Python + MongoDB: /examples/python-mongodb/
• Django + PostgreSQL: /examples/django-postgres/

Troubleshooting¶

Application can't connect to database¶

Diagnosis:

docker compose logs app
docker compose logs db

Checklist: - [ ] Service name in connection string matches docker-compose.yml - [ ] Both containers on same network - [ ] Database password matches in both places - [ ] Database is actually running: docker compose ps

Database initialization failed¶

Check logs:

docker compose logs db

If using init.sql: - Ensure init.sql is properly formatted SQL - Check volume mount path

Data disappeared after restart¶

Ensure volume exists:

docker volume ls | grep postgres-data
docker volume inspect postgres-data

Check docker-compose.yml has:

volumes:
  - postgres-data:/var/lib/postgresql/data

volumes:
  postgres-data:

"Disk space full" error¶

Check disk usage:

df -h
docker system df

Clean up old volumes/images:

docker system prune -a

What's Next¶

After completing this lesson:

1. ✅ You've deployed applications with databases
2. ✅ You understand multi-container architecture
3. ✅ You can manage persistent data with volumes
4. ✅ You know how to handle secrets safely

Next Step: Lesson 3: Static Sites →

Learn how to deploy static content, documentation sites, and single-page applications.

Need Help?¶

Docker Compose Reference¶

docker compose up -d       # Start all services
docker compose ps          # Check status
docker compose logs -f     # View logs
docker compose exec db ... # Execute command in container
docker compose down        # Stop all services

Security Checklist¶

• .env is in .gitignore
• Database not exposed on ports
• Using strong passwords (openssl rand -base64 32)
• Different passwords per environment
• Volumes configured for database

File Checklist¶

• Dockerfile (application)
• docker-compose.yml (multi-container config)
• .env (on server only, not in git)
• init.sql (optional database schema)
• Application code with database connection logic
• .gitignore (excludes .env and dependencies)

← Back to Module Overview Continue to Lesson 3: Static Sites →