Skip to main content
Glama
Maxim2324

MCP PostgreSQL Server

by Maxim2324

MCP PostgreSQL 서버

다음을 수행하는 모델-컨트롤러-공급자(MCP) 서버:

  • PostgreSQL 데이터베이스에 연결합니다

  • 테이블 스키마를 리소스로 노출합니다.

  • 읽기 전용 SQL 쿼리를 실행하기 위한 도구를 제공합니다.

  • 일반적인 데이터 분석 작업에 대한 프롬프트가 포함되어 있습니다.

특징

  • 스키마 탐색 : 데이터베이스 스키마, 테이블 및 열 탐색

  • 읽기 전용 쿼리 실행 : 데이터베이스에 대해 SQL 쿼리를 안전하게 실행합니다.

  • 데이터 분석 프롬프트 : 일반적인 분석 작업을 위한 미리 작성된 SQL 템플릿

  • 데이터 시각화 : 시각화를 위한 데이터 생성

  • 관계 탐색 : 테이블 관계 및 외래 키 시각화

  • API 문서 : 자동 생성된 OpenAPI 사양

Related MCP server: mcp-postgres

건축학

이 애플리케이션은 MCP(모델-컨트롤러-공급자) 패턴을 따릅니다.

  • 모델 계층 : 데이터베이스와의 직접적인 상호 작용

  • 공급자 계층 : 비즈니스 로직 및 데이터 처리

  • 컨트롤러 계층 : API 엔드포인트 및 요청 처리

보안 기능

  • 읽기 전용 쿼리 검증

  • SQL 주입 보호

  • 속도 제한

  • 매개변수화된 쿼리

  • 인증 지원

  • CORS 구성

설치

  1. 저장소를 복제합니다.

    지엑스피1

  2. 종속성 설치:

    npm install
  3. .env.template 기반으로 .env 파일을 만듭니다.

    cp .env.template .env
  4. .env 파일을 PostgreSQL 데이터베이스 자격 증명으로 업데이트합니다.

  5. 서버를 시작합니다:

    npm start

구성

모든 구성은 환경 변수를 통해 관리됩니다.

  • 서버 : 포트, 환경, CORS 설정

  • 데이터베이스 : 연결 세부 정보, 풀 설정

  • 보안 : JWT 설정, 속도 제한

  • 쿼리 : 실행 제한, 결과 크기 제한

API 엔드포인트

스키마 엔드포인트

  • GET /api/schemas - 모든 스키마 나열

  • GET /api/schemas/:schema/tables - 스키마의 테이블 나열

  • GET /api/schemas/:schema/tables/:table - 테이블 스키마 세부 정보 가져오기

  • GET /api/schemas/:schema/relationships - 테이블 관계 가져오기

  • GET /api/structure - 전체 데이터베이스 구조 가져오기

  • GET /api/search?q=term - 테이블 및 열 검색

쿼리 엔드포인트

  • POST /api/query - SQL 쿼리 실행

  • POST /api/query/explain - 쿼리 실행 계획 가져오기

  • GET /api/schemas/:schema/tables/:table/sample - 샘플 데이터 가져오기

  • GET /api/schemas/:schema/tables/:table/stats - 테이블 통계 가져오기

분석 프롬프트 엔드포인트

  • GET /api/prompts - 분석 프롬프트 템플릿 목록

  • GET /api/prompts/:templateId - 프롬프트 템플릿 세부 정보 가져오기

  • POST /api/prompts/:templateId/generate - 템플릿에서 SQL 생성

  • GET /api/schemas/:schema/tables/:table/analysis/suggest - 분석 제안 받기

예제 쿼리

기본 테이블 쿼리

// API request
fetch('/api/query', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    sql: 'SELECT * FROM users LIMIT 10'
  })
})
.then(response => response.json())
.then(data => console.log(data));

분석 프롬프트 사용

// Get suggested analysis for a table
fetch('/api/schemas/public/tables/orders/analysis/suggest')
.then(response => response.json())
.then(suggestions => {
  // Use a suggestion
  const suggestionId = suggestions.data[0].templateId;
  const params = suggestions.data[0].params;
  
  // Generate SQL from the template
  return fetch(`/api/prompts/${suggestionId}/generate`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ params })
  });
})
.then(response => response.json())
.then(data => {
  // Execute the generated SQL
  return fetch('/api/query', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ sql: data.data.sql })
  });
})
.then(response => response.json())
.then(results => console.log(results));

