> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Celaya55/app-cr/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Setup

> Configure PostgreSQL database connection and environment for App CR

## Overview

App CR uses PostgreSQL 15 as the database, managed through Docker Compose for development and Prisma ORM for database operations.

## Prerequisites

<CardGroup cols={2}>
  <Card title="Docker & Docker Compose" icon="docker">
    Required to run PostgreSQL container
  </Card>

  <Card title="Node.js & npm" icon="node">
    Required to run Prisma CLI commands
  </Card>
</CardGroup>

***

## Quick Start

<Steps>
  <Step title="Start PostgreSQL">
    Launch the PostgreSQL container:

    ```bash theme={null}
    cd ~/workspace/source
    docker-compose up -d postgres
    ```

    Verify the container is running:

    ```bash theme={null}
    docker-compose ps
    ```
  </Step>

  <Step title="Configure Environment">
    Create `.env` file in the backend directory:

    ```bash theme={null}
    cd ~/workspace/source/backend
    touch .env
    ```

    Add the database connection URL:

    ```bash theme={null}
    DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"
    ```
  </Step>

  <Step title="Run Migrations">
    Apply database schema:

    ```bash theme={null}
    npx prisma migrate deploy
    ```
  </Step>

  <Step title="Generate Prisma Client">
    Generate TypeScript types:

    ```bash theme={null}
    npx prisma generate
    ```
  </Step>
</Steps>

***

## Docker Configuration

### PostgreSQL Container

The `docker-compose.yml` defines the PostgreSQL service:

```yaml theme={null}
services:
  postgres:
    image: postgres:15
    restart: always
    environment:
      POSTGRES_USER: user_admin
      POSTGRES_PASSWORD: password123
      POSTGRES_DB: mi_db_crud
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:
```

### Configuration Details

<ParamField path="image" type="string" default="postgres:15">
  PostgreSQL version 15 official Docker image
</ParamField>

<ParamField path="restart" type="string" default="always">
  Automatically restart container if it stops
</ParamField>

<ParamField path="POSTGRES_USER" type="string" default="user_admin" required>
  Database superuser username
</ParamField>

<ParamField path="POSTGRES_PASSWORD" type="string" default="password123" required>
  Database superuser password

  <Warning>Change this in production environments</Warning>
</ParamField>

<ParamField path="POSTGRES_DB" type="string" default="mi_db_crud" required>
  Initial database name
</ParamField>

<ParamField path="ports" type="array" default="5432:5432">
  Maps container port 5432 to host port 5432
</ParamField>

<ParamField path="volumes" type="array">
  Persists database data in Docker volume `postgres_data`
</ParamField>

### Docker Commands

<CodeGroup>
  ```bash Start Database theme={null}
  # Start PostgreSQL in background
  docker-compose up -d postgres
  ```

  ```bash Stop Database theme={null}
  # Stop PostgreSQL container
  docker-compose stop postgres
  ```

  ```bash View Logs theme={null}
  # View PostgreSQL logs
  docker-compose logs postgres

  # Follow logs in real-time
  docker-compose logs -f postgres
  ```

  ```bash Restart Database theme={null}
  # Restart PostgreSQL container
  docker-compose restart postgres
  ```

  ```bash Remove Database theme={null}
  # Stop and remove container (keeps volume)
  docker-compose down

  # Remove container AND volume (deletes all data)
  docker-compose down -v
  ```
</CodeGroup>

***

## Environment Variables

### DATABASE\_URL Format

The `DATABASE_URL` follows PostgreSQL connection string format:

```bash theme={null}
DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"
```

### Default Configuration

```bash theme={null}
DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"
```

### URL Components

<ResponseField name="Protocol" type="string" required>
  `postgresql://` - PostgreSQL protocol identifier
</ResponseField>

<ResponseField name="User" type="string" default="user_admin" required>
  Database username from `POSTGRES_USER`
</ResponseField>

<ResponseField name="Password" type="string" default="password123" required>
  Database password from `POSTGRES_PASSWORD`
</ResponseField>

<ResponseField name="Host" type="string" default="localhost" required>
  Database host

  * `localhost` for local development
  * `postgres` when connecting from another Docker container
  * Production hostname/IP in deployment
</ResponseField>

<ResponseField name="Port" type="number" default="5432" required>
  PostgreSQL port (default: 5432)
</ResponseField>

<ResponseField name="Database" type="string" default="mi_db_crud" required>
  Database name from `POSTGRES_DB`
</ResponseField>

<ResponseField name="Schema" type="string" default="public">
  Optional schema name (defaults to `public`)

  Example: `?schema=app_schema`
</ResponseField>

### Environment File Location

```bash theme={null}
~/workspace/source/backend/.env
```

<Warning>
  Never commit `.env` files to version control. Add to `.gitignore`:

  ```gitignore theme={null}
  .env
  .env.local
  .env.*.local
  ```
</Warning>

***

## Database Connection

### Using Prisma Client

<CodeGroup>
  ```typescript Initialize Client theme={null}
  // backend/src/db.ts
  import { PrismaClient } from '@prisma/client';

  const prisma = new PrismaClient({
    log: ['query', 'error', 'warn'],
  });

  export default prisma;
  ```

  ```typescript Query Example theme={null}
  import prisma from './db';

  // Fetch all users with their tasks
  const users = await prisma.user.findMany({
    include: {
      tasks: true,
    },
  });
  ```

  ```typescript Create Record theme={null}
  import prisma from './db';

  // Create new user
  const newUser = await prisma.user.create({
    data: {
      email: 'user@example.com',
      password: 'hashed_password',
    },
  });
  ```

  ```typescript Transaction Example theme={null}
  import prisma from './db';

  // Create user and task in transaction
  const result = await prisma.$transaction([
    prisma.user.create({
      data: {
        email: 'user@example.com',
        password: 'hashed_password',
      },
    }),
    prisma.task.create({
      data: {
        title: 'First Task',
        userId: 1,
      },
    }),
  ]);
  ```
