Skip to main content
Glama

Github Project Manager

database-service.ts8.33 kB
import postgres from 'postgres'; import { DatabaseError } from '../errors/database-error.js'; export interface DatabaseConfig { connectionString: string; ssl?: boolean; } // Define interfaces we need interface QueryResultRow { [column: string]: any; } /** * Extended QueryResult interface to match our previous API */ interface QueryResult<T = any> { rows: T[]; rowCount: number; command: string; fields: { name: string; dataTypeID: number; }[]; } /** * Service for handling Postgres database connections and operations */ export class DatabaseService { private sql: postgres.Sql; constructor(config: DatabaseConfig) { this.sql = postgres(config.connectionString, { ssl: config.ssl ? { rejectUnauthorized: false } : undefined, onnotice: () => {}, // Silence notices debug: false, // Disable debug logging }); } /** * Execute a query with parameters * @param query SQL query string * @param params Query parameters * @returns Query result */ async query<T extends QueryResultRow = any>(query: string, params: any[] = []): Promise<QueryResult<T>> { try { // Create a template literal dynamically using the query and params // This is needed because postgres package uses tagged template literals const result: T[] = await this.sql.unsafe(query, params); // Construct result to match expected interface return { rows: result, rowCount: result.length, command: '', // Not easily available with postgres package fields: Object.keys(result[0] || {}).map((name) => ({ name, dataTypeID: 0, // Not easily available with postgres package })), }; } catch (error: any) { throw new DatabaseError( error.message || 'Database query failed', error.code || 'UNKNOWN_ERROR', error.detail || '', ); } } /** * Get database schema information * @returns Object containing tables and their schemas */ async getSchema(): Promise<Record<string, any>> { try { // Get tables const tablesQuery = ` SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' `; const tables = await this.sql.unsafe(tablesQuery); const schema: Record<string, any> = {}; // For each table, get columns for (const table of tables) { const tableName = table.table_name; const columnsQuery = ` SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position `; const columns = await this.sql.unsafe(columnsQuery, [tableName]); schema[tableName] = columns; } return schema; } catch (error: any) { throw new DatabaseError('Failed to get database schema', error.code || 'UNKNOWN_ERROR', error.detail || ''); } } /** * Get detailed information about a specific table * @param tableName Name of the table * @param options Options for including additional details * @returns Table structure details including columns and optionally constraints and indexes */ async getTableDetails( tableName: string, options: { includeConstraints?: boolean; includeIndexes?: boolean } = {}, ): Promise<any> { const { includeConstraints = true, includeIndexes = true } = options; try { // Get table columns const columnsQuery = ` SELECT column_name, data_type, character_maximum_length, is_nullable, column_default, ordinal_position FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position `; const columnsResult = await this.query(columnsQuery, [tableName]); // Check if table exists if (columnsResult.rows.length === 0) { throw new DatabaseError(`Table '${tableName}' not found`, 'TABLE_NOT_FOUND', ''); } const tableDetails: any = { name: tableName, columns: columnsResult.rows, }; // Get constraint info if (includeConstraints) { const constraintsQuery = ` SELECT con.conname as constraint_name, con.contype as constraint_type, CASE WHEN con.contype = 'p' THEN 'PRIMARY KEY' WHEN con.contype = 'f' THEN 'FOREIGN KEY' WHEN con.contype = 'u' THEN 'UNIQUE' WHEN con.contype = 'c' THEN 'CHECK' ELSE con.contype::text END as constraint_type_desc, array_agg(att.attname) as column_names, CASE WHEN con.contype = 'f' THEN ( SELECT relname FROM pg_class WHERE oid = con.confrelid ) ELSE NULL END as referenced_table FROM pg_constraint con JOIN pg_class rel ON rel.oid = con.conrelid JOIN pg_namespace nsp ON nsp.oid = rel.relnamespace JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey) WHERE nsp.nspname = 'public' AND rel.relname = $1 GROUP BY con.conname, con.contype, con.confrelid ORDER BY con.contype, con.conname `; const constraintsResult = await this.query(constraintsQuery, [tableName]); tableDetails.constraints = constraintsResult.rows; } // Get index info if (includeIndexes) { const indexesQuery = ` SELECT idx.indexname as index_name, idx.indexdef as index_definition, CASE WHEN idx.indexdef LIKE '%UNIQUE INDEX%' THEN true ELSE false END as is_unique FROM pg_indexes idx WHERE idx.schemaname = 'public' AND idx.tablename = $1 ORDER BY idx.indexname `; const indexesResult = await this.query(indexesQuery, [tableName]); tableDetails.indexes = indexesResult.rows; } return tableDetails; } catch (error: any) { if (error instanceof DatabaseError) { throw error; } throw new DatabaseError( error.message || `Failed to get details for table '${tableName}'`, error.code || 'UNKNOWN_ERROR', error.detail || '', ); } } /** * Close all database connections */ async close(): Promise<void> { await this.sql.end(); } }

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/Monsoft-Solutions/model-context-protocols'

If you have feedback or need assistance with the MCP directory API, please join our Discord server