Database Code Patterns

This document describes the code patterns used for database operations in Orcha.

See also: migrations.md for migration-specific patterns.

Core Libraries

Case Conversion

Clojure uses kebab-case (:tenant-id), PostgreSQL uses snake_case (tenant_id).

Automatic Conversion (Output)

The connection pool is configured with jdbc/with-options to automatically convert query results to qualified kebab-case keys:

;; In com.getorcha.erp.db
(jdbc/with-options
  {:datasource pool}
  {:builder-fn jdbc.rs/as-kebab-maps})

Qualified Keys

Query results return qualified keys (table-prefixed):

;; Query result example
{:tenant/id #uuid "..."
 :tenant/name "Orcha"
 :tenant/slug "orcha"}

This enables:

• Better namespacing
• Avoids key collisions in joins
• Clear origin of each field

SQL Helper Functions

Located in src/com/getorcha/db/sql.clj:

execute! / execute-one!

Thin wrappers around jdbc/execute! and jdbc/execute-one! that format HoneySQL maps:

(require '[com.getorcha.db.sql :as sql])

;; Multiple results
(sql/execute! conn
  {:select [:id :name :slug]
   :from   [:tenant]
   :where  [:= :is-active true]})
;; => [{:tenant/id ... :tenant/name ... :tenant/slug ...} ...]

;; Single result
(sql/execute-one! conn
  {:select [:*]
   :from   [:tenant]
   :where  [:= :slug "orcha"]})
;; => {:tenant/id ... :tenant/name "Orcha" ...}

;; Insert with return
(sql/execute-one! conn
  {:insert-into :tenant
   :values [{:name "Acme" :slug "acme"}]}
  {:return-keys true})
;; => {:tenant/id #uuid "..." :tenant/name "Acme" ...}

->cast

Helper for PostgreSQL enum casting with automatic snake_case conversion:

;; Converts type name to snake_case automatically
(sql/->cast "webhook" :doc-source-type)
;; => [:cast "webhook" :doc_source_type]

;; Usage in insert
(sql/execute-one! conn
  {:insert-into :ap-doc-source
   :values [{:tenant-id tenant-id
             :type      (sql/->cast "webhook" :doc-source-type)}]}
  {:return-keys true})

HoneySQL Patterns

Basic Queries

;; SELECT
{:select [:id :name]
 :from   [:tenant]
 :where  [:= :slug "orcha"]}

;; INSERT
{:insert-into :tenant
 :values [{:name "Orcha" :slug "orcha"}]}

;; UPDATE
{:update :tenant
 :set    {:name "New Name"}
 :where  [:= :id some-uuid]}

;; DELETE
{:delete-from :tenant
 :where [:= :slug "orcha"]}

Joins

{:select [:t.name :ds.type]
 :from   [[:tenant :t]]
 :join   [[:ap-doc-source :ds] [:= :t.id :ds.tenant-id]]
 :where  [:= :t.slug "orcha"]}

Subqueries

{:delete-from :ap-doc-source
 :where [:in :id {:select [:ds.id]
                  :from   [[:ap-doc-source :ds]]
                  :join   [[:tenant :t] [:= :ds.tenant-id :t.id]]
                  :where  [:= :t.slug "orcha"]}]}

PostgreSQL Enum Casting

PostgreSQL enums require explicit casting. Use sql/->cast:

(sql/execute-one! conn
  {:insert-into :ap-doc-source
   :values [{:tenant-id tenant-id
             :type      (sql/->cast "webhook" :doc-source-type)}]}
  {:return-keys true})

The ->cast helper automatically converts the type name to snake_case, so you can use kebab-case (:doc-source-type becomes :doc_source_type).

Common Enums

Raw SQL Expressions

For PostgreSQL-specific functions, use [:raw ...]:

(sql/execute-one! conn
  {:insert-into :ap-doc-source-webhook
   :values [{:doc-source-id doc-source-id
             :endpoint-path "/webhook/orcha"
             :secret-hash   [:raw "encode(gen_random_bytes(32), 'hex')"]}]})

Connection Handling

The database pool is an Integrant component at ::db/pool. It's pre-configured with kebab-case result conversion.

;; In a handler or component
(let [db (:com.getorcha.db/pool system)]
  (sql/execute! db
    {:select [:*] :from [:tenant]}))

Transactions

For operations that need to be atomic, use next.jdbc/with-transaction:

(require '[next.jdbc :as jdbc])

(jdbc/with-transaction [tx conn]
  (let [tenant     (sql/execute-one! tx
                     {:insert-into :tenant
                      :values [{:name "Acme" :slug "acme"}]}
                     {:return-keys true})
        doc-source (sql/execute-one! tx
                     {:insert-into :ap-doc-source
                      :values [{:tenant-id (:tenant/id tenant)
                                :type      (sql/->cast "webhook" :doc-source-type)}]}
                     {:return-keys true})]
    (sql/execute-one! tx
      {:insert-into :ap-doc-source-webhook
       :values [{:doc-source-id (:ap-doc-source/id doc-source)
                 :endpoint-path "/webhook/acme"
                 :secret-hash   [:raw "encode(gen_random_bytes(32), 'hex')"]}]})))