Dmitigr Pgfe  1.1beta
The modern C++ API to PostgreSQL
Introduction to the C++ client library for PostgreSQL

The PostgreSQL Frontend (Pgfe) is client C++ API to PostgreSQL servers. ATTENTION, this software is "beta" quality, and the API is a subject to change. Any feedback (especially results of testing) is highly appreciated! Together we can make Pgfe library really production-ready!

Please note, this tutorial can also be viewed at the official Pgfe documentation site. Because of the Pgfe documentation is generated by Doxygen, most of references to the classes and methods on the official documentation site are clickable, which makes the familiarization more convenient. Also there are overview class diagram of the API for better understanding.

Hello, World

#include <dmitigr/pgfe.hpp>
#include <iostream>
int main()
{
namespace pgfe = dmitigr::pgfe;
try {
const auto conn = pgfe::Connection_options::make()->
set(pgfe::Communication_mode::tcp)->
set_tcp_host_name("localhost")->
set_database("pgfe_test")->
set_username("pgfe_test")->
set_password("pgfe_test")->
make_connection();
conn->connect();
conn->execute("SELECT generate_series($1::int, $2::int) AS natural", 1, 3);
conn->for_each([](const auto* const row)
{
std::cout << pgfe::to<int>(row->data("natural")) << "\n";
});
std::cout << "The " << conn->completion()->operation_name() << " query is done.\n";
// As a sample of error handling let's provoke syntax error and handle it away.
try {
conn->perform("PROVOKE SYNTAX ERROR");
} catch (const pgfe::Server_exception& e) {
if (e.error()->code() == pgfe::Server_errc::c42_syntax_error)
std::cout << "Error " << e.error()->sqlstate() << " is handled as expected.\n";
else
throw;
}
} catch (const std::exception& e) {
std::cerr << "Oops: " << e.what() << "\n";
}
}

Features

Current API allows to work with:

Features of the near future

The urgent TODO-list includes support of:

Tutorial

Client programs that use Pgfe should include header file dmitigr/pgfe.hpp and must link with dmitigr_pgfe (or the debug build - dmitigr_pgfed) library. Logically Pgfe library consists of the following parts:

WARNING Headers other than dmitigr/pgfe.hpp should be avoided to use in applications since that headers are subject to reorganize. Also, namespaces dmitigr::pgfe::detail and dmitigr::pgfe::internal contains implementation details and an internal stuff and should not be used in applications.

Connecting to a server

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

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 customized options:

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

Example 2. Creation of the connection with 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 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:

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

Commands can be executed and processed asynchronously, 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 _async shall be used, such as dmitigr::pgfe::Connection::perform_async() or dmitigr::pgfe::Connection::prepare_statement_async().

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. Thus, there is no need to create an instance of dmitigr::pgfe::Sql_string and std::string should be used instead.

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* 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* 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* conn)
{
static const auto sql = dmitigr::pgfe::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();
}

Responses handling

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

void handle_error_example(dmitigr::pgfe::Connection* conn)
{
try {
conn->perform("PROVOKE SYNTAX ERROR");
} catch (const dmitigr::pgfe::Server_exception& e) {
}
}

To initiate asynchronous retrieving of the first response (i.e. with no blocking the thread), methods of the class dmitigr::pgfe::Connection with the suffix "_async" must 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 retrieved object of type dmitigr::pgfe::Error, which contains the error details.

Server responses can be retrieved:

Data type conversions

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

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

There is the partial specialization of the template structure dmitigr::pgfe::Conversions to perform conversions from/to PostgreSQL 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:

In case when all the array elements are non-NULL, such an array can be represented as just the container with elements of type T (ie no need to make it optional). 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.

In light of the above:

User-defined data conversions could be implemented by either:

Signals handling

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

Signals can be handled:

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 can be popped up by using dmitigr::pgfe::Connection::pop_notice() and/or dmitigr::pgfe::Connection::pop_notification() depending on the type of signal.

WARNING If signals are not popped up from the internal storage it may cause memory exhaustion! Thus, signals must be handled anyway!

Dynamic SQL

