Lesson 23-C++ Operating PostgreSQL Database

PostgreSQL is a powerful, open-source relational database widely used in enterprise applications, data analysis, and other scenarios. Interacting with PostgreSQL in C++ requires the use of its official C++ interface library libpqxx (or the C interface library libpq).

Environment Configuration

Installing PostgreSQL and Development Libraries

To use C++ to operate PostgreSQL, you need to install the PostgreSQL service and development libraries (including header files and link libraries) first.

Windows System:

  1. Install PostgreSQL Service:
    Download the Windows installer from the PostgreSQL official website (choose the version matching your system), and follow the wizard to install (default port 5432).
  2. Install Development Libraries:
    During installation, check “Development Tools”, or separately download the libpq development package (includes libpq.lib, libpqxx.lib, etc.).

Linux System (Ubuntu as an example):

# Install PostgreSQL service
sudo apt-get install postgresql postgresql-contrib

# Install development libraries (including header files and link libraries)
sudo apt-get install libpq-dev libpqxx-dev

macOS System:

# Install PostgreSQL using Homebrew
brew install postgresql

# Install development library (libpqxx)
brew install libpqxx

Configuring the Compiler

When compiling a C++ program, you need to specify the paths to PostgreSQL header files and library files.

Windows (Visual Studio):

  1. Include Directories: Add the libpqxx header file path (e.g., C:\Program Files\PostgreSQL\15\include).
  2. Library Directories: Add the libpqxx library file path (e.g., C:\Program Files\PostgreSQL\15\lib).
  3. Link Dependencies: In “Linker → Input → Additional Dependencies”, add libpqxx.lib and libpq.lib (choose Debug/Release version based on the library type).

Linux (g++):

Use -lpqxx and -lpq to link libraries:

g++ your_code.cpp -o output -lpqxx -lpq

macOS (Clang):

clang++ your_code.cpp -o output -lpqxx -lpq

Connecting to PostgreSQL Database

libpqxx is the official C++ wrapper library provided by PostgreSQL, supporting object-oriented programming and simplifying the connection and operation process.

Example Code: Establishing a Connection

#include <pqxx/pqxx>  // Include libpqxx header file
#include <iostream>

int main() {
    try {
        // Connection string (format: postgresql://user:password@host:port/database_name)
        std::string conn_str = "postgresql://postgres:your_password@localhost:5432/test_db";
        
        // Establish connection
        pqxx::connection conn(conn_str);
        if (conn.is_open()) {
            std::cout << "Connection successful! Database: " << conn.dbname() << std::endl;
        } else {
            std::cerr << "Connection failed" << std::endl;
            return 1;
        }

        // Execute a simple query (example: query PostgreSQL version)
        pqxx::work txn(conn);  // Transaction object (auto-commit disabled by default)
        pqxx::result res = txn.exec("SELECT VERSION()");

        // Process result set
        if (!res.empty()) {
            std::cout << "PostgreSQL version: " << res[0][0].as<std::string>() << std::endl;
        }

        // Commit transaction (optional, for read-only query, auto-commit applies)
        txn.commit();

    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
        return 1;
    }

    return 0;
}

Key Steps Explained:

  • Connection String: Supports multiple formats (e.g., host=localhost port=5432 dbname=test_db user=postgres password=xxx).
  • Transaction Object: pqxx::work is used to manage transactions (auto-commit disabled by default, requires explicit commit() call).
  • Resource Management: pqxx::connection and pqxx::work objects automatically release the connection when they go out of scope.

Using Native C Interface (libpq)

For lower-level control (e.g., high-performance scenarios), you can use the native C API (libpq) provided by PostgreSQL.

Example Code: Establishing a Connection

#include <libpq-fe.h>
#include <iostream>
#include <string>

int main() {
    PGconn* conn = PQconnectdb(
        "host=localhost port=5432 dbname=test_db user=postgres password=your_password"
    );

    // Check connection status
    if (PQstatus(conn) != CONNECTION_OK) {
        std::cerr << "Connection failed: " << PQerrorMessage(conn) << std::endl;
        PQfinish(conn);
        return 1;
    }

    std::cout << "Connection successful!" << std::endl;

    // Execute query
    PGresult* res = PQexec(conn, "SELECT VERSION()");
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        std::cerr << "Query failed: " << PQerrorMessage(conn) << std::endl;
    } else {
        std::cout << "PostgreSQL version: " << PQgetvalue(res, 0, 0) << std::endl;
    }

    // Release resources
    PQclear(res);
    PQfinish(conn);
    return 0;
}

Key Steps Explained:

  • Connection Status: PQstatus(conn) returns the connection status (CONNECTION_OK indicates success).
  • Error Handling: PQerrorMessage(conn) retrieves specific error messages.
  • Result Set Handling: PQgetvalue(res, row, col) retrieves result values by row and column index.

Executing SQL Operations (CRUD)

Inserting Data (INSERT)

Use the INSERT INTO statement to insert data. It is recommended to use prepared statements to avoid SQL injection.

Example (libpqxx):

#include <pqxx/pqxx>

