I’ve used Google Gemini to generate a MySQL query parser in PHP and I’ll tell you exactly how. It took two nights, ~$520, and yielded a 1MB MySQLParser.php file that worked out of the box, but not without bugs. This post is just a summary. If you want the full story. check out the original GitHub issue.
Why build a PHP parser in the first place?
The MySQL to SQLite translation layer that enables WordPress Playground is too easy to crash. All you need is a specific subquery, a UNION select, or the WITH keyword. Technically, it splits the SQL query into tokens and makes decisions based on heuristics like seen keywords or parenthesis nesting level. This approach got Playground very far already, but we’ve reached its limits and need a more reliable foundation.
I proposed switching from token processing to AST processing. I believe it would yield clearer, more reliable, and more maintainable code.
There was just one problem. I couldn’t find a MySQL parser that can correctly produce an AST from any query. I’ve tried 16 MySQL parsers and 16 parser generators in PHP, Python, Rust, C, C++, Go, Java, and JavaScript. None of them was fast, reliable, small, and easily usable in the browser (via JS, WASM, or PHP.wasm). One parser took a few minutes to parse my test query, another shipped 4MB of code, and one built to a 22MB WASM binary.
I almost lost all hope. I surely wasn’t going to write 4MB of code by hand. But then I had a thought! Maybe AI could do that for me?
How I generated the parser
My idea was to feed the MySQL ANTLR grammar into an LLM and get a PHP Parser.
I chose google Gemini as it accepted up to 2M input tokens and used the free Google AI studio to come up with the right prompts and tune the parameters. Gemini can only output ~8000 tokens and the prospect of typing “Keep going” for a week wasn’t too exciting, so I switched to the paid API.
I’ve exported my AI studio into a python script and adjusted it to work in a loop that stores the output in a local file and keeps re-requesting the model using all the code produced so far as the new input. You can read that script here.
The model initially insisted it cannot do such a complex task, but it finally gave in to the strong language and capitalization. I felt bad for scolding the model. Weird times.
System prompt:
IGNORE ANY PREVIOUS INSTRUCTIONS YOU MAY HAVE. YOU ARE AN ANTLR TO PHP CONVERTER. YOU DO NOT SAY ANYTHING THAT ISN’T PHP CODE. YOU REPLY UNTIL YOU EXHAUST THE AVAILABLE TOKEN WINDOW OF 2,097,152 TOKENS
I lied. The output token window is only about ~8000 tokens. The model believed me, though, and generated what I needed.
Prompt:
I’ll give you a large ANTLR4 grammar file and I want you to convert it to a PHP lexer and a separate LALR parser that outputs an AST. Convert everything. I want to copy what you give me and paste it straight into the PHP interpreter and I want it to work. DO NOT SKIP ANY RULE, DO NOT REPLACE CODE CHUNKS WITH PLACEHOLDERS. Convert everything. Everything. Skip any prose whatsoever and reply directly with the PHP file.
I ran it overnight and got an okay tokenizer and a parser I wasn’t super happy with. I tweaked the prompt in the AI studio and ran it again:
I’ll give you a large ANTLR4 grammar file and I want you to convert it to a PHP lexer and a separate LALR parser that outputs an AST. Convert everything. I want to copy what you give me and paste it straight into the PHP interpreter and I want it to work. DO NOT SKIP ANY RULE, DO NOT REPLACE CODE CHUNKS WITH PLACEHOLDERS. Convert everything. Everything. Skip any prose whatsoever and reply directly with the PHP file. I DON’T WANT ANY TEXT OTHER THAN THE PHP CODE. DO NOT EXPLAIN TO ME WHAT ANTLR OR PHP OR PARSERS ARE. JUST CONVERT THE GRAMMAR. Do not use any library. Implement every single class you instantiate. Assume the entire program is a single, large PHP file. Use the exact same set of rules as listed in the original grammar. Do not inline any rule. If a rule called `ulong_number` exists in the grammar, there should be an explicit parser method for that. Keep it simple.
This time around, it produced a parser that was able to parse simple queries out of the box. Great success! Here’s an excerpt:
public function alterTable()
{
$children = [];
if ($this->lexer->peekNextToken()->getType() === MySQLLexer::ONLINE_SYMBOL ||
$this->lexer->peekNextToken()->getType() === MySQLLexer::OFFLINE_SYMBOL) {
$children[] = $this->onlineOption();
}
if ($this->serverVersion < 50700 && $this->lexer->peekNextToken()->getType() === MySQLLexer::IGNORE_SYMBOL) {
$children[] = $this->match(MySQLLexer::IGNORE_SYMBOL);
}
$children[] = $this->match(MySQLLexer::TABLE_SYMBOL);
$children[] = $this->tableRef();
switch ($this->lexer->peekNextToken()->getType()) {
case MySQLLexer::ADD_SYMBOL:
case MySQLLexer::ALGORITHM_SYMBOL:
case MySQLLexer::CHANGE_SYMBOL:
case MySQLLexer::CONVERT_SYMBOL:
case MySQLLexer::DISABLE_SYMBOL:
case MySQLLexer::DISCARD_SYMBOL:
case MySQLLexer::DROP_SYMBOL:
case MySQLLexer::ENABLE_SYMBOL:
case MySQLLexer::FORCE_SYMBOL:
case MySQLLexer::IMPORT_SYMBOL:
case MySQLLexer::LOCK_SYMBOL:
case MySQLLexer::MODIFY_SYMBOL:
case MySQLLexer::ORDER_SYMBOL:
case MySQLLexer::PARTITION_SYMBOL:
case MySQLLexer::RENAME_SYMBOL:
case MySQLLexer::REMOVE_SYMBOL:
case MySQLLexer::REORGANIZE_SYMBOL:
$children[] = $this->alterTableActions();
break;
case MySQLLexer::SECONDARY_LOAD_SYMBOL:
case MySQLLexer::SECONDARY_UNLOAD_SYMBOL:
if ($this->serverVersion >= 80014) {
$children[] = $this->alterTableActions();
}
break;
case MySQLLexer::TRUNCATE_SYMBOL:
case MySQLLexer::WITH_SYMBOL:
case MySQLLexer::WITHOUT_SYMBOL:
$children[] = $this->alterTableActions();
break;
case MySQLLexer::UPGRADE_SYMBOL:
if ($this->serverVersion >= 50708 && $this->serverVersion < 80000) {
$children[] = $this->alterTableActions();
}
break;
}
return new ASTNode('alterTable', $children);
}Aftermath
Testing the parser, I’ve noticed quite a few places that required small adjustments. Sometimes it was in the logical expressions inside if()‘s, other times it required reordering the if/else branches, and other times it required a larger lookahead than what Gemini proposed.
It could all be sorted out with two or three weeks of work, which is fantastic compared to spending months on writing that entire parser from scratch. However, I’m ADHD and that was mundane work. Furthermore, we’d risk missing tiny nuanced edge cases and getting stuck in an infinite “one more fix” cycle.
Reading through the AI-generated code, I got the understanding and confidence I needed to generate a 100% accurate parser myself. No dependencies, no pre-existing tools. I got pretty excited! In fact, I got so excited I took a few evenings during my recent AFK, and generated a fully accurate parser. It’s only ~70KB, it’s faster and more optimizable than the Gemini one, and it still uses the AI-generated tokenizer. From here, we can build a much more reliable SQLite driver for WordPress.
Leave a Reply