Code-MCP

```typescript import { Pool, PoolClient, QueryResult } from "pg"; // ============================================ // Configuration // ============================================ const pool = new Pool({ host: process.env.DB_HOST || "localhost", port: parseInt(process.env.DB_PORT || "5432"), database: process.env.DB_NAME || "myapp", user: process.env.DB_USER || "postgres", password: process.env.DB_PASSWORD || "password", max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, }); // ============================================ // Types // ============================================ export interface User { id: number; email: string; name: string; created_at: Date; updated_at: Date; } export interface Post { id: number; user_id: number; title: string; content: string; published: boolean; created_at: Date; } // ============================================ // Base Repository // ============================================ export abstract class BaseRepository<T> { constructor(protected tableName: string) {} protected async query<R = T>(sql: string, params?: unknown[]): Promise<R[]> { const result = await pool.query(sql, params); return result.rows; } protected async queryOne<R = T>( sql: string, params?: unknown[], ): Promise<R | null> { const rows = await this.query<R>(sql, params); return rows[0] || null; } async findById(id: number): Promise<T | null> { return this.queryOne(`SELECT * FROM ${this.tableName} WHERE id = $1`, [id]); } async findAll(limit = 100, offset = 0): Promise<T[]> { return this.query( `SELECT * FROM ${this.tableName} ORDER BY id DESC LIMIT $1 OFFSET $2`, [limit, offset], ); } async delete(id: number): Promise<boolean> { const result = await pool.query( `DELETE FROM ${this.tableName} WHERE id = $1`, [id], ); return result.rowCount! > 0; } async count(): Promise<number> { const result = await this.queryOne<{ count: string }>( `SELECT COUNT(*) FROM ${this.tableName}`, ); return parseInt(result?.count || "0"); } } // ============================================ // User Repository // ============================================ export interface CreateUserDTO { email: string; name: string; password_hash: string; } export interface UpdateUserDTO { email?: string; name?: string; } export class UserRepository extends BaseRepository<User> { constructor() { super("users"); } async findByEmail(email: string): Promise<User | null> { return this.queryOne("SELECT * FROM users WHERE email = $1", [email]); } async create(data: CreateUserDTO): Promise<User> { const result = await this.queryOne<User>( `INSERT INTO users (email, name, password_hash, created_at, updated_at) VALUES ($1, $2, $3, NOW(), NOW()) RETURNING *`, [data.email, data.name, data.password_hash], ); return result!; } async update(id: number, data: UpdateUserDTO): Promise<User | null> { const fields: string[] = []; const values: unknown[] = []; let paramIndex = 1; if (data.email !== undefined) { fields.push(`email = $${paramIndex++}`); values.push(data.email); } if (data.name !== undefined) { fields.push(`name = $${paramIndex++}`); values.push(data.name); } if (fields.length === 0) return this.findById(id); fields.push(`updated_at = NOW()`); values.push(id); return this.queryOne<User>( `UPDATE users SET ${fields.join(", ")} WHERE id = $${paramIndex} RETURNING *`, values, ); } async search(query: string, limit = 10): Promise<User[]> { return this.query( `SELECT * FROM users WHERE name ILIKE $1 OR email ILIKE $1 ORDER BY name LIMIT $2`, [`%${query}%`, limit], ); } } // ============================================ // Post Repository // ============================================ export interface CreatePostDTO { user_id: number; title: string; content: string; published?: boolean; } export class PostRepository extends BaseRepository<Post> { constructor() { super("posts"); } async findByUser(userId: number): Promise<Post[]> { return this.query( "SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC", [userId], ); } async findPublished(limit = 20, offset = 0): Promise<Post[]> { return this.query( `SELECT * FROM posts WHERE published = true ORDER BY created_at DESC LIMIT $1 OFFSET $2`, [limit, offset], ); } async create(data: CreatePostDTO): Promise<Post> { const result = await this.queryOne<Post>( `INSERT INTO posts (user_id, title, content, published, created_at) VALUES ($1, $2, $3, $4, NOW()) RETURNING *`, [data.user_id, data.title, data.content, data.published ?? false], ); return result!; } async publish(id: number): Promise<Post | null> { return this.queryOne<Post>( "UPDATE posts SET published = true WHERE id = $1 RETURNING *", [id], ); } } // ============================================ // Transaction Helper // ============================================ export async function withTransaction<T>( callback: (client: PoolClient) => Promise<T>, ): Promise<T> { const client = await pool.connect(); try { await client.query("BEGIN"); const result = await callback(client); await client.query("COMMIT"); return result; } catch (error) { await client.query("ROLLBACK"); throw error; } finally { client.release(); } } // ============================================ // Migrations // ============================================ export const migrations = { async createUsersTable(): Promise<void> { await pool.query(` CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW() ); CREATE INDEX IF NOT EXISTS idx_users_email ON users(email); `); }, async createPostsTable(): Promise<void> { await pool.query(` CREATE TABLE IF NOT EXISTS posts ( id SERIAL PRIMARY KEY, user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, title VARCHAR(255) NOT NULL, content TEXT, published BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT NOW() ); CREATE INDEX IF NOT EXISTS idx_posts_user ON posts(user_id); CREATE INDEX IF NOT EXISTS idx_posts_published ON posts(published); `); }, async runAll(): Promise<void> { await this.createUsersTable(); await this.createPostsTable(); console.log("Migrations completed"); }, }; // ============================================ // Exports // ============================================ export const userRepo = new UserRepository(); export const postRepo = new PostRepository(); export { pool }; ```