Database patterns

Skill taipt1504/agent-skills/skills/database-patterns

Database patterns for Java Spring — R2DBC (reactive), JPA/Hibernate (servlet), PostgreSQL, MySQL, connection pooling (HikariCP/r2dbc-pool), Flyway migrations, and query optimization. Use when writing @Entity or R2DBC models, creating Repository interfaces, writing @Query methods, planning Flyway migrations, tuning connection pools, or reviewing SQL performance. Includes scripts/validate-migration.sh for migration file validation.From its SKILL.md

Install

npx -y skills add taipt1504/agent-skills --skill database-patterns

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

2 things to look at

no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
3 stars3 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

5.3 KB, as published. Nobody here has run it

Database Patterns

Stack Decision

Criterion	JPA / HikariCP	R2DBC / R2DBC Pool
Runtime	Spring MVC (blocking)	Spring WebFlux (reactive)
Repository	`JpaRepository`	`ReactiveCrudRepository`
Transaction	`@Transactional`	`@Transactional` + `TransactionalOperator`
PG driver	`org.postgresql:postgresql`	`org.postgresql:r2dbc-postgresql`
MySQL driver	`com.mysql.cj.jdbc.Driver`	`io.asyncer:r2dbc-mysql`

Engine Differences

Aspect	PostgreSQL	MySQL
PK strategy	`GENERATED ALWAYS AS IDENTITY` / `SEQUENCE`	`BIGINT UNSIGNED AUTO_INCREMENT`
JPA `@GeneratedValue`	`SEQUENCE` + `@SequenceGenerator`	`IDENTITY`
Strings	`text` (no storage diff vs varchar)	`VARCHAR(n)` + `utf8mb4` required
Timestamps	`timestamptz` (always TZ-aware)	`DATETIME(3)` (store in UTC)
Covering index	`INCLUDE (col)`	List all cols in key (no INCLUDE)
Partial index	`WHERE` clause on index	Not supported (use composite)
Concurrent index	`CREATE INDEX CONCURRENTLY`	`ALGORITHM=INPLACE, LOCK=NONE`
Transactional DDL	Yes	No (implicit commit)
Default isolation	`READ_COMMITTED`	`REPEATABLE_READ` (gap locks)

Critical Rules (Both Engines)

@SQLRestriction not @Where — Hibernate 6+
FetchType.LAZY always — JOIN FETCH / @EntityGraph at call site
No .block() in reactive — use R2DBC on WebFlux
open-in-view: false — always disable
Parameterized queries only — never concatenate user input
Index all FK columns
Pool size = vCPU * 2 + 1 (SSD); smaller pools outperform oversized
DTO projections for read-only — skip entity lifecycle overhead

Migration Safety (Expand-Contract)

Expand — add nullable column; code writes both old+new
Migrate — backfill in batches (1000 rows), not single UPDATE
Contract — add NOT NULL / drop old column in separate deploy

Always safe: add nullable column, index (CONCURRENTLY/INPLACE), add table. Never in prod: DROP without expand-contract, ALTER to smaller type, TRUNCATE.

Verification Checklist

No @Data on entities; use @Getter + @NoArgsConstructor(PROTECTED) + builder
@EntityGraph / JOIN FETCH for known N+1 paths
DTO projections for read-only queries
@Transactional(readOnly = true) on query methods
HikariCP leak detection enabled in non-prod
Migrations follow V{n}__{description}.sql naming
Large table changes use online DDL or batch strategy
Migration tested with Testcontainers

References

File	Contents
postgresql.md	Schema, indexing (B-tree/GIN/GiST/BRIN/covering/partial), UPSERT, keyset pagination, SKIP LOCKED, RLS, JPA config, R2DBC config, pool sizing, Flyway
mysql.md	Schema, indexing (covering without INCLUDE, invisible, generated columns), utf8mb4, JPA config, R2DBC asyncer driver, pool sizing, Flyway
jpa-hibernate.md	Entity design, N+1 prevention (EntityGraph/JOIN FETCH/DTO projections), HikariCP config, pagination (offset + cursor), batch writes, specifications
r2dbc.md	ReactiveCrudRepository, R2dbcEntityTemplate, DatabaseClient, reactive transactions, pool config, Testcontainers testing
migrations.md	Flyway naming, expand-contract, safety rules, large table strategies (PG vs MySQL), Testcontainers validation

Related Skills

summer-data — R2DBC converters, audit_log/outbox_events DDL, AuditTableValidator
spring-webflux-patterns — JPA (MVC) or R2DBC (WebFlux) stack selection
redis-patterns — Cache-aside to reduce DB load
testing-workflow — Testcontainers for DB integration tests, @DataR2dbcTest/@DataJpaTest