菜单
Use when designing database schemas, implementing repository patterns, writing optimized queries, managing migrations, or working with indexes and transactions for SQL/NoSQL databases.
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/MadAppGang/magus --skill database-patterns命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
Picks the browser-use agent brain LLM per task. Use when choosing a model for retry_with_browser_use_agent or a cloud session, or the user mentions a cheap, slow, or smart model.
Reference for the Browser Use MCP tools — parameters, returns, session lifecycle. Use when navigating, clicking, typing, evaluating JS, or extracting page content.
Visual UI debugging — screenshot capture and analysis, responsive layout checking at multiple viewport sizes, CSS validation via DOM state, visual regression detection, before/after state comparison.
正在显示 SKILL.md
基于 SOC 职业分类
| name | database-patterns |
| description | Use when designing database schemas, implementing repository patterns, writing optimized queries, managing migrations, or working with indexes and transactions for SQL/NoSQL databases. |
| disable-model-invocation | true |
Database design and access patterns for relational and NoSQL databases.
| Level | Description | Use Case |
|---|---|---|
| 1NF | Atomic values, no repeating groups | Base requirement |
| 2NF | No partial dependencies | Most applications |
| 3NF | No transitive dependencies | OLTP systems |
| Denormalized | Redundant data for reads | Read-heavy, analytics |
-- Users table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR() ,
status () ,
created_at ,
updated_at
);
users deleted_at ;
INDEX idx_users_deleted users(deleted_at) deleted_at ;
users created_by UUID users(id);
users updated_by UUID users(id);
-- One-to-Many
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID NOT NULL REFERENCES users(id),
total DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_user ON orders(user_id);
-- Many-to-Many
CREATE TABLE order_products (
order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
-- Self-referential (tree/hierarchy)
CREATE TABLE categories (
id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id UUID REFERENCES categories(id)
);
CREATE INDEX idx_categories_parent ON categories(parent_id);
| Type | Use Case | Example |
|---|---|---|
| B-tree | Range, equality | Most columns |
| Hash | Equality only | Exact matches |
| GIN | Arrays, JSON, full-text | JSONB, text search |
| GiST | Geometric, range types | PostGIS, IP ranges |
-- Primary key (automatic)
CREATE TABLE users (id UUID PRIMARY KEY);
-- Foreign keys
CREATE INDEX idx_orders_user ON orders(user_id);
-- Frequent filters
CREATE INDEX idx_users_status ON users(status);
-- Composite for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index for common queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Use specific columns, not *
SELECT id, name, email FROM users WHERE id = $1;
-- Limit results
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;
-- Exists vs COUNT
SELECT EXISTS(SELECT 1 FROM users WHERE email = $1);
-- Batch inserts
INSERT INTO users (name, email) VALUES
('User 1', 'user1@example.com'),
('User 2', 'user2@example.com'),
('User 3', 'user3@example.com');
-- Offset pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY created_at DESC LIMIT 20 OFFSET 100;
-- Cursor pagination (better performance)
SELECT * FROM users
WHERE created_at < $cursor
ORDER BY created_at DESC
LIMIT 20;
-- Keyset pagination with tie-breaker
SELECT * FROM users
WHERE (created_at, id) < ($cursor_time, $cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Upsert (INSERT or UPDATE)
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();
-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = $1;
SELECT * FROM users WHERE deleted_at IS NULL;
-- Lock for update (prevent race conditions)
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;
-- Bulk update
UPDATE orders SET status = 'shipped'
WHERE id = ANY($1::uuid[]);
interface UserRepository {
findById(id: string): Promise<User | null>;
findByEmail(email: string): Promise<User | null>;
findAll(filter: UserFilter, pagination: Pagination): Promise<PaginatedResult<User>>;
create(data: CreateUserInput): Promise<User>;
update(id: string, data: UpdateUserInput): Promise<User>;
delete(id: string): Promise<void>;
}
class PostgresUserRepository implements UserRepository {
constructor(private db: Database) {}
async findById(id: string): Promise<User | null> {
const result = await this.db.query(
'SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL',
[id]
);
return result.rows[0] || null;
}
async create(data: CreateUserInput): Promise<User> {
const result = await this.db.query(
`INSERT INTO users (name, email, password_hash)
VALUES ($1, $2, $3)
RETURNING *`,
[data.name, data.email, await hashPassword(data.password)]
);
return result.rows[0];
}
}
async function transferFunds(fromId: string, toId: string, amount: number) {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Lock accounts
await client.query(
'SELECT * FROM accounts WHERE id IN ($1, $2) FOR UPDATE',
[fromId, toId]
);
// Debit
await client.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[amount, fromId]
);
// Credit
await client.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[amount, toId]
);
await client.query('COMMIT');
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | No | Yes | Yes |
| Repeatable Read | No | No | Yes |
| Serializable | No | No | No |
-- Set isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
migrations/
├── 001_create_users.sql
├── 002_add_user_status.sql
├── 003_create_orders.sql
└── 004_add_order_index.sql
-- Always reversible
-- UP
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- DOWN
ALTER TABLE users DROP COLUMN phone;
-- Non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);
-- Safe column renames (PostgreSQL)
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Add NOT NULL safely
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
UPDATE users SET status = 'active' WHERE status IS NULL;
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
max: 20, // Max connections
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// Embedded (for one-to-few)
{
_id: ObjectId("..."),
name: "John",
addresses: [
{ type: "home", street: "123 Main St" },
{ type: "work", street: "456 Office Blvd" }
]
}
// Referenced (for one-to-many)
{
_id: ObjectId("..."),
name: "John",
orderIds: [ObjectId("..."), ObjectId("...")]
}
PK | SK | Attributes
----------------|-------------------|------------------
USER#123 | METADATA | name, email, ...
USER#123 | ORDER#001 | total, status, ...
USER#123 | ORDER#002 | total, status, ...
ORDER#001 | METADATA | userId, total, ...
ORDER#001 | ITEM#1 | productId, qty, ...
Database design and access patterns