How Graphify's PostgreSQL Schema Introspection Works: From Live Database to Knowledge Graph

Graphify's PostgreSQL schema introspection works by reconstructing the database schema as SQL DDL statements and feeding them to a generic SQL extractor, turning a live PostgreSQL connection into a knowledge graph without persisting credentials or modifying source data.

Graphify can transform a live PostgreSQL database into a structured knowledge graph through a specialized introspection pipeline. This process, implemented in graphify/pg_introspect.py, queries the database catalog to generate anonymized DDL statements that represent tables, views, functions, and foreign key relationships. The generated DDL is then parsed by Graphify's tree-sitter SQL extractor to produce graph nodes and edges.

The Introspection Pipeline

The introspection workflow executes eight distinct stages to safely extract schema metadata from PostgreSQL.

Dependency Verification and Connection

The process begins with a strict dependency check. Graphify attempts to import psycopg (PostgreSQL 3 driver), raising an ImportError with installation instructions if the graphify[postgres] extra is missing.

Once dependencies are satisfied, introspect_postgres() establishes a connection using psycopg.connect(dsn or ""). Supplying an empty DSN string allows the connection to read standard PostgreSQL environment variables (PGHOST, PGUSER, etc.). If connection fails, the raw OperationalError is sanitized to remove credential information before being re-raised as a ConnectionError to prevent secret leakage in logs.

Transaction Isolation and Stability

Immediately after connecting, Graphify executes SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE. This creates a stable snapshot of the database catalog, ensuring consistent reads while guaranteeing the transaction cannot modify any data.

Catalog Queries Against information_schema

With the safe transaction established, Graphify queries four specific information_schema views, filtering out system schemas (pg_catalog and information_schema):

1. Tables – Basic table metadata
2. Views – View definitions where accessible
3. Routines – Functions and procedures with their implementation languages
4. Foreign Keys – Constraint relationships including composite keys

These queries reside in graphify/pg_introspect.py (lines 33-88) and collect only the metadata necessary for graph construction.

DDL Generation and Identifier Quoting

Rather than extracting full table definitions, Graphify generates stub DDL optimized for graph extraction:

• Tables: Emits CREATE TABLE "name" (id INT) stubs (full column lists are unnecessary for relationship mapping)
• Views: Uses the actual definition when available; otherwise generates SELECT 1 stubs
• Functions/Procedures: Captures the body verbatim when permissions allow; otherwise uses minimal stubs. All are emitted as CREATE FUNCTION ... LANGUAGE <lang> to ensure the SQL parser can handle them uniformly
• Foreign Keys: Generates complete ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY (...) REFERENCES ... (...) statements to preserve relationship semantics

The helper function _quote_ident (lines 6-30 in pg_introspect.py) double-quotes all identifiers to preserve case sensitivity, hyphens, and reserved words.

Virtual Path Creation and SQL Extraction

To prevent credential exposure in graph metadata, Graphify constructs a virtual path using psycopg.conninfo.conninfo_to_dict to extract only the host and database name. This creates a fake URI like postgresql://myhost/mydb that downstream components treat as a file source.

The generated DDL string is then passed to extract_sql(virtual_path, content=ddl_string) from graphify/extract.py. This function uses the tree-sitter SQL grammar to parse the DDL and emit graph nodes for tables, columns, views, functions, and foreign key edges.

Security and Error Handling

Graphify implements several safeguards to protect credentials and handle permission restrictions gracefully.

Credential Sanitization: Connection error messages are scrubbed to remove password and connection string details before exceptions bubble up to the user.

Graceful Degradation: When the database user lacks permission to view view definitions or routine bodies, Graphify inserts harmless stubs rather than aborting. This ensures the graph contains the object's presence and relationships even without implementation details.

Read-Only Guarantees: The SERIALIZABLE READ ONLY transaction level prevents accidental writes while ensuring a consistent snapshot of the catalog during introspection.

Using the PostgreSQL Introspection API

Command-Line Interface

Extract a live PostgreSQL database via the --postgres flag:

graphify extract --postgres "postgresql://myuser:mysecret@db.example.com/mydb"

The CLI internally calls introspect_postgres(dsn) and outputs the resulting graph as JSON.

Programmatic Integration

Import the introspection function to incorporate PostgreSQL schemas into larger graph workflows:

from graphify.pg_introspect import introspect_postgres

# Connect using environment variables (PGHOST, PGUSER, etc.)

graph_fragment = introspect_postgres()

# Merge with other extracts

# Result structure: {"nodes": [...], "edges": [...]}

Debugging the Generated DDL

To inspect the intermediate DDL that Graphify constructs for debugging:

from graphify.pg_introspect import introspect_postgres

def debug_ddl(dsn):
    result = introspect_postgres(dsn)
    # The DDL is stored in the content field during extraction

    ddl = result.get("content", "")
    print(ddl)

debug_ddl("postgresql://user:pw@localhost/testdb")

Summary

• Graphify's PostgreSQL introspection lives in graphify/pg_introspect.py and uses psycopg for database connectivity
• The process generates stub DDL from catalog queries against information_schema, preserving only shape and relationships rather than full column definitions
• Credential sanitization occurs at both the connection error level and through virtual path generation to prevent secret leakage
• Foreign key constraints are extracted as ALTER TABLE statements to create accurate relationship edges in the knowledge graph
• The generated DDL is parsed by extract_sql using tree-sitter SQL grammar to produce the final graph structure
• All identifiers are double-quoted via _quote_ident to handle case sensitivity and special characters

Frequently Asked Questions

Why does Graphify use stub DDL instead of full table definitions?

Graphify only requires schema shape (object names, types, and relationships) to construct the knowledge graph. Full column definitions are usually unnecessary for graph topology, and stubs keep the extraction fast while ensuring foreign key edges remain accurate. When view definitions or function bodies are inaccessible due to permissions, stubs allow the graph to still represent the object's existence and its relationships to other entities.

How does Graphify protect database credentials during introspection?

Graphify implements two-layer credential protection. First, connection failures sanitize the OperationalError message to remove connection strings before re-raising as ConnectionError. Second, the DSN is parsed to extract only host and database name, creating a virtual path (postgresql://host/db) that downstream components treat as a file source. The actual credentials never appear in graph metadata or error logs.

What PostgreSQL permission level is required for introspection?

Graphify requires read access to information_schema tables for tables, views, routines, and constraints. The user needs SELECT privileges on these catalog views. If the user cannot view the definition of a view (pg_catalog.pg_views.definition) or the body of a function (pg_catalog.pg_proc.prosrc), Graphify gracefully falls back to stub definitions rather than failing, ensuring the graph can still represent the schema structure.

Can Graphify introspect PostgreSQL databases using connection parameters instead of DSN strings?

Yes. When calling introspect_postgres() with an empty DSN string or omitting the argument entirely, Graphify passes the empty string to psycopg.connect(""). This allows psycopg to read standard PostgreSQL environment variables including PGHOST, PGPORT, PGUSER, PGPASSWORD, and PGDATABASE, supporting both DSN-based and environment-based authentication workflows.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}