Build a C++ App with CockroachDB and libpqxx

Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 10, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

This tutorial shows you how build a simple C++ application with CockroachDB and the C++ libpqxx driver.

We have tested the C++ libpqxx driver enough to claim beta-level support. If you encounter problems, please open an issue with details to help us make progress toward full support.

Step 1. Start CockroachDB

Choose whether to run a temporary local cluster or a free CockroachDB cluster on CockroachDB Serverless (beta). The instructions below will adjust accordingly.

Create a free cluster

  1. If you haven't already, sign up for a CockroachDB Cloud account.
  2. Log in to your CockroachDB Cloud account.
  3. On the Clusters page, click Create Cluster.
  4. On the Create your cluster page, select CockroachDB Serverless.

    Note:

    This cluster will be free forever.

  5. Click Create your free cluster.

Your cluster will be created in approximately 20-30 seconds.

Set up your cluster connection

Once your cluster is created, the Connect to cluster-name dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

  1. In your terminal, run the second command from the dialog to create a new certs directory on your local machine and download the CA certificate to that directory:

    icon/buttons/copy

    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    curl --create-dirs -o ~/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/<cluster-id>/cert
    

    Your cert file will be downloaded to ~/.postgres/root.crt.

    icon/buttons/copy
    mkdir -p $env:appdata\.postgresql\; Invoke-WebRequest -Uri https://cockroachlabs.cloud/clusters/<cluster-id>/cert -OutFile $env:appdata\.postgresql\root.crt
    

    Your cert file will be downloaded to %APPDATA%/.postgres/root.crt.

  2. Copy the connection string provided, which will be used in the next steps (and to connect to your cluster in the future).

    Warning:

    This connection string contains your password, which will be provided only once. If you forget your password, you can reset it by going to the SQL Users page.

    icon/buttons/copy

    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'
    

    icon/buttons/copy
    cockroach sql --url 'postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert='$HOME'/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>'
    
    icon/buttons/copy
    cockroach sql --url "postgresql://<username>:<password>@<serverless-host>:26257/defaultdb?sslmode=verify-full&sslrootcert=$env:appdata/.postgresql/root.crt&options=--cluster=<cluster-name>-<tenant-id>"
    

    Where:

    • <username> is the SQL user. By default, this is your CockroachDB Cloud account username.
    • <password> is the password for the SQL user. The password will be shown only once in the Connection info dialog after creating the cluster.
    • <serverless-host> is the hostname of the CockroachDB Serverless (beta) cluster.
    • <cluster-name>-<tenant-id> is the short name of your cluster plus the tenant ID. For example, funny-skunk-123. The <tenant-id> is used to identify your tenant cluster on a multitenant host.
    • <cluster-id> is a unique string used to identify your cluster when downloading the CA certificate. For example, 12a3bcde-4fa5-6789-1234-56bc7890d123.

    You can find these settings in the Connection parameters tab of the Connection info dialog.

  1. If you haven't already, download the CockroachDB binary.
  2. Run the cockroach demo command:

    icon/buttons/copy
    $ cockroach demo \
    --empty
    

    This starts a temporary, in-memory cluster and opens an interactive SQL shell to the cluster. Any changes to the database will not persist after the cluster is stopped.

  3. Take note of the (sql/tcp) connection string in the SQL shell welcome text:

    # Connection parameters:
    #   (console) http://127.0.0.1:61009
    #   (sql)     postgres://root:admin@?host=%2Fvar%2Ffolders%2Fk1%2Fr048yqpd7_9337rgxm9vb_gw0000gn%2FT%2Fdemo255013852&port=26257
    #   (sql/tcp) postgres://root:admin@127.0.0.1:61011?sslmode=require    
    

    In this example, the port number is 61011. You will use the port number in your application code later.

