Clojurians Log v2
xtdb
hey all, i've been playing a bit with assert and i was wondering if xtdb has something like SQL check constraint?
correct me if i'm wrong, but the assert happens before the data is committed, so i can verify constraints that are related to the existing data (current state) like uniqueness, foreign key...
but if i want to verify constraints that are related only to the entity (regardless of the current state) i have to do that in the application server? or is there an equivalent to pg "check" constraints or trigger functions?
you can also run ASSERT after your DML to check post-conditions - XTDB runs transactions in a single thread (strict serializable, the highest isolation level) so you're guaranteed that no other writes will affect your data until after it commits
we don't yet have the ability to store these in the database I'm afraid - the more people tell us they want them, the sooner it'll happen, so thanks for letting us know 🙂
ok i understand, i'm giving it a try the constraints that i can execute are limited to the functions and predicates XTDB support, correct?
yep, but you also get access to subqueries etc too, so you can check data in other tables - ASSERT is often used as ASSERT [NOT] EXISTS (SELECT ...) to achieve foreign-key behaviour
or, equivalently, ASSERT ? IN (SELECT _id FROM table)
cool
i'm missing something tho
i have tried validating the length of name to be less than 10
the assertion fails but the data is written, what am i doing wrong?
try an explicit transaction? BEGIN beforehand
yup, the explicit tx did work why is it requried? i thought that xtdb wraps the expressions in txs
sorry, that's my bad, it splits the statements before it wraps each individual one in a transaction
Prompted by discussions in the above Reddit thread (thanks everyone!) I've published some additional Clojure examples in https://github.com/xtdb/driver-examples/tree/main/clojure/dev - including a sample https://www.jooq.org/sakila data set and a key snippet that allows next.jdbc to traverse the schema (but not including the many-to-many join tables):
(tap> (jdbc/execute! conn ["select * from inventory"]
{:schema-opts {:pk "_id"}})) ;; infer all FKs for datafy/nav usage
How are the m2m tables / relationships structured (since you imply next.jdbc cannot auto-nav them)?
The original Postgres schema for those tables looks like: https://github.com/jOOQ/sakila/blob/aed53ce65404eac184f4134f34239a08c464df77/postgres-sakila-db/postgres-sakila-schema.sql#L194-L198
For my XT import we derive a surrogate _id PK which is just a concatenation of the FK value pairs.
Dustin and I spent some time playing with some code to infer the join tables here: https://gitlab.com/hyperfiddle/electric-fiddle/-/blob/f79b98d3f58ef025896bc8a8d800334abeda567c/src/dustingetz/xtdb/xtdb.cljc#L51-100
> any other requests? oh, HoneySQL examples(!)