Database: SQLite → PostgreSQL Migration

SQLite is great for development but doesn't handle concurrent users well. For production, switch to PostgreSQL.

Why Migrate?

Step 1: Install PostgreSQL Adapter

pip install psycopg2-binary

Add to requirements.txt:

psycopg2-binary

If using Docker, rebuild the image after adding the dependency:

cd aiproject/backend
docker compose build

Step 2: Update Database Configuration

The code already supports a DATABASE_URL environment variable. Modify backend/app/__init__.py to use it:

import os

def create_app():
    app = Flask(__name__)

    database_url = os.getenv("DATABASE_URL", "sqlite:///chat.db")
    # Some providers use postgres:// but SQLAlchemy needs postgresql://
    if database_url and database_url.startswith("postgres://"):
        database_url = database_url.replace("postgres://", "postgresql://", 1)

    app.config["SQLALCHEMY_DATABASE_URI"] = database_url
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    ...

When DATABASE_URL is not set (local development), it falls back to SQLite. You don't need to change anything locally.

Step 3: Set Up Supabase PostgreSQL

Supabase offers a generous free tier (500MB database, no sleep).

1. Go to https://supabase.com and sign up (GitHub login works)
2. Click New project
3. Enter:
4. Name: aiproject (or anything you like)
5. Database Password: Create a strong password (save it somewhere)
6. Region: Pick the closest to you
7. Pricing Plan: Free (don't worry, $0)
8. Wait ~2 minutes for the database to provision
9. Go to Project Settings → Database → Connection string
10. Copy the URI connection string. It looks like: postgresql://postgres:YOUR_PASSWORD@db.xxxxxxxxxxxxxx.supabase.co:6543/postgres Replace YOUR_PASSWORD with the password you created in step 3.

Step 4: Add DATABASE_URL to Hugging Face Spaces

1. Go to your Space: https://huggingface.co/spaces/YOUR_USERNAME/aiproject-backend
2. Click Settings → Repository Secrets
3. Add a new secret:
4. Name: DATABASE_URL
5. Value: Your Supabase connection string from Step 3
6. The Space will automatically restart with the new secret

Step 5: Verify

curl https://YOUR_USERNAME-aiproject-backend.hf.space/api/messages
# → [] (empty — fresh PostgreSQL database)

Your backend is now using Supabase PostgreSQL in production.

Step 6: Migrate Existing Data (Optional)

If you have chat messages in your local SQLite database that you want to keep:

# scripts/migrate_to_postgres.py
import sqlite3
import psycopg2
import os

# Connect to SQLite
sqlite_conn = sqlite3.connect("instance/chat.db")
sqlite_cursor = sqlite_conn.cursor()

# Connect to PostgreSQL (set DATABASE_URL to your Supabase connection string)
pg_conn = psycopg2.connect(os.getenv("DATABASE_URL"))
pg_cursor = pg_conn.cursor()

# Create table
pg_cursor.execute("""
    CREATE TABLE IF NOT EXISTS message (
        id SERIAL PRIMARY KEY,
        content VARCHAR(1000) NOT NULL,
        sender VARCHAR(20) NOT NULL,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
""")

# Copy data
sqlite_cursor.execute("SELECT content, sender, timestamp FROM message")
rows = sqlite_cursor.fetchall()

for row in rows:
    pg_cursor.execute(
        "INSERT INTO message (content, sender, timestamp) VALUES (%s, %s, %s)",
        row
    )

pg_conn.commit()
print(f"Migrated {len(rows)} messages")

sqlite_conn.close()
pg_conn.close()

Run it:

cd aiproject/backend
source venv/bin/activate
DATABASE_URL="postgresql://..." python scripts/migrate_to_postgres.py

Switching Back to SQLite Locally

Your local development environment still uses SQLite (no DATABASE_URL env var set). You don't need to change anything.