void insert_user(pqxx::connection& conn, const std::string& name, int age, const std::string& email) {
    try {
        pqxx::work txn(conn);
        // Prepared statement (? as placeholder)
        pqxx::prepared_statement pstmt(
            "INSERT INTO users (name, age, email) VALUES ($1, $2, $3)"
        );
        // Bind parameters ($1, $2, $3 correspond to placeholders)
        pstmt("Zhang San", 25, "zhangsan@example.com");
        // Execute insertion
        pqxx::result res = pstmt.exec();
        std::cout << "Insertion successful, ID: " << res[0][0].as<int>() << std::endl;
        txn.commit();
    } catch (const std::exception& e) {
        std::cerr << "Insertion failed: " << e.what() << std::endl;
    }
}

Querying Data (SELECT)

Query data using the SELECT statement to retrieve result sets and iterate through them.

Example (libpqxx):

void query_users(pqxx::connection& conn) {
    try {
        pqxx::work txn(conn);
        pqxx::result res = txn.exec("SELECT id, name, age FROM users WHERE age > $1");
        res.parameter(1) = 18;  // Bind parameter ($1 corresponds to condition >18)

        // Iterate through result set
        for (const auto& row : res) {
            int id = row[0].as<int>();
            std::string name = row[1].as<std::string>();
            int age = row[2].as<int>();
            std::cout << "User: " << name << " (ID: " << id << ", Age: " << age << ")" << std::endl;
        }

        txn.commit();
    } catch (const std::exception& e) {
        std::cerr << "Query failed: " << e.what() << std::endl;
    }
}

Updating Data (UPDATE)

Use the UPDATE statement to modify existing data.

Example (libpqxx):

void update_user_age(pqxx::connection& conn, int user_id, int new_age) {
    try {
        pqxx::work txn(conn);
        pqxx::result res = txn.exec_prepared(
            "update_user_age",  // Prepared statement name (must be declared in advance)
            user_id,            // Parameter 1: user ID
            new_age             // Parameter 2: new age
        );
        std::cout << "Update successful, affected rows: " << res.affected_rows() << std::endl;
        txn.commit();
    } catch (const std::exception& e) {
        std::cerr << "Update failed: " << e.what() << std::endl;
    }
}

Deleting Data (DELETE)

Use the DELETE statement to delete data.

Example (libpqxx):

void delete_user(pqxx::connection& conn, int user_id) {
    try {
        pqxx::work txn(conn);
        pqxx::result res = txn.exec(
            "DELETE FROM users WHERE id = $1",
            user_id  // Bind parameter ($1 corresponds to user ID)
        );
        std::cout << "Deletion successful, affected rows: " << res.affected_rows() << std::endl;
        txn.commit();
    } catch (const std::exception& e) {
        std::cerr << "Deletion failed: " << e.what() << std::endl;
    }
}

Transaction Processing (Transaction)

Transactions are used to ensure the atomicity of a group of SQL operations (either all succeed or all roll back), suitable for scenarios requiring data consistency (e.g., bank transfers).

Example (libpqxx):

void transfer_money(pqxx::connection& conn, int from_id, int to_id, double amount) {
    try {
        pqxx::work txn(conn);
        txn.autocommit(false);  // Disable auto-commit

        // Operation 1: Deduct from source account
        pqxx::result res1 = txn.exec_prepared(
            "withdraw", from_id, amount
        );
        if (res1.affected_rows() == 0) {
            throw std::runtime_error("Source account does not exist or insufficient balance");
        }

        // Operation 2: Add to destination account
        pqxx::result res2 = txn.exec_prepared(
            "deposit", to_id, amount
        );
        if (res2.affected_rows() == 0) {
            throw std::runtime_error("Destination account does not exist");
        }

        // Commit transaction
        txn.commit();
        std::cout << "Transfer successful, amount: " << amount << std::endl;

    } catch (const std::exception& e) {
        // Roll back transaction
        txn.rollback();
        std::cerr << "Transfer failed, rolled back: " << e.what() << std::endl;
    }
}

Advanced Techniques

Batch Operations (Batch Insert)

Use the COPY command or batch parameter binding to reduce network I/O overhead.

Example (libpqxx Batch Insert):

void batch_insert_users(pqxx::connection& conn, const std::vector<User>& users) {
    try {
        pqxx::work txn(conn);
        // Use COPY command for batch insert (requires temporary table)
        conn.exec(
            "CREATE TEMP TABLE tmp_users (name TEXT, age INT, email TEXT)"
        );
        pqxx::stream_to stream(conn, "tmp_users");
        for (const auto& user : users) {
            stream << user.name << user.age << user.email << pqxx::endrow;
        }
        stream.complete();

        // Insert data from temporary table into target table
        conn.exec(
            "INSERT INTO users (name, age, email) SELECT name, age, email FROM tmp_users"
        );

        txn.commit();
        std::cout << "Batch insertion completed, total " << users.size() << " records" << std::endl;

    } catch (const std::exception& e) {
        std::cerr << "Batch insertion failed: " << e.what() << std::endl;
    }
}

Calling Stored Procedures

Call PostgreSQL stored procedures, supporting input and output parameters.

