# Build a PHP App with CockroachDB and php-pgsql

This tutorial shows you how build a simple PHP application with CockroachDB and the php-pgsql driver.

We have tested the php-pgsql 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.

## Before you begin

1. Install CockroachDB.
2. Start up a secure or insecure local cluster.
3. Choose the instructions that correspond to whether your cluster is secure or insecure:

## Step 1. Install the php-pgsql driver

Install the php-pgsql driver as described in the official documentation.

## Step 2. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

    $ cockroach sql --certs-dir=certs

In the SQL shell, issue the following statements to create the `maxroach` user and `bank` database:

    > CREATE USER IF NOT EXISTS maxroach;

    > CREATE DATABASE bank;

Give the `maxroach` user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

    > \q

## Step 3. Generate a certificate for the `maxroach` user

Create a certificate and key for the `maxroach` user by running the following command. The code samples will run as this user.

    $ cockroach cert create-client maxroach --certs-dir=certs --ca-key=my-safe-directory/ca.key

## Step 4. Run the PHP code

Now that you have a database and a user, you'll 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

First, use the following code to connect as the `maxroach` user and execute some basic SQL statements, inserting rows and reading and printing the rows.

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

    <?php
    try {
        $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=require;sslrootcert=certs/ca.crt;sslkey=certs/client.maxroach.key;sslcert=certs/client.maxroach.crt',
        'maxroach', null, array(
          PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => true,
          PDO::ATTR_PERSISTENT => true
        ));

      $dbh->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)');

      print "Account balances:\r\n";
      foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
          print $row['id'] . ': ' . $row['balance'] . "\r\n";
      }
    } catch (Exception $e) {
        print $e->getMessage() . "\r\n";
        exit(1);
    }
    ?>

The output should be:

    Account balances:
    1: 1000
    2: 250

### Transaction (with retry logic)

Next, use the following code to again connect as the `maxroach` user 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.

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

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.

    <?php

    function transferMoney($dbh, $from, $to, $amount) {
      try {
        $dbh->beginTransaction();
        // This savepoint allows us to retry our transaction.
        $dbh->exec("SAVEPOINT cockroach_restart");
      } catch (Exception $e) {
        throw $e;
      }

      while (true) {
        try {
          $stmt = $dbh->prepare(
            'UPDATE accounts SET balance = balance + :deposit ' .
            'WHERE id = :account AND (:deposit > 0 OR balance + :deposit >= 0)');

          // First, withdraw the money from the old account (if possible).
          $stmt->bindValue(':account', $from, PDO::PARAM_INT);
          $stmt->bindValue(':deposit', -$amount, PDO::PARAM_INT);
          $stmt->execute();
          if ($stmt->rowCount() == 0) {
            print "source account does not exist or is underfunded\r\n";
            return;
          }

          // Next, deposit into the new account (if it exists).
          $stmt->bindValue(':account', $to, PDO::PARAM_INT);
          $stmt->bindValue(':deposit', $amount, PDO::PARAM_INT);
          $stmt->execute();
          if ($stmt->rowCount() == 0) {
            print "destination account does not exist\r\n";
            return;
          }

          // Attempt to release the savepoint (which is really the commit).
          $dbh->exec('RELEASE SAVEPOINT cockroach_restart');
          $dbh->commit();
          return;
        } catch (PDOException $e) {
          if ($e->getCode() != '40001') {
            // Non-recoverable error. Rollback and bubble error up the chain.
            $dbh->rollBack();
            throw $e;
          } else {
            // Cockroach transaction retry code. Rollback to the savepoint and
            // restart.
            $dbh->exec('ROLLBACK TO SAVEPOINT cockroach_restart');
          }
        }
      }
    }

    try {
        $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=require;sslrootcert=certs/ca.crt;sslkey=certs/client.maxroach.key;sslcert=certs/client.maxroach.crt',
        'maxroach', null, array(
          PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => true,
        ));

      transferMoney($dbh, 1, 2, 10);

      print "Account balances after transfer:\r\n";
      foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
          print $row['id'] . ': ' . $row['balance'] . "\r\n";
      }
    } catch (Exception $e) {
        print $e->getMessage() . "\r\n";
        exit(1);
    }
    ?>

