The first part is to install PostgreSQL, which depends on your system, and can be a little bit of a pain. Then create a role, and create a DB. When setting up a new computer, I usually
createrole then restore my pg_dumpall by using psql -f filename db . That's the easiest way for me to get my DBs up and running on a new computer when I need to work on my website, and don't feel like filling the DBs with fake entries, or setting up accounts again.Getting JDBC up and running really is as simple as 1-2-3. The configuration for each server is a simple Clojure hash called a db-spec. Here are some examples from the JDBC git:
(def mysql-db {:dbtype "mysql"
:dbname "clojure_test"
:user "clojure_test"
:password "clojure_test"})
(def pg-db {:dbtype "postgresql"
:dbname "mypgdatabase"
:host "mydb.server.com"
:user "myuser"
:password "secret"
:ssl true
:sslfactory "org.postgresql.ssl.NonValidatingFactory"})
As simple as this. As far as using your db-spec for queries or whatnot, without HoneySQL (or another Clojure forms to SQL library), it's just writing SQL strings. They return Clojure maps, however (basically the point of this whole charade).
backend.db> (jdbc/query pg-db ["select title, id from posts;"])
=> ({:title "clojure blog 1: why clojure?", :id 1} {:title "clojure blog 3: quick hiccup run-through", :id 5} {:title "clojure blog 2: HTTP routing (ring / compojure)", :id 2} {:title "asynchronize your life: shell commands 10x faster", :id 4} {:title "clojure blog 4: postgreSQL & JDBC", :id 6})
Aaaaand that's almost all you need to know. In fact, if you're a big fan of string concatenation, you've read enough; go crazy with
(str) and you can write any query you want. Personally, I'm not a huge fan of strings for every query, so I use HoneySQL.It's about as simple as it gets for a DSL. Lisp forms -> SQL forms. Here is some example:
(jdbc/query db/pg-db (-> (select :*)
(from :posts)
(where [:is :status true])
sql/format))
This post is more for the sake of completeness of my guide, and isn't particularly useful by itself, but maybe someone benefits from a the "JDBC at 30,000 feet" which this became. Next in my clojure blog series: User Authentication! That was somewhat of a handful, so I'll attempt to reel it in and write something useful. I've also added some features that certainly warrant a post: screenshot upload, link shortening, and a new secondary backend for all that to work on. More to come!