This page is CockroachDB’s Hello, World! tutorial. It walks you through creating a user, creating a database, granting privileges on the database to the new user, and then connecting with that user from your preferred language to execute basic statements as well as more complex transactions.

Before You Begin

Make sure you have already:

Feel free to watch this process in action before going through the steps yourself. Note that the demo video features Python code for executing basic statements (step 4), but the code for executing more complex transactions is not covered (step 5). Also note that you can copy commands directly from the video, and you can use < and > to go back and forward.

Step 1. Create a user

As the root user, use the cockroach user command to create a new user, maxroach.

$ cockroach user set maxroach
INSERT 1

Step 2. Create a database and grant privileges

As the root user, use the built-in SQL client to create a bank database and grant privileges to the maxroach user. The privileges will enable the user to execute statements in the next steps.

$ cockroach sql -e 'CREATE DATABASE bank'
CREATE DATABASE
$ cockroach sql -e 'GRANT ALL ON DATABASE bank TO maxroach'
GRANT

Step 3. Create a table in the new database

As the maxroach user, use the built-in SQL client to create an accounts table in the new database.

$ cockroach sql --database=bank --user=maxroach -e \
'CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)'
CREATE TABLE

Step 4. Execute basic statements from a client

As the maxroach user, connect from your preferred language, insert a few rows into the accounts table, and read and print the rows.

Python

# Import the driver.
import psycopg2

# Connect to the "bank" database.
conn = psycopg2.connect(database='bank', user='maxroach', host='localhost', port=26257)

# Make each statement commit immediately.
conn.set_session(autocommit=True)

# Open a cursor to perform database operations.
cur = conn.cursor()

# Insert two rows into the "accounts" table.
cur.execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)")

# Print out the balances.
cur.execute("SELECT id, balance FROM accounts")
rows = cur.fetchall()
print('Initial balances:')
for row in rows:
    print([str(cell) for cell in row])

# Close the database connection.
cur.close()
conn.close()

The balance printout will look like this:

Initial balances:
['1', '1000']
['2', '250']

Ruby

# Import the driver.
require 'pg'

# Connect to the "bank" database.
conn = PG.connect(user: 'maxroach', dbname: 'bank', host: 'localhost', port: 26257)

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

# Print out the balances.
puts "Initial balances:"
conn.exec("SELECT id, balance FROM accounts") do |res|
        res.each do |row|
                puts row
        end
end

# Close communication with the database.
conn.close()

Go

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

func main() {
	db, err := sql.Open("postgres", "postgresql:[email protected]:26257/bank?sslmode=disable")
	if err != nil {
		log.Fatalf("error connection to the database: %s", err)
	}

	// Insert two rows into the "accounts" table.
	if _, err := db.Exec(
		"INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
		log.Fatal(err)
	}

	// Print out the balances.
	rows, err := db.Query("SELECT id, balance FROM accounts")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	fmt.Println("Initial balances:")
	for rows.Next() {
		var id, balance int
		if err := rows.Scan(&id, &balance); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%d %d\n", id, balance)
	}
}

Java

import java.sql.*;

/*
You can compile and run this example with a command like:
  javac BasicSample.java && java -cp .:~/path/to/postgresql-9.4.1208.jar BasicSample
You can download the postgres JDBC driver jar from https://jdbc.postgresql.org.
*/
public class BasicSample {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Load the postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the "bank" database.
        Connection db = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:26257/bank?sslmode=disable", "maxroach", "");

        try {
            // Insert two rows into the "accounts" table.
            db.createStatement().execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)");

            // Print out the balances.
            System.out.println("Initial balances:");
            ResultSet res = db.createStatement().executeQuery("SELECT id, balance FROM accounts");
            while (res.next()) {
                System.out.printf("\taccount %s: %s\n", res.getInt("id"), res.getInt("balance"));
            }
        } finally {
            // Close the database connection.
            db.close();
        }
    }
}

Node.js

var async = require('async');

// Require the driver.
var pg = require('pg');

// Connect to the "bank" database.
var config = {
  user: 'maxroach',
  host: 'localhost',
  database: 'bank',
  port: 26257
};

