Our users table is a simple one, which only requires three values from us at the time of inserting: username, email, and password (the others, created_at, updated_at, and user_id are autogenerated for us). As such, the SQL is straight forward. Open the users.sql file and enter the following:

-- name: insert-user<!
-- Inserts a new user into the Users table
-- Expects :username, :email, and :password
INSERT INTO users (username, email, password)
VALUES (:username, :email, :password)

The preceding code looks pretty standard. We're inserting a new row into the users table, and are providing each the username, email, and password columns a value. In the VALUES clause, we are specifying a :username, :email, and :password key, each of which will be bound to the map passed to the generated function.

SQL aside, we also used the name: annotation, which YeSQL uses for the name of the generated function, and a docstring, which YeSQL also uses to document the generated function. However, there's another interesting syntax, and that's the <! suffix we gave the name.

There are two special suffixes we can use in the names of our YeSQL queries. These suffixes tell YeSQL to return an appropriate result when mutable queries (that is, queries that insert/update/delete/alter the database) are executed. These suffixes are as follows:

These two special characters are convenient when we want to report appropriate outcomes to the client.

Now that we have the SQL file with the insert statement, we need to give our user_model.clj file some meat. Open up the user_model.clj file and add the following code:

(ns hipstr.models.user-model
  (:require [yesql.core :refer [defqueries]]))

(def db-spec {:classname "org.postgresql.Driver"  ;#1
              :subprotocol "postgresql"
              :subname     "//localhost/postgres"
              :user        "hipstr"
              :password    "p455w0rd"})

(defqueries "hipstr/models/users.sql"
            {:connection db-spec})    ; #2

The preceding code is all we need for YeSQL to generate a series of functions out of all the SQL we write in the users.sql file. At #1 we are defining our database connection. This should look pretty familiar to the JDBC connection maps that one provides when using vanilla clojure.jdbc (which is what YeSQL uses under the hood—there's no magic there).

Code at #2 is, however, where the magic happens. At #2 we tell YeSQL," Hey, go look at the users.sql file and generate a function for each query, and for each query use this database connection." In essence, our SQL becomes our data model, and the user_model namespace is a thin shim connecting our business to the data model.

Passing the connection to defqueries is a convenience and not necessary. If we left it out, however, we would have to provide the connection anytime we called the generated function, for example (insert-user<! {:username…} {db-spec}). This gets annoying, and it creates unnecessary clutter in our code.

We can do a sanity check using the REPL to ensure that the preceding code actually works:

# lein repl
hipstr.repl=> (load "models/user_model")
hipstr.repl=> (ns hipstr.models.user-model)
hipstr.repl=> (:doc (meta insert-user<!))
>> "Inserts a new user into the Users table
Expects :username, :email, and :password"
hipstr.repl=> (insert-user<! {:username "test" :email "[email protected]" :password "abc123"})
>> {:updated_at #inst "2014-12-03T07:48:53...." :created_at #inst "2014-12-03T07:48:53..." :password "abc123" :email "[email protected]" :username "test" :user_id 1}

With the knowledge that it works, we can complete our Signup process.

As it stands, the only thing we actually have to do is adjust our /signup POST route. Open the hipstr.routes.home namespace and perform the following steps:

That's all we need to do. We simply pass our user map to the function generated by YeSQL and our newly signed-up user is saved to the database. This works because our user form's field names map perfectly to the keys expected by the generated insert-user<! function. However, there is one more thing we'll want to do with our data before we save it.

We are good developers. We care (somewhat) about security (I hope). As such, we'll want to encrypt the password before we store it in the database. This is not something we want to worry about every time we add a new user, so we'll add a small function in our hipstr.models.user-model namespace, which will encrypt the password before we throw it in the database.

The easiest way to encrypt a password is to use crypto-password, a tiny encryption library written by James Reeves (yep, him again). To encrypt our user's password, do the following:

We are now storing a fully encrypted password in our database, and we barely had to do a thing. Go ahead and restart hipstr, and start accepting billions of users.