list_migrations.ts•4.06 kB
import * as pg from "pg";
import type { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
export interface ToolProps {
pool: pg.Pool;
}
export function ListMigrations(server: McpServer, props: ToolProps) {
server.tool(
"list_migrations",
"List all migrations applied to the database (supports common migration table patterns)",
{},
async () => {
if (!props.pool) {
throw new Error("Database pool not initialized");
}
const client = await props.pool.connect();
try {
// Common migration table patterns to check
const migrationTables = [
"supabase_migrations.schema_migrations",
"public.schema_migrations",
"public.migrations",
"public._migrations",
"auth.schema_migrations",
"storage.migrations",
];
const migrations: any[] = [];
for (const table of migrationTables) {
try {
const [schema, tableName] = table.includes(".")
? table.split(".")
: ["public", table];
// Check if table exists
const tableExistsQuery = `
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = $1 AND table_name = $2
);
`;
const tableExists = await client.query(tableExistsQuery, [
schema,
tableName,
]);
if (tableExists.rows[0].exists) {
// Try to get migration data with common column patterns
let query = "";
if (table.includes("supabase_migrations")) {
query = `
SELECT
version,
name,
executed_at
FROM ${table}
ORDER BY version;
`;
} else {
// Try common migration table structures
const columnsQuery = `
SELECT column_name
FROM information_schema.columns
WHERE table_schema = $1 AND table_name = $2;
`;
const columns = await client.query(columnsQuery, [
schema,
tableName,
]);
const columnNames = columns.rows.map((row) => row.column_name);
if (columnNames.includes("version") || columnNames.includes("id")) {
const versionCol = columnNames.includes("version")
? "version"
: "id";
const nameCol = columnNames.includes("name")
? "name"
: columnNames.includes("filename")
? "filename"
: null;
const timeCol = columnNames.includes("executed_at")
? "executed_at"
: columnNames.includes("created_at")
? "created_at"
: columnNames.includes("timestamp")
? "timestamp"
: null;
query = `
SELECT
${versionCol} as version
${nameCol ? `, ${nameCol} as name` : ", NULL as name"}
${timeCol ? `, ${timeCol} as executed_at` : ", NULL as executed_at"}
FROM ${table}
ORDER BY ${versionCol};
`;
}
}
if (query) {
const result = await client.query(query);
if (result.rows.length > 0) {
migrations.push({
source_table: table,
migrations: result.rows,
});
}
}
}
} catch (error) {
// Continue to next table if this one fails
console.warn(`Failed to query ${table}:`, error);
}
}
return {
content: [
{
type: "text",
text: JSON.stringify(
{
migration_sources: migrations,
total_migrations: migrations.reduce(
(sum, source) => sum + source.migrations.length,
0,
),
},
null,
2,
),
},
],
};
} catch (error) {
throw new Error(
`Failed to list migrations: ${error instanceof Error ? error.message : "Unknown error"}`,
);
} finally {
client.release();
}
},
);
}