pg.connect(config, function (err, client, done) {
  // Closes communication with the database and exits.
  var finish = function () {
    done();
    process.exit();
  };

  if (err) {
    console.error('could not connect to cockroachdb', err);
    finish();
  }
  async.waterfall([
    function (next) {
      // Insert two rows into the "accounts" table.
      client.query("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250);", next);
    },
    function (results, next) {
      // Print out the balances.
      client.query('SELECT id, balance FROM accounts;', next);
    },
  ],
  function (err, results) {
    if (err) {
      console.error('error inserting into and selecting from accounts', err);
      finish();
    }

    console.log('Initial balances:');
    results.rows.forEach(function (row) {
      console.log(row);
    });

    finish();
  });
});

C++

// Build with g++ -std=c++11 basic-sample.cpp -lpq -lpqxx

#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:[email protected]:26257/bank");

    pqxx::nontransaction w(c);

    // 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;
}

Clojure

;; Import the driver.
(use 'clojure.java.jdbc)

;; Connect to the "bank" database.
(def db-spec {:subprotocol "postgresql"
              :subname "//localhost:26257/bank"
              :user "maxroach"
              :password ""})

(with-db-connection [conn db-spec]

  ;; Insert two rows into the "accounts" table.
  (insert! conn :accounts {:id 1 :balance 1000} {:id 2 :balance 250})

  ;; Print out the balances.
  (println "Initial balances:")
  (->> (query conn ["SELECT id, balance FROM accounts"])
       (map println)
       doall)

;; The database connection is automatically closed by with-db-connection.
)

PHP

<?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,
  ));

  $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);
}
?>

Rust

extern crate postgres;

use postgres::{Connection, TlsMode};

fn main() {
    let conn = Connection::connect("postgresql:[email protected]:26257/bank", TlsMode::None)
        .unwrap();

    // Insert two rows into the "accounts" table.
    conn.execute("INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)", &[])
        .unwrap();

    // Print out the balances.
    println!("Initial balances:");
    for row in &conn.query("SELECT id, balance FROM accounts", &[]).unwrap() {
        let id: i64 = row.get(0);
        let balance: i64 = row.get(1);
        println!("{} {}", id, balance);
    }
}

Step 5. Execute transactions from a client

As the maxroach user, connect again from your preferred language, but this time execute a batch of statements as an atomic transaction, where all included statements are either commited or aborted.

Because the CockroachDB transaction model requires the client to initiate retries in the case of 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. For more details, see Transaction Retries.

Python

# Import the driver.
import psycopg2
import psycopg2.errorcodes

# Connect to the cluster.
conn = psycopg2.connect(database='bank', user='maxroach', host='localhost', port=26257)


def onestmt(conn, sql):
    with conn.cursor() as cur:
        cur.execute(sql)


# Wrapper for a transaction.
# This automatically re-calls "op" with the open transaction as an argument
# as long as the database server asks for the transaction to be retried.
def run_transaction(conn, op):
    with conn:
        onestmt(conn, "SAVEPOINT cockroach_restart")
        while True:
            try:
                # Attempt the work.
                op(conn)

                # If we reach this point, commit.
                onestmt(conn, "RELEASE SAVEPOINT cockroach_restart")
                break

            except psycopg2.OperationalError as e:
                if e.pgcode != psycopg2.errorcodes.SERIALIZATION_FAILURE:
                    # A non-retryable error; report this up the call stack.
                    raise e
                # Signal the database that we'll retry.
                onestmt(conn, "ROLLBACK TO SAVEPOINT cockroach_restart")


# The transaction we want to run.
def transfer_funds(txn, frm, to, amount):
    with txn.cursor() as cur:

        # Check the current balance.
        cur.execute("SELECT balance FROM accounts WHERE id = " + str(frm))
        from_balance = cur.fetchone()[0]
        if from_balance < amount:
            raise "Insufficient funds"

        # Perform the transfer.
        cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, frm))
        cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to))


# Execute the transaction.
run_transaction(conn, lambda conn: transfer_funds(conn, 1, 2, 100))


