<!-- [](https://versions.deps.co/camsaul/toucan2) --> <!-- [](https://versions.deps.co/camsaul/toucan2) -->

T2: Toucan 2

Toucan 2 is a library for delightful database interaction.

At the end of the day, almost every non-trivial project needs to interact with a database. Toucan 2 makes its easy to query the data in your database in a consistent way and define behaviors that should happen every time a row is retrieved, created, updated, or deleted.

Toucan 2 is a successor library to Toucan with a modern and more-extensible API, more consistent behavior (less gotchas), support for different backends including non-JDBC databases and non-HoneySQL queries, support for namespaced keywords, and with more useful utilities.

Toucan 2 uses Honey SQL 2, next.jdbc, and Methodical under the hood. Everything is super efficient and reducible under the hood (even inserts, updates, and deletes!) and magical (in a good way).

You Can Toucan: 12 Cool Things You Can Do with Toucan 2

REPL-friendly syntax

Toucan 2 is optimized to for REPL-driven development, It offers a high-level interface that's quick and easy to use from the REPL.

Compare a simple query with next.jdbc vs the equivalent way to do it in Toucan:

(require '[next.jdbc :as jdbc])

(def db-spec
  {:dbtype   "postgresql"
   :dbname   "toucan2"
   :host     "localhost"
   :post     5432
   :user     "cam"
   :password "cam"})

;;; next.jdbc

(let [my-datasource (jdbc/get-datasource db-spec)]
  (with-open [connection (jdbc/get-connection my-datasource)]
    (jdbc/execute! connection ["SELECT * FROM people WHERE name = ?" "Cam"])))
;; =>
[{:people/id 1
  :people/name "Cam"
  :people/created-at #inst "2020-04-21T23:56:00.000000000-00:00"}]

;;; Toucan 2

(require '[toucan2.core :as t2])

(t2/select :conn db-spec "people" :name "Cam")
;; =>
[{:id 1
  :name "Cam"
  :created-at #object[java.time.OffsetDateTime 0x2c8ec7ed "2020-04-21T23:56Z"]}]

;;; next.jdbc + Honey SQL 2

(require '[honey.sql :as sql])

(let [my-datasource (jdbc/get-datasource db-spec)]
  (with-open [connection (jdbc/get-connection my-datasource)]
    (jdbc/execute! connection (sql/format {:select [:*]
                                           :from   [:people]
                                           :where  [:like :name "C%"]}))))
;; =>
[{:people/id 1
  :people/name "Cam"
  :people/created-at #inst "2020-04-21T23:56:00.000000000-00:00"}]

;;; Toucan 2

(t2/select :conn db-spec "people" :name [:like "C%"])
;; =>
[{:id 1
  :name "Cam"
  :created-at #object[java.time.OffsetDateTime 0x2c8ec7ed "2020-04-21T23:56Z"]}]

Define a default connection

As you can see, Toucan 2 is quick and easy to use from the REPL. But it can be even easier! Typing dbspec over and over can get tedious. Toucan 2 lets you define a default connection:

(require '[methodical.core :as m])

(m/defmethod t2/do-with-connection :default
  [_connectable f]
  (t2/do-with-connection db-spec f))

(t2/select "people" :name [:like "C%"])
;; =>
[{:id 1
  :name "Cam"
  :created-at #object[java.time.OffsetDateTime 0x2bf3111d "2020-04-21T23:56Z"]}]

You can also define a default connection for specific tables (models). For more information, see Connections.

Define models

As we'll see below, you can define lots of arbitrary behaviors when selecting, updating, inserting, and deleting rows from various tables in your database. These behaviors are encapsulated in multimethods that are triggered for what Toucan 2 calls models, which are usually just plain Clojure keywords.

By default, Toucan 2 will use the name of a model keyword as the table name to use when querying it. Let's try using a model called :model/people:

(t2/table-name :model/people)
;; => :people

;; Select one row from :people with the primary key 1
(t2/select-one :model/people 1)
;; =>
{:id 1
 :name "Cam"
 :created-at #object[java.time.OffsetDateTime 0x2c8ec7ed "2020-04-21T23:56Z"]}

Going forward, we'll be deriving a lot of models from :models/people. To make new models like :models/people.cool use the right table name, let's tell Toucan 2 to always use people as the table name for anything deriving from :models/people:

(m/defmethod t2/table-name :models/people
  [_model]
  :people)

To learn more about models, see Models.

Define transforms

Toucan 2 is much more than just a glorified collection of helper functions. Suppose we have a column that we would like to automatically be converted to keywords whenever it comes out of the database, and automatically be converted back to strings when it goes back into the database. With Toucan 2, you can easily define column transformations with deftransforms.

Let's define a new model, so we don't affect :models/people itself.

(derive :models/people.keyword-name :models/people)

(t2/deftransforms :models/people.keyword-name
  {:name {:in  name
          :out keyword}})

(t2/select :models/people.keyword-name :name :Cam)
;; =>
[{:id 1
  :name :Cam
  :created-at #object[java.time.OffsetDateTime 0x3af24cf "2020-04-21T23:56Z"]}]

Whenever a non-nil value goes in to the database, Toucan 2 calls name on it; when a non-nil value comes out, Toucan 2 calls keyword on it. For more info, see Transforms.

Define behavior after selecting something

Sometimes we want to do more general things than just transform a single column to another value. You can use tools like define-after-select to define more general transformations for results, such as adding additional columns, or to trigger some other behavior for side effects. Let's say we want to give all our people cool names when they come out of the database.

(derive :models/people.cool :models/people)

(t2/define-after-select :models/people.cool
  [person]
  (assoc person :cool-name (str "Cool " (:name person))))

(t2/select-one :models/people.cool 1)
;; =>
{:name       "Cam"
 :cool-name  "Cool Cam"
 :id         1
 :created-at #object[java.time.OffsetDateTime 0x2691c719 "2020-04-21T23:56Z"]}

This method is not applied when you use :models/people or other models derived from it, unless those models derive from :models/people.cool. You can define before- and after- methods for select, insert, update, and delete. For more information, see Before & After Methods.

Compose behaviors

Because Toucan 2 is implemented with multimethods, you can compose various behaviors by simply deriving a model from one or more others. Built-in Toucan 2 tools like deftransforms and define-after-select automatically compose.

Let's define another after-select method, ::without-created-at, to remove the :created-at key from our results, then create a new model that combines :models/people.cool with ::without-created-at to get both behaviors:

(t2/define-after-select ::without-created-at
  [row]
  (dissoc row :created-at))

(derive :models/people.cool.without-created-at :models/people.cool)
(derive :models/people.cool.without-created-at ::without-created-at)

;;; Tell Methodical to call the :models.people.cool method before the
;;; ::without-created-at method
(m/prefer-method! #'toucan2.tools.after-select/after-select
                  :models/people.cool
                  ::without-created-at)

(t2/select-one :models/people.cool.without-created-at 1)
;; =>
{:name "Cam", :cool-name "Cool Cam", :id 1}

Define named queries

Often you'll want to write a big complicated query:

(t2/select "venues" {:select    [:venues.id
                                 [:venues.name :venue-name]
                                 [:category.name :category-name]
                                 [:category.slug :category-slug]]
                     :from      [:venues]
                     :left-join [:category [:= :venues.category :category.name]]})
;; =>
[{:id 1, :venue-name "Temp