Fork me on GitHub
#sql
<
2022-01-25
>
pinkfrog10:01:02

How can I enforce a (execute-one! ) is read-only and does not modify database?

pinkfrog10:01:40

Seems like I can set it a java.sql.conneciton level, but how to achieve that per execute-one! call? Ideally, if the opts parameter supports it then it would be perfect.

dharrigan11:01:00

In the past, I've done something like this:

dharrigan11:01:33

(defn plan-and-stream
  "Plan And Stream takes control of the entire connection in order to fetch a large amount
   of data as a streaming result that is reduced by the callee - it doesn't use a connection
   pool as it needs to customise the connection to support streaming."
  ([sql app-config] (plan-and-stream sql app-config {:builder-fn rs/as-unqualified-kebab-maps}))
  ([sql app-config opts]
   (log/tracef "Executing JDBC '%s'." sql)
   (try
    (let [{{:keys [txi-db]} :secrets} app-config
          {:keys [username]} txi-db
          datasource (jdbc/get-datasource (merge txi-db {:user username :auto-commit false :read-only true}))]
      (jdbc/plan datasource sql (merge opts {:fetch-size 5000 :concurrency :read-only :cursors :close :result-type :forward-only})))
    (catch Exception e
      (log/error e)
      (throw e)))))

dharrigan11:01:49

I basically create a new read-only connection for that specific use-case.

dharrigan11:01:14

instead of jdbc/plan, you can do jdbc/execute or whatever 🙂

pinkfrog12:01:35

Btw, your startrek is a great read.

dharrigan12:01:38

np, you're most welcome. Perhaps it may give you some hints 🙂

dharrigan12:01:54

I have to brush that project up a bit, add some more stuff to it

dharrigan12:01:02

but thank you none-the-less 🙂

seancorfield18:01:18

FYI: whether that really sets a connection to read-only is database-specific -- so don't rely on it for security. You're better off setting up credentials on the database that restrict the operations you can do, e.g., only SELECT statements.

👍 2