Build a C++ App with CockroachDB and libpqxx

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 CockroachCloud. The instructions below will adjust accordingly.

Create a free cluster

If you haven't already, sign up for a CockroachCloud account.
Log in to your CockroachCloud account.
On the Clusters page, click Create Cluster.
On the Create your cluster page, select the Free Plan.

Note:

This cluster will be free forever.
(Optional) Select a cloud provider (GCP or AWS) in the Additional configuration section.
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 Connection info dialog displays. Use the information provided in the dialog to set up your cluster connection for the SQL user that was created by default:

Click the name of the cc-ca.crt to download the CA certificate to your local machine.
Create a certs directory on your local machine:
```
$ mkdir certs
```
Move the downloaded cc-ca.crt file to the certs directory:
```
$ mv <path>/<to>/cc-ca.crt <path>/<to>/certs
```
For example:
```
$ mv Users/maxroach/Downloads/cc-ca.crt Users/maxroach/certs
```
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.

If you haven't already, download the CockroachDB binary.
Run the cockroach demo command:
```
$ 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.

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

# Connection parameters:
#   (console) http://127.0.0.1:8080/demologin?password=demo11762&username=demo
#   (sql)     postgres://demo:demo11762@?host=%2Fvar%2Ffolders%2Fc8%2Fb_q93vjj0ybfz0fz0z8vy9zc0000gp%2FT%2Fdemo382139081&port=26257
#   (sql/tcp) postgres://demo:demo11762@127.0.0.1:26257?sslmode=require

Step 2. Create a database and a user

In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```
Create a SQL user for your app:
```
> CREATE USER <username> WITH PASSWORD <password>;
```
Take note of the username and password. You will use it in your application code later.
Give the user the necessary permissions:
```
> GRANT ALL ON DATABASE bank TO <username>;
```

If you haven't already, download the CockroachDB binary.
Start the built-in SQL shell using the connection string you got from the CockroachCloud Console earlier:
```
$ 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 CockroachCloud 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.
In the SQL shell, create the bank database that your application will use:
```
> CREATE DATABASE bank;
```

Step 3. Install the libpq and libpqxx drivers

Install libpq on your machine. For example, on macOS:
```
brew install libpq
```
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 CockroachCloud Console earlier.
Replace defaultdb in the connection string with bank to connect to the bank database you created earlier.

#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}/{cluster_name}.bank?sslmode=verify-full&sslrootcert={path/to/ca.crt}");

    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:

$ g++ -std=c++17 basic-sample.cpp -lpq -lpqxx -o basic-sample

Then run the basic-sample file from that directory:

$ ./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 CockroachCloud 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.

#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}/{cluster_name}.bank?sslmode=verify-full&sslrootcert={path/to/ca.crt}");

    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:

$ g++ -std=c++17 txn-sample.cpp -lpq -lpqxx -o txn-sample

Then run the txn-sample file from that directory:

$ ./txn-sample

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

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

Was this page helpful?

Yes

The Most Highly Evolved Database on the Planet

CockroachDB

CockroachCloud

Get to know CockroachDB