aiskillstore/marketplace / skills/aayushbaniya2006/db-handler
db-handler
VerifiedManage database schemas, Drizzle ORM, migrations, and data modeling. Use when creating tables, modifying columns, or planning database changes.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 1.7 KB |
| reference.md | markdown | 2.1 KB |
| skill-report.json | config | 18.9 KB |
reference.md
View on GitHub# Database Architecture Reference
## 1. Schema Definition
**Location:** `src/db/schema/{domain}.ts`
### Standard Imports
```typescript
import { pgTable, text, timestamp, boolean, integer, jsonb, index, primaryKey } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
```
### Primary Keys & Timestamps
```typescript
id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
createdAt: timestamp("createdAt", { mode: "date" }).defaultNow()
```
### JSONB with Zod (MANDATORY)
```typescript
import { z } from "zod";
export const settingsSchema = z.object({ ... });
settings: jsonb("settings").$type<z.infer<typeof settingsSchema>>()
```
## 2. Performance & Indexes
Always define indexes for Foreign Keys and filter columns.
```typescript
export const posts = pgTable("posts", {
userId: text("userId").references(() => users.id),
status: text("status"),
}, (t) => [
// Index Foreign Key (Crucial for Joins)
index("posts_user_id_idx").on(t.userId),
// Index Filter Column
index("posts_status_idx").on(t.status)
]);
```
## 3. Solving N+1 Queries
**Problem**: Fetching related data in a loop (1 query per item).
**Solution**: Use Relational Queries or Joins.
### Using Query Builder (Preferred)
```typescript
const usersWithPosts = await db.query.users.findMany({
with: {
posts: true, // Fetches posts automatically in 1 round-trip
profile: true
}
});
```
### Using Joins (Advanced)
```typescript
const result = await db
.select()
.from(users)
.leftJoin(posts, eq(users.id, posts.userId));
```
## 4. Relations
- Use `relations` helper to enable the Query Builder syntax above.
```typescript
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
```
## 5. Best Practices
- **Normalization**: Default to 3NF. Use JSONB only for tightly coupled data.
- **Safety**: Confirm complex changes.
- **File Org**: Group by domain. Export in `src/db/schema/index.ts`.
- **Migrations**: We use `drizzle-kit push` for prototyping/development. Do not generate migration files manually unless strictly required for production versioning.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |