dbee.core — tessellator/dbee 0.2.0

Functions to query databases and a macro to generate a local database API.

The functions in this namespace provide a java.jdbc-like API for querying databases using HoneySQL queries. It assumes you will manage database connections in your application.

dbee.core/defdb will create an opinionated local database API based on the functions defined in this namespace. For configuration options and example usages, refer to the defdb docstring.

The functions used with either use case accept a number of common options. Those options include the following:

• :long-running-threshold

The number of milliseconds to use as a long-running threshold. If queries exceed this value, a warning will be logged that includes the query and the run time. This value will override any default or configured value.

• :row-fn

A function that will be executed against every row returned from the database. This value will override any default or configured value.

• :result-set-fn

A function that will be applied to the entire returned result set.

aggregate clj

(aggregate conn query aggregate field)

Executes the specified aggregate function on the field from query.

Supported aggregate functions include :avg, :count, :max, :min, and sum.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

Examples:

;; Get the record count of the users table:
(aggregate :users :count :id)

all clj

(all conn query)

(all conn query opts)

Fetches all records from the database matching the given query.

This function will expand query so abbreviated forms may be used.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

Examples:

;; get all users
(all :users)

;; get all users with long-running threshold of 100ms
(all :users {:long-running-threshold 100})

;; get all with a HoneySQL query
(all {:from [:users] :select [:id :name :username]})

by clj

(by m)

(by query m)

Creates or updates a query with a where clause based on the kv pairs in m.

The resulting query will have an updated where clause that searches for each column specified by the keys in m to equal their associated values.

This function will expand query so abbreviated forms may be used.

Examples:

