Clojurians Log v2

Hi there. I’m working on some database tests and I came across this website https://purelyfunctional.tv/mini-guide/clojure-database-test-faster/. In the article, the author has this block of code:

(defn clear
    (sql/with-db-transaction [db db]
        (sql/db-set-rollback-only! db)
        (binding [db db] ;; rebind dynamic var db, used in tests
          (test))))

You may want to consider something like clj-test-containers which spins up a real instance of your database inside a docker container. Thus you can exercise your tests against a real instance, thus your tests should be more accurate (in the sense of not mocking out tests, or using another in-memory data structure to mimic a database).

<https://github.com/javahippie/clj-test-containers>

Nice thing is that when your tests are done, the instance is destroyed 🙂

Ooooh that is AWESOME! Thank you so much 🙂

One question: is this mainly for the entire test suite, i.e. you spin it up once before running the tests and tear it down afterward? Because I imagine spinning a container up for every test seems like suboptimal.

the former

Is there a way to effectively reset the db after each test instead? I’m currently using Migratus to reset the db after each test at the moment.

It works, but I have a feeling I’m doing it wrong 😅

It sounds like, if that's what you want to do, is to wrap each of your tests in a transaction, then roll that back

i.e., using use-fixtures

<https://clojure.github.io/clojure/clojure.test-api.html#clojure.test/use-fixtures>

<https://clojuredocs.org/clojure.test/use-fixtures>

Yep, and hence my original question if there’s anything like db-set-rollback-only! in next.jdbc.

There is the rollback-only

where is that sorry?

let me dig that out for you

Lovely. Thanks 🙂

This may give you some hints: <https://github.com/seancorfield/next-jdbc/blob/7e13e719ce2df78050c6efb2704e771c56a25035/test/next/jdbc_test.clj#L209>

This is possible if you test the dB layer but what if you want to test higher levels such as ring handlers that create their own transactions.? I assume then you need to reset db state after every test

Seems like that's not testing in isolation

I would only test the layer that I'm interested in

and mock out above/below

jdbc/with-transaction [t (ds) {:rollback-only true}] seems like the way to do it

it’s still quite useful if you want to do end-to-end testing I reckon

The question becomes, where do you stop?

If you're testing ring handling, do you go up the stack and test your network call to the ring?

Yes. Mocking is quite brittle and not that useful imho

for it's not truly end-to-end testing, if you're not going all the way 🙂

well, by end2end I mean something akin to selenium

you're looking at more at integration test rather than a "simple" unit test

Oh definitely- I think anything involving a db isn’t a unit test anymore

Yep I agree.

shock

agreement on slack, on a saturday morning!

haha

I fall off my chair

too bad I’m not feeling up to wage war on a saturday morning 😅

🙂

still, thanks a lot for the help @dharrigan

really appreciate it 🙂

You're most welcome! Enjoy!

(not related to clojure, but I am assuming this is ok in this channel since it is says "all things sql", and don't know a better place to ask and can't find anything about this) I am trying to do the following in postgres: I want to do a dynamic LIKE prefix query on a column that is indexed with the text_pattern_ops index. The query looks like WITH prefixes (...) SELECT url FROM urls, prefixes WHERE urls.url LIKE prefixes.prefix || '%'; This doesn't use the index because prefixes.prefix might contain a bunch of wildcards for all postgres knows. How do I escape wildcards in prefixes.prefix and more importantly tell postgres to use the index on the url column when pattern-matching?

Does it use the index without the string manipulation? What happens if you introduce another WITH where you do the string concat and use that for the like?

It uses the index if I specify the string 'http://google.com%' myself. Doesn't use the index if the whole string comes from the with, without concatenation.

I just found there is starts_with, but it doesn't seem to use the index at all even if I specify the string manually.

Hmm, looks like there is a message in the postgres mailing list about this problem with no replies: https://www.postgresql.org/message-id/flat/F5AA20A6-F2BC-4A60-A58A-0EF5220FD077%40ya.ru

I’ve had good results asking things in the Postgres Reddit. Perhaps you could be lucky and get an answer.

Is the prefixes static ? Perhaps you could end up doing some fancy sql generation

It comes from the client, so no.

I'll try asking, thank you! Also will ask in their mailing list, this seems to be the main place of activity for them.

Actually it is simpler, I just replaced the the LIKE part with url.url ~>=~ prefix AND url.url ~<~ prefix || '~', this does use the index, and actually does the same thing under the hood as a prefix LIKE (well, except the prefix LIKE replaces the last character of the prefix string with the next one in ~< part to be completely correct, but I don't need to worry about non-ascii characters in my case and has the largest number among ascii characters).

I haven’t seen this operator before! Is this a regex? Can you point to the docs?

I am not sure what operator that is and didn't find it in the docs, I just saw that it in explain analyze of the LIKE query.

Found this: https://stackoverflow.com/questions/35807872/operator-in-postgres