with conn:
    with conn.cursor() as cur:
        # Check account balances.
        cur.execute("SELECT id, balance FROM accounts")
        rows = cur.fetchall()
        print('Balances after transfer:')
        for row in rows:
            print([str(cell) for cell in row])

# Close communication with the database.
conn.close()

Ruby

Coming soon.

Go

For Go, the CockroachDB retry function is in the crdb package of the CockroachDB Go client. You can clone the library into your $GOPATH as follows:

$ mkdir -p $GOPATH/github.com/cockroachdb
$ cd $GOPATH/github.com/cockroachdb
$ git clone [email protected]:cockroachdb/cockroach-go.git
package main

import (
	"database/sql"
	"fmt"
	"log"

	"github.com/cockroachdb/cockroach-go/crdb"
)

func transferFunds(tx *sql.Tx, from int, to int, amount int) error {
	// Read the balance.
	var fromBalance int
	if err := tx.QueryRow(
		"SELECT balance FROM accounts WHERE id = $1", from).Scan(&fromBalance); err != nil {
		return err
	}

	if fromBalance < amount {
		return fmt.Errorf("insufficient funds")
	}

	// Perform the transfer.
	if _, err := tx.Exec(
		"UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from); err != nil {
		return err
	}
	if _, err := tx.Exec(
		"UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to); err != nil {
		return err
	}
	return nil
}

func main() {
	db, err := sql.Open("postgres", "postgresql:[email protected]:26257/bank?sslmode=disable")
	if err != nil {
		log.Fatal("error connection to the database: ", err)
	}

	// Run a transfer in a transaction.
	err = crdb.ExecuteTx(db, func(tx *sql.Tx) error {
		return transferFunds(tx, 1 /* from acct# */, 2 /* to acct# */, 100 /* amount */)
	})
	if err == nil {
		fmt.Println("Success")
	} else {
		log.Fatalf("error: %s", err)
	}
}

Java

import java.sql.*;

/*
  You can compile and run this example with a command like:
  javac TxnSample.java && java -cp .:~/path/to/postgresql-9.4.1208.jar TxnSample
  You can download the postgres JDBC driver jar from https://jdbc.postgresql.org.
*/

class InsufficientBalanceException extends Exception {}
class AccountNotFoundException extends Exception {
    public int account;
    public AccountNotFoundException(int account) {
        this.account = account;
    }
}

// A simple interface that provides a retryable lambda expression.
interface RetryableTransaction {
    public void run(Connection conn)
        throws SQLException, InsufficientBalanceException, AccountNotFoundException;
}

public class TxnSample {
    public static RetryableTransaction transferFunds(int from, int to, int amount) {
        return new RetryableTransaction() {
            public void run(Connection conn)
                throws SQLException, InsufficientBalanceException, AccountNotFoundException {
                // Check the current balance.
                ResultSet res = conn.createStatement().executeQuery("SELECT balance FROM accounts WHERE id = " + from);
                if(!res.next()) {
                    throw new AccountNotFoundException(from);
                }
                int balance = res.getInt("balance");
                if(balance < from) {
                    throw new InsufficientBalanceException();
                }
                // Perform the transfer.
                conn.createStatement().executeUpdate("UPDATE accounts SET balance = balance - " + amount + " where id = " + from);
                conn.createStatement().executeUpdate("UPDATE accounts SET balance = balance + " + amount + " where id = " + to);
            }
        };
    }

