Table of Contents

This guide covers Datomic’s two primary mechanisms for reading data: Datalog queries and the Pull API. Both are powerful, composable, and fundamentally different from SQL—yet every concept has a SQL analogue that we show side by side.

We continue using the game studio database from the my previous post about schema modeling guide: users, games, play sessions, achievements, and leaderboards. All examples assume that schema and sample data are already transacted.

How Datomic Reads Differ from SQL
#

In SQL, you write SELECT statements that specify columns from tables with joins and filters. In Datomic, you work at the datom level:

[entity-id  attribute  value  transaction-id]

A Datalog query pattern-matches against these datoms using logic variables (prefixed with ?). The query engine finds all combinations of variable bindings that satisfy every clause in the :where section.

Two Complementary Tools
#

Datalog queries (d/q): Declarative pattern matching with joins, filtering, aggregation, negation, disjunction, and rules. Returns flat result sets (tuples).
Pull API (d/pull): Declarative, hierarchical data retrieval for a single entity or integrated into queries. Returns nested maps.

These two compose beautifully: you use Datalog to find entities, and Pull to shape what you retrieve about each one.

Part 1: Your First Query
#

Anatomy of a Datalog Query
#

Every Datomic query is a map (or vector) with these keys:

Key	Purpose	Required?
`:find`	What to return (variables, aggregates)	Yes
`:where`	Pattern clauses that must all be satisfied	Yes
`:in`	Input parameters (including the database)	No (defaults to `$`)
`:with`	Variables to keep for correct aggregation	No
`:keys`	Return maps with keyword keys	No
`:strs`	Return maps with string keys	No
`:syms`	Return maps with symbol keys	No

A Simple Select
#

Find all game titles:

;; Datomic Datalog
(d/q '[:find ?title
        :where [?g :game/title ?title]]
     db)
;; => #{["Dragon's Quest Online"] ["Puzzle Planet"]}

-- SQL equivalent
SELECT title FROM games;

In the :where clause, [?g :game/title ?title] is a data pattern that matches every datom where the attribute is :game/title. It binds the entity ID to ?g and the value to ?title.

Filtering with Multiple Clauses
#

Find titles of published games:

(d/q '[:find ?title
        :where
        [?g :game/title ?title]
        [?g :game/published? true]]
     db)
;; => #{["Dragon's Quest Online"] ["Puzzle Planet"]}

SELECT title FROM games WHERE published = TRUE;

The key insight: joins happen automatically. Because ?g appears in both clauses, Datomic unifies them—only entities that satisfy both patterns are returned. There is no explicit JOIN keyword.

The Blank Placeholder `"_"`
#

If you don’t care about a position in a datom, use _:

;; Find all entity IDs that have any game/title (we don't care what the title is)
(d/q '[:find ?g
        :where [?g :game/title _]]
     db)

Implicit Joins: The Power of Shared Variables
#

Find usernames of users who have played “Dragon’s Quest Online”:

(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        [?s :session/user ?u]
        [?s :session/game ?g]
        [?g :game/title "Dragon's Quest Online"]]
     db)
;; => #{["alice_gamer"] ["bob_admin"]}

SELECT u.username
FROM users u
  JOIN play_sessions s ON s.user_id = u.id
  JOIN games g         ON s.game_id = g.id
WHERE g.title = 'Dragon''s Quest Online';

Notice how there is no JOIN syntax in Datalog. The variable ?u appearing in both [?u :user/username ?username] and [?s :session/user ?u] creates the join. Similarly, ?g joins sessions to games.

Part 2: Input Parameters
#

The `:in` Clause
#

Rather than hard-coding values in :where, you can parameterize queries with the :in clause. The first input is conventionally the database itself, denoted $.

;; Parameterized query: find games by genre
(d/q '[:find ?title
        :in $ ?genre
        :where
        [?g :game/title ?title]
        [?g :game/genre ?genre]]
     db
     :game.genre/mmorpg)
;; => #{["Dragon's Quest Online"]}

-- SQL with parameter binding
SELECT title FROM games
  JOIN game_genres ON games.id = game_genres.game_id
WHERE genre = 'mmorpg';

-- Or with a prepared statement:
SELECT title FROM games WHERE genre = ?;  -- bind: 'mmorpg'

Collection Binding (`[$var ...]`)
#

Pass a collection of values—like SQL’s IN clause:

;; Find games on any of these platforms
(d/q '[:find ?title
        :in $ [?platform ...]
        :where
        [?g :game/title ?title]
        [?g :game/platforms ?platform]]
     db
     [:game.platform/pc :game.platform/mobile])
;; => #{["Dragon's Quest Online"] ["Puzzle Planet"]}

SELECT DISTINCT g.title
FROM games g
  JOIN game_platforms gp ON g.id = gp.game_id
  JOIN platforms p       ON gp.platform_id = p.id
WHERE p.name IN ('pc', 'mobile');

Tuple Binding (`[$var1 $var2]`)
#

Bind multiple values from a single tuple:

;; Find the session for a specific user and game
(d/q '[:find ?started
        :in $ [?username ?game-title]
        :where
        [?u :user/username ?username]
        [?g :game/title ?game-title]
        [?s :session/user ?u]
        [?s :session/game ?g]
        [?s :session/started-at ?started]]
     db
     ["alice_gamer" "Dragon's Quest Online"])

Relation Binding (`[$[$var1 $var2]]`)
#

Pass a set of tuples—like joining against an inline temporary table:

;; Find scores for specific user+game combinations
(d/q '[:find ?username ?title ?score
        :in $ [[?username ?title]]
        :where
        [?u :user/username ?username]
        [?g :game/title ?title]
        [?s :session/user ?u]
        [?s :session/game ?g]
        [?s :session/score ?score]]
     db
     [["alice_gamer" "Dragon's Quest Online"]
      ["bob_admin"   "Dragon's Quest Online"]])

-- SQL equivalent: join against a values list
SELECT u.username, g.title, s.score
FROM users u
  JOIN play_sessions s ON s.user_id = u.id
  JOIN games g         ON s.game_id = g.id
  JOIN (VALUES ('alice_gamer', 'Dragon''s Quest Online'),
               ('bob_admin',   'Dragon''s Quest Online'))
       AS params(username, title)
    ON u.username = params.username AND g.title = params.title;

Part 3: Find Specifications
#

The :find clause controls the shape of the result.

Relation (Default): `:find ?a ?b`
#

Returns a set of tuples:

(d/q '[:find ?username ?email
        :where
        [?u :user/username ?username]
        [?u :user/email ?email]]
     db)
;; => #{["alice_gamer" "alice@example.com"]
;;      ["bob_admin"   "bob@example.com"]
;;      ["charlie_dev" "charlie@studio.example.com"]}

Scalar: `:find ?a .`
#

Returns a single scalar value (the dot after the variable):

(d/q '[:find ?email .
        :where
        [?u :user/username "alice_gamer"]
        [?u :user/email ?email]]
     db)
;; => "alice@example.com"

-- SQL: implicitly scalar when only one row/column
SELECT email FROM users WHERE username = 'alice_gamer';

Collection: `:find [?a ...]`
#

Returns a flat collection of values:

(d/q '[:find [?title ...]
        :where [_ :game/title ?title]]
     db)
;; => ["Dragon's Quest Online" "Puzzle Planet"]

Tuple: `:find [?a ?b]`
#

Returns a single tuple:

(d/q '[:find [?username ?email]
        :where
        [?u :user/username "alice_gamer"]
        [?u :user/username ?username]
        [?u :user/email ?email]]
     db)
;; => ["alice_gamer" "alice@example.com"]

Returning Maps with `:keys`
#

Instead of positional tuples, return maps with named keys:

