taylorlapeyre / oj

:tropical_drink: A Clojure library for talking to your database.

oj

A refreshing Clojure library for talking to your database, heavily influenced by Ring.

Features

• Gives you a standard interface for running and generating SQL
• Focuses on the most common and useful features of SQL
• Enforces type checking and validation for queries
• Sensible defaults
• Concise and powerful API
• Encourages reusable components

Anti-features

• Doesn't try to implement the entiretly of SQL
• Doesn't require you to write SQL
• Doesn't create its own domain-specific language
• Doesn't surprise you

The SPEC file provides a complete description of the OJ interface.

Installation

Add this to your Leiningen :dependencies:

[oj "0.3.0"]

You'll also need a database driver (thanks to yesql for providing this handy table):

Database	:dependencies Entry
PostgreSQL	[org.postgresql/postgresql "9.3-1102-jdbc41"]
MySQL	[mysql/mysql-connector-java "5.1.32"]
Oracle	[com.oracle/ojdbc14 "10.2.0.4.0"]
SQLite	[org.xerial/sqlite-jdbc "3.7.2"]
Derby	[org.apache.derby/derby "10.11.1.1"]

Usage

Queries are represented as a Clojure map. The full specification of a query map can be found here.

(def users-named-taylor
  {:table :users
   :select [:id :email]
   :where {:first_name "taylor"}})

Queries can be executed by passing a query map and a database config into oj/exec:

(def db {:subprotocol "mysql"
         :subname "//127.0.0.1:3306/wishwheel3"
         :user "root"
         :password ""})

(oj/exec users-named-taylor db)
; => ({:id 1 :email "taylorlapeyre@gmail"} ...)

Modifiers are functions that transform a query map into another query map. This allows us to chain them together. Some basic modifiers are provided by default at oj.modifiers.

(require [oj.core :as oj]
         [oj.modifiers :as db])

(defn find-by-username [username]
  (-> (db/query :users)
      (db/select [:id :username :email :created_at])
      (db/where {:username username})
      (oj/exec db-config)
      (first)))

(find-by-username "taylorlapeyre")
; => {:id 1 :username "taylorlapeyre"}

OJ's roots in regular Clojure data structures make it extremely powerful for building abstractions.

(defn user [& forms]
  (let [query (reduce merge {:table :users} forms)]
    (oj/exec query db)))

(user {:where {:id 1}})
=> SELECT * FROM users WHERE users.id=1

(user {:where {:id 1}}
      {:select [:id :username]})
=> SELECT id, username FROM users WHERE users.id=1

Not quite ActiveRecord, but it's getting there. And in 3 lines of code no less!

Of course, you can also perform all of the standard CRUD operations that you'd expect:

(defn create [user-data]
  (when (valid? user-data)
    (-> (db/query :users)
        (db/insert user-data)
        (oj/exec db-config))))

(defn update [id user-data]
  (when (valid? user-data)
    (-> (db/query :users)
        (db/where {:id id})
        (db/update user-data)
        (oj/exec db-config))))

(defn delete [id]
  (-> (db/query :users)
      (db/where {:id id})
      (db/delete)
      (oj/exec db-config)))

How about using SQL's aggregate functions? OJ allows you to use those as well, using a Clojure-like syntax.

For example, to get the average price of all items:

(-> (db/query :items)
    (select '(avg :price))
    (oj/exec db-config))
; => 46.76

For more advanced uses, OJ will provide the data in a useful format.

(-> (db/query :items)
    (group :published)
    (select [:published '(avg :price)])
    (oj/exec db-config))
; ({:published 1 :avg {:price 64.35}}, {:published 0 :avg {:price 10.35}})

OJ gives you a lot of flexibility. For instance, you could write some custom modifier functions and then execute them when you like. This allows you to combine them.

(defn find-by-username
[query username]
(-> query
    (db/where {:username username})))

(-> (query :users)
  (find-by-username "taylor")
  (oj/exec db-config)
  (first))

Printing SQL Queries

If you'd like SQL queries logged to your console when executed, you can enable it by setting the environment variable PRINT_DB_LOGS to true.

Contributing

1. Fork this repository
2. Create a new branch
3. Do your thing
4. Submit a pull request with a description of the change.

TODO

• Joins

License

Copyright © 2014 Taylor Lapeyre

Distributed under the Eclipse Public License either version 1.0 or (at your option) any later version.

oj's Issues

support for CREATE TABLE?

I like what I'm seeing here with oj. Nice library!

I've been working quite a bit on SQL + clojure and have found the need to create database schema from application code. Have you considered adding support for creating tables?

could do something like active record does for creating "models"

class Customer < ActiveRecord::Base
  validates :first_name, presence: true
  has_one :small_order 
end

Thanks!

how to use 'or' in :where

Implement aggregate functions (COUNT, AVERAGE)

Need to figure out how to represent this in a query map.

In most cases, these will not be needed. Counting and averaging results is trivial to do in Clojure. But in some cases (large amounts of data), it may be desirable.

Implement >= and <= operators in WHERE statements.

Pretty simple. Edit the generator for :where to support these new operators in addition to the ones we currently support ([< > not=]).

Validation fails when creating an update query containing a boolean value

It looks like insert was updated to allow boolean values, but update is still broken.

Name the result of aggregate functions in a more friendly way.

{:table :items
 :select ['(avg :price)]}

; => ({:avg(price) 20})

I wonder if we can make this return a map that allows us to access the result of this a bit easier.

Use of double quotes as string delimiters in SQL statement causes errors

While MySQL allows either single or double quotes as string delimiters, other databases such as PostgreSQL only allow single quotes. OJ is currently using double quotes as a delimiter, which causes errors in any query on Postgres that uses a string in its :where element.

Correctly parse Date/Time

TBD

Add option to log executed SQL queries to a file instead of just the console.

Forcing values in query map for insert/update to be integer or string only prevents lib usage with MySQL boolean columns

Being prevented from adding non-string or integer values to a query map being used to create an insert or update query has some significant implications when working with MySQL databases.

In MySQL, boolean values in MySQL are represented by integers; boolean columns are actually TINYINT(1). TRUE and true evaluate to 1 and FALSE and false evaluate to 0. The strings "TRUE" "true" "FALSE" and "false" do not evaluate to 1 or 0, and when passed as a value for this type of column will result in a java.sql.SQLException.

For instance:

Given this MySQL table:

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| text      | varchar(255) | YES  |     | NULL    |                |
| completed | tinyint(1)   | NO   |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+

When I try to call oj/exec with this map:

{:insert {:text "call mom", :completed "false"}, :table :todos}

...this query is executed:

INSERT INTO todos (text, completed) VALUES ('call mom', 'false')

...resulting in this error:

java.sql.SQLException: Incorrect integer value: 'false' for column 'completed' at row 1

Sending values of boolean type is not allowed by oj, and will result in an error:

java.lang.Exception: The query map had a problem: Every value to an :insert map must be either a string or a number.

Add a section about aggregate functions to the README

Implement Joins

(Better description coming)

What we need to do is implement sql JOIN queries and return the joined data in a useful format:

{:column "data" ... :joined_table ({:column "data" ...})}

What the difference between oj and korma?

Is there principal difference between oj and korma?

Use parallelism to speed up joins

In OJ, "joins" are implemented as subqueries to enable nesting the joined table in a different key in the returned results. Because of this, it can be a little slow: the amount of subqueries to be executed is equal to the number of returned tuples O(n).

We can speed this up by executing these subqueries in parallel using pmap.