Comparing the performance of SQL queries
I wrote a little Rust program, sqlstopwatch, to compare the performance of different SQL queries. I was inspired by the article Benchmarking SQL published by the jOOQ project, where they write the test code in SQL. I wanted something similar, but with a few additional requirements:
- A command-line program with a simple TUI showing a progress bar.
- The program should support PostgreSQL, MySQL, and SQLite.
- It should read the SQL queries and test parameters from a configuration file specified on the command line.
- It should print the results as a table in the terminal or save them as a CSV file that I can use to generate a chart.
The TUI was easy to make with Ratatui. To interact with the database, I used the async database library sqlx, which provides a bundled SQLite library.
I considered using TOML for the config file, but the file
has to specify the database credentials. In testing environments, credentials
may have to be loaded from environment variables or from files on a mounted file
system, but TOML doesn’t support carrying out those sorts of operations.
Therefore, it’s better if the config file is a program in a scripting language.
My exercise in writing an interpreter for a scripting language
had given me a new appreciation for the Lua virtual
machine. So the config file is a Lua script, which I find more convenient to
write in Fennel, a Lisp dialect that compiles to
Lua. Thanks to the mlua library, it was very
easy to embed a Lua interpreter in the program, and to use
Serde to convert Lua tables into Rust structs.
Now I can get back to working on my SQL queries.