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

2021-08-14: Obscure but dangerous encoding issue uncovered.

Have a look at bug #473 because I couldn't explain it better myself. Expect a fix in the next libpqxx release, 7.7.0. (More about that below.)

As I understand it, things go wrong when you change your connection's client encoding, and then insert data using stream_to, and the data has a string that contains non-ASCII characters.

Internally, stream_to encodes bytes with values greater than 127 using numerical escapes. But as Andres Grob discovered, those numerical escapes work in an unexpected and thus far undocumented way. The server will interpret these escapes not in the client encoding, but in the database encoding. By default these two encodings are the saame, but if your application explicitly changes its client encoding, there may be trouble.

What kind of trouble? The insertion may fail because the encoded bytes do not form valid characters in the database encoding. But in the worst case, you may get incorrect characters! The more "tolerant" your database encoding is, the greater this risk. A timely error is not pleasant, but it's generally better than corrupt data.

How big is your risk of data corruption? It all depends:

If your application never changes the client encoding, my understanding is that you'll have no problem — the server and client encodings will be identical, and so there will be no room for misunderstanding between them.

If either your database encoding or your client encoding is SQL_ASCII, the problem can't happen either. The encoding won't accept non-ASCII characters anyway.

If your database encoding is UNICODE, that's actually UTF-8. This is generally the most sensible setting, and I would guess it's the most common. UTF-8 is designed such that text in a different encoding is very unlikely to accidentally pass for valid UTF-8. So the risk there is minimal. If your code inserts non-ASCII characters more than just once or twice, the manifestation of the problem will almost certainly be an error, not corrupt data.

In other cases, there is a risk. If your application does not fall in any of the categories above, please have a careful look at non-ASCII characters in your database that may have been inserted using stream_to. Are they what you intended them to be?

The fix itself isn't too big a deal, but it does change the library's ABI in order to avoid an inefficiency. And so it deserves a version bump — from 7.6.0 to 7.7.0.

2021-07-28: libpqxx 7.6.0 released

It was about time for a new release. We saved up so many changes, I couldn't wait any longer.

Bad news first: I just removed the ability to convert a string to std::basic_string_view<std::byte>. I'm sorry: this conversion should never have existed and it's not safe to use. It's not safe because it leaves no object to "own" the converted data. The view points into deallocated memory. If this breaks your code, use std::basic_string<std::byte> instead — so string, not string_view.

Now for the good news, of which there is a lot:

Finally, "result slicing" is now deprecated. It will go away in the future. Was anyone using this? It never felt complete or useful to me, and I haven't heard anyone mention it in at least a decade. Once we get rid of it, that'll shave a tiny bit of complexity out of your inner loops and make them a little more efficient. The main reason though is simplicity. Simpler code means fewer mistakes and surprises.

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, 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: the transaction's exec method receives all result data from the server and then returns a result object. It acts like a standard C++ container of rows, and the rows in turn are standard C++ containers of fields. You can keep a result object around for as long as you like, even after you close the transaction and even after you close the connection.

Alternatively, you can stream your query, iterating over its result rows. This is generally faster, but of course your processing can be interrupted if you lose your network connection while reading data.

Either way, don't do any if statements to check for errors. 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[])
{
  pqxx::connection c{"postgresql://accounting@localhost/company"};
  pqxx::work txn{c};

  // Normally we'd query the DB using txn.exec().  But for querying just one
  // single value, we can use txn.query_value() as a shorthand.
  //
  // 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 query and check
  // that it produces an empty result.  If the result does contain data, it
  // 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 string
  // 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};

  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.
  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.


GTF Contributor