/**
* postgres-mcp - Schema Tools Unit Tests
*
* Tests for PostgreSQL schema management tools with focus on
* schemas, sequences, views, functions, triggers, and constraints.
*/
import { describe, it, expect, vi, beforeEach } from "vitest";
import { getSchemaTools } from "../schema.js";
import type { PostgresAdapter } from "../../PostgresAdapter.js";
import {
createMockPostgresAdapter,
createMockRequestContext,
} from "../../../../__tests__/mocks/index.js";
describe("getSchemaTools", () => {
let adapter: PostgresAdapter;
let tools: ReturnType<typeof getSchemaTools>;
beforeEach(() => {
vi.clearAllMocks();
adapter = createMockPostgresAdapter() as unknown as PostgresAdapter;
tools = getSchemaTools(adapter);
});
it("should return 12 schema tools", () => {
expect(tools).toHaveLength(12);
});
it("should have all expected tool names", () => {
const toolNames = tools.map((t) => t.name);
expect(toolNames).toContain("pg_list_schemas");
expect(toolNames).toContain("pg_create_schema");
expect(toolNames).toContain("pg_drop_schema");
expect(toolNames).toContain("pg_list_sequences");
expect(toolNames).toContain("pg_create_sequence");
expect(toolNames).toContain("pg_list_views");
expect(toolNames).toContain("pg_create_view");
expect(toolNames).toContain("pg_list_functions");
expect(toolNames).toContain("pg_list_triggers");
expect(toolNames).toContain("pg_list_constraints");
});
it("should have group set to schema for all tools", () => {
for (const tool of tools) {
expect(tool.group).toBe("schema");
}
});
});
describe("pg_list_schemas", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list all schemas", async () => {
mockAdapter.listSchemas.mockResolvedValueOnce(["public", "app", "auth"]);
const tool = tools.find((t) => t.name === "pg_list_schemas")!;
const result = (await tool.handler({}, mockContext)) as {
schemas: string[];
count: number;
};
expect(mockAdapter.listSchemas).toHaveBeenCalled();
expect(result.schemas).toEqual(["public", "app", "auth"]);
expect(result.count).toBe(3);
});
});
describe("pg_create_schema", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should create a schema", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_schema")!;
const result = (await tool.handler({ name: "app" }, mockContext)) as {
success: boolean;
schema: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE SCHEMA "app"',
);
expect(result.success).toBe(true);
expect(result.schema).toBe("app");
});
it("should create schema with IF NOT EXISTS", async () => {
// First call: existence check, second call: create
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_schema")!;
await tool.handler({ name: "app", ifNotExists: true }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenNthCalledWith(
2,
'CREATE SCHEMA IF NOT EXISTS "app"',
);
});
it("should return alreadyExisted: false when schema does not exist", async () => {
// First call: existence check returns empty, second call: create
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_schema")!;
const result = (await tool.handler(
{ name: "new_schema", ifNotExists: true },
mockContext,
)) as {
success: boolean;
schema: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(false);
});
it("should return alreadyExisted: true when schema already exists", async () => {
// First call: existence check returns row, second call: create (no-op)
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_schema")!;
const result = (await tool.handler(
{ name: "existing_schema", ifNotExists: true },
mockContext,
)) as {
success: boolean;
schema: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(true);
});
it("should create schema with authorization", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_schema")!;
await tool.handler(
{ name: "app", authorization: "admin_user" },
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE SCHEMA "app" AUTHORIZATION "admin_user"',
);
});
});
describe("pg_drop_schema", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should drop a schema", async () => {
// First call: existence check, second call: drop
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_drop_schema")!;
const result = (await tool.handler({ name: "old_app" }, mockContext)) as {
success: boolean;
dropped: string | null;
existed: boolean;
};
expect(mockAdapter.executeQuery).toHaveBeenNthCalledWith(
2,
'DROP SCHEMA "old_app"',
);
expect(result.success).toBe(true);
expect(result.dropped).toBe("old_app");
expect(result.existed).toBe(true);
});
it("should drop schema with IF EXISTS", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_drop_schema")!;
await tool.handler({ name: "old_app", ifExists: true }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenNthCalledWith(
2,
'DROP SCHEMA IF EXISTS "old_app"',
);
});
it("should drop schema with CASCADE", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_drop_schema")!;
await tool.handler({ name: "old_app", cascade: true }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenNthCalledWith(
2,
'DROP SCHEMA "old_app" CASCADE',
);
});
it("should indicate when schema did not exist with ifExists", async () => {
// First call: existence check returns no rows
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_drop_schema")!;
const result = (await tool.handler(
{ name: "nonexistent_schema", ifExists: true },
mockContext,
)) as {
success: boolean;
dropped: string | null;
existed: boolean;
note: string;
};
expect(result.success).toBe(true);
expect(result.dropped).toBeNull();
expect(result.existed).toBe(false);
expect(result.note).toContain("did not exist");
});
});
describe("pg_list_sequences", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list all sequences", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ schema: "public", name: "users_id_seq", owned_by: "public.users.id" },
],
});
const tool = tools.find((t) => t.name === "pg_list_sequences")!;
const result = (await tool.handler({}, mockContext)) as {
sequences: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("c.relkind = 'S'"),
);
expect(result.sequences).toHaveLength(1);
});
it("should filter by schema", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_sequences")!;
await tool.handler({ schema: "app" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("n.nspname = 'app'"),
);
});
});
describe("pg_create_sequence", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should create a basic sequence", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
const result = (await tool.handler(
{ name: "order_num_seq" },
mockContext,
)) as {
success: boolean;
sequence: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE SEQUENCE "order_num_seq"',
);
expect(result.success).toBe(true);
expect(result.sequence).toBe("public.order_num_seq");
});
it("should create sequence with options", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
await tool.handler(
{
name: "custom_seq",
schema: "app",
start: 100,
increment: 10,
minValue: 1,
maxValue: 10000,
cycle: true,
},
mockContext,
);
const call = mockAdapter.executeQuery.mock.calls[0][0] as string;
expect(call).toContain('"app"."custom_seq"');
expect(call).toContain("START WITH 100");
expect(call).toContain("INCREMENT BY 10");
expect(call).toContain("MINVALUE 1");
expect(call).toContain("MAXVALUE 10000");
expect(call).toContain("CYCLE");
});
it("should accept sequenceName as alias for name parameter", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
const result = (await tool.handler(
{
sequenceName: "aliased_seq", // Using alias
},
mockContext,
)) as {
success: boolean;
sequence: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE SEQUENCE "aliased_seq"',
);
expect(result.success).toBe(true);
expect(result.sequence).toBe("public.aliased_seq");
});
it("should return alreadyExisted: false when sequence does not exist", async () => {
// First call: existence check returns empty, second call: create
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
const result = (await tool.handler(
{ name: "new_seq", ifNotExists: true },
mockContext,
)) as {
success: boolean;
sequence: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(false);
});
it("should return alreadyExisted: true when sequence already exists", async () => {
// First call: existence check returns row, second call: create (no-op)
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
const result = (await tool.handler(
{ name: "existing_seq", ifNotExists: true },
mockContext,
)) as {
success: boolean;
sequence: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(true);
});
});
describe("pg_list_views", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list views and materialized views", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{
schema: "public",
name: "active_users",
type: "view",
definition: "SELECT * FROM users WHERE active",
},
{
schema: "public",
name: "user_stats",
type: "materialized_view",
definition: "SELECT count(*) FROM users",
},
],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler({}, mockContext)) as {
views: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("IN ('v', 'm')"),
);
expect(result.views).toHaveLength(2);
});
it("should filter by schema", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_views")!;
await tool.handler({ schema: "reports" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("n.nspname = 'reports'"),
);
});
it("should exclude materialized views when requested", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_views")!;
await tool.handler({ includeMaterialized: false }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("= 'v'"),
);
});
it("should include hasMatViews in response", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ schema: "public", name: "v1", type: "view" },
{ schema: "public", name: "mv1", type: "materialized_view" },
],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler({}, mockContext)) as {
views: unknown[];
hasMatViews: boolean;
};
expect(result.hasMatViews).toBe(true);
});
it("should set hasMatViews to false when no materialized views exist", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ schema: "public", name: "v1", type: "view" }],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler({}, mockContext)) as {
hasMatViews: boolean;
};
expect(result.hasMatViews).toBe(false);
});
it("should truncate long view definitions by default (1000 chars)", async () => {
const longDef = "SELECT " + "a".repeat(1500);
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ schema: "public", name: "v1", type: "view", definition: longDef },
],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler({}, mockContext)) as {
views: Array<{ definition: string; definitionTruncated?: boolean }>;
truncatedDefinitions: number;
};
expect(result.views[0].definition.length).toBeLessThanOrEqual(1003); // 1000 + "..."
expect(result.views[0].definition.endsWith("...")).toBe(true);
expect(result.views[0].definitionTruncated).toBe(true);
expect(result.truncatedDefinitions).toBe(1);
});
it("should not truncate definitions when truncateDefinition is 0", async () => {
const longDef = "SELECT " + "a".repeat(1500);
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ schema: "public", name: "v1", type: "view", definition: longDef },
],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler(
{ truncateDefinition: 0 },
mockContext,
)) as {
views: Array<{ definition: string; definitionTruncated?: boolean }>;
truncatedDefinitions?: number;
};
expect(result.views[0].definition).toBe(longDef);
expect(result.views[0].definitionTruncated).toBeUndefined();
expect(result.truncatedDefinitions).toBeUndefined();
});
it("should use custom truncateDefinition limit", async () => {
const longDef = "SELECT " + "a".repeat(500);
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{ schema: "public", name: "v1", type: "view", definition: longDef },
],
});
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler(
{ truncateDefinition: 100 },
mockContext,
)) as {
views: Array<{ definition: string; definitionTruncated?: boolean }>;
truncatedDefinitions: number;
};
expect(result.views[0].definition.length).toBeLessThanOrEqual(103); // 100 + "..."
expect(result.views[0].definitionTruncated).toBe(true);
expect(result.truncatedDefinitions).toBe(1);
});
});
describe("pg_create_view", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should create a view", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{
name: "active_users",
query: "SELECT * FROM users WHERE active = true",
},
mockContext,
)) as {
success: boolean;
view: string;
materialized: boolean;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE VIEW "active_users" AS SELECT * FROM users WHERE active = true',
);
expect(result.success).toBe(true);
expect(result.materialized).toBe(false);
});
it("should create a materialized view", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{
name: "user_counts",
query: "SELECT count(*) FROM users",
materialized: true,
},
mockContext,
)) as {
materialized: boolean;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE MATERIALIZED VIEW "user_counts" AS SELECT count(*) FROM users',
);
expect(result.materialized).toBe(true);
});
it("should create or replace a view", async () => {
// First call: existence check, second call: create
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
await tool.handler(
{
name: "my_view",
query: "SELECT 1",
orReplace: true,
},
mockContext,
);
expect(mockAdapter.executeQuery).toHaveBeenNthCalledWith(
2,
'CREATE OR REPLACE VIEW "my_view" AS SELECT 1',
);
});
it("should return alreadyExisted: false when view does not exist with orReplace", async () => {
// First call: existence check returns empty, second call: create
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{ name: "new_view", query: "SELECT 1", orReplace: true },
mockContext,
)) as {
success: boolean;
view: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(false);
});
it("should return alreadyExisted: true when view already exists with orReplace", async () => {
// First call: existence check returns row, second call: create/replace
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [{ "?column?": 1 }],
});
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{ name: "existing_view", query: "SELECT 1", orReplace: true },
mockContext,
)) as {
success: boolean;
view: string;
alreadyExisted: boolean;
};
expect(result.success).toBe(true);
expect(result.alreadyExisted).toBe(true);
});
it("should accept viewName as alias for name parameter", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{
viewName: "aliased_view", // Using alias
query: "SELECT * FROM users",
},
mockContext,
)) as {
success: boolean;
view: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE VIEW "aliased_view" AS SELECT * FROM users',
);
expect(result.success).toBe(true);
});
});
describe("pg_list_functions", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list user-defined functions", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{
schema: "public",
name: "calculate_total",
arguments: "integer, integer",
returns: "integer",
language: "plpgsql",
},
],
});
const tool = tools.find((t) => t.name === "pg_list_functions")!;
const result = (await tool.handler({}, mockContext)) as {
functions: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("pg_proc"),
);
expect(result.functions).toHaveLength(1);
});
it("should filter functions by schema", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_functions")!;
await tool.handler({ schema: "utils" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("n.nspname = 'utils'"),
);
});
});
describe("pg_list_triggers", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list all triggers", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{
schema: "public",
table_name: "users",
name: "update_timestamp",
timing: "BEFORE",
event: "UPDATE",
function_name: "set_timestamp",
enabled: true,
},
],
});
const tool = tools.find((t) => t.name === "pg_list_triggers")!;
const result = (await tool.handler({}, mockContext)) as {
triggers: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("pg_trigger"),
);
expect(result.triggers).toHaveLength(1);
});
it("should filter triggers by table", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_triggers")!;
await tool.handler({ table: "orders" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("c.relname = 'orders'"),
);
});
});
describe("pg_list_constraints", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("should list all constraints", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({
rows: [
{
schema: "public",
table_name: "users",
name: "users_pkey",
type: "primary_key",
definition: "PRIMARY KEY (id)",
},
{
schema: "public",
table_name: "users",
name: "users_email_key",
type: "unique",
definition: "UNIQUE (email)",
},
],
});
const tool = tools.find((t) => t.name === "pg_list_constraints")!;
const result = (await tool.handler({}, mockContext)) as {
constraints: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("pg_constraint"),
);
expect(result.constraints).toHaveLength(2);
});
it("should filter constraints by table", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_constraints")!;
await tool.handler({ table: "orders" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("c.relname = 'orders'"),
);
});
it("should filter constraints by type", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_constraints")!;
await tool.handler({ type: "foreign_key" }, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("con.contype = 'f'"),
);
});
});
/**
* Parameter Smoothing Tests
*
* Tests for code mode compatibility - tools should work when called
* without explicit parameters (using undefined instead of {})
*/
describe("Parameter Smoothing", () => {
let mockAdapter: ReturnType<typeof createMockPostgresAdapter>;
let tools: ReturnType<typeof getSchemaTools>;
let mockContext: ReturnType<typeof createMockRequestContext>;
beforeEach(() => {
vi.clearAllMocks();
mockAdapter = createMockPostgresAdapter();
tools = getSchemaTools(mockAdapter as unknown as PostgresAdapter);
mockContext = createMockRequestContext();
});
it("pg_list_sequences should work without params", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_sequences")!;
// Simulate code mode: params is undefined
const result = (await tool.handler(undefined, mockContext)) as {
sequences: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalled();
expect(result.sequences).toBeDefined();
});
it("pg_list_views should work without params", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_views")!;
const result = (await tool.handler(undefined, mockContext)) as {
views: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalled();
expect(result.views).toBeDefined();
});
it("pg_list_functions should work without params", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_functions")!;
const result = (await tool.handler(undefined, mockContext)) as {
functions: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalled();
expect(result.functions).toBeDefined();
});
it("pg_list_triggers should work without params", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_triggers")!;
const result = (await tool.handler(undefined, mockContext)) as {
triggers: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalled();
expect(result.triggers).toBeDefined();
});
it("pg_list_constraints should work without params", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_constraints")!;
const result = (await tool.handler(undefined, mockContext)) as {
constraints: unknown[];
};
expect(mockAdapter.executeQuery).toHaveBeenCalled();
expect(result.constraints).toBeDefined();
});
it("pg_create_view should accept sql as alias for query", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{
name: "sql_alias_view",
sql: "SELECT * FROM users WHERE active = true", // Using sql instead of query
},
mockContext,
)) as {
success: boolean;
view: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE VIEW "sql_alias_view" AS SELECT * FROM users WHERE active = true',
);
expect(result.success).toBe(true);
});
it("pg_create_sequence should accept schema.name format", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_sequence")!;
const result = (await tool.handler(
{
name: "test_schema.order_seq",
},
mockContext,
)) as {
success: boolean;
sequence: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE SEQUENCE "test_schema"."order_seq"',
);
expect(result.success).toBe(true);
expect(result.sequence).toBe("test_schema.order_seq");
});
it("pg_list_constraints should exclude NOT NULL constraints", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_constraints")!;
await tool.handler({}, mockContext);
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
expect.stringContaining("con.contype != 'n'"),
);
});
it("pg_list_functions should filter extension-owned functions via pg_depend", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_list_functions")!;
await tool.handler({ exclude: ["ltree"] }, mockContext);
const sql = mockAdapter.executeQuery.mock.calls[0]?.[0] as string;
expect(sql).toContain("pg_depend");
expect(sql).toContain("pg_extension");
expect(sql).toContain("e.extname IN ('ltree')");
});
it("pg_create_view should accept definition as alias for query", async () => {
mockAdapter.executeQuery.mockResolvedValueOnce({ rows: [] });
const tool = tools.find((t) => t.name === "pg_create_view")!;
const result = (await tool.handler(
{
name: "def_alias_view",
definition: "SELECT * FROM users WHERE active = true", // Using definition instead of query
},
mockContext,
)) as {
success: boolean;
view: string;
};
expect(mockAdapter.executeQuery).toHaveBeenCalledWith(
'CREATE VIEW "def_alias_view" AS SELECT * FROM users WHERE active = true',
);
expect(result.success).toBe(true);
});
});
