postgres

Documentation

Overview

Package postgres implements a source of MCP tools for PostgreSQL database introspection. It exposes tools for listing schemas and tables, executing read-only SQL queries, and fetching comprehensive table information including columns, indexes, constraints, and foreign key relationships with recursive traversal.

Per-type Connect establishes a *sql.DB connection from the source's `connect:` map, pings the database, and returns the four MCP tools with their embedded JSON schemas. The 3 introspection tools (list_schemas, list_tables, get_table_info) are tagged ReadOnlyHint with a closed world (OpenWorldHint: false). The execute_query tool leaves Annotations nil because the implementer cannot tell from a query string whether it is read-only — the user/middleware decides.

Index

• Variables
• func Connect(ctx context.Context, connect map[string]any, opts ...tool.Option) (tool.Response, error)
• type ConstraintInfo
• type DetailedColumnInfo
• type DetailedTableInfo
• type IndexInfo
• type PostgresExecuteRequest
• type PostgresExecuteResponse
• type PostgresSchemasRequest
• type PostgresSchemasResponse
• type PostgresTableInfoRequest
• type PostgresTableInfoResponse
• type PostgresTablesRequest
• type PostgresTablesResponse
• type ReferenceInfo
• type SchemaInfo
• type TableInfo
• type Tool

Variables

var ErrQueryNotReadOnly = errors.New(
	"query is not read-only: only SELECT, SHOW, EXPLAIN, DESCRIBE, " +
		"and WITH (read-only CTEs) queries are allowed",
)

ErrQueryNotReadOnly is returned when a query is not read-only.

Functions

func Connect

func Connect(
	ctx context.Context,
	connect map[string]any,
	opts ...tool.Option,
) (tool.Response, error)

Connect decodes the source's `connect:` map, opens a *sql.DB, pings it, and returns the four PostgreSQL introspection tools. The returned Response is ready to be registered with an MCP server by the dispatcher. The 3 introspection tools (list_schemas, list_tables, get_table_info) set Annotations: ReadOnlyHint=true with OpenWorldHint=false (closed world, no external services). The execute_query tool leaves Annotations nil because the implementer cannot tell from a query string whether it is read-only — the user/middleware decides.

Types

type ConstraintInfo

type ConstraintInfo struct {
	Name              string   `json:"name"                         jsonschema:"Constraint name"`
	Type              string   `json:"type"                         jsonschema:"Constraint type (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)"`
	Columns           []string `json:"columns"                      jsonschema:"Columns involved in the constraint"`
	ReferencedSchema  string   `json:"referenced_schema,omitempty"  jsonschema:"Referenced table schema (for foreign keys)"`
	ReferencedTable   string   `json:"referenced_table,omitempty"   jsonschema:"Referenced table name (for foreign keys)"`
	ReferencedColumns []string `json:"referenced_columns,omitempty" jsonschema:"Referenced columns (for foreign keys)"`
	OnUpdate          string   `json:"on_update,omitempty"          jsonschema:"On update action (for foreign keys)"`
	OnDelete          string   `json:"on_delete,omitempty"          jsonschema:"On delete action (for foreign keys)"`
	CheckClause       string   `json:"check_clause,omitempty"       jsonschema:"Check constraint expression"`
	IsDeferrable      bool     `json:"is_deferrable"                jsonschema:"Whether the constraint is deferrable"`
}

ConstraintInfo contains information about a table constraint

type DetailedColumnInfo

type DetailedColumnInfo struct {
	Name             string `json:"name"                        jsonschema:"Column name"`
	OrdinalPosition  int    `json:"ordinal_position"            jsonschema:"Column position in table"`
	DataType         string `json:"data_type"                   jsonschema:"Column data type"`
	UdtName          string `json:"udt_name,omitempty"          jsonschema:"User-defined type name"`
	CharMaxLength    int    `json:"char_max_length,omitempty"   jsonschema:"Maximum character length"`
	NumericPrecision int    `json:"numeric_precision,omitempty" jsonschema:"Numeric precision"`
	NumericScale     int    `json:"numeric_scale,omitempty"     jsonschema:"Numeric scale"`
	IsNullable       bool   `json:"is_nullable"                 jsonschema:"Whether the column allows NULL values"`
	IsIdentity       bool   `json:"is_identity"                 jsonschema:"Whether the column is an identity column"`
	IsGenerated      bool   `json:"is_generated"                jsonschema:"Whether the column is generated"`
	DefaultValue     string `json:"default_value,omitempty"     jsonschema:"Default value of the column"`
	Comment          string `json:"comment,omitempty"           jsonschema:"Column comment"`
	IsPrimaryKey     bool   `json:"is_primary_key"              jsonschema:"Whether the column is part of the primary key"`
}

DetailedColumnInfo contains comprehensive information about a table column

type DetailedTableInfo

