wiki:WikiStart

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. It is the standard C++ language binding for the postgres RDBMS.

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

News

2020-01-30: Releasing 7.0.0

Today we're releasing libpqxx 7.0. It's a radical change, and you may find that your software needs changes to compile.

For starters, C++17 is the minimum now. You'll find std::string_view everywhere, and I hope that you'll find libpqxx faster and more modern than before. If your compiler doesn't implement C++17 fully, that's probably okay — but you may have to pass an option such as -std=c++17.

If you implemented your own string_traits to support additional string conversions, you'll need major changes. The new system is faster, friendlier in some ways, but also a bit more complicated. There's a separate null_traits template, and conversions to and from char buffers.

Connections lost many of their advanced features. It's now a single, much lighter class. No inheritance hierarchy, no automatic connection reactivation. If you want to reconnect, create a new connection. Prepared statements and session variables are no longer cached, so when you define or undefine them, that goes straight to the server.

Invoking prepared or parameterised statements is now easier, thanks to template parameter packs.

The tablereader and tablewriter classes finally have worthy successors: stream_from and stream_to.

Transactors are much simpler: you create your transaction (and even your connection if you like), you commit it when done. A simple lambda can now be a complete transactor!

Exception classes have been rearranged. There is no longer a pqxx_exception base class, either.

Lots of enums have been moved out of classes, some have become enum classes.

Large objects now support 64-bit object sizes. Not for reads and writes though — writing multi-gigabyte chunks at a time is probably a bad idea.

Custom build files for Visual Studio or MinGW are gone. On systems that don't support configure, use CMake.

The robusttransaction class no longer needs to create its own working table. It does require PostgreSQL 10 or better.

And that's just the major points. A lot more has gone into this release, over the better part of a year. We've had many wonderful contributions, and I'm sure there will be lots more that we can continue to improve. Enjoy!

Finding Everything

Where What
Online Documentation Documentation hosted on Read The Docs
Github page Get source, report bugs, submit patches, request changes
Author and Contributors Who made all this?

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. This transaction type provides basic non-transactional behaviour. (This is sometimes called "autocommit": it commits every command right away).

Every command or query returns a result. Your query fetches its result data immediately when you execute it, and stores it in the result. Don't check your result for errors; failures show up as regular C++ exceptions.

Keep result objects around for as long as you need them, completely separate from the connections and transactions that originated them. You can access the rows in a result using standard iterators, or more like an array using numerical indexes. Inside each row you can access the fields by standard iterators, numerical indexes, or using column names.

Brief example

Can't have a database example without an Employee table. Here's a simple program: 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);

  // Query the DB using txn.exec().
  // Here, we expect exactly one result row, so we use
  // exec1() instead.  It checks that we get exactly one
  // row (throwing an exception otherwise), and returns that
  // row, instead of a full result set.
  // As of libpqxx 7.x, you'd just use query_value<int>() for
  // this simple case.
  pqxx::row r = txn.exec1(
    "SELECT id "
    "FROM Employee "
    "WHERE name =" + txn.quote(argv[1]));

  // We can address the first field in the row as r[0].
  // The column's name would also work, but be less
  // efficient: r["id"].
  // The field's as<type>() method converts the field's
  // string value to a type of our choice.
  int employee_id = r[0].as<int>();

  std::cout << "Updating employee #" << employee_id << std::endl;

  // Update the employee's salary.
  // Here we use exec0() to say that we expect an empty
  // result.  If the result does contain data, it will
  // throw an exception.
  txn.exec0(
    "UPDATE EMPLOYEE "
    "SET salary = salary + 1 "
    "WHERE id = " + txn.quote(employee_id));

  // Make our change definite.
  txn.commit();
}

For some more examples, see:

Also, see the API reference for details about what everything does.

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, 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. 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: front()/back(), size(), index operator, and so on. You can't modify the result's contents, however.


Ohloh Metrics GTF Contributor
Last modified 8 weeks ago Last modified on Feb 5, 2020, 7:35:32 PM