# Database Guide

> MySQL database development guide for the HMIS project. Use when working with database queries, SQL debugging, database schema investigation, MySQL performance tuning, credential management, or database connection troubleshooting.

- Skill: `majiayu000/database-guide` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds add majiayu000/database-guide`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/database-guide/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/database-guide

---


# MySQL Developer Guide

## Security Policy

- MySQL credentials MUST NEVER be committed to git
- Credentials stored in separate folder outside the project directory
- Windows: `C:\Credentials\credentials.txt`
- Linux/Mac: `~/.config/hmis/credentials.txt`

## Common Database Operations

### Structure Investigation
```sql
SHOW TABLES LIKE '%bill%';
DESCRIBE bill;
SHOW CREATE TABLE bill;
SELECT COUNT(*) FROM bill WHERE retired = 0;
```

### JPQL Query Patterns
```java
// Standard entity query
String jpql = "SELECT b FROM Bill b WHERE b.retired = false AND b.department = :dept";

// DTO constructor query (use findLightsByJpql)
String jpql = "SELECT new com.divudi.core.data.dto.MyDTO(b.id, b.name) FROM Bill b WHERE ...";
List<MyDTO> results = (List<MyDTO>) facade.findLightsByJpql(jpql, params, TemporalType.TIMESTAMP);

// Aggregate query
String jpql = "SELECT SUM(b.netTotal) FROM Bill b WHERE ...";
```

### Performance Tips
- Always include `b.retired = false` in queries
- Use indexed columns in WHERE clauses
- Use DTOs instead of full entities for display
- Limit result sets with pagination

## Persistence Configuration

- **Development**: Use hardcoded JNDI (`jdbc/rhDS`)
- **Before push**: Must use environment variables (`${JDBC_DATASOURCE}`)

For complete reference, read [developer_docs/database/mysql-developer-guide.md](../../developer_docs/database/mysql-developer-guide.md).