The C++ connector for PostgreSQL
libpqxx
is the official C++ client API for
PostgreSQL, the enterprise-strength
open-source relational database. (If "PostgreSQL" is too verbose, call it by
its shorter name, postgres).
If you are writing software in C++ that needs to access databases managed by postgres —on just about any platform— then libpqxx is the library you use.
The source code for libpqxx is available under the BSD license, so you're free to download it, pass it on to others, change it, sell it, include it in your own code, and share your changes with anyone you choose. No charge, no catch. Also, no guarantees. :-)
Finding Everything
Quick links:
-
Documentation is at ReadTheDocs.
-
Code, bug tracker, and releases are on Github.
-
Stats and insights are in OpenHub
News
2024-02-18: libpqxx 7.9.0 released: important fixes and new features
This is a pretty big release. As things stand, this looks to be the last release which supports C++17. The plan is to move on to libpqxx 8.0 next, which will require C++20 as a baseline.
Here's what's changed.
Assertion failure while streaming data
There was a bug that triggered an assertion failure when a row in a streaming query result ended in a null string.
The assertions in the code were just a little too strict. It amazes me that this took to long to surface. It has now been fixed.
New type aliases for binary data
We've been using std::basic_string<std::byte>
and
std::basic_string_view<std::byte>
to represent binary data. But
@tambry noted that compilers aren't actually
required to support this! Worse, libc++18 had already marked it as deprecated.
The authors seem to have changed their minds about that, but Raul contributed a fix anyway. After all the problem may well pop up again.
And so, from libpqxx 7.9.0 onwards, to represent binary data coming from or
going into the database, use the new type aliases pqxx::bytes
(for a
std::string
of std::byte
) and pqxx::bytes_view
(for a std::string_view
of std::byte
).
If your environment supports the old types, these are just aliases for those,
and nothing changes. But if it doesn't, then the aliases will refer to slightly
different types that work around the problem. (The alternative definitions use
a custom char_traits
. The fine print in the C++ standard said that you need
this, and that the library is not obligated to offer a generic definition of
these traits for std::byte
.)
Consistent library naming in CMake
Building CMake projects using libpqxx became a little easier. Thanks to
@alexv-ds, you can now just use
shared_link_libraries([...] libpqxx::pqxx)
.
The library name used to vary depending on whether you use find_package()
or
add_subdirectory()
to add libpqxx to your project. Now it's just always
libpqxx::pqxx
.
Exception-related link errors
If you had a libpqxx built in C++17, and linked it into a project built using
C++20, or vice versa, you'd get a lot of link errors about missing classes.
They were related to exceptions, such as std::runtime_error
.
Linking code built in one C++ version to code built in another is categorically dangerous. Please don't do it. There is no guarantee that it will work. Sadly though all package managers deal with this issue by sticking their heads in the sand.
It turns out that in practice the linking often worked, and various pre-built
packaged versions of libpqxx shipped just one binary for all C++ versions. If
you built your project in a different C++ version than was used to build
libpqxx, two recent changes conspired to break your build:
1. For C++20 and up, I added source_location
information to exceptions. If
your compiler didn't support that, you just didn't get it — but it affected
how an exception object was laid out in memory.
2. Instead of generating a header listing which C++ features were available when
you built libpqxx, I just detected features at compile time. But that breaks
down when the language changes between inbetween the libpqxx build and your
own project's build!
The new release works around this using all-new code to generate a configuration header at build configuration time. The enw code is much more regular, and easier for me to extend and maintain. This should make it easier to add support for most new C++ features in the future. I also believe the build became just slightly faster.
Conversion from std::string_view
to SQL string
Converting a std::string_view
to an SQL string never actually worked. It
wasn't a priority in part because pqxx::zview
is likely to be much faster.
Still, this was an annoying irregularity and it has been fixed. You can now
pass a std::vector<std::string_view>
to a prepared statement, for example.
Expect future libpqxx versions to be a bit more liberal in allowing conversions of various view types. Which does mean that... * sometimes the conversion may do a bit of extra work under the bonnet and it will be up to you to avoid this when performance is critical; and * it will be your responsibility to ensure that the data underlying a view or span is still valid whenever you make use of its SQL conversion.
Parameterised versions of query()
etc.
It has long bothered me that libpqxx has separate functions for executing a query, and for executing a query with parameters.
There are good reasons why you can't just pass any additional arguments to these
functions and have them all converted into SQL parameters. It makes it easier
to write code that doesn't mean what you might expect. It also complicates
overloading, especially in a future where every query execution function also
takes an implicit std::source_location
to improve error reporting.
As of libpqxx 7.9.0 however you can now pass a pqxx::params
when executing a
query, and it will be unambiguously clear that it should be interpreted as a
bundle of SQL parameters.
Streaming queries and C++20 pipelines
A streaming query can now act as a std::input_iterator
. This removes an
obstacle to building C++20 statement pipelines using streaming queries.
Clearer documentation for defining your own data types
The updated documentation makes it a bit easier to see how to go about defining SQL conversions for your own data types, so you can convert them between their SQL and C++ representations.
These conversions are particularly important when you want to pass them to parameterised or prepared statements.
Support for std::span
as SQL arrays
This is still somewhat experimental, but libpqxx 8.0 will rely a lot more on
std::span
.
Thanks to @alexolog and
@fallenworld1 you should now be able to pass
any std::span
(over a supported type of course) as a parameter to a prepared
or parameterised statement, and it will automatically convert into an SQL array.
Support for PQfsize()
and PQfmod()
You can now query a column's storage size and type modifier. This code was contributed by @TeamPlatform1.
These are only useful for low-level coders. Touch only if you know what you're doing.
Thanks
As you can see, various code changes have been contributed directly by libpqxx users. Others were requested in bug tickets. It would be a bit redundant for me to name them all here, but I am grateful: after all a good bug report is not so much a "customer complaint" as it is real-world feedback on what can be improved.
Further thanks go out to everyone who contributed, and not to forget — the tireless @tt4g and @KayEss who have stepped in to help time and again when people ran into problems.
Technical Overview
This library works on top of the C-level API library, libpq
. It comes with
postgres. You will link your program with both libpqxx
and libpq
in that
order.
Coding with libpqxx revolves around transactions. Transactions are a central concept in database management systems, but they are widely under-appreciated among application developers. In libpqxx, they're fundamental.
With conventional database APIs, you issue commands and queries to a database session or connection, and optionally create the occasional transaction. In libpqxx you start with a connection, but you do all your SQL work in transactions that you open in your connection. You commit each transaction when it's complete. If you don't, all changes made inside the transaction get rolled back.
There are several types of transactions with various "quality of service" properties. If you really don't want a transaction on the database, one of the available transaction types is called nontransaction. It provides basic non-transactional behaviour. (This is sometimes called "autocommit": it commits every successful command right away).
You can execute queries in different ways, each by calling methods on the transaction: * "query" methods execute a query, wait for the full result data to come back, and provide you with each row of data converted to field types of your choice. * "stream" methods execute a query, taking a bit more time to start up, but the data then comes in at a higher rate, and you can start processing each row right as it comes in. Just like the "query" methods, they convert the data to field types of your choice. * "exec" methods execute a query, wait for the full result to come in, and then give you a result object. It's a container of rows (and each row is a container of fields), but it also contains some metadata about the result set. In particular, use "exec0" when you execute a command that should not return any data rows at all.
Either way, don't do any if
statements to check for errors when you execute an
SQL command. If something
goes wrong, you will get an exception.
Quick example
Can't have a database example without an Employee table. Here's a simple application: find an employee by name, and raise their salary by 1 whatever-it-is-they-get-paid-in.
This example is so simple that anything that goes wrong crashes the program. You won't need to do a lot more to fix that, but we'll get to it later.
#include <iostream>
#include <pqxx/pqxx>
int main(int, char *argv[])
{
// (Normally you'd check for valid command-line arguments.)
pqxx::connection c{"postgresql://accounting@localhost/company"};
pqxx::work txn{c};
// For querying just one single value, the transaction has a shorthand method
// query_value().
//
// Use txn.quote() to escape and quote a C++ string for use as an SQL string
// in a query's text.
int employee_id = txn.query_value<int>(
"SELECT id "
"FROM Employee "
"WHERE name =" + txn.quote(argv[1]));
std::cout << "Updating employee #" << employee_id << '\n';
// Update the employee's salary. Use exec0() to perform a command and check
// that it produces no result rows. If the result does contain data, this
// will throw an exception.
//
// The ID is an integer, so we don't need to escape and quote it when using
// it in our query text. Just convert it to its PostgreSQL textual
// representation using to_string().
txn.exec0(
"UPDATE EMPLOYEE "
"SET salary = salary + 1 "
"WHERE id = " + pqxx::to_string(employee_id));
// Make our change definite.
txn.commit();
}
You'll find more detailed explanations and reference-style docs on the ReadTheDocs site.
Building your libpqxx program
The details depend on your system and compiler. On a typical Unix-like system, you might do:
c++ add_employee.cxx -lpqxx -lpq
Remember to keep the -lpqxx
and -lpq
in that order! Otherwise the linker
will complain bitterly about missing functions like PQconnectdb
and PQexec
.
If libpqxx is installed in a nonstandard location, such as /usr/local
, you
may need to add options like -I/usr/local/include
(to make the compiler find
headers pqxx/*
in /usr/local/include/pqxx
), and/or -L/usr/local/lib
(to
make the linker find the library in /usr/local/lib
).
This should work on most GNU/Linux systems (Mint, Debian, Fedora, Gentoo,
Red Hat, Slax, Ubuntu, etc.), BSD systems (FreeBSD, NetBSD, OpenBSD), vaguely
Unix-like systems such as Apple's macOS, and so on — as long as you have
libpqxx, libpq, and a C++ compiler installed. If your C++ compiler has a
different name on the command line, use that instead of "c++
".
It works differently on Microsoft Windows, though there are development environments out there that behave more like a Unix system.
Handling errors
Errors are exceptions, and derived from std::exception
, just like you'd
expect. So you can handle database errors like all others:
#include <iostream>
#include "my-db-code.hxx"
int main(int argc, char *argv[])
{
try
{
do_db_work(trans);
}
catch (std::exception const &e)
{
std::cerr << e.what() << std::endl;
return 1;
}
}
Of course libpqxx also defines its own exception hierarchy for errors it throws, so you can handle those specially if you like:
#include <iostream>
#include <pqxx/except>
#include "my-db-code.hxx"
int main(int argc, char *argv[])
{
try
{
do_db_work(trans);
}
catch (pqxx::sql_error const &e)
{
std::cerr
<< "Database error: " << e.what() << std::endl
<< "Query was: " << e.query() << std::endl;
return 2;
}
catch (std::exception const &e)
{
std::cerr << e.what() << std::endl;
return 1;
}
}
Just one caveat: not all platforms support throwing an exception in a shared library and catching it outside that shared library. Unless you're building for Windows, it's probably a good habit to use static libraries instead.
Complete example
Here's a more complete example, showing iteration and direct field access.
#include <iostream>
#include <pqxx/pqxx>
/// Query employees from database. Return result.
pqxx::result query()
{
pqxx::connection c{"postgresql://accounting@localhost/company"};
pqxx::work txn{c};
// Silly example: Add up all salaries. Normally you'd let the database do
// this for you.
long total = 0;
for (auto [salary] : txn.query("SELECT salary FROM Employee"))
total += salary;
std::cout << "Total salary: " << total << '\n';
// Execute and process some data.
pqxx::result r{txn.exec("SELECT name, salary FROM Employee")};
for (auto row: r)
std::cout
// Address column by name. Use c_str() to get C-style string.
<< row["name"].c_str()
<< " makes "
// Address column by zero-based index. Use as<int>() to parse as int.
<< row[1].as<int>()
<< "."
<< std::endl;
// Not really needed, since we made no changes, but good habit to be
// explicit about when the transaction is done.
txn.commit();
// Connection object goes out of scope here. It closes automatically.
// But the result object remains valid.
return r;
}
/// Query employees from database, print results.
int main(int, char *argv[])
{
try
{
pqxx::result r{query()};
// Results can be accessed and iterated again. Even after the connection
// has been closed.
for (auto row: r)
{
std::cout << "Row: ";
// Iterate over fields in a row.
for (auto field: row) std::cout << field.c_str() << " ";
std::cout << std::endl;
}
}
catch (pqxx::sql_error const &e)
{
std::cerr << "SQL error: " << e.what() << std::endl;
std::cerr << "Query was: " << e.query() << std::endl;
return 2;
}
catch (std::exception const &e)
{
std::cerr << "Error: " << e.what() << std::endl;
return 1;
}
}
Results and result rows have all the member functions you expect to find in a
container: begin()
/end()
, front()
/back()
, size()
, index operator, and
so on. The contents are immutable.