SchemaTongs for PostgreSQL

Overview

SchemaTongs is the starting point of the SchemaSmith workflow. It connects to a PostgreSQL database and extracts metadata representing the current state of that database - tables, procedures, views, etc. This metadata becomes the raw material that feeds into the SchemaSmith toolchain for shaping and deploying your desired state across environments. We call this initial process of extracting schema definitions casting the database.

Simply point the application at each of your databases and everything within them will be extracted out to the folder of your choosing.

Where SchemaTongs Fits

SchemaTongs extracts your schema into metadata.
SchemaHammer provides powerful search and edit capabilities for your metadata.
SchemaQuench applies your schema and data metadata to your Postgres server.
DataTongs extracts your seed data into metadata

Quick Start Guide

1. Configure your appSettings.json

Point SchemaTongs at your source database. PostgreSQL requires username and password authentication. All object types are extracted by default. Use ObjectList to limit extraction to specific objects:

{
  "Source": {
    "Server": "localhost",
    "Database": "dvdrental",
    "User": "postgres",
    "Password": "your_password"
  },
  "Product": {
    "Path": "/opt/schemapackage",
    "Name": "DVDRental"
  },
  "Template": {
    "Name": "DVDRental"
  },
  "ShouldCast": {
    "Tables": true,
    "Schemas": true,
    "DomainTypes": true,
    "EnumTypes": true,
    "CompositeTypes": true,
    "Functions": true,
    "Aggregates": true,
    "Procedures": true,
    "Sequences": true,
    "Rules": true,
    "Triggers": true,
    "Views": true,
    "ObjectList": ""
  }
}

2. Run SchemaTongs

Execute SchemaTongs from the command line and the casting process will begin.

> SchemaTongs

3. Shape your metadata

Move on to editing your metadata with SchemaHammer.

See the SchemaTongs Walkthrough to help you get started with the tool.

Tip

The same concepts for how to override appSettings.json for SchemaQuench apply to SchemaTongs. You can also use Command Line Options to specify the log file location or an alternate config file.

What is Exported

SchemaTongs extracts 12 object types from PostgreSQL. Tables are exported as JSON definitions; all other objects are exported as SQL scripts. The Functions flag controls three related directories: Functions, Trigger Functions, and Window Functions.

Object Type	Directory	Format
Tables	`Tables/`	JSON (`schema.table.json`)
Schemas	`Schemas/`	SQL (`schema.sql`)
Domain Types	`Domain Types/`	SQL
Enum Types	`Enum Types/`	SQL
Composite Types	`Composite Types/`	SQL
Functions	`Functions/`	SQL (`schema.name.sql`)
Trigger Functions	`Trigger Functions/`	SQL (`schema.name.sql`)
Window Functions	`Window Functions/`	SQL (`schema.name.sql`)
Aggregates	`Aggregates/`	SQL (`schema.name.sql`)
Procedures	`Procedures/`	SQL (`schema.name.sql`)
Sequences	`Sequences/`	SQL
Rules	`Rules/`	SQL
Triggers	`Triggers/`	SQL (`schema.table.trigger.sql`)
Views	`Views/`	SQL (`schema.name.sql`)

All directories are relative to Product:Path/Templates/Template:Name/.

Package Initialization

On the first cast to a new product path, SchemaTongs creates Product.json and Template.json with default values, along with JSON schema files in .json-schemas/ for editor validation. All script folders are created physically on disk.

On subsequent casts, Product.json and Template.json are never overwritten; only the extracted object scripts and table JSON files are refreshed. New templates are added to the product's TemplateOrder automatically.

After your first cast, review and customize Product.json and Template.json, particularly ValidationScript, DatabaseIdentificationScript, and VersionStampScript, to match your deployment needs.

Extraction Details

ObjectList Filtering

By default, SchemaTongs extracts all objects from the database. Set ShouldCast:ObjectList to a comma or semicolon-separated list of object names to limit extraction to specific objects only.

Matching is case-insensitive and supports both bare names and schema-qualified names:

"ObjectList": "film" - matches public.film, archive.film, etc.
"ObjectList": "public.film" - matches only public.film
"ObjectList": "film,get_customer,public.rental_view" - matches multiple objects

ObjectList uses exact string matching only, no wildcards or regular expressions. The filter applies to all object types (tables, views, functions, etc.).

Excluded Objects

The following objects are automatically excluded from extraction:

