skills.rest
jacexh
jacexhCOMMUNITY
Explore all skills by this author →
GitHub

database-patterns

Design and optimize database schemas and access patterns for relational and document databases.

Updated Mar 3, 2026
Search Tags:#indexing#database#migrations#sql#transactions#nosql#schema-design
One-click install
npx skills add https://github.com/jacexh/skills --skill database-patterns-jacexh
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-patterns
Source: https://github.com/jacexh/skills/tree/main/skills/database-patterns
Command: npx skills add https://github.com/jacexh/skills --skill database-patterns-jacexh

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Provides clear patterns to prevent common database problems such as slow queries, poor indexing, unsafe migrations, concurrency bugs, and brittle repository implementations so applications remain reliable and performant as they scale.

Core Features & Use Cases

  • Schema & Modeling: Guidance on normalization, denormalization, relationships, timestamps, soft deletes, and audit columns for relational and document stores.
  • Indexing & Query Optimization: Recommendations for B-tree, GIN, GiST, composite, partial, and expression indexes plus pagination and upsert patterns.
  • Transactions, Concurrency & Migrations: Safe transaction patterns, optimistic locking, isolation levels, non-blocking migration practices, and connection pooling advice.
  • Use Case: Build a user and order service with efficient keyset pagination, safe schema migrations, concurrent-safe fund transfers, and a repository layer for application code.

Quick Start

Create a PostgreSQL users table with TIMESTAMPTZ timestamps, soft-delete support, an index on lower(email), and a repository interface for CRUD, pagination, and optimistic locking.

Frequently Asked Questions about database-patterns

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

FAQPage Schema
How do I design a database schema with safe non-blocking migrations in PostgreSQL?▼

To design a PostgreSQL database schema with safe non-blocking migrations, apply patterns for adding columns with defaults, creating indexes concurrently, and using transaction isolation to prevent locking issues and ensure reliable schema evolution.

What is the best way to handle database indexing and query optimization for slow queries?▼

The best way to handle database indexing and query optimization is implementing B-tree, GIN, composite, partial, and expression indexes, alongside keyset pagination and upsert patterns to ensure efficient queries as applications scale.

How do I manage concurrent transactions and optimistic locking in MySQL?▼

Manage concurrent transactions and optimistic locking in MySQL by applying safe transaction patterns, appropriate isolation levels, and version checking to prevent concurrency bugs and ensure reliable concurrent-safe data transfers.

Does this database schema design approach work for both relational and document databases like MongoDB?▼

Yes, this database schema design approach works for both relational and document databases like MongoDB, providing guidance on normalization, denormalization, relationships, timestamps, soft deletes, and audit columns for document stores.

How do I build a repository interface for application code with database CRUD operations?▼

Build a repository interface for application code by implementing structured CRUD operations, keyset pagination, and optimistic locking patterns that abstract database access and prevent brittle repository implementations.

When should I use NoSQL schema design patterns like DynamoDB over relational databases?▼

Use NoSQL schema design patterns like DynamoDB when your application requires specific access patterns, denormalization, and composite key design, applying indexing and pagination strategies distinct from traditional relational database normalization.

Related Skills

Explore More Software Engineering Skills→
HechaoYannetHechaoYannet

create-architecture

Generate a master architecture document from GDDs, ADRs, and governance constraints.

Community
Advanced
4lerman4lerman

sql-database-assistant

Generate SQL queries, introspect schemas, optimize performance, and create migration scripts.

Community
Advanced
augustoheissaugustoheiss

agency-database-optimizer

Analyze PostgreSQL schemas and queries to recommend indexes and migrations.

Community
Advanced
maingocthanhtan96maingocthanhtan96
2

packages-larajs-generator

Automate code and database migration generation for LaraJS projects.

Community
Intermediate