Back to Details

704-technologies-sql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQL best practices

SQL 最佳实践

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.
帮助团队为 Java 应用在各种关系数据库引擎上创建可维护、安全且高性能的 SQL。
本 Skill 涵盖哪些内容？
  • 表、列、索引、约束、视图、触发器和存储例程的命名约定
  • 关系表设计：键、约束、时间戳、软删除、规范化以及适配数据库方言的数据类型
  • 查询编写：易读的格式、显式列清单、JOIN、CTE、绑定参数和执行计划审查
  • 索引策略：外键、过滤/排序路径、复合索引、过度索引风险和索引生命周期
  • 生产环境数据库工作的安全、事务、迁移、测试和监控实践
范围： 与框架无关的 SQL 质量实践。对于 Java 数据访问 API，应交由对应的 JDBC、Spring Data JDBC、Panache 或 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
除非用户明确要求与 Java 框架集成，否则建议应始终停留在 SQL 和数据库设计层。编辑本仓库的 XML 源文件后，重新生成 Skill 并验证构建。
  • 强制要求：在提出同一变更集中的 Java 或 Maven 修改之前，运行 
    ./mvnw compile
    或 
    mvn compile
  • SQL 注入：切勿将不可信输入拼接进 SQL 字符串；应通过应用的数据访问 API 使用绑定参数
  • 数据库方言：使用 PostgreSQL、MySQL、Oracle、SQL Server、H2 或其他引擎时，应明确指出数据库特定语法和可移植性权衡
  • 框架：Spring JDBC 应交由 
    @311-frameworks-spring-jdbc
    处理，Quarkus JDBC 应交由 
    @411-frameworks-quarkus-jdbc
    处理，Micronaut JDBC 应交由 
    @511-frameworks-micronaut-jdbc
    处理；框架特定的迁移应交由对应的 Flyway Skill 处理
  • 验证：在合入变更前，运行 
    ./mvnw clean verify
    或 
    mvn clean verify
  • 边界情况：如果目标数据库方言、迁移工具或生产环境约束缺失且会影响建议，应在编辑 SQL 前提出澄清问题
  • 边界情况：如果所请求的变更与数据安全、向后兼容性或零停机部署约束冲突，应说明相关权衡并请求确认

When to use this skill

何时使用本 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
  • 审查 SQL Schema 或迁移
  • 提升 SQL 查询性能和可读性
  • 设计关系表和索引
  • 审查数据库事务、安全或监控实践
  • 将 SQL 最佳实践应用于数据库 DDL 或 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.
  1. 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.
  1. 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.
  1. 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.
  1. 阅读参考资料并评估数据库上下文
在提出变更前，阅读 
references/704-technologies-sql.md
，并检查当前 Schema、迁移、SQL 查询、测试和数据库配置。
  1. 识别数据库方言和运维约束
确认目标数据库引擎、迁移工具、数据量预期、可用性要求，以及影响 SQL 设计的兼容性约束。
  1. 实施符合 SQL 原则的变更
按照参考模式和项目约定实施或重构 SQL 构件；除非用户明确要求，否则不纳入 Java 框架集成。
  1. 运行验证并报告结果
执行适当的构建、迁移、SQL 校验和测试检查；总结变更内容、已验证项目以及剩余的数据库风险。

Reference

参考资料

For detailed guidance, examples, and constraints, see references/704-technologies-sql.md.
有关详细指导、示例和约束，请参阅 references/704-technologies-sql.md。