Example (libpqxx Calling Stored Procedure):

// Assume stored procedure: CREATE OR REPLACE PROCEDURE get_user_email(
//     IN p_id INT, 
//     OUT p_email TEXT
// ) LANGUAGE plpgsql AS $$
// BEGIN
//     SELECT email INTO p_email FROM users WHERE id = p_id;
// END;
// 
$$;

void get_user_email(pqxx::connection& conn, int user_id) {
    try {
        pqxx::work txn(conn);
        pqxx::result res = txn.exec_prepared(
            "call_get_user_email",  // Stored procedure name
            user_id                 // Input parameter
        );
        res.parameter(1) = user_id;  // Bind input parameter

        // Get output parameter (assume stored procedure output parameter named p_email)
        std::string email = res[0][0].as<std::string>();
        std::cout << "Email for user ID " << user_id << ": " << email << std::endl;

        txn.commit();
    } catch (const std::exception& e) {
        std::cerr << "Calling stored procedure failed: " << e.what() << std::endl;
    }
}

Multithreading Support

libpq Multithreading Support

libpq itself is not thread-safe, but it can be used in a multithreaded environment with proper methods.

#include <thread>
#include <mutex>

std::mutex g_mutex;

void thread_function(PGconn* conn) {
    std::lock_guard<std::mutex> lock(g_mutex);
    PGresult* res = PQexec(conn, "SELECT * FROM mytable");
    // Process result
    PQclear(res);
}

int main() {
    PGconn* conn = PQconnectdb("dbname=testdb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432");

    std::vector<std::thread> threads;
    for (int i = 0; i < 10; ++i) {
        threads.emplace_back(thread_function, conn);
    }

    for (auto &t : threads) {
        t.join();
    }

    PQfinish(conn);
    return 0;
}

libpqxx Multithreading Support

libpqxx provides better multithreading support.

#include <thread>

void thread_function(pqxx::connection &conn) {
    pqxx::work txn(conn);
    pqxx::result res = txn.exec("SELECT * FROM mytable");
    // Process result
    txn.commit();
}

int main() {
    pqxx::connection conn("dbname=testdb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432");

    std::vector<std::thread> threads;
    for (int i = 0; i < 10; ++i) {
        threads.emplace_back(thread_function, std::ref(conn));
    }

    for (auto &t : threads) {
        t.join();
    }

    conn.disconnect();
    return 0;
}

Performance Optimization

Batch Insertion

Using transactions for batch data insertion can significantly improve performance.

pqxx::work txn(conn);
for (int i = 0; i < 1000; ++i) {
    txn.exec("INSERT INTO mytable (column1, column2) VALUES ('value1', 'value2')");
}
txn.commit();

Connection Pool

Using a connection pool can reuse connections and reduce connection establishment time.

class ConnectionPool {
public:
    shared_ptr<pqxx::connection> getConnection() {
        if (!m_freeConnections.empty()) {
            shared_ptr<pqxx::connection> conn = m_freeConnections.front();
            m_freeConnections.pop();
            return conn;
        }
        return nullptr;
    }

    void releaseConnection(shared_ptr<pqxx::connection> conn) {
        m_freeConnections.push(conn);
    }

private:
    queue<shared_ptr<pqxx::connection>> m_freeConnections;
};

Security

SQL Injection Protection

Use parameterized queries to avoid SQL injection.

pqxx::work txn(conn);
txn.exec("INSERT INTO mytable (column1, column2) VALUES ($1, $2)", "value1", "value2");
txn.commit();

Data Encryption

Encrypt sensitive data for storage.

pqxx::work txn(conn);
std::string encrypted_value = encrypt("sensitive data");
txn.exec("INSERT INTO mytable (column1) VALUES ($1)", encrypted_value);
txn.commit();

Use TLS/SSL to protect data transmission.

pqxx::connection conn("dbname=testdb user=myuser password=mypassword hostaddr=127.0.0.1 port=5432 sslmode=require");

Advanced Topics

ORM Framework

Use ORM frameworks like libpqxx‘s nontransaction and work objects to simplify database operations.

pqxx::nontransaction n(conn);
pqxx::result res = n.exec("SELECT * FROM mytable");
for (const auto &row : res) {
    std::cout << "Row: " << row[0].as<std::string>() << std::endl;
}

Concurrent Programming

Use multithreading to execute database operations concurrently.

std::vector<std::thread> threads;
for (int i = 0; i < 10; ++i) {
    threads.emplace_back(thread_function, std::ref(conn));
}

Best Practices

Code Organization

Encapsulate database operations into classes or functions.

class MyDatabase {
public:
    void insertData(const std::string &data) {
        pqxx::work txn(conn);
        txn.exec("INSERT INTO mytable (column1) VALUES ($1)", data);
        txn.commit();
    }

private:
    pqxx::connection conn;
};

Unit Testing

Write unit tests to ensure the correctness of database operations.

#include <gtest/gtest.h>

TEST(MyDatabaseTest, InsertData) {
    MyDatabase db;
    db.insertData("test data");
    // Verify data is inserted correctly
}
Share your love