Database Schema

Live schema analysis and DDL export for MySQL, PostgreSQL, and SQLite.

Introduction

Schema documentation in database projects is often out of date: the table was changed, but the wiki was not. And anyone who wants to port a schema from MySQL to PostgreSQL ends up rewriting DDL by hand. Schema analysis should not rely on documentation, but come directly from the running database.

jardistools/dbschema reads live table structures from a PDO connection and exports them in three formats:

SQL DDL — dialect-correct CREATE TABLE, CREATE INDEX, ALTER TABLE ... ADD FOREIGN KEY with automatic dependency sorting
JSON — structured output for tooling, diffing, and the Jardis Builder pipeline
PHP Array — for programmatic processing

Three databases, one API: MySQL/MariaDB, PostgreSQL, and SQLite. Automatic driver detection via PDO::ATTR_DRIVER_NAME.

Installation

bash

composer require jardistools/dbschema

GitHub: jardisTools/dbSchema

Required: ext-pdo + at least one database driver (ext-pdo_mysql, ext-pdo_pgsql, ext-pdo_sqlite).

Basic Usage

Reading a Schema

php

use JardisTools\DbSchema\DbSchemaReader;

$pdo = new PDO('mysql:host=localhost;dbname=shop', 'user', 'pass');
$reader = new DbSchemaReader($pdo);

// All tables
$tables = $reader->tables();
// [['name' => 'users', 'type' => 'BASE TABLE'], ['name' => 'orders', ...]]

// Columns of a table
$columns = $reader->columns('users');
// [['name' => 'id', 'type' => 'int', 'primary' => true, 'auto_increment' => true, ...], ...]

// Indexes
$indexes = $reader->indexes('orders');

// Foreign Keys
$foreignKeys = $reader->foreignKeys('orders');

// DB type → PHP type
$reader->fieldType('varchar');       // 'string'
$reader->fieldType('decimal');       // 'float'
$reader->fieldType('timestamp');     // 'datetime'
$reader->fieldType('jsonb');         // 'array'

Exporting a Schema

php

use JardisTools\DbSchema\DbSchemaExporter;

$exporter = new DbSchemaExporter($reader);

// SQL DDL
$sql = $exporter->toSql(['users', 'orders']);
// Complete script with DROP, CREATE TABLE, INDEX, FK

// JSON (Pretty-Print)
$json = $exporter->toJson(['users', 'orders'], prettyPrint: true);

// PHP Array
$array = $exporter->toArray(['users', 'orders']);

Schema Data

Column Metadata

Each column is normalized into a consistent format across all three databases:

php

[
    'name'           => 'email',
    'type'           => 'varchar',     // Normalized, lowercase
    'length'         => 255,           // Character length (null for non-string types)
    'precision'      => null,          // Numeric precision
    'scale'          => null,          // Numeric scale
    'nullable'       => false,         // NULL allowed?
    'default'        => null,          // Default value as string
    'primary'        => false,         // Part of the primary key?
    'auto_increment' => false,         // Auto-increment?
    'enumValues'     => null,          // ['draft', 'published'] for ENUM columns
]

Filtered Column Selection

php

// Only specific columns, in the given order
$columns = $reader->columns('users', ['id', 'name', 'email']);

Type Mapping (DB → PHP)

PHP Type	Database Types
`int`	int, integer, tinyint, smallint, mediumint, bigint, serial
`string`	varchar, char, text, blob, binary, enum, uuid, bytea
`float`	decimal, numeric, float, double, real
`bool`	boolean, bool
`date`	date
`datetime`	datetime, timestamp, timestamptz
`time`	time, timetz
`array`	json, jsonb

Index Metadata

php

[
    'name'        => 'idx_user_id',
    'column_name' => 'user_id',
    'is_unique'   => false,
    'type'        => 'BTREE',
    'index_type'  => 'index',      // 'primary' | 'unique' | 'index'
    'sequence'    => 1,            // Position in multi-column index
]

Foreign Key Metadata

php

[
    'container'      => 'orders',
    'constraintName' => 'fk_orders_user_id',
    'constraintCol'  => 'user_id',
    'refContainer'   => 'users',
    'refColumn'      => 'id',
    'onUpdate'       => 'CASCADE',
    'onDelete'       => 'CASCADE',
    'sequence'       => 1,
]

