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:

News

2026-02-22: Working on libpqxx 8.0

I've been working hard on a big 8.0 release, I just haven't told you about it!

There are some sweeping changes:

  • C++20 is now the minimum. No more C++17.
  • Features that have been marked as deprecated for 3 years or more are gone.
  • To pass binary data you can now use anything that looks like binary data: std::vector<std::byte>, std::span<unsigned char>, you name it. Just not a regular string, because that looks like text.
  • The string conversion API is changing. Your old conversions will still work, but please start updating them to the new API. We'll support the old one for a few years, but not forever.
  • You can now "convert" types like std::string_view to a string. Of course what you get back is also a std::string_view, so it's your problem to make sure that the underlying data won't change or go away while you're using it.
  • Exceptions have a std::source_location — ideally of the place where you called libpqxx that led to te exception.

In general there will be fewer raw pointers, fewer begin/end pointers, fewer function pointers, and fewer specific types that you need to use.

And I'm working on more. If there is time, I would like to rewrite the result and row iterator classes. It breaks compatibility so I can only do that during a major release. And I may throw in a marker type for SQL identifiers, though that can always go into a smaller release.

All this comes on top of the big 7.x changes to how you execute queries, how you check for expected numbers of rows or columns in a query result, and lambda-friendly versions of the notification mechanism and receiving warning messages. It will be a very different API. Much nicer, I hope!

And of course 8.0 will have many smaller changes as well. See the NEWS file for an up-to-date list. Development work is in a pull request on Github.

Oh, and by popular demand... You can now send me a reward if you like what I'm doing. I'm hoping it will help with the costs of developing libpqxx.

2024-08-18: Big changes — how to execute a query

I've overhauled the way you execute prepared statements, making them much more uniform with plain statements and parameterised statements.

At the same time, I've deprecated functions like exec0() and exec1() which check how many rows are in the query's result. There are now separate functions for that in result itself.

Read the full story.

2024-05-22: YouTube video

The May 2024 Amsterdam Open Source Data Infrastructure Meetup graciously invited me to speak about libpqxx.

This half-hour presentation covers a basic introdction to libpqxx, as well as how streaming queries got to be faster than in libpq.

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.

Either way, don't do any if statements to check for errors when you execute an SQL command. If something goes wrong, the function will throw 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 cx{"postgresql://accounting@localhost/company"};
  pqxx::work tx{cx};

  // For querying just one single value, the transaction has a shorthand method
  // query_value().
  //
  // Use tx.quote() to escape and quote a C++ string for use as an SQL string
  // in a query's text.
  int employee_id = tx.query_value<int>(
    "SELECT id "
    "FROM Employee "
    "WHERE name =" + tx.quote(argv[1]));

  std::cout << "Updating employee #" << employee_id << '\n';

  // Update the employee's salary.  Use exec() to perform the command, and
  // no_rows() to check that it produces no result rows.  If the result does
  // contain data, this will throw an exception.
  //
  // The employee ID shows up in the query as "$1"; that means we'll pass it as
  // a parameter.  Pass all parameters together in a single "params" object.
  tx.exec(
    "UPDATE EMPLOYEE "
    "SET salary = salary + 1 "
    "WHERE id = $1",
    pqxx::params{employee_id}
  ).no_rows();

  // Make our change definite.
  tx.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 cx{"postgresql://accounting@localhost/company"};
  pqxx::work tx{cx};

  // Silly example: Add up all salaries.  Normally you'd let the database do
  // this for you.
  long total = 0;
  for (auto [salary] : tx.query("SELECT salary FROM Employee"))
    total += salary;
  std::cout << "Total salary: " << total << '\n';

  // Execute and process some data.
  pqxx::result r{tx.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.
  tx.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.

If you'd like to reward me for the work on libpqxx, you can now do that on buymeacoffee.com. It will help me pay for the costs of developing this library. And wouldn't it be great to have some swag for those help improve the code, or answer questions other users have?

GTF Contributor