Fork me on GitHub
#sql
<
2020-03-09
>
dharrigan07:03:08

That's great documentation and good examples, thanks @seancorfield! 🙂

4
kwladyka09:03:14

(postgres/query
          ["SELECT * FROM shops WHERE uuid = ?::uuid" uuid]
          {:qualifier "shop"})
Do you work with :qualifier? I am trying to decide if I want or not. It looks improving readability of data, but on the other hand things like graphql and everything don’t support namespaces keywords, so it makes things more complex at the same time. What is your experience? Is it worth to use this? To have namespaced data structure in the system vs not namespaced?
{:user {:id 1
        :email ""}
 :shops [{:id 1
          :engine "foo"}]}

vs

{:user/id 1
 :user/email ""
 :shops [{:user/id 1
          :shop/id 1
          :shop/engine "foo"}]}

or something like that, but for example only shop returned
The example is bad, but I more and more I am thinking about this… When make sense to use :qualifier ?

dharrigan09:03:58

yes, next jdbc == bees knees

kwladyka10:03:47

oh this include JSONB, uuid etc. data types . Am I right?

dharrigan10:03:11

No, for example, if you want to support JSONB there is a nice section here on how to get next jdbc to work with it: https://cljdoc.org/d/seancorfield/next.jdbc/1.0.395/doc/getting-started/tips-tricks

kwladyka10:03:41

oh, ok I said that too fast

kwladyka10:03:46

(extend-protocol prepare/SettableParameter
  clojure.lang.IPersistentMap
  (set-parameter [m s i]
    (.setObject s i (->pgobject m))))

  clojure.lang.IPersistentVector
  (set-parameter [v s i]
    (.setObject s i (->pgobject v))))

vs

(extend-protocol prepare/SettableParameter
  clojure.lang.IPersistentMap
  (set-parameter [m s i]
    (.setObject s i (->pgobject m)))

  clojure.lang.IPersistentVector
  (set-parameter [v s i]
    (.setObject s i (->pgobject v))))
There is a bug in the doc, additional )

seancorfield18:03:31

@kwladyka Thanks for spotting that! Fixed on master.

kwladyka10:03:34

still thinking about this namespaced keywords like :user/id in context of graphql etc. What do you feel about this?

dharrigan11:03:26

I don't have much experience with graphql, so cannot really comment.

kwladyka11:03:52

