Client API for PostgreSQL in C++ {#mainpage}

Dmitigr Pgfe (PostGres FrontEnd, hereinafter referred to as Pgfe) - is a client API to PostgreSQL servers written in C++. The development is focused on easines and robustness of use. At the same time, everything possible is being done to ensure that the performance is at its best. Pgfe is a part of the Dmitigr Cefeika project, but also available as a standalone project here.

Upcoming release 2.0

ATTENTION, API breaking changes starting from commit 62ceba3!

I'm currently working on Pgfe 2.0. The current stage is early alpha. Nonetheless, I recommend to switch to the new API despite the fact that it's still a subject to change while work on release 2.0 is in progress. (Although I don't think the changes will be significant.) Efforts will be made to make the API of Pgfe 2.0 as stable as possible.

Documentation

The Doxygen-generated documentation is located here. There is overview class diagram.

Hello, World

#include <dmitigr/pgfe.hpp>
#include <cstdio>

namespace pgfe = dmitigr::pgfe;

int main() try {
  // Making the connection.
  pgfe::Connection conn{pgfe::Connection_options{pgfe::Communication_mode::net}
    .net_hostname("localhost").database("pgfe_test")
    .username("pgfe_test").password("pgfe_test")};

  // Connecting.
  conn.connect();

  // Using Pgfe's conversion function.
  using pgfe::to;

  // Executing query with positional parameters.
  conn.execute([](auto&& r)
  {
    std::printf("Number %i\n", to<int>(r.data()));
  }, "select generate_series($1::int, $2::int)", 1, 3);

  // Prepare and execute the statement with named parameters.
  conn.prepare_statement("select :begin b, :end e")->
    bind("begin", 0).bind("end", 1).execute([](auto&& r)
  {
    std::printf("Range [%i, %i]\n", to<int>(r["b"]), to<int>(r["e"]));
  });

  // Invoking the function.
  conn.invoke([](auto&& r)
  {
    std::printf("cos(%f) = %f\n", .5f, to<float>(r.data()));
  }, "cos", .5f);

  // Provoking the syntax error.
  conn.perform("provoke syntax error");
 } catch (const pgfe::c42_Syntax_error& e) {
  std::printf("Error %s is handled as expected.\n", e.error()->sqlstate());
 } catch (const std::exception& e) {
  std::printf("Oops: %s\n", e.what());
  return 1;
 }

Features

• fast (negligible overhead compared to libpq);
• can be used as header-only library;
• work with database connections (in both blocking and non-blocking IO manner);
• execute prepared statements (named parameters are supported);
• conveniently call functions and procedures;
• conveniently handle errors by either via exceptions or error codes;
• conveniently work with large objects;
• exception class and enum entry for each predefined SQLSTATE code;
• easily convert the data from the client side representation to the server side representation and vice versa (conversions of multidimensional PostgreSQL arrays to/from any combinations of STL containers are supported out of the box!);
• dynamically construct SQL queries;
• separate SQL and C++ code (e.g., by placing SQL code into a text file);
• simple and thread-safe connection pool.

Major features of the nearcome release

• support of COPY command;
• yet more powerful support of arrays of variable dimensions at runtime.

Usage

Please, see Cefeika Usage section for hints how to link the library to a project.

Tutorial

Logically, Pgfe library consists of the following parts:

• main (client/server communication);
• data types conversions;
• errors (exceptions and error codes);
• utilities.

Connecting to a server

Class dmitigr::pgfe::Connection is a central abstraction of the Pgfe library. By using methods of this class it's possible to:

• send requests to a server;
• receive responses from a server (see dmitigr::pgfe::Response);
• receive signals from a server (see dmitigr::pgfe::Signal);
• perform other operations that depend on a server data (such as dmitigr::pgfe::Connection::to_quoted_literal()).

To make an instance of the class dmitigr::pgfe::Connection, the instance of the class dmitigr::pgfe::Connection_options is required. A copy of this instance is always read-only accessible via dmitigr::pgfe::Connection::options().

Example 1. Creation of the connection with the customized options:

std::unique_ptr<dmitigr::pgfe::Connection> create_customized_connection()
{
  return pgfe::Connection_options::make(Communication_mode::net)->
    set_net_hostname("localhost")->
    set_database("db")->
    set_username("user")->
    set_password("password")->
    make_connection();
}

Example 2. Creation of the connection with the default options:

std::unique_ptr<dmitigr::pgfe::Connection> create_default_connection_1()
{
  const auto opts = pgfe::Connection_options::make();
  return pgfe::Connection::make(opts.get());
}

Example 3. Creation of the connection with the default options:

std::unique_ptr<dmitigr::pgfe::Connection> create_default_connection_2()
{
  return pgfe::Connection::make();
}

After creation of an object of type dmitigr::pgfe::Connection there are two ways to connect available:

1. synchronously by using dmitigr::pgfe::Connection::connect();
2. in non-blocking IO maner by using dmitigr::pgfe::Connection::connect_nio().

Executing commands

SQL commands can be executed through either of two ways:

1. by using "simple query" protocol (which implies parsing and executing a query by a server on each request) with dmitigr::pgfe::Connection::perform();
2. by using "extended query" protocol (which implies using of parameterizable prepared statements):
   • by explicitly preparing a statement with dmitigr::pgfe::Connection::prepare_statement() and executing it with dmitigr::pgfe::Prepared_statement::execute();
   • by implicitly preparing and executing an unnamed prepared statement with dmitigr::pgfe::Connection::execute().

Commands can be executed and processed in non-blocking IO maner, i.e. without need of waiting a server response(-s), and thus, without thread blocking. For this purpose the methods of the class dmitigr::pgfe::Connection with the suffix _nio shall be used, such as dmitigr::pgfe::Connection::perform_nio() or dmitigr::pgfe::Connection::prepare_statement_nio().

Prepared statements can be parameterized with either positional or named parameters. In order to use the named parameters, a SQL string must be preparsed by Pgfe. Preparsed SQL strings are represented by the class dmitigr::pgfe::Sql_string. Unparameterized prepared statements, or prepared statements parameterized by only positional parameters does not require to be preparsed, and thus, there is no need to create an instance of dmitigr::pgfe::Sql_string in such cases and std::string can be used instead when performance is critical.

To set a value of a prepared statement's parameter it should be converted to an object of the class dmitigr::pgfe::Data. For convenience, there is the templated method dmitigr::pgfe::Prepared_statement::set_parameter(std::size_t, T&&) which do such a conversion by using one of the specialization of the template structure dmitigr::pgfe::Conversions.

Example 1. Simple querying.

void simple_query(dmitigr::pgfe::Connection* const conn)
{
  conn->perform("SELECT generate_series(1, 3) AS num");
}

Example 2. Implicit execution of the unnamed prepared statement.

void implicit_prepare_and_execute(dmitigr::pgfe::Connection* const conn)
{
  conn->execute("SELECT generate_series($1::int, $2::int) AS num", 1, 3);
}

Example 3. Explicit execution of the named prepared statement with named parameters.

void explicit_prepare_and_execute(const std::string& name,
  dmitigr::pgfe::Connection* const conn)
{
  using dmitigr::pgfe::Sql_string;
  static const auto sql = Sql_string::make(
    "SELECT generate_series(:infinum::int, :supremum::int) AS num");
  auto ps = conn->prepare_statement(sql.get(), name);
  ps->set_parameter("infinum",  1);
  ps->set_parameter("supremum", 3);
  ps->execute();
}

Invoking functions and calling procedures

In order to invoke a function the methods dmitigr::pgfe::Connection::invoke() and dmitigr::pgfe::Connection::invoke_unexpanded() can be used. Procedures can be called by using the method dmitigr::pgfe::Connection::call(). All of these methods have the same signatures.

To illustrate the API the following function definition is used:

CREATE FUNCTION person_info(id integer, name text, age integer)
RETURNS text LANGUAGE SQL AS
$$
  SELECT format('id=%s name=%s age=%s', id, name, age);
$$;

Example 1. Using Positional Notation.

void foo(dmitigr::pgfe::Connection* const conn)
{
  conn->invoke("person_info", 1, "Dmitry", 36);
  // ...
}

Example 2. Using Named Notation.

void foo(dmitigr::pgfe::Connection* const conn)
{
  using dmitigr::pgfe::_;
  conn->invoke("person_info", _{"name", "Dmitry"}, _{"age", 36}, _{"id", 1});
  // ...
}

Example 3. Using Mixed Notation.

void foo(dmitigr::pgfe::Connection* const conn)
{
  using dmitigr::pgfe::_;
  conn->invoke("person_info", 1, _{"age", 36}, _{"name", "Dmitry"});
  // ...
}

Responses handling

Server responses are represented by the classes, inherited from dmitigr::pgfe::Response:

• responses that are server errors are represented by the class dmitigr::pgfe::Error. Each server error is identifiable by a SQLSTATE code. In Pgfe each such a code is represented by the member of the enum class dmitigr::pgfe::Server_errc, integrated in framework for reporting errors provided by the standard library in <system_error>. Therefore, working with SQLSTATE codes is as simple and safe as with std::error_code and enumerated types! For example:

void handle_error_example(dmitigr::pgfe::Connection* const conn)
{
  try {
    conn->perform("PROVOKE SYNTAX ERROR");
  } catch (const dmitigr::pgfe::Server_exception& e) {
    assert(e.error()->code() == dmitigr::pgfe::Server_errc::c42_syntax_error);
  }
}

• responses that are rows are represented by the class dmitigr::pgfe::Row. Objects of this class can be accessed by using dmitigr::pgfe::Connection::row() and/or dmitigr::pgfe::Connection::release_row(). However, it is best to use the method dmitigr::pgfe::Connection::for_each() for rows processing. Be aware, that before executing the subsequent operations, all of the rows must be processed!

• responses that are prepared statements are represented by the class dmitigr::pgfe::Prepared_statement. Prepared statements are accessible via the method dmitigr::pgfe::Connection::prepared_statement().

• responses that indicates success of operations are represented by the class dmitigr::pgfe::Completion. Such responses can be accessed by calling dmitigr::pgfe::Connection::completion() and/or dmitigr::pgfe::Connection::release_completion(). Alternatively, to process completion responses the method dmitigr::pgfe::Connection::complete() can be used.

To initiate non-blocking IO retrieving of the first response methods of the class dmitigr::pgfe::Connection with the suffix _nio should be used. Otherwise, Pgfe will wait for the first response and if that response is dmitigr::pgfe::Error, an object of type dmitigr::pgfe::Server_exception will be thrown as exception. This object provides access to the object of type dmitigr::pgfe::Error, which contains the error details.

Server responses can be retrieved:

• synchronously by using the methods such as dmitigr::pgfe::Connection::wait_response() and dmitigr::pgfe::Connection::wait_last_response();
• in non-blocking IO maner by using the methods such as dmitigr::pgfe::Connection::read_input(), dmitigr::pgfe::Connection::handle_input(), dmitigr::pgfe::Connection::socket_readiness().

Data type conversions

Pgfe ships with support of conversions for fundamental and standard C++ types. Conversions for special PostgreSQL types such as Date/Time Types aren't provided out of the box, since many implementations of these types are possible at the client side. Instead it's up to the user to decide what implementation to use. (If such conversions are needed at all.) For example, the template structure dmitigr::pgfe::Conversions can be easily specialized to perform conversions between PostgreSQL Date/Time Types and types from the Boost.Date_Time library.

The class dmitigr::pgfe::Data is designed to store:

• the values of prepared statements' parameters;
• the data retrieved from a PostgreSQL server.

The template structure dmitigr::pgfe::Conversions are used by:

• dmitigr::pgfe::Prepared_statement::set_parameter(std::size_t, T&&) to perfrom data conversions from objects or type T to objects of type dmitigr::pgfe::Data;
• dmitigr::pgfe::to() to perform data conversions from objects of type dmitigr::pgfe::Data to objects of the specified type T.

There is the partial specialization of the template structure dmitigr::pgfe::Conversions to perform conversions from/to PostgreSQL arrays (including multidimensional arrays!) representation to any combination of the STL containers! (At the moment, arrays conversions are only implemented for dmitigr::pgfe::Data_format::text format.) In general, any PostgreSQL array can be represented as Container<Optional<T>>, where:

• Container - is a template class of a container such as std::vector or std::list or std::deque;
• Optional - is a template class of an optional value holder such as std::optional or boost::optional. The special value like std::nullopt represents the SQL NULL;
• T - is the type of elements of the array. It can be Container<Optional<U>> to represent the multidimensional array.

In case when all of the array elements are non-NULL, it can be represented as the container with elements of type T rather than Optional<T>. But in case when the source array (which comes from the PostgreSQL server) contain at least one NULL element a runtime exception will be thrown. Summarizing:

• the types Container<Optional<T>>, Container<Optional<Container<Optional<T>>>>, ... can be used to represent N-dimensional arrays of T which can contain NULL values;

• the types Container<T>, Container<Container<T>>, ... can be used to represent N-dimensional arrays of T which cannot contain NULL values.

User-defined data conversions could be implemented by either:

• overloading the operators operator<< and operator>> for std::ostream and std::istream respectively;
• specializing the template structure dmitigr::pgfe::Conversions. (With this approach overheads of standard IO streams can be avoided.)

Signal handling

Server signals are represented by classes, inherited from dmitigr::pgfe::Signal:

• signals that are server notices are represented by the class dmitigr::pgfe::Notice;
• signals that are server notifications are represented by the class dmitigr::pgfe::Notification.

Signals can be handled:

• synchronously, by using the signal handlers (see dmitigr::pgfe::Connection::set_notice_handler(), dmitigr::pgfe::Connection::set_notification_handler());
• in non-blocking IO maner, by using the methods that provides access to the retrieved signals directly (see dmitigr::pgfe::Connection::notice(), dmitigr::pgfe::Connection::notification()).

Signal handlers, being set, called by dmitigr::pgfe::Connection::handle_signals(). The latter is called automatically while waiting a response. If no handler is set, corresponding signals will be collected in the internal storage and should be popped up by using dmitigr::pgfe::Connection::pop_notice() and/or dmitigr::pgfe::Connection::pop_notification().

Be aware, that if signals are not popped up from the internal storage it may cause memory exhaustion!

Dynamic SQL

The standard classes like std::string or std::ostringstream can be used to make SQL strings dynamically. However, in some cases it is more convenient to use the class dmitigr::pgfe::Sql_string for this purpose. Consider the following statement:

SELECT :expr::int, ':expr';

This SQL string has one named parameter expr and one string constant ':expr'. It's possible to replace the named parameters of the SQL string with another SQL string by using dmitigr::pgfe::Sql_string::replace_parameter(), for example:

auto sql = dmitigr::pgfe::Sql_string::make("SELECT :expr::int, ':expr'");
sql->replace_parameter("expr", "sin(:expr1::int), cos(:expr2::int)");

Now the statement has two named parameters, and looks like:

SELECT sin(:expr1::int), cos(:expr2::int), ':expr'

Note, that the quoted string :expr is not affected by the replacement operation!

Working with SQL code separately of C++ code

The idea of the approach is to store the SQL code in a separate place, such as a text file. Consider the following SQL input, which is consists of two SQL strings with an extra data specified by the dollar-quoted string constants in the related comments:

-- This is query 1
--
-- $id$plus-one$id$
SELECT :n::int + 1, ';'; -- note, the semicolons in quotes are allowed!

/* This is query 2
 *
 * $id$minus-one$id$
 */
SELECT :n::int - 1

These SQL strings can be easily accessed by using the dmitigr::pgfe::Sql_vector API, for example:

std::string read_file(const std::filesystem::path& path);

void foo()
{
  namespace pgfe = dmitigr::pgfe;
  const auto input = read_file("bunch.sql");
  auto bunch = pgfe::Sql_vector::make(input);
  auto* minus_one = bunch->sql_string("id", "minus-one"); // SELECT :n::int - 1
  auto*  plus_one = bunch->sql_string("id",  "plus-one"); // SELECT :n::int + 1, ';'
  // ...
}

Connection pool

Pgfe ships with a simple connection pool implemented in class dmitigr::pgfe::Connection_pool.

Example:

namespace pgfe = dmitigr::pgfe;

inline std::unique_ptr<pgfe::Connection_pool> pool;

// Defined somewhere.
std::unique_ptr<pgfe::Connection_options> connection_options();

int main()
{
  pool = pgfe::Connection_pool::make(2, connection_options().get());
  pool->connect(); // opening up 2 connections
  {
    auto conn1 = pool->connection(); // attempting to get the connection from pool
    conn1->perform("select 1");
    auto conn2 = pool->connection(); // again
    conn2->perform("select 2");
    auto conn3 = pool->connection();
    assert(!conn3); // false -- the pool is exhausted
  } // connections returned back to the pool here
  auto conn = pool->connection();
  assert(conn); // true
  pool->disconnect(); // done with the pool
}

Exceptions

Pgfe may throw:

• an instance of the type std::logic_error when:
  • API contract requirements are violated;
  • an assertion failure has occurred (it's possible only with the "debug" build of Pgfe);
• an instance of the types std::runtime_error or dmitigr::pgfe::Client_exception when some kind of runtime error occured on the client side;
• an instance of the type dmitigr::pgfe::Server_exception when some error occured on the server side and the methods like dmitigr::pgfe::Connection::wait_response_throw() is in use (which is the case when using dmitigr::pgfe::Connection::perform(), dmitigr::pgfe::Connection::execute() etc).

Thread safety

By default, if not explicitly documented, all functions and methods of Pgfe are not thread safe. Thus, in most cases, some of the synchronization mechanisms (like mutexes) must be used to work with the same object from several threads.

Dependencies

Pgfe is depends on the libpq library.

CMake options

The table below (one may need to use horizontal scrolling for full view) contains variables which can be passed to CMake for customization of the Pgfe library.

Copyright

Copyright (C) Dmitry Igrishin