PostgreSQL Schema Reader

The PostgreSQL Schema Reader is a powerful data source tool for reading the complete table structure information of a PostgreSQL database, including column definitions, indexes, constraints, foreign key relationships, sequences, and more, returning data in a structured array format.

Core Features

• Complete Table Structure Reading - Retrieve detailed structural information for all tables
• Index and Constraint Information - Include complete index and constraint details
• Primary Key and Foreign Key Relationships - Automatically identify inter-table relationships
• View and Trigger Support - Optionally include view and trigger information
• Sequence Information Retrieval - PostgreSQL-specific sequence information
• Flexible Table Filtering - Support for including/excluding specific tables
• Connection Pool Management - Efficient database connection management
• Schema-Level Isolation - Support for PostgreSQL's Schema concept
• SSL Connection Support - Secure database connections
• High-Performance Batch Queries - Optimized batch query performance

Configuration Parameters

Database Connection Configuration

Parameter	Type	Default	Description
host	string	localhost	PostgreSQL server host address
port	number	5432	PostgreSQL server port
user	string	postgres	Database username
password	string	""	Database password
database	string	""	Target database name
schema	string	public	PostgreSQL Schema name

All connection fields have defaults (host=localhost, port=5432, user=postgres, password="", database="", schema=public) and can be overridden via inputs.config. The operator merges inputs.config over these defaults, so none of these fields are strictly required.

note

The Read Options, Performance, and Security parameters below are handled by the worker implementation and are not part of the operator's registry configuration schema (which only defines host, port, user, password, database, schema). Behavior may vary depending on the worker.

Read Options Configuration

Parameter	Type	Default	Description
includeTables	array	[]	Specific table names to read (empty array means all tables)
excludeTables	array	[]	Table names to exclude
includeViews	boolean	false	Whether to include views
includeIndexes	boolean	true	Whether to include index information
includeConstraints	boolean	true	Whether to include constraint information
includeTriggersInfo	boolean	false	Whether to include trigger information
includeSequences	boolean	false	Whether to include sequence information
includeFunctions	boolean	false	Whether to include function information (⚠️ Configuration accepted but functionality not fully implemented)

Performance Configuration

Parameter	Type	Default	Description
max	number	10	Maximum connection pool size
idleTimeoutMillis	number	30000	Idle connection timeout (milliseconds)
connectionTimeoutMillis	number	60000	Connection timeout (milliseconds)
query_timeout	number	60000	Query timeout (milliseconds)

Security Configuration

Parameter	Type	Default	Description
ssl	boolean	false	Whether to use SSL connections

Usage

Basic Usage

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public"
  }
}

Read Specific Tables

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "includeTables": ["users", "orders", "products"]
  }
}

Full Feature Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "includeViews": true,
    "includeIndexes": true,
    "includeConstraints": true,
    "includeTriggersInfo": true,
    "includeSequences": true,
    "includeFunctions": true
  }
}

SSL Connection Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "postgres",
    "password": "your_password",
    "database": "your_database",
    "schema": "public",
    "ssl": true
  }
}

Output Format

The tool returns structured data containing the following fields:

Output key

PostgreSQL returns the tables array under the key tables. In workflows, bind it as {{node.tables}}. Note that the MySQL Schema Reader uses schema instead — be sure to bind the correct key for each operator.

Success Response

{
  "success": true,
  "database": "your_database",
  "host": "localhost",
  "schema": "public",
  "tables": [
    {
      "tableName": "users",
      "tableType": "BASE TABLE",
      "schemaName": "public",
      "tableComment": "User table",
      "columns": [
        {
          "columnName": "id",
          "dataType": "integer",
          "udtName": "int4",
          "isNullable": false,
          "defaultValue": "nextval('users_id_seq'::regclass)",
          "isAutoIncrement": true,
          "comment": "User ID",
          "maxLength": null,
          "precision": 32,
          "scale": 0,
          "position": 1
        }
      ],
      "primaryKeys": ["id"],
      "foreignKeys": [],
      "indexes": [
        {
          "indexName": "users_pkey",
          "columns": ["id"],
          "isUnique": true,
          "isPrimary": true,
          "indexType": "btree"
        }
      ],
      "constraints": [
        {
          "constraintName": "users_pkey",
          "constraintType": "PRIMARY KEY",
          "checkClause": null
        }
      ],
      "triggers": [],
      "sequences": [
        {
          "sequenceName": "users_id_seq",
          "dataType": "bigint",
          "startValue": "1",
          "increment": "1",
          "maxValue": "9223372036854775807",
          "minValue": "1"
        }
      ]
    }
  ],
  "metadata": {
    "totalTables": 1,
    "readAt": "2024-01-01T12:00:00.000Z",
    "executionTime": 1500,
    "config": {
      "schema": "public",
      "includeViews": false,
      "includeIndexes": true,
      "includeConstraints": true,
      "includeTriggersInfo": false,
      "includeSequences": false,
      "includeFunctions": false
    }
  }
}