개발

  • 개발 모드에서 실행: npm run dev

  • 테스트 실행: npm test

  • 린트 코드: npm run lint

특허

MIT

Available Tools

6 tools
connect_dbA

Connect to PostgreSQL database. NOTE: Default connection exists - only use when requested or if other commands fail

ParametersJSON Schema
NameRequiredDescriptionDefault
databaseYesDatabase name
hostYesDatabase host
passwordYesDatabase password
portNoDatabase port (default: 5432)
userYesDatabase user

TDQS

A4/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are provided, so the description carries full burden. It mentions the existence of a default connection and failure conditions, which adds useful behavioral context. However, it doesn't disclose other important traits like authentication requirements (implied by password parameter), potential side effects of establishing connections, or error handling behavior.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two concise sentences with zero waste. The first states the purpose, the second provides crucial usage guidance. Every word earns its place, and the structure is front-loaded with essential information.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a connection tool with 5 parameters and no output schema, the description provides good contextual completeness. It covers purpose and usage guidelines well. The main gap is lack of output information (what happens after connection), but given the tool's nature and absence of output schema, the description does reasonably well.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema already documents all 5 parameters thoroughly. The description doesn't add any parameter-specific information beyond what's in the schema. This meets the baseline of 3 when schema does the heavy lifting, but no extra value is provided.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the action ('Connect to PostgreSQL database') and resource ('PostgreSQL database'), making the purpose immediately understandable. It doesn't explicitly distinguish from sibling tools like 'execute' or 'query', but the connection focus is clear enough to avoid confusion with execution or querying operations.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines5/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides explicit usage guidance: 'Default connection exists - only use when requested or if other commands fail.' This clearly indicates when to use (when requested or after failure) and when not to use (when default connection works), helping the agent avoid unnecessary invocations.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

describe_tableC

Get table structure

ParametersJSON Schema
NameRequiredDescriptionDefault
schemaNoSchema name (default: public)
tableYesTable name

TDQS

C2.6/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden of behavioral disclosure. 'Get table structure' implies a read-only operation, but it doesn't specify whether this requires specific permissions, what the output format is (e.g., JSON, table), or if there are any limitations (e.g., rate limits, error handling). The description is too brief to adequately inform the agent about behavioral traits beyond the basic action.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is extremely concise with just three words, making it front-loaded and efficient. However, it borders on under-specification given the tool's purpose and lack of annotations, which slightly reduces its effectiveness. Every word earns its place, but more detail could improve clarity without sacrificing brevity.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool's complexity (a read operation with 2 parameters), no annotations, and no output schema, the description is incomplete. It fails to explain what 'table structure' includes (e.g., columns, data types, indexes), the return format, or any behavioral aspects like error cases. This leaves significant gaps for the agent to understand how to use the tool effectively.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema has 100% description coverage, clearly documenting both parameters ('schema' and 'table') with their types and requirements. The description adds no additional semantic context beyond implying that 'table' is the primary identifier. Since the schema does the heavy lifting, a baseline score of 3 is appropriate, as the description doesn't compensate but also doesn't detract.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose3/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description 'Get table structure' clearly states the action (get) and target (table structure), which is better than a tautology. However, it's somewhat vague about what 'structure' entails (e.g., columns, types, constraints) and doesn't distinguish it from sibling tools like 'list_tables' or 'query', which might also provide structural information.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides no guidance on when to use this tool versus alternatives. It doesn't mention prerequisites (e.g., needing a database connection via 'connect_db'), differentiate it from 'list_tables' (which lists table names) or 'query' (which might retrieve data), or specify use cases like schema exploration. This leaves the agent with minimal context for selection.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

executeB

Execute an INSERT, UPDATE, or DELETE query

ParametersJSON Schema
NameRequiredDescriptionDefault
paramsNoQuery parameters (optional)
sqlYesSQL query (INSERT, UPDATE, DELETE) (use $1, $2, etc. for parameters)

