datumbazo

datumbazo copied to clipboard

• Datumbazo

  [[https://clojars.org/datumbazo][https://img.shields.io/clojars/v/datumbazo.svg]] [[https://github.com/r0man/datumbazo/actions?query=workflow%3A%22Clojure+CI%22][https://github.com/r0man/datumbazo/workflows/Clojure%20CI/badge.svg]] [[https://versions.deps.co/r0man/datumbazo][https://versions.deps.co/r0man/datumbazo/status.svg]] [[https://versions.deps.co/r0man/datumbazo][https://versions.deps.co/r0man/datumbazo/downloads.svg]]

  A Clojure [[https://www.oracle.com/technetwork/java/javase/jdbc/index.html][JDBC]] driver for [[https://github.com/r0man/sqlingvo][SQLingvo]].

** Usage *** Imports

/Datumbazo/ shadows some functions from the =clojure.core=
namespace, such as =distinct=, =group-by= and =update=
functions. It's recommended to require the =datumbazo.core=
namespace via an alias, such as =sql=.

#+BEGIN_SRC clojure :exports code :results silent
  (require '[datumbazo.core :as sql])
#+END_SRC

*** Database component

You can make a new database [[https://github.com/stuartsierra/component][component]] from an URL with the
=db= function.

#+BEGIN_SRC clojure :exports both :results silent
  (def db-spec (sql/db "postgresql://tiger:scotch@localhost/datumbazo"))
#+END_SRC

A database component can be started and stopped with the =start=
and =stop= functions from the [[https://github.com/stuartsierra/component][component]] library. The following
code opens a connection to the database and binds the updated
component to the =db= symbol.

#+BEGIN_SRC clojure :exports both :results silent
  (require '[com.stuartsierra.component :as component])
  (def db (component/start db-spec))
#+END_SRC

*** Select

Select a simple expression. Note that the column names in the
result are generated by the database driver and are database
vendor specific.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/select db [1 2 3])
#+END_SRC

#+RESULTS:
: ({:?column? 1, :?column?_2 2, :?column?_3 3})

Select the result of a function call.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/select db ['(- (now) (cast "1 day" :interval))])
#+END_SRC

#+RESULTS:
: ({:?column? #inst "2019-01-13T21:05:17.621-00:00"})

Select columns from the =information_schema.tables= table.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/select db [:table_catalog :table_schema :table_name]
     (sql/from :information_schema.tables)
     (sql/where '(= :table_name "pg_statistic")))
#+END_SRC

#+RESULTS:
: ({:table_catalog "datumbazo", :table_schema "pg_catalog", :table_name "pg_statistic"})

*** Create table

Create a countries table with =name= and =code= columns.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/create-table db :countries
     (sql/column :id :serial :primary-key? true)
     (sql/column :name :text)
     (sql/column :code :text))
#+END_SRC

#+RESULTS:
: [{:count 0}]

*** Insert

Insert a row into the countries table and return the inserted rows.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/insert db :countries []
     (sql/values [{:code "de" :name "Germany"}
                  {:code "es" :name "Spain"}])
     (sql/returning :*))
#+END_SRC

#+RESULTS:
: ({:id 5, :name "Germany", :code "de"} {:id 6, :name "Spain", :code "es"})

*** Order by

Select all countries ordered by the =name= column in ascending
order.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/select db [:*]
     (sql/from :countries)
     (sql/order-by (sql/asc :name)))
#+END_SRC

#+RESULTS:
: ({:id 1, :name "Germany", :code "de"} {:id 3, :name "Germany", :code "de"} {:id 5, :name "Germany", :code "de"} {:id 2, :name "Spain", :code "es"} {:id 4, :name "Spain", :code "es"} {:id 6, :name "Spain", :code "es"})

*** Where clauses

Select the =id= and =code= columns of the =countries= table for
all rows whose =name= columns is equal to =Spain=.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/select db [:id :code]
     (sql/from :countries)
     (sql/where '(= :name "Spain")))
#+END_SRC

#+RESULTS:
: ({:id 2, :code "es"} {:id 4, :code "es"} {:id 6, :code "es"})

*** Delete

Delete a country by name, and return the affected rows.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/delete db :countries
     (sql/where '(= :name "Spain"))
     (sql/returning :*))
#+END_SRC

#+RESULTS:
: ({:id 2, :name "Spain", :code "es"} {:id 4, :name "Spain", :code "es"} {:id 6, :name "Spain", :code "es"})

*** Drop table

Drop the countries table.

#+BEGIN_SRC clojure :exports both :results verbatim
  @(sql/drop-table db [:countries])
#+END_SRC

#+RESULTS:
: [{:count 0}]

** License

Copyright © 2012-2019 [[https://github.com/r0man][r0man]]

Distributed under the Eclipse Public License, the same as Clojure.