Sql Parser Laravel Package

Getting Started

Install via Composer: composer require iamcal/sql-parser. Instantly parse a MySQL CREATE TABLE statement by creating a new SQLParser instance and calling parse($sql). Access the structured schema via $parser->tables, which returns an associative array keyed by table name. Your first use case is likely inspecting column metadata — e.g., to validate foreign key constraints — by iterating over $parser->tables['users']['fields'] and checking type, null, unsigned, and length.

Implementation Patterns

• Schema Diffing in Migrations: Parse two SQL dump strings (e.g., schema.sql and staging.sql) into separate $parser1->tables and $parser2->tables, then compare fields, indexes, and props to auto-generate ALTER TABLE statements or flag unsafe changes. This is the library’s original use case (Flickr/Tiny Speck production safety checks).
• CI/CD Validation: In Laravel’s after:pull hooks or GitHub Actions, run an Artisan command that parses .sql schema files and enforces rules (e.g., “all timestamps must be nullable()”, “foreign key columns must be unsigned()”). Fail the build if violations occur.
• Dynamic Model Generation: Feed parsed schemas into a Blade or PHP template to generate Eloquent models or Doctrine entities. Access $field['unsigned'], $index['type'], and $props['ENGINE'] to tailor column mappings (e.g., map unsigned integer to BigInteger with unsigned() modifier).
• Lexer Power Tools: Use lex($sql) for lightweight token extraction — e.g., scan a SQL file for all CREATE INDEX statements by inspecting $parser->tokens for 'CREATE' → 'INDEX' sequences, then reconstruct full DDL using raw substrings.

Gotchas and Tips

• Silent truncation on bad syntax: Unterminated comments (/* without */) or strings (') cause parsing to stop silently — not ideal for validation. Set $parser->throw_on_bad_syntax = true; to catch these early with iamcal\SQLParserSyntaxException.
• Index ambiguity: Primary keys use 'type' => 'PRIMARY', unique keys 'UNIQUE', others 'KEY'. Columns are nested in cols as ['name' => 'user_id'], not strings — double-check array access.
• Props casing matters: Engine/charset appear as uppercase keys ('ENGINE', 'CHARSET'), but column attributes like 'unsigned' are lowercase. Normalize keys when comparing across environments.
• Unsupported fields silently ignored: YEAR(2), FLOAT(m, n), partitions, or UNION may result in incomplete field lists. Validate $parser->tables[$table]['fields'] exists and has expected count.
• Performance win in loops: Parsing a 100-table SQL file takes ~140ms — fast, but avoid re-parsing inside foreach. Parse once, cache in APCu/Redis, and reuse results across checks.