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

> Discover how Graphify's PostgreSQL schema introspection transforms your live database into a knowledge graph by reconstructing SQL DDL without credentials or data modification.

- Repository: [Safi/graphify](https://github.com/safishamsi/graphify)
- Tags: internals
- Published: 2026-06-15

---

**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`](https://github.com/safishamsi/graphify/blob/main/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`](https://github.com/safishamsi/graphify/blob/main/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`](https://github.com/safishamsi/graphify/blob/main/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`](https://github.com/safishamsi/graphify/blob/main/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:

```bash
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:

```python
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:

```python
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`](https://github.com/safishamsi/graphify/blob/main/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.