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
PostgreSQL server hostname or IP address
PostgreSQL server port number
PostgreSQL username for authentication
SQL query or queries to execute. Can contain:
- Single SQL statement
- Multiple semicolon-separated statements
- Mixed SELECT and DML statements
SSL connection mode (disable, require, prefer)
Response
Single Statement Response
Array of result rows for SELECT queries
Column metadata for result set
Number of rows affected/returned
SQL command type (SELECT, INSERT, UPDATE, DELETE, etc.)
Query execution time in milliseconds
The executed SQL statement
Error message if query failed
Multiple Statements Response
Always true for multi-statement queries
Array of QueryExecutionResult objects, one per statement
Total execution time for all statements in milliseconds
Whether statements were wrapped in a transaction
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:
- Whitespace Normalization: Removes excess whitespace
- Comment Removal: Strips SQL comments
- Statement Analysis: Determines if queries are read-only
- 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
}
- 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.