TDQS

B3.2/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are provided, so the description carries full burden. It states the tool executes queries but doesn't disclose critical behavioral traits: whether it requires authentication, what permissions are needed, if changes are reversible, transaction behavior, error handling, or what happens on success/failure. For a mutation tool with zero annotation coverage, this is a significant gap.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is extremely concise (one sentence) with zero waste. It's front-loaded with the core purpose and includes essential parameter syntax guidance. Every word earns its place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given this is a mutation tool with no annotations and no output schema, the description is incomplete. It doesn't explain what happens after execution (e.g., returns row count, success/failure, error messages), security implications, or transactional behavior. For a tool that modifies data, this leaves critical gaps.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema already documents both parameters (sql and params) with their types and purposes. The description adds minimal value beyond what the schema provides, mentioning the same query types and parameter placeholders ($1, $2). Baseline 3 is appropriate when schema does the heavy lifting.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the tool executes SQL queries (INSERT, UPDATE, DELETE), providing specific verb+resource. It distinguishes from the 'query' sibling tool by specifying the types of queries it handles (data manipulation vs. likely SELECT queries). However, it doesn't explicitly mention database operations or contrast with all siblings like 'connect_db'.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines3/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implies usage for data modification queries (INSERT/UPDATE/DELETE) versus the 'query' tool likely for SELECT queries, but doesn't explicitly state when to use this tool versus alternatives. No guidance on prerequisites (like needing an established connection) or exclusions is provided.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_schemasA

List all schemas in the database

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A3.8/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are provided, so the description carries the full burden. The verb 'List' indicates a read-only operation, but the description does not disclose permissions, return format, or any potential side effects. It is minimally adequate but lacks extra behavioral context.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, concise sentence with no filler. Every word earns its place, and it is front-loaded with the action.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness4/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the tool's simplicity (no parameters, no output schema), the description is largely complete. It clearly states the tool lists all schemas in the database, which is sufficient for an agent to understand the tool's scope, though it does not mention details like whether system schemas are included.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

With zero parameters, the schema already fully covers parameter semantics. The baseline of 4 applies, and the description adds no unnecessary parameter details.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description uses a specific verb ('List') and resource ('all schemas in the database'), making the tool's purpose clear. It also implicitly distinguishes from siblings like list_tables (tables vs schemas) and describe_programmable_object.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description offers no guidance on when to use this tool versus alternatives such as list_tables or describe_table. It only states what the tool does, leaving the agent to infer usage context without explicit comparisons or exclusions.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesC

List tables in the database

ParametersJSON Schema
NameRequiredDescriptionDefault
schemaNoSchema name (default: public)

TDQS

C2.9/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden of behavioral disclosure. It states the tool lists tables, implying a read-only operation, but doesn't specify whether it returns all tables, includes system tables, requires specific permissions, or handles errors. This leaves significant gaps in understanding the tool's behavior beyond the basic action.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is extremely concise—a single, clear sentence that directly states the tool's purpose without any unnecessary words. It is front-loaded and efficiently communicates the core functionality, making it easy to parse quickly.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the lack of annotations and output schema, the description is incomplete for a tool that interacts with a database. It doesn't address key contextual aspects like what the output looks like (e.g., a list of table names, metadata), error conditions, or dependencies on other tools (e.g., 'connect_db'). This leaves the agent with insufficient information to use the tool effectively in complex scenarios.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

The input schema has 100% description coverage, with the single parameter 'schema' documented as 'Schema name (default: public)'. The description adds no additional meaning about parameters beyond what the schema provides, such as explaining the significance of the schema parameter or how it affects the listing. With high schema coverage, the baseline score of 3 is appropriate.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the action ('List') and resource ('tables in the database'), making the tool's purpose immediately understandable. However, it doesn't differentiate from sibling tools like 'list_schemas' or 'describe_table', which would require specifying what makes this listing operation unique.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides no guidance on when to use this tool versus alternatives. It doesn't mention prerequisites (e.g., needing a database connection via 'connect_db'), nor does it explain how it differs from 'list_schemas' (which might list schemas instead of tables) or 'describe_table' (which might provide detailed metadata).

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

