Managing SQLite in Docker: A Complete Guide

This guide demonstrates how to effectively manage a SQLite database in a Docker environment, specifically for a Python FastAPI blogging project.

Table of Contents

• Project Structure
• Initial Setup
• Database Management
• Backup and Restore
• Common Operations
• Troubleshooting

Project Structure

/project
├── Dockerfile
├── docker-compose.yml
├── backup.sh
├── restore.sh
├── app/
│   ├── main.py
│   └── requirements.txt
├── data/
│   └── blog.db
└── backups/
    └── backup_20240311_123456.db

Initial Setup

1. Create the Dockerfile

FROM python:3.11-slim

WORKDIR /app

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

COPY app/ .

CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

2. Create docker-compose.yml

version: '3.8'

services:
  web:
    build: .
    ports:
      - "8000:8000"
    volumes:
      - ./data:/data
      - ./app:/app
    environment:
      - DATABASE_URL=sqlite:////data/blog.db
    command: sh -c "uvicorn main:app --host 0.0.0.0 --port 8000 --reload"

3. Create Directory Structure

mkdir -p app data backups
chmod +x backup.sh restore.sh

Database Management

Creating Backup Scripts

1. Create backup.sh

#!/bin/bash
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="./backups"

mkdir -p $BACKUP_DIR
docker-compose exec -T web sqlite3 /data/blog.db ".backup '/data/backup_${TIMESTAMP}.db'"
mv ./data/backup_${TIMESTAMP}.db $BACKUP_DIR/

echo "Backup created: backup_${TIMESTAMP}.db"

2. Create restore.sh

#!/bin/bash
if [ -z "$1" ]; then
    echo "Usage: ./restore.sh backup_filename.db"
    exit 1
fi

if [ ! -f "./backups/$1" ]; then
    echo "Backup file not found: $1"
    exit 1
fi

cp "./backups/$1" ./data/blog.db
echo "Database restored from $1"

Common Operations

Starting the Application

# Build and start containers
docker-compose up -d

# View logs
docker-compose logs -f

Database Operations

1. Create a Backup

./backup.sh

2. Restore from Backup

./restore.sh backup_20240311_123456.db

3. Run Database Migrations

# Using Alembic
docker-compose exec web python -m alembic upgrade head

# Direct SQL
docker-compose exec web sqlite3 /data/blog.db < update.sql

4. Access Database Shell

docker-compose exec web sqlite3 /data/blog.db

Database Access Methods

1. From Host Machine
   - Direct file access: ./data/blog.db
   - Use any SQLite tool pointing to this file

2. From Container
   - Database path: /data/blog.db
   - Access through FastAPI application
   - Use SQLite CLI tools within container

Troubleshooting

Common Issues

1. Permission Denied

# Fix permissions on data directory
chmod 777 data
chmod 666 data/blog.db

2. Database Locked
   - Ensure only one process is accessing the database
   - Check for hanging connections:

docker-compose exec web sqlite3 /data/blog.db "PRAGMA busy_timeout = 60000;"

3. Backup Failed
   - Verify sufficient disk space
   - Check file permissions
   - Ensure database is not corrupted:

docker-compose exec web sqlite3 /data/blog.db "PRAGMA integrity_check;"

Best Practices

1. Regular Backups
   - Schedule regular backups using cron
   - Keep multiple backup generations
   - Verify backup integrity regularly

2. Performance
   - Enable WAL mode for better concurrent access:

docker-compose exec web sqlite3 /data/blog.db "PRAGMA journal_mode=WAL;"

3. Security
   - Restrict access to the data directory
   - Regularly audit file permissions
   - Keep backups in a secure location

4. Monitoring
   - Monitor database size
   - Track backup success/failure
   - Set up alerts for failed operations

Further Enhancements

Consider implementing these additional features:

1. Automated Periodic Backups

# Add to crontab
0 */6 * * * /path/to/backup.sh

2. Backup Compression

# Modify backup.sh to include compression
gzip $BACKUP_DIR/backup_${TIMESTAMP}.db

3. Remote Backup Storage
   - Configure automatic upload to S3/GCS
   - Implement backup rotation
   - Set up monitoring and alerting

4. Backup Verification
   - Add integrity checks
   - Test restore procedures
   - Validate backup contents

Remember to adjust paths and permissions according to your specific setup and requirements.