The output should be:

    Account balances after transfer:
    1: 900
    2: 350

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

    $ cockroach sql --certs-dir=certs -e 'SELECT id, balance FROM accounts' --database=bank

    +----+---------+
    | id | balance |
    +----+---------+
    |  1 |     900 |
    |  2 |     350 |
    +----+---------+
    (2 rows)

## Step 2. Create the `maxroach` user and `bank` database

Start the built-in SQL shell:

    $ cockroach sql --insecure

In the SQL shell, issue the following statements to create the `maxroach` user and `bank` database:

    > CREATE USER IF NOT EXISTS maxroach;

    > CREATE DATABASE bank;

Give the `maxroach` user the necessary permissions:

    > GRANT ALL ON DATABASE bank TO maxroach;

Exit the SQL shell:

    > \q

## Step 3. Run the PHP code

Now that you have a database and a user, you'll 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

First, use the following code to connect as the `maxroach` user and execute some basic SQL statements, inserting rows and reading and printing the rows.

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

    <?php
    try {
      $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
        'maxroach', null, array(
          PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => true,
          PDO::ATTR_PERSISTENT => true
        ));

      $dbh->exec('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)');

      print "Account balances:\r\n";
      foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
          print $row['id'] . ': ' . $row['balance'] . "\r\n";
      }
    } catch (Exception $e) {
        print $e->getMessage() . "\r\n";
        exit(1);
    }
    ?>

The output should be:

    Account balances:
    1: 1000
    2: 250

### Transaction (with retry logic)

Next, use the following code to again connect as the `maxroach` user 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.

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

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.

    <?php

    function transferMoney($dbh, $from, $to, $amount) {
      try {
        $dbh->beginTransaction();
        // This savepoint allows us to retry our transaction.
        $dbh->exec("SAVEPOINT cockroach_restart");
      } catch (Exception $e) {
        throw $e;
      }

      while (true) {
        try {
          $stmt = $dbh->prepare(
            'UPDATE accounts SET balance = balance + :deposit ' .
            'WHERE id = :account AND (:deposit > 0 OR balance + :deposit >= 0)');

          // First, withdraw the money from the old account (if possible).
          $stmt->bindValue(':account', $from, PDO::PARAM_INT);
          $stmt->bindValue(':deposit', -$amount, PDO::PARAM_INT);
          $stmt->execute();
          if ($stmt->rowCount() == 0) {
            print "source account does not exist or is underfunded\r\n";
            return;
          }

          // Next, deposit into the new account (if it exists).
          $stmt->bindValue(':account', $to, PDO::PARAM_INT);
          $stmt->bindValue(':deposit', $amount, PDO::PARAM_INT);
          $stmt->execute();
          if ($stmt->rowCount() == 0) {
            print "destination account does not exist\r\n";
            return;
          }

          // Attempt to release the savepoint (which is really the commit).
          $dbh->exec('RELEASE SAVEPOINT cockroach_restart');
          $dbh->commit();
          return;
        } catch (PDOException $e) {
          if ($e->getCode() != '40001') {
            // Non-recoverable error. Rollback and bubble error up the chain.
            $dbh->rollBack();
            throw $e;
          } else {
            // Cockroach transaction retry code. Rollback to the savepoint and
            // restart.
            $dbh->exec('ROLLBACK TO SAVEPOINT cockroach_restart');
          }
        }
      }
    }

    try {
      $dbh = new PDO('pgsql:host=localhost;port=26257;dbname=bank;sslmode=disable',
        'maxroach', null, array(
          PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => true,
        ));

      transferMoney($dbh, 1, 2, 10);

      print "Account balances after transfer:\r\n";
      foreach ($dbh->query('SELECT id, balance FROM accounts') as $row) {
          print $row['id'] . ': ' . $row['balance'] . "\r\n";
      }
    } catch (Exception $e) {
        print $e->getMessage() . "\r\n";
        exit(1);
    }
    ?>

The output should be:

    Account balances after transfer:
    1: 900
    2: 350

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

    $ cockroach sql --insecure -e 'SELECT id, balance FROM accounts' --database=bank

    +----+---------+
    | id | balance |
    +----+---------+
    |  1 |     900 |
    |  2 |     350 |
    +----+---------+
    (2 rows)

## What's next?

Read more about using the php-pgsql driver.

You might also be interested in the following pages:

- Client Connection Parameters
- Data Replication
- Fault Tolerance & Recovery
- Replication & Rebalancing
- Cross-Cloud Migration
- Automated Operations