Databases

40.1 next.jdbc

(require '[next.jdbc :as jdbc]
         '[next.jdbc.sql :as sql]
         '[next.jdbc.result-set :as rs])

(def ds (jdbc/get-datasource {:dbtype "sqlite" :dbname "app.db"}))
;; PostgreSQL: {:dbtype "postgresql" :dbname "app" :user "app" :password (System/getenv "DB_PASS")}

(jdbc/execute! ds ["CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"])
(sql/insert! ds :users {:name "Ana" :email "ana@x.com"})
(sql/query ds ["SELECT * FROM users WHERE name = ?" "Ana"])   ; parameterized: safe from SQL injection
;; => [#:users{:id 1, :name "Ana", :email "ana@x.com"}]
(jdbc/execute-one! ds ["SELECT count(*) AS n FROM users"] {:builder-fn rs/as-unqualified-lower-maps})
(sql/update! ds :users {:email "new@x.com"} {:id 1})
(sql/delete! ds :users {:id 1})

(jdbc/with-transaction [tx ds]
  (sql/insert! tx :users {:name "Bo"})
  (sql/insert! tx :users {:name "Cy"}))

;; Connection pooling with HikariCP
;; (require '[next.jdbc.connection :as connection]) (connection/->pool com.zaxxer.hikari.HikariDataSource db-spec)

40.2 HoneySQL: SQL as Data

(require '[honey.sql :as hsql]
         '[honey.sql.helpers :as h])

(hsql/format {:select [:id :name]
              :from [:users]
              :where [:and [:= :active true] [:> :age 18]]
              :order-by [[:name :asc]]
              :limit 10})
;; ["SELECT id, name FROM users WHERE (active = TRUE) AND (age > ?) ORDER BY name ASC LIMIT ?" 18 10]

(-> (h/select :*) (h/from :orders) (h/where [:= :user-id 42]) hsql/format)
;; ["SELECT * FROM orders WHERE user_id = ?" 42]
(jdbc/execute! ds (hsql/format {:select [:*] :from [:users]}))

40.3 Datomic and XTDB

Datomic (Pro/Cloud/Local, free) Immutable facts, Datalog, history, used by Nubank Bitemporal, SQL + Datalog, open source

Datalevin, Datascript (in-memory, also in the browser), Asami next.jdbc + HoneySQL

Monger, carmine (Redis), Elastisch Migratus, ragtime

;; Datomic: immutable database of facts (datoms) with Datalog queries and time travel
;; Schema:
[{:db/ident :user/email :db/valueType :db.type/string :db/cardinality :db.cardinality/one :db/unique :db.unique/identity}
 {:db/ident :user/name :db/valueType :db.type/string :db/cardinality :db.cardinality/one}]
;; Transact: (d/transact conn {:tx-data [{:user/email "ana@x.com" :user/name "Ana"}]})
;; Query:
;; (d/q '[:find ?name
;;        :where [?u :user/email "ana@x.com"]
;;               [?u :user/name ?name]]
;;      (d/db conn))
;; Time travel: (d/as-of db #inst "2025-01-01")