Code-MCP

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 };