Saturday, October 31, 2015

Clojure API with Yesql, Migrations and More (Part 2.)

In the previous article, we started working on kashmir, a Clojure project that interacts with a database, and exposes the data through a JSON HTTP endpoint.
In this post we'll seed the database with some test data, add yesql as our DB communication tool, at the end we will cover testing.

Adding Seed Data

Use this commit as your starting point for this exercise. Rebuild your database by running make build-db to make sure you have no records in the tables. Create a new file in resources/seeds.sql and add the following content to it:

INSERT INTO bands(name) VALUES ('The Beatles');
INSERT INTO bands(name) VALUES ('The Doors');

INSERT INTO members(first_name, last_name, email)
VALUES ('John', 'Lennon', 'jlennon@beatles.com');
INSERT INTO members(first_name, last_name, email)
VALUES ('Paul', 'McCartney', 'pmccartney@beatles.com');
INSERT INTO members(first_name, last_name, email)
VALUES ('George', 'Harrison', 'gharrison@beatles.com');
INSERT INTO members(first_name, last_name, email)
VALUES ('Ringo', 'Starr', 'rstarr@beatles.com');

INSERT INTO bands_members(band_id, member_id) VALUES(1, 1);
INSERT INTO bands_members(band_id, member_id) VALUES(1, 2);
INSERT INTO bands_members(band_id, member_id) VALUES(1, 3);
INSERT INTO bands_members(band_id, member_id) VALUES(1, 4);
This will create 2 band and 4 member records. It will also associate the members of The Beatles with their band record. We will insert these records through Postgres' command line tool. Let's add this to our build-db target in our Makefile:
  ...
build-db:
 dropdb --if-exists --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
 createdb --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
 lein clj-sql-up migrate
 psql -U $(USER) -d $(DBNAME) --file resources/seeds.sql > /dev/null
We added < /dev/null to this line, we are not interested seeing how many records got inserted into the tables. When you run make build-db you should have the seed data inserted into your database.
(Commit point.)

Talking to the Database with Yesql

The natural way to communicate with a database in Clojure is using java.jdbc. However, the spaghetti SQL is hard to understand, and mixing of Clojure code with SQL could make it a mess very quickly. I found the fantastic tool yesql a few weeks ago and it was just what I needed: an easy way to separate SQL from Clojure. Let's add yesql and the Postgres jdbc driver to the project by modifying the project.clj file this way:

(defproject kashmir "0.1.0-SNAPSHOT"
  ...
  :dependencies [[org.clojure/clojure "1.7.0"]
                 [org.postgresql/postgresql "9.4-1201-jdbc41"]
                 [yesql "0.5.1"]]
  ...)
Create a new directory called "sql" under "src/kashmir". Create a new SQL file in this directory called "data.sql". Add these two queries to it:
-- name: find-member-by-id
-- Find the member with the given ID(s).
SELECT *
FROM members
WHERE id = :id

-- name: count-members
-- Counts the number of members
SELECT count(*) AS count
FROM members
The line in this SQL file that begins with -- name: has special significance. Yesql will create data access functions with the name you define there.
Add a new Clojure file under "src/kashmir" called "data.clj", this file will hold the data access functions. Add the following code to it:
(ns kashmir.data
  (:require [yesql.core :refer [defqueries]]
            [clojure.java.jdbc :as jdbc]))

(def db-spec {:classname "org.postgresql.Driver"
              :subprotocol "postgresql"
              :subname "//localhost:5432/kashmir"
              :user "kashmir_user"
              :password "password1"})

(defqueries "kashmir/sql/data.sql"
            {:connection db-spec})
I am a bit unhappy with duplicating the Postgres connection information here, I'll leave you to set up the DB connection in the project.clj file.
Fire up the REPL to see if this works (you can find my input highlighted below):
% lein repl
nREPL server started on port 55527 on host 127.0.0.1 - nrepl://127.0.0.1:55527
REPL-y 0.3.7, nREPL 0.2.10
Clojure 1.7.0
Java HotSpot(TM) 64-Bit Server VM 1.8.0_60-b27
    Docs: (doc function-name-here)
          (find-doc "part-of-name-here")
  Source: (source function-name-here)
 Javadoc: (javadoc java-object-or-class-here)
    Exit: Control+D or (exit) or (quit)
 Results: Stored in vars *1, *2, *3, an exception in *e