The standard tools 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:

auto sql = dmitigr::pgfe::Sql_string::make("SELECT :expr::int, ':expr'");

This statement has one named parameter expr and one string constant ‘’:expr'`. If prepare this statement with dmitigr::pgfe::Connection::prepare_statement(), the actual prepared statement parsed by the server will looks like:

SELECT $1::int, ':expr'

Before preparing the statement, it is 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::), ':expr'

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

Working with SQL queries separately of C++ code

The idea of the approach is to store the SQL queries in the separate place, such as a text file. First, the content of this file must be read into the object of type std::string. Next, the object of type dmitigr::pgfe::Sql_vector should be created with dmitigr::pgfe::Sql_vector::make(). Finally, the required SQL string can be accessed by the index or by the extra data, such as, for example, the SQL string identifier which was specified in advance in the related comments of the query. Let's consider the simple SQL input:

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

The vector of two SQL strings can be created from this input with dmitigr::pgfe::Sql_vector::make(). These objects has an extra data specified by the dollar-quoted string constants. (The dollar quoting syntax are well-know way of quoting string literals in PostgreSQL.) Next, these queries 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 std::string 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, ';'
// Next, working with the queries ...
}

Exceptions

Pgfe may throw:

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.

Documentation

The Pgfe documentation is located at the official site here.

Download

The Pgfe repository is located at Github here.

Installation and consuming

Dependencies

Build time settings

Settings that may be specified at build time by using CMake variables are:

  1. the type of the build;
  2. the flag of build the shared library;
  3. the flag of building the tests (default is on);
  4. dependencies;
  5. installation directories;
  6. default values of the connection options.

Details:

CMake variable Possible values Default on Unix Default on Windows
The type of the build
CMAKE_BUILD_TYPE Debug | Release | RelWithDebInfo | MinSizeRel Debug Debug
The flag of build the shared library
BUILD_SHARED_LIBS On | Off On On
The flag of building the tests
PGFE_BUILD_TESTS On | Off On On
Dependencies
LIBPQ_PREFIX a path not set (rely on CMake) not set (rely on CMake)
LIBPQ_LIB_PREFIX a path ${LIBPQ_PREFIX} ${LIBPQ_PREFIX}
LIBPQ_INCLUDE_PREFIX a path ${LIBPQ_PREFIX} ${LIBPQ_PREFIX}
Installation directories
CMAKE_INSTALL_PREFIX an absolute path "/usr/local" "%ProgramFiles%\dmitigr_pgfe"
PGFE_CMAKE_INSTALL_DIR a path relative to CMAKE_INSTALL_PREFIX "share/dmitigr_pgfe/cmake" "cmake"
PGFE_DOC_INSTALL_DIR a path relative to CMAKE_INSTALL_PREFIX "share/dmitigr_pgfe/doc" "doc"
PGFE_LIBRARY_INSTALL_DIR a path relative to CMAKE_INSTALL_PREFIX "lib" "lib"
PGFE_INCLUDES_INSTALL_DIR a path relative to CMAKE_INSTALL_PREFIX "include" "include"
Default values of the connection options
PGFE_CONNECTION_COMMUNICATION_MODE uds | tcp uds tcp
PGFE_CONNECTION_UDS_DIRECTORY an absolute path /tmp unavailable
PGFE_CONNECTION_UDS_FILE_EXTENSION a string 5432 unavailable
PGFE_CONNECTION_UDS_REQUIRE_SERVER_PROCESS_USERNAME a string not set unavailable
PGFE_CONNECTION_TCP_KEEPALIVES_ENABLED On | Off Off Off
PGFE_CONNECTION_TCP_KEEPALIVES_IDLE non-negative number null (system default) null (system default)
PGFE_CONNECTION_TCP_KEEPALIVES_INTERVAL non-negative number null (system default) null (system default)
PGFE_CONNECTION_TCP_KEEPALIVES_COUNT non-negative number null (system default) null (system default)
PGFE_CONNECTION_TCP_HOST_ADDRESS IPv4(v6) address 127.0.0.1 127.0.0.1
PGFE_CONNECTION_TCP_HOST_NAME a string localhost localhost
PGFE_CONNECTION_TCP_HOST_PORT a number 5432 5432
PGFE_CONNECTION_USERNAME a string postgres postgres
PGFE_CONNECTION_DATABASE a string postgres postgres
PGFE_CONNECTION_PASSWORD a string "" ""
PGFE_CONNECTION_KERBEROS_SERVICE_NAME a string null (not used) null (not used)
PGFE_CONNECTION_SSL_ENABLED On | Off Off Off
PGFE_CONNECTION_SSL_SERVER_HOST_NAME_VERIFICATION_ENABLED On | Off Off Off
PGFE_CONNECTION_SSL_COMPRESSION_ENABLED On | Off Off Off
PGFE_CONNECTION_SSL_CERTIFICATE_FILE an absolute path null (libpq's default) null (libpq's default)
PGFE_CONNECTION_SSL_PRIVATE_KEY_FILE an absolute path null (libpq's default) null (libpq's default)
PGFE_CONNECTION_SSL_CERTIFICATE_AUTHORITY_FILE an absolute path null (libpq's default) null (libpq's default)
PGFE_CONNECTION_SSL_CERTIFICATE_REVOCATION_LIST_FILE an absolute path null (libpq's default) null (libpq's default)