(d/q '[:find ?username ?email ?active
        :keys user/name user/email user/active?
        :where
        [?u :user/username ?username]
        [?u :user/email ?email]
        [?u :user/active? ?active]]
     db)
;; => [{:user/name "alice_gamer"
;;       :user/email "alice@example.com"
;;       :user/active? true}
;;      ...]

This is similar to SQL’s column aliasing but with structured keys.

Part 4: Predicate Expressions (WHERE Conditions)
#

Predicate expressions filter results using functions that return true/false. They are written in the :where clause inside a list form.

Comparison Predicates
#

;; Games with rating above 4.0
(d/q '[:find ?title ?rating
        :where
        [?g :game/title ?title]
        [?g :game/rating ?rating]
        [(> ?rating 4.0)]]
     db)
;; => #{["Dragon's Quest Online" 4.5] ["Puzzle Planet" 4.2]}

SELECT title, rating FROM games WHERE rating > 4.0;

Multiple Predicates (AND)
#

;; Sessions longer than 60 minutes with score above 10000
(d/q '[:find ?username ?score ?minutes
        :where
        [?s :session/user ?u]
        [?u :user/username ?username]
        [?s :session/score ?score]
        [?s :session/duration-minutes ?minutes]
        [(> ?minutes 60)]
        [(> ?score 10000)]]
     db)

SELECT u.username, s.score, s.duration_minutes
FROM play_sessions s
  JOIN users u ON s.user_id = u.id
WHERE s.duration_minutes > 60
  AND s.score > 10000;

Range Queries
#

;; Games released between two dates
(d/q '[:find ?title ?date
        :in $ ?start ?end
        :where
        [?g :game/title ?title]
        [?g :game/release-date ?date]
        [(<= ?start ?date)]
        [(< ?date ?end)]]
     db
     #inst "2025-01-01"
     #inst "2026-01-01")

SELECT title, release_date
FROM games
WHERE release_date >= '2025-01-01'
  AND release_date < '2026-01-01';

String Predicates with Clojure Functions
#

You can call any pure Clojure function as a predicate:

;; Usernames starting with "a"
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        [(.startsWith ^String ?username "a")]]
     db)
;; => #{["alice_gamer"]}

SELECT username FROM users WHERE username LIKE 'a%';

Part 5: Function Expressions (Computed Values)
#

Function expressions are like predicates but they bind the return value to a new variable.

Basic Functions
#

;; Calculate session end time from start + duration
(d/q '[:find ?username ?title ?started ?hours
        :where
        [?s :session/user ?u]
        [?u :user/username ?username]
        [?s :session/game ?g]
        [?g :game/title ?title]
        [?s :session/started-at ?started]
        [?s :session/duration-minutes ?mins]
        [(/ ?mins 60.0) ?hours]]
     db)

SELECT u.username, g.title, s.started_at,
       s.duration_minutes / 60.0 AS hours
FROM play_sessions s
  JOIN users u ON s.user_id = u.id
  JOIN games g ON s.game_id = g.id;

String Functions
#

;; Uppercase game titles
(d/q '[:find ?title ?upper
        :where
        [?g :game/title ?title]
        [(.toUpperCase ^String ?title) ?upper]]
     db)
;; => #{["Dragon's Quest Online" "DRAGON'S QUEST ONLINE"]
;;      ["Puzzle Planet" "PUZZLE PLANET"]}

SELECT title, UPPER(title) AS upper_title FROM games;

Using `get-else` for Default Values
#

get-else is a built-in function that returns a default when an attribute is missing (Datomic’s equivalent of COALESCE):

;; Get game description, defaulting to "No description available"
(d/q '[:find ?title ?desc
        :where
        [?g :game/title ?title]
        [(get-else $ ?g :game/description "No description available") ?desc]]
     db)

SELECT title, COALESCE(description, 'No description available') AS description
FROM games;

Using `get-some` for First Available Value
#

get-some tries multiple attributes and returns the first one found:

;; Get user's display name, falling back to username
(d/q '[:find ?name
        :where
        [?u :user/username "alice_gamer"]
        [(get-some $ ?u :user/display-name :user/username) [_ ?name]]]
     db)
;; => #{["Alice"]}

SELECT COALESCE(display_name, username) AS name
FROM users
WHERE username = 'alice_gamer';

Using `ground` for Constant Values
#

;; Bind a constant inside the query
(d/q '[:find ?title ?label
        :where
        [?g :game/title ?title]
        [?g :game/published? true]
        [(ground "Available") ?label]]
     db)

Using `missing?` for NULL Checks
#

Datomic has no NULLs, but missing? checks if an entity lacks a particular attribute:

;; Games without a description
(d/q '[:find ?title
        :where
        [?g :game/title ?title]
        [(missing? $ ?g :game/description)]]
     db)

SELECT title FROM games WHERE description IS NULL;

Part 6: Negation (`not` and `not-join`)
#

Simple Negation with `not`
#

not removes results matching a set of patterns. Variables inside not are unified with the outer query.

;; Users who have NOT unlocked any achievement
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        (not [?u :user/achievements _])]
     db)
;; => #{["charlie_dev"]}

SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM user_achievements ua
    WHERE ua.user_id = u.id
);

Negation with Joins: `not-join`
#

not-join lets you control which variables unify with the outer query. This is necessary when the negation introduces new variables.

;; Users who have never played "Puzzle Planet"
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        (not-join [?u]
          [?s :session/user ?u]
          [?s :session/game ?g]
          [?g :game/title "Puzzle Planet"])]
     db)
;; => #{["bob_admin"] ["charlie_dev"]}

SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM play_sessions s
      JOIN games g ON s.game_id = g.id
    WHERE s.user_id = u.id
      AND g.title = 'Puzzle Planet'
);

The [?u] after not-join specifies that only ?u should unify with the outer query. The variables ?s and ?g are local to the negation block.

Part 7: Disjunction (`or` and `or-join`)
#

Simple Disjunction with `or`
#

;; Games that are RPG or MMORPG
(d/q '[:find ?title
        :where
        [?g :game/title ?title]
        (or [?g :game/genre :game.genre/rpg]
            [?g :game/genre :game.genre/mmorpg])]
     db)
;; => #{["Dragon's Quest Online"]}

SELECT title FROM games WHERE genre IN ('rpg', 'mmorpg');

Disjunction with `and` Branches
#

Each branch of an or can contain multiple clauses wrapped in and:

;; Games that are either:
;;   - rated above 4.0 AND published, OR
;;   - released before 2025
(d/q '[:find ?title
        :where
        [?g :game/title ?title]
        (or (and [?g :game/rating ?r]
                 [(> ?r 4.0)]
                 [?g :game/published? true])
            (and [?g :game/release-date ?d]
                 [(< ?d #inst "2025-01-01")]))]
     db)

SELECT title FROM games
WHERE (rating > 4.0 AND published = TRUE)
   OR release_date < '2025-01-01';

`or-join` for Controlled Variable Unification
#

When branches introduce different variables, use or-join to specify which variables bind to the outer query:

;; Find users who either:
;;   - have an admin role, OR
;;   - have played more than 100 minutes in a single session
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        (or-join [?u]
          [?u :user/role :user.role/admin]
          (and [?s :session/user ?u]
               [?s :session/duration-minutes ?mins]
               [(> ?mins 100)]))]
     db)
;; => #{["alice_gamer"] ["bob_admin"]}
;; alice: 150-minute session; bob: admin role

SELECT DISTINCT u.username
FROM users u
WHERE u.role = 'admin'
   OR EXISTS (
       SELECT 1 FROM play_sessions s
       WHERE s.user_id = u.id
         AND s.duration_minutes > 100
   );

Nested Negation and Disjunction
#

These compose freely:

;; Users who are neither admin nor developer
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        (not (or [?u :user/role :user.role/admin]
                 [?u :user/role :user.role/developer]))]
     db)