kashmir.core=> (require '[kashmir.data :refer :all])
nil
kashmir.core=> (count-members)
({:count 4})
kashmir.core=> (find-member-by-id {:id 2})
({:id 2, :first_name "Paul", :last_name "McCartney", :email "pmccartney@beatles.com", :created_at #inst "2015-10-14T19:59:48.905474000-00:00"})
kashmir.core=>
Fantastic! We can talk to the database through yesql based on the SQL scripts defined in "src/kashmir/sql/data.sql" file.
(Commit point.)

Adding Tests

Although our application does not have much logic just yet, I'd like to show you how you could start writing automated tests. Create a new test file under "test/kashmir/data_test.clj". Add the following code to it:

(ns kashmir.data-test
  (:require [clojure.test :refer :all]
            [kashmir.data :refer :all]))

(deftest count-members-test
  (testing "there are 4 members"
    (is (= 4 (-> (count-members) first :count)))))
Remove the failing test in "test/kashmir/core_test.clj" file:
(ns kashmir.core-test
  (:require [clojure.test :refer :all]
            [kashmir.core :refer :all]))
Run the tests by invoking lein test and you should see the one and only test passing:
% lein test

lein test kashmir.data-test

Ran 1 tests containing 1 assertions.
0 failures, 0 errors.
(Commit point.)

Finding a Member

Yesql needs a hash even when a record is looked up by an ID. This is how you invoke the yesql generated function: (find-member-by-id {:id 2}). We should keep the data access interface unaware of this implementation detail. Let's find a member by an ID this way: (find-member 2). Write the test for this in test/kashmir/data_test.clj:

...

(deftest find-member-by-id-test
  (testing "finds PM with id 2"
    (is (= "Paul" (-> (find-member 2) first :first_name)))))
This is the code implementation of it in "src/kashmir/data.clj":
...

(defn find-member [id]
  (find-member-by-id {:id id}))
Both of the tests should pass now.
(Commit point.)

Adding a Member

Reading data with yesql is simple, but adding records and testing that over and over can be more challenging. The database has to be reset to its original state after each test run. You have two options here:

  • truncate all the tables after each test,
  • roll back the INSERT transactions.
The way you can truncate all the tables is a blog post in itself. Unfortunately the Clojure community has not created a DatabaseCleaner project we love so much in the Ruby world just yet. Let's use the roll-back feature of the INSERT transaction for our tests in the examples.

When you create a member, you need to associate that member with a band. In fact, a member can not be added to the database without a band. A hash with all the member data and the band name will be the arguments to this create function.
Let's write the test first in the "test/kashmir/data_test.clj" file:

...
(deftest create-member-test
  (testing "adds a member to the DB"
    (let [member {:first_name "Jim" :last_name "Morrison" :email "jmorrison@doors.com"}]
      (is (= 1 (create-member! member "The Doors"))))))
Let's write the simplest code that could possibly work. First, we need to add the INSERT SQL statements to "src/kashmir/data.sql". This is what those look like:
...

-- name: find-band-by-name
-- Finds a band record based on the provided name
SELECT *
FROM bands
WHERE name = :name

-- name: create-member-raw!
-- Adds a new member with the bands_members association
WITH inserted AS (
  INSERT INTO members (first_name, last_name, email)
  VALUES (:first_name, :last_name, :email)
  RETURNING id
)
INSERT INTO bands_members (member_id, band_id)
SELECT inserted.id, :band_id FROM inserted
As I was writing this blog post, I researched how I could insert records into different tables with one SQL statement. Using stored procedure or function would be an easy choice, but that's too heavy for what we need. I found this blog post by Rob Conery. He shows how CTEs (Common Table Expressions) can be used to insert and reuse the created record in a subsequent insert. That's what you see in the second SQL command. By using this solution, the Clojure code will be small, as the database write functionality is delegated to PostgreSQL.
This is what the data logic will look like in the "src/kashmir/data.clj" file:
...

(defn create-member!
  ([member band-name]
    (let [band-id (-> (find-band-by-name {:name band-name})
                       first
                       :id)]
        (create-member-raw! (conj member {:band_id band-id})))))
The "-raw" postfix was used for the function that gets generated by yesql. We could have created an alias, but I liked this kind of naming-convention.
When you run the test it won't error out, but one of the tests will fail. It has more than 4 total records in the members table. Absolutely, the database was not restored to its default state. Let's take care of that! We will insert the record, but we will roll back the transaction once the test is complete, leaving the database in it's original, default state.
Add/modify the highlighted lines in your "src/kashmir/data.clj" file:
...

(defn create-member!
  ([member band-name]
    (jdbc/with-db-transaction [tx db-spec]
      (create-member! member band-name tx)))
  ([member band-name tx]
    (let [band-id (-> (find-band-by-name {:name band-name})
                       first
                       :id)]
        (create-member-raw! (conj member {:band_id band-id})
                            {:connection tx}))))
And finally, initialize and roll back the transaction from the test. Change the highlighted lines in "test/kashmir/data_test.clj" this way:
(ns kashmir.data-test
  (:require [clojure.test :refer :all]
            [kashmir.data :refer :all]
            [clojure.java.jdbc :as jdbc]))
  ...

(deftest create-member-test
  (jdbc/with-db-transaction [tx db-spec]
    (jdbc/db-set-rollback-only! tx)
      (testing "adds a member to the DB"
        (let [member {:first_name "Jim" :last_name "Morrison" :email "jmorrison@doors.com"}]
          (is (= 1 (create-member! member "The Doors" tx)))))))
Rebuild your database and run your tests. You should see the 0 failures, 0 errors. message. Do it many times, the tests should always pass.
(Commit point.)

One Last Refactoring

I am unhappy with the create-member! function. The way we are looking up the band by its name is inelegant, I feel we could do better. Since we have one band record by name, when we call find-band-by-name, we should get back one single hash and not a lazy-seq with a hash in it. Let's refactor to that! First, we'll renamed the yesql generated function to find-band-by-name-raw in the "src/kashmir/sql/data.sql" file:

...

-- name: find-band-by-name-raw
-- Finds a band record based on the provided name
SELECT *
FROM bands
WHERE name = :name
Let's refactor the actual code like this in "src/kashmir/data.clj":
...

(defn find-band-by-name [name]
  (first (find-band-by-name-raw {:name name})))

(defn create-member!
  ([member band-name]
    (jdbc/with-db-transaction [tx db-spec]
      (create-member! member band-name tx)))
  ([member band-name tx]
    (let [band-id (:id (find-band-by-name band-name))]
        (create-member-raw! (conj member {:band_id band-id})
                            {:connection tx}))))
I rebuilt the db, ran the tests and everything passed.
(Commit point.)

You could say this is only the "happy path", what if the band name is incorrect and no band will be found. This will blow up somewhere. Absolutely! You need to do exception handling and error checking. I wanted to keep my examples simple, so others coming to Clojure can benefit from the simplified code.

This last refactoring concludes the second part of the series. In the final session we will add logging to jdbc to monitor how yesql communicates with the database. We will also expose the data as JSON through an HTTP endpoint.

Clojure API with Yesql, Migrations and More (Part 1.)

I've found endless books and articles explaining core ideas and the building blocks of the Clojure programming language. They show you how to use the different data structures, they have good examples for little tasks, like reading and parsing a CSV file, but books or articles that walk you through an example of building a comprehensive solution is hard to find.
I always liked writings that showed me how to build an app. I would learn many aspects of a language, get familiar with tools, and most of all, I would build something that could serve as foundation for my future projects.
I am planning to do just that with this series of blog posts. I'd like to show you how to:

  • Set up your database environment with scripts
  • Manage database changes through migrations
  • Test the different components
  • Stub out function calls you don't need for testing
  • Add logging to monitor the database communication
  • And all this in Clojure!
By the end of these blog posts you will be able to expose database through Clojure libraries as JSON data with HTTP endpoints.

I have following presumptions:

  • You have Clojure installed (I have version 1.7.0 at the time of writing)
  • We'll use PostgreSQL (mine is 9.4.4)
  • I am using OSX (10.10.5)
The name of the app is "kashmir", you can find the final solution in this public repo. I will link specific commit points to the blog posts, this way you can join this tutorial at any point you want. Let's dive in!

The Data Model

The data model is simple, it has only 3 tables. The members table lists the various band members, the bands table lists all the bands those members belong to, and the bands_members table is used to map the members to their bands.
This is what it looks like:

Creating the Database User

I use the excellent pgcli tool as my command line interface for Postgres. It has code completion, table name suggestion features, it's your psql tool on steorid. If you don't have it, grab it through homebrew. Create a DB user called "kashmir_user" and allow this user to create DBs. This is how you do it in the command line, all the inputs are highlighted:

% pgcli postgres
Version: 0.19.1
Chat: https://gitter.im/dbcli/pgcli
Mail: https://groups.google.com/forum/#!forum/pgcli
Home: http://pgcli.com
postgres> CREATE USER kashmir_user PASSWORD 'password1';
CREATE ROLE
Command Time: 0.000s
Format Time: 0.000s
postgres> ALTER USER kashmir_user CREATEDB;
ALTER ROLE
Command Time: 0.000s
Format Time: 0.000s
postgres>

Initializing the Project

Generate the new project skeleton by runnig the lein new app kashmir command in the terminal. You should have a skeleton app project that looks like this. When you run lein run, you see "Hello, World!", and when you run the tests you see the 1 failure:

% lein test

lein test kashmir.core-test

lein test :only kashmir.core-test/a-test

FAIL in (a-test) (core_test.clj:7)
FIXME, I fail.
expected: (= 0 1)
  actual: (not (= 0 1))

Ran 1 tests containing 1 assertions.
1 failures, 0 errors.
Tests failed.

Creating the Database

Database drop and create operations should be scripted. You can use rake db:drop and rake db:create in Rails, we should have something similar here. You can use the Postgres command line tools to create and drop databases with the createdb and dropdb commands. The --if-exists switch helps you when you're running it for the first time, the command won't error out if the database does not exist.
The easiest way to create executable tasks is with a Makefile. Create a new file called Makefile in your project root and add this to it:

DBNAME=kashmir
USER=kashmir_user
PORT=5432
HOST=localhost

PGPASSWORD=password1

# Builds the DB by dropping and recreating it
build-db:
 dropdb --if-exists --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
 createdb --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
We set up variables in the Makefile, it will be easy to change these values later, you are also adhering to good DRY principles.
Run the Make target by typing the command make build-db in the terminal. You can run this as many times as you want, it will drop and recreate the empty database for you.
(Commit point.)

Running Migrations

The best way to implement changes in a database is through reversible migrations. I usually use the great clj-sql-up migration tool for that. Let's add it to the project.clj file:

(defproject kashmir "0.1.0-SNAPSHOT"
  ...
  :clj-sql-up {:database "jdbc:postgresql://kashmir_user:password@localhost:5432/kashmir"
               :deps [[org.postgresql/postgresql "9.4-1201-jdbc4"]]}
  :plugins  [[clj-sql-up "0.3.7"]])
Run the command lein clj-sql-up create create-members to generate your first migration. This should create a new file in the "migrations" directory. Open up that file and add your migration SQL to it:
(defn up []
    ["CREATE TABLE members(id SERIAL PRIMARY KEY,
                           first_name varchar(50) NOT NULL,
                           last_name varchar(50) NOT NULL,
                           email varchar(50) NOT NULL,
                           created_at timestamp NOT NULL default CURRENT_TIMESTAMP)"
     "CREATE INDEX idx_members_id ON members(id)"
     "CREATE UNIQUE INDEX idx_email_unique ON members(email)"])

(defn down []
  ["DROP TABLE members"])
Test your SQL by running lein clj-sql-up migrate in the terminal. I would recommend looking at the database to make sure the first table, "members" got created properly. Open up pgcli and run \dt from the pgcli prompt. You should see two tables listed there:
  • clj_sql_migrations
  • members
The table "clj_sql_migrations" is used to track the actual version of your database, it's the metadata for clj-sql-up to run the migrations. Let's add the "bands" and "bands_members" tables as well, create a new migration file with the clj-sql-up generator: lein clj-sql-up create create-bands. Open up the migrations/*-create-bands.clj file and add this SQL:
(defn up []
    ["CREATE TABLE bands(id SERIAL PRIMARY KEY,
                         name varchar(50) NOT NULL,
                         created_at timestamp NOT NULL default CURRENT_TIMESTAMP)"
     "CREATE INDEX index_bands_id ON bands(id)"
     "CREATE TABLE bands_members(id SERIAL PRIMARY KEY,
                                 band_id INTEGER REFERENCES bands (id),
                                 member_id INTEGER REFERENCES members (id),
                                 created_at timestamp NOT NULL default CURRENT_TIMESTAMP)"])

(defn down []
  ["DROP TABLE bands"]
  ["DROP TABLE bands_members"])
We should be running the migrations when we drop and rebuild the database. Change your Makefile's "build-db" target like this:
  ...
# Builds the DB by dropping and recreating it
build-db:
  dropdb --if-exists --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
  createdb --username $(USER) $(DBNAME) -h $(HOST) -p $(PORT)
  lein clj-sql-up migrate
Now when you run make build-db in your terminal, you should see the database recreated by dropping it first, creating it and running the migration at the end.
(Commit point.)

In the next post we'll add seed data to the database for testing purposes, we will also use the excellent Yesql library to communicate with the database.

Friday, October 2, 2015

The $18 Web Design

My first employer in Minneapolis charged 10s of thousands of dollars for a brochureware website that only promoted a small business in the early 2000s. It became harder and harder to acquire new business, and the price point eventually dropped significantly, but still, it was a pretty good business to be in.

Then in August 2011 Twitter open sourced bootstrap, which turned the entire web to look the same. It was a huge leap in the right direction, but every other website or internal app looked very much alike.

More and more UI engineers cranked out "customized" bootstrap apps. Engineers familiar with bootstrap were able to modify and tweak the design. I even built my own resume on one of those bootstrap designs.

A couple of weeks ago a good friend of mine pinged me about help with his app prototype. He even picked out the template for himself. He sent it over to me as a zip file, I extracted it and my jaw dropped. The 566 MB of content I found in it was amazing.

It had:

  • 4 layouts
  • 5 dashboard mockups with sample graphs
  • 7 different graphs
  • an email template with all messages, view and write email templates
  • metrics dashboard with 6 different data representations
  • all sorts of UI widgets like zoomable maps
  • form widgets with wizards, file uploads
  • 19 user profile views
  • 2 login, forgot password and different error pages
  • a code editor, a timeline view, tree and chat view
  • different UI elements like panels, buttons, tabs and badges
  • 4 data table templates
  • full e-commerce design for products, orders, order detail and payment forms
  • 3 galleries
  • 2 menu options
  • a public site design, this way your company can have a marketing site aligned with the app
  • 5 full and starter templates (angular.js, ASP.NET MVC, meteor.js, static HTML 5 template, Rails 4 templates)
  • 3 CSS pre-processors (SASS, SCSS, LESS)
  • all the templates in PSD in case you want to further tweak it yourself

With a template like that you can pretty much do everything you want. An e-commerce app? Sure! A data analytics application? Absolutely. I've tried using my UI chops before, but none of my attempts were remotely close to what a template like this can offer.

And the best part? This template does not cost $1000. Not even $500. You can have it all for 18 dollars. Yes, for the cost of your lunch you can put your ideas in motion.