    public static void retryTransaction(Connection conn, RetryableTransaction tx)
        throws SQLException, InsufficientBalanceException, AccountNotFoundException {
        Savepoint sp = conn.setSavepoint("cockroach_restart");
        while(true) {
            try {
                // Attempt the transaction.
                tx.run(conn);

                // If we reach this point, commit the transaction,
                // which implicitly releases the savepoint.
                conn.commit();
                break;
            } catch(SQLException e) {
                // Check if the error code indicates a SERIALIZATION_FAILURE.
                if(e.getErrorCode() == 40001) {
                    // Signal the database that we will attempt a retry.
                    conn.rollback(sp);
                }
                // This is a not a serialization failure, pass it up the chain.
                throw e;
            }
        }
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // Load the postgres JDBC driver.
        Class.forName("org.postgresql.Driver");

        // Connect to the "bank" database.
        Connection db = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:26257/bank?sslmode=disable", "maxroach", "");
            try {
                // We need to turn off autocommit mode to allow for
                // multi-statement transactions.
                db.setAutoCommit(false);
                // Perform the transfer. This assumes the table has
                // already been set up as in the "Build a Test App"
                // tutorial.
                RetryableTransaction transfer = transferFunds(1, 2, 100);
                retryTransaction(db, transfer);
                // Check balances after transfer.
                ResultSet res = db.createStatement().executeQuery("SELECT id, balance FROM accounts");
                while (res.next()) {
                    System.out.printf("\taccount %s: %s\n", res.getInt("id"), res.getInt("balance"));
                }
            } catch(InsufficientBalanceException e) {
                System.out.println("Insufficient balance");
            } catch(AccountNotFoundException e) {
                System.out.println("No users in the table with id " + e.account);
            } catch(SQLException e) {
                System.out.println("SQLException encountered:" + e);
            } finally {
                // Close the database connection.
                db.close();
            }
    }
}

Node.js

var async = require('async');

// Require the driver.
var pg = require('pg');

// Connect to the cluster.
var config = {
  user: 'maxroach',
  host: 'localhost',
  database: 'bank',
  port: 26257
};

// Wrapper for a transaction.
// This automatically re-calls "op" with the client as an argument as
// long as the database server asks for the transaction to be retried.
function txnWrapper(client, op, next) {
  client.query('BEGIN; SAVEPOINT cockroach_restart', function (err) {
    if (err) {
      return next(err);
    }

    var released = false;
    async.doWhilst(function (done) {
      var handleError = function (err) {
        // If we got an error, see if it's a retryable one and, if so, restart.
        if (err.code === '40001') {
          // Signal the database that we'll retry.
          return client.query('ROLLBACK TO SAVEPOINT cockroach_restart', done);
        }
        // A non-retryable error; break out of the doWhilst with an error.
        return done(err);
      };

      // Attempt the work.
      op(client, function (err) {
        if (err) {
          return handleError(err);
        }

        // If we reach this point, release and commit.
        client.query('RELEASE SAVEPOINT cockroach_restart', function (err) {
          if (err) {
            return handleError(err);
          }
          released = true;
          return done();
        });
      });
    },
    function () {
      return !released;
    },
    function (err) {
      if (err) {
        client.query('ROLLBACK', function () {
          next(err);
        });
      } else {
        client.query('COMMIT', next);
      }
    });
  });
}

// The transaction we want to run.
function transferFunds(client, from, to, amount, next) {
  // Check the current balance.
  client.query('SELECT balance FROM accounts WHERE id = $1', [from], function (err, results) {
    if (err) {
      return next(err);
    } else if (results.rows.length === 0) {
      return next(new Error('account not found in table'));
    }

    var acctBal = results.rows[0].balance;
    if (acctBal >= amount) {
      // Perform the transfer.
      async.series([
        function (next) {
          // Subtract amount from account 1.
          client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from], next);
        },
        function (next) {
          // Add amount to account 2.
          client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to], next);
        },
      ], next);
    } else {
      next(new Error('insufficient funds'));
    }
  });
}

pg.connect(config, function (err, client, done) {
  // Closes communication with the database and exits.
  var finish = function () {
    done();
    process.exit();
  };

  if (err) {
    console.error('could not connect to cockroachdb', err);
    finish();
  }

  // Execute the transaction.
  txnWrapper(client,
  function (client, next) {
    transferFunds(client, 1, 2, 100, next);
  },
  function (err, results) {
    if (err) {
      console.error('error performing transaction', err);
      finish();
    }

    // Check account balances after the transaction.
    client.query('SELECT id, balance FROM accounts', function (err, results) {
      if (err) {
        console.error('error querying accounts', err);
        finish();
      }

      console.log('Balances after transfer:');
      results.rows.forEach(function (row) {
        console.log(row);
      });

      finish();
    });
  });
});

C++