;; => #{["alice_gamer"]}

SELECT username FROM users
WHERE role NOT IN ('admin', 'developer');

Part 8: Aggregates
#

Datomic provides built-in aggregate functions that appear in the :find clause.

Available Aggregate Functions
#

Aggregate	Description	SQL Equivalent
`(count ?x)`	Count of values	`COUNT(x)`
`(count-distinct ?x)`	Count of distinct values	`COUNT(DISTINCT x)`
`(sum ?x)`	Sum of numeric values	`SUM(x)`
`(avg ?x)`	Arithmetic mean	`AVG(x)`
`(min ?x)`	Minimum value (works on all types)	`MIN(x)`
`(max ?x)`	Maximum value (works on all types)	`MAX(x)`
`(median ?x)`	Median value	`PERCENTILE_CONT(0.5)`
`(variance ?x)`	Population variance	`VAR_POP(x)`
`(stddev ?x)`	Population standard deviation	`STDDEV_POP(x)`
`(rand N ?x)`	N random values (may have duplicates)	`ORDER BY RANDOM() LIMIT N`
`(sample N ?x)`	Up to N distinct random values	(no direct equiv)
`(min N ?x)`	N smallest values	`ORDER BY x LIMIT N`
`(max N ?x)`	N largest values	`ORDER BY x DESC LIMIT N`
`(distinct ?x)`	Set of distinct values	`ARRAY_AGG(DISTINCT x)`

Simple Aggregation
#

;; Total number of play sessions
(d/q '[:find (count ?s) .
        :where [?s :session/user _]]
     db)
;; => 3

SELECT COUNT(*) FROM play_sessions;

Grouped Aggregation
#

When non-aggregate variables appear alongside aggregates, they act as GROUP BY columns automatically:

;; Total play time per user
(d/q '[:find ?username (sum ?mins) (count ?s)
        :where
        [?s :session/user ?u]
        [?u :user/username ?username]
        [?s :session/duration-minutes ?mins]]
     db)
;; => #{["alice_gamer" 195 2]}
;; (bob's session has no duration-minutes yet, so it's not included)

SELECT u.username,
       SUM(s.duration_minutes) AS total_minutes,
       COUNT(*) AS session_count
FROM play_sessions s
  JOIN users u ON s.user_id = u.id
WHERE s.duration_minutes IS NOT NULL
GROUP BY u.username;

The `:with` Clause—Preventing Collapse
#

Datomic returns sets of tuples, so duplicate find-variable combinations are collapsed by default. The :with clause prevents this by including extra variables that keep rows distinct.

;; WRONG: if two sessions have the same score, one is lost
(d/q '[:find (sum ?score)
        :where [?s :session/score ?score]]
     db)

;; CORRECT: :with ?s ensures each session is counted separately
(d/q '[:find (sum ?score) .
        :with ?s
        :where [?s :session/score ?score]]
     db)
;; => 36700   (18500 + 3200 + 15000)

This is critical. Without :with, if two sessions have score 5000, only one 5000 would be summed. The :with clause is Datomic’s way of saying “keep these rows separate for aggregation purposes.”

-- SQL doesn't have this problem because rows are naturally distinct:
SELECT SUM(score) FROM play_sessions WHERE score IS NOT NULL;

Multiple Aggregates
#

;; Statistics about game ratings
(d/q '[:find (min ?r) (max ?r) (avg ?r) (count ?r)
              (median ?r) (stddev ?r)
        :with ?g
        :where
        [?g :game/rating ?r]]
     db)
;; => [[4.2 4.5 4.35 2 4.35 0.15]]

SELECT MIN(rating), MAX(rating), AVG(rating), COUNT(rating),
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY rating),
       STDDEV_POP(rating)
FROM games
WHERE rating IS NOT NULL;

Top-N Aggregates
#

;; The 3 highest session scores
(d/q '[:find (max 3 ?score)
        :with ?s
        :where [?s :session/score ?score]]
     db)
;; => [[[18500 15000 3200]]]

SELECT score FROM play_sessions
WHERE score IS NOT NULL
ORDER BY score DESC
LIMIT 3;

Per-Game Average Score
#

(d/q '[:find ?title (avg ?score) (count ?s)
        :with ?s
        :where
        [?s :session/game ?g]
        [?g :game/title ?title]
        [?s :session/score ?score]]
     db)
;; => #{["Dragon's Quest Online" 16750.0 2]
;;      ["Puzzle Planet" 3200.0 1]}

SELECT g.title, AVG(s.score) AS avg_score, COUNT(*) AS session_count
FROM play_sessions s
  JOIN games g ON s.game_id = g.id
WHERE s.score IS NOT NULL
GROUP BY g.title;

Part 9: The Pull API
#

While Datalog returns flat tuples, Pull returns hierarchical, map-shaped data—perfect for building API responses or rendering UIs.

Basic Pull
#

;; Pull specific attributes from an entity
(d/pull db
  [:user/username :user/email :user/active?]
  [:user/username "alice_gamer"])
;; => {:user/username "alice_gamer"
;;     :user/email    "alice@example.com"
;;     :user/active?  true}

SELECT username, email, active FROM users WHERE username = 'alice_gamer';

Wildcard Pull
#

The * wildcard returns all attributes of an entity:

