Welcome back, fellow Cojurians! In our previous article, we explored the fundamental differences between SQL and Datomic, focusing on basic data modeling and querying. Today, we’ll dive into one of Datomic’s most distinctive features: its time-oriented design.

Remember how we mentioned that Datomic retains a complete history of changes? Let’s see what that means in practice. But first, why should you care about temporal data? Well, imagine you’re debugging a production issue from last week, or you need to audit who changed what and when, or perhaps you’re building a feature that needs to show how data evolved over time. In a traditional SQL database, you’d need to implement these features yourself, often through audit tables or temporal tables. In Datomic, this functionality is built right in.

Extending our Library System

Let’s extend our Booky system to showcase these temporal features. We’ll add some realistic scenarios:

1. A book’s status changes over time (available, borrowed, lost)
2. Borrowers can extend their borrowing period
3. Books can be transferred between borrowers
4. Book information might be corrected or updated

First, let’s add a status attribute to our schema and transact it

;; Add to our existing schema.edn
{:db/ident       :book/status
 :db/valueType   :db.type/keyword
 :db/cardinality :db.cardinality/one
 :db/doc         "Current status of the book: :available, :borrowed, :lost"}

Now, let’s update our book’s status and see how Datomic handles the history:

;; First, let's find our book's entity id
(def musil-id 
  (ffirst 
    (d/q '[:find ?e
           :where [?e :book/title "The Man Without Qualities"]]
         (d/db conn))))

(do
  @(d/transact conn [[:db/add musil-id :book/status :available]])

  ;; mark it as borrowed
  (Thread/sleep 5000)
  @(d/transact conn [[:db/add musil-id :book/status :borrowed]])

  ;; mark it as lost
  (Thread/sleep 5000)
  @(d/transact conn [[:db/add musil-id :book/status :lost]]))

Time Travel with Datomic

Now comes the fun part. Let’s see how we can query this data across time:

(defn book-status-history [title]
  ; Get historical database view with all past states
  (let [db (d/history (d/db conn))]
    ; Select status, timestamp and operation type
    (->> (d/q '[:find ?status ?inst ?op
                ; Input: database and book title
                :in $ ?title
                :where
                ; 1. Find entity with matching title
                [?e :book/title ?title]
                ; 2. Get its status changes
                [?e :book/status ?status ?tx ?op]
                ; 3. Get timestamps for changes
                [?tx :db/txInstant ?inst]]
              ; Pass history db and title to query
              db title)
         ; Transform each result tuple into a map
         (map (fn [[status inst op]]
                {:status    status
                 :when      inst
                 :operation (if (= op true) :assert :retract)}))
         (sort-by :when))))

(print-table (book-status-history "The Man Without Qualities"))

What’s happening here? Let’s break down this result, as it reveals one of Datomic’s most powerful features:

1. First, notice we’re using (d/history (d/db conn)) instead of just (d/db conn). Without d/history, we’d only see the current state (:lost).

2. We get 5 records, not 3, even though we only made 3 changes. Why?

• The earliest record (:available, at 19:41:04) is our first state change
• The next 2 records (both at 19:41:09) show both sides of our second change:
  • One record shows :available being retracted
  • One record shows :borrowed being asserted
• And the final 2 rows (both at 19:41:14) illustrates the same behaviour as the previous ones.

This is Datomic’s true temporal nature at work – it doesn’t just track current state with a changelog (like most databases), it maintains a complete history of assertions and retractions. When you use d/history, you see the full story of how your data changed over time.

Point-in-Time Queries

One of Datomic’s most powerful features is the ability to query the database as it existed at any point in time. Think of it as a time machine for your data – you can see exactly what your database looked like at any moment in the past. Let’s explore this with a practical example.

First, we’ll create a function that shows us the state of our library at any given moment:

(defn library-snapshot [instant]
  ; Get database as it existed at that instant
  (let [db-at-point (d/as-of (d/db conn) instant)]
    ; Select title status and borrower
    (d/q '[:find ?title ?status ?borrower
           ; Return them in a map with these keys
           :keys book status borrower
           :where
           ; Start with book entity
           [?b :book/title ?title]
           ; Get its status
           [?b :book/status ?status]
           ; Join with registry using book
           [?r :registry/book ?b]
           ; Get borrower from registry
           [?r :registry/borrower ?br]
           ; Get borrower's name
           [?br :person/first-name ?borrower]]
         db-at-point)))

Now, let’s see this in action with a concrete example. We’ll create a sequence of events and then look at the database state at different points in time:

(let [ ;; Initial state - book is available
      _ @(d/transact conn [[:db/add musil-id :book/status :available]])
      t1 (t/inst)
      _ (swap! timeline assoc :available t1)
      _ (Thread/sleep 2000)             ; Wait 2 seconds

      ;; Someone borrows the book
      _ @(d/transact conn [[:db/add musil-id :book/status :borrowed]])
      t2 (t/inst)
      _ (swap! timeline assoc :borrowed t2)
      _ (Thread/sleep 2000)             ; Wait 2 seconds

      ;; Unfortunately, book gets lost
      _ @(d/transact conn [[:db/add musil-id :book/status :lost]])
      t3 (t/inst)
      _ (swap! timeline assoc :lost t3)]

  ;; Look at different points in time
  {:at-start  (library-snapshot t1)
   :when-borrowed (library-snapshot t2)
   :when-lost (library-snapshot t3)})

