> ## Documentation Index
> Fetch the complete documentation index at: https://pgtofu.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Core Concepts
> Understanding how pgtofu works under the hood
pgtofu transforms database schema management from an imperative process (writing migrations) to a declarative one (defining desired state). This page explains the key concepts that make this possible.
## The Four Phases
pgtofu operates in four distinct phases:
```mermaid theme={null}
flowchart LR
subgraph Extract
E1[Database] --> E2[JSON]
end
subgraph Parse
P1[SQL Files] --> P2[JSON]
end
subgraph Diff
D1[Compare
Schemas]
end
subgraph Generate
G1[Migration
Files]
end
Extract --> Parse --> Diff --> Generate
```
Connect to PostgreSQL and export the current schema as JSON
Parse SQL files defining your desired schema into JSON
Compare current and desired schemas to detect changes
Create ordered migration files from detected changes
## Declarative vs Imperative
### Imperative Approach (Traditional)
With traditional migration tools, you manually write each change:
```sql theme={null}
-- 20240115_add_users.up.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255)
);
-- 20240116_add_name.up.sql
ALTER TABLE users ADD COLUMN name VARCHAR(100);
-- 20240117_add_created_at.up.sql
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();
```
Problems:
* Hard to see current schema state
* Easy to miss dependencies
* Manual ordering required
* Merge conflicts are common
### Declarative Approach (pgtofu)
With pgtofu, you define the desired end state:
```sql theme={null}
-- schema/tables/users.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255),
name VARCHAR(100),
created_at TIMESTAMPTZ DEFAULT NOW()
);
```
pgtofu automatically:
* Compares with current database
* Detects what changed
* Orders changes correctly
* Generates safe migrations
## Schema Representation
Both extracted and parsed schemas are normalized to the same JSON format:
```json theme={null}
{
"tables": [{
"schema": "public",
"name": "users",
"columns": [{
"name": "id",
"data_type": "integer",
"is_nullable": false,
"is_identity": true
}, {
"name": "email",
"data_type": "character varying",
"max_length": 255,
"is_nullable": true
}],
"constraints": [{
"name": "users_pkey",
"type": "PRIMARY KEY",
"columns": ["id"]
}]
}]
}
```
This normalized representation enables accurate comparison regardless of:
* SQL formatting differences
* Type alias variations (`VARCHAR` vs `character varying`)
* Whitespace and comments
## Change Detection
The differ compares schemas field-by-field and detects:
| Category | Examples |
| ----------------- | ----------------------------------------- |
| **Additions** | New tables, columns, indexes, constraints |
| **Deletions** | Removed objects |
| **Modifications** | Changed types, defaults, definitions |
Each change is classified by severity:
* **SAFE** - Non-breaking additions
* **POTENTIALLY\_BREAKING** - May affect applications
* **BREAKING** - May cause data loss
* **DATA\_MIGRATION\_REQUIRED** - Cannot be automated
## Dependency Resolution
pgtofu uses [topological sorting](/concepts/how-it-works#dependency-resolution) to order changes correctly:
```mermaid theme={null}
flowchart TB
EXT[Extension: uuid-ossp]
EXT --> TYPE[Type: uuid]
TYPE --> TABLE[Table: users]
TABLE --> COL[Column: id]
TABLE --> IDX[Index: idx_users_id]
IDX --> FK[Foreign Key: orders.user_id]
```
Changes are ordered so dependencies are created before dependents, and dropped in reverse order.
## Migration Generation
The generator creates golang-migrate compatible files with:
* **Idempotent DDL** - `IF EXISTS` / `IF NOT EXISTS` clauses
* **Transaction control** - Wrapped in `BEGIN`/`COMMIT` when safe
* **Reversibility** - Both up and down migrations generated
* **Safety warnings** - Breaking changes are flagged
## Key Principles
Your SQL files in version control are the authoritative schema definition. The database state is derived, not authoritative.
pgtofu prioritizes generating safe, correct migrations over minimizing migration count or simplifying output.
All schema objects must be explicitly defined. pgtofu doesn't infer or assume schema elements.
Given the same current schema and desired schema, pgtofu always generates the same migrations.
## Next Steps
Technical details of the processing pipeline
Supported PostgreSQL features
TimescaleDB support
Complete command reference