sql 2024-01-03 | Slack Archive

Ravi12:01:56

Hi folks, I am new to next jdbc 1. I am trying to use join using next.jdbc ! Currently, since i am doing the left join on the same table, the output has the keys with the table name associated with it for eg, :emptable/id, now is there a way where i can just get id for it ? 2. I am currently using plain query with execute! using preparedStatement of next.jdbc is there any better way we can do it ? is the execute! along with preparedStatement safe from sql injection ?

igrishaev13:01:06

1. in your query, specify the aliases explicitly, for example:

select
 t1.id as id,
 t2.id as parent_id
etc

2). The execute! call creates a one-time PreparedStatement under the hood. use PreparedStatement only if you're going to call that query in a loop

igrishaev13:01:05

for example:

(let [stmt (jdbc/prepare ....)
  (doseq ...
    (jdbc/execute stmt params)))

igrishaev13:01:45

To prevent sql injections, just pass all the parameters through params, not as a part of a query. Long story short, never use format or str for building a query, and you're good

🙌 1

Ravi14:01:05

@U1WAUKQ3E I am currently passing the alias explicitly

SELECT parent.employee_id AS id,
                                          parent.name AS name,

but since maybe i am using Left JOIN on same table it is working little weird and I am also getting the table name as :employee/id

igrishaev15:01:58

To omit the table names, pass the :as option with a value from the result-set module. Smth like this {:as rs/as-unqualified-maps} Writing by memory

👀 1

seancorfield16:01:59

It's :builder-fn but I'd recommend just using unique aliases and keeping the qualified keys.

seancorfield16:01:05

If you're working with large result sets use plan

🙌 1

seancorfield16:01:19

Otherwise use execute! You don't need to work with prepared statements directly in general. You do want to use connection pooling in production code. That's all explained in the docs.

Ravi10:01:47

@U1WAUKQ3E @U04V70XH6 thanks for the inputs I had already implemented :builder-fn in custom config for all the conn during its creation but it was overriding because instead of passing the ds i was passing a new connection ! Eg: instead of (execute! ds [params]) I was doing (execute! (:connectable ds) [params]) This did create a new conn Silly !! 😅

2024-01-03

Channels