(d/pull db '[*] [:user/username "alice_gamer"])
;; => {:db/id          17592186045418
;;     :user/username  "alice_gamer"
;;     :user/email     "alice@example.com"
;;     :user/display-name "Alice"
;;     :user/active?   true
;;     :user/role      {:db/id 123456}
;;     :user/joined-at #inst "2024-01-15T00:00:00.000-00:00"
;;     :user/external-id #uuid "a1a1a1a1-0000-0000-0000-000000000001"
;;     :user/achievements #{{:db/id 201} {:db/id 202} {:db/id 203}}
;;     :user/friends   #{{:db/id 301}}}

SELECT * FROM users WHERE username = 'alice_gamer';
-- (But SQL * doesn't follow foreign keys)

Notice that ref attributes return :db/id maps by default (unless the ref is a component, which is auto-expanded).

Nested Pull (Following References)
#

To follow references, use a map spec: a map where the key is the attribute and the value is a nested pull pattern:

;; Pull a user with their role (enum) resolved, and achievements expanded
(d/pull db
  [:user/username
   :user/display-name
   {:user/role [:db/ident]}
   {:user/achievements [:achievement/name :achievement/points]}]
  [:user/username "alice_gamer"])
;; => {:user/username    "alice_gamer"
;;     :user/display-name "Alice"
;;     :user/role        {:db/ident :user.role/player}
;;     :user/achievements [{:achievement/name "First Blood"
;;                           :achievement/points 10}
;;                          {:achievement/name "Dragon Slayer"
;;                           :achievement/points 100}
;;                          {:achievement/name "Eureka!"
;;                           :achievement/points 15}]}

-- SQL requires explicit JOINs:
SELECT u.username, u.display_name, r.name AS role,
       a.name AS achievement, a.points
FROM users u
  LEFT JOIN roles r                ON u.role_id = r.id
  LEFT JOIN user_achievements ua   ON u.id = ua.user_id
  LEFT JOIN achievements a         ON ua.achievement_id = a.id
WHERE u.username = 'alice_gamer';
-- Returns multiple flat rows, needs application-level nesting

Pull gives you nested, hierarchical data in a single call—no client-side assembly of flat rows required.

Deeply Nested Pull
#

Pull patterns can nest arbitrarily:

;; Pull a game with its achievements and the users who unlocked each
(d/pull db
  [:game/title
   {:game/genre [:db/ident]}
   {:game/achievements
    [:achievement/name
     :achievement/points
     {:user/_achievements [:user/username]}]}]
  [:game/title "Dragon's Quest Online"])
;; => {:game/title "Dragon's Quest Online"
;;     :game/genre {:db/ident :game.genre/mmorpg}
;;     :game/achievements
;;       [{:achievement/name "First Blood"
;;         :achievement/points 10
;;         :user/_achievements [{:user/username "alice_gamer"}
;;                              {:user/username "bob_admin"}]}
;;        {:achievement/name "Dragon Slayer"
;;         :achievement/points 100
;;         :user/_achievements [{:user/username "alice_gamer"}]}
;;        {:achievement/name "Social Butterfly"
;;         :achievement/points 25}]}

Notice :user/_achievements=—the reverse reference! Pull follows the =:user/achievements ref backwards to find which users have each achievement.

Component Auto-Expansion
#

Component attributes (:db/isComponent true) are automatically expanded by the wildcard:

;; Prices are components—automatically nested with *
(d/pull db '[* {:game/genre [:db/ident]}]
  [:game/title "Dragon's Quest Online"])
;; The :game/prices key will contain fully expanded price maps
;; [{:price/region "US" :price/amount 49.99M :price/currency "USD"} ...]

Part 10: Pull Options
#

Pull supports several options that control output shape.

`:as` — Rename Keys
#

(d/pull db
  [[:user/username :as :name]
   [:user/email :as :contact]]
  [:user/username "alice_gamer"])
;; => {:name    "alice_gamer"
;;     :contact "alice@example.com"}

SELECT username AS name, email AS contact
FROM users WHERE username = 'alice_gamer';

`:default` — Default Values for Missing Attributes
#

(d/pull db
  [:game/title
   [:game/description :default "No description available"]
   [:game/rating :default 0.0]]
  [:game/title "Puzzle Planet"])
;; If description is missing:
;; => {:game/title "Puzzle Planet"
;;     :game/description "No description available"
;;     :game/rating 4.2}

SELECT title,
       COALESCE(description, 'No description available'),
       COALESCE(rating, 0.0)
FROM games WHERE title = 'Puzzle Planet';

`:limit` — Control Cardinality-Many Results
#

By default, Pull returns the first 1000 values for cardinality-many attributes. You can change this:

;; Only get 2 achievements
(d/pull db
  [:user/username
   {[:user/achievements :limit 2]
    [:achievement/name]}]
  [:user/username "alice_gamer"])
;; => {:user/username "alice_gamer"
;;     :user/achievements [{:achievement/name "First Blood"}
;;                          {:achievement/name "Dragon Slayer"}]}

Use :limit nil to get all values with no cap.

-- SQL equivalent:
SELECT a.name
FROM user_achievements ua
  JOIN achievements a ON ua.achievement_id = a.id
WHERE ua.user_id = (SELECT id FROM users WHERE username = 'alice_gamer')
LIMIT 2;

`:xform` — Transform Values
#

The :xform option applies a function to the pulled value:

(d/pull db
  [[:user/username :xform clojure.string/upper-case]]
  [:user/username "alice_gamer"])
;; => {:user/username "ALICE_GAMER"}

Note: :xform does not apply to :default values.

Part 11: Pull in Queries
#

The real power emerges when you combine Datalog queries with Pull expressions.

Pull Expression in `:find`
#

Instead of binding individual variables, use (pull ?entity pattern) directly in the :find clause:

;; Find all active users with their full profile
(d/q '[:find (pull ?u [:user/username
                        :user/display-name
                        :user/email
                        {:user/role [:db/ident]}])
        :where
        [?u :user/active? true]]
     db)
;; => [[{:user/username "alice_gamer"
;;        :user/display-name "Alice"
;;        :user/email "alice@example.com"
;;        :user/role {:db/ident :user.role/player}}]
;;      [{:user/username "bob_admin" ...}]
;;      [{:user/username "charlie_dev" ...}]]

SELECT u.username, u.display_name, u.email, r.name AS role
FROM users u
  JOIN roles r ON u.role_id = r.id
WHERE u.active = TRUE;

Combining Filtering with Rich Pull
#

;; Find sessions longer than 60 minutes, pull full details
(d/q '[:find (pull ?s [:session/started-at
                        :session/duration-minutes
                        :session/score
                        {:session/user [:user/username :user/display-name]}
                        {:session/game [:game/title]}
                        {:session/status [:db/ident]}
                        {:session/platform [:db/ident]}])
        :where
        [?s :session/duration-minutes ?mins]
        [(> ?mins 60)]]
     db)
;; => [[{:session/started-at #inst "2026-03-14T18:00:00.000Z"
;;        :session/duration-minutes 150
;;        :session/score 18500
;;        :session/user {:user/username "alice_gamer"
;;                       :user/display-name "Alice"}
;;        :session/game {:game/title "Dragon's Quest Online"}
;;        :session/status {:db/ident :session.status/completed}
;;        :session/platform {:db/ident :game.platform/pc}}]]

This is enormously powerful: Datalog does the filtering and joining, while Pull shapes the output into nested maps ready for JSON serialization.

Part 12: Rules—Reusable Query Logic
#

Rules let you package :where clause patterns into named, parameterized, reusable blocks.

Defining Rules
#

A rule is a list of clauses with a head that defines its name and parameters:

;; Rule: "user ?u plays game ?g"
(def game-rules
  '[;; A user plays a game if there's a session linking them
    [(plays? ?u ?g)
     [?s :session/user ?u]
     [?s :session/game ?g]]

    ;; A game is popular if its rating is above 4.0
    [(popular? ?g)
     [?g :game/rating ?r]
     [(> ?r 4.0)]]

    ;; A user is a power user if they have more than 100 minutes in a session
    [(power-user? ?u)
     [?s :session/user ?u]
     [?s :session/duration-minutes ?mins]
     [(> ?mins 100)]]])

Using Rules in Queries
#

Rules are passed as the first :in argument after $, using the % symbol:

;; Find power users who play popular games
(d/q '[:find ?username ?title
        :in $ %
        :where
        [?u :user/username ?username]
        [?g :game/title ?title]
        (power-user? ?u)
        (popular? ?g)
        (plays? ?u ?g)]
     db
     game-rules)
;; => #{["alice_gamer" "Dragon's Quest Online"]}

-- SQL equivalent: inline subqueries or CTEs
WITH power_users AS (
    SELECT DISTINCT user_id FROM play_sessions
    WHERE duration_minutes > 100
),
popular_games AS (
    SELECT id FROM games WHERE rating > 4.0
)
SELECT u.username, g.title
FROM users u
  JOIN power_users pu ON u.id = pu.user_id
  JOIN play_sessions s ON s.user_id = u.id
  JOIN games g ON s.game_id = g.id
  JOIN popular_games pg ON g.id = pg.id;

Rules with Multiple Clauses (OR Semantics)
#

If you define multiple rules with the same name, they act as alternatives (logical OR):

(def engagement-rules
  '[;; A user is "engaged" if they have unlocked achievements
    [(engaged? ?u)
     [?u :user/achievements _]]

    ;; OR if they have played more than 2 sessions
    ;; (simplified: at least 2 sessions)
    [(engaged? ?u)
     [?s1 :session/user ?u]
     [?s2 :session/user ?u]
     [(!= ?s1 ?s2)]]])

;; Find engaged users
(d/q '[:find ?username
        :in $ %
        :where
        [?u :user/username ?username]
        (engaged? ?u)]
     db
     engagement-rules)

Part 13: Recursive Rules—Graph Traversal
#

This is where Datomic truly shines for graph-like data. Rules can call themselves recursively, enabling transitive closure queries that would require recursive CTEs in SQL.

The Friends-of-Friends Problem
#

Our schema has :user/friends (cardinality-many, unidirectional). Let’s find the full social network reachable from a user.

