2024-08-18 — The query functions explosion

Not so long ago, the transaction_base class offered many functions for executing an SQL statement. You call exec() and pass your SQL statement as an argument. Or you call query() or for_query() or stream() to execute the same statement and iterate over its results, and there were a bunch more.

You could also execute parameterised statements, in similar ways: exec_params(). And then there's prepared statements: exec_prepared() et cetera. That's three different kinds of just about every query execution function.

On top of that, most of these functions also had variants which specify that you expect the query to return zero rows (exec0()), or exactly one row (exec1()), or some other number of rows (exec_n()).

See the problem? It all multiplies. Really there should also have been query_params1(), stream_prepared_n(), and so on. By my latest calculation there ought to be about 90 of these, assuming we don't add any new frills.

Honestly, it was just too much. I couldn't manage that much code effectively, and you probably don't have the time to read through it all to find just the function you need.

So as of 7.9.3 I've simplified the whole thing. Let me describe how. I'll assume that you have a pqxx::connection cx and a pqxx::transaction_base tx.

Row-count restrictions

First: I'm no longer going to write special query functions that return a result object but also check how many rows the result contains.

We already have a bunch of these, but they are now deprecated — exec0(), exec1(), exec_n() and most of their variants. They'll stay around for a few more years, but I'm going to remove them in libpqxx 9.

Instead, you make a separate call to check the row count. There are new member functions in result for this: * expect_rows() to throw an exception if the number of rows is not what you expected; * one_row() to check that there's exactly one row, and return that row; * no_rows() as a shorthand for expect_rows(0); and * expect_columns() as a new bonus.

So if you have this code:

tx.exec0("SELECT foo()");

you should start rewriting that

tx.exec("SELECT foo()").no_rows();

Code like this:

pqxx::row r = tx.exec1("SELECT x FROM y");

should become...

pqxx::row r = tx.exec("SELECT x FROM y").one_row();

And this:

pqxx::result r = tx.exec_n(12, "SELECT id, name FROM model");

becomes...

pqxx::result r = tx.exec("SELECT id, name FROM model");
r.expect_rows(12);

Yes, it's a bit more work, for which I apologise — but it lets a single new function do the work of an ever-growing legion of exec_n()-like functions in transaction_base.

Rewriting existing code (in libpqxx, client code, and tests) actually was surprisingly easy and flexible. I hope your experience will be similar.

I haven't come up with an equivalent yet for the execution functions that don't return a result. At some point I guess we'll want a solution for those, but it'll have to look very different. Give me time. And if you have them... your ideas. File a discussion on the Github page to bring up any helpful thoughts you may have. Thank you!

Prepared statements, parameterised statements, and... statements

Second: the functions for executing a preared statement, a parameterised statement, and a plain statement will now all have the same names. The difference will be clear from the arguments you pass.

To start with the obvious: inside libpqxx there's a big difference between executing a plain SQL statement and executing a parameterised statement, but from the outside... the difference is just whether you pass any parameters.

Here's how you execute a plain statement:

tx.exec("SELECT hello('world')");

And here's how you execute a parameterised statement:

tx.exec("SELECT hello($1)", pqxx::params{"world"});

The difference is clear enough, right? No need to spell that out in the function's name. In fact it already worked like this.

But now we get to the new part... here's how you prepare and then call a prepared statement hi in the new scheme:

cx.prepare("hi", "SELECT hello('world')");
tx.exec(pqxx::prepped{"hi"});

Wrapping the name of the prepared statement in a pqxx::prepped makes its nature clear: it's not SQL, it's the name of a prepared statement. Here once again we don't actually need to spell it out in the function's name.

And obviously, if you wanted to pass parameters to your prepared statement...

cx.prepare("hi", "SELECT hello($1)");
tx.exec(pqxx::prepped{"hi"}, pqxx::params{"world"});

Clear as day, right? You just call exec() and most of the details sort themselves out. There's 4 different ways of calling it, leading to 3 entirely different execution paths internally, but none of that is your concern. Just call exec().

(Similar for the other functions, of course. I hope to reach full feature parity for stream(), for_stream(), stream_like(), for_query(), and so on eventually.)

Here too, rewriting libpqxx's own code turned out to be a clean and easy job, and the result was prettier and more regular. We're cutting down on the number of functions in transaction_base, but the number of function names that you need to remember shrinks even more radically.