Skip to main content
POST
/
api
/
query
curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "sql": "SELECT id, name, email FROM users LIMIT 10;",
    "sslMode": "disable"
  }'

{
  "rows": [
    { "id": 1, "name": "Alice", "email": "alice@example.com" },
    { "id": 2, "name": "Bob", "email": "bob@example.com" }
  ],
  "fields": [
    { "name": "id", "dataTypeID": 23 },
    { "name": "name", "dataTypeID": 1043 },
    { "name": "email", "dataTypeID": 1043 }
  ],
  "rowCount": 2,
  "command": "SELECT",
  "executionTime": 12,
  "statement": "SELECT id, name, email FROM users LIMIT 10;"
}

​
Overview

Executes SQL queries against PostgreSQL databases with intelligent handling of single and multi-statement queries. Supports automatic transaction wrapping, concurrent execution of read-only queries, and query optimization.

​
Request

​
host
string
required
PostgreSQL server hostname or IP address
​
port
number
required
PostgreSQL server port number
​
user
string
required
PostgreSQL username for authentication
​
password
string
PostgreSQL user password
​
database
string
required
Target database name
​
sql
string
required
SQL query or queries to execute. Can contain:
  • Single SQL statement
  • Multiple semicolon-separated statements
  • Mixed SELECT and DML statements
​
sslMode
string
SSL connection mode (disable, require, prefer)

​
Response

​
Single Statement Response

​
rows
array
Array of result rows for SELECT queries
​
fields
array
Column metadata for result set
​
rowCount
number
Number of rows affected/returned
​
command
string
SQL command type (SELECT, INSERT, UPDATE, DELETE, etc.)
​
executionTime
number
Query execution time in milliseconds
​
statement
string
The executed SQL statement
​
error
string
Error message if query failed

​
Multiple Statements Response

​
multipleResults
boolean
Always true for multi-statement queries
​
results
array
Array of QueryExecutionResult objects, one per statement
​
totalExecutionTime
number
Total execution time for all statements in milliseconds
​
transactionUsed
boolean
Whether statements were wrapped in a transaction
​
error
string
Error message if any statement failed

​
Query Execution Modes

​
Single Statement

Executes directly against the connection pool: 
SELECT * FROM users WHERE id = 1;

​
Concurrent Execution

All read-only SELECT queries execute in parallel: 
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM products;

​
Transaction Mode

Write operations (INSERT, UPDATE, DELETE) are wrapped in a transaction: 
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
UPDATE users SET active = true WHERE name = 'John';

​
Sequential Execution

Mixed read/write queries execute sequentially: 
SELECT * FROM users WHERE id = 1;
UPDATE users SET last_login = NOW() WHERE id = 1;

​
Examples

​
Single SELECT Query

curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "sql": "SELECT id, name, email FROM users LIMIT 10;",
    "sslMode": "disable"
  }'

{
  "rows": [
    { "id": 1, "name": "Alice", "email": "alice@example.com" },
    { "id": 2, "name": "Bob", "email": "bob@example.com" }
  ],
  "fields": [
    { "name": "id", "dataTypeID": 23 },
    { "name": "name", "dataTypeID": 1043 },
    { "name": "email", "dataTypeID": 1043 }
  ],
  "rowCount": 2,
  "command": "SELECT",
  "executionTime": 12,
  "statement": "SELECT id, name, email FROM users LIMIT 10;"
}

​
Multiple SELECT Queries (Concurrent)

curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "sql": "SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders;"
  }'

{
  "multipleResults": true,
  "results": [
    {
      "rows": [{ "count": "150" }],
      "fields": [{ "name": "count", "dataTypeID": 20 }],
      "rowCount": 1,
      "command": "SELECT",
      "executionTime": 8,
      "statement": "SELECT COUNT(*) FROM users;"
    },
    {
      "rows": [{ "count": "450" }],
      "fields": [{ "name": "count", "dataTypeID": 20 }],
      "rowCount": 1,
      "command": "SELECT",
      "executionTime": 6,
      "statement": "SELECT COUNT(*) FROM orders;"
    }
  ],
  "totalExecutionTime": 9
}

​
Transaction with Multiple Writes

curl -X POST http://localhost:3000/api/query \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "sql": "INSERT INTO users (name) VALUES ('\''Alice'\''); UPDATE users SET active = true WHERE name = '\''Alice'\'';"
  }'

{
  "multipleResults": true,
  "transactionUsed": true,
  "results": [
    {
      "rows": [],
      "rowCount": 1,
      "command": "INSERT",
      "executionTime": 4,
      "statement": "INSERT INTO users (name) VALUES ('Alice');"
    },
    {
      "rows": [],
      "rowCount": 1,
      "command": "UPDATE",
      "executionTime": 3,
      "statement": "UPDATE users SET active = true WHERE name = 'Alice';"
    }
  ],
  "totalExecutionTime": 15
}

​
Query Error

{
  "error": {
    "message": "relation \"nonexistent_table\" does not exist",
    "code": "42P01",
    "severity": "ERROR",
    "routine": "parserOpenTable",
    "executionTime": 5
  }
}

​
Transaction Rollback

{
  "error": "Transaction failed at statement: UPDATE users SET invalid_column = 'value'... Error: column \"invalid_column\" does not exist"
}

​
Missing Required Fields

{
  "error": "Missing required fields"
}

​
No Valid SQL

{
  "error": "No valid SQL statements found"
}

​
Query Optimization

The query parser applies automatic optimizations:
  1. Whitespace Normalization: Removes excess whitespace
  2. Comment Removal: Strips SQL comments
  3. Statement Analysis: Determines if queries are read-only
  4. Execution Strategy: Selects optimal execution mode
From /app/api/query/route.ts:34-40: 
// Parse the query into individual statements
const statements = QueryParser.parseQuery(sql)

if (statements.length === 0) {
  return NextResponse.json({ error: 'No valid SQL statements found' }, { status: 400 })
}

​
Execution Flow

​
TypeScript Interface

From /app/api/query/route.ts:5-13: 
interface QueryExecutionResult {
  rows?: any[]
  fields?: any[]
  rowCount?: number
  command?: string
  executionTime: number
  statement: string
  error?: string
}

​
Error Handling

​
Statement-Level Errors

Each statement in a multi-statement query includes an error field if it fails: 
{
  "multipleResults": true,
  "results": [
    { "rows": [...], "executionTime": 10 },
    { "error": "syntax error at or near 'FRON'", "executionTime": 2 }
  ],
  "error": "One or more statements failed"
}

​
Transaction Rollback

If any statement fails in a transaction, all changes are rolled back: 
try {
  await client.query('BEGIN')
  // Execute statements...
  await client.query('COMMIT')
} catch (error) {
  await client.query('ROLLBACK')
  throw error
}

​
Performance Considerations

  • Connection Pooling: Reuses connections across requests
  • Concurrent Reads: Parallel execution for multiple SELECT queries
  • Query Optimization: Automatic query rewriting for performance
  • Transaction Batching: Groups writes to minimize round trips
Long-running queries may timeout based on PostgreSQL server settings. Consider using statement_timeout for query limits.