// Build with g++ -std=c++11 txn-sample.cpp -lpq -lpqxx

#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::pqxx_exception& 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 retriable errors.
      if (string(e.base().what()).find("restart transaction:") == string::npos) {
        throw;
      }
    }
  }
  tx.commit();
}

int main() {
  try {
    pqxx::connection c("postgresql:[email protected]:26257/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;
}

Clojure

;; Import the driver.
(use 'clojure.java.jdbc)

;; Connect to the cluster.
(def db-spec {:subprotocol "postgresql"
              :subname "//localhost:26257/bank"
              :user "maxroach"
              :password ""})


;; Wrapper for a transaction.
;; This automatically invokes the body again
;; as long as the database server asks the transaction to be retried.
(defmacro with-txn-retry
  [c conn & body]
  `(with-db-transaction [c conn]
     (loop []
       (let [res# (try (let [r# ~@body]
                         {:ok r#})
                       (catch org.postgresql.util.PSQLException e#
                         (if (re-find #"restart transaction" (.getMessage e#))
                           {:retry true}
                           (throw e#))))]
         (if (:retry res#) (recur) (:ok res#))
         ))))

;; The transaction we want to run.
(defn transferFunds
  [txn from to amount]

  ;; Check the current balance.
  (let [fromBalance (->> (query txn ["SELECT balance FROM accounts WHERE id = ?" from])
                         (mapv :balance)
                         (first))]
    (when (< fromBalance amount)
      (throw (Exception. "Insufficient funds"))))

  ;; Perform the transfer.
  (execute! txn [(str "UPDATE accounts SET balance = balance - " amount " WHERE id = " from)])
  (execute! txn [(str "UPDATE accounts SET balance = balance + " amount " WHERE id = " to)])
  )
      

(with-db-connection [conn db-spec]
  ;; Execute the transaction.
  (with-txn-retry [c conn]
     (transferFunds c 1 2 100))

  (println "Balances after transfer:")
  (->> (query conn ["SELECT id, balance FROM accounts"])
       (map println)
       (doall))
  )
  
  


PHP

<?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);
}
?>

Rust

extern crate postgres;

use postgres::{Connection, TlsMode, Result};
use postgres::transaction::Transaction;
use postgres::error::{Error, SqlState};

/// Runs op 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.
fn execute_txn<T, F>(conn: &Connection, mut op: F) -> Result<T>
    where F: FnMut(&Transaction) -> Result<T> 
{
    let txn = try!(conn.transaction());
    let res: Result<T>;
    loop {
        let sp = try!(txn.savepoint("cockroach_restart"));
        match op(&sp).and_then(|t| sp.commit().map(|_| t)) {
            Err(Error::Db(ref e)) if e.code == SqlState::SerializationFailure => continue,
            r => res = r,
        }
        break
    }
    res.and_then(|t| txn.commit().map(|_| t))
}

fn transfer_funds(txn: &Transaction, from: i64, to: i64, amount: i64) -> Result<()> {
    // Read the balance.
    let from_balance: i64 = try!(txn.query("SELECT balance FROM accounts WHERE id = $1", &[&from]))
        .get(0)
        .get(0);
        
    assert!(from_balance >= amount);

    // Perform the transfer.
    try!(txn.execute("UPDATE accounts SET balance = balance - $1 WHERE id = $2",
                     &[&amount, &from]));
    try!(txn.execute("UPDATE accounts SET balance = balance + $1 WHERE id = $2",
                     &[&amount, &to]));
    Ok(())
}

fn main() {
    let conn = Connection::connect("postgresql:[email protected]:26257/bank", TlsMode::None)
        .unwrap();

    // Run a transfer in a transaction.
    execute_txn(&conn, |txn| transfer_funds(txn, 1, 2, 100))
        .unwrap();
    
    // Check account balances after the transaction.
    for row in &conn.query("SELECT id, balance FROM accounts", &[]).unwrap() {
        let id: i64 = row.get(0);
        let balance: i64 = row.get(1);
        println!("{} {}", id, balance);
    }
}

What’s Next?

Use a local cluster to explore the following core CockroachDB features:



Yes No