metosin.jdbc — metosin/metosin-common 0.4.2

Wraps clojure.jdbc with optionated entities and identifiers fn.

create-table-ddl clj

(create-table-ddl table specs)

(create-table-ddl table specs opts)

Given a table name and a vector of column specs, return the DDL string for creating that table. Each column spec is, in turn, a vector of keywords or strings that is converted to strings and concatenated with spaces to form a single column description in DDL, e.g., [:cost :int "not null"] [:name "varchar(32)"] The first element of a column spec is treated as a SQL entity (so if you provide the :entities option, that will be used to transform it). The remaining elements are left as-is when converting them to strings. An options map may be provided that can contain: :table-spec -- a string that is appended to the DDL -- and/or :entities -- a function to specify how column names are transformed. :conditional? -- either a boolean, indicating whether to add 'IF NOT EXISTS', or a string, which is inserted literally before the table name, or a function of two arguments (table name and the create statement), that can manipulate the generated statement to better support other databases, e.g., MS SQL Server which need to wrap create table in an existence query.

db-do-commands clj

(db-do-commands db sql-commands)

(db-do-commands db transaction? sql-commands)

Executes SQL commands on the specified database connection. Wraps the commands in a transaction if transaction? is true. transaction? can be omitted and it defaults to true. Accepts a single SQL command (string) or a vector of them. Uses executeBatch. This may affect what SQL you can run via db-do-commands.

db-do-prepared clj

(db-do-prepared db sql-params)

(db-do-prepared db transaction? sql-params)

(db-do-prepared db transaction? sql-params opts)

Executes an (optionally parameterized) SQL prepared statement on the open database connection. Each param-group is a seq of values for all of the parameters. transaction? can be omitted and defaults to true. The sql parameter can either be a SQL string or a PreparedStatement. Return a seq of update counts (one count for each param-group).

delete! clj

(delete! db table where-clause)

(delete! db table where-clause options)

Given a database connection, a table name and a where clause of columns to match, perform a delete. The options may specify how to transform column names in the map (default 'as-is') and whether to run the delete in a transaction (default true). Example: (delete! db :person ["zip = ?" 94546]) is equivalent to: (execute! db ["DELETE FROM person WHERE zip = ?" 94546])

entities clj

(entities x)

execute! clj

(execute! db sql-params)

(execute! db sql-params opts)

Given a database connection and a vector containing SQL (or PreparedStatement) followed by optional parameters, perform a general (non-select) SQL operation.

The :transaction? option specifies whether to run the operation in a transaction or not (default true).

If the :multi? option is false (the default), the SQL statement should be followed by the parameters for that statement.

If the :multi? option is true, the SQL statement should be followed by one or more vectors of parameters, one for each application of the SQL statement.

If :return-keys is provided, db-do-prepared-return-keys will be called instead of db-do-prepared, and the result will be a sequence of maps containing the generated keys. If present, :row-fn will be applied. If :multi? then :result-set-fn will also be applied if present. :as-arrays? may also be specified (which will affect what :result-set-fn is passed).

If there are no parameters specified, executeUpdate will be used, otherwise executeBatch will be used. This may affect what SQL you can run via execute!

find-by-keys clj

(find-by-keys db table columns)

(find-by-keys db table columns opts)

Given a database connection, a table name, a map of column name/value pairs, and an optional options map, return any matching rows.

An :order-by option may be supplied to sort the rows, e.g.,

{:order-by [{:name :asc} {:age :desc} {:income :asc}]}
;; equivalent to:
{:order-by [:name {:age :desc} :income]}

The :order-by value is a sequence of column names (to sort in ascending order) and/or maps from column names to directions (:asc or :desc). The directions may be strings or keywords and are not case-sensitive. They are mapped to ASC or DESC in the generated SQL.

Note: if a ordering map has more than one key, the order of the columns in the generated SQL ORDER BY clause is unspecified (so such maps should only contain one key/value pair).

get-by-id clj

(get-by-id db table columns)

(get-by-id db table columns opts)

