Clojurians Log v2

Is it a single statement with assert and insert? Or two separate statements? If it's the former and the semantics is "insert if it doesn't exist", then I'm very surprised they decided to create a custom syntax for it instead of just using a CTE.

Since it's a top-level clause, I don't think there's anything built into HoneySQL that would result in that query, apart from :raw maybe. But it's trivial to add on your end with honey.sql/register-clause!.

it is displayed as a single statement but actually to run it i had to concat the statements with ;

(jdbc/execute! conn [(str "assert (select count(*) from test_03 where name = 'unique3') < 1;"
                            "insert into test_03 (_id, name) values (05, 'unique3');")])

> I'm very surprised they decided to create a custom syntax for it instead of just using a CTE This is an interesting observation, thank you for mentioning it. Right now XTDB doesn't support CTEs as part of INSERT statements, but it definitely could 🤔 In any case the explicit ASSERT statement is intended as a useful primitive in its own right as it gives stronger guarantees around failure, which are needed because XTDB doesn't support interactive transactions. Therefore it's quite handy if you want the entire transaction to fail, rather than silently skip over a single statement. Does that make sense? /cc @jarohen

Yeah, if it's a separate statement then there's no surprise on my side. :) FWIW, I personally still wouldn't add a separate clause that's not SQL-compliant (well, AFAIK). Instead, I'd use some function that throws, like SELECT throw('The email already exists') FROM users WHERE email = '...', which also has a benefit of providing a friendly text message and using any data from the offending rows.

yes, separating by semi-colon is expected - we'll then create an implicit transaction for you (as Postgres does too)

you can also do this explicitly with next.jdbc's transaction API, or simply wrap it in BEGIN/COMMIT (again, all same as Postgres)

@seancorfield has very kindly added https://github.com/seancorfield/honeysql/blob/develop/doc/xtdb.md of support for XTDB extensions, I'm sure he'd accept a patch for ASSERT too 🙂

@itai I'd probably go for ASSERT NOT EXISTS (SELECT 1 FROM ...) in your case 🙂 slight perf improvement through not having to count the rows

Feel free to create GH issues for any XTDB syntax that is not currently supported. I prefer to have issues first, with PRs following. PRs should include tests and doc updates as well (so a lot of folks create an issue and leave it at that, for me to write tests and docs 🙂 ).

(and please link to XTDB docs for any missing syntax so I know what HoneySQL is supposed to do!)

HoneySQL has no support for ;-separated statements either (because it's generally discouraged by most database drivers), but having a helper function to merge across multiple honey.sql/format results would be a reasonable addition (GH issue welcome for that too).

yeah, Postgres JDBC driver doesn't really support it, it splits them apart. thinking about the issue for the ASSERT support, reckon that's a good first issue for someone looking to contribute to honeysql 🙂

The latest snapshot (and git deps) has support for ASSERT. And IS DISTINCT FROM / IS NOT DISTINCT FROM which was another recently requested addition. I have not (yet) added a helper to merge multiple sql+params vectors for the multi-statement case but I'm about to create a ticket for that too.

And now I've added semicolon to merge multiple SQL+params vectors. Ugh! 🙂 But (sql/semicolon (sql/format {:assert ...}) (sql/format {:insert-into ...})) should give you what you need @itai