Step 2. Create a database and a user

  1. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    
  2. Create a SQL user for your app:

    icon/buttons/copy
    > CREATE USER <username> WITH PASSWORD <password>;
    

    Take note of the username and password. You will use it in your application code later.

  3. Give the user the necessary permissions:

    icon/buttons/copy
    > GRANT ALL ON DATABASE bank TO <username>;
    
  1. If you haven't already, download the CockroachDB binary.
  2. Start the built-in SQL shell using the connection string you got from the CockroachDB Cloud Console earlier:

    icon/buttons/copy
    $ cockroach sql \
    --url='postgres://<username>:<password>@<global host>:26257/<cluster_name>.defaultdb?sslmode=verify-full&sslrootcert=<certs_dir>/cc-ca.crt'
    

    In the connection string copied from the CockroachDB Cloud Console, your username, password and cluster name are pre-populated. Replace the <certs_dir> placeholder with the path to the certs directory that you created earlier.

  3. In the SQL shell, create the bank database that your application will use:

    icon/buttons/copy
    > CREATE DATABASE bank;
    

Step 3. Install the libpq and libpqxx drivers

  1. Install libpq on your machine. For example, on macOS:

    icon/buttons/copy
    brew install libpq
    
  2. Install the libpqxx driver, using CMake or the configure script provided in the libpqxx repo.

    Note:

    If you are running macOS, you need to install version 4.0.1 or higher of the libpqxx driver.

Step 4. Get the C++ code

Download the basic-sample.cpp file, or create the file yourself and copy the code into it.

Step 5. Run the code

You'll first run code to create a table and insert some rows, and then you'll run code to read and update values as an atomic transaction.

Basic statements

Use the following code to connect as the user you created earlier and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows.

You will need to open basic-sample.cpp, and edit the connection configuration parameters:

  • Replace the value for username with the user you created earlier.
  • Replace the value for password with the password you created for your user.
  • Replace the value for host with the host to your cluster.
  • Replace the value for port with the port to your cluster.

Use the following code to connect and execute some basic SQL statements, creating a table, inserting rows, and reading and printing the rows.

You will need to open basic-sample.cpp, and edit the following:

  • Replace the connection string in the code with the connection string that was provided in the CockroachDB Cloud Console earlier.
  • Replace defaultdb in the connection string with bank to connect to the bank database you created earlier.
icon/buttons/copy
#include <cassert>
#include <functional>
#include <iostream>
#include <stdexcept>
#include <string>
#include <pqxx/pqxx>

using namespace std;

