Fork me on GitHub
#sql
<
2021-04-03
>
aratare07:04:55

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))))
which essentially allows test to do whatever it wants and then everything will be rolled back at the end. This is quite neat but apparently db-set-rollback-only! is only available in clojure.java.jdbc. Is there an equivalent of such function in next.jdbc? If not, is there a preferred way of doing database testing in Clojure? Thanks in advance 🙂

dharrigan08:04:00

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).

dharrigan08:04:06

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

aratare08:04:57

Ooooh that is AWESOME! Thank you so much 🙂

aratare08:04:03

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.

aratare08:04:57

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.

aratare08:04:11

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

dharrigan08:04:22

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

jumar08:04:00

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

dharrigan08:04:32

Seems like that's not testing in isolation

dharrigan08:04:39

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

dharrigan08:04:45

and mock out above/below

aratare08:04:28

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

dharrigan08:04:41

The question becomes, where do you stop?

dharrigan08:04:52

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

jumar08:04:59

Yes. Mocking is quite brittle and not that useful imho

👆 3
dharrigan08:04:08

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

aratare08:04:30

well, by end2end I mean something akin to selenium

dharrigan08:04:41

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

jumar08:04:34

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

aratare08:04:14

Yep I agree.

dharrigan08:04:54

agreement on slack, on a saturday morning!

dharrigan08:04:06

I fall off my chair

aratare08:04:26

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

aratare08:04:43

still, thanks a lot for the help @U11EL3P9U

aratare08:04:48

really appreciate it 🙂

dharrigan08:04:56

You're most welcome! Enjoy!

dharrigan08:04:48

i.e., using use-fixtures

aratare08:04:07

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

dharrigan08:04:51

There is the rollback-only

aratare08:04:14

where is that sorry?

dharrigan08:04:20

let me dig that out for you

aratare08:04:35

Lovely. Thanks 🙂

dharrigan08:04:40

This may give you some hints:

aratare08:04:48

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

Gleb Posobin16:04:02

(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?

orestis16:04:57

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?

Gleb Posobin16:04:56

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.

Gleb Posobin16:04:24

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.

Gleb Posobin17:04:33

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

orestis17:04:47

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

orestis17:04:22

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

Gleb Posobin17:04:25

It comes from the client, so no.

Gleb Posobin17:04:46

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

Gleb Posobin17:04:36

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).

orestis17:04:58

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

Gleb Posobin18:04:02

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.