DDL Export

Script Structure

The generated SQL script follows a fixed structure:

Header comment with timestamp and table list
BEGIN TRANSACTION / START TRANSACTION
DROP TABLE (in reverse dependency order)
CREATE TABLE (in dependency order, referenced tables first)
CREATE INDEX (non-PK indexes)
ALTER TABLE ... ADD FOREIGN KEY (MySQL/PostgreSQL only)
COMMIT

Dialect Differences

Feature	MySQL	PostgreSQL	SQLite
Identifier quoting	`backtick`	`"double-quote"`	`"double-quote"`
Auto-increment	`AUTO_INCREMENT`	`SERIAL` / `BIGSERIAL`	`AUTOINCREMENT`
DROP TABLE	`DROP TABLE IF EXISTS`	`DROP ... CASCADE`	`DROP TABLE IF EXISTS`
Foreign keys	`ALTER TABLE ... ADD`	`ALTER TABLE ... ADD`	Not supported (inline)
Engine	`ENGINE=InnoDB`	—	—

Example: Generated MySQL DDL

sql

-- Database Schema Export
-- Generated: 2025-04-05 10:30:00
-- Tables (2): users, orders

START TRANSACTION;

-- Drop existing tables
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `users`;

-- Create tables
CREATE TABLE `users` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `balance` DECIMAL(10,2) DEFAULT 0.00,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `orders` (
    `id` BIGINT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `total` DECIMAL(10,2) NOT NULL,
    `status` VARCHAR(20) DEFAULT 'pending',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create indexes
CREATE INDEX `idx_user_id` ON `orders` (`user_id`);
CREATE INDEX `idx_status` ON `orders` (`status`);

-- Add foreign key constraints
ALTER TABLE `orders` ADD CONSTRAINT `fk_orders_user_id`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON UPDATE CASCADE ON DELETE CASCADE;

COMMIT;

JSON/Array Export

Output Format

json

{
  "version": "1.0",
  "generated": "2025-04-05 10:30:00",
  "tables": {
    "users": {
      "columns": [
        {
          "name": "id",
          "type": "int",
          "nullable": false,
          "primary": true,
          "auto_increment": true,
          "phpType": "int"
        }
      ],
      "indexes": [...],
      "foreignKeys": [...]
    }
  }
}

The array export has the same structure, ideal as input for the Jardis Builder pipeline:

php

$schema = $exporter->toArray(['users', 'orders']);

// Pass directly to the builder
$builderConfig = DatabaseSchema::fromArray($schema);

Dependency Sorting

Foreign keys define dependencies between tables. The DependencyResolver sorts tables topologically (Kahn's algorithm) so that referenced tables are always created before referencing ones:

php

// order_items → orders → users
// order_items → products

// Result: users, products → orders → order_items

Circular dependencies throw a RuntimeException.

Architecture

Two orchestrators with driver-specific handlers:

DbSchemaReader                         ← Orchestrator (driver detection)
├── MySqlReader                        ← information_schema queries
├── PostgresReader                     ← information_schema + pg_catalog
└── SqLiteReader                       ← PRAGMA statements

DbSchemaExporter                       ← Orchestrator (format routing)
├── SqlDdlExporter                     ← DDL script generation
│   └── DependencyResolver             ← Topological sorting
├── MySqlDialect                       ← MySQL DDL syntax
├── PostgresDialect                    ← PostgreSQL DDL syntax
├── SqLiteDialect                      ← SQLite DDL syntax
└── JsonExporter                       ← JSON/Array export

Directory Structure

src/
├── DbSchemaReader.php              ← Reader orchestrator
├── DbSchemaExporter.php            ← Exporter orchestrator
├── Reader/
│   ├── MySqlReader.php
│   ├── PostgresReader.php
│   └── SqLiteReader.php
└── Exporter/
    ├── Ddl/
    │   ├── DdlDialectInterface.php
    │   ├── DependencyResolver.php
    │   └── SqlDdlExporter.php
    ├── Dialect/
    │   ├── MySqlDialect.php
    │   ├── PostgresDialect.php
    │   └── SqLiteDialect.php
    └── Json/
        └── JsonExporter.php

API Reference

DbSchemaReader

Method	Signature	Description
`tables`	`tables(): ?array`	All tables
`columns`	`columns(string $container, ?array $fields = null): ?array`	Column metadata
`indexes`	`indexes(string $table): ?array`	Index metadata
`foreignKeys`	`foreignKeys(string $table): ?array`	Foreign key metadata
`fieldType`	`fieldType(string $dbType): ?string`	DB type → PHP type
`getDriverName`	`getDriverName(): string`	Driver name

DbSchemaExporter

Method	Signature	Description
`toSql`	`toSql(array $tables): string`	DDL script
`toJson`	`toJson(array $tables, bool $prettyPrint = false): string`	JSON export
`toArray`	`toArray(array $tables): array`	Array export

Complete Example

Schema analysis and export in all three formats:

php

use JardisTools\DbSchema\DbSchemaReader;
use JardisTools\DbSchema\DbSchemaExporter;

$pdo = new PDO('mysql:host=localhost;dbname=shop', 'user', 'pass');
$reader = new DbSchemaReader($pdo);
$exporter = new DbSchemaExporter($reader);

// List all tables in the database
$tables = $reader->tables();
$tableNames = array_column($tables, 'name');
// ['users', 'orders', 'order_items', 'products']

// Inspect the schema of a single table
$columns = $reader->columns('orders');
foreach ($columns as $col) {
    echo sprintf(
        "%s: %s%s%s\n",
        $col['name'],
        $col['type'],
        $col['nullable'] ? ' NULL' : ' NOT NULL',
        $col['primary'] ? ' PK' : '',
    );
}

// Analyze foreign keys
$fks = $reader->foreignKeys('orders');
foreach ($fks as $fk) {
    echo sprintf(
        "%s.%s → %s.%s (%s/%s)\n",
        $fk['container'], $fk['constraintCol'],
        $fk['refContainer'], $fk['refColumn'],
        $fk['onUpdate'], $fk['onDelete'],
    );
}

// Export complete DDL script
$ddl = $exporter->toSql($tableNames);
file_put_contents('schema.sql', $ddl);

// JSON for the Builder pipeline
$json = $exporter->toJson($tableNames, prettyPrint: true);
file_put_contents('schema.json', $json);

// Array for programmatic processing
$schema = $exporter->toArray($tableNames);
$builderConfig = DatabaseSchema::fromArray($schema);

Core

Adapter

Support

Tools

DevOps

Database Schema

Introduction

Installation

Basic Usage

Reading a Schema

Exporting a Schema

Schema Data

Column Metadata

Filtered Column Selection

Type Mapping (DB → PHP)

Index Metadata

Foreign Key Metadata

DDL Export

Script Structure

Dialect Differences

Example: Generated MySQL DDL

JSON/Array Export

Output Format

Dependency Sorting

Architecture

Directory Structure

API Reference

DbSchemaReader

DbSchemaExporter

Complete Example

Database Schema ​

Introduction ​

Installation ​

Basic Usage ​

Reading a Schema ​

Exporting a Schema ​

Schema Data ​

Column Metadata ​

Filtered Column Selection ​

Type Mapping (DB → PHP) ​

Index Metadata ​

Foreign Key Metadata ​

DDL Export ​

Script Structure ​

Dialect Differences ​

Example: Generated MySQL DDL ​

JSON/Array Export ​

Output Format ​

Dependency Sorting ​

Architecture ​

Directory Structure ​

API Reference ​

DbSchemaReader ​

DbSchemaExporter ​

Complete Example ​

Database Schema

Introduction

Installation

Basic Usage

Reading a Schema

Exporting a Schema

Schema Data

Column Metadata

Filtered Column Selection

Type Mapping (DB → PHP)

Index Metadata

Foreign Key Metadata

DDL Export

Script Structure

Dialect Differences

Example: Generated MySQL DDL

JSON/Array Export

Output Format

Dependency Sorting

Architecture

Directory Structure

API Reference

DbSchemaReader

DbSchemaExporter

Complete Example