type DetailedTableInfo struct {
	Schema       string               `json:"schema"                       jsonschema:"Schema name"`
	Name         string               `json:"name"                         jsonschema:"Table name"`
	Type         string               `json:"type"                         jsonschema:"Table type (BASE TABLE, VIEW, etc.)"`
	Comment      string               `json:"comment,omitempty"            jsonschema:"Table comment"`
	RowCountEst  int64                `json:"row_count_estimate,omitempty" jsonschema:"Estimated number of rows"`
	Columns      []DetailedColumnInfo `json:"columns"                      jsonschema:"List of columns with complete information"`
	Indexes      []IndexInfo          `json:"indexes"                      jsonschema:"List of indexes on the table"`
	Constraints  []ConstraintInfo     `json:"constraints"                  jsonschema:"List of table constraints"`
	References   []ReferenceInfo      `json:"references"                   jsonschema:"Foreign key relationships to other tables"`
	ReferencedBy []ReferenceInfo      `json:"referenced_by"                jsonschema:"Foreign key relationships from other tables"`
}

DetailedTableInfo contains comprehensive information about a database table

type IndexInfo

type IndexInfo struct {
	Name       string   `json:"name"       jsonschema:"Index name"`
	IsUnique   bool     `json:"is_unique"  jsonschema:"Whether the index is unique"`
	IsPrimary  bool     `json:"is_primary" jsonschema:"Whether the index is the primary key"`
	Columns    []string `json:"columns"    jsonschema:"List of column names in the index"`
	Method     string   `json:"method"     jsonschema:"Index method (btree, hash, gin, gist, etc.)"`
	IsPartial  bool     `json:"is_partial" jsonschema:"Whether this is a partial index"`
	Predicate  string   `json:"predicate"  jsonschema:"WHERE clause predicate for partial indexes"`
	Definition string   `json:"definition" jsonschema:"Full index definition SQL statement"`
}

IndexInfo contains information about a table index

type PostgresExecuteRequest

type PostgresExecuteRequest struct {
	Query  string `json:"query"            jsonschema:"SQL query to execute"`
	Params []any  `json:"params,omitempty" jsonschema:"Query parameters for prepared statement placeholders"`
}

PostgresExecuteRequest requests execution of a SQL query

type PostgresExecuteResponse

type PostgresExecuteResponse struct {
	Columns []string `json:"columns" jsonschema:"List of column names in the result set"`
	Rows    [][]any  `json:"rows"    jsonschema:"Query result rows"`
}

PostgresExecuteResponse contains query results

type PostgresSchemasRequest

type PostgresSchemasRequest struct {
}

PostgresSchemasRequest requests schema listing

type PostgresSchemasResponse

type PostgresSchemasResponse struct {
	Schemas []SchemaInfo `json:"schemas" jsonschema:"List of database schemas"`
}

PostgresSchemasResponse contains schema information

type PostgresTableInfoRequest

type PostgresTableInfoRequest struct {
	Schema string `json:"schema" jsonschema:"Schema name"`
	Table  string `json:"table"  jsonschema:"Table name"`
}

PostgresTableInfoRequest requests comprehensive table information

type PostgresTableInfoResponse

type PostgresTableInfoResponse struct {
	Tables []DetailedTableInfo `json:"tables" jsonschema:"List of tables with complete information"`
}

type PostgresTablesRequest

type PostgresTablesRequest struct {
	Schema string `json:"schema" jsonschema:"Schema name to list tables from"`
}

PostgresTablesRequest requests table listing for a schema

type PostgresTablesResponse

type PostgresTablesResponse struct {
	Tables []TableInfo `json:"tables" jsonschema:"List of tables in the schema"`
}

PostgresTablesResponse contains table information

type ReferenceInfo

type ReferenceInfo struct {
	ConstraintName string   `json:"constraint_name" jsonschema:"Foreign key constraint name"`
	FromSchema     string   `json:"from_schema"     jsonschema:"Source table schema"`
	FromTable      string   `json:"from_table"      jsonschema:"Source table name"`
	FromColumns    []string `json:"from_columns"    jsonschema:"Source table columns"`
	ToSchema       string   `json:"to_schema"       jsonschema:"Target table schema"`
	ToTable        string   `json:"to_table"        jsonschema:"Target table name"`
	ToColumns      []string `json:"to_columns"      jsonschema:"Target table columns"`
	OnUpdate       string   `json:"on_update"       jsonschema:"On update action"`
	OnDelete       string   `json:"on_delete"       jsonschema:"On delete action"`
}

ReferenceInfo contains information about a foreign key relationship

type SchemaInfo

type SchemaInfo struct {
	Name string `json:"name" jsonschema:"Schema name"`
}

SchemaInfo contains information about a database schema

type TableInfo

type TableInfo struct {
	Schema           string `json:"schema"                       jsonschema:"Schema name"`
	Name             string `json:"name"                         jsonschema:"Table name"`
	Type             string `json:"type"                         jsonschema:"Table type (BASE TABLE, VIEW, etc.)"`
	RowCountEstimate int64  `json:"row_count_estimate,omitempty" jsonschema:"Estimated number of rows"`
}

TableInfo contains information about a database table

type Tool

type Tool struct {
	// contains filtered or unexported fields
}

Tool holds the per-source state for a postgres source: the live database connection and the logger propagated from the dispatcher. The type is exported only because tests in this package construct it directly with a pre-opened *sql.DB; production code reaches it via Connect and never touches the fields.