SQL best practices
Help teams create maintainable, secure, and performant SQL for Java applications across relational database engines.
What is covered in this Skill?
- Naming conventions for tables, columns, indexes, constraints, views, triggers, and routines
- Relational table design: keys, constraints, timestamps, soft deletes, normalization, and dialect-aware data types
- Query writing: readable formatting, explicit column lists, joins, CTEs, bind parameters, and execution-plan review
- Index strategy: foreign keys, filter/sort paths, composite indexes, over-indexing risks, and index lifecycle
- Security, transactions, migrations, testing, and monitoring practices for production database work
Scope: Framework-agnostic SQL quality. For Java data-access APIs, defer to the matching JDBC, Spring Data JDBC, Panache, or Micronaut Data skill.
Constraints
Keep recommendations at the SQL and database-design layer unless the user explicitly asks for Java framework integration. After editing this repository's XML sources, regenerate skills and verify the build.
- MANDATORY: Run
./mvnw compile or mvn compile before proposing Java or Maven changes in the same change set
- SQL INJECTION: Never concatenate untrusted input into SQL strings; use bind parameters through the application's data-access API
- DIALECTS: Call out database-specific syntax and portability trade-offs when using PostgreSQL, MySQL, Oracle, SQL Server, H2, or another engine
- FRAMEWORK: Defer Spring JDBC to
@311-frameworks-spring-jdbc, Quarkus JDBC to @411-frameworks-quarkus-jdbc, Micronaut JDBC to @511-frameworks-micronaut-jdbc, and framework-specific migrations to the matching Flyway skill
- VERIFY: Run
./mvnw clean verify or mvn clean verify before promoting changes
- EDGE CASE: If the target database dialect, migration tool, or production constraint is missing and affects the recommendation, ask a clarifying question before editing SQL
- EDGE CASE: If requested changes conflict with data safety, backwards compatibility, or zero-downtime deployment constraints, explain the trade-off and ask for confirmation
When to use this skill
- Review SQL schema or migrations
- Improve SQL query performance and readability
- Design relational tables and indexes
- Review database transaction, security, or monitoring practices
- Apply SQL best practices to database DDL or DML
Workflow
- Read reference and assess database context
Read references/704-technologies-sql.md and inspect current schema, migrations, SQL queries, tests, and database configuration before proposing changes.
- Identify dialect and operational constraints
Confirm the target database engine, migration tool, data volume expectations, availability requirements, and compatibility constraints that shape the SQL design.
- Apply SQL-aligned changes
Implement or refactor SQL artifacts following the reference patterns and project conventions, keeping Java framework integration out of scope unless explicitly requested.
- Run verification and report results
Execute appropriate build, migration, SQL validation, and test checks; summarize what changed, what was verified, and any remaining database risks.
Reference
For detailed guidance, examples, and constraints, see references/704-technologies-sql.md.
1---
2name: 704-technologies-sql
3description: Use when you need framework-agnostic SQL guidance — schema naming, relational table design, query readability, indexes, transactions, database security, migrations, testing, and monitoring — without choosing Spring Boot, Quarkus, or Micronaut. This should trigger for requests such as Review SQL schema or migrations; Improve SQL query performance and readability; Design relational tables and indexes; Review database transaction, security, or monitoring practices. Part of Plinth Toolkit
4license: Apache-2.0
5metadata:
6 author: Juan Antonio Breña Moral
7 version: 0.18.0
8---
9
10# SQL best practices
11
12Help teams create maintainable, secure, and performant **SQL** for Java applications across relational database engines.
13
14**What is covered in this Skill?**
15
16- Naming conventions for tables, columns, indexes, constraints, views, triggers, and routines
17- Relational table design: keys, constraints, timestamps, soft deletes, normalization, and dialect-aware data types
18- Query writing: readable formatting, explicit column lists, joins, CTEs, bind parameters, and execution-plan review
19- Index strategy: foreign keys, filter/sort paths, composite indexes, over-indexing risks, and index lifecycle
20- Security, transactions, migrations, testing, and monitoring practices for production database work
21
22**Scope:** Framework-agnostic SQL quality. For Java data-access APIs, defer to the matching JDBC, Spring Data JDBC, Panache, or Micronaut Data skill.
23
24## Constraints
25
26Keep recommendations at the SQL and database-design layer unless the user explicitly asks for Java framework integration. After editing this repository's XML sources, regenerate skills and verify the build.
27
28- **MANDATORY**: Run `./mvnw compile` or `mvn compile` before proposing Java or Maven changes in the same change set
29- **SQL INJECTION**: Never concatenate untrusted input into SQL strings; use bind parameters through the application's data-access API
30- **DIALECTS**: Call out database-specific syntax and portability trade-offs when using PostgreSQL, MySQL, Oracle, SQL Server, H2, or another engine
31- **FRAMEWORK**: Defer Spring JDBC to `@311-frameworks-spring-jdbc`, Quarkus JDBC to `@411-frameworks-quarkus-jdbc`, Micronaut JDBC to `@511-frameworks-micronaut-jdbc`, and framework-specific migrations to the matching Flyway skill
32- **VERIFY**: Run `./mvnw clean verify` or `mvn clean verify` before promoting changes
33- **EDGE CASE**: If the target database dialect, migration tool, or production constraint is missing and affects the recommendation, ask a clarifying question before editing SQL
34- **EDGE CASE**: If requested changes conflict with data safety, backwards compatibility, or zero-downtime deployment constraints, explain the trade-off and ask for confirmation
35
36## When to use this skill
37
38- Review SQL schema or migrations
39- Improve SQL query performance and readability
40- Design relational tables and indexes
41- Review database transaction, security, or monitoring practices
42- Apply SQL best practices to database DDL or DML
43
44## Workflow
45
461. **Read reference and assess database context**
47
48Read `references/704-technologies-sql.md` and inspect current schema, migrations, SQL queries, tests, and database configuration before proposing changes.
49
502. **Identify dialect and operational constraints**
51
52Confirm the target database engine, migration tool, data volume expectations, availability requirements, and compatibility constraints that shape the SQL design.
53
543. **Apply SQL-aligned changes**
55
56Implement or refactor SQL artifacts following the reference patterns and project conventions, keeping Java framework integration out of scope unless explicitly requested.
57
584. **Run verification and report results**
59
60Execute appropriate build, migration, SQL validation, and test checks; summarize what changed, what was verified, and any remaining database risks.
61
62## Reference
63
64For detailed guidance, examples, and constraints, see [references/704-technologies-sql.md](references/704-technologies-sql.md).
jabrena