Clojurians Log v2
xtdb
How do I include temporal/metadata columns in historical queries? for example, "SELECT * FROM user FOR ALL VALID_TIME where ID = ?" returns all defined fields for user, and all recorded updates, but I'd also like to include the valid times for each record update (and ideally by whom)? Bonus points for expressing the same in honeysql.
SELECT *, _valid_from, ... 🙂
we elected not to include the temporal columns in SELECT * so that by default documents round-tripped as inserted, but it's one of those where some people wish we did and others are glad we didn't 😅
as for HoneySQL, I'd guess (/naively hope?) you can ask for both * and other columns...?
if not, we've said 'honey SQL' three times now, that usually summons Sean 🙂
lol, often I've noted just thinking about him does the trick. thanks for the prompt reply - i figured it had to be that easy, and you probably did document it somewhere but somehow I missed it.
I see it now, in the "Time in finance" example, though in that case, it was in the context of cherry picking fields (_id, info) and not a * query, but I guess I should have been able to deduce it from first principles.
user=> (h/format {:select [:* :-valid-from]})
["SELECT *, _valid_from"]When running xtdb 2 in a docker container and mapping ports 3000 (HTTP server), 8080 , and 5432, is it expected that some kind of UI is served up at port 3000?
Is that the /healthz endpoint for the health check?
No, that's at port 8080. I see when I run 'docker compose up' that port 3000 goes to "HTTP Server" but when I navigate there I get 404. So I'm thinking it's API only?
No idea. I don't even have it forwarded. Just 5432 and 8080:
> curl localhost:8080/healthz/alive
Alive.The docs do not show port 3000 being forwarded when starting Docker.
BTW, while I have you on the line, are you aware of any templates (using your clj-new tool - i happen to be using clojure stack lite originally configured for postgres) that configure XTDB2 correctly for dev/test/prod?
Yeah I didn't know 3000 went to a web server till I ran docker compose up and was just curious.
Port 3000 may be the metrics server? ISTR hearing that mentioned at some point. Re: templates -- no I don't think anyone has created anything for XTDB stuff (yet).
I'll reach out to the clojure stack lite dev to consider adding it; but otherwise may just roll my own. Will check out your docs at clj-new for pointers.
deps-new is what you want these days, not clj-new
I like most of the Clojure Stack Lite defaults/choices but with freedom to reconsider I can think of a few things I'd do a little differently.
ah, yeah, that was just a mental typo. That's what clojure stack lite is based on - deps-new.
The main thing I'm not sure how to do with XTDB2 in that context is fire up an ephemeral test instance (all defaults in memory) for unit testing.
Otherwise with a few tweaks to the integrant config, bb tasks, and of course unit tests, it's almost tolerable just drop in replacing the main dev postgres instance with XTDB2.
port 3000 is an old HTTP server, hanging around from before we started work on the Postgres compatibility
nowadays we'd only recommend using the Postgres server - the HTTP server has stagnated a little. we moved it to a 'labs' module before GA but tbh it's most likely to be deprecated and removed
check out https://docs.xtdb.com/drivers/clojure.html#_in_process 🙂
So, I saw that, but the problem (I think) I'm trying to solve is get the Clojure Stack Lite integrant component to work right. Out of the box with PG, it simply uses Testcontainers PG and sets it up via a connection string as a TC_DAEMON. I was hoping there was an analogous capability for the dockerized version of XTDB. Anyway, I just need to rethink how it runs, period, and redo the integrant part of it from scratch. WHich is why I was considering a deps-new alternative once I figure the incantations out.
oh, sorry, I see - try adding playground to the CMD for the XT Docker image?
playground opens up a clean instance for every database name that comes its way - so just need to pick a fresh database name in each test
sorry, --playground-port 5432 in 2.0.0
sorry - can you concretize that for me just a little more?
I'm not invoking docker command line style at the moment, but actually running docker compose up. I could create different configs for different environments.
right - so in your docker-compose file, you can override the command sent to the docker image - I think it's something like command: ['--playground-port', '5432']
What do you mean "so just need to pick a fresh database name in each test"?
in playground mode, every time you open up a connection to a different database name (in your JDBC options), the XT server will give you a completely fresh database
so when you're setting up your connection pool for a test, you'll set the database name to be (say) (str (random-uuid)), and then you get an empty database for every test
Ok I think I got it.
translating to my setup now. will let you know how it went.
cool 🙂 just as a minimal working example:
docker run --rm -ti --name xt -p 5432:5432 --playground-port 5432 (t/deftest test-playground
(with-open [conn1 (jdbc/get-connection "jdbc:")
conn2 (jdbc/get-connection "jdbc:")]
(jdbc/execute! conn1 ["INSERT INTO foo RECORDS {_id: 'database1'}"])
(jdbc/execute! conn2 ["INSERT INTO foo RECORDS {_id: 'database2'}"])
(t/is (= [{:_id "database1"}] (jdbc/execute! conn1 ["SELECT * FROM foo"])))
(t/is (= [{:_id "database2"}] (jdbc/execute! conn2 ["SELECT * FROM foo"]))))) but yeah, if you're on the JVM, it's simpler to set up an Integrant component to start an in-process XT node
looking at Clojure Stack Lite, you're probably deleting code to get it working with XT rather than Postgres 🙂
that'd be my recommendation tbh
rather than needing to set up something akin to test-containers