(def social-rules
  '[;; Base case: ?u1 is directly connected to ?u2
    [(connected? ?u1 ?u2)
     [?u1 :user/friends ?u2]]

    ;; Recursive case: ?u1 is connected to ?u2 through an intermediary
    [(connected? ?u1 ?u2)
     [?u1 :user/friends ?intermediate]
     (connected? ?intermediate ?u2)]])

;; Find everyone reachable from alice
(d/q '[:find ?friend-name
        :in $ % ?start-name
        :where
        [?start :user/username ?start-name]
        (connected? ?start ?friend)
        [?friend :user/username ?friend-name]]
     db
     social-rules
     "alice_gamer")
;; => #{["bob_admin"] ["charlie_dev"]}
;; alice → bob (direct), bob → charlie (through bob)

-- SQL requires recursive CTEs:
WITH RECURSIVE connected AS (
    -- Base case: direct friends
    SELECT friend_id AS user_id
    FROM user_friends
    WHERE user_id = (SELECT id FROM users WHERE username = 'alice_gamer')

    UNION

    -- Recursive case: friends of friends
    SELECT uf.friend_id
    FROM user_friends uf
      JOIN connected c ON uf.user_id = c.user_id
)
SELECT u.username
FROM connected c
  JOIN users u ON c.user_id = u.id;

Datomic’s rule-based recursion automatically handles cycle detection (it terminates when no new bindings are found), making it safer and simpler than SQL’s recursive CTEs.

Bidirectional Friendship
#

Since :user/friends is unidirectional, we can make the traversal bidirectional in the rules:

(def bidi-social-rules
  '[;; Base: ?a is friends with ?b in either direction
    [(friends? ?a ?b)
     [?a :user/friends ?b]]
    [(friends? ?a ?b)
     [?b :user/friends ?a]]

    ;; Transitive: reachable through friendship chain
    [(reachable? ?a ?b)
     (friends? ?a ?b)]
    [(reachable? ?a ?b)
     (friends? ?a ?mid)
     (reachable? ?mid ?b)]])

Depth-Limited Traversal
#

Datomic rules don’t have a built-in depth limit, but you can encode one by expanding the rule manually:

(def depth-limited-rules
  '[;; Depth 1: direct friends
    [(friends-within-1 ?a ?b)
     [?a :user/friends ?b]]

    ;; Depth 2: friend of a friend
    [(friends-within-2 ?a ?b)
     (friends-within-1 ?a ?b)]
    [(friends-within-2 ?a ?b)
     [?a :user/friends ?mid]
     (friends-within-1 ?mid ?b)]

    ;; Depth 3: extend the chain
    [(friends-within-3 ?a ?b)
     (friends-within-2 ?a ?b)]
    [(friends-within-3 ?a ?b)
     [?a :user/friends ?mid]
     (friends-within-2 ?mid ?b)]])

(d/q '[:find ?name
        :in $ % ?start
        :where
        [?s :user/username ?start]
        (friends-within-2 ?s ?f)
        [?f :user/username ?name]]
     db
     depth-limited-rules
     "alice_gamer")

Hierarchical Data: Category Trees
#

Imagine games organized into categories and subcategories:

;; Schema for categories
[{:db/ident       :category/name
  :db/valueType   :db.type/string
  :db/cardinality :db.cardinality/one
  :db/unique      :db.unique/identity}

 {:db/ident       :category/parent
  :db/valueType   :db.type/ref
  :db/cardinality :db.cardinality/one
  :db/doc         "Parent category (tree structure)"}

 {:db/ident       :game/category
  :db/valueType   :db.type/ref
  :db/cardinality :db.cardinality/one}]

;; Sample data: a category tree
[{:category/name "All Games"}
 {:category/name "Action"
  :category/parent [:category/name "All Games"]}
 {:category/name "Shooter"
  :category/parent [:category/name "Action"]}
 {:category/name "RPG"
  :category/parent [:category/name "All Games"]}
 {:category/name "MMORPG"
  :category/parent [:category/name "RPG"]}]

;; Recursive rule: find all descendant categories
(def category-rules
  '[;; Base: direct child
    [(descendant? ?ancestor ?desc)
     [?desc :category/parent ?ancestor]]

    ;; Recursive: child of a descendant
    [(descendant? ?ancestor ?desc)
     [?desc :category/parent ?mid]
     (descendant? ?ancestor ?mid)]])

;; Find all categories under "All Games"
(d/q '[:find ?name
        :in $ %
        :where
        [?root :category/name "All Games"]
        (descendant? ?root ?cat)
        [?cat :category/name ?name]]
     db
     category-rules)
;; => #{["Action"] ["Shooter"] ["RPG"] ["MMORPG"]}

-- SQL recursive CTE for tree traversal:
WITH RECURSIVE descendants AS (
    SELECT id, name FROM categories WHERE name = 'All Games'
    UNION ALL
    SELECT c.id, c.name
    FROM categories c
      JOIN descendants d ON c.parent_id = d.id
)
SELECT name FROM descendants WHERE name != 'All Games';

Part 14: Pull Recursion
#

The Pull API has its own recursion mechanism for navigating graph-shaped data without writing Datalog rules.

Unlimited Recursion (`...`)
#

;; Pull the entire category tree starting from "All Games"
(d/pull db
  [:category/name {:category/_parent ...}]
  [:category/name "All Games"])
;; => {:category/name "All Games"
;;     :category/_parent
;;       [{:category/name "Action"
;;         :category/_parent
;;           [{:category/name "Shooter"}]}
;;        {:category/name "RPG"
;;         :category/_parent
;;           [{:category/name "MMORPG"}]}]}

The ... symbol means “recurse this map spec to an unlimited depth.” Pull automatically terminates when it reaches leaf entities (those with no children).

Depth-Limited Recursion
#

Use a positive integer instead of ... to limit depth:

;; Only go 1 level deep
(d/pull db
  [:category/name {:category/_parent 1}]
  [:category/name "All Games"])
;; => {:category/name "All Games"
;;     :category/_parent
;;       [{:category/name "Action"}
;;        {:category/name "RPG"}]}
;; Shooter and MMORPG are NOT included (depth limit reached)

Recursion on Forward References
#

Recursion works in either direction. For a linked-list or chain structure:

;; Navigate up from a leaf to the root
(d/pull db
  [:category/name {:category/parent ...}]
  [:category/name "MMORPG"])
;; => {:category/name "MMORPG"
;;     :category/parent
;;       {:category/name "RPG"
;;        :category/parent
;;          {:category/name "All Games"}}}

-- SQL: walk up the tree with a recursive CTE
WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id FROM categories WHERE name = 'MMORPG'
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM categories c
      JOIN ancestors a ON c.id = a.parent_id
)
SELECT name FROM ancestors;

Social Network Pull
#

;; Pull alice's friend network to depth 2
(d/pull db
  [:user/username {:user/friends 2}]
  [:user/username "alice_gamer"])
;; => {:user/username "alice_gamer"
;;     :user/friends
;;       [{:user/username "bob_admin"
;;         :user/friends
;;           [{:user/username "alice_gamer"}   ;; cycle detected, stops
;;            {:user/username "charlie_dev"}]}]}

Pull handles cycles gracefully—when it encounters an entity it has already visited in the current recursion path, it stops without infinite looping.

Part 15: Time Travel Queries
#

Datomic’s immutable architecture means every past state of the database is preserved and queryable. This is like having automatic audit logging and point-in-time recovery built into every query.

`as-of` — Snapshot at a Point in Time
#