</CodeGroup>

### Connection Pooling

Prisma automatically manages connection pooling. Configure pool size via environment:

```bash theme={null}
DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud?connection_limit=10"
```

<ParamField path="connection_limit" type="number" default="10">
  Maximum number of database connections in the pool
</ParamField>

***

## Testing Database Connection

### Using Prisma Studio

Open the interactive database browser:

```bash theme={null}
cd ~/workspace/source/backend
npx prisma studio
```

Access at: `http://localhost:5555`

### Using psql CLI

Connect directly to PostgreSQL:

```bash theme={null}
# Using docker-compose exec
docker-compose exec postgres psql -U user_admin -d mi_db_crud

# Or using psql if installed locally
psql postgresql://user_admin:password123@localhost:5432/mi_db_crud
```

### Test Queries

<CodeGroup>
  ```sql List Tables theme={null}
  -- View all tables
  \dt

  -- Expected output:
  -- User
  -- Task
  -- _prisma_migrations
  ```

  ```sql Describe Table theme={null}
  -- View User table structure
  \d "User"

  -- View Task table structure
  \d "Task"
  ```

  ```sql Sample Query theme={null}
  -- Count users
  SELECT COUNT(*) FROM "User";

  -- Count tasks
  SELECT COUNT(*) FROM "Task";
  ```

  ```sql Check Migrations theme={null}
  -- View applied migrations
  SELECT * FROM _prisma_migrations;
  ```
</CodeGroup>

***

## Production Setup

### Environment Configuration

For production, use secure environment variables:

```bash theme={null}
DATABASE_URL="postgresql://prod_user:SECURE_PASSWORD@prod-host.example.com:5432/app_cr_prod?sslmode=require"
```

<ParamField path="sslmode" type="string" default="require">
  SSL/TLS mode for encrypted connections

  Values:

  * `disable` - No SSL (development only)
  * `require` - Require SSL
  * `verify-ca` - Require and verify CA
  * `verify-full` - Full certificate verification
</ParamField>

### Security Best Practices

<AccordionGroup>
  <Accordion title="Use strong passwords" icon="lock">
    * Minimum 16 characters
    * Mix of uppercase, lowercase, numbers, symbols
    * Use password manager or secrets management service
  </Accordion>

  <Accordion title="Enable SSL/TLS" icon="shield">
    Always use encrypted connections in production:

    ```bash theme={null}
    DATABASE_URL="...?sslmode=require"
    ```
  </Accordion>

  <Accordion title="Restrict database access" icon="user-lock">
    * Use firewall rules to limit access
    * Create application-specific database user (not superuser)
    * Grant minimal required permissions
  </Accordion>

  <Accordion title="Use secrets management" icon="key">
    Store `DATABASE_URL` in:

    * AWS Secrets Manager
    * HashiCorp Vault
    * GitHub Secrets (for CI/CD)
    * Never hardcode in application code
  </Accordion>

  <Accordion title="Enable connection pooling" icon="network-wired">
    Use connection pooler like PgBouncer for high-traffic applications:

    ```bash theme={null}
    DATABASE_URL="postgresql://user:pass@pgbouncer:6432/db?pgbouncer=true"
    ```
  </Accordion>
</AccordionGroup>

***

## Troubleshooting

### Connection Refused

**Error:** `ECONNREFUSED 127.0.0.1:5432`

<Steps>
  <Step title="Check PostgreSQL is running">
    ```bash theme={null}
    docker-compose ps postgres
    ```
  </Step>

  <Step title="Start PostgreSQL if stopped">
    ```bash theme={null}
    docker-compose up -d postgres
    ```
  </Step>

  <Step title="Verify port mapping">
    ```bash theme={null}
    docker-compose port postgres 5432
    # Should output: 0.0.0.0:5432
    ```
  </Step>
</Steps>

### Authentication Failed

**Error:** `password authentication failed for user`

<Steps>
  <Step title="Verify credentials">
    Check `.env` file matches `docker-compose.yml`:

    * User: `user_admin`
    * Password: `password123`
    * Database: `mi_db_crud`
  </Step>

  <Step title="Recreate container if changed">
    ```bash theme={null}
    docker-compose down
    docker-compose up -d postgres
    ```
  </Step>
</Steps>

### Database Does Not Exist

**Error:** `database "mi_db_crud" does not exist`

<Steps>
  <Step title="Check database name in URL">
    Ensure `DATABASE_URL` uses correct database name:

    ```bash theme={null}
    DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"
    ```
  </Step>

  <Step title="Create database manually">
    ```bash theme={null}
    docker-compose exec postgres createdb -U user_admin mi_db_crud
    ```
  </Step>
</Steps>

### Schema Out of Sync

**Error:** `The database schema is not in sync with your Prisma schema`

<Steps>
  <Step title="Run migrations">
    ```bash theme={null}
    npx prisma migrate deploy
    ```
  </Step>

  <Step title="Regenerate client">
    ```bash theme={null}
    npx prisma generate
    ```
  </Step>
</Steps>

***

## Next Steps

<CardGroup cols={2}>
  <Card title="Database Schema" icon="database" href="/database/schema">
    Explore the complete database schema
  </Card>

  <Card title="Run Migrations" icon="arrows-rotate" href="/database/migrations">
    Learn how to manage database migrations
  </Card>
</CardGroup>