korma.core documentation

Core querying and entity functions

aggregate

(aggregate query agg alias & [group-by])
Use a SQL aggregator function, aliasing the results, and optionally grouping by
a field:

(select users 
  (aggregate (count :*) :cnt :status))

Aggregates available: count, sum, avg, min, max, first, last

as-sql

(as-sql query)
Force a query to return a string of SQL when (exec) is called.

belongs-to

(belongs-to ent sub-ent & [opts])
Add a belongs-to relationship for the given entity. It is assumed that the foreign key
is on the current entity with the format sub-ent-table_id: email.user_id = user.id.
Opts can include a key for :fk to explicitly set the foreign key.

(belongs-to users email {:fk :emailID})

create-entity

(create-entity table)
Create an entity representing a table in a database.

create-relation

(create-relation ent sub-ent type opts)
Create a relation map describing how two entities are related.

database

(database ent db)
Set the database connection to be used for this entity.

defentity

(defentity ent & body)
Define an entity representing a table in the database, applying any modifications in
the body.

delete

(delete ent & body)
Creates a delete query, applies any modifying functions in the body and then
executes it. `ent` is either a string or an entity created by defentity.

ex: (delete user 
      (where {:id 7}))

delete*

(delete* ent)
Create an empty delete query. Ent can either be an entity defined by defentity,
or a string of the table name

dry-run

(dry-run & body)
Wrap around a set of queries to print to the console all SQL that would 
be run and return dummy values instead of executing them.

entity-fields

(entity-fields ent & fields)
Set the fields to be retrieved by default in select queries for the
entity.

exec

(exec query)
Execute a query map and return the results.

exec-raw

(exec-raw conn? & [sql with-results?])
Execute a raw SQL string, supplying whether results should be returned. `sql` can either be
a string or a vector of the sql string and its params. You can also optionally
provide the connection to execute against as the first parameter.

(exec-raw ["SELECT * FROM users WHERE age > ?" [5]] :results)

fields

(fields query & vs)
Set the fields to be selected in a query. Fields can either be a keyword
or a vector of two keywords [field alias]:

(fields query :name [:firstname :first])

from

(from query table)
Add tables to the from clause.

group

(group query & fields)
Add a group-by clause to a select query

has-many

(has-many ent sub-ent & [opts])
Add a has-many relation for the given entity. It is assumed that the foreign key
is on the sub-entity with the format table_id: user.id = email.user_id
Opts can include a key for :fk to explicitly set the foreign key.

(has-many users email {:fk :emailID})

has-one

(has-one ent sub-ent & [opts])
Add a has-one relationship for the given entity. It is assumed that the foreign key
is on the sub-entity with the format table_id: user.id = address.user_id
Opts can include a key for :fk to explicitly set the foreign key.

(has-one users address {:fk :addressID})

insert

(insert ent & body)
Creates an insert query, applies any modifying functions in the body and then
executes it. `ent` is either a string or an entity created by defentity. Inserts
return the last inserted id.

ex: (insert user 
      (values [{:name "chris"} {:name "john"}]))

insert*

(insert* ent)
Create an empty insert query. Ent can either be an entity defined by defentity,
or a string of the table name

join

(join query ent)(join query table clause)(join query type table clause)
Add a join clause to a select query, specifying the table name to join and the predicate
to join on.

(join query addresses)
(join query addresses (= :addres.users_id :users.id))
(join query :right addresses (= :address.users_id :users.id))

limit

(limit query v)
Add a limit clause to a select query.

modifier

(modifier query & modifiers)
Add a modifer to the beginning of a query:

(select orders
  (modifier "DISTINCT"))

offset

(offset query v)
Add an offset clause to a select query.

order

(order query field & [dir])
Add an ORDER BY clause to a select query. field should be a keyword of the field name, dir
is ASC by default.

(order query :created :asc)

pk

(pk ent pk)
Set the primary key used for an entity. :id by default.

post-query

(post-query query post)
Add a function representing a query that should be executed for each result in a select.
This is done lazily over the result set.

prepare

(prepare ent func)
Add a function to be applied to records/values going into the database

query-only

(query-only & body)
Wrap around a set of queries to force them to return their query objects.

raw

Embed a raw string of SQL in a query. This is used when Korma doesn't
provide some specific functionality you're looking for:

(select users
  (fields (raw "PERIOD(NOW(), NOW())")))

select

(select ent & body)
Creates a select query, applies any modifying functions in the body and then
executes it. `ent` is either a string or an entity created by defentity.

ex: (select user 
      (fields :name :email)
      (where {:id 2}))

select*

(select* ent)
Create an empty select query. Ent can either be an entity defined by defentity,
or a string of the table name

set-fields

(set-fields query fields-map)
Set the fields and values for an update query.

sql-only

(sql-only & body)
Wrap around a set of queries so that instead of executing, each will return a string of the SQL 
that would be used.

sqlfn

(sqlfn func & params)
Call an arbitrary SQL function by providing func as a symbol or keyword
and its params

sqlfn*

(sqlfn* fn-name & params)
Call an arbitrary SQL function by providing the name of the function
and its params

subselect

(subselect & parts)
Create a subselect clause to be used in queries. This works exactly like (select ...)
execept it will wrap the query in ( .. ) and make sure it can be used in any current
query:

(select users
  (where {:id [in (subselect users2 (fields :id))]}))

table

(table ent t & [alias])
Set the name of the table and an optional alias to be used for the entity. 
By default the table is the name of entity's symbol.

transform

(transform ent func)
Add a function to be applied to results coming from the database

update

(update ent & body)
Creates an update query, applies any modifying functions in the body and then
executes it. `ent` is either a string or an entity created by defentity.

ex: (update user 
      (set-fields {:name "chris"}) 
      (where {:id 4}))

update*

(update* ent)
Create an empty update query. Ent can either be an entity defined by defentity,
or a string of the table name.

values

(values query values)
Add records to an insert clause. values can either be a vector of maps or a single
map.

(values query [{:name "john"} {:name "ed"}])

where

(where query form)
Add a where clause to the query, expressing the clause in clojure expressions
with keywords used to reference fields.
e.g. (where query (or (= :hits 1) (> :hits 5)))

Available predicates: and, or, =, not=, <, >, <=, >=, in, like, not

Where can also take a map at any point and will create a clause that compares keys
to values. The value can be a vector with one of the above predicate functions 
describing how the key is related to the value: (where query {:name [like "chris"})

where*

(where* query clause)
Add a where clause to the query. Clause can be either a map or a string, and
will be AND'ed to the other clauses.

with

(with query ent & body)
Add a related entity to the given select query. If the entity has a relationship
type of :belongs-to or :has-one, the requested fields will be returned directly in
the result map. If the entity is a :has-many, a second query will be executed lazily
and a key of the entity name will be assoc'd with a vector of the results.

(defentity email (entity-fields :email))
(defentity user (has-many email))
(select user
  (with email) => [{:name "chris" :email [{email: "c@c.com"}]} ...

With can also take a body that will further refine the relation:
(select user
   (with address
      (with state)
      (fields :address.city :state.state)
      (where {:address.zip x})))