/**
* postgres-mcp - PostgresAdapter Unit Tests
*
* Tests for the main PostgreSQL adapter focusing on:
* - Connection lifecycle management
* - Query execution with proper error handling
* - Transaction management (begin, commit, rollback, savepoints)
* - Metadata caching behavior
* - Schema and table operations
*/
import { describe, it, expect, vi, beforeEach, afterEach } from "vitest";
import { PostgresAdapter } from "../PostgresAdapter.js";
import type {
DatabaseConfig,
HealthStatus,
QueryResult,
} from "../../../types/index.js";
import type { PoolClient } from "pg";
// Create mock pool client factory
const createMockPoolClient = (): Partial<PoolClient> => ({
query: vi.fn().mockResolvedValue({ rows: [], rowCount: 0 }),
release: vi.fn(),
});
let mockPoolClient = createMockPoolClient();
// Mock pool instance methods
const mockPoolMethods = {
initialize: vi.fn().mockResolvedValue(undefined),
shutdown: vi.fn().mockResolvedValue(undefined),
checkHealth: vi
.fn()
.mockResolvedValue({ connected: true, latencyMs: 5 } as HealthStatus),
query: vi.fn().mockResolvedValue({ rows: [] } as QueryResult),
getConnection: vi
.fn()
.mockImplementation(() => Promise.resolve(mockPoolClient as PoolClient)),
releaseConnection: vi.fn(),
getStats: vi.fn().mockReturnValue({
total: 5,
active: 1,
idle: 4,
waiting: 0,
totalQueries: 100,
}),
};
// Mock ConnectionPool as a class
vi.mock("../../../pool/ConnectionPool.js", () => {
return {
ConnectionPool: class MockConnectionPool {
initialize = mockPoolMethods.initialize;
shutdown = mockPoolMethods.shutdown;
checkHealth = mockPoolMethods.checkHealth;
query = mockPoolMethods.query;
getConnection = mockPoolMethods.getConnection;
releaseConnection = mockPoolMethods.releaseConnection;
getStats = mockPoolMethods.getStats;
},
};
});
describe("PostgresAdapter", () => {
let adapter: PostgresAdapter;
const mockConfig: DatabaseConfig = {
type: "postgresql",
host: "localhost",
port: 5432,
database: "test_db",
username: "test_user",
password: "test_password",
};
beforeEach(() => {
vi.clearAllMocks();
mockPoolClient = createMockPoolClient();
mockPoolMethods.getConnection.mockImplementation(() =>
Promise.resolve(mockPoolClient as PoolClient),
);
adapter = new PostgresAdapter();
});
afterEach(async () => {
// Ensure clean state for next test
try {
await adapter.disconnect();
} catch {
// Ignore disconnect errors in cleanup
}
});
describe("Static Properties", () => {
it("should have correct adapter type", () => {
expect(adapter.type).toBe("postgresql");
});
it("should have correct adapter name", () => {
expect(adapter.name).toBe("PostgreSQL Adapter");
});
it("should have version defined", () => {
expect(adapter.version).toBeDefined();
expect(typeof adapter.version).toBe("string");
});
});
describe("connect()", () => {
it("should initialize connection pool", async () => {
await adapter.connect(mockConfig);
// Pool should be initialized after connect
expect(adapter.getPool()).not.toBeNull();
expect(mockPoolMethods.initialize).toHaveBeenCalled();
});
it("should handle duplicate connection attempts gracefully", async () => {
await adapter.connect(mockConfig);
// Second connect just logs warning and returns (doesn't throw)
await expect(adapter.connect(mockConfig)).resolves.not.toThrow();
// Pool should still be valid
expect(adapter.getPool()).not.toBeNull();
});
it("should store configuration for later use", async () => {
await adapter.connect(mockConfig);
// Adapter should remember the connection was established
expect(adapter.getPool()).not.toBeNull();
});
});
describe("disconnect()", () => {
it("should shutdown pool gracefully", async () => {
await adapter.connect(mockConfig);
await adapter.disconnect();
// Pool should be null after disconnect
expect(adapter.getPool()).toBeNull();
expect(mockPoolMethods.shutdown).toHaveBeenCalled();
});
it("should be safe to call when not connected", async () => {
// Should not throw when not connected
await expect(adapter.disconnect()).resolves.not.toThrow();
});
it("should rollback any active transactions before disconnect", async () => {
await adapter.connect(mockConfig);
// Start a transaction
const txId = await adapter.beginTransaction();
expect(txId).toBeDefined();
// Disconnect should clean up transaction
await adapter.disconnect();
// Verify transaction cleanup (rollback should have been called)
expect(mockPoolClient.query).toHaveBeenCalledWith("ROLLBACK");
});
});
describe("getHealth()", () => {
it("should return unhealthy status when not connected", async () => {
const health = await adapter.getHealth();
expect(health.connected).toBe(false);
});
it("should return healthy status when connected", async () => {
await adapter.connect(mockConfig);
const health = await adapter.getHealth();
expect(health.connected).toBe(true);
expect(health.latencyMs).toBeDefined();
});
});
describe("executeQuery()", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("should execute query through pool", async () => {
const mockResult: QueryResult = {
rows: [{ id: 1, name: "test" }],
rowsAffected: 1,
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const result = await adapter.executeQuery(
"SELECT * FROM users WHERE id = $1",
[1],
);
expect(result.rows).toHaveLength(1);
expect(result.rows?.[0]).toHaveProperty("id", 1);
});
it("should handle empty result sets", async () => {
const mockResult: QueryResult = {
rows: [],
rowsAffected: 0,
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const result = await adapter.executeQuery(
"SELECT * FROM users WHERE id = $1",
[999],
);
expect(result.rows).toEqual([]);
});
it("should throw when not connected", async () => {
await adapter.disconnect();
await expect(adapter.executeQuery("SELECT 1")).rejects.toThrow();
});
it("should propagate database errors", async () => {
const dbError = new Error('relation "nonexistent" does not exist');
mockPoolMethods.query.mockRejectedValueOnce(dbError);
await expect(
adapter.executeQuery("SELECT * FROM nonexistent"),
).rejects.toThrow(/does not exist/);
});
});
describe("executeReadQuery() and executeWriteQuery()", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("executeReadQuery should execute SELECT statements", async () => {
const mockResult: QueryResult = {
rows: [{ count: 42 }],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const result = await adapter.executeReadQuery(
"SELECT COUNT(*) as count FROM users",
);
expect(result.rows?.[0]).toEqual({ count: 42 });
});
it("executeWriteQuery should execute INSERT statements", async () => {
const mockResult: QueryResult = {
rows: [{ id: 1 }],
rowsAffected: 1,
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const result = await adapter.executeWriteQuery(
"INSERT INTO users (name) VALUES ($1) RETURNING id",
["Test User"],
);
expect(result.rows).toHaveLength(1);
expect(result.rows?.[0]).toHaveProperty("id", 1);
});
});
describe("Transaction Management", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("beginTransaction should return transaction ID", async () => {
const txId = await adapter.beginTransaction();
expect(txId).toBeDefined();
expect(typeof txId).toBe("string");
expect(mockPoolClient.query).toHaveBeenCalledWith("BEGIN");
});
it("beginTransaction should support custom isolation levels", async () => {
await adapter.beginTransaction("SERIALIZABLE");
expect(mockPoolClient.query).toHaveBeenCalledWith(
expect.stringContaining("SERIALIZABLE"),
);
});
it("commitTransaction should commit and release connection", async () => {
const txId = await adapter.beginTransaction();
await adapter.commitTransaction(txId);
expect(mockPoolClient.query).toHaveBeenCalledWith("COMMIT");
expect(mockPoolClient.release).toHaveBeenCalled();
});
it("rollbackTransaction should rollback and release connection", async () => {
const txId = await adapter.beginTransaction();
await adapter.rollbackTransaction(txId);
expect(mockPoolClient.query).toHaveBeenCalledWith("ROLLBACK");
expect(mockPoolClient.release).toHaveBeenCalled();
});
it("should reject invalid transaction IDs", async () => {
await expect(
adapter.commitTransaction("invalid-tx-id"),
).rejects.toThrow();
await expect(
adapter.rollbackTransaction("invalid-tx-id"),
).rejects.toThrow();
});
it("getTransactionConnection should return client for valid transaction", async () => {
const txId = await adapter.beginTransaction();
const client = adapter.getTransactionConnection(txId);
expect(client).toBeDefined();
});
it("getTransactionConnection should return undefined for invalid ID", () => {
const client = adapter.getTransactionConnection("nonexistent");
expect(client).toBeUndefined();
});
});
describe("Savepoint Management", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("createSavepoint should create named savepoint", async () => {
const txId = await adapter.beginTransaction();
await adapter.createSavepoint(txId, "before_update");
expect(mockPoolClient.query).toHaveBeenCalledWith(
expect.stringContaining("SAVEPOINT"),
);
});
it("rollbackToSavepoint should rollback to named savepoint", async () => {
const txId = await adapter.beginTransaction();
await adapter.createSavepoint(txId, "sp1");
await adapter.rollbackToSavepoint(txId, "sp1");
expect(mockPoolClient.query).toHaveBeenCalledWith(
expect.stringContaining("ROLLBACK TO SAVEPOINT"),
);
});
});
describe("Metadata Caching", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("clearMetadataCache should clear all cached data", async () => {
// First call to listTables will cache results
const mockResult: QueryResult = {
rows: [
{
table_name: "users",
schema_name: "public",
table_type: "BASE TABLE",
},
],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
await adapter.listTables();
// Clear cache - should not throw
expect(() => adapter.clearMetadataCache()).not.toThrow();
});
});
describe("Tool Registration", () => {
it("should return tool definitions", () => {
const tools = adapter.getToolDefinitions();
expect(Array.isArray(tools)).toBe(true);
expect(tools.length).toBeGreaterThan(0);
// Verify tools have required properties
for (const tool of tools) {
expect(tool.name).toBeDefined();
expect(typeof tool.name).toBe("string");
expect(tool.handler).toBeDefined();
expect(typeof tool.handler).toBe("function");
}
});
it("should include core PostgreSQL tools", () => {
const tools = adapter.getToolDefinitions();
const toolNames = tools.map((t) => t.name);
// Core tools should be present
expect(toolNames).toContain("pg_read_query");
expect(toolNames).toContain("pg_write_query");
expect(toolNames).toContain("pg_list_tables");
});
it("should group tools by category", () => {
const tools = adapter.getToolDefinitions();
// Tools should have group property
const groupedTools = tools.filter((t) => t.group);
expect(groupedTools.length).toBeGreaterThan(0);
// Verify expected groups exist
const groups = new Set(tools.map((t) => t.group).filter(Boolean));
expect(groups.has("core")).toBe(true);
});
});
describe("Resource and Prompt Definitions", () => {
it("should return resource definitions", () => {
const resources = adapter.getResourceDefinitions();
expect(Array.isArray(resources)).toBe(true);
});
it("should return prompt definitions", () => {
const prompts = adapter.getPromptDefinitions();
expect(Array.isArray(prompts)).toBe(true);
});
});
describe("Schema Operations", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("getSchema should return tables, views, materializedViews, and indexes", async () => {
// Mock tables response
const mockTablesResult: QueryResult = {
rows: [
{
name: "users",
schema: "public",
type: "table",
owner: "admin",
row_count: 100,
size_bytes: 1024,
total_size_bytes: 2048,
},
{ name: "user_view", schema: "public", type: "view", owner: "admin" },
{
name: "user_cache",
schema: "public",
type: "materialized_view",
owner: "admin",
},
{
name: "orders",
schema: "public",
type: "partitioned_table",
owner: "admin",
},
],
};
// Mock indexes response
const mockIndexesResult: QueryResult = {
rows: [
{
name: "users_pkey",
table_name: "users",
schema_name: "public",
type: "btree",
is_unique: true,
columns: ["id"],
size_bytes: 512,
num_scans: 1000,
},
],
};
mockPoolMethods.query
.mockResolvedValueOnce(mockTablesResult)
.mockResolvedValueOnce(mockIndexesResult);
const schema = await adapter.getSchema();
expect(schema.tables.length).toBe(2); // table + partitioned_table
expect(schema.views?.length).toBe(1);
expect(schema.materializedViews?.length).toBe(1);
expect(schema.indexes?.length).toBe(1);
});
it("describeTable should return table info with columns", async () => {
const mockColumnsResult: QueryResult = {
rows: [
{
name: "id",
type: "integer",
nullable: false,
primary_key: true,
default_value: null,
is_generated: false,
generated_expression: null,
comment: null,
},
{
name: "name",
type: "text",
nullable: true,
primary_key: false,
default_value: null,
is_generated: false,
generated_expression: null,
comment: "User name",
},
],
};
const mockTableResult: QueryResult = {
rows: [
{
type: "table",
owner: "admin",
row_count: 100,
comment: "Users table",
is_partitioned: false,
partition_key: null,
},
],
};
mockPoolMethods.query
.mockResolvedValueOnce(mockColumnsResult)
.mockResolvedValueOnce(mockTableResult);
const tableInfo = await adapter.describeTable("users", "public");
expect(tableInfo.name).toBe("users");
expect(tableInfo.schema).toBe("public");
expect(tableInfo.columns?.length).toBe(2);
expect(tableInfo.columns?.[0].primaryKey).toBe(true);
});
it("listSchemas should return schema names", async () => {
const mockResult: QueryResult = {
rows: [{ nspname: "public" }, { nspname: "custom_schema" }],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const schemas = await adapter.listSchemas();
expect(schemas).toContain("public");
expect(schemas).toContain("custom_schema");
});
it("getTableIndexes should return indexes for a specific table", async () => {
const mockResult: QueryResult = {
rows: [
{
name: "users_pkey",
type: "btree",
is_unique: true,
columns: ["id"],
size_bytes: 512,
num_scans: 1000,
},
],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const indexes = await adapter.getTableIndexes("users", "public");
expect(indexes.length).toBe(1);
expect(indexes[0].name).toBe("users_pkey");
expect(indexes[0].tableName).toBe("users");
});
});
describe("Extension Support", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("isExtensionAvailable should return true when extension exists", async () => {
const mockResult: QueryResult = { rows: [{ available: true }] };
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const available = await adapter.isExtensionAvailable("pgvector");
expect(available).toBe(true);
});
it("isExtensionAvailable should return false when extension does not exist", async () => {
const mockResult: QueryResult = { rows: [{ available: false }] };
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
const available = await adapter.isExtensionAvailable("nonexistent");
expect(available).toBe(false);
});
});
describe("Capabilities", () => {
it("getCapabilities should return all supported features", () => {
const caps = adapter.getCapabilities();
expect(caps.json).toBe(true);
expect(caps.fullTextSearch).toBe(true);
expect(caps.vector).toBe(true);
expect(caps.geospatial).toBe(true);
expect(caps.transactions).toBe(true);
expect(caps.preparedStatements).toBe(true);
expect(caps.connectionPooling).toBe(true);
expect(caps.partitioning).toBe(true);
expect(caps.replication).toBe(true);
expect(caps.cte).toBe(true);
expect(caps.windowFunctions).toBe(true);
});
it("getSupportedToolGroups should return all tool groups", () => {
const groups = adapter.getSupportedToolGroups();
expect(groups).toContain("core");
expect(groups).toContain("transactions");
expect(groups).toContain("jsonb");
expect(groups).toContain("vector");
expect(groups).toContain("postgis");
expect(groups).toContain("cron");
expect(groups).toContain("partman");
expect(groups).toContain("codemode");
});
});
describe("executeOnConnection", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("should execute query on specific client", async () => {
const txId = await adapter.beginTransaction();
const client = adapter.getTransactionConnection(txId);
expect(client).toBeDefined();
(mockPoolClient.query as ReturnType<typeof vi.fn>).mockResolvedValueOnce({
rows: [{ id: 1 }],
rowCount: 1,
command: "SELECT",
});
const result = await adapter.executeOnConnection(
client!,
"SELECT * FROM users",
);
expect(result.rows).toHaveLength(1);
expect(result.executionTimeMs).toBeDefined();
await adapter.rollbackTransaction(txId);
});
it("should throw QueryError on client query failure", async () => {
const txId = await adapter.beginTransaction();
const client = adapter.getTransactionConnection(txId);
(mockPoolClient.query as ReturnType<typeof vi.fn>).mockRejectedValueOnce(
new Error("Query execution failed"),
);
await expect(
adapter.executeOnConnection(client!, "INVALID SQL"),
).rejects.toThrow(/Query failed/);
await adapter.rollbackTransaction(txId);
});
});
describe("Savepoint Operations", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("releaseSavepoint should release named savepoint", async () => {
const txId = await adapter.beginTransaction();
await adapter.createSavepoint(txId, "sp1");
await adapter.releaseSavepoint(txId, "sp1");
expect(mockPoolClient.query).toHaveBeenCalledWith(
expect.stringContaining("RELEASE SAVEPOINT"),
);
await adapter.rollbackTransaction(txId);
});
it("releaseSavepoint should throw for invalid transaction", async () => {
await expect(
adapter.releaseSavepoint("invalid-tx", "sp1"),
).rejects.toThrow(/Transaction not found/);
});
});
describe("Tool Definition Caching", () => {
it("should cache tool definitions on second call", () => {
const tools1 = adapter.getToolDefinitions();
const tools2 = adapter.getToolDefinitions();
// Both should return the same array reference (cached)
expect(tools1).toBe(tools2);
});
});
// =========================================================================
// Phase 1 Coverage Tests: Lines 89-93, 144-145, 213, 264, 279-280, 324, 348
// =========================================================================
describe("Cache TTL Expiration", () => {
it("should return undefined and delete expired cache entries", async () => {
await adapter.connect(mockConfig);
// Mock getAllIndexes to populate cache
const mockResult: QueryResult = {
rows: [
{
name: "test_idx",
table_name: "users",
schema_name: "public",
type: "btree",
is_unique: false,
columns: ["id"],
size_bytes: 1024,
num_scans: 100,
},
],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResult);
// Call getSchema which triggers getAllIndexes and caches
await adapter.getSchema();
// Clear and re-mock for second call
mockPoolMethods.query.mockResolvedValueOnce({ rows: [] }); // listTables
mockPoolMethods.query.mockResolvedValueOnce({
rows: [
{
name: "new_idx",
table_name: "orders",
schema_name: "public",
type: "btree",
is_unique: true,
columns: ["order_id"],
size_bytes: 2048,
num_scans: 50,
},
],
}); // getAllIndexes - new data after cache expires
// Advance time past cache TTL (default 30s) by manipulating adapter
// Access private cache via prototype hack to test TTL
const adapterAny = adapter as unknown as {
metadataCache: Map<string, { data: unknown; timestamp: number }>;
cacheTtlMs: number;
};
// Set an entry with an old timestamp to trigger expiration
adapterAny.metadataCache.set("all_indexes", {
data: [{ name: "old_idx" }],
timestamp: Date.now() - 60000, // 60 seconds ago, past 30s TTL
});
// Calling getSchema again should detect expired cache, delete it, and refetch
await adapter.getSchema();
// Verify the old cache was cleared and new query was made
expect(mockPoolMethods.query).toHaveBeenCalled();
});
});
describe("Connection Failure Handling", () => {
it("should reset pool to null and throw ConnectionError when initialize fails", async () => {
const initError = new Error("ECONNREFUSED: Connection refused");
mockPoolMethods.initialize.mockRejectedValueOnce(initError);
await expect(adapter.connect(mockConfig)).rejects.toThrow(
/Failed to connect/,
);
// Pool should be set to null after failure
expect(adapter.getPool()).toBeNull();
});
});
describe("Query Field Metadata Mapping", () => {
beforeEach(async () => {
await adapter.connect(mockConfig);
});
it("should map field metadata from pg result when fields array is present", async () => {
const mockResultWithFields = {
rows: [{ id: 1, name: "test" }],
rowCount: 1,
command: "SELECT",
fields: [
{
name: "id",
tableID: 12345,
columnID: 1,
dataTypeID: 23,
dataTypeSize: 4,
dataTypeModifier: -1,
format: "text",
},
{
name: "name",
tableID: 12345,
columnID: 2,
dataTypeID: 25,
dataTypeSize: -1,
dataTypeModifier: -1,
format: "text",
},
],
};
mockPoolMethods.query.mockResolvedValueOnce(mockResultWithFields);
const result = await adapter.executeQuery("SELECT id, name FROM users");
expect(result.fields).toBeDefined();
expect(result.fields).toHaveLength(2);
expect(result.fields?.[0].name).toBe("id");
expect(result.fields?.[0].dataTypeID).toBe(23);
expect(result.fields?.[1].name).toBe("name");
});
});
describe("Transaction Edge Cases", () => {
it("beginTransaction should throw ConnectionError when not connected", async () => {
// Adapter is not connected (no connect() called)
await expect(adapter.beginTransaction()).rejects.toThrow(/Not connected/);
});
it("beginTransaction should release client and throw TransactionError when BEGIN fails", async () => {
await adapter.connect(mockConfig);
// Mock client.query to throw on BEGIN
(mockPoolClient.query as ReturnType<typeof vi.fn>).mockRejectedValueOnce(
new Error("database is shutting down"),
);
await expect(adapter.beginTransaction()).rejects.toThrow(
/Failed to begin transaction/,
);
// Verify client was released
expect(mockPoolClient.release).toHaveBeenCalled();
});
it("createSavepoint should throw TransactionError for non-existent transaction", async () => {
await adapter.connect(mockConfig);
await expect(
adapter.createSavepoint("nonexistent-tx-id", "sp1"),
).rejects.toThrow(/Transaction not found/);
});
it("rollbackToSavepoint should throw TransactionError for non-existent transaction", async () => {
await adapter.connect(mockConfig);
await expect(
adapter.rollbackToSavepoint("nonexistent-tx-id", "sp1"),
).rejects.toThrow(/Transaction not found/);
});
});
});
