=sqlosure= is a relational algebra library and DSL for interacting with relational data, primarily in a SQL-Database from your Clojure-program.
Using it Just add the current version of SQLosure to your =deps.edn=. To interact with a specific database system, you must also include the corresponding JDBC driver.
Example using [[https://www.postgresql.org][PostgreSQL]]:
{:deps {de.active-group/sqlosure "0.5.0-SNAPSHOT" org.postgresql/postgresql "42.2.14"}
Overview The core of =sqlosure= is a declarative query language based on [[https://en.wikipedia.org/wiki/Relational_algebra][relational algebra]]. There are two types of values in SQLosure: [[Queries][queries]] and [[Expressions][expressions]]. ** Example Suppose you want to write queries on a =employee= model. An employee consists of
The corresponding SQL-tables might have these =CREATE=-statements
CREATE TABLE employee ( id INT, name TEXT, is_admin BOOLEAN, office_id INT );
CREATE TABLE office ( id INT, floor INT, name TEXT );
To construct queries on those tables, you first need to define relations to operate on
(ns your.name.space (:require [sqlosure.core :refer :all] [sqlosure.db-connection :as db-connection]))
;; Defines a relation with three fields. (def employee (table "employee" [["id" $integer-t] ["name" $string-t] ["is_admin" $boolean-t]]))
(def employee-with-office (table "employee" [["id" $integer-t] ["name" $string-t] ["admin" $boolean-t] ["office_id" $integer-t]]))
(def office (table "office" [["id" $integer-t] ["floor" $integer-t] ["name" $string-t]]))
The =table= function expects the database table-name as string and a vector of tuples for each attribute we want to consider. Note that your are not required to "project" all fields from a table -- you just need to define what you need in this each particular case.
Next, we want to query our database. To accommodate for the differences between different RDBMSs, SQLosure has to concept of a /backend/. Luckily, SQLosure comes equipped with several of those backends. In this case, let's say we want to talk to a PostgreSQL-instance. First, we jot down the [[https://github.com/clojure/java.jdbc][JDBC]] connection map:
(def pg-db {:dbtype "postgresql" :dbname "somedb" :host "localhost" :port "5432" :user "myuser" :password "secret"})
To actually run the query, there are two basic modes. The one we will use here is more basic. [[*CRUD][Further down the document]] we will show the more elaborated way.
(ns ... [sqlosure.db-connection :as db-connection] [sqlosure.backends.postgresql :as postgresql])
(def db-conn (db-connect pg-db postgresql/implementation))
(db-connection/run-query db-conn employee) ;; => [[0 "Marco" true] ;; [1 "Markus" false] ;; ...]
As you can see, running a base relation as the query selects the whole table. Now, we only want employees that are admins.
(def admins (query [emp (embed employee)] (restrict! ($= (! emp "is_admin") ($boolean true))) (project emp)))
Let's break this down
If we run the query, we get the following result:
(db-connection/run-query db-conn admins) ;; => [[0 "Marco" true] ;; ... ;; [42 "Tim" true]]
Now, perhaps we only want to see the names of employees, instead of the whole record. We could of course define a new query that just projects the ="name"= of each employee:
(def admin-names (query [emp (embed employee)] (restrict! ($= (! emp "is_admin") ($boolean true))) (project [["name" (! emp "name")]])))
This would do the trick. Still, we did just define almost the same query as above. This is where it comes into play that the result of a =query=-call is itself a query. This means they easily compose to larger/more complex queries from simpler ones. Thus, we can then redefine =admin-names= as follows:
(def admin-names (query [as (embed admins)] (project [["name" (! emp "name")]])))
We treat =admins= just as a base relation. The schema (e.g. the fields you can select via the =!=-operator) depends on the projection.
Lastly, let's write a query that returns all admins and the name's of their offices:
(def admins-with-offices (query [as (embed admins) os (embed office)] (restrict! ($= (! as "office_id") (! os "id"))) (project [["name" (! as "name")] ["office" (! os "name")]]))) (db-connection/run-query db-conn admins-with-offices) ;; => [["Marco" "Terassen Office"] ;; ["Tim" "Obergeschoss"] ;; ...]
CRUD SQLosure provides a DSL for reading and writing from and to databases. To demonstrate, we will keep our tables from [[*Example][the examples above]]. There are four basic functions to construct programs that read and write to a database
(ns your.name.space (:require [sqlosure.core :refer :all] [sqlosure.db-connection :as db-connection] [sqlosure.lang :as db]))
;; Defines a relation with three fields. (def employee ..) (def office ...)
(db/read! employee) (db/create! employee [42 "Mike" true 0]) (db/udpate! employee (fn [id name admin? office-id] ($= name ($string "Marco"))) (fn [id name admin? office-id] {"is_admin" ($boolean false)})) (db/delete! employee (fn [id name admin? office-id] ($= id ($integer 42))))
What do these functions do?
The keen observer might have noticed that a) all those functions just return values and b) there is no mention of a database backend or connection strings. First, let's construct a program out of these functions, using [[https://github.com/active-group/active-clojure][active-clojure]]'s free monad.
(ns your.name.space (:require [sqlosure.core :refer :all] [sqlosure.db-connection :as db-connection] [sqlosure.lang :as db]
[active.clojure.monad :as monad]));; Defines a relation with three fields. (def employee ..) (def office ...)
(def make-all-employees-admins! (db/udpate! employee (fn [ ] ($boolean true)) (fn [ ] {"is_admin" ($boolean true)})))
(def prog (monad/monadic (db/create! employee [0 "Marco" false 0]) (db/create! employee [1 "Erika" true 0]) (db/create! employee [2 "Sibylle" false 0]) [employees (db/read! employee)] make-all-users-admins! (db/delete! employee (fn [id _] ($= id ($integer 0)))) [employees-after (db/read! employee)] (monad/return {:before employees :after employees-after})))
This program first inserts three employees and reads them back, storing the result as =employees=. Then, after making all employees admins, deletes the user with ="id" = 0=. It then reads the users back once more, storing the result as =employees-after= before returning the two query results. Keep in mind that is still just the description of the operations we want to execute.
To actually run the program, we need a "command-config" object. For this, we have two optionse. ** Option 1: Running against an actual database If we want to run the program with an actual database, we need a db-connection as above. Then, we can run the program using the =run-db= function:
(ns your.name.space (:require ... [sqlosure.runner.database :as db-runner]))
... (run-db (db-runner/command-config db-conn) prog) ;; => {:before [[0 "Marco" false 0] ;; [1 "Erike" true 0] ;; [2 "Sibylle" false 0]] ;; :after [[1 "Erika" true 0] ;; [2 "Sibylle" true 0]]}
The second argument decides how the program will be executed. Since we used the =sqlosure.runner.database= runner, the program is executed in the context of our PostgeSQL database, just as before. ** Option 2: Running with the =embedded= runner Often, we want to construct such programs without really running them against a database. SQLosure provides the =sqlosure.runner.embedded= runner to enable the user to run such programs against a simple, map based "database". Such a database is just a map from table-names to a set of maps, representing the individual records.
(ns your.name.space (:require ... [sqlosure.runner.embedded :as embedded-runner]))
... (def empty-db {}) (def db {"employees" #{{"id" 42 "name" "Simon" "is_admin" false "office_id" 23}}})
(run-db (embedded-runner/command-config empty-db) prog) ;; => {:before [[0 "Marco" false 0] ;; [1 "Erike" true 0] ;; [2 "Sibylle" false 0]] ;; :after [[1 "Erika" true 0] ;; [2 "Sibylle" true 0]]}
(run-db (embedded-runner/command-config db) prog) ;; => {:before [[0 "Marco" false 0] ;; [1 "Erike" true 0] ;; [2 "Sibylle" false 0] ;; [42 "Simon" false 23]] ;; :after [[1 "Erika" true 0] ;; [2 "Sibylle" true 0] ;; [42 "Simon" true 23]]}
This yields the exact same result without ever touching the database and is therefore the perfect way to tests your queries (or even just operate on an in-memory "database").