How do I connect to a MySQL database from Clojure?

Assumption: you already have both Clojure and MySQL running on your machine.

1. checkout and build clojure-contrib:

   git clone git://github.com/richhickey/clojure-contrib.git
   cd clojure-contrib
   build
   

   Put the resulting clojure-contrib.jar on your CLASSPATH.

2. Download MySQL Connector/J and put the mysql-connector-java-5.1.7-bin.jar on your CLASSPATH

   You might have to run your JVM with these arguments:

   -Djdbc.drivers=com.mysql.jdbc.Driver
   

3. Determine the connection URL of your MySQL database

   For example, if you are running MySQL under MAMP then the URL that you would use in JDBC will look something like:

   conn = DriverManager.getConnection
           ("jdbc:mysql://localhost:8889/db_name?user=root&password=root")
   

   The url is broken down into these components:

   • protocol: jdbc:
   • subprotocol: mysql
   • db-host: localhost
   • db-port: 8889
   • username
   • password

4. Make this clojure script, modify the database connection parameters to match your URL, save as test.clj, compile and run.

    (use 'clojure.contrib.sql)               ;;' satisfy prettify

      (let [db-host "localhost"
            db-port 8889
            db-name "db_name"]
        (def db {:classname "com.mysql.jdbc.Driver"
               :subprotocol "mysql"
               :subname (str "//" db-host ":" db-port "/" db-name)
               :user "root"
               :password "root"})
        (with-connection db
          (with-query-results rs ["select * from languages"]
            (dorun (map #(println (:language :iso_code %)) rs)))))

            ; rs will be a sequence of maps,
            ; one for each record in the result set.

NB This code was adapted from similar code written by Mark Volkmann to access a Postgres database from Clojure

This is a lein-friendly answer, with much guidance from this blog by Nurullah Akkaya:

1. add dependencies to your project.clj:

   (defproject clojql "1.0.0-SNAPSHOT"
     :description "FIXME: write description"
     :dependencies [[org.clojure/clojure "1.2.1"]
                    [org.clojure/clojure-contrib "1.2.0"]   ;; for clojure.contrib.sql
                    [org.clojure/java.jdbc "0.0.6"]         ;; jdbc 
                    [mysql/mysql-connector-java "5.1.6"]])  ;; mysql driver
   

2. run lein deps from the command line to grab the dependencies

3. specify your connection information in a map:

   user=> (use 'clojure.contrib.sql)
   nil
   user=> (def db {:classname "com.mysql.jdbc.Driver" 
                   :subprotocol "mysql" 
                   :subname "//localhost:3306/nmr" 
                   :user "root"})
   

4. use with-connection and with-query-results macros (& others):

   user=> (with-connection db (with-query-results rs ["select * from sometable"] (count rs)))
   667
   

steps 3 & 4 can either be from the repl (lein repl to start it) or within normal source code

As of 2016:

using Leiningen, add dependencies inside project.clj:

:dependencies [[org.clojure/clojure "1.8.0"]
               [org.clojure/java.jdbc "0.4.2"]
               [mysql/mysql-connector-java "5.1.38"]]

require database connector inside namespace definition:

(ns name.space
  (:require [clojure.java.jdbc :as j]))

define database connection:

(def db-map {:subprotocol "mysql"
             :subname "//localhost:3306/SCHEME"
             :user "DB_USER"
             :password "DB_USER_PASS"})

query the database:

(j/query db-map ["SELECT * FROM table"])

find more examples at http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql.html

If you want some syntactic sugar, you can try Korma.

Docs

Github

(use 'korma.db)
(defdb db (postgres {:db "mydb"
                     :user "user"
                     :password "dbpass"}))

(use 'korma.core)
(defentity users)


(select users)
;; executes: SELECT * FROM users


(select users
  (where (or (= :usersname "chris")
             (= :email "[email protected]"))))
;; executes: SELECT * FROM users WHERE (users.usersname = 'chris' OR users.email = '[email protected]')