postgresql

PostgreSQL database patterns for Neon serverless database. Use when configuring database connections, writing queries, or optimizing PostgreSQL for the Todo backend.

📖 中文导读:PostgreSQL database patterns for Neon serverless database. Use when configuring database connections, writing queries, or optimizing PostgreSQL for the Todo backend. 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

name: postgresql description: PostgreSQL database patterns for Neon serverless database. Use when configuring database connections, writing queries, or optimizing PostgreSQL for the Todo backend. allowed-tools: Read, Edit, Write, Glob, Grep, Bash

PostgreSQL Skill

Quick Reference

PostgreSQL is the database used with Neon Serverless for the Todo app.

Neon Connection

DATABASE_URL = os.getenv(
    "DATABASE_URL",
    "postgresql://user:pass@ep-xxx.us-east-1.aws.neon.tech/neon?sslmode=require"
)

# SSL is required for Neon
engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,  # Verify connection before use
)

Common Queries

-- List tasks for user
SELECT * FROM tasks WHERE user_id = 'user_123' ORDER BY created_at DESC;

-- Count tasks by status
SELECT completed, COUNT(*) FROM tasks WHERE user_id = 'user_123' GROUP BY completed;

-- Search tasks
SELECT * FROM tasks WHERE title ILIKE '%groceries%';

-- Update task
UPDATE tasks SET completed = true, updated_at = NOW() WHERE id = 1 AND user_id = 'user_123';

-- Delete task
DELETE FROM tasks WHERE id = 1 AND user_id = 'user_123';

Indexes for Performance

-- User task lookup
CREATE INDEX IF NOT EXISTS idx_tasks_user_id ON tasks(user_id);

-- Status filtering
CREATE INDEX IF NOT EXISTS idx_tasks_user_completed ON tasks(user_id, completed);

-- Date sorting
CREATE INDEX IF NOT EXISTS idx_tasks_user_created ON tasks(user_id, created_at DESC);

For Detailed Reference

See REFERENCE.md for:

  • Advanced queries
  • Performance tuning
  • Neon-specific considerations
  • Backup and recovery