skills.rest
kohdice
kohdiceCOMMUNITY
Explore all skills by this author →
Website𝕏@kohdiceGitHub

sql-schema-design

Reviews and writes PostgreSQL, MySQL, and SQLite DDL with engine-aware schema design guidance.

Updated Jul 4, 2023
Search Tags:#SQL#schema design#postgresql#migrations#SQLite#MySQL#database review
One-click install
npx skills add https://github.com/kohdice/dotfiles --skill sql-schema-design-kohdice
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-schema-design
Source: https://github.com/kohdice/dotfiles/tree/main/config/agents/skills/sql-schema-design
Command: npx skills add https://github.com/kohdice/dotfiles --skill sql-schema-design-kohdice

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve? Designing database schemas that enforce data integrity is error-prone: missing foreign keys, wrong data types for money or timestamps, unsafe migrations that lock large tables, and engine-specific quirks (SQLite's disabled foreign keys, MySQL's ignored CHECK constraints before 8.0.16) silently corrupt data or cause outages. ## Core Features & Use Cases - Engine-aware schema review: Resolves the database engine and version from project evidence (drivers, migration tooling, connection strings) before judging any DDL, and gates recommendations on version-specific features. - Severity-ordered design catalog: Covers primary keys, foreign keys, NOT NULL and CHECK constraints, UNIQUE constraints, data types, normalization, naming, and schema-change safety, each traced to the official PostgreSQL, MySQL, or SQLite manual. - Migration safety analysis: Evaluates ALTER TABLE operations per engine (PostgreSQL rewrites, MySQL InnoDB instant DDL, SQLite twelve-step rebuild) so a correct target schema is not reached through a locking migration. - Use Case: Point it at a migrations directory in a Rust/sqlx or Go project and receive a structured review report tagging each finding as integrity, static, requirement, or migration, with manual citations. ## Quick Start Ask the AI to review the SQL migration files in this repository for schema design and integrity issues using the appropriate database engine.

Frequently Asked Questions about sql-schema-design

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I review SQL schema design for data integrity issues?▼

Run a schema review against your migration files or ORM model definitions. The review resolves your database engine and version first, then checks primary keys, foreign keys, nullability, constraints, and data types against a severity-ordered catalog traced to official manuals.

What data type should I use for money columns in PostgreSQL or MySQL?▼

Use numeric or DECIMAL(p, s) for monetary amounts, as recommended by both the PostgreSQL and MySQL manuals. FLOAT, DOUBLE, and real are approximate types and are flagged as findings on money, price, tax, or balance columns.

Does SQLite enforce foreign keys by default?▼

No, SQLite disables foreign key enforcement by default. You must run PRAGMA foreign_keys = ON on every connection, otherwise declared foreign keys are not enforced and orphan rows can be inserted.

Are CHECK constraints enforced in MySQL?▼

CHECK constraints are enforced only from MySQL 8.0.16 onward. On earlier versions they are parsed and ignored, so the review flags them as integrity findings and suggests triggers or application-level checks as alternatives.

How do I safely add a NOT NULL column to a large existing table?▼

Backfill existing rows first, then add the constraint. On PostgreSQL, add CHECK or foreign key constraints as NOT VALID and run VALIDATE CONSTRAINT separately; on MySQL, instant DDL covers column add and drop but nullability changes rebuild the table.

When should I use a UUID instead of an integer primary key?▼

Use UUIDs only when distributed or client-side key generation is a stated requirement, since random UUIDs fragment clustered indexes. Prefer time-ordered UUIDs like PostgreSQL 18's uuidv7() or MySQL's UUID_TO_BIN with time swap, and keep integer keys otherwise.

Related Skills

Explore More Software Engineering Skills→
crgarcia12crgarcia12

data-model-extractor

Extract database schemas and generate Mermaid ER diagrams from ORM models.

Community
Advanced
TencentCloudBaseTencentCloudBase
1.1k

postgresql-development

Query and manage CloudBase PostgreSQL databases via MCP tools with RLS and schema inspection.

Official
Basic
goodnight000goodnight000

sql-optimization-patterns

Analyze slow SQL queries with EXPLAIN ANALYZE and apply indexing optimizations.

Community
Advanced
wraps-teamwraps-team
55

drizzle-migrations

Automates PostgreSQL schema design and migration workflow for multi-tenant apps.

Official
Advanced