Clojure - idiomatic way to handle database persistence

Question

I am an experienced Java and SQL developer considering Clojure for my next project. I have spent some time studying the basics but still struggling to understand the idiomatic way to painlessly handle interactions with a relational database with regards to updates.

Most of the threads I have read focus on the query side which for me is actually not a problem since I am very comfortable with SQL and consider jdbc.next to be more than adequate for my querying needs (maybe augmented by Toucan 'hydration' support if required).

My main concern is how to handle persistence e.g. how to automatically persist a modified (nested) map(?) back to the database without the need to keep track of state myself.

As a side note, I have enough experience with both Hibernate itself and JPA and still feel they are too rigid and verbose.

The tool I consider ideal on this regard is https://www.sqlalchemy.org/ which I have previously used via jython with a great sense of satisfaction.

Thanks a lot for all your input.

Update based on some comments/questions below:

To - hopefully - set the context more clearly, suppose a web application shopping cart scenario: A new customer checks out. An insert needs to be performed for the customer, possibly another for the shipping address, another for the order and these need to be synchronized in a way that referential integrity is preserved. Doing all the steps manually is of course an option but the more involved the scenario the more complex handling becomes. My database is relational.

Second update

To clear up my question further, my problem is not how to issue the 'insert/update/delete' statements but more of keeping track of which statements must be issued according to the current state of things.

I would expect the 'ORM' layer to track which of my 'data' are new, which have been modified, deleted etc. and execute the above snippet automatically in a consistent way similar to Hibernate's entity manager (please have a look at the below link https://www.sitepoint.com/hibernate-introduction-persisting-java-objects/ in section "Performing CRUD Operations on Entities" for an example of the intended functionality).

Alternatively I would like to be able to identify what happened on each 'piece' of my data (object graph in hibernate parlance) and then execute the snippet you shared by traversing each element, 'querying' its state/status and inferring the correct statement to send back to the database.

From my understanding of the language so far this functionality is native(?) to clojure data structures - but I have not been able to put my finger on it yet.

I might not be understanding your problem well, but I think the Clojure way would be to avoid state if at all possible. Query your dB when needed, transform the data, display result. Similarly a transaction back into the dB will not change the state of your application. — alex314159
what is your question then? if you want to perform an update, just execute it with jdbc. if you want to keep a track of "state" (or "changes"), you might want to take look at datomic.com — a.k
To - hopefully - set the context more clearly, suppose a web application shopping cart scenario: A new customer checks out. An insert needs to be performed for the customer, another for the order and these need to be synchronized in a way that referential integrity is preserved. Doing all the steps manually is of course an option but the more involved the scenario the more complex handling becomes. My database is relational. — ospyros
Turing complete programming languages are differentiated by what they make easy vs what they don't. Complex state persistence (as a.k. pointed out) is what datomic is for (and it can sit on top of relational DBs just fine), but based on your description of what you want I'm wondering if you don't really grok what Clojure is about. "Update" isn't really something Clojure strives to make easy. If you don't buy in to the underlying concepts and think of it as a nicer way to write Java I think you're going to encounter a lot of friction... — Jared Smith

Sean Corfield Sean Corfield · Accepted Answer · 2020-11-14T15:49:38

You say you're using next.jdbc -- the documentation has examples of performing updates and inserts, using transactions, to ensure consistency, so I'm not sure what you're really asking here.

The next.jdbc.sql namespace contains update! and insert! functions. The next.jdbc namespace has with-transaction for creating transactions around multiple operations.

update! returns the count of rows updated. insert! returns the newly-inserted keys (as best it can, based on how the specific database driver you use works).

It's common in Clojure programs that deal with the database to try to separate out the persistence code from the pure business logic so you'd typically have something like:

(defn some-process [...]
  (let [data    (some-database-query ...)
        updates (perform-business-logic ...)]
    (with-transaction [tx ...]
      ...based on what is in updates...
      (update-something tx ...)
      (insert-something tx ...)
      (insert-more-stuff tx ...))))

If subsequent inserts/updates rely on generated keys from earlier inserts, you'd use a let to capture the result of the earlier inserts, so you could access those keys in the other inserts/updates.

It would be reasonable to refactor the above so the whole with-transaction form was in a separate function that some-process called, passing in the updates and the connection/datasource.

If that doesn't answer your question, can you try to clarify what you are really asking?

Also, feel free to join the Clojurians Slack and post questions in the #sql channel if you want an interactive conversation with other Clojurians about relational database persistence.

Clojure Slack: https://clojurians.slack.com

Self-signup via: http://clojurians.net (or I can post a custom invite link here if you have problems with self-signup)

Clojure - idiomatic way to handle database persistence

3 Answers