SQL best practices

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

1. 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.