JDBC

Crux nodes can use JDBC databases to store their transaction logs and/or document stores.

Project Dependencies

deps.edn
pom.xml

pro.juxt.crux/crux-jdbc {:mvn/version "1.18.1"}

<dependency>
    <groupId>pro.juxt.crux</groupId>
    <artifactId>crux-jdbc</artifactId>
    <version>1.18.1</version>
</dependency>

Example configuration

JDBC transaction logs and document stores depend on a 'connection pool' component - if you use both, they can share the same connection pool.

Connection pools require a JDBC 'dialect' - out of the box, Crux supports the following:

H2: crux.jdbc.h2/->dialect
MySQL: crux.jdbc.mysql/->dialect
Microsoft SQL Server: crux.jdbc.mssql/->dialect
Oracle: crux.jdbc.oracle/->dialect
PostgreSQL: crux.jdbc.psql/->dialect
SQLite: crux.jdbc.sqlite/->dialect

Each of these also require an additional dependency to pull in the relevant JDBC driver - see the Crux JDBC project.clj for our latest dependencies.

The connection pool also requires a db-spec - a map containing either a full jdbcUrl or its component parts, including dbtype (provided by the dialect by default), host, port, dbname. Any other attributes supplied (user, password, say) are appended to the URL as query parameters - see your individual JDBC driver for full details.

Crux uses HikariCP to provide connection pools. You can pass options directly to HikariConfig via pool-opts - for example, to setMaximumPoolSize, add maximumPoolSize to your configuration.

JDBC as a Transaction Log

JSON
Clojure
EDN

{
  "crux/tx-log": {
    "crux/module": "crux.jdbc/->tx-log",
    "connection-pool": {
      "dialect": {
        "crux/module": "crux.jdbc.psql/->dialect"
      },
      "pool-opts": { ... },
      "db-spec": {
        "jdbcUrl": "...",
        // OR
        "host":"...",
        "dbname":"...",
        "user":"...",
        "password":"...",
        ...
        }
    },

    "poll-sleep-duration": "PT1S"
  },

  ...
}

{:crux/tx-log {:crux/module 'crux.jdbc/->tx-log
               :connection-pool {:dialect {:crux/module 'crux.jdbc.psql/->dialect}
                                 :pool-opts { ... }
                                 :db-spec {:jdbcUrl "..."
                                           ;; OR
                                           :host "..."
                                           :dbname "..."
                                           :user "..."
                                           :password "..."
                                           ... }}
               :poll-sleep-duration (Duration/ofSeconds 1)}
 ...}

{:crux/tx-log {:crux/module crux.jdbc/->tx-log
               :connection-pool {:dialect {:crux/module crux.jdbc.psql/->dialect}
                                 :pool-opts { ... }
                                 :db-spec {:jdbcUrl "..."
                                           ;; OR
                                           :host "..."
                                           :dbname "..."
                                           :user "..."
                                           :password "..."
                                           ... }}
               :poll-sleep-duration "PT1S"}
 ...}

JDBC as a Document Store

JSON
Clojure
EDN

{
  "crux/document-store": {
    "crux/module": "crux.jdbc/->document-store",
    "connection-pool": {
      "dialect": {
        "crux/module": "crux.jdbc.psql/->dialect"
      },
      "pool-opts": { ... },
      "db-spec": { ... }
    }
  },

  ...
}

{:crux/document-store {:crux/module 'crux.jdbc/->document-store
                       :connection-pool {:dialect {:crux/module 'crux.jdbc.psql/->dialect}
                                         :pool-opts { ... }
                                         :db-spec { ... }}}
 ...}

{:crux/document-store {:crux/module crux.jdbc/->document-store
                       :connection-pool {:dialect {:crux/module crux.jdbc.psql/->dialect}
                                         :pool-opts { ... }
                                         :db-spec { ... }}}
 ...}

Sharing connection pools

If you use JDBC for both the transaction log and document store, you can share the same connection pool between the two modules as follows:

JSON
Clojure
EDN

{
  "crux.jdbc/connection-pool": {
    "dialect": {
      "crux/module": "crux.jdbc.psql/->dialect"
    },
    "pool-opts": { ... },
    "db-spec": { ... }
  },


  "crux/document-store": {
    "crux/module": "crux.jdbc/->document-store",
    "connection-pool": "crux.jdbc/connection-pool"
  },

  "crux/tx-log": {
    "crux/module": "crux.jdbc/->tx-log",
    "connection-pool": "crux.jdbc/connection-pool"
  },

  ...
}

{:crux.jdbc/connection-pool {:dialect {:crux/module 'crux.jdbc.psql/->dialect}
                             :pool-opts { ... }
                             :db-spec { ... }}
 :crux/tx-log {:crux/module 'crux.jdbc/->tx-log
               :connection-pool :crux.jdbc/connection-pool}
 :crux/document-store {:crux/module 'crux.jdbc/->document-store
                       :connection-pool :crux.jdbc/connection-pool}
 ...}

{:crux.jdbc/connection-pool {:dialect {:crux/module crux.jdbc.psql/->dialect}
                             :pool-opts { ... }
                             :db-spec { ... }}
 :crux/tx-log {:crux/module crux.jdbc/->tx-log
               :connection-pool :crux.jdbc/connection-pool}
 :crux/document-store {:crux/module crux.jdbc/->document-store
                       :connection-pool :crux.jdbc/connection-pool}
 ...}

Parameters

Connection pool (`crux.jdbc/->connection-pool`)

dialect (dialect, required): JDBC dialect
pool-opts (map): see above
db-spec (map, required): see above

Transaction log (`crux.jdbc/->tx-log`)

connection-pool
poll-sleep-duration (string/Duration, default 100 milliseconds, "PT0.1S"): time to sleep between each poll, if the previous poll didn’t yield any transactions.

Document store (`crux.jdbc/->document-store`)

connection-pool
cache-size (int): size of in-memory document cache

Reference