;; Search for all users with the name "John".
(all (-> :users
         (by {:name "John"}))

defdb clj macro

(defdb config)

Unpacks an opinionated API implementation into the calling namespace.

This macro will create a database connection pool in the calling namespace and create functions that wrap the database functions in dbee.core with the connection pool.

If using this macro, it is very possible that the only reference to the dbee library will be to call this macro; everything else can use the unpacked functions defined in the calling namespace.

The config value will be wrapped in a delay, so you can use functions to populate the configuration map without worry of them being executed at compile time.

Because this will unpack functions with the names get and update, you will likely want to include the following line of code in your ns definition:

(:refer-clojure :exclude [get update])

Configuration options include the following:

• hikari-cp configuration options Refer to the hikari-cp docs. It should be noted that dbee does not make any assumptions about which adapter you are using, so you must provide the specific dependency in your project. This is the same behavior as hikari-cp.

• :row-fn A function to be executed on every query unless overridden. An example of how you might want to use this is to convert all keys in records returned from queries from snake_case to kebab-case. This value can be overridden by providing a different :row-fn function in the opts map of a query function.

  Default: identity

• :long-running-threshold The number of milliseconds to use as a long-running threshold. If queries exceed this value, a warning will be logged that includes the query and the run time. This value can be overridden by providing a different value for :long-running-threshold in the opts map of a query function.

  Default: 500

Examples:

;; Simple configuration
(defdb {:adapter "postgresql"
        :database-name "my_database"
        :server-name "localhost"
        :username "postgres"
        :password ""
        :maximum-pool-size 10})

;; Configuration happens at runtime, so the following will load the
;; username and password when your application is running (not at compile
;; time)
(defdb {:adapter "postgresql"
        :database-name "my_database"
        :server-name "localhost"
        :username (load-my-username)
        :password (load-my-password)
        :maximum-pool-size 10})

;; Configuration with queries returning kebab-case by default (using the
;; camel-snake-kebab library) and with a shorter long-running-threshold
(defdb {:adapter "postgresql"
        :database-name "my_database"
        :server-name "localhost"
        :username "postgres"
        :password ""
        :maximum-pool-size 10
        :row-fn (partial cske/transform-keys csk/->kebab-case)
        :long-running-threshold 100})

delete clj

(delete conn table id-or-record)

(delete conn table id-or-record opts)

Deletes id-or-record from table and returns affected row count.

The primary key of a record is assumed to be named id. This can be overridden by providing the name of the column as the value associated with :primary-key in opts.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

Examples:

;; Delete the user with id = 1
(delete :users 1)

;; Delete the user specified by the record, which has id = 1
(delete :users {:id 1 :name "John" :username "jdoe"})

;; Delete user the user with :user_id = 1
(delete :users 1 {:primary-key :user_id})

delete-all clj

(delete-all conn table query)

(delete-all conn table query opts)

Deletes records from table matching query and returns affected row count.

Examples:

;; delete all users with id > 15
(delete-all :users {:where [:> :id 15]})

execute clj

(execute conn query)

(execute conn query opts)

Executes the specified query.

This function will return a map with the following keys:

:query - the expanded HoneySQL query executed :ms - the elapsed time during the period of this call :sql - the raw SQL executed :result - the query result

A warning containing the run time and query will be logged if the elapsed time exceeds a long-running threshold. The threshold is 500ms by default but may be set by providing a :long-running-threshold value in opts.

If an exception is thrown during the execution of the query, the exception is logged with the elapsed time and raw SQL. The exception is re-thrown.

This function will expand query so that abbreviated forms may be used.

exists? clj

(exists? conn query)

(exists? conn query opts)

Returns a value indicating whether any records match query.

For performance reasons, the query is executed and limited to returning one result.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

Examples:

;; Are there any users with the name John?
(exists? (by :users {:name "John"}))

expand-query clj

(expand-query query)

Expands a query from an abbreviated form into a valid HoneySQL query.

The expansion works as follows:

map -> returned as-is (assumed to be a query) nil -> empty query keyword -> select-all query with keyword as table name function -> calls the function (assumed to be a generator) otherwise -> throws clojure.lang.ExceptionInfo

get clj

(get conn query id)

(get conn query id opts)

Fetches a single record from the database with the specified primary key.

The primary key is assumed to be a column named id. This can be overridden by providing the name of the column as the value associated with :primary-key in opts.

Throws an exception if more than one matching record is found.

Returns nil if no matching record is found. You can use dbee.core/get! to throw an exception instead if desired.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

See also: dbee.core/one and dbee.core/get-by

Examples:

;; Get the record from the users table with id 2
(get :users 2)

;; Get the record from the users table with user_id 2
(get :users 2 {:primary-key :user_id})

get! clj

(get! conn query id)

(get! conn query id opts)

Fetches a single record from the database with the specified primary key.

The primary key is assumed to be a column named id. This can be overridden by providing the name of the column as the value associated with :primary-key in opts.

Throws an exception if more than one matching record is found.

Unlike dbee.core/get, this function will also throw an exception if no matching record is found.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

See also: dbee.core/one! and dbee.core/get-by!

Examples:

;; Get the record from the users table with id 2
(get! :users 2)

;; Get the record from the users table with user_id 2
(get! :users 2 {:primary-key :user_id})

get-by clj

(get-by conn query m)

(get-by conn query m opts)

Fetches a single record using query and the kv-pairs in m.

m is expanded into a set of where clauses where the column with each key is equal to the associated value.

Throws an exception if more than one matching record is found.

Returns nil if no matching record is found. You can use dbee.core/get-by! to throw an exception instead if desired.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

See also: dbee.core/one and dbee.core/get

Examples:

;; Get the record from the users table where the name is "John" and the
;; username is "jdoe"
(get-by :users {:name "John" :username "jdoe"})

get-by! clj

(get-by! conn query m)

(get-by! conn query m opts)

Fetches a single record using query and the kv-pairs in m.

m is expanded into a set of where clauses where the column with each key is equal to the associated value.

Throws an exception if more than one matching record is found.

Unlike dbee.core/get-by!, this function will also throw an exception if no matching record is found.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

This function will expand query so abbreviated forms may be used.

See also: dbee.core/one! and dbee.core/get!

Examples:

;; Get the record from the users table where the name is "John" and the
;; username is "jdoe"
(get-by! :users {:name "John" :username "jdoe"})

in-transaction? clj

(in-transaction?)

Indicates whether current execution is inside a transaction.

insert clj

(insert conn table record)

(insert conn table record opts)

Inserts record into table and returns the resulting record.

Each key-value pair in record will be treated as a column name and the associated value.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

Examples:

(insert :users {:name "John" :username "jdoe"})

insert-all clj

(insert-all conn table records)

(insert-all conn table records opts)

Inserts records into table and returns affected row count.

This function will determine which columns to use for insertion based on the distinct collection of keys from all of the records unless other specified.

A :columns value may be provided in opts that is a sequence of column names to be used for update. Providing :columns will override the calculation of the distinct collection of keys as previously described.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

Examples:

(insert-all :users [{:name "John" :username "jdoe"}
                    {:name "Jane" :last_name "Doe"}])

one clj

(one conn query)

(one conn query opts)

Fetches a single result from the database matching the given query.

Throws an exception if more than one matching record is found.

Returns nil if no matching record is found. You can use dbee.core/one! to throw an exception instead if desired.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

See also: dbee.core/get and dbee.core/get-by

Examples:

;; Get the one user record where the username is "jdoe"
(one {:from [:users] :where [:= :username "jdoe"]})

one! clj

(one! conn query)

(one! conn query opts)

Fetches a single result from the database matching the given query.

Throws an exception if more than one matching record is found.

Unlike dbee.core/one, this function will also throw an exception if no matching record is found.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

See also: dbee.core/get! and dbee.core/get-by!

Examples:

;; Get the one user record where the username is "jdoe"
(one {:from [:users] :where [:= :username "jdoe"]})

rollback clj

(rollback)

Marks the transaction for rollback.

Throws an exception if the current execution is not inside a transaction.

update clj

(update conn table record)

(update conn table record opts)

Updates record in table and returns the updated record.

Each key-value pair in the record except the one representing the primary key is used to set new values using the key as the column name and the associated value as the new value for that column. Note that only the keys present in record will be updated.

The primary key of a record is assumed to be named id. This can be overridden by providing the name of the column as the value associated with :primary-key in opts. Throws an exception if the primary key cannot be found in the record.

opts is an optional map that may contain any of the shared options. Refer to the dbee.core namespace docstring for information about shared options.

Examples:

;; Assume we have a record {:id 1 :name "John" :username "jdoe"}
;; Update John's name to Jane and leave the username as jdoe
(update :users {:id 1 :name "Jane"})

;; Update John's name to Jane with the primary key being on the user_id column
(update :users {:user_id 1 :name "Jane"} {:primary-key :user_id})

with-transaction clj macro

(with-transaction conn & body)

Executes body within the context of a transaction created with conn.