Toucan provides a variety of DB functions that simplify common access patterns for querying the DB and inserting
or updating records using Toucan models. These functions can be found in the toucan.db namespace.
toucan.db functions work hand-in-hand with the model functions found in toucan.models. For example,
a model might define some custom behavior to take place whenever an instance is retrieved from the DB via the select family
of functions by providing an implementation for toucan.models/post-select.
The most important toucan.db functions are for the basic CRUD operations: the select, insert!, update!, and delete!
families of functions. Like their names imply, there are but simply sophisticated wrappers around the corresponding JDBC functions,
with built-in compilation of HoneySQL forms (and some syntactic sugar for creating them) and integration with the Toucan models and
their IModel method implementations.
The select family of functions is used to retrieve objects from the database.
(db/select User) -> [...] ; return a sequence of Usersselect returns a eagerly fetched sequence of model instances. You can restrict the set of values returned by passing key-value
pairs of columns and values:
(db/select User :name "Cam") ; -> return a sequence of Users whose name is CamWhile it may seem magical, under the hood Toucan is just compiling these key-value arguments into HoneySQL forms and passing them
to clojure.java.jdbc. The example above gets compiled to function call like:
(jdbc/query (db/connection)
(sql/format {:select [:*], :from [:users], :where [:= :name "Cam"]}, :quoting (db/quoting-style)))In turn, this generates SQL like:
SELECT *
FROM "users"
WHERE "name" = 'Cam'But typing that all out would get repetitive, which is why Toucan simplifies things for you.
Since it's all HoneySQL under the hood, you can also do things a bit more advanced when passing key-value arguments:
(db/select User :name [:not= nil]) ; return a sequence of Users whose :name is non-nilThat gets compiled to a HoneySQL form with a where clause like:
{:where [:not= :name nil], ...}Nice. Now let's say you only want to select a certain group of fields from User:
(db/select [User :first-name :last-name])
;; -> [{:first-name "Cam", :last-name "Saul"}
;; {:first-name "Rasta", :last-name "Can"}]By replacing the model with a vector of the form [model & fields] you can specify which fields you want Toucan to return. Often
DB queries return fields that aren't needed; Toucan makes it simple to specify exactly what you want, and no more, making it
easy to write performant code right from the beginning.
(The default-fields method of IModel can be used to define what is essentially a default [model & fields] vector
to be used whenever a vector isn't specified; e.g. you can tell Toucan to automatically treat calls like
(db/select User) as calls like (db/select [User :first-name ...]). Passing a vector will override default-fields.
See the models guide for more details.)
Restricting the results returned and selecting a subset of the available fields is nice, but what if you want to do other things SQL lets you do, like specifying ordering or limiting the number of results?
Toucan tries to make such things easy by also accepting raw HoneySQL forms as arguments to the select family of functions.
(db/select User :name [:not= nil] {:limit 2}) -> [...]This is compiled to a clojure.java.jdbc/HoneySQL call like:
(jdbc/query (db/connection)
(sql/format {:select [:*], :from [:users], :where [:not= :name nil], :limit 2}, :quoting (db/quoting-style)))The raw HoneySQL forms are merged directly into the map generated automatically from the other arguments.
select-field is a convenience for selecting distinct values of a single field.
(db/select-field :first-name User) ; -> #{"Cam", "Rasta", ...}Values are returned as a set to facilitate things like calls to contains?. The example above is equivalent to:
(set (map :first-name (db/select [User :first-name])))select-one selects a single object; the object is returned directly, rather than an item in a sequence (as with select).
(db/select-one User :id 1) ; -> {:id 1, :first-name "Cam", :last-name "Saul"}The example above is equivalent to:
(first (db/select User :id 1, {:limit 1}))Keep in mind that the query has a LIMIT 1 clause; if it would otherwise return more than one result, which one gets returned
is indeterminate. In that case, it may be prudent to include a HoneySQL {:order-by ...} clause or additional key-value (:where)
clauses.
select-one-field returns a single value of a single field from a single object. It's a combination of select-one
and select-field:
(db/select-one-field :first-name User :id 1) ; -> "Cam"The example above is equivalent to:
(:first-name (db/select-one [User :first-name] :id 1))Like select-one-field, but assumes the field in question is :id.
(db/select-one-id User :first-name "Cam") ; -> 1The example above is equivalent to:
(:id (db/select-one [User :id] :first-name "Cam"))Select a set of IDs. Like select-field, but assumes the field in question is :id.
;; return Users with no :first-name
(db/select-ids User :first-name nil) ; -> #{10 20}The call above is equivalent to:
(set (map :id (db/select [User :id] :first-name nil)))Select the number of objects matching some criteria. Equivalent to a SQL SELECT COUNT(*) query.
(db/count User :first-name nil) ; -> 2A convenience for generating a map of values of one column to values of another. This is useful for things like mapping IDs to names or vice versa:
(db/select-field->field :id :name Venue) ; -> {1 "Tempest", 2 "Ho's Bootleg Tavern", 3 "Louie's", ...}Like select-field->field, but assumes the second field is :id.
(db/select-field->id :name Venue) ; -> {"Tempest", 1, "Ho's Bootleg Tavern" 2, "Louie's" 3, ...}Like select-field->field, but assumes the first field is :id.
(db/select-id->field :name Venue) ; -> {1 "Tempest", 2 "Ho's Bootleg Tavern", 3 "Louie's", ...}The exists? function can be used to efficiently check whether a record matching certain constraints exists. It accepts
syntax similar to the select family of functions, but returns a Boolean value:
(db/exists? User :first-name "Cam") ; -> true
(db/exists? User :first-name "Spam") ; -> falseUsing select will realize the full set of results in memory. For smaller sets of results this is fine but queries that
could potentially return many rows, this could cause memory issues. select-reducible is an similar function to select,
but will return a reducible sequence instead of a vector. Using this, it's possible to consume the query results as they
are streamed from the database. Using this, you can avoid fully realizing the set of results in memory.
;; Send every active user a push notification!
(run! send-push-notification!
(db/select-reducible User :active true))
;; Select every active user, filter some out with complex-filter-logic-fn,
;; and serialize the rest to a streaming HTTP response
(transduce (filter complex-filter-logic-fn)
serialize-to-http-response
(db/select-reducible User :active true))With select-reducible, rows are processed as they are streamed from the database.
The toucan.db/insert familiy of functions makes it easy to insert new objects into the database while taking advantage of
Toucan's customizable behavior for different models. Toucan makes it easy to automatically add new values to objects before they're
inserted into the database, check preconditions, or perform automatic type conversion.
As the names imply, the insert! family of functions are wrappers around clojure.java.jdbc/insert! functions, which in turn
perform the equivalent of a SQL INSERT.
insert! is the primary Toucan function for adding new records to your DB. For convenience, insert! accepts either a single
object or key-value arg pairs:
(db/insert! Label {:name "Toucan Friendly"})
;; same as
(db/insert! 'Label :name "Toucan Friendly")Before insert! inserts an object into the database, it goes through several steps. First, it calls the model's implementation
of pre-insert, if it has one; this is a good opportunity to check preconditions or modify the incoming values of the object in ways
that aren't feasible by defining types or properties. Next, it performs any type conversions specified by the model's types,
such as converting keywords to strings or serializing objects as JSON. Finally, it applies any property functions for the model,
for example adding created_at or updated-at values. Finally, the new value is inserted into the DB.
After the new record is inserted into the DB, insert! returns the newly created object, and goes through the same steps
as the select family of functions (e.g., performing type conversions, calling property functions, and post-select methods
as appropriate).
Finally, the model's implementation of post-insert, if any, is called with the newly inserted object. This can be a good place
to do things like trigger asynchronous tasks (perhaps sending welcome emails after a new User is added) or adding newly created
Photos to a moderation queue.
Most of the examples above are explored in more detail in the guide to defining models.
insert-many! is a variation of insert! that can be used to insert many objects at once.
(db/insert-many! Label [{:name "Toucan Friendly"}
{:name "Bird Approved"}]) ;; -> [38 39]insert-many! returns a sequence of the IDs of the newly created objects.
Note: Unlike insert!, insert-many! does not currently call post-insert on the newly created objects. If you need
post-insert behavior, be sure to use insert! for the time being. This is subject to change in the future; there is an
open issue to consider it.
There are a few occasions where it might be desirable to avoid calls to pre-insert, post-insert, post-select, and
the various type conversion and property functions, perhaps for testing or performance reasons. simple-insert! and
simple-insert-many!, variations of insert! and insert-many!, respectively, skip these calls.
Rather than returning the newly created object itself, simple-insert! returns the ID of the newly created object.
The update! family of functions, which predictably correspond to SQL UPDATE commands, update records in the database.
update! updates values for a single object with a given :id. Like insert!, update! accepts either a map of new values
or key-value argument pairs:
(db/update! Label 11 :name "ToucanFriendly")
;; same as
(db/update! Label 11 {:name "ToucanFriendly"})In the examples above, update! changes the :name of a Label with :id 11. No other values of Label 11 are affected.
Before making updates, the normal type conversions and property functions are applied, and the model's implementation of
pre-update, if any. As with insert!, these methods can be implemented to check preconditions or add values to records
(such as an updated :updated-at timestamp) when objects are updated.
update! returns true if a row was affected, and false otherwise (i.e., if the :id did not match any rows).
NOTE As with some other functions, Toucan assumes your models have primary keys named :id; this limitation
will be addressed in a future release . See this GitHub issue.
If your models do not have a primary key named :id, you can use update-where! instead; see below.
update-where! can be used to update one or more objects that match a map of conditions at the same time. Note that this
can also be used to update objects with no :id primary key, unlike update!.
;; set the `:email` of users named Rasta Toucan to "[email protected]"
(db/update-where! User {:first-name "Rasta"
:last-name "Toucan"}
:email "[email protected]"}Most conditions that you can pass to the select family of functions work here as well.
Like update!, update-where! returns true if any objects were affected, false otherwise.
NOTE Unlike update!, update-where! does not perform automatic type conversions or invoke property functions or pre-update
implementations on the objects that will be updated. This is subject to change in the future;
this GitHub issue is open to consider changing this behavior. Because this is desirable
in some situations, we'll likely add a simple-update-where! function that maintains the original functionality.
Deleting objects with Toucan follows similar patterns to the other CRUD operations.
The basic delete function in Toucan is predictably called delete!:
(delete! User :id 1)delete! takes a model and a key-value map of conditions and deletes all objects that match. Arguments are passed directly to select,
which means anything that words there will work with delete!. Before deleting objects, the model's implementation of pre-delete, if any,
is called on each object about to be deleted. This is a good opportunity to check preconditions (and thus abort the delete if needed) or
delete dependent or child objects. The output of pre-delete is ignored.
NOTE: Like a few other Toucan functions, delete! is designed to work with objects that have a primary key named :id. If your models
don't follow this pattern, you can use simple-delete! instead. This is something we plan to address in a future release of Toucan.
simple-delete! is similar to delete!, but doesn't pass objects about to be deleted to pre-delete.
;; delete labels where :name == "Cam"
(db/simple-delete! Label :name "Cam")
;; for flexibility either a single map or kwargs are accepted
(db/simple-delete! Label {:name "Cam"})simple-delete! doesn't assume objects have a primary key named :id, so it's useful in situations where that's not the case.
As mentioned in the guide to defining models, each Toucan model lives in its own namespace; this can sometimes
lead to circular dependencies between namespaces. As a convenience, the various functions in toucan.db can automatically resolve
quoted models:
(db/select User ...)
;; ...is the same as...
(db/select 'User ...)Such resolution automatically requires the correct namespace as needed. (Part of the reason each Toucan model must live in its
own namespace is to facilitate the automatic model resolution).
Automatic model resolution is also extremely handy when developing from the REPL; toucan.db functions lend themselves well to
such REPL-driven development.
As mentioned in the models guide, select returns instances of the record type associated with a given model.
In other words, if you've defined a User model:
(defmodel User :users) ;; User model corresponds to the :users tablethen things returned by select will have class of UserInstance:
(db/select User)
;; -> [#models.user_instance.UserInstance{:id 1. :first-name "Cam", :last-name "Saul", ...}
;; ...]This is handy for several reasons.
You can easily define additional protocols and provide individual
implementations for different models, or otherwise implement different behavior based on object classes. For example, let's
say we wanted to implement a permissions checking system in a protocol called IPermissions that checks whether the current user
(e.g., one associated with some action such as an API request) has permissions to update a given object. For a User, you could
say admins have permissions to modify any User, and non-admins only have permissions to modify themselves:
(defprotocol IPermissions
(can-write? [this]))
(defmodel User :user_table
IPermissions
(can-write? [user]
(or (current-user-is-admin?)
(is-current-user? user))))This could be taken a step further, and you could implement automatic permissions checking by defining a custom property:
(models/add-property! :write-check?
:update (fn [obj]
(assert (can-write? obj)
"You don't have permissions to do that.")
obj))
(defmodel User :user_table
models/IModel
(properties [_]
{:write-check? true}))Now whenever you try to update! an object with the :write-check? property (such as User), can-write? must return a truthy
value, or the update will be aborted. (Properties are discussed in detail in the models guide; the example
above is provided to give you a sense of what you can do with Toucan).
There's more benefits to having record-typed objects. You can also call any IModel method on any instance of a model:
(models/default-fields some-user) ; -> [:id :first-name :last-name]Toucan makes it easy to run queries inside transactions with the transaction macro.
(require '[honeysql.core :as hsql])
;; send some money from User 1 to User 2.
(db/transaction
(db/update! User 1 :account_balance (hsql/call :- :account_balance 100) ; SET account_balance = account_balance - 100
(db/update! User 2 :account_balance (hsql/call :+ :account_balance 100)) ; SET account_balance = account_balance + 100Toucan is powerful and has helper functions for doing most kinds of simple queries, but there are a few that it can't do just
yet (JOINs come to mind). Toucan offers a pair of low-level functions for these situatuions.
query takes a raw HoneySQL form, compiles it to SQL, and passes it to jdbc/query along with the current DB connection.
(db/query {:select [:%count.*]
:from [:users]
:where [:= :first-name "Cam"]})
;; -> [{:count 1}]Similiarly, execute! can be used in cases where you'd use jdbc/execute! instead of jdbc/query (i.e., for queries that return
no results):
(db/execute! {:delete-from :users
:where [:not= :name "Cam"]})Sometimes Toucan queries don't work the way you'd expect. Luckily, Toucan makes it easy to see exactly what's going on under the
hood with the debug-print-queries macro. debug-print-queries will print (to stdout) the compiled HoneySQL form and the
compiled SQL (along with prepared statement arguments), all while returning results as normal:
(debug-print-queries
(select-one User :id 10))In stdout (the REPL or console terminal), you'll see:
{:select [:*],
:from [{:table :users, :name "User", :toucan.models/model true}],
:where [:= :id 10],
:limit 1}
nil
SELECT *
FROM "users"
WHERE "id" = ?
LIMIT ?
(10 1)This makes debugging fast and easy.
Similarly, the debug-count-calls macro can be used to count the number of calls made to toucan.db functions.
(debug-count-calls
(select-one 'User)
(select-one 'User))in stdout you'll see:
DB Calls: 2This is useful for performance tuning. If you'd like to use this number programatically, the with-call-counting macro
lets you provide a binding for a function that can be used to fetch the current call count; refer to the source for more details.
Note that this information is more of a rough estimate than a scientifically accurate count of DB calls; some Toucan functions
aren't yet set up to count calls, and non-Toucan DB calls (i.e., DB calls that bypass Toucan and hit clojure.java.jdbc directly)
are not counted. Nonetheless, the entire select family of functions is counted, and that is often enough to point you in the right
direction when performance tuning an application.