Schema Operations

Overview

Fetches the complete schema tree for a PostgreSQL database, including schemas, tables, views, materialized views, and row count estimates. Automatically filters out system schemas (pg_catalog, information_schema).

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 to fetch schema from

sslMode

string

SSL connection mode (disable, require, prefer)

Response

Returns an array containing a single DatabaseInfo object with the schema tree.

[0].name

string

Database name

[0].schemas

array

Array of SchemaInfo objects

[0].schemas[].name

string

Schema name (e.g., public, myschema)

[0].schemas[].tables

array

Array of TableInfo objects in this schema

[0].schemas[].tables[].name

string

Table or view name

[0].schemas[].tables[].type

string

Object type: table, view, or materialized_view

[0].schemas[].tables[].schema

string

Parent schema name

[0].schemas[].tables[].rowCount

number

Estimated row count from pg_class.reltuples (may be undefined for views)

Examples

Fetch Schema Tree

curl -X POST http://localhost:3000/api/schema/tree \
  -H "Content-Type: application/json" \
  -d '{
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "mypassword",
    "database": "mydb",
    "sslMode": "disable"
  }'

[
  {
    "name": "mydb",
    "schemas": [
      {
        "name": "public",
        "tables": [
          {
            "name": "users",
            "type": "table",
            "schema": "public",
            "rowCount": 1500
          },
          {
            "name": "orders",
            "type": "table",
            "schema": "public",
            "rowCount": 4200
          },
          {
            "name": "user_summary",
            "type": "view",
            "schema": "public"
          },
          {
            "name": "sales_stats",
            "type": "materialized_view",
            "schema": "public",
            "rowCount": 120
          }
        ]
      },
      {
        "name": "analytics",
        "tables": [
          {
            "name": "events",
            "type": "table",
            "schema": "analytics",
            "rowCount": 98500
          }
        ]
      }
    ]
  }
]

Error: Missing Required Fields

{
  "error": "Missing required fields"
}

Error: Database Connection Failed

{
  "error": "connection to server at \"localhost\" (127.0.0.1), port 5432 failed: Connection refused"
}

Schema Filtering

System schemas are automatically excluded:

pg_catalog - PostgreSQL system catalog
information_schema - SQL standard information schema

From /app/api/schema/tree/route.ts:4-6:

function isSystemSchema(name: string): boolean {
  return name === 'pg_catalog' || name === 'information_schema'
}

Table Type Detection

The endpoint uses PostgreSQL’s pg_class.relkind to accurately determine object types:

relkind	Type	Description
`r`	`table`	Regular table
`v`	`view`	View
`m`	`materialized_view`	Materialized view
`p`	`table`	Partitioned table

From /app/api/schema/tree/route.ts:74-82:

if (stat.relkind === 'v') mappedType = 'view'
else if (stat.relkind === 'm') mappedType = 'materialized_view'
else mappedType = 'table'

Row Count Estimates

Row counts are estimates from PostgreSQL’s statistics, not exact counts:

SELECT GREATEST(c.reltuples::bigint, 0) as row_estimate
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'users'

Row counts are estimates updated by ANALYZE. Run VACUUM ANALYZE for more accurate statistics.

Query Optimization

The endpoint executes three optimized queries:

Schemas Query: Fetches schema names from information_schema.schemata
Tables Query: Retrieves table metadata from information_schema.tables
Statistics Query: Gets row estimates and relkind from pg_class

From /app/api/schema/tree/route.ts:24-55:

// Fetch schemas
const schemasRes = await dbPool.query(poolConfig, `
  SELECT schema_name
  FROM information_schema.schemata
  ORDER BY schema_name
`)

// Fetch tables and views metadata
const tablesRes = await dbPool.query(poolConfig, `
  SELECT t.table_schema as schema,
         t.table_name as name,
         t.table_type as type
  FROM information_schema.tables t
  WHERE t.table_schema NOT IN ('pg_catalog','information_schema')
  ORDER BY t.table_schema, t.table_name
`)

// Row count estimate and relkind
const statsRes = await dbPool.query(poolConfig, `
  SELECT n.nspname as schema,
         c.relname as name,
         c.relkind,
         GREATEST(c.reltuples::bigint, 0) as row_estimate
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname NOT IN ('pg_catalog','information_schema')
    AND c.relkind IN ('r','v','m','p')
`)

TypeScript Interfaces

From /types/database.ts:1-9:

export interface DatabaseInfo {
  name: string
  schemas: SchemaInfo[]
}

export interface SchemaInfo {
  name: string
  tables: TableInfo[]
}

From /types/database.ts:11-19:

export interface TableInfo {
  name: string
  type: "table" | "view" | "materialized_view"
  schema: string
  rowCount?: number
  columnCount?: number
  size?: string
  createdAt?: Date
}

Use Cases

Schema Explorer: Display database structure in tree view
Table Browser: Navigate schemas and tables
Data Dictionary: Generate documentation of database objects
Query Builder: Populate table/column dropdowns

Performance

Typical execution time: 50-200ms for databases with:

10-50 schemas
100-500 tables
Proper statistics maintained

Large databases with thousands of tables may take several seconds. Consider caching results or implementing pagination for very large schemas.

Overview

Endpoints

Types

Schema Operations

Overview

Request

Response

Examples

Fetch Schema Tree

Error: Missing Required Fields

Error: Database Connection Failed

Schema Filtering

Table Type Detection

Row Count Estimates

Query Optimization

TypeScript Interfaces

Use Cases

Performance

Overview

Endpoints

Types

​Overview

​Request

​Response

​Examples

​Fetch Schema Tree

​Error: Missing Required Fields

​Error: Database Connection Failed

​Schema Filtering

​Table Type Detection

​Row Count Estimates

​Query Optimization

​TypeScript Interfaces

​Use Cases

​Performance

Overview

Request

Response

Examples

Fetch Schema Tree

Error: Missing Required Fields

Error: Database Connection Failed

Schema Filtering

Table Type Detection

Row Count Estimates

Query Optimization

TypeScript Interfaces

Use Cases

Performance