Side by side: Datomic and PostgreSQL, Part 1

Greetings fellow Cojurians, to clarify from the outset, the objective of this article (or series of articles) is to highlight the differences in data modeling between SQL and Datomic. The conclusion will be yours to draw; I will merely present the facts.

Datomic was once considered a luxury product in the realm of parentheses, causing many companies and projects to avoid it. Eventually, NuBank decided to acquire Cognitect. Perhaps it was cheaper to buy the company than to continue paying licensing fees in the long term. A few months later, Datomic was released as a free-to-use product.

👉 Subscribe to our newsletter for more insights like this.

What makes Datomic special? In my experience, it’s an entirely different type of database compared to what I, and possibly you, have used in production before. It’s a refreshing change in terms of developer experience and code organization. One particular feature that may seem alien to those in the SQL world is Datomic’s time-oriented design.

What does it mean to be time-oriented? In Datomic, the database does not only contain the current state of the data, but it also retains a complete history of changes made to that data. In other words, Datomic stores not only the present state but also the past states of the data. However, before we delve into this, we will take a thorough tour of Datomic and SQL, comparing these two paradigms.

While there’s much to discuss, I prefer to demonstrate through examples. The context will be simple data modeling and execution to provide examples of both approaches, enabling readers to draw their own conclusions. —

ERD

To kick off our exploration, let’s define a simple Entity Relationship Model:

The path ahead of us is straightforward; it’s a diagram illustrating ERD in an SQL database. Our SQL choice is the well-known Postgres. Within the Clojure ecosystem, we have a number of libraries that deal with SQL, such as HugSQL, HoneySQL, and plain SQL migration scripts. When dealing with database migration, my personal preference is the combination of plain SQL and migratus, a pair endorsed by our dear friend, exceptional individual, and veteran of Clojure open-source software – Sean Corfield, the author of our beloved HoneySQL and many other libraries.

Schema

SQL

--;; is Migratus specific comment to distinguish between SQL statements.

Datomic

• :db/ident: Unique identifier for an entity, which is typically used to provide a human-readable, namespace-qualified name for an entity, and once set, it cannot be changed. Namespace in our context is :book and title is its attribute.
• :db/valueType: The type of value that can be associated with an entity. For instance, it could be used to specify that an attribute must be of type :db.type/string, :db.type/long, :db.type/boolean, etc.
• db.type/ref means it would refer to another entity, fact (datum) Important different compared to SQL is a lack of direct connection with any specific entity. There’s no concept of foreign key in Datomic.
• :db/cardinality: Is used to specify whether an attribute of an entity can have one or multiple values. If it is set to :db.cardinality/one, then each entity can have at most one value for the attribute. If it is set to :db.cardinality/many, then each entity can have many values for the attribute.
• :db/doc: Is used to store a human-readable documentation string for an entity. It’s a good practice to provide documentation for all the entities and attributes in the database, to make it clear what they represent and how they should be used.

Database setup and Migrations

The next step in line is to start the database and migrate the schema.

SQL

Create role and corresponding database (I assume we have PostgreSQL running and console available):

Dependencies

Add these dependencies to deps.edn under :deps key

Code

sql.clj

1. import migratus library
2. Read config

• The important step is to manually create dir resources/migrations and SQL file schema.sql with SQL schema mentioned above

1. init function performs database initialization through provided configuration

Datomic

In this case things a little different as Datomic relies on other different types of storage for persistence. In our case, we’ll be using in memory for the demonstration purposes.1

Dependency

Code

datomic.clj

(ns gig.datomic
  (:require [datomic.api :as d]))

(def db-uri "datomic:mem://booky")

(d/create-database db-uri)

(def conn (d/connect db-uri))

(def schema (edn/read-string (slurp "resources/schema.edn")))

@(d/transact conn schema)

• To avoid polluting namespace, I’d rather keep schema into a separate file — resources/schema.edn
• d/transact submits transaction to the database

Save data into database

SQL

Sample data

(def book
  {:title "The Man Without Qualities"
   :author "Robert Musil"
   :genre "Philosophical Fiction"
   :publication-date (t/date "1943-11-06")})

(def patron
  {:first-name "Ulrich"
   :last-name ""
   :email "ulrich@kakania.at"})

(def borrower
  {:first-name "Agatha"
   :last-name ""
   :email "agatha@kakania.at"})