Installation in common

The only dependence of Pgfe is libpq. By default, CMake will try to locate it automatically. Anyway, it is possible to manually specify where libpq is located by using the following CMake variables:

Installation on Linux

$ git clone https://github.com/dmitigr/pgfe.git
$ mkdir -p pgfe/build
$ cd pgfe/build
$ cmake -DBUILD_TYPE=Debug ..
$ make
$ sudo make install

The value of the BUILD_TYPE could be replaced. Also, remember about the possibility to specify location of libpq if CMake could not detect it automatically (see "Installation in common" section above).

Installation on Microsoft Windows

Run the Developer Command Prompt for Visual Studio and type:

> git clone https://github.com/dmitigr/pgfe.git
> mkdir pgfe\build
> cd pgfe\build
> cmake -G "Visual Studio 15 2017 Win64" ..
> cmake --build -DBUILD_TYPE=Debug ..

Next, run the Elevated Command Prompt (i.e. the command prompt with administrator privileges) and type:

> cd pgfe\build
> cmake -DBUILD_TYPE=Debug -P cmake_install.cmake

If the target architecture is Win32 or ARM, then "Win64" should be replaced by "Win32" or "ARM" accordingly.

The value of the BUILD_TYPE could be replaced. Also, remember about the possibility to specify location of libpq if CMake could not detect it automatically (see "Installation in common" section above).

WARNING The target architecture must corresponds to the bitness of libpq to link!

Consuming

If you are using CMake the consuming of the Pgfe library is quite simple. For example:

cmake_minimum_required(VERSION 3.10)
project(foo)
find_package(dmitigr_pgfe REQUIRED)
set(CMAKE_CXX_STANDARD 17)
set(CXX_STANDARD_REQUIRED ON)
add_executable(foo foo.cpp)
target_link_libraries(foo dmitigr_pgfe)

The above code snippet is minimal CMakeLists.txt that enough to build the application foo that depends on the Pgfe library.

License

Pgfe library is distributed under zlib license. For conditions of distribution and use, see files LICENSE.txt or pgfe.hpp.

Contributions, sponsorship, partnership

Pgfe has been developed on the own funds. Donations are welcome!

If you are using Pgfe for commercial purposes it is reasonable to donate or even sponsor the further development of Pgfe.

To make a donation, via PayPal please go here or here.

If you need a commercial support, or you need to develop a custom client-side or server-side software based on PostgreSQL, please contact us by sending email to dmiti.nosp@m.gr@g.nosp@m.mail..nosp@m.com.

Pgfe is a free software. Enjoy using it!

Feedback

Any feedback are welcome. Contact us by sending email to dmiti.nosp@m.gr@g.nosp@m.mail..nosp@m.com.

Copyright

Copyright (C) Dmitry Igrishin