init_db.py

import os
import psycopg2
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Get the DATABASE_URL from environment variables
DATABASE_URL = os.getenv('DATABASE_URL')

# Check if the URL is set
if not DATABASE_URL:
    print("DATABASE_URL not found in environment variables. Please check your .env file.")
    exit()

def create_tables():
    """Connects to the database and creates the tables from schema.sql."""
    conn = None
    try:
        # Connect to your PostgreSQL database
        conn = psycopg2.connect(DATABASE_URL)
        cur = conn.cursor()

        # Read the SQL file
        with open('schema.sql', 'r') as f:
            sql_commands = f.read()

        # Execute the SQL commands
        cur.execute(sql_commands)

        # Commit the changes
        conn.commit()
        print("Tables created successfully!")

    except psycopg2.Error as e:
        print(f"Database error: {e}")
        if conn:
            conn.rollback() # Rollback in case of error

    finally:
        if conn:
            conn.close()

if __name__ == '__main__':
    create_tables()