;; What was alice's email yesterday?
(def yesterday-db (d/as-of db #inst "2026-03-14T00:00:00.000Z"))

(d/q '[:find ?email .
        :where
        [?u :user/username "alice_gamer"]
        [?u :user/email ?email]]
     yesterday-db)

-- SQL has no native equivalent. You'd need:
-- 1. Temporal tables (SQL:2011, limited vendor support)
-- 2. Manual audit log tables
-- 3. Point-in-time backup restore

-- PostgreSQL temporal (if configured):
SELECT email FROM users
  FOR SYSTEM_TIME AS OF '2026-03-14T00:00:00Z'
WHERE username = 'alice_gamer';

as-of accepts either an Instant or a transaction ID (t value).

`since` — Only Changes After a Point
#

;; What games were added since March 1st?
(def since-db (d/since db #inst "2026-03-01T00:00:00.000Z"))

;; Combine with current db for entity resolution
(d/q '[:find ?title
        :in $ $since
        :where
        [$since ?g :game/title]  ;; new datoms only
        [$ ?g :game/title ?title]]  ;; resolve from current db
     db
     since-db)

Note the two-database pattern: since only sees datoms added after the cutoff, so you often need the current database to resolve attributes that were established earlier.

`history` — The Complete Audit Trail
#

The history database shows all assertions and retractions:

;; Full history of alice's email changes
(d/q '[:find ?email ?tx ?added
        :where
        [?u :user/username "alice_gamer"]
        [?u :user/email ?email ?tx ?added]]
     (d/history db))
;; => #{["alice@example.com" 13194139534313 true]     ;; asserted
;;      ["alice@old-email.com" 13194139534313 false]  ;; retracted
;;      ["alice@old-email.com" 13194139534312 true]}  ;; originally asserted

The fifth binding ?added is a boolean: true for assertions, false for retractions.

-- SQL would require a custom audit table:
SELECT email, changed_at, operation
FROM users_audit
WHERE username = 'alice_gamer'
ORDER BY changed_at;

Comparing Two Points in Time
#

;; What changed between two timestamps?
(let [t1 #inst "2026-03-01T00:00:00.000Z"
      t2 #inst "2026-03-15T00:00:00.000Z"
      db-t1 (d/as-of db t1)
      db-t2 (d/as-of db t2)]

  ;; Games present at t2 but not at t1
  (d/q '[:find ?title
          :in $now $then
          :where
          [$now ?g :game/title ?title]
          (not [$then ?g :game/title])]
       db-t2
       db-t1))

Transaction Metadata
#

Every transaction in Datomic is itself an entity. You can annotate transactions with metadata and query it:

;; Transact with metadata
(d/transact conn
  [{:user/username "alice_gamer"
    :user/email    "alice@newest.com"}
   {:db/id "datomic.tx"
    :audit/who   "admin_bob"
    :audit/why   "User requested email change"
    :audit/when  #inst "2026-03-15T10:00:00.000Z"}])

;; Query: who changed alice's email and why?
(d/q '[:find ?who ?why ?when
        :where
        [?u :user/username "alice_gamer"]
        [?u :user/email _ ?tx]
        [?tx :audit/who ?who]
        [?tx :audit/why ?why]
        [?tx :audit/when ?when]]
     (d/history db))

This is vastly more elegant than maintaining separate audit tables in SQL.

Part 16: Advanced Analytical Queries
#

Subquery Pattern: Using Query Results as Input
#

Datomic doesn’t have subqueries in the SQL sense, but you can compose queries by feeding results from one query into another:

;; Step 1: Find the average score across all sessions
(def avg-score
  (d/q '[:find (avg ?score) .
          :with ?s
          :where [?s :session/score ?score]]
       db))

;; Step 2: Find sessions above average
(d/q '[:find ?username ?title ?score
        :in $ ?threshold
        :where
        [?s :session/score ?score]
        [(> ?score ?threshold)]
        [?s :session/user ?u]
        [?u :user/username ?username]
        [?s :session/game ?g]
        [?g :game/title ?title]]
     db
     avg-score)

SELECT u.username, g.title, s.score
FROM play_sessions s
  JOIN users u ON s.user_id = u.id
  JOIN games g ON s.game_id = g.id
WHERE s.score > (SELECT AVG(score) FROM play_sessions WHERE score IS NOT NULL);

HAVING Equivalent: Filter After Aggregation
#

Datomic doesn’t have HAVING, but you compose two queries:

;; Find users with more than 1 play session
;; Step 1: get per-user counts
(def active-users
  (d/q '[:find ?u (count ?s)
          :where
          [?s :session/user ?u]]
       db))

;; Step 2: filter in Clojure
(filter (fn [[_ cnt]] (> cnt 1)) active-users)
;; Or re-query with the filtered user set

SELECT u.username, COUNT(*) AS session_count
FROM play_sessions s
  JOIN users u ON s.user_id = u.id
GROUP BY u.username
HAVING COUNT(*) > 1;

Alternatively, use a rule-based approach:

;; Find users with at least 2 distinct sessions
(d/q '[:find ?username
        :where
        [?u :user/username ?username]
        [?s1 :session/user ?u]
        [?s2 :session/user ?u]
        [(!= ?s1 ?s2)]]
     db)

Cross-Entity Analytics
#

;; Revenue analysis: total price * number of completed sessions per game
(d/q '[:find ?title ?price ?completed-sessions
        :with ?s
        :where
        [?g :game/title ?title]
        [?g :game/price ?price]
        [?s :session/game ?g]
        [?s :session/status :session.status/completed]]
     db)
;; Then compute revenue in application code:
;; (map (fn [[title price sessions]] [title (* price sessions)]) results)

SELECT g.title, g.price,
       COUNT(*) AS completed_sessions,
       g.price * COUNT(*) AS estimated_revenue
FROM games g
  JOIN play_sessions s ON s.game_id = g.id
WHERE s.status = 'completed'
GROUP BY g.title, g.price;

Window Function Equivalent: Ranking
#

Datomic has no window functions, but you can achieve ranking with post-processing:

;; Get all leaderboard entries, sort and rank in application code
(def entries
  (d/q '[:find ?username ?title ?score
          :where
          [?l :leaderboard/user ?u]
          [?u :user/username ?username]
          [?l :leaderboard/game ?g]
          [?g :game/title ?title]
          [?l :leaderboard/high-score ?score]]
       db))

;; Rank in Clojure:
(->> entries
     (sort-by #(nth % 2) >)        ;; sort by score descending
     (map-indexed (fn [i [u t s]]
                    {:rank (inc i) :user u :game t :score s})))

SELECT username, title, high_score,
       RANK() OVER (ORDER BY high_score DESC) AS rank
FROM leaderboard l
  JOIN users u ON l.user_id = u.id
  JOIN games g ON l.game_id = g.id;

Partition-Based Ranking (Per Group)
#

;; Per-game leaderboard ranking
(def per-game-scores
  (d/q '[:find ?title ?username ?score
          :where
          [?l :leaderboard/game ?g]
          [?g :game/title ?title]
          [?l :leaderboard/user ?u]
          [?u :user/username ?username]
          [?l :leaderboard/high-score ?score]]
       db))

;; Group and rank in Clojure:
(->> per-game-scores
     (group-by first)  ;; group by game title
     (map (fn [[title entries]]
            [title (->> entries
                        (sort-by #(nth % 2) >)
                        (map-indexed (fn [i [_ user score]]
                                       {:rank (inc i) :user user :score score})))])))

SELECT title, username, high_score,
       RANK() OVER (PARTITION BY game_id ORDER BY high_score DESC) AS rank
FROM leaderboard l
  JOIN users u ON l.user_id = u.id
  JOIN games g ON l.game_id = g.id;

Part 17: Index-Based Access and Performance
#

Understanding Datomic’s Indexes
#

Datomic maintains four covering indexes over all datoms:

Index	Order	Use Case
EAVT	Entity → Attribute → Value	Pull attributes for an entity
AEVT	Attribute → Entity → Value	Find all entities with an attribute
AVET	Attribute → Value → Entity	Range scans, unique lookups
VAET	Value → Attribute → Entity	Reverse reference navigation

The AVET index is only maintained for attributes with :db/unique or an explicit :db/index true in the schema.

Clause Ordering Matters
#

Datomic evaluates :where clauses in order. Put the most selective clauses first:

;; GOOD: start with the most selective clause
(d/q '[:find ?username
        :where
        [?g :game/title "Dragon's Quest Online"]  ;; very selective (one entity)
        [?s :session/game ?g]                       ;; narrows to sessions for this game
        [?s :session/user ?u]                       ;; gets users from those sessions
        [?u :user/username ?username]]               ;; resolves username
     db)

