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 }); } */