Code-MCP

import { pgTable, text, timestamp, integer, boolean, foreignKey } from 'drizzle-orm/pg-core'; import { drizzle } from 'drizzle-orm/node-postgres'; import { eq, like, desc, sql, and } from 'drizzle-orm'; import { Pool } from 'pg'; import { migrate } from 'drizzle-orm/node-postgres/migrator'; // ============================================ // Schema Definition // ============================================ export const users = pgTable('users', { id: integer('id').primaryKey().generatedAlwaysAsIdentity(), email: text('email').unique().notNull(), name: text('name').notNull(), passwordHash: text('password_hash').notNull(), role: text('role', { enum: ['user', 'admin'] }).default('user'), createdAt: timestamp('created_at').defaultNow(), updatedAt: timestamp('updated_at').defaultNow(), }); export const posts = pgTable('posts', { id: integer('id').primaryKey().generatedAlwaysAsIdentity(), authorId: integer('author_id').references(() => users.id, { onDelete: 'cascade' }), title: text('title').notNull(), content: text('content'), published: boolean('published').default(false), viewCount: integer('view_count').default(0), createdAt: timestamp('created_at').defaultNow(), updatedAt: timestamp('updated_at').defaultNow(), }); export const comments = pgTable('comments', { id: integer('id').primaryKey().generatedAlwaysAsIdentity(), postId: integer('post_id').references(() => posts.id, { onDelete: 'cascade' }), authorId: integer('author_id').references(() => users.id, { onDelete: 'set null' }), content: text('content').notNull(), createdAt: timestamp('created_at').defaultNow(), }); // ============================================ // Database Connection // ============================================ const pool = new Pool({ connectionString: process.env.DATABASE_URL || 'postgres://postgres:password@localhost:5432/myapp', }); // Create Drizzle instance export const db = drizzle(pool, { schema: { users, posts, comments }, }); // ============================================ // Types (Inferred) // ============================================ export type User = typeof users.$inferSelect; export type NewUser = typeof users.$inferInsert; export type Post = typeof posts.$inferSelect; export type NewPost = typeof posts.$inferInsert; // ============================================ // Repository Pattern Example // ============================================ export class UserRepository { static async getAll() { return db.select().from(users).orderBy(desc(users.createdAt)); } static async getById(id: number) { return db.query.users.findFirst({ where: eq(users.id, id), with: { posts: { limit: 5, orderBy: desc(posts.createdAt), }, }, }); } static async getByEmail(email: string) { return db.query.users.findFirst({ where: eq(users.email, email), }); } static async create(data: NewUser) { const result = await db.insert(users).values(data).returning(); return result[0]; } static async update(id: number, data: Partial<NewUser>) { const result = await db .update(users) .set({ ...data, updatedAt: new Date() }) .where(eq(users.id, id)) .returning(); return result[0]; } static async delete(id: number) { return db.delete(users).where(eq(users.id, id)).returning(); } } export class PostRepository { static async getFeed(limit = 20, offset = 0) { return db.query.posts.findMany({ where: eq(posts.published, true), limit, offset, orderBy: desc(posts.createdAt), with: { author: { columns: { id: true, name: true, email: true, }, }, comments: { limit: 3, with: { author: { columns: { name: true }, }, }, }, }, }); } static async search(term: string) { return db .select() .from(posts) .where( and( eq(posts.published, true), like(posts.title, `%${term}%`) ) ); } static async create(data: NewPost) { return db.insert(posts).values(data).returning(); } static async incrementViewCount(id: number) { return db .update(posts) .set({ viewCount: sql`${posts.viewCount} + 1` }) .where(eq(posts.id, id)) .returning(); } } // ============================================ // Migration Runner // ============================================ export async function runMigrations() { console.log('Running migrations...'); try { await migrate(db, { migrationsFolder: './drizzle' }); console.log('Migrations completed successfully'); } catch (error) { console.error('Migration failed:', error); process.exit(1); } } // ============================================ // Seeding Helper // ============================================ export async function seed() { console.log('Seeding database...'); // Clear existing data await db.delete(comments); await db.delete(posts); await db.delete(users); // Create users const [user1] = await db.insert(users).values({ email: 'alice@example.com', name: 'Alice', passwordHash: 'hashed_password', role: 'admin', }).returning(); const [user2] = await db.insert(users).values({ email: 'bob@example.com', name: 'Bob', passwordHash: 'hashed_password', role: 'user', }).returning(); // Create posts await db.insert(posts).values([ { title: 'Hello Drizzle', content: 'Drizzle ORM is awesome!', authorId: user1.id, published: true, }, { title: 'Draft Post', content: 'Work in progress...', authorId: user1.id, published: false, }, { title: 'Bob\'s Thoughts', content: 'TypeScript is great.', authorId: user2.id, published: true, }, ]); console.log('Seeding completed'); } // ============================================ // Usage Example // ============================================ /* import { UserRepository, PostRepository } from './db'; async function main() { const users = await UserRepository.getAll(); const feed = await PostRepository.getFeed(); console.log({ users, feed }); } */