konnosDashboard
parse-query.ts65 lines · main
1import { parse } from 'libpg-query'
2import { NextApiRequest, NextApiResponse } from 'next'
3
4const getOperation = async (sql: string) => {
5 const ast = await parse(sql)
6 const stmt = ast.stmts?.[0]?.stmt
7
8 if (!stmt) return null
9
10 if ('SelectStmt' in stmt) return 'SELECT'
11 if ('InsertStmt' in stmt) return 'INSERT'
12 if ('UpdateStmt' in stmt) return 'UPDATE'
13 if ('DeleteStmt' in stmt) return 'DELETE'
14}
15
16const getTablesInQuery = async (sql: string) => {
17 const ast = await parse(sql)
18 const tables: string[] = []
19
20 function traverse(node: unknown): void {
21 if (!node || typeof node !== 'object') return
22 const obj = node as Record<string, unknown>
23
24 if ('RangeVar' in obj) {
25 const rv = obj.RangeVar as { relname?: string; schemaname?: string }
26 if (rv.relname) tables.push(rv.schemaname ? `${rv.schemaname}.${rv.relname}` : rv.relname)
27 }
28
29 if ('relation' in obj && obj.relation && typeof obj.relation === 'object') {
30 const rv = obj.relation as { relname?: string; schemaname?: string }
31 if (rv.relname) tables.push(rv.schemaname ? `${rv.schemaname}.${rv.relname}` : rv.relname)
32 }
33
34 for (const value of Object.values(obj)) {
35 Array.isArray(value) ? value.forEach(traverse) : traverse(value)
36 }
37 }
38
39 traverse(ast)
40 return [...new Set(tables)].sort((a, b) => a.localeCompare(b))
41}
42
43export default async function handler(req: NextApiRequest, res: NextApiResponse) {
44 if (req.method !== 'POST') {
45 res.setHeader('Allow', ['POST'])
46 return res.status(405).json({ error: `Method ${req.method} Not Allowed` })
47 }
48
49 try {
50 const { sql } = req.body
51
52 if (typeof sql !== 'string' || sql.trim().length === 0) {
53 return res.status(400).json({ error: 'Missing or invalid "sql" in request body' })
54 }
55
56 const tables = await getTablesInQuery(sql)
57 const operation = await getOperation(sql)
58 return res.status(200).json({ tables, operation })
59 } catch (error) {
60 const message =
61 (error as { sqlDetails?: { message?: string } })?.sqlDetails?.message ??
62 (error instanceof Error ? error.message : 'Failed to parse SQL')
63 return res.status(400).json({ error: message })
64 }
65}