Database Access

Configuring the Database

Luminus defaults to using Migratus for database migrations and Yesql for database interaction. The migrations and a default connection will be setup when using a database profile such as +postgres.

Configuring Migrations

We first have to set the connection strings for our database in profiles.clj. By default two profiles are generated for development and testing respectively:

{:profiles/dev  {:env {:database-url "jdbc:postgresql://localhost/my_app_dev?user=db_user&password=db_password"}}
 :profiles/test {:env {:database-url "jdbc:postgresql://localhost/myapp_test?user=db_user&password=db_password"}}}

Then we can create SQL scripts to migrate the database schema, and to roll it back. These are applied using the numeric order of the ids. Conventionally the current date is used to prefix the filename. The files are expected to be present under the resources/migrations folder. The template will generate sample migration files for the users table.

resources/migrations/20150720004935-add-users-table.down.sql
resources/migrations/20150720004935-add-users-table.up.sql

With the above setup we can run the migrations as follows:

lein run migrate

Applied migration can then be rolled back with:

lein run rollback

Additional migration files can be generated using the migratus plugin as follows:

lein migratus create add-guestbook-table

Please refer to the Database Migrations section for more details.

Setting up the database connection

The connection settings are found in the <app>.db.core namespace of the application. By default the database connection is expected to be provided as the DATABASE_URL environment variable.

The connection is stored in an atom called conn and it's managed by the conman library.

By default the connection is set to a dynamic variable containing an atom. The connection is initialized by calling the conman/connect! function with the connection atom and a database specification map. The connect! function will create a pooled JDBC connection using the HikariCP library. The connection can be terminated by calling the disconnect! function.

(ns myapp.db.core
  (:require
    ...
    [config.core :refer [env]]
    [conman.core :as conman]
    [mount.core :refer [defstate]]))
            
(def pool-spec
  {:adapter    :postgresql
   :init-size  1
   :min-idle   1
   :max-idle   4
   :max-active 32}) 

(defn connect! []
  (let [conn (atom nil)]
    (conman/connect!
      conn
      (assoc
        pool-spec
        :jdbc-url (env :database-url)))
    conn))

(defn disconnect! [conn]
  (conman/disconnect! conn))

(defstate ^:dynamic *db*
          :start (connect!)
          :stop (disconnect! *db*))

The connection is specified using the defstate macro. The connect! function is called when the *db* component enters the :start state and the disconnect! function is called when it enters the :stop state.

The lifecycle of the *db* component is managed by the mount library as discussed in the Managing Component Lifecycle section.

The <app>.handler/init and <app>.handler/destroy functions will initialize and tear down any components defined using defstate by calling (mount/start) and (mount/stop) respectively. This ensures that the connection is available when the server starts up and that it's cleaned up on server shutdown.

When working with multiple databases, a separate atom is required to track each database connection.

Working with Yesql

Yesql uses plain SQL to define the queries. The comments are used to supply the function name and a doc string for each query.

Conventionally the queries are placed in the resources/sql/queries.sql file. However, once your application grows you may consider splitting the queries into multiple files.

The format for the file is (<name tag> [docstring comments] <the query>)*. Let's take a look at a simple query below:

--name: create-user!
-- creates a new user record
INSERT INTO users
(id, first_name, last_name, email, pass)
VALUES (:id, :first_name, :last_name, :email, :pass)

As we can see the query is written using plain SQL and the parameters are denoted by prefixing the parameter name with a colon.

The query functions are generated by calling the the conman/bind-connection macro. The macro accepts the connection atom and one or more query files such as the one described above.

(conman/bind-connection conn "sql/queries.sql")

Once bind-connection is run the query we defined above will be mapped to myapp.db.core/create-user! function. The functions generated by bind-connection use the connection found in the conn atom by default unless one is explicitly passed in. The parameters are passed in using a map with the keys that match the parameter names specified:

(create-user!
  {:id "user1"
   :first_name "Bob"
   :last_name "Bobberton"
   :email "bob.bobberton@mail.com"
   :pass "verysecret"})

The generated function can be run without parameters, e.g:

(get-users)

It can also be passed in an explicit connection, as would be the case for running in a transaction:

(def some-other-conn
  "jdbc:postgresql://localhost/myapp_test?user=test&password=test")
  
(create-user!
  {:id "user1"
   :first_name "Bob"
   :last_name "Bobberton"
   :email "bob.bobberton@mail.com"
   :pass "verysecret"}
   some-other-conn)

The conman library also provides a with-transaction macro for running statements within a transaction. The macro rebinds the connection to the transaction connection within its body. Any SQL query functions generated by running bind-connection will use the transaction connection within the with-transaction macro:

(with-transaction [t-conn conn]
  (jdbc/db-set-rollback-only! t-conn)
  (create-user!
    {:id         "foo"
     :first_name "Sam"
     :last_name  "Smith"
     :email      "sam.smith@example.com"})
  (get-user {:id "foo"}))

See the official documentation for more details.

SQL Korma

Korma is a domain specific language for Clojure that takes the pain out of working with your favorite RDBMS. Built for speed and designed for flexibility, Korma provides a simple and intuitive interface to your data that won't leave a bad taste in your mouth.

Adding Korma support to an existing project is rather simple as well. You will first need to add the Korma dependency to you project.clj:

[korma "0.4.0"]

We'll have to add a reference to korma.db in order to start using Korma.

(ns myapp.db.core
  (:require
        [korma.core :refer :all]
        [korma.db :refer [create-db default-connection]]))

Korma requires us to create the connection using create-db and the default connection can then be set by calling the default-connection function.

(connect! [] (create-db db-spec))

(defstate ^:dynamic *db*
          :start (connect!))

(default-connection *db*)

This will create a connection pool for your db spec using the c3p0 library. Note that the last created pool is set as the default for all queries.

Korma uses entities to represent SQL tables. The entities represent the core building blocks of your queries. These entities are created by using defentity macro:

(defentity users)

Using the users entity we can rewrite our query to create a user as follows:

(defn create-user [user]
  (insert users
          (values user)))

The get user query would then be rewritten as:

(defn get-user [id]
  (first (select users
                 (where {:id id})
                 (limit 1))))

For further documentation on Korma and its features please refer to the official documentation page.

Database Access

Contents

Configuring the Database

Configuring Migrations

Setting up the database connection

Working with Yesql

SQL Korma

Topics

Books