int main() {
  try {
    // Connect to the "bank" database.
    pqxx::connection c("postgresql://{username}:{password}@{host}:{port}/bank");

    pqxx::nontransaction w(c);

    // Create the "accounts" table.
    w.exec("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT)");

    // Insert two rows into the "accounts" table.
    w.exec("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

    // Print out the balances.
    cout << "Initial balances:" << endl;
    pqxx::result r = w.exec("SELECT id, balance FROM accounts");
    for (auto row : r) {
      cout << row[0].as<int>() << ' ' << row[1].as<int>() << endl;
    }

    w.commit();  // Note this doesn't doesn't do anything
                 // for a nontransaction, but is still required.
  }
  catch (const exception &e) {
    cerr << e.what() << endl;
    return 1;
  }
  cout << "Success" << endl;
  return 0;
}

To build the basic-sample.cpp source code to an executable file named basic-sample, run the following command from the directory that contains the code:

icon/buttons/copy
$ g++ -std=c++17 basic-sample.cpp -lpq -lpqxx -o basic-sample

Then run the basic-sample file from that directory:

icon/buttons/copy
$ ./basic-sample

Transaction (with retry logic)

Next, use the following code to again connect as the user you created earlier but this time execute a batch of statements as an atomic transaction to transfer funds from one account to another, where all included statements are either committed or aborted.

You will need to open basic-sample.cpp, and edit the connection configuration parameters:

  • Replace the value for username with the user you created earlier.
  • Replace the value for password with the password you created for your user.
  • Replace the value for host with the host to your cluster.
  • Replace the value for port with the port to your cluster.

Next, use the following code to again connect, but this time execute a batch of statements as an atomic transaction to transfer funds from one account to another, where all included statements are either committed or aborted.

You will need to open txn-sample.cpp, and edit the following:

  • Replace the connection string in the code with the connection string that was provided in the CockroachDB Cloud Console earlier.
  • Replace defaultdb in the connection string with bank to connect to the bank database you created earlier.
Note:

CockroachDB may require the client to retry a transaction in case of read/write contention. CockroachDB provides a generic retry function that runs inside a transaction and retries it as needed. You can copy and paste the retry function from here into your code.

Download the txn-sample.cpp file, or create the file yourself and copy the code into it.

icon/buttons/copy
#include <cassert>
#include <functional>
#include <iostream>
#include <stdexcept>
#include <string>
#include <pqxx/pqxx>

using namespace std;

void transferFunds(
    pqxx::dbtransaction *tx, int from, int to, int amount) {
  // Read the balance.
  pqxx::result r = tx->exec(
      "SELECT balance FROM accounts WHERE id = " + to_string(from));
  assert(r.size() == 1);
  int fromBalance = r[0][0].as<int>();

  if (fromBalance < amount) {
    throw domain_error("insufficient funds");
  }

  // Perform the transfer.
  tx->exec("UPDATE accounts SET balance = balance - "
      + to_string(amount) + " WHERE id = " + to_string(from));
  tx->exec("UPDATE accounts SET balance = balance + "
      + to_string(amount) + " WHERE id = " + to_string(to));
}


// ExecuteTx runs fn inside a transaction and retries it as needed.
// On non-retryable failures, the transaction is aborted and rolled
// back; on success, the transaction is committed.
//
// For more information about CockroachDB's transaction model see
// https://cockroachlabs.com/docs/transactions.html.
//
// NOTE: the supplied exec closure should not have external side
// effects beyond changes to the database.
void executeTx(
    pqxx::connection *c, function<void (pqxx::dbtransaction *tx)> fn) {
  pqxx::work tx(*c);
  while (true) {
    try {
      pqxx::subtransaction s(tx, "cockroach_restart");
      fn(&s);
      s.commit();
      break;
    } catch (const pqxx::sql_error& e) {
      // Swallow "transaction restart" errors; the transaction will be retried.
      // Unfortunately libpqxx doesn't give us access to the error code, so we
      // do string matching to identify retryable errors.
      if (string(e.what()).find("restart") == string::npos) {
        throw;
      }
    }
  }
  tx.commit();
}

int main() {
  try {
    pqxx::connection c("postgresql://{username}:{password}@{host}:{port}/bank");

    executeTx(&c, [](pqxx::dbtransaction *tx) {
          transferFunds(tx, 1, 2, 100);
      });
  }
  catch (const exception &e) {
    cerr << e.what() << endl;
    return 1;
  }
  cout << "Success" << endl;
  return 0;
}

To build the txn-sample.cpp source code to an executable file named txn-sample, run the following command from the directory that contains the code:

icon/buttons/copy
$ g++ -std=c++17 txn-sample.cpp -lpq -lpqxx -o txn-sample

Then run the txn-sample file from that directory:

icon/buttons/copy
$ ./txn-sample

After running the code, use the built-in SQL client to verify that funds were transferred from one account to another:

icon/buttons/copy
$ cockroach sql --url 'postgresql://{username}:{password}@{host}:{port}/{cluster_name}.bank?sslmode=verify-full&sslrootcert={path/to/ca.crt}' -e 'SELECT id, balance FROM accounts'
id | balance
+----+---------+
 1 |     900
 2 |     350
(2 rows)

What's next?

Read more about using the C++ libpqxx driver.

You might also be interested in the following pages:

YesYes NoNo