;; LESS EFFICIENT: starts with a broad scan
(d/q '[:find ?username
        :where
        [?u :user/username ?username]               ;; scans all users
        [?s :session/user ?u]                       ;; all sessions for all users
        [?s :session/game ?g]                       ;; all games for all sessions
        [?g :game/title "Dragon's Quest Online"]]   ;; finally filters
     db)

The first version starts from one game and fans out. The second starts from all users and only filters at the end—potentially much more work.

Using `d/datoms` for Direct Index Access
#

For performance-critical operations, you can bypass Datalog and access indexes directly:

;; Efficiently scan all usernames using the AEVT index
(d/datoms db :aevt :user/username)
;; => sequence of datoms: [e a v tx]

;; Efficiently find an entity by indexed value using AVET
(d/datoms db :avet :user/username "alice_gamer")

Part 18: Multiple Databases in a Single Query
#

Datomic queries can take multiple database values as inputs, enabling cross-database joins and temporal comparisons.

Cross-Time Comparison
#

;; Compare current ratings with ratings from a month ago
(let [month-ago-db (d/as-of db #inst "2026-02-15T00:00:00.000Z")]
  (d/q '[:find ?title ?current-rating ?old-rating
          :in $current $old
          :where
          [$current ?g :game/title ?title]
          [$current ?g :game/rating ?current-rating]
          [$old ?g :game/rating ?old-rating]]
       db
       month-ago-db))

Each database input gets a name ($current, $old) and clauses prefix with the database name to indicate which database to query.

-- SQL has no clean equivalent. You'd need temporal tables or snapshots:
-- (Conceptual only)
SELECT curr.title, curr.rating AS current_rating, old.rating AS old_rating
FROM games_current curr
  JOIN games_snapshot_feb15 old ON curr.id = old.id;

Part 19: Query Patterns Cookbook
#

Exists Check
#

;; Does this user exist?
(some?
  (d/q '[:find ?u .
          :where [?u :user/username "alice_gamer"]]
       db))
;; => true

SELECT EXISTS(SELECT 1 FROM users WHERE username = 'alice_gamer');

Count with Condition
#

;; Count of sessions per status
(d/q '[:find ?status-ident (count ?s)
        :where
        [?s :session/status ?status]
        [?status :db/ident ?status-ident]]
     db)
;; => #{[:session.status/completed 2] [:session.status/active 1]}

SELECT status, COUNT(*) FROM play_sessions GROUP BY status;

Distinct Values
#

;; All genres that have at least one game
(d/q '[:find [?genre-ident ...]
        :where
        [_ :game/genre ?genre]
        [?genre :db/ident ?genre-ident]]
     db)
;; => [:game.genre/mmorpg :game.genre/puzzle]

SELECT DISTINCT genre FROM games WHERE genre IS NOT NULL;

Self-Join: Find Users with Shared Achievements
#

;; Pairs of users who share at least one achievement
(d/q '[:find ?u1-name ?u2-name ?ach-name
        :where
        [?u1 :user/achievements ?a]
        [?u2 :user/achievements ?a]
        [(!= ?u1 ?u2)]
        [?u1 :user/username ?u1-name]
        [?u2 :user/username ?u2-name]
        [?a :achievement/name ?ach-name]
        ;; Avoid duplicate pairs (alice,bob) and (bob,alice)
        [(< ?u1-name ?u2-name)]]
     db)
;; => #{["alice_gamer" "bob_admin" "First Blood"]}

SELECT u1.username, u2.username, a.name
FROM user_achievements ua1
  JOIN user_achievements ua2 ON ua1.achievement_id = ua2.achievement_id
  JOIN users u1 ON ua1.user_id = u1.id
  JOIN users u2 ON ua2.user_id = u2.id
  JOIN achievements a ON ua1.achievement_id = a.id
WHERE u1.username < u2.username;

Left Outer Join Pattern
#

Datomic doesn’t have explicit outer joins, but get-else and optional patterns achieve similar results:

;; All users with their session count (including users with 0 sessions)
;; Strategy: query users separately, then enrich

;; All users:
(def all-users
  (d/q '[:find [?username ...]
          :where [_ :user/username ?username]]
       db))

;; Session counts (only users with sessions):
(def session-counts
  (into {}
    (d/q '[:find ?username (count ?s)
            :where
            [?s :session/user ?u]
            [?u :user/username ?username]]
         db)))

;; Combine:
(map (fn [u] [u (get session-counts u 0)]) all-users)
;; => (["alice_gamer" 2] ["bob_admin" 1] ["charlie_dev" 0])

SELECT u.username, COUNT(s.id) AS session_count
FROM users u
  LEFT JOIN play_sessions s ON u.id = s.user_id
GROUP BY u.username;

Finding Entities with All Values from a Set
#

;; Games available on BOTH PC and PlayStation
(d/q '[:find ?title
        :where
        [?g :game/title ?title]
        [?g :game/platforms :game.platform/pc]
        [?g :game/platforms :game.platform/playstation]]
     db)
;; => #{["Dragon's Quest Online"]}

SELECT g.title
FROM games g
  JOIN game_platforms gp1 ON g.id = gp1.game_id AND gp1.platform_id = (SELECT id FROM platforms WHERE name = 'pc')
  JOIN game_platforms gp2 ON g.id = gp2.game_id AND gp2.platform_id = (SELECT id FROM platforms WHERE name = 'playstation');

-- Or:
SELECT g.title
FROM games g
  JOIN game_platforms gp ON g.id = gp.game_id
  JOIN platforms p ON gp.platform_id = p.id
WHERE p.name IN ('pc', 'playstation')
GROUP BY g.title
HAVING COUNT(DISTINCT p.name) = 2;

Part 20: Concept Comparison—SQL vs Datomic Queries Cheat Sheet
#

SQL Concept	Datomic Equivalent
`SELECT col`	`:find ?var` or `(pull ?e [...])`
`SELECT *`	`(pull ?e [*])`
`FROM table`	`:in $` (database input)
`WHERE col = val`	`:where [?e :attr val]`
`WHERE col > val`	`[(> ?var val)]`
`WHERE col IS NULL`	`[(missing? $ ?e :attr)]`
`WHERE col IS NOT NULL`	`[?e :attr _]`
`WHERE ... AND ...`	Multiple `:where` clauses (implicit AND)
`WHERE ... OR ...`	`(or ...)` or `(or-join [...] ...)`
`WHERE NOT EXISTS`	`(not ...)` or `(not-join [...] ...)`
`WHERE col IN (...)`	`:in $ [?var ...]` with collection binding
`JOIN`	Shared `?variable` across clauses
`LEFT JOIN`	`get-else` or two-query composition
`GROUP BY`	Automatic: non-aggregate vars in `:find`
`HAVING`	Post-query filter in application code
`ORDER BY`	`sort` / `sort-by` in application code
`LIMIT`	`take` in application code
`DISTINCT`	Default (results are sets)
`COUNT(*)`	`(count ?var)` in `:find`
`SUM(col)`	`(sum ?var)` in `:find` (with `:with`)
`AVG(col)`	`(avg ?var)` in `:find`
`MIN / MAX`	`(min ?var)` / `(max ?var)`
`COALESCE`	`get-else` or `get-some`
`CASE WHEN`	Clojure function in `:where`
`UNION`	`(or ...)` or multiple queries + `concat`
`CREATE VIEW`	Define a Clojure function that runs a query
`WITH RECURSIVE`	Recursive rules in `:in $ %`
`AS OF TIMESTAMP`	`(d/as-of db instant)`
`FOR SYSTEM_TIME`	`(d/history db)`
`RANK() OVER(...)`	Post-query sort + `map-indexed` in Clojure
Prepared statement	`:in $ ?param` with parameterized queries
Stored procedure	Transaction functions