(get-by-id :shops #uuid "00000000-0000-0000-0000-000000000000" :uuid {})
Is a way to make it work without #uuid ?

kwladyka11:03:52

Well yes, But what I mean namespaces keywords from postgresql don’t work with other ideas and systems, which don’t predict namespaced keywords. Mainly in API I guess. How do you deal with this?

kwladyka11:03:10

(-> (shop-db/get-shop-by-uuid uuid)
      (clojure.walk/stringify-keys)
      (clojure.walk/keywordize-keys))
each time?

dharrigan11:03:20

You can remove the namespace, thus:

dharrigan11:03:45

by passing this {:builder-fn rs/as-unqualified-lower-maps} into the ops of the jdbc invocation

dharrigan11:03:12

there's a plethora of choices 😉

kwladyka11:03:09

yes, but maybe I miss how this should by solved in API like graphql. Maybe this is fine, but I have to see this from different perspective. Or I just shouldn’t use namspeaces keywords 🙂

kwladyka11:03:25

I have to go, thank you for feedback. I will be back in a few hours.

dharrigan11:03:28

You can strip the namespace programmically if you wish (into {} (map (fn [[k v]] [(keyword (name k)) v])) m)

seancorfield18:03:36

Re: qualified keys in SQL results. I try to use the qualified keys as much as possible, unless I have very specific situations where I explicitly do not want them. For APIs, data will come in unqualified (usually) and that can be validated and inserted as-is -- the logic in both c.j.j and next.jdbc uses just the name portion of keywords for inserts etc. For returning output, I generally try to coerce the SQL results to plain hash maps anyway because it's rare (in my experience) that you'll be passing the entire result set back to whatever client is consuming that data (outside your code) so that explicit coercion can do the unqualify operation. I think JSON libraries in general will also strip the qualifier when converting from hash maps to JSON but I can't say I've paid much attention to that.

seancorfield18:03:55

It's worth noting that if you use plan (in next.jdbc, or reducible-query in c.j.j) then you will likely be accessing columns via their plain label anyway and not actually producing full-on Clojure hash maps so you can avoid the whole issue. See the link to the updated docs I posted above.

kwladyka18:03:40

not sure what is the the conclusion: Do you use :foo/bar when get data from postgresql or other database and before you add them to response change them to :foo_bar or something like that?

kwladyka18:03:59

I don’t know plan yet. I will read doc soon.

seancorfield18:03:13

:foo/bar for column bar from table foo -- letting next.jdbc handle that automatically.

kwladyka18:03:45

yes but if you will have to return this data in graphql or other API what will you do?

kwladyka18:03:59

to keep structure of data consistent and make sense

seancorfield18:03:03

(`clojure.java.jdbc` handles this differently: it doesn't qualify by default and the :qualifier option is a fixed string rather than the auto-added table name as in next.jdbc)

seancorfield18:03:22

Returning data -- exactly as I described above.

seancorfield18:03:38

> For returning output, I generally try to coerce the SQL results to plain hash maps anyway because it's rare (in my experience) that you'll be passing the entire result set back to whatever client is consuming that data (outside your code) so that explicit coercion can do the unqualify operation.

kwladyka18:03:22

sorry, not sure how to interpretate this. Can you say that using Clojure? 😉

seancorfield18:03:23

For example, I might have a setting column in the DB that is an int and I might want to return it as one of a set of string values.

isak18:03:55

For GraphQL a pretty simple approach is to just use queries that return JSON (works fine in postgres and SQL Server)

kwladyka18:03:32

not sure what you mean @isak in context of namespaces keywords

kwladyka18:03:21

graphql doesn’t let return :foo/bar

isak18:03:31

@kwladyka GraphQL doesn't have namespaces, so you would just label stuff by their fieldName in GraphQL

kwladyka18:03:57

I know, but this mean I have to add a lot of complexity DB <-> graphql

kwladyka18:03:04

just to play with namespaced keywords

kwladyka18:03:08

does it make sense?

isak18:03:46

no, i'm not sure why you're considering namespaced keywords at all if you need to return data to GraphQL

seancorfield18:03:35

I draw a pretty hard line between data in my domain (where I would use namespace-qualified keys) and data I return to clients outside (which I would use plain string keys in general).

seancorfield18:03:08

I don't view it as "complexity" since I think there should always be a translation layer from your domain data to your external data.

seancorfield18:03:39

I would not expect your regular result set data to be structured the same as your GraphQL result -- in general.

seancorfield18:03:02

(result sets are flat vectors of hash maps -- GraphQL results are typically more structured)

isak18:03:36

Also if all your data is one relational DB, it is better to just transform the query AST to a SQL query directly, instead of relying on resolvers

kwladyka18:03:51

> translation layer from your domain data to your external data. sure but I feel in many cases I can return data as it is

kwladyka18:03:02

in this case + remove namespace from keywords

kwladyka18:03:12

maybe not a big deal

kwladyka18:03:23

but then why to use them

kwladyka18:03:41

> transform the query AST to a SQL query directly, instead of relying on resolvers Can you show me code? I don’t what it means.

kwladyka18:03:05

Abstract Syntax Trees? I didn’t use this

kwladyka18:03:12

at least I don’t recognize name

kwladyka18:03:27

yeah so in short words I confused myself if I should use namespaced keywords or not. It was looking like a cool idea, but mmm I don’t know.

isak18:03:13

Basically for a GraphQL query like this:

users(first: 10) {
	id
	posts { title }
}
Just run a SQL query like this (SQL Server syntax, it is slightly less elegant for postgres, but basic idea the same) :
select top 10 id,
    (select title
     from posts p 
     where p.user_id = a.i 
	 for json path) as posts
 from users a
 for json path

kwladyka18:03:14

Does lacinia have such kind of integration with postgresql?

isak18:03:58

No, I don't havent seen anything

seancorfield18:03:59

> yeah so in short words I confused myself if I should use namespaced keywords or not. It was looking like a cool idea, but mmm I don’t know. Like I said: within the domain, I try to use namespaced keywords because it plays nicely with Spec and makes it easier to see what entity :name or :id belongs to (because it would be :member/name, :product/name, :invoice/id, etc). When I'm returning data from an API, that (flat) data will be transformed into whatever is more appropriate for the client -- most likely a nested structure -- and that transformation will include converting (qualified) keywords to strings.

seancorfield18:03:17

If you have clear system boundaries (and you should) then this is a no-brainer.

kwladyka18:03:28

Oh maybe I will ask in that way: What advantage did you achieve by using namespaced keywords in practice in your systems? What pros it gives to be worth to add complexity to convert nammespaced keywords into fileds for graphql or other API?

seancorfield18:03:43

I just answered that 🙂

kwladyka18:03:44

oh you already answered

kwladyka18:03:57

I started to write this at the same time

seancorfield18:03:14

Spec, clarity of domain entities. Those are why Clojure has qualified keywords.

kwladyka18:03:49

ok this is the answer what I wanted. So you are saying this is still worth to do and you are happy with this choice.

seancorfield18:03:19

I think it is the right choice in general, yes. Apart from anything else, it reminds you that you have a system boundary that you are passing data across.

kwladyka18:03:37

(-> (shop-db/get-shop-by-uuid uuid)
      (clojure.walk/stringify-keys)
      (clojure.walk/keywordize-keys))
Do you convert data similar to above or you foud more sophisticated way?

kwladyka18:03:04

the general concept

seancorfield18:03:22

Why not use a JSON library? I assume you trying to produce JSON to return?

kwladyka18:03:04

hmm can I return JSON to lacinia ? I thought I can’t.

kwladyka18:03:57

lacinia return JSON, but as an input I think it needs edn

kwladyka18:03:21

but I am not lacinia expert

kwladyka18:03:27

doing this without lacinia would be nightmare

kwladyka19:03:23

but I see, you probably don’t use graphql so this is the point where you don’t feel my concern

seancorfield19:03:41

We use Lacinia at work -- well, my teammate uses it, I haven't had to deal with that code much yet.

kwladyka19:03:44

thank you both @seancorfield @isak for advices

seancorfield19:03:22

My teammate's code uses namespace-qualified keywords very heavily. I'm checking with him whether we do anything specific to work with Lacinia in that regard...

👍 4
seancorfield19:03:36

@kwladyka He says "it looks like I manually map all the namespace keywords to keywords that literally match what is in the schema, so for example :storage/message-count -> :message_count" -- which is exactly the pattern I was recommending above: an explicit transformation layer between the domain and the API return values.

❤️ 4
dharrigan19:03:45

That's what I (mostly) do as well. I remap the keys that I puck from my sql queries into json keys (we work in json a lot!)

dharrigan19:03:01

so internally to my application, it's all namespace qualified

dharrigan19:03:08

but interface to the outside world, it's remapped.

seancorfield19:03:37

I see that jsonista allows you to explicitly map qualified names to unqualified names.

seancorfield19:03:53

(I was looking for that in Cheshire but didn't see it)

seancorfield19:03:23

(! 774)-> clj -Sdeps '{:deps {metosin/jsonista {:mvn/version "RELEASE"}}}'
Clojure 1.10.1
user=> (require '[jsonista.core :as j])
nil
user=> (def mapper (j/object-mapper {:encode-key-fn name :decode-key-fn keyword}))
#'user/mapper
user=> (j/write-value-as-string {:hello/world 1} mapper)
"{\"world\":1}"
user=> (j/read-value *1 mapper)
{:world 1}

dharrigan19:03:58

Yeah, I use cheshire - simply because interia - when I was (still am!!!) learning clojure, Cheshire was my first hit 🙂

seancorfield19:03:14

Ah, yeah, Cheshire can do it too...

seancorfield19:03:46

(! 775)-> clj -Sdeps '{:deps {cheshire {:mvn/version "RELEASE"}}}'
Clojure 1.10.1
user=> (require '[cheshire.core :as j])
nil
user=> (j/generate-string {:foo/bar 42} {:key-fn name})
"{\"bar\":42}"
user=> (j/parse-string *1 true)
{:bar 42}

kwladyka22:03:09

sorry, probably I miss something big here:

(defn fixture-db-transaction [tests]
  (jdbc/with-transaction [tx db-psql/db {:rollback-only true}]
                         (with-redefs [db-psql/db tx]
                           (tests))))
it doesn’t work even if I do (.rollback tx)
(use-fixtures :each utils/fixture-db-transaction)
why data stay in DB?

kwladyka22:03:31

Does it work like it replace db-psql with tx and rollback on the end of tests? why not?

kwladyka22:03:23

this is db-psql/db

(def db "jdbc:")

kwladyka22:03:52

this doesn’t help too

(throw (Exception. "foo"))

kwladyka22:03:49

oh maybe because this is macro

kwladyka22:03:28

yeah, exactly

kwladyka22:03:37

(next.jdbc/transact db-psql/db (fn* [tx] (with-redefs [db-psql/db tx] (tests))) {:rollback-only true})

kwladyka22:03:53

How can I achieve what I want?

seancorfield22:03:40

Your use of a def is what's problematic there since it is not dynamic -- so it will be compiled into the code that uses it.

seancorfield22:03:43

If you made it a ^:dynamic var and used binding instead of with-redefs it would work I think.

seancorfield22:03:05

(the same applies to clojure.test)