queryC

Execute a SELECT query

ParametersJSON Schema
NameRequiredDescriptionDefault
paramsNoQuery parameters (optional)
sqlYesSQL SELECT query (use $1, $2, etc. for parameters)

TDQS

C2.9/5.0
Behavior2/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the full burden of behavioral disclosure. It states the action ('Execute a SELECT query') but doesn't describe what happens: whether it returns results, error handling, performance implications, or security constraints (e.g., read-only access). For a query execution tool, this leaves critical behavioral traits unspecified.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, efficient sentence with zero waste. It's front-loaded with the core action and resource, making it immediately understandable without unnecessary elaboration. Every word earns its place by directly conveying the tool's purpose.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness2/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given the complexity of a database query tool with no annotations and no output schema, the description is incomplete. It doesn't explain return values (e.g., result sets, error formats), usage context (e.g., requires prior connection), or limitations (e.g., query timeout, row limits). For a tool that executes arbitrary SQL, this leaves significant gaps for an AI agent.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%, so the schema already documents both parameters ('sql' and 'params') with clear descriptions. The description adds no additional meaning beyond what the schema provides, such as examples of valid SQL syntax or parameter binding details. Baseline 3 is appropriate when the schema does the heavy lifting.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose4/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states the verb ('Execute') and resource ('SELECT query'), making the purpose unambiguous. It distinguishes from siblings like 'connect_db' or 'list_tables' by focusing on query execution rather than connection or metadata listing. However, it doesn't explicitly differentiate from 'execute' which might handle non-SELECT queries, leaving some ambiguity.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines2/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description provides no guidance on when to use this tool versus alternatives like 'execute' (which might handle INSERT/UPDATE) or 'describe_table' (for schema inspection). It lacks context about prerequisites (e.g., requires an established database connection) or exclusions (e.g., only for SELECT queries, not data modification).

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

  1. 6 tool updatesv1.0.0
    • First observedconnect_db
    • First observeddescribe_table
    • First observedexecute
    • First observedlist_schemas
    • First observedlist_tables
    • First observedquery

TDQS

B3.4/5.0

Scored across 6 tools

Disambiguation5/5

Each tool has a clearly distinct purpose with no ambiguity. 'execute' handles INSERT/UPDATE/DELETE, 'query' handles SELECT, 'list_tables' and 'list_schemas' are separate listing functions, 'describe_table' provides metadata, and 'connect_db' is for connection management. The separation between 'execute' and 'query' is particularly well-defined.

Naming Consistency4/5

Most tools follow a consistent verb_noun pattern (describe_table, list_schemas, list_tables). The main deviation is 'execute' and 'query' which use single verbs without objects, and 'connect_db' uses a verb_object pattern but with abbreviated 'db' instead of 'database'. The naming is still readable and mostly predictable.

Tool Count5/5

Six tools is well-scoped for a PostgreSQL server. This covers essential database operations: connection management, schema/table discovery, metadata inspection, and both read (SELECT) and write (INSERT/UPDATE/DELETE) operations. Each tool earns its place without bloat.

Completeness4/5

The toolset covers core PostgreSQL workflows well: discovery (list_schemas, list_tables), inspection (describe_table), and query execution (query, execute). Minor gaps include lack of transaction control tools (BEGIN, COMMIT, ROLLBACK) and database management operations (CREATE/DROP database/table), but agents can work around these using the existing execute tool.

Maintenance

ActivityInactive
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • F
    license
    Not graded
    quality
    D
    maintenance
    Provides read-only access to PostgreSQL databases with schema inspection, query execution in multiple formats (JSON, CSV, Markdown), and query history tracking with built-in security features.
    -
  • A
    license
    Not graded
    quality
    D
    maintenance
    Read-only PostgreSQL MCP server that enables running SELECT queries, listing tables and schemas, and describing columns, with built-in protection against writes and malicious SQL attacks.
    977 npm
    MIT
  • A
    license
    Not graded
    quality
    D
    maintenance
    Enables safe interaction with PostgreSQL databases through read-only queries, schema exploration, and performance analysis.
    134 npm
    MIT