/**
* Database Tools - Query and inspect PostgreSQL
*/
import { z } from 'zod'
import { query } from '../db.js'
// ============================================
// SCHEMAS
// ============================================
export const queryDbSchema = z.object({
sql: z.string().describe('SQL SELECT query to execute'),
limit: z.number().optional().describe('Limit results (default: 100)'),
})
export const inspectTableSchema = z.object({
tableName: z.string().describe('Name of the table to inspect'),
})
export const listTablesSchema = z.object({
schema: z.string().optional().describe('Schema name (default: public)'),
})
// ============================================
// TOOLS
// ============================================
export async function queryDb(params: z.infer<typeof queryDbSchema>): Promise<string> {
const { sql, limit = 100 } = params
// Security: Only allow SELECT queries
const trimmed = sql.trim().toLowerCase()
if (!trimmed.startsWith('select')) {
return JSON.stringify({ error: 'Only SELECT queries are allowed' }, null, 2)
}
try {
// Add LIMIT if not present
const finalSql = sql.includes('LIMIT') ? sql : `${sql} LIMIT ${limit}`
const result = await query(finalSql)
return JSON.stringify(
{
rows: result.rows,
rowCount: result.rowCount,
query: finalSql,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
export async function inspectTable(
params: z.infer<typeof inspectTableSchema>
): Promise<string> {
const { tableName } = params
try {
// Get table schema
const result = await query(
`
SELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_name = $1
ORDER BY ordinal_position
`,
[tableName]
)
if (result.rows.length === 0) {
return JSON.stringify(
{ error: `Table '${tableName}' not found` },
null,
2
)
}
return JSON.stringify(
{
table: tableName,
columns: result.rows,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
export async function listTables(
params: z.infer<typeof listTablesSchema>
): Promise<string> {
const { schema = 'public' } = params
try {
const result = await query(
`
SELECT
table_name,
table_type
FROM information_schema.tables
WHERE table_schema = $1
ORDER BY table_name
`,
[schema]
)
return JSON.stringify(
{
schema,
tables: result.rows,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
