# Build a Clojure App with CockroachDB

This tutorial shows you how build a simple Clojure application with CockroachDB using leiningen and a PostgreSQL-compatible driver.

We have tested the Clojure java.jdbc driver in conjunction with the PostgreSQL JDBC driver enough to claim beta-level support, so that combination is featured here. If you encounter problems, please open an issue with details to help us make progress toward full support.

## Before you begin

Make sure you have already installed CockroachDB.

## Step 1. Install `leiningen`

Install the Clojure `lein` utility as described in its official documentation.

## Step 2. Start a single-node cluster

For the purpose of this tutorial, you need only one CockroachDB node running in insecure mode:

    $ cockroach start \
    --insecure \
    --store=hello-1 \
    --listen-addr=localhost

## Step 3. Create a user

In a new terminal, as the `root` user, use the `cockroach user` command to create a new user, `maxroach`.

    $ cockroach user set maxroach --insecure

## Step 4. Create a database and grant privileges

As the `root` user, use the built-in SQL client to create a `bank` database.

    $ cockroach sql --insecure -e 'CREATE DATABASE bank'

Then grant privileges to the `maxroach` user.

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

## Step 5. 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 --insecure \
    --database=bank \
    --user=maxroach \
    -e 'CREATE TABLE accounts (id INT PRIMARY KEY, balance INT)'

## Step 6. Run the Clojure 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.

### Create a basic Clojure/JDBC project

1. Create a new directory `myapp`.

2. Create a file `myapp/project.clj` and populate it with the following code, or download it directly. Be sure to place the file in the subdirectory `src/test` in your project.

       (defproject test "0.1"
       :description "CockroachDB test"
       :url "http://cockroachlabs.com/"
       :dependencies [[org.clojure/clojure "1.8.0"]
                    [org.clojure/java.jdbc "0.6.1"]
                    [org.postgresql/postgresql "9.4.1211"]]
       :main test.test)

3. Create a file `myapp/src/test/util.clj` and populate it with the code from this file. Be sure to place the file in the subdirectory `src/test` in your project.

### 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.

Create a file `myapp/src/test/test.clj` and copy the code below to it, or download it directly. Be sure to rename this file to `test.clj` in the subdirectory `src/test` in your project.

    (ns test.test
      (:require [clojure.java.jdbc :as j]
                [test.util :as util]))

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

    (defn test-basic []
      ;; Connect to the cluster and run the code below with
      ;; the connection object bound to 'conn'.
      (j/with-db-connection [conn db-spec]

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

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

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

    (defn -main [& args]
      (test-basic))

Run with:

    lein run

### 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.

Copy the code below to `myapp/src/test/test.clj` or download it directly. Again, preserve the file name `test.clj`.

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.

    (ns test.test
      (:require [clojure.java.jdbc :as j]
                [test.util :as util]))

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

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

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

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

    (defn test-txn []
      ;; Connect to the cluster and run the code below with
      ;; the connection object bound to 'conn'.
      (j/with-db-connection [conn db-spec]

        ;; Execute the transaction within an automatic retry block;
        ;; the transaction object is bound to 'txn'.
        (util/with-txn-retry [txn conn]
          (transferFunds txn 1 2 100))

        ;; Execute a query outside of an automatic retry block.
        (println "Balances after transfer:")
        (->> (j/query conn ["SELECT id, balance FROM accounts"])
             (map println)
             (doall))))

    (defn -main [& args]
      (test-txn))

Run with:

    lein run

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

    $ 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 Clojure java.jdbc driver.

You might also be interested in using a local cluster to explore the following CockroachDB benefits:

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