README

Reads a PostgreSQL schema out of pg_dump output

Reads the schema of a PostgreSQL database out of plain-text pg_dump output, in pure PHP.

SQL parsing in PHP is largely oriented towards MySQL, so PostgreSQL's type grammar tends to be only partially supported — the multi-word standard spellings such as timestamp with time zone and double precision, the PostgreSQL-native types, and array columns. This reads them properly.

Installation

composer require calebdw/pg-schema-parser

The only runtime dependency is sad_spirit/pg_builder, whose reimplementation of PostgreSQL's own type grammar does the hard part.

Usage

use CalebDW\PgSchemaParser\PgDumpParser;

$database = (new PgDumpParser())->parse(file_get_contents('pgsql-schema.sql'));

foreach ($database->tables as $table) {
    echo $table->qualifiedName(), "\n";

    foreach ($table->columns as $column) {
        printf(
            "  %-20s %-24s %s\n",
            $column->name,
            (string) $column->type,
            $column->nullable ? 'null' : 'not null',
        );
    }
}

Canonical type names

Type names are canonicalised the way PostgreSQL itself resolves them, so each type has exactly one spelling to handle:

Declared in the dump	`$column->type->qualifiedName()`
`integer`, `int`, `int4`	`pg_catalog.int4`
`bigint`, `int8`	`pg_catalog.int8`
`boolean`	`pg_catalog.bool`
`double precision`, `float8`	`pg_catalog.float8`
`character varying(255)`	`pg_catalog.varchar`
`character(2)`	`pg_catalog.bpchar`
`timestamp(0) without time zone`	`pg_catalog.timestamp`
`timestamp with time zone`	`pg_catalog.timestamptz`
`bit varying(5)`	`pg_catalog.varbit`
`jsonb`, `uuid`, `inet`	`jsonb`, `uuid`, `inet`

Length and precision arrive separately from array dimensions, which is the distinction a consumer needs to tell string from list<string>:

$type = $database->table('t')->column('tags')->type;   // character varying(50)[]

$type->qualifiedName();  // 'pg_catalog.varchar'
$type->modifiers;        // ['50']
$type->dimensions;       // 1
$type->isArray();        // true

Enums and domains

PostgreSQL enums are a type rather than a column constraint, so the labels are attached to the database rather than the column:

$type = $database->table('people')->column('mood')->type;

$type->isBuiltin();               // false
$database->enumFor($type)->values; // ['sad', 'ok', 'happy']

Domains resolve to the type they wrap, transitively:

$database->resolve($type)->qualifiedName(); // 'pg_catalog.int4'

A type that is neither built in, nor an enum, nor a domain came from an extension — hstore, ltree, citext — or is a composite. resolve() returns it unchanged, and it is up to the consumer to decide what to do with it; treating an unrecognised type as a string is usually right.

Keys and defaults

pg_dump does not write primary keys inline, and it rewrites serial into a plain integer column plus a sequence. Both are reassembled:

$table = $database->table('users');

$table->primaryKey;                        // ['id']
$table->column('id')->isAutoIncrement();   // true, from DEFAULT nextval(...)
$table->column('id')->isRequired();        // false - the database supplies it

Scope

The accepted grammar is deliberately the subset pg_dump emits, not all of PostgreSQL DDL. That subset is small, regular and stable, because pg_dump is a code generator rather than a person. Recognised statements:

CREATE TABLE, including quoted identifiers, schema qualification, table constraints, generated and identity columns
CREATE TYPE ... AS ENUM
CREATE DOMAIN
ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY
ALTER TABLE ... ALTER COLUMN ... SET DEFAULT

Everything else — functions, triggers, indexes, views, grants, comments, extensions, SET, and psql meta-commands such as the \restrict that pg_dump has emitted since PostgreSQL 18 — is skipped rather than rejected. Dollar-quoted function bodies, string literals containing --, and comments containing SQL are all handled without being mistaken for schema.

Not currently read: foreign keys, unique constraints, indexes, check constraints, partitioning, inheritance, and views.

Contributing

See CONTRIBUTING.md.

License

MIT.

calebdw / pg-schema-parser

Maintainers

Package info

Fund package maintenance!

Statistics

Security