Exclusion	Reason
`pg_catalog`	PostgreSQL system catalog schema.
`information_schema`	SQL standard system views.
`pg_toast`	PostgreSQL internal TOAST storage schema.
`SchemaSmith` schema	Helper infrastructure installed by SchemaTongs itself.
`public` schema	Excluded from the Schemas export only. Objects within `public` (tables, views, etc.) are still extracted.
`pg_temp_%`	Temporary schemas.
`pg_toast_temp_%`	Temporary TOAST schemas.

Helper Infrastructure

Before extraction, SchemaTongs installs helper objects in a SchemaSmith schema on the source database. These objects are used during extraction and deployment. They include:

Functions: ExecuteOrDebug, QuoteColumnList, QuoteIndexColumnList, StripParenWrapping, FormatJson, GenerateTableJson - extraction helpers and JSON formatting.
Quench procedures: TableQuench, IndexOnlyQuench, MissingTableAndColumnQuench, ModifiedTableQuench, MissingIndexesAndConstraintsQuench - used by SchemaQuench at deployment time.
Ownership management: ValidateTableOwnership, FixupTableOwnership, FixupIndexOwnership - functions that track which product owns each table and index.
Tables: CompletedMigrationScripts - tracks which migration scripts have been executed. ProductOwnership - maps tables and indexes to their owning product, preventing cross-product conflicts.

These objects are excluded from extraction output automatically. The SkipKindlingForge command-line argument can skip this installation step if the helpers are already present.

Re-extraction Preservation

When SchemaTongs re-extracts into an existing schema package, it preserves user-configured properties that were added after the initial extraction. This means you can safely re-run SchemaTongs without losing your customizations.

Preserved on table JSON files:

Data delivery properties: ContentFile, MergeType, MatchColumns, MergeFilter, MergeDisableTriggers, MergeDisableRules, MergeUpdateDescendents
OldName (for tracking table renames)
ShouldApplyExpression (conditional deployment logic)
All custom properties on tables, columns, indexes, foreign keys, check constraints, statistics, and exclude constraints

Components are matched by name (case-insensitive) or by OldName for renamed objects. Schema structure is always refreshed from the database; only user-defined metadata is preserved.

Script Formatting

Script formatting varies by object type. Functions and procedures are extracted using PG_GET_FUNCTIONDEF(), which produces a complete CREATE OR REPLACE FUNCTION statement. Views use CREATE OR REPLACE VIEW. Rules and triggers use CREATE OR REPLACE variants.

Domain types, enum types, and composite types are wrapped in DO $$...END$$; blocks with IF NOT EXISTS checks. Schemas use CREATE SCHEMA IF NOT EXISTS. Sequences use CREATE SEQUENCE IF NOT EXISTS.

All line endings are normalized to \r\n (Windows CRLF) regardless of the source database platform.

ShouldCast Flags Reference

Flag	Default	Description
`Tables`	`true`	Extract table definitions as JSON files. Includes columns, indexes, foreign keys, check constraints, exclude constraints, and statistics.
`Schemas`	`true`	Extract user-defined schemas (excludes `public`, `pg_catalog`, `information_schema`, and temporary schemas).
`DomainTypes`	`true`	Extract domain types (user-defined types based on existing types with optional constraints).
`EnumTypes`	`true`	Extract enumerated types.
`CompositeTypes`	`true`	Extract composite (row) types.
`Functions`	`true`	Extract functions, trigger functions, and window functions. Each category is written to its own directory.
`Aggregates`	`true`	Extract user-defined aggregate functions.
`Procedures`	`true`	Extract stored procedures (PostgreSQL 11+).
`Sequences`	`true`	Extract sequence definitions.
`Rules`	`true`	Extract query rewrite rules.
`Triggers`	`true`	Extract table triggers (DML triggers that fire on INSERT, UPDATE, DELETE, or TRUNCATE).
`Views`	`true`	Extract view definitions.
`ObjectList`	`""`	Comma or semicolon-separated list of object names to extract. Leave empty to extract all objects. See Extraction Details.

Additional Resources

SchemaTongs Walkthrough - A step-by-step guide for casting the sample product. How-to Product Demos - Sample databases converted to the SchemaSmith metadata format. Examples Configuration Settings Reference - Explore the configuration options available in appSettings.json. Reference Product Definition Guide - Structure your metadata into products. Reference SchemaHammer - An IDE for editing your metadata. Related Run SchemaTongs with Ask Forge - Execute schema extraction through the AI assistant. AI Assistant

SchemaTongs - PostgreSQL (Enterprise)