# Database Credentials from Environment Variables

## Overview

jPOS now supports loading database credentials from environment variables instead of storing them in plain text in configuration files. This is a more secure approach, especially for production environments.

## How It Works

The `ApplicationProperties` class has been enhanced to check environment variables before falling back to the `cfg/application.properties` file. This allows you to:

1. Store default/development credentials in `cfg/application.properties`
2. Override them with environment variables in production
3. Keep sensitive credentials out of version control

## Supported Environment Variables

### Database Connection

| Environment Variable | Alternative Name | Property File Key | Description |
|---------------------|------------------|-------------------|-------------|
| `JPOS_DB_USER` | `DB_USERNAME` | `javax.persistence.jdbc.user` | Database username |
| `JPOS_DB_PASSWORD` | `DB_PASSWORD` | `javax.persistence.jdbc.password` | Database password |
| `JPOS_DB_URL` | - | `javax.persistence.jdbc.url` | JDBC connection URL |
| `JPOS_DB_DRIVER` | - | `javax.persistence.jdbc.driver` | JDBC driver class |
| `JPOS_DB_DIALECT` | - | `hibernate.dialect` | Hibernate dialect |
| `JPOS_DB_SHOW_SQL` | - | `hibernate.show_sql` | Show SQL statements |
| `JPOS_DB_FORMAT_SQL` | - | `hibernate.format_sql` | Format SQL statements |
| `JPOS_DB_HBM2DDL_AUTO` | - | `hibernate.hbm2ddl.auto` | Schema auto-generation |

### HikariCP Connection Pool Settings

| Environment Variable | Property File Key | Default | Description |
|---------------------|-------------------|---------|-------------|
| `JPOS_DB_POOL_SIZE` | `hibernate.hikari.maximumPoolSize` | 10 | Maximum pool size |
| `JPOS_DB_MIN_IDLE` | `hibernate.hikari.minimumIdle` | 5 | Minimum idle connections |
| `JPOS_DB_CONNECTION_TIMEOUT` | `hibernate.hikari.connectionTimeout` | 30000 | Connection timeout (ms) |
| `JPOS_DB_IDLE_TIMEOUT` | `hibernate.hikari.idleTimeout` | 600000 | Idle timeout (ms) - 10 minutes |
| `JPOS_DB_MAX_LIFETIME` | `hibernate.hikari.maxLifetime` | 1800000 | Max connection lifetime (ms) - 30 minutes |
| `JPOS_DB_LEAK_DETECTION` | `hibernate.hikari.leakDetectionThreshold` | 60000 | Leak detection threshold (ms) - 1 minute. Set to 0 to disable |

**Note**: HikariCP is automatically configured as the connection provider, eliminating the "Using Hibernate built-in connection pool" warning.

## Usage Examples

### Linux/Unix

```bash
# Set environment variables
export JPOS_DB_USER=mydbuser
export JPOS_DB_PASSWORD=mySecurePassword123
export JPOS_DB_URL=jdbc:mysql://production-db:3306/jpos_prod

# Optional: Configure connection pool for production
export JPOS_DB_POOL_SIZE=20
export JPOS_DB_MIN_IDLE=10

# Start Q2
./bin/q2
```

### Using systemd Service

Create a systemd service file with environment variables:

```ini
[Unit]
Description=jPOS Q2 Service
After=network.target

[Service]
Type=simple
User=jpos
WorkingDirectory=/opt/jpos
Environment="JPOS_DB_USER=produser"
Environment="JPOS_DB_PASSWORD=prodpassword"
Environment="JPOS_DB_URL=jdbc:mysql://db-server:3306/jpos_prod"
Environment="JPOS_DB_POOL_SIZE=20"
Environment="JPOS_DB_MIN_IDLE=10"
ExecStart=/opt/jpos/bin/q2

[Install]
WantedBy=multi-user.target
```

### Docker Container

```bash
docker run -d \
  -e JPOS_DB_USER=dbuser \
  -e JPOS_DB_PASSWORD=dbpassword \
  -e JPOS_DB_URL=jdbc:mysql://mysql-host:3306/jpos \
  jpos-app
```

Or with Docker Compose:

```yaml
version: '3'
services:
  jpos:
    image: jpos-app
    environment:
      - JPOS_DB_USER=dbuser
      - JPOS_DB_PASSWORD=dbpassword
      - JPOS_DB_URL=jdbc:mysql://mysql:3306/jpos
    depends_on:
      - mysql
  mysql:
    image: mysql:8
    environment:
      - MYSQL_ROOT_PASSWORD=rootpass
      - MYSQL_DATABASE=jpos
```

### Using Environment Files

