next.jdbc

The next.jdbc.sql namespace provides "friendly" functions for common CRUD (Create, Read, Update, Delete) operations. These functions simplify basic SQL tasks by accepting table names as keywords and using Clojure data structures (like maps and vectors) instead of requiring you to write full SQL strings for every operation.

Available functions:

• insert! and insert-multi! (Create)
• query (Read - an alias for execute! when using a vector of SQL and parameters)
• update! (Update)
• delete! (Delete)
• find-by-keys and get-by-id (Specific Read operations)
• aggregate-by-keys (Aggregate Read operation)

Note: These functions are intended for simple, common operations. For complex queries, it is recommended to use the primary API (plan, execute!, execute-one!) or a SQL DSL like HoneySQL.

;; Example of the pattern
(sql/insert! ds :table-name {:column "value"})
(sql/update! ds :table-name {:column "new-value"} {:id 1})

By default, next.jdbc.sql functions use the provided keywords exactly as-is. If your table or column names are SQL reserved words, you must use quoting functions via the :table-fn and :column-fn options.

Quoting Functions (next.jdbc.quoted):

• ansi, postgres, oracle: Double quotes ("name")
• mysql: Back ticks (`name`)
• sql-server: Square brackets ([name])
• schema: A modifier to support schema-qualified names (e.g., (schema sql-server) produces [dbo].[table]).

Pre-configured Options:

• jdbc/snake-kebab-opts: Uses camel-snake-kebab to transform keywords to snake_case for both tables and columns, and performs the reverse transformation on results.
• jdbc/unqualified-snake-kebab-opts: Similar to above, but for unqualified names.

;; Using MySQL backtick quoting
(sql/insert! ds :my-table {:some "data"} {:table-fn next.jdbc.quoted/mysql})

;; Using snake_case transformations
(sql/insert! ds :my-table {:some-data "val"} jdbc/snake-kebab-opts)

;; Using schema-qualified SQL Server quoting
(sql/insert! ds :dbo.table {:col "val"} {:table-fn (next.jdbc.quoted/schema next.jdbc.quoted/sql-server)})

For high-performance data processing, use jdbc/plan instead of execute!. plan returns an IReduceInit object that can be used with reduce, transduce, or into.

Key Advantages:

• Low Overhead: It avoids creating Clojure hash maps for every row. Instead, it provides an abstraction over the underlying ResultSet. You can access columns via their SQL labels (e.g., (:unit_price row)) directly.
• Automatic Resource Management: The database connection is opened when the reduction starts and closed automatically when the reduction completes.

Important Notes:

• The row object in a reduction is not a standard Clojure map. Using functions like assoc, keys, or vals on it will force the row to be fully realized into a hash map, negating the performance benefits.
• To get a fully realized, navigable hash map within a reduction, use next.jdbc.result-set/datafiable-row.
• Do not use plan for DDL or statements that only return update counts; it is intended for result set processing.

;; Using reduce with plan
(reduce (fn [acc row] (+ acc (:unit_price row) (:unit_count row))) 
        0 
        (jdbc/plan ds ["SELECT unit_price, unit_count FROM invoice"]))

;; Using transduce with plan
(transduce (map #(apply * [% :unit_price :unit_count])) 
           + 
           0 
           (jdbc/plan ds ["SELECT unit_price, unit_count FROM invoice"]))

;; Using into with plan
(into #{} (map :product) (jdbc/plan ds ["SELECT product FROM invoice"]))

By requiring next.jdbc.datafy, the Datafiable protocol is extended to several JDBC object types. This allows you to call datafy on these objects to convert them into Clojure hash maps using Java Bean introspection (via clojure.java.data/from-java-shallow).

Key datafied types include:

• java.sql.Connection: Datafies as a bean. The :metaData property returns a datafiable java.sql.DatabaseMetaData object.
• DatabaseMetaData: Datafies as a bean with an additional :all-tables property. It includes six navigable properties that produce fully-realized datafiable result sets:
  • :all-tables (via .getTables)
  • :catalogs (via .getCatalogs)
  • :clientInfoProperties (via .getClientInfoProperties)
  • :schemas (via .getSchemas)
  • :tableTypes (via .getTableTypes)
  • :typeInfo (via .getTypeInfo)
• ParameterMetaData: Datafies as a vector of parameter description maps. Each map contains: :class, :mode (:in, :in-out, or :out), :nullability (:null, :not-null, or :unknown), :precision, :scale, :type, and :signed.
• ResultSet: Datafies as a bean. If the ResultSet is associated with a Statement and a Connection, an additional :rows key is provided containing a datafied result set (using next.jdbc.result-set/datafiable-result-set with default options).
• ResultSetMetaData: Datafies as a vector of column description maps. Each map contains metadata such as :auto-increment, :case-sensitive, :catalog, :class, :currency, :definitely-writable, :display-size, :label, :name, :nullability, :precision, :read-only, :searchable, :signed, :scale, :schema, :table, :type, and :writable.
• Statement: Datafies as a bean.