Given a database connection, a table name, a primary key value, an optional primary key column name, and an optional options map, return a single matching row, or nil. The primary key column name defaults to :id.

identifiers clj

(identifiers x)

insert! clj

(insert! db table row)

(insert! db table cols-or-row values-or-opts)

(insert! db table cols values opts)

Given a database connection, a table name and either a map representing a rows, or a list of column names followed by a list of column values also representing a single row, perform an insert. When inserting a row as a map, the result is the database-specific form of the generated keys, if available (note: PostgreSQL returns the whole row). When inserting a row as a list of column values, the result is the count of rows affected (1), if available (from getUpdateCount after executeBatch). The row map or column value vector may be followed by a map of options: The :transaction? option specifies whether to run in a transaction or not. The default is true (use a transaction). The :entities option specifies how to convert the table name and column names to SQL entities.

insert-multi! clj

(insert-multi! db table rows)

(insert-multi! db table cols-or-rows values-or-opts)

(insert-multi! db table cols values opts)

Given a database connection, a table name and either a sequence of maps (for rows) or a sequence of column names, followed by a sequence of vectors (for the values in each row), and possibly a map of options, insert that data into the database.

When inserting rows as a sequence of maps, the result is a sequence of the generated keys, if available (note: PostgreSQL returns the whole rows). A separate database operation is used for each row inserted. This may be slow for if a large sequence of maps is provided.

When inserting rows as a sequence of lists of column values, the result is a sequence of the counts of rows affected (a sequence of 1's), if available. Yes, that is singularly unhelpful. Thank you getUpdateCount and executeBatch! A single database operation is used to insert all the rows at once. This may be much faster than inserting a sequence of rows (which performs an insert for each map in the sequence).

The :transaction? option specifies whether to run in a transaction or not. The default is true (use a transaction). The :entities option specifies how to convert the table name and column names to SQL entities.

query clj

(query db sql-params)

(query db sql-params options)

Given a database connection and a vector containing SQL and optional parameters, perform a simple database query. The options specify how to construct the result set (and are also passed to prepare-statement as needed): :as-arrays? - return the results as a set of arrays, default false. :identifiers - applied to each column name in the result set, default lower-case :keywordize? - defaults to true, can be false to opt-out of converting identifiers to keywords :qualifier - optionally provides the namespace qualifier for identifiers :result-set-fn - applied to the entire result set, default doall / vec if :as-arrays? true, :result-set-fn will default to vec if :as-arrays? false, :result-set-fn will default to doall :row-fn - applied to each row as the result set is constructed, default identity The second argument is a vector containing a SQL string or PreparedStatement, followed by any parameters it needs. See also prepare-statement for additional options.

update! clj

(update! db table set-map where-clause)

(update! db table set-map where-clause options)

Given a database connection, a table name, a map of column values to set and a where clause of columns to match, perform an update. The options may specify how column names (in the set / match maps) should be transformed (default 'as-is') and whether to run the update in a transaction (default true). Example: (update! db :person {:zip 94540} ["zip = ?" 94546]) is equivalent to: (execute! db ["UPDATE person SET zip = ? WHERE zip = ?" 94540 94546])

with-db-connection clj/s macro

(with-db-connection binding & body)

Evaluates body in the context of an active connection to the database. (with-db-connection [con-db db-spec opts] ... con-db ...)

with-db-metadata clj/s macro

(with-db-metadata binding & body)

Evaluates body in the context of an active connection with metadata bound to the specified name. See also metadata-result for dealing with the results of operations that retrieve information from the metadata. (with-db-metadata [md db-spec opts] ... md ...)

with-db-transaction clj/s macro

(with-db-transaction binding & body)

Evaluates body in the context of a transaction on the specified database connection. The binding provides the database connection for the transaction and the name to which that is bound for evaluation of the body. The binding may also specify the isolation level for the transaction, via the :isolation option and/or set the transaction to readonly via the :read-only? option. (with-db-transaction [t-con db-spec {:isolation level :read-only? true}] ... t-con ...) See db-transaction* for more details.