Time-travel with Immutable SQL Databases

Time-travel with Immutable SQL Databases

févr. 8, 2025 · by Aziz Sereme

Time-travel with Immutable SQL Databases

Clojure Overview

What is Clojure?

Clojure is a dynamic, general-purpose programming language that runs on the Java Virtual Machine (JVM). It's a dialect of Lisp, emphasizing functional programming, immutability, and data-oriented design. It's known for its conciseness, power, and ability to handle concurrency effectively.

What is Clojure Good For?

  • Data Processing: Clojure's focus on immutability and data structures makes it excellent for data manipulation and transformation. It's often used in ETL (Extract, Transform, Load) pipelines and data analysis.

  • Web Development: Clojure has several robust web frameworks (e.g., Ring, Compojure, Pedestal) and libraries that make it suitable for building web applications. Its functional nature can lead to more maintainable code.

  • Concurrency:** Clojure's immutability makes dealing with concurrent operations much easier. It provides powerful tools for managing state and parallelism.

  • Distributed Systems:** Clojure's ability to run on the JVM and its focus on data makes it well-suited for building distributed systems.

  • Cloud Computing: Clojure runs well in cloud environments and is often used with cloud platforms.

  • Specific Niches: Finance, e-commerce, and other data-intensive fields often benefit from Clojure's strengths.

How to Get Started

  1. Install Leiningen (Lein): Leiningen is the standard build tool for Clojure. It simplifies project management, dependency management, and REPL usage. Follow the instructions on the Leiningen website for your operating system.

  2. Create a Project: Use Lein to create a new Clojure project: `lein new my-clojure-project`

  3. Start a REPL: Navigate to your project directory and start a REPL: `lein repl`

  4. Learn the Basics: Familiarize yourself with Clojure's core data structures (lists, vectors, maps), functions, and syntax. Online resources like Clojure.org and "Clojure for the Brave and True" are excellent starting points.

Basic Clojure Syntax

  • Lists: `(1 2 3)` (used for function calls, code representation)

  • Vectors: `[1 2 3]` (used for ordered data)

  • Maps: `{ :a 1 :b 2 }` (key-value pairs)

  • Functions: `(defn my-function [x y] (+ x y))`

  • Keywords: `:my-keyword` (used as keys in maps, represent themselves)

Use Cases

  • XTDB: Clojure is commonly used with XTDB, a graph database that emphasizes time and history. Clojure's data-oriented approach aligns well with XTDB's design.

  • Data Analysis: Clojure libraries like Neanderthal and Incanter make it suitable for numerical and statistical computing.

  • Web Applications: Building web services and APIs.

  • Microservices: Creating small, independent services.

Example (REPL Interaction)

lein repl  ; Start the REPL

; Define a function
(defn greet [name]
  (str "Hello, " name "!"))

; Call the function
(greet "World")  ; => "Hello, World!"

; Work with a vector
(def numbers [1 2 3 4 5])
(first numbers)   ; => 1

; Work with a map
(def person {:name "Alice" :age 30})
(:name person)    ; => "Alice"

XTDB

What is XTDB?

XTDB is a bitemporal database that combines the best features of traditional SQL databases, document stores, and graph databases. It's designed for modern applications that require temporal data management and complex querying capabilities.

Key Features

Bitemporality

Records both valid time (when facts were true in reality) and transaction time (when facts were recorded) Enables time-travel queries and audit trails Perfect for compliance, historical analysis, and data lineage

Schemaless Documents

Store data without predefined schemas Flexible document model similar to MongoDB Schema-on-read approach Supports nested data structures

Graph Queries

Query data as a graph without special modeling Natural representation of relationships Powerful traversal capabilities Compatible with both document and graph paradigms

SQL Support

Familiar SQL syntax for queries Combines SQL with document and graph capabilities Easy transition for SQL developers JDBC driver support

Immutable Data Model

All changes are additions, never mutations Complete audit history Data integrity guarantees Perfect for compliance requirements

Use Cases

Financial Services

  • Transaction history

  • Audit trails

  • Regulatory compliance

  • Historical reporting

Healthcare

  • Patient records over time

  • Treatment history

  • Regulatory compliance

  • Data lineage

Supply Chain

  • Product tracking

  • Inventory history

  • Relationship management

  • Timeline analysis

Technical Advantages

Simple Architecture

  • Single source of truth

  • No separate event log

  • Easy to reason about

  • Simplified deployment

ACID Transactions

  • Strong consistency guarantees

  • Multi-document transactions

  • Reliable data operations

  • Safe concurrent access

Excellent Performance

  • Efficient indexing

  • Fast temporal queries

  • Good read/write balance

  • Scalable design

Code Example: Basic Operations

;; Start a node
(def xtdb-node
(xt/start-node {:xtdb/tx-log {:kv-store {:xtdb/module 'xtdb.rocksdb/->kv-store}}
:xtdb/document-store {:kv-store {:xtdb/module 'xtdb.rocksdb/->kv-store}}}))

;; Put a document
(xt/submit-tx xtdb-node
[[::xt/put
{:xt/id :person/joe
:name "Joe"
:role "Developer"}]])
;; Query data
(xt/q (xt/db xtdb-node)
'{:find [name role]
:where [[e :name name]
[e :role role]]})
;; Time-travel query (as-of specific point in time)
(xt/q (xt/db xtdb-node #inst "2023-01-01")
'{:find [name role]
:where [[e :name name]
[e :role role]]})

Getting Started

Installation

;; Add to deps.edn
{:deps {com.xtdb/xtdb-core {:mvn/version "LATEST"}}}

Basic Setup Steps

  • Add XTDB dependency

  • Configure storage backend

  • Start XTDB node

  • Begin writing queries

VS Code & Calva

Development Workflow

Start with simple queries Use REPL-driven development Test temporal scenarios Document your data model

XTDB Documentation: https://docs.xtdb.com Clojure API Reference Tutorials and Guides

Conclusion

XTDB offers: Powerful temporal capabilities Flexible data modeling Strong consistency guarantees Modern development experience Active community

Demo

["Clojure" "XTDB" "SQL" "Immutable" "Immutable DB" "Python" "PostgreSQL wire protocol" "Immutable Databases" "Bitemporal Model"]