secondsky/claude-skills / plugins/drizzle-orm-d1/skills/drizzle-orm-d1
drizzle-orm-d1
Verified| Type-safe ORM for Cloudflare D1 databases using Drizzle. Use when: building D1 database schemas, writing type-safe SQL queries, managing migrations with Drizzle Kit, defining table relations, implementing prepared statements, using D1 batch API, or encountering D1_ERROR, transaction errors, foreign key constraint failures, or schema inference issues.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 9.2 KB |
| references/common-errors.md | markdown | 5.3 KB |
| references/error-catalog.md | markdown | 7.9 KB |
| references/links-to-official-docs.md | markdown | 1.8 KB |
| references/migration-workflow.md | markdown | 2.7 KB |
| references/query-builder-api.md | markdown | 3.9 KB |
| references/schema-patterns.md | markdown | 3.4 KB |
| references/wrangler-setup.md | markdown | 2.7 KB |
| scripts/check-versions.shexecutable | script | 1017 B |
| templates/basic-queries.ts | script | 7.5 KB |
| templates/basic-schema.ts | script | 2.9 KB |
| templates/client.ts | script | 2.2 KB |
| templates/cloudflare-worker-integration.ts | script | 7.7 KB |
| templates/drizzle.config.ts | script | 2.2 KB |
| templates/migrations/0001_example.sql | other | 2.1 KB |
| templates/package.json | manifest | 1.1 KB |
| templates/prepared-statements.ts | script | 7.3 KB |
| templates/relations-queries.ts | script | 6.8 KB |
| templates/schema.ts | script | 6.5 KB |
| templates/transactions.ts | script | 6.8 KB |
templates/prepared-statements.ts
View on GitHub/**
* Prepared Statements with Drizzle ORM and D1
*
* Prepared statements allow you to define queries once and execute them
* multiple times with different parameters for better performance.
*
* IMPORTANT: D1 doesn't cache prepared statements between requests like
* traditional SQLite. They're still useful for code reusability and type safety.
*/
import { drizzle } from 'drizzle-orm/d1';
import { users, posts, comments } from './schema';
import { eq, and, gte, sql } from 'drizzle-orm';
/**
* Basic Prepared Statements
*/
// Get user by ID (prepared)
export function prepareGetUserById(db: ReturnType<typeof drizzle>) {
return db
.select()
.from(users)
.where(eq(users.id, sql.placeholder('id')))
.prepare();
}
// Usage:
// const getUserById = prepareGetUserById(db);
// const user1 = await getUserById.get({ id: 1 });
// const user2 = await getUserById.get({ id: 2 });
// Get user by email (prepared)
export function prepareGetUserByEmail(db: ReturnType<typeof drizzle>) {
return db
.select()
.from(users)
.where(eq(users.email, sql.placeholder('email')))
.prepare();
}
// Get posts by author (prepared)
export function prepareGetPostsByAuthor(db: ReturnType<typeof drizzle>) {
return db
.select()
.from(posts)
.where(eq(posts.authorId, sql.placeholder('authorId')))
.prepare();
}
/**
* Prepared Statements with Multiple Parameters
*/
// Get published posts after a date
export function prepareGetPublishedPostsAfterDate(db: ReturnType<typeof drizzle>) {
return db
.select()
.from(posts)
.where(
and(
eq(posts.published, sql.placeholder('published')),
gte(posts.createdAt, sql.placeholder('afterDate'))
)
)
.prepare();
}
// Usage:
// const getPublishedPosts = prepareGetPublishedPostsAfterDate(db);
// const recentPosts = await getPublishedPosts.all({
// published: true,
// afterDate: new Date('2024-01-01'),
// });
// Search users by partial email/name match
export function prepareSearchUsers(db: ReturnType<typeof drizzle>) {
return db
.select()
.from(users)
.where(
sql`${users.email} LIKE ${sql.placeholder('searchTerm')} OR ${users.name} LIKE ${sql.placeholder('searchTerm')}`
)
.prepare();
}
// Usage:
// const searchUsers = prepareSearchUsers(db);
// const results = await searchUsers.all({ searchTerm: '%john%' });
/**
* Prepared Statements for INSERT
*/
// Insert user (prepared)
export function prepareInsertUser(db: ReturnType<typeof drizzle>) {
return db
.insert(users)
.values({
email: sql.placeholder('email'),
name: sql.placeholder('name'),
bio: sql.placeholder('bio'),
})
.returning()
.prepare();
}
// Usage:
// const insertUser = prepareInsertUser(db);
// const [newUser] = await insertUser.get({
// email: 'test@example.com',
// name: 'Test User',
// bio: null,
// });
// Insert post (prepared)
export function prepareInsertPost(db: ReturnType<typeof drizzle>) {
return db
.insert(posts)
.values({
title: sql.placeholder('title'),
slug: sql.placeholder('slug'),
content: sql.placeholder('content'),
authorId: sql.placeholder('authorId'),
published: sql.placeholder('published'),
})
.returning()
.prepare();
}
/**
* Prepared Statements for UPDATE
*/
// Update user name (prepared)
export function prepareUpdateUserName(db: ReturnType<typeof drizzle>) {
return db
.update(users)
.set({
name: sql.placeholder('name'),
updatedAt: sql.placeholder('updatedAt'),
})
.where(eq(users.id, sql.placeholder('id')))
.returning()
.prepare();
}
// Usage:
// const updateUserName = prepareUpdateUserName(db);
// const [updated] = await updateUserName.get({
// id: 1,
// name: 'New Name',
// updatedAt: new Date(),
// });
// Publish post (prepared)
export function preparePublishPost(db: ReturnType<typeof drizzle>) {
return db
.update(posts)
.set({
published: true,
updatedAt: sql.placeholder('updatedAt'),
})
.where(eq(posts.id, sql.placeholder('id')))
.returning()
.prepare();
}
/**
* Prepared Statements for DELETE
*/
// Delete user (prepared)
export function prepareDeleteUser(db: ReturnType<typeof drizzle>) {
return db
.delete(users)
.where(eq(users.id, sql.placeholder('id')))
.returning()
.prepare();
}
// Delete posts by author (prepared)
export function prepareDeletePostsByAuthor(db: ReturnType<typeof drizzle>) {
return db
.delete(posts)
.where(eq(posts.authorId, sql.placeholder('authorId')))
.returning()
.prepare();
}
/**
* Best Practices
*/
// Create a class to encapsulate all prepared statements
export class PreparedQueries {
private db: ReturnType<typeof drizzle>;
// Prepared statements
private getUserByIdStmt;
private getUserByEmailStmt;
private insertUserStmt;
private updateUserNameStmt;
private deleteUserStmt;
constructor(db: ReturnType<typeof drizzle>) {
this.db = db;
// Initialize all prepared statements once
this.getUserByIdStmt = prepareGetUserById(db);
this.getUserByEmailStmt = prepareGetUserByEmail(db);
this.insertUserStmt = prepareInsertUser(db);
this.updateUserNameStmt = prepareUpdateUserName(db);
this.deleteUserStmt = prepareDeleteUser(db);
}
// Convenient methods that use prepared statements
async getUserById(id: number) {
return await this.getUserByIdStmt.get({ id });
}
async getUserByEmail(email: string) {
return await this.getUserByEmailStmt.get({ email });
}
async insertUser(data: { email: string; name: string; bio?: string | null }) {
const [user] = await this.insertUserStmt.get({
email: data.email,
name: data.name,
bio: data.bio ?? null,
});
return user;
}
async updateUserName(id: number, name: string) {
const [user] = await this.updateUserNameStmt.get({
id,
name,
updatedAt: new Date(),
});
return user;
}
async deleteUser(id: number) {
const [user] = await this.deleteUserStmt.get({ id });
return user;
}
}
// Usage:
// const queries = new PreparedQueries(db);
// const user = await queries.getUserById(1);
/**
* Performance Considerations for D1
*
* Unlike traditional SQLite:
* - D1 doesn't cache prepared statements between requests
* - Each request starts fresh
* - Prepared statements are still useful for:
* 1. Code reusability
* 2. Type safety
* 3. Preventing SQL injection
* 4. Cleaner code organization
*
* But don't expect:
* - Performance improvements from statement caching
* - Faster execution on repeated calls
* - Shared state between requests
*/
/**
* When to Use Prepared Statements:
*
* ✅ Good for:
* - Queries you'll execute multiple times in the same request
* - Complex queries with dynamic parameters
* - Code organization and reusability
* - Type-safe parameter passing
*
* ❌ Not necessary for:
* - One-off queries
* - Simple CRUD operations
* - Static queries without parameters
*/
/**
* Execution Methods:
*
* - .all() - Returns all results as array
* - .get() - Returns first result or undefined
* - .run() - Executes query, returns metadata only
*/
/**
* TypeScript Types
*/
import type { InferSelectModel } from 'drizzle-orm';
export type PreparedQuery<T> = {
all: (params: T) => Promise<any[]>;
get: (params: T) => Promise<any | undefined>;
run: (params: T) => Promise<any>;
};
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 |