(def registry
  {:book-id 1
   :patron-id 2
   :borrower-id 3
   :borrow-date (t/date "2023-07-28")
   :due-date (t/date "2023-10-28")})

Create a connection to database

(def db (jdbc/get-connection (:db config)))

Save

(jdbc/execute!
 db
 (sql/format
  {:insert-into :book
   :values [book]})
 {:return-keys true})

1. jdbc/execut! takes in datasource and query, performs execution and returns the result
2. sql/format is responsible for processing Clojure map into SQL query
3. The map is the example of HoneySQL map syntax
4. An option to return inserted data

The result

[#:book{:id 14,
        :title "The Man Without Qualities",
        :author "Robert Musil",
        :genre "Philosophical Fiction",
        :publication_date #inst "1943-11-05T23:00:00.000-00:00"}]

sql/format and honeysql

The query above results in:

["INSERT INTO book (title, author, genre, publication_date) VALUES (?, ?, ?, ?)"
 "The Man Without Qualities"
 "Robert Musil"
 "Philosophical Fiction"
 #time/date "1943-11-06"]

Perform the rest of insertions

At this point, I’d rather compose a handy function for execution

(defn execute [q]
  (jdbc/execute! db (sql/format q)
                  {:return-keys true}))

Hence insertion of remaining data is simplified

(execute
 {:insert-into :person
  :values [patron
           borrower]})

(execute
 {:insert-into :registry
  :values [registry]})

Datomic

Sample data

In case of Datomic, sample data looks a bit different, instead of separate definitions, we have all data in a single var:

(def data
  [{:db/id "musil"
    :book/title "The Man Without Qualities"
    :book/author "Robert Musil"
    :book/genre "Philosophical Fiction"
    :book/publication-date (instant/read-instant-date "1943-11-06")}

   {:db/id "ulrich"
    :person/first-name "Ulrich"
    :person/last-name ""
    :person/email "ulrich@kakania.at"}

   {:db/id "agatha"
    :person/first-name "Agatha"
    :person/last-name ""
    :person/email "agatha@kakania.at"}

   {:registry/book "musil"
    :registry/patron "ulrich"
    :registry/borrower "agatha"
    :registry/borrow-date (instant/read-instant-date "2023-07-28")
    :registry/due-date (instant/read-instant-date "2023-10-28")}])

Save (transact)

(d/transact conn data)

• :db/id is a temporary id used in the context of transaction to actually create a relationship. Example from the execution {"musil" 17592186045418, "ulrich" 17592186045419, "agatha" 17592186045420}
• Hence we don’t explicitly force relationship on the schema level, but during transaction. If we try to transact without temporary id the REPL will greet we with error – :db.error/tempid-not-an-entity tempid 'whatever' used only as value in transaction

Querying data from database

I wouldn’t say that we’ve been sailing into much familiar waters until now, but at this point things do actually get quite different, challenging at some extent, but I do usually hold myself from jumping to conclusions till the moment I comprehend the technology and reasoning behind design decisions. Down the down you’ll see what I mean.

Querying through (Honey)SQL

Run a simple query first, fetch all data from person table

(execute
 {:select [:*]
  :from :person})

The result:

[#:person{:id 1,
          :first_name "Ulrich",
          :last_name "",
          :email "ulrich@kakania.at"}
 #:person{:id 2,
          :first_name "Agatha",
          :last_name "",
          :email "agatha@kakania.at"}]

Borrower Agatha

Now, let’s make things a little complicated and fetch books from registry where the borrower is Agatha.2 A regular SQL:

SELECT book.title, 
       registry.borrow_date,
       borrower.first_name AS borrower,
       patron.first_name AS patron
FROM registry
         INNER JOIN book ON registry.book_id = book.id
         INNER JOIN person AS borrower ON registry.borrower_id = borrower.id
         INNER JOIN person AS patron ON registry.patron_id = patron.id
WHERE borrower.email = 'agatha@kakania.at';

HoneySQL:

(execute
 {:select [:book.title
           :registry.borrow-date
           [:borrower.first-name :borrower]
           [:patron.first-name :patron]]
  :from [:registry]
  :join [:book [:= :registry.book-id :book.id]
        [:person :borrower] [:= :registry.borrower-id :borrower.id]
        [:person :patron] [:= :registry.patron-id :patron.id]]
  :where [:= :borrower.email "agatha@kakania.at"]})

To view the results and the complete blog, please continue reading : https://flexiana.com/2023/08/side-by-side-datomic-and-postgresql-part-1-2