This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
- # announcements (1)
- # babashka (31)
- # bangalore-clj (3)
- # beginners (18)
- # bitcoin (2)
- # calva (14)
- # clj-kondo (37)
- # clojure (43)
- # clojure-europe (4)
- # clojurescript (24)
- # data-science (4)
- # datahike (1)
- # datascript (3)
- # datomic (1)
- # fulcro (16)
- # lsp (13)
- # malli (1)
- # meander (7)
- # off-topic (1)
- # polylith (12)
- # re-frame (7)
- # reitit (1)
- # rewrite-clj (46)
- # rum (4)
- # shadow-cljs (15)
- # specter (3)
- # sql (55)
- # tools-deps (20)
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:
which essentially allows
(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))))
testto 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 🙂
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).
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.
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 sounds like, if that's what you want to do, is to wrap each of your tests in a transaction, then roll that back
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
If you're testing ring handling, do you go up the stack and test your network call to the ring?
still, thanks a lot for the help @U11EL3P9U
Yep, and hence my original question if there’s anything like
(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.
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 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.