Skip to main content
POST
/
api
/
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
          }
        ]
      }
    ]
  }
]

​
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:
relkindTypeDescription
rtableRegular table
vviewView
mmaterialized_viewMaterialized view
ptablePartitioned 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:
  1. Schemas Query: Fetches schema names from information_schema.schemata
  2. Tables Query: Retrieves table metadata from information_schema.tables
  3. 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.