Some examples using Sqlingvo and korma with clojure.java.jdbc to create a table and execute queries

gistfile1.clj

(require '[clojure.java.jdbc :as jdbc]
         '[korma.db :as kdb]
         '[korma.core :as kcore]
         '[sqlingvo.core :as sql]
         '[sqlingvo.vendor :as v]))

;;;examples here are directly from the sqlingvo, jdbc, korma websites
;;;in order to provide a concrete example of using a SQL DSL with jdbc
;;;to execute mysql commands

(def mysql-db {:subprotocol "mysql"
               :subname "//127.0.0.1:3306/clojure_test"
               :user "user"
               :password "pass"})

;;;mySQL table creation
(def tbl
  (sql/sql (v/->mysql)
   (sql/create-table :films
                     (sql/column :title :varchar :length 40 :not-null? true)
                     (sql/column :kind :varchar :length 10))))
;["CREATE TABLE `films` (`title` VARCHAR(40) NOT NULL, `kind` VARCHAR(10))"]

;;;insert 2 rows into the newly created films table
(def add
  (sql/sql (v/->mysql)
   (sql/insert :films []
               (sql/values [{:title "Yojimbo" :kind "Drama"}
                            {:title "The Dinner Game" :kind "Comedy"}]))))
;["INSERT INTO `films` (`kind`, `title`) VALUES (?, ?), (?, ?)" "Drama" "Yojimbo" "Comedy" "The Dinner Game"]

;;;query the fils table for all entries
(def query (sql/sql (v/->mysql) (sql/select [*] (sql/from :films))))

;;;create table
(jdbc/with-db-connection [db-con mysql-db]
  (jdbc/execute! db-con tbl))
;[0]

;;;insert rows
(jdbc/with-db-connection [db-con mysql-db]
  (jdbc/execute! db-con add))
;(2)

;;;query table
(jdbc/with-db-connection [db-con mysql-db]
  (jdbc/query db-con query)) ;must use jdbc/query for queries
;({:kind "Drama", :title "Yojimbo"} {:kind "Comedy", :title "The Dinner Game"})

;;;Alternatively, once the table is created, mysql queries can also be executed using korma
;;;korma provides an elegant functional way to execute some mysql commands by wrapping the 
;;;jdbc functions

(kdb/defdb db 
  (kdb/mysql {:db "clojure_test"
              :user "user"
              :password "pass"}))

(kcore/defentity users);table films

(kcore/select films)
;[{:kind "Drama", :title "Yojimbo"} {:kind "Comedy", :title "The Dinner Game"}]