asle/DATABASE_SETUP.md

# Database Setup Guide

This guide helps you set up the PostgreSQL database for ASLE backend.

## Prerequisites

- PostgreSQL 15+ installed and running
- Access to create databases

## Quick Setup

### Option 1: Using Docker (Recommended)

```bash
# Start PostgreSQL container
docker-compose up -d postgres

# The database will be created automatically with:
# - User: asle
# - Password: asle_password
# - Database: asle
```

Update `backend/.env`:
```env
DATABASE_URL="postgresql://asle:asle_password@localhost:5432/asle?schema=public"
```

### Option 2: Local PostgreSQL

1. **Create database:**
   ```bash
   # As postgres superuser
   sudo -u postgres psql -c "CREATE DATABASE asle;"
   
   # Or create a user first
   sudo -u postgres psql -c "CREATE USER asle WITH PASSWORD 'your_password';"
   sudo -u postgres psql -c "CREATE DATABASE asle OWNER asle;"
   ```

2. **Update `backend/.env`:**
   ```env
   DATABASE_URL="postgresql://asle:your_password@localhost:5432/asle?schema=public"
   ```

### Option 3: Using Existing PostgreSQL

If you have PostgreSQL running with different credentials:

1. **Create database:**
   ```bash
   psql -U your_user -c "CREATE DATABASE asle;"
   ```

2. **Update `backend/.env`:**
   ```env
   DATABASE_URL="postgresql://your_user:your_password@localhost:5432/asle?schema=public"
   ```

## Running Migrations

After setting up the database:

```bash
cd backend

# Generate Prisma client
npm run prisma:generate

# Run migrations
npm run prisma:migrate

# Initialize database with default configs
npm run setup:db

# Create admin user
npm run setup:admin
```

## Troubleshooting

### Authentication Failed

If you see `Authentication failed`:

1. Check PostgreSQL is running:
   ```bash
   pg_isready -h localhost -p 5432
   ```

2. Verify credentials in `backend/.env`

3. Test connection:
   ```bash
   psql $DATABASE_URL -c "SELECT version();"
   ```

### Database Already Exists

If database already exists, migrations will still work:
```bash
npm run prisma:migrate
```

### Permission Denied

If you get permission errors:
```bash
# Grant permissions
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE asle TO your_user;"
```

## Connection String Format

```
postgresql://[user]:[password]@[host]:[port]/[database]?schema=public
```

Examples:
- `postgresql://postgres@localhost:5432/asle?schema=public` (no password)
- `postgresql://asle:password@localhost:5432/asle?schema=public` (with password)
- `postgresql://user:pass@db.example.com:5432/asle?schema=public` (remote)

## Next Steps

After database is set up:
1. ✅ Run migrations
2. ✅ Initialize database
3. ✅ Create admin user
4. ✅ Start backend: `npm run dev`