Like many developers, I've spent my fair share of time dealing with database schema changes across different environments. Keeping development, staging, and production in sync can feel like herding cats. Traditional migration tools often use tracking tables to remember what's been run, which works great, but I found myself wanting something a bit different for certain workflows.
A while back, I built a small Node.js tool called agogo based on a specific idea: what if the tool didn't track state? What if, instead, it focused entirely on ensuring the SQL scripts themselves were idempotent – safe to run again and again, always resulting in the same outcome?
Recently, I decided to revisit this concept and rebuild agogo from the ground up in Rust. Why the rewrite? Partly for the potential performance and safety benefits Rust offers, but mostly because it felt like a great learning opportunity and a chance to rethink the original design.
The Philosophy: Ditching the Tracking Table
The core idea behind Agogo is statelessness. Instead of querying a schema_migrations table, it relies on analyzing the SQL scripts themselves. It asks: "Does this CREATE TABLE have IF NOT EXISTS?" "Does this INSERT use IGNORE or ON DUPLICATE KEY UPDATE?"
The goal is a more declarative approach. You define the desired end state in your collection of SQL scripts, and Agogo validates that these scripts look like they can be safely run repeatedly to achieve that state. If a script seems potentially non-idempotent (like a plain INSERT or a tricky ALTER TABLE operation), the tool flags it during validation before it even touches the database during a run.
This feels particularly useful in CI/CD pipelines or for spinning up fresh development environments where you just want the database to match the current state defined in your repo, without worrying about migration history.
The Build: Rust, SQL Parsers, and Idempotency Hurdles
Rebuilding this in Rust was... an experience!
Rust's Charm: The compiler acting as a strict partner-in-crime really does help prevent a whole class of errors. Traits, ownership, and the ecosystem around libraries like serde (for config parsing), clap (for CLIs), and sqlx (for database interaction) felt robust and well-designed.
Parsing Challenges: Understanding the structure of SQL accurately enough for validation turned out to be non-trivial, especially with MySQL's dialect variations. More on this tricky part below!
Idempotency Isn't Always Obvious: While checking for IF NOT EXISTS is straightforward, validating more complex statements like ALTER TABLE purely through syntax is tricky. Can the tool really know if modifying a column is safe to re-run? Often, no. So, Agogo takes a conservative approach: it validates simple, known-good patterns and flags (or outright rejects during validation) anything ambiguous or complex by default. It can even auto-fix some simple DDL (CREATE TABLE -> CREATE TABLE IF NOT EXISTS), which is neat.
Configuration is Key: Making the tool flexible meant leaning heavily on configuration files (YAML) to define database connections per environment (local, dev, prod) and the specific order scripts should run in (tables first, then triggers, then data).
What Using It Feels Like
The end result is a command-line tool where the workflow looks something like this:
You point Agogo at your project directory, tell it which database connection and environment you're targeting, and issue a command:
# Conceptual CLI Usage
agogo --project ./my-app --db main_db --env dev <COMMAND>
Where <COMMAND> is typically one of:
validate: "Check my scripts for idempotency rules, but don't touch the database."
lint: Similar to validate, but can also flag things it could auto-fix. Add --fix and it'll offer to modify your SQL files (use with git!).
run: "Validate my scripts, connect to the DB, make sure the database itself exists, and then execute the scripts in the configured order."
It feels less like applying incremental patches and more like saying, "Make the database look like this set of definitions."
A Deeper Dive: The SQL Parsing Tightrope
Okay, let's circle back to that SQL parsing challenge I mentioned earlier. This was arguably the trickiest part of the build. To validate SQL for idempotency, you first need the tool to understand its structure accurately. I started with the excellent sqlparser-rs library, which handles a wide range of standard SQL wonderfully.
The snag? SQL dialects are notoriously varied, and MySQL has its own unique syntax flavors, especially for more complex DDL like ALTER TABLE or stored procedures (CREATE FUNCTION/PROCEDURE). I quickly hit specific spots where sqlparser-rs, aiming for broad compatibility, couldn't quite decipher certain MySQL-specific clauses or variations that were crucial for Agogo's validation logic. For example, knowing precisely if an ALTER TABLE ... ADD COLUMN statement included the MySQL 8+ IF NOT EXISTS clause was vital. If the parser couldn't reliably extract that detail, Agogo couldn't validate the statement correctly.
Bridging the Gaps with Nom: This is where things got interesting. Reinventing a full MySQL parser wasn't feasible, so I turned to nom. Nom is a "parser combinator" library – like building specific parsers from small, reusable Lego bricks. It's incredibly powerful for zeroing in on particular syntax patterns, though it takes more setup than a general parser.
So, the solution became a hybrid approach:
First, try parsing the whole script file with sqlparser-rs.
If it works, great! However, if the statement being processed is one of the known tricky types (like certain ALTERs or CREATE FUNCTIONs), take the raw SQL string for just that statement and pass it to a custom, highly specific mini-parser built with nom. This nom parser only needs to understand that one statement type well enough to extract the specific flags (IF NOT EXISTS, etc.) that sqlparser-rs might have missed.
If sqlparser-rs fails to parse the file initially (maybe due to very complex procedures), try the nom parser as a fallback, though it can only handle the limited statement types it was explicitly built for.
This strategy lets sqlparser-rs handle the broad strokes, while nom provides targeted precision for the known MySQL-specific blind spots. It adds internal complexity, for sure, but it was a pragmatic way to significantly improve validation accuracy for key idempotency patterns without the Herculean effort of writing a full dialect parser. It's still not perfect – esoteric SQL syntax can always throw a wrench in the works – but it covers the common ground much more reliably.
Reflections
Building Agogo in Rust was rewarding. It forced me to think deeply about SQL idempotency patterns and the trade-offs of stateless versus stateful schema management. Rust's ecosystem is impressive, though tackling complex parsing tasks remains challenging (as detailed next!). The resulting tool feels focused – it does one thing (stateless, validated runs) and tries to do it reasonably well, with clear limitations.
Is it the right tool for every job? Definitely not. If you need complex transactional migrations, fine-grained rollbacks, or absolute certainty provided by a tracking table, traditional tools are likely a better fit. But for projects where a declarative, idempotent script set is maintainable, it offers a potentially simpler alternative.
What's Next?
There's always more to do! Refining the auto-fixing logic, improving parser error handling (especially communicating why something failed parsing), and perhaps adding better ways to handle secrets like database passwords are on the list. For now, though, it's a functioning tool that scratches a particular itch I had.
It's been a fun journey from Node.js to Rust, wrestling with SQL's quirks along the way!