;; => {:at-start
;;     [{:book "The Man Without Qualities", :status :available, :borrower "Agatha"}],
;;     :when-borrowed
;;     [{:book "The Man Without Qualities", :status :borrowed, :borrower "Agatha"}],
;;     :when-lost
;;     [{:book "The Man Without Qualities", :status :lost, :borrower "Agatha"}]}

This example demonstrates how we can:

1. Record a series of changes to our data
2. Capture timestamps at specific points
3. Query the exact state of our data at those historical moments

But the real power comes from being able to query any arbitrary point in our timeline. For example:

;; Query 1 second before the book was borrowed
(library-snapshot
 (t/inst (t/< [{:book "The Man Without Qualities", :status :available, :borrower "Agatha"}]

;; Query 1 second after the book was borrowed
(library-snapshot
 (t/inst (t/>> (@timeline :borrowed)
              (t/new-duration 1 :seconds))))
;; => [{:book "The Man Without Qualities", :status :borrowed, :borrower "Agatha"}]

;; Or look at the state just before it was lost
(library-snapshot
 (t/inst (t/< [{:book "The Man Without Qualities", :status :borrowed, :borrower "Agatha"}]

The beauty of this system is that it’s not just for debugging or auditing – you can build features around it. Want to show users what their library looked like last week? Need to generate a report of book status changes over the past month? With Datomic’s temporal capabilities, these become straightforward queries rather than complex data modeling challenges.

Comparing with PostgreSQL

To achieve similar functionality in PostgreSQL, we’d typically need something like this:

CREATE TABLE book_status_history (
    id SERIAL PRIMARY KEY,
    book_id INT REFERENCES book(id),
    status TEXT NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by TEXT
);

-- And triggers to maintain the history 
CREATE OR REPLACE FUNCTION log_book_status_change()
RETURNS TRIGGER AS $$
BEGIN 
    INSERT INTO book_status_history (book_id, status, changed_at)
    VALUES (NEW.id, NEW.status, CURRENT_TIMESTAMP);
    RETURN NEW;
END; 
$$  LANGUAGE  plpgsql;

CREATE TRIGGER book_status_change
AFTER UPDATE OF status ON book
FOR EACH ROW
EXECUTE FUNCTION log_book_status_change();

The difference is striking. In PostgreSQL:

1. We need explicit audit tables
2. We need to set up triggers
3. We need to manage the additional storage ourselves
4. Point-in-time queries become more complex

In Datomic, this functionality is built into the database itself. Every change is automatically tracked, and we can query any point in time without additional setup.

The Power of History

Let’s look at one more practical example. Suppose we want to analyze borrowing patterns over time:

(defn borrowing-history [book-title]
  (let [db (d/history (d/db conn))]
    ; Select borrower, status, timestamp
    (->> (d/q '[:find ?borrower ?status ?inst
                ; Input: database and book title
                :in $ ?title
                :where
                ; Find book by title
                [?b :book/title ?title]
                ; Get only assertions (added=true) of status changes
                [?b :book/status ?status ?tx true]
                ; Find registry entry
                [?r :registry/book ?b]
                ; Get borrower entity
                [?r :registry/borrower ?br]
                ; Get borrower's name
                [?br :person/first-name ?borrower]
                ; Get timestamp of transaction
                [?tx :db/txInstant ?inst]]
              db book-title)
         (map (fn [[borrower status inst]]
                {:borrower  borrower
                 :status    status
                 :when      inst
                 :operation "assert"}))
         (sort-by :when))))

(print-table (borrowing-history "The Man Without Qualities"))

(defn borrowing-history [book-title]
  (let [db (d/history (d/db conn))]
    ; Select borrower, status, timestamp
    (->> (d/q '[:find ?borrower ?status ?inst
                ; Input: database and book title
                :in $ ?title
                :where
                ; Find book by title
                [?b :book/title ?title]
                ; Get only assertions (added=true) of status changes
                [?b :book/status ?status ?tx true]
                ; Find registry entry
                [?r :registry/book ?b]
                ; Get borrower entity
                [?r :registry/borrower ?br]
                ; Get borrower's name
                [?br :person/first-name ?borrower]
                ; Get timestamp of transaction
                [?tx :db/txInstant ?inst]]
              db book-title)
         (map (fn [[borrower status inst]]
                {:borrower  borrower
                 :status    status
                 :when      inst
                 :operation "assert"}))
         (sort-by :when))))

(print-table (borrowing-history "The Man Without Qualities"))

This query shows us every time the book was borrowed and returned, giving us a complete picture of the book’s journey through our library system (including retracts).

Conclusion

Datomic’s temporal features aren’t just a nice-to-have – they fundamentally change how we think about and interact with our data. Instead of just storing the current state, we’re maintaining a complete history of how our data evolved over time. This can be invaluable for:

• Debugging and troubleshooting
• Audit trails and compliance
• Understanding data evolution
• Building time-aware features

In our next article, we’ll explore another powerful feature of Datomic: advanced querying with rules and recursion. We’ll see how to model and query more complex relationships in our library system, such as book recommendations based on borrowing history and patron reading patterns.

Stay tuned, and happy coding!

The complete code for this article is available in the booky repository.