Part 21: Design Guidance
#

When to Use Datalog vs Pull
#

Scenario	Use	Why
Find entities matching criteria	Datalog	Pattern matching, filtering
Get attributes of a known entity	Pull	Direct, hierarchical
Complex joins across entity types	Datalog	Automatic join via variables
Nested/hierarchical API responses	Pull in `:find`	One query, nested result
Aggregation (counts, sums)	Datalog	Built-in aggregates
Tree/graph traversal (bounded)	Pull with recursion	Simpler syntax
Graph traversal (filtered/complex)	Datalog with rules	Full control over traversal logic
Time-travel queries	Datalog with filtered db	`as-of`, `since`, `history`
Audit trail	Datalog on `history` db	See all assertions/retractions

Query Performance Tips
#

Most selective clause first: Start :where with the clause that narrows the result set the most.
Use parameterized queries: The :in clause allows query plan caching.
Use :with correctly: Always include :with when aggregating to prevent accidental deduplication.
Prefer Pull for known entities: When you know the entity ID or lookup ref, d/pull is faster than a query.
Limit Pull results: Use :limit on cardinality-many attributes if you don’t need all values.
Index attributes you filter on: Add :db/index true to attributes used in range scans or value lookups.
Avoid unbounded recursion on large graphs: Use depth-limited recursion (integer) rather than ... when graph size is unpredictable.

Appendix A: Quick Reference—Query Anatomy
#

(d/q
  '{:find  [?var1 (aggregate ?var2) (pull ?e [:attr ...])]
    :keys  [key1 key2 key3]            ;; optional: return maps
    :with  [?var3]                      ;; optional: prevent dedup
    :in    [$ % ?param [?coll ...]]     ;; optional: inputs
    :where [[?e :attr ?val]             ;; data patterns
            [(predicate ?val)]          ;; predicate expressions
            [(function ?val) ?result]   ;; function expressions
            (not [?e :attr2 _])         ;; negation
            (or [?e :attr3 "a"]         ;; disjunction
                [?e :attr3 "b"])
            (rule-name ?e ?val)]}       ;; rule invocation
  db                                    ;; $  - database
  rules                                 ;; %  - rules
  param-value                           ;; ?param
  [:val1 :val2])                        ;; [?coll ...]

Appendix B: Quick Reference—Pull Pattern Syntax
#

;; Attribute name
:user/username

;; Attribute with options
[:user/username :as :name]
[:user/email :default "unknown"]
[:user/display-name :xform clojure.string/upper-case]

;; Nested reference (map spec)
{:user/role [:db/ident]}
{:session/user [:user/username :user/email]}

;; Nested with limit
{[:user/achievements :limit 5] [:achievement/name]}

;; Reverse reference
:session/_user   ;; "sessions that reference this user"

;; Recursive (unlimited)
{:category/_parent ...}

;; Recursive (depth-limited)
{:category/_parent 3}

;; Wildcard
*

;; Combined pattern
[:user/username
 :user/email
 [:user/display-name :default "Anonymous" :as :name]
 {:user/role [:db/ident]}
 {[:user/achievements :limit 10]
  [:achievement/name :achievement/points]}
 {:user/friends 2}]

Appendix C: Built-in Query Functions
#

Function	Purpose	Example
`get-else`	Default value for missing attr	`[(get-else $ ?e :attr "default") ?val]`
`get-some`	First present attr from list	`[(get-some $ ?e :a1 :a2) [?attr ?val]]`
`ground`	Bind a constant	`[(ground 42) ?x]`
`missing?`	Check attr absence	`[(missing? $ ?e :attr)]`
`!`	Not equal	`[(!` ?a ?b)]=
`tuple`	Construct a tuple	`[(tuple ?a ?b) ?tup]`
`untuple`	Destructure a tuple	`[(untuple ?tup) [?a ?b]]`

Appendix D: Further Reading
#

Query Data Reference — Complete Datalog query specification
Pull API — Full Pull pattern syntax and options
Executing Queries — d/q, d/query, and performance
Database Filters — as-of, since, history
Query Overview — Conceptual introduction to Datomic queries
Datalog Enhancements — Negation, disjunction, range optimizations
A Whirlwind Tour of Datomic Query — Classic introduction
Learn Datalog Today — Interactive Datalog tutorial
Using Datomic as a Graph Database — Graph traversal patterns

How Datomic Reads Differ from SQL #

Two Complementary Tools #

Part 1: Your First Query #

Anatomy of a Datalog Query #

A Simple Select #

Filtering with Multiple Clauses #

The Blank Placeholder "_" #

Implicit Joins: The Power of Shared Variables #

Part 2: Input Parameters #

The :in Clause #

Collection Binding ([$var ...]) #

Tuple Binding ([$var1 $var2]) #

Relation Binding ([$[$var1 $var2]]) #

Part 3: Find Specifications #

Relation (Default): :find ?a ?b #

Scalar: :find ?a . #

Collection: :find [?a ...] #

Tuple: :find [?a ?b] #

Returning Maps with :keys #

Part 4: Predicate Expressions (WHERE Conditions) #

Comparison Predicates #

Multiple Predicates (AND) #

Range Queries #

String Predicates with Clojure Functions #

Part 5: Function Expressions (Computed Values) #

Basic Functions #

String Functions #

Using get-else for Default Values #

Using get-some for First Available Value #

Using ground for Constant Values #

Using missing? for NULL Checks #

Part 6: Negation (not and not-join) #

Simple Negation with not #

Negation with Joins: not-join #

Part 7: Disjunction (or and or-join) #

Simple Disjunction with or #

Disjunction with and Branches #

or-join for Controlled Variable Unification #

Nested Negation and Disjunction #

Part 8: Aggregates #

Available Aggregate Functions #

Simple Aggregation #

Grouped Aggregation #

The :with Clause—Preventing Collapse #

Multiple Aggregates #

Top-N Aggregates #

Per-Game Average Score #

Part 9: The Pull API #

Basic Pull #

Wildcard Pull #

Nested Pull (Following References) #

Deeply Nested Pull #

Component Auto-Expansion #

Part 10: Pull Options #

:as — Rename Keys #

:default — Default Values for Missing Attributes #

:limit — Control Cardinality-Many Results #

:xform — Transform Values #

Part 11: Pull in Queries #

Pull Expression in :find #

Combining Filtering with Rich Pull #

Part 12: Rules—Reusable Query Logic #

Defining Rules #

Using Rules in Queries #

Rules with Multiple Clauses (OR Semantics) #

Part 13: Recursive Rules—Graph Traversal #

The Friends-of-Friends Problem #

Bidirectional Friendship #

Depth-Limited Traversal #

Hierarchical Data: Category Trees #

Part 14: Pull Recursion #

Unlimited Recursion (...) #

Depth-Limited Recursion #

Recursion on Forward References #

Social Network Pull #

Part 15: Time Travel Queries #

as-of — Snapshot at a Point in Time #

since — Only Changes After a Point #

history — The Complete Audit Trail #

Comparing Two Points in Time #