Table Operations

Overview

Retrieves paginated data from PostgreSQL tables and views with column metadata, sorting support, and primary key detection. Includes safety features like identifier validation and configurable page sizes.

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

schema

string

required

Schema name containing the table

table

string

required

Table or view name to fetch data from

page

number

Page number (1-indexed)Default: 1

pageSize

number

Number of rows per pageDefault: 50Range: 1-1000

sortColumn

string

Column name to sort by (must exist in table)

sortDirection

string

Sort direction: asc or descDefault: asc

sslMode

string

SSL connection mode (disable, require, prefer)

Response

columns

array

Array of ColumnInfo objects describing table columns

columns[].name

string

Column name

columns[].dataType

string

PostgreSQL data type (e.g., integer, varchar, timestamp)

columns[].isNullable

boolean

Whether the column accepts NULL values

columns[].defaultValue

string

Default value expression (may be undefined)

columns[].isPrimaryKey

boolean

Whether the column is part of the primary key

rows

array

Array of row objects (key-value pairs)

totalRows

number

Total number of rows in the table (for pagination)

currentPage

number

Current page number (1-indexed)

pageSize

number

Number of rows per page

Examples

Fetch First Page

curl -X POST http://localhost:3000/api/table-data \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "schema": "public",
    "table": "users",
    "page": 1,
    "pageSize": 50
  }'

{
  "columns": [
    {
      "name": "id",
      "dataType": "integer",
      "isNullable": false,
      "defaultValue": "nextval('users_id_seq'::regclass)",
      "isPrimaryKey": true
    },
    {
      "name": "name",
      "dataType": "character varying",
      "isNullable": false,
      "isPrimaryKey": false
    },
    {
      "name": "email",
      "dataType": "character varying",
      "isNullable": false,
      "isPrimaryKey": false
    },
    {
      "name": "created_at",
      "dataType": "timestamp without time zone",
      "isNullable": true,
      "defaultValue": "CURRENT_TIMESTAMP",
      "isPrimaryKey": false
    }
  ],
  "rows": [
    {
      "id": 1,
      "name": "Alice Johnson",
      "email": "alice@example.com",
      "created_at": "2024-01-15T10:30:00.000Z"
    },
    {
      "id": 2,
      "name": "Bob Smith",
      "email": "bob@example.com",
      "created_at": "2024-01-16T14:20:00.000Z"
    }
  ],
  "totalRows": 1500,
  "currentPage": 1,
  "pageSize": 50
}

Sorted by Column (Descending)

curl -X POST http://localhost:3000/api/table-data \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "schema": "public",
    "table": "users",
    "page": 1,
    "pageSize": 20,
    "sortColumn": "created_at",
    "sortDirection": "desc"
  }'

{
  "columns": [...],
  "rows": [
    {
      "id": 1500,
      "name": "Zoe Williams",
      "email": "zoe@example.com",
      "created_at": "2024-03-20T09:15:00.000Z"
    },
    {
      "id": 1499,
      "name": "Yves Martin",
      "email": "yves@example.com",
      "created_at": "2024-03-19T16:45:00.000Z"
    }
  ],
  "totalRows": 1500,
  "currentPage": 1,
  "pageSize": 20
}

Specific Page

curl -X POST http://localhost:3000/api/table-data \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "schema": "public",
    "table": "users",
    "page": 5,
    "pageSize": 100
  }'

{
  "columns": [...],
  "rows": [...],
  "totalRows": 1500,
  "currentPage": 5,
  "pageSize": 100
}

Error: Missing Required Fields

{
  "error": "Missing required fields"
}

Error: Table Not Found

{
  "error": "Table has no columns or does not exist"
}

Error: Invalid Identifier

{
  "error": "Invalid identifier: users; DROP TABLE users;"
}

Column Metadata Query

The endpoint fetches column information including primary keys: From /app/api/table-data/route.ts:47-64:

SELECT c.column_name,
       c.data_type,
       c.is_nullable,
       c.column_default,
       (tc.constraint_type = 'PRIMARY KEY') AS is_primary_key
FROM information_schema.columns c
LEFT JOIN information_schema.key_column_usage kcu
  ON c.table_schema = kcu.table_schema
 AND c.table_name = kcu.table_name
 AND c.column_name = kcu.column_name
LEFT JOIN information_schema.table_constraints tc
  ON kcu.table_schema = tc.table_schema
 AND kcu.table_name = tc.table_name
 AND kcu.constraint_name = tc.constraint_name
