app.py

from fastapi import FastAPI, HTTPException
import psycopg2
from psycopg2.extras import RealDictCursor
import os

app = FastAPI()

# Database connection details
DB_NAME = os.getenv("POSTGRES_DB", "mydatabase")
DB_USER = os.getenv("POSTGRES_USER", "myuser")
DB_PASSWORD = os.getenv("POSTGRES_PASSWORD", "mypassword")
DB_HOST = os.getenv("POSTGRES_HOST", "localhost")
DB_PORT = os.getenv("POSTGRES_PORT", "5432")

def get_db_connection():
    try:
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT,
            cursor_factory=RealDictCursor  # So we can return rows as dictionaries
        )
        return conn
    except Exception as e:
        print(f"Error connecting to the database: {e}")
        raise HTTPException(status_code=500, detail="Database connection error")

def create_table():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS items (
            id SERIAL PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            description TEXT
        );
    """)
    conn.commit()
    cursor.close()
    conn.close()
    
    
@app.get("/")
def read_root():
    return {"message": "Welcome to FastAPI with PostgreSQL!"}

@app.get("/items/")
def read_items():
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("SELECT * FROM items;")
    except Exception as e:
        print(e)
        create_table()
        cursor.execute("SELECT * FROM items;")
    items = cursor.fetchall()
    cursor.close()
    conn.close()

    return items

@app.post("/items/")
def create_item(name: str, description: str = "description"):
    conn = get_db_connection()
    cursor = conn.cursor()
    create_table()
    
    try:
        cursor.execute(
            "INSERT INTO items (name, description) VALUES (%s, %s) RETURNING id;",
            (name, description)
        )
    except Exception as e:
        print(e)
        create_table()
        cursor.execute(
            "INSERT INTO items (name, description) VALUES (%s, %s) RETURNING id;",
            (name, description)
        )
    item_id = cursor.fetchone()['id']
    conn.commit()
    cursor.close()
    conn.close()

    return {"id": item_id, "name": name, "description": description}