README

A FakerPHP provider for generating test fixture data from SQL schemas. Parses CREATE TABLE statements and generates type-appropriate fake data.

Overview

SQL Fixture reads your database schema (CREATE TABLE) and automatically generates realistic fixture data with correct types, lengths, and constraints. It supports MySQL and SQLite with three usage modes:

• FixtureProvider - Generate fixtures from CREATE TABLE SQL strings
• DatabaseFixtureProvider - Generate fixtures from a live database connection (PDO)
• FileFixtureProvider - Generate fixtures from a directory of DDL files

All modes support object hydration via ReflectionHydrator, converting generated data directly into typed PHP objects.

Requirements

• PHP 8.1 or higher
• fakerphp/faker ^1.23
• phpmyadmin/sql-parser ^5.11

Installation

composer require --dev k-kinzal/sql-fixture

Usage

From SQL Strings

Use FixtureProvider when you have CREATE TABLE SQL available:

use Faker\Factory;
use SqlFixture\FixtureProvider;

$faker = Factory::create();
$faker->addProvider(new FixtureProvider($faker));

$fixture = $faker->fixture(
    'CREATE TABLE users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(100) NOT NULL,
        age INT UNSIGNED,
        status ENUM("active", "inactive"),
        balance DECIMAL(10, 2),
        created_at DATETIME
    )'
);
// Returns: ['name' => 'John Doe', 'email' => 'john@example.com', 'age' => 28, ...]
// Note: auto_increment columns (id) are skipped by default

From Database Connection

Use DatabaseFixtureProvider with a PDO connection to generate fixtures from your actual database schema:

use Faker\Factory;
use SqlFixture\DatabaseFixtureProvider;

$pdo = new PDO('mysql:host=localhost;dbname=myapp', 'user', 'password');
$faker = Factory::create();
$faker->addProvider(new DatabaseFixtureProvider($faker, $pdo));

// Generate fixture by table name
$fixture = $faker->fixture('users');
// Schema is fetched via SHOW CREATE TABLE and cached

From DDL Files

Use FileFixtureProvider with a directory containing .sql files:

use Faker\Factory;
use SqlFixture\FileFixtureProvider;

// Directory contains: users.sql, posts.sql, comments.sql
$faker = Factory::create();
$faker->addProvider(new FileFixtureProvider($faker, '/path/to/ddl/'));

$fixture = $faker->fixture('users');

// List available tables
$tables = $faker->getTableNames(); // ['users', 'posts', 'comments']

// Register additional schemas at runtime
$faker->registerSchema('CREATE TABLE tags (id INT PRIMARY KEY, name VARCHAR(50))');

Overriding Values

Pass specific values to override generated data:

$fixture = $faker->fixture(
    'CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))',
    ['id' => 1, 'name' => 'Alice']
);
// Returns: ['id' => 1, 'name' => 'Alice', 'email' => '<generated>']

Object Hydration

Generate typed PHP objects directly using ReflectionHydrator:

class User
{
    public function __construct(
        public readonly int $id,
        public readonly string $userName,
        public readonly string $email,
    ) {}
}

$user = $faker->fixture(
    'CREATE TABLE users (id INT PRIMARY KEY, user_name VARCHAR(255), email VARCHAR(255))',
    ['id' => 1],
    User::class
);
// Returns: User(id: 1, userName: 'generated_name', email: 'generated@example.com')

The hydrator handles:

• Constructor parameter matching (exact name or snake_case to camelCase conversion)
• Type casting (string to int/float/bool, JSON decode for arrays)
• Default values and nullable parameters
• Property-based hydration for classes without constructors

SQLite Support

use SqlFixture\FixtureProvider;
use SqlFixture\Platform\PlatformFactory;

// Specify dialect explicitly
$faker->addProvider(new FixtureProvider($faker, dialect: PlatformFactory::DRIVER_SQLITE));

$fixture = $faker->fixture(
    'CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        score REAL
    )'
);

// DatabaseFixtureProvider auto-detects dialect from PDO driver
$pdo = new PDO('sqlite::memory:');
$faker->addProvider(new DatabaseFixtureProvider($faker, $pdo));
// SQLite dialect is automatically detected

Supported Types

MySQL

SQLite

Column Handling

• AUTO_INCREMENT / AUTOINCREMENT columns are skipped (not generated)
• Generated columns (AS expressions) are skipped
• Nullable columns have a 10% chance of returning null/default
• UNSIGNED constraint is respected for numeric types
• Length/precision/scale are respected for string and decimal types
• ENUM/SET values are randomly selected from the declared options
• DEFAULT values are used when a nullable column generates null

Extensibility

Customize behavior by providing your own implementations:

use SqlFixture\FixtureProvider;
use SqlFixture\TypeMapper\TypeMapperInterface;
use SqlFixture\Schema\SchemaParserInterface;
use SqlFixture\Hydrator\HydratorInterface;

$provider = new FixtureProvider(
    $faker,
    typeMapper: $customTypeMapper,      // Custom type-to-value mapping
    hydrator: $customHydrator,          // Custom object hydration
    schemaParser: $customSchemaParser,  // Custom CREATE TABLE parsing
    dialect: 'mysql',
);

Development

# Run unit tests
composer test:unit

# Run all tests
composer test

# Run linter (PHP-CS-Fixer + PHPStan level max)
composer lint

# Run fuzz tests
composer fuzz:create-table
composer fuzz:insert-select

# Fix code style
composer format

License

MIT License. See LICENSE for details.