Create a `.env` file (keep this file secure and don't commit to version control):

```bash
JPOS_DB_USER=myuser
JPOS_DB_PASSWORD=mySecurePassword
JPOS_DB_URL=jdbc:mysql://localhost:3306/jpos
```

Load it before starting:

```bash
source .env
./bin/q2
```

## Precedence Order

The system uses the following precedence (highest to lowest):

1. **Primary environment variable** (e.g., `JPOS_DB_USER`)
2. **Alternative environment variable** (e.g., `DB_USERNAME`)
3. **Properties file** (`cfg/application.properties`)
4. **persistence.xml defaults** (fallback if nothing else is set)

## HikariCP Connection Pool

### Overview

The application now uses HikariCP, a high-performance JDBC connection pool, instead of Hibernate's built-in pool. This eliminates the warning:
```
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
```

### Default Configuration

HikariCP is automatically configured with production-ready defaults:
- **Maximum Pool Size**: 10 connections
- **Minimum Idle**: 5 connections
- **Connection Timeout**: 30 seconds
- **Idle Timeout**: 10 minutes
- **Max Lifetime**: 30 minutes
- **Leak Detection Threshold**: 1 minute
  - Logs warnings when connections are held longer than threshold
  - Helps identify connection leaks in application code
  - Set to 0 to disable

### Tuning for Production

Adjust pool settings based on your application's needs:

```bash
# For high-traffic applications
export JPOS_DB_POOL_SIZE=50
export JPOS_DB_MIN_IDLE=20

# For low-traffic applications
export JPOS_DB_POOL_SIZE=5
export JPOS_DB_MIN_IDLE=2
```

Or in `application.properties`:
```properties
hibernate.hikari.maximumPoolSize=50
hibernate.hikari.minimumIdle=20
hibernate.hikari.connectionTimeout=30000
hibernate.hikari.idleTimeout=600000
hibernate.hikari.maxLifetime=1800000
hibernate.hikari.leakDetectionThreshold=60000  # 1 minute, set to 0 to disable
```

### Connection Pool Best Practices

1. **Set pool size based on load**: Monitor your application and adjust accordingly
2. **Don't over-provision**: More connections don't always mean better performance
3. **Monitor connection usage**: Use database monitoring tools to track active connections
4. **Consider database limits**: Ensure your database can handle the total connections from all app instances

## Security Best Practices

1. **Never commit credentials to version control**: Add `.env` files to `.gitignore`
2. **Use environment variables in production**: Keep production credentials out of configuration files
3. **Restrict file permissions**: If using properties files, ensure they're only readable by the application user
4. **Use secret management**: In cloud environments, use services like AWS Secrets Manager, Azure Key Vault, or HashiCorp Vault
5. **Rotate credentials regularly**: Make credential rotation part of your security policy

## Migration Guide

### From Hardcoded Credentials in persistence.xml

**Before:**
```xml
<property name="javax.persistence.jdbc.user" value="uatsqladmin" />
<property name="javax.persistence.jdbc.password" value="2TNpvWn3H7sQ" />
```

**After:**
1. Remove hardcoded credentials from `persistence.xml` (or leave them as defaults for development)
2. Set environment variables:
   ```bash
   export JPOS_DB_USER=uatsqladmin
   export JPOS_DB_PASSWORD=2TNpvWn3H7sQ
   ```

### From application.properties File

**Before:**
```properties
javax.persistence.jdbc.user=produser
javax.persistence.jdbc.password=prodpassword
```

**After:**
1. Keep default/development values in `application.properties`
2. Override with environment variables in production:
   ```bash
   export JPOS_DB_USER=produser
   export JPOS_DB_PASSWORD=prodpassword
   ```

## Backward Compatibility

This feature is **fully backward compatible**. Existing deployments using `cfg/application.properties` or hardcoded values in `persistence.xml` will continue to work without any changes. Environment variables are optional and only used when present.

## Troubleshooting

### Credentials Not Being Used

1. Verify environment variables are set:
   ```bash
   echo $JPOS_DB_USER
   echo $JPOS_DB_PASSWORD
   ```

2. Check that variables are exported:
   ```bash
   export JPOS_DB_USER=myuser
   ```

3. Ensure variables are available to the process that starts Q2

### Connection Errors

1. Check database connectivity:
   ```bash
   mysql -h hostname -u $JPOS_DB_USER -p$JPOS_DB_PASSWORD
   ```

2. Verify the JDBC URL format is correct

3. Check application logs for detailed error messages

## Additional Resources

- jPOS Documentation: http://jpos.org
- Hibernate Configuration: https://hibernate.org/orm/documentation/
- JDBC Connection URLs: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-jdbc-url-format.html