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