Error Response

{
  "success": false,
  "error": "Failed to connect to database: connection refused",
  "database": "your_database",
  "host": "localhost",
  "readAt": "2024-01-01T12:00:00.000Z",
  "executionTime": 500
}

Use Cases

1. Database Documentation Generation

Automatically generate database structure documentation including complete table structures, relationship diagrams, and constraint information.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "production_db",
    "schema": "public",
    "includeViews": true,
    "includeConstraints": true,
    "includeTriggersInfo": true,
    "includeSequences": true
  }
}

2. Database Migration and Synchronization

Compare database structures across environments and generate migration scripts.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "source_db",
    "schema": "app_schema",
    "includeIndexes": true,
    "includeConstraints": true
  }
}

3. Code Generator

Automatically generate ORM models, API interfaces, and other code based on database structure.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "app_db",
    "schema": "public",
    "includeTables": ["users", "orders", "products"],
    "includeConstraints": true
  }
}

4. Database Health Check

Periodically check database structure and monitor table structure changes.

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "monitoring_db",
    "schema": "public",
    "excludeTables": ["logs", "temp_data"]
  }
}

Differences from the MySQL Schema Tool

Feature	PostgreSQL Schema	MySQL Schema
Schema Support	✅ Native support	❌ Not supported
Sequence Information	✅ Supported	❌ Not supported
User-Defined Types	✅ UDT support	❌ Limited support
Constraint Types	✅ More comprehensive	✅ Basic support
Function Information	✅ Supported	❌ Not supported
Default Port	5432	3306
Connection Library	pg	mysql2
Output Key (tables array)	tables	schema

Output key difference

The two operators return the tables array under different keys: PostgreSQL uses tables ({{node.tables}}), while MySQL uses schema ({{node.schema}}). Bind the correct key when consuming the output in a workflow.

Security Configuration

Database User Permissions

It is recommended to create a dedicated read-only user:

-- Create read-only user
CREATE USER schema_reader WITH PASSWORD 'secure_password';

-- Grant connection permission
GRANT CONNECT ON DATABASE your_database TO schema_reader;

-- Grant schema usage permission
GRANT USAGE ON SCHEMA public TO schema_reader;

-- Grant table query permission
GRANT SELECT ON ALL TABLES IN SCHEMA public TO schema_reader;

-- Grant system table query permissions
GRANT SELECT ON information_schema.tables TO schema_reader;
GRANT SELECT ON information_schema.columns TO schema_reader;
GRANT SELECT ON information_schema.key_column_usage TO schema_reader;
GRANT SELECT ON information_schema.table_constraints TO schema_reader;
GRANT SELECT ON information_schema.constraint_column_usage TO schema_reader;
GRANT SELECT ON information_schema.triggers TO schema_reader;
GRANT SELECT ON information_schema.sequences TO schema_reader;

SSL Connection Configuration

SSL is recommended for production environments:

{
  "operator": "postgresql-schema",
  "config": {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "schema_reader",
    "password": "secure_password",
    "database": "production_db",
    "schema": "public",
    "ssl": true
  }
}

Performance Optimization

Connection Pool Configuration

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "large_db",
    "max": 5,
    "idleTimeoutMillis": 10000,
    "connectionTimeoutMillis": 30000,
    "query_timeout": 30000
  }
}

Table Filtering Optimization

For large databases, use table filtering to improve performance:

{
  "operator": "postgresql-schema",
  "config": {
    "host": "localhost",
    "database": "large_db",
    "includeTables": ["core_table1", "core_table2"],
    "excludeTables": ["log_table", "temp_table"]
  }
}

Troubleshooting

Common Errors

1. Connection Failed

   Error: connection refused
   Solution: Check PostgreSQL service status and network connectivity

2. Insufficient Permissions

   Error: permission denied for table
   Solution: Ensure the user has sufficient query permissions

3. Schema Does Not Exist

   Error: schema "xxx" does not exist
   Solution: Verify the Schema name is correct

4. Timeout Error

   Error: query timeout
   Solution: Increase query_timeout configuration or optimize the query

Debugging Tips

1. Enable Verbose Logging

   • Check tool execution logs
   • Review database connection logs

2. Test Connection

   • Use psql or similar tools to test connectivity
   • Verify user permissions

3. Performance Monitoring

   • Monitor query execution time
   • Check connection pool usage

Best Practices

1. Security

   • Use a dedicated read-only user
   • Enable SSL connections
   • Rotate passwords regularly

2. Performance

   • Configure connection pool size appropriately
   • Use table filtering to reduce query volume
   • Avoid running during peak hours

3. Reliability

   • Set appropriate timeout durations
   • Implement error retry mechanisms
   • Monitor tool execution status

4. Maintainability

   • Regularly update tool versions
   • Document configuration changes
   • Set up monitoring and alerts

Related Links

• PostgreSQL Information Schema Documentation
• Creating Custom Operators
• Workflow Engine Overview
• MySQL Schema Tool