Spaces:
Running
Running
File size: 3,780 Bytes
0a96199 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
#!/usr/bin/env python3
"""
Database setup script for Booknap
Run this script to create the necessary database tables
"""
import os
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError
def setup_database():
"""Set up the database schema"""
# Database configuration - update with your actual password
DATABASE_URL = os.environ.get(
"DATABASE_URL",
"postgresql://postgres:YOUR_ACTUAL_PASSWORD@db.ckrqyjfdifjbsuuofegd.supabase.co:5432/postgres"
)
print("Connecting to database...")
print(f"URL: {DATABASE_URL.replace('YOUR_ACTUAL_PASSWORD', '***')}")
try:
# Create engine
engine = create_engine(DATABASE_URL, pool_pre_ping=True)
# Test connection
with engine.connect() as conn:
print("✅ Database connection successful!")
# Create users table
create_users_table = """
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
full_name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"""
# Create index
create_index = """
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
"""
# Execute schema creation
print("Creating users table...")
conn.execute(text(create_users_table))
print("Creating email index...")
conn.execute(text(create_index))
# Commit changes
conn.commit()
print("✅ Database schema created successfully!")
# Verify table exists
result = conn.execute(text("SELECT table_name FROM information_schema.tables WHERE table_name = 'users'"))
if result.fetchone():
print("✅ Users table verified!")
# Show table structure
result = conn.execute(text("\\d users"))
print("\nTable structure:")
# Note: \d command won't work in Python, let's use a different approach
result = conn.execute(text("SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position"))
columns = result.fetchall()
for col in columns:
print(f" {col[0]}: {col[1]} ({'NULL' if col[2] == 'YES' else 'NOT NULL'})")
else:
print("❌ Users table not found!")
except SQLAlchemyError as e:
print(f"❌ Database error: {e}")
print("\nPlease check:")
print("1. Your database password is correct")
print("2. The database is accessible from your network")
print("3. Update the DATABASE_URL in this script or set the environment variable")
except Exception as e:
print(f"❌ Unexpected error: {e}")
if __name__ == "__main__":
print("🚀 Booknap Database Setup")
print("=" * 40)
# Check if DATABASE_URL is set
if not os.environ.get("DATABASE_URL"):
print("⚠️ DATABASE_URL environment variable not set")
print("Please set it with your actual database password:")
print("export DATABASE_URL='postgresql://postgres:YOUR_PASSWORD@db.ckrqyjfdifjbsuuofegd.supabase.co:5432/postgres'")
print()
setup_database()
print("\n" + "=" * 40)
print("Setup complete!")
|