PostgreSQL - C / C++

PostgreSQL - Modern C++ Interface with libpqxx

This tutorial covers libpqxx, the official C++ client API for PostgreSQL. We will focus on modern C++ practices (C++17 and later) and a modern development workflow using CMake.

Installation and Project Setup (Modern Approach)

Manually downloading and building libraries is an outdated practice. Modern C++ projects use package managers like vcpkg or Conan, or system package managers. We’ll use a CMake build system, which is the industry standard.

1. Install libpqxx

Use your system’s package manager. For example:

# On Debian/Ubuntu
sudo apt-get install libpqxx-dev

# On macOS with Homebrew
brew install libpqxx

# On Windows with vcpkg
vcpkg install libpqxx:x64-windows

2. Configure pg_hba.conf

Ensure your pg_hba.conf file allows local connections. For development, a line like this is common. Find this file in your PostgreSQL data directory.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256

Note: md5 is older; scram-sha-256 is the modern default for password authentication. Restart the PostgreSQL server after making changes.

Core libpqxx Concepts

• pqxx::connection: Represents the connection to the database. It should be created once and can be long-lived.
• pqxx::work: A transaction object. All SQL commands that modify data should be executed within a transaction. It ensures atomicity: either all commands succeed, or none do.
• pqxx::nontransaction: A non-transactional object, typically used for read-only SELECT queries for slightly better performance.
• Prepared Statements: The safest and most efficient way to execute the same query multiple times with different parameters. They prevent SQL injection attacks.

Complete Example: CRUD Operations

Let’s create a single main.cpp file demonstrating a full connection and CRUD (Create, Read, Update, Delete) cycle.

main.cpp

#include <iostream>
#include <stdexcept>
#include <pqxx/pqxx>

int main() {
    // Best practice: Use a connection string. Avoid hardcoding credentials.
    // Consider loading from environment variables or a config file.
    const std::string conn_string = "dbname=testdb user=postgres password=pass123 hostaddr=127.0.0.1 port=5432";

    try {
        pqxx::connection c{conn_string};
        std::cout << "Opened database successfully: " << c.dbname() << std::endl;

        // --- CREATE TABLE (within a transaction) ---
        {
            pqxx::work w{c};
            w.exec0(
                "CREATE TABLE IF NOT EXISTS employees ("
                "id SERIAL PRIMARY KEY, "
                "name TEXT NOT NULL UNIQUE, "
                "salary INT);"
            );
            w.commit();
            std::cout << "Table 'employees' created or already exists." << std::endl;
        }

        // --- INSERT using a PREPARED STATEMENT (Best Practice) ---
        {
            pqxx::work w{c};
            c.prepare("insert_employee", "INSERT INTO employees (name, salary) VALUES ($1, $2)");

            w.exec_prepared("insert_employee", "Alice", 95000);
            w.exec_prepared("insert_employee", "Bob", 80000);
            std::cout << "Inserted Alice and Bob via prepared statement." << std::endl;

            // Use ON CONFLICT for an 'upsert' operation
            w.exec0("INSERT INTO employees (name, salary) VALUES ('Alice', 96000) "
                    "ON CONFLICT (name) DO UPDATE SET salary = EXCLUDED.salary;");
            std::cout << "Upserted Alice's salary." << std::endl;
            w.commit();
        }

        // --- SELECT (read-only, can use nontransaction) ---
        {
            pqxx::nontransaction n{c};
            std::cout << "\n--- Current Employees --- " << std::endl;
            for (auto const &[id, name, salary] : n.query<int, std::string, int>("SELECT id, name, salary FROM employees ORDER BY name")) {
                std::cout << "ID: " << id << ", Name: " << name << ", Salary: " << salary << std::endl;
            }
        }

        // --- UPDATE and DELETE ---
        {
            pqxx::work w{c};
            // Update Bob's salary
            pqxx::result res = w.exec_params("UPDATE employees SET salary = $1 WHERE name = $2", 85000, "Bob");
            std::cout << "\nUpdated " << res.affected_rows() << " row(s)." << std::endl;

            // Delete Alice
            res = w.exec_params("DELETE FROM employees WHERE name = $1", "Alice");
            std::cout << "Deleted " << res.affected_rows() << " row(s)." << std::endl;
            w.commit();
        }

        c.close();
        std::cout << "\nOperation done successfully." << std::endl;

    } catch (const pqxx::sql_error &e) {
        std::cerr << "SQL error: " << e.what() << std::endl;
        std::cerr << "Query was: " << e.query() << std::endl;
        return 1;
    } catch (const std::exception &e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return 1;
    }

    return 0;
}

Building with CMake

Create a CMakeLists.txt file in the same directory as main.cpp.

CMakeLists.txt

cmake_minimum_required(VERSION 3.15)
project(PostgresExample CXX)

set(CMAKE_CXX_STANDARD 17)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# Find the libpqxx library
find_package(libpqxx REQUIRED)

add_executable(app main.cpp)

# Link libpqxx and its dependencies (libpq)
target_link_libraries(app PRIVATE pqxx::pqxx)

Now, compile and run the project from your terminal:

# Create a build directory
mkdir build
cd build

# Configure the project with CMake
cMAKE ..

# Build the executable
cMAKE --build .

# Run the application
./app