WHERE c.table_schema = $1 AND c.table_name = $2
ORDER BY c.ordinal_position

Identifier Validation

To prevent SQL injection, table and column names are validated: From /app/api/table-data/route.ts:6-13:

function quoteIdentifier(identifier: string): string {
  // Basic identifier validation and quoting to prevent injection
  // Allows letters, numbers, underscore, and $; rejects others
  if (!/^[A-Za-z_][A-Za-z0-9_$]*$/.test(identifier)) {
    throw new Error(`Invalid identifier: ${identifier}`)
  }
  return '"' + identifier.replace(/""/g, '""') + '"'
}

Only standard PostgreSQL identifiers are allowed. Special characters or SQL injection attempts will be rejected.

Pagination Implementation

From /app/api/table-data/route.ts:102-108:

// Page and offset
const limit = Math.max(1, Math.min(1000, Number(pageSize) || 50))
const pageNum = Math.max(1, Number(page) || 1)
const offset = (pageNum - 1) * limit

// Build data query
const dataSql = `SELECT * FROM ${quoteIdentifier(schema)}.${quoteIdentifier(table)}${orderByClause} LIMIT $1 OFFSET $2`

Pagination Constraints

Minimum page size: 1 row
Maximum page size: 1000 rows
Default page size: 50 rows
Minimum page number: 1

Sorting

Sort columns are validated against actual table columns: From /app/api/table-data/route.ts:88-94:

let orderByClause = ''
if (sortColumn) {
  const allowed = columns.some((c) => c.name === sortColumn)
  if (allowed) {
    const safeDirection = String(sortDirection).toLowerCase() === 'desc' ? 'desc' : 'asc'
    orderByClause = ` ORDER BY ${quoteIdentifier(sortColumn)} ${safeDirection.toUpperCase()}`
  }
}

Invalid sort columns are silently ignored (no ORDER BY clause added). Only columns returned by the metadata query can be used for sorting.

TypeScript Interfaces

From /types/database.ts:21-27:

export interface ColumnInfo {
  name: string
  dataType: string
  isNullable: boolean
  defaultValue?: string
  isPrimaryKey?: boolean
}

From /types/database.ts:29-35:

export interface TableData {
  columns: ColumnInfo[]
  rows: Record<string, any>[]
  totalRows: number
  currentPage: number
  pageSize: number
}

Performance Considerations

Row Count Query

The endpoint executes COUNT(*) for every request:

SELECT COUNT(*)::bigint AS cnt FROM "public"."users"

For large tables (millions of rows), this can be slow. Consider:

Caching total counts
Using estimated counts from pg_class.reltuples
Implementing cursor-based pagination

Query Execution Order

Column metadata (with primary key info)
Total row count (COUNT query)
Data fetch (LIMIT/OFFSET query)

Typical execution time: 10-100ms for tables with fewer than 100k rows.

Use Cases

Table Data Viewer: Browse table contents with pagination
Data Export: Fetch data in chunks for export
Query Builder: Display sample data for tables
Data Editor: Load rows for editing with primary key detection

Limitations

No filtering/search support (use query execution endpoint)
No column selection (always fetches all columns)
Sort limited to single column
Maximum 1000 rows per page

Overview

Endpoints

Types

Table Operations

Overview

Request

Response

Examples

Fetch First Page

Sorted by Column (Descending)

Specific Page

Error: Missing Required Fields

Error: Table Not Found

Error: Invalid Identifier

Column Metadata Query

Identifier Validation

Sorting

TypeScript Interfaces

Performance Considerations

Row Count Query

Query Execution Order

Use Cases

Limitations

Overview

Endpoints

Types

​Overview

​Request

​Response

​Examples

​Fetch First Page

​Sorted by Column (Descending)

​Specific Page

​Error: Missing Required Fields

​Error: Table Not Found

​Error: Invalid Identifier

​Column Metadata Query

​Identifier Validation

​Pagination Implementation

​Pagination Constraints

​Sorting

​TypeScript Interfaces

​Performance Considerations

​Row Count Query

​Query Execution Order

​Use Cases

​Limitations

Overview

Request

Response

Examples

Fetch First Page

Sorted by Column (Descending)

Specific Page

Error: Missing Required Fields

Error: Table Not Found

Error: Invalid Identifier

Column Metadata Query

Identifier Validation

Pagination Implementation

Pagination Constraints

Sorting

TypeScript Interfaces

Performance Considerations

Row Count Query

Query Execution Order

Use Cases

Limitations