JDBC
XTDB nodes can use JDBC databases to store their transaction logs and/or document stores.
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, XTDB supports the following:
-
H2:
xtdb.jdbc.h2/->dialect -
MySQL:
xtdb.jdbc.mysql/->dialect -
Microsoft SQL Server:
xtdb.jdbc.mssql/->dialect -
Oracle:
xtdb.jdbc.oracle/->dialect -
PostgreSQL:
xtdb.jdbc.psql/->dialect -
SQLite:
xtdb.jdbc.sqlite/->dialect
Each of these also require an additional dependency to pull in the relevant JDBC driver - see the XTDB 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.
Please note that you are responsible for the URL encoding of the values you provide in db-spec. JDBC drivers handle URL encoding differently and XTDB will not URL encode the values you provide. Hence in case you provide a full jdbcUrl, make sure it is consistent with the requirements of your JDBC driver; alternatively if you chose to provide individual attributes (dbname, user, password, …), make sure their corresponding values are correctly URL encoded according to your driver’s requirements.
XTDB 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.
To avoid confusion and unexpected behaviours in cases where the database server’s timezone differs from the JVM timezone, it is recommended to configure all database servers and JVMs to operate on UTC (e.g. using -Duser.timezone=UTC).
Alternatively, a serverTimezone parameter may be configured as part of the JDBC connection db-spec configuration (e.g. "serverTimezone": "UTC").
JDBC as a Transaction Log
{
"xtdb/tx-log": {
"xtdb/module": "xtdb.jdbc/->tx-log",
"connection-pool": {
"dialect": {
"xtdb/module": "xtdb.jdbc.psql/->dialect"
},
"pool-opts": { ... },
"db-spec": {
"jdbcUrl": "...",
// OR
"host":"...",
"dbname":"...",
"user":"...",
"password":"...",
...
}
},
"poll-sleep-duration": "PT1S"
},
...
}{:xtdb/tx-log {:xtdb/module 'xtdb.jdbc/->tx-log
:connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
:pool-opts { ... }
:db-spec {:jdbcUrl "..."
;; OR
:host "..."
:dbname "..."
:user "..."
:password "..."
... }}
:poll-sleep-duration (Duration/ofSeconds 1)}
...}{:xtdb/tx-log {:xtdb/module xtdb.jdbc/->tx-log
:connection-pool {:dialect {:xtdb/module xtdb.jdbc.psql/->dialect}
:pool-opts { ... }
:db-spec {:jdbcUrl "..."
;; OR
:host "..."
:dbname "..."
:user "..."
:password "..."
... }}
:poll-sleep-duration "PT1S"}
...}JDBC as a Document Store
{
"xtdb/document-store": {
"xtdb/module": "xtdb.jdbc/->document-store",
"connection-pool": {
"dialect": {
"xtdb/module": "xtdb.jdbc.psql/->dialect"
},
"pool-opts": { ... },
"db-spec": { ... }
}
},
...
}{:xtdb/document-store {:xtdb/module 'xtdb.jdbc/->document-store
:connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
:pool-opts { ... }
:db-spec { ... }}}
...}{:xtdb/document-store {:xtdb/module xtdb.jdbc/->document-store
:connection-pool {:dialect {:xtdb/module xtdb.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:
{
"xtdb.jdbc/connection-pool": {
"dialect": {
"xtdb/module": "xtdb.jdbc.psql/->dialect"
},
"pool-opts": { ... },
"db-spec": { ... }
},
"xtdb/document-store": {
"xtdb/module": "xtdb.jdbc/->document-store",
"connection-pool": "xtdb.jdbc/connection-pool"
},
"xtdb/tx-log": {
"xtdb/module": "xtdb.jdbc/->tx-log",
"connection-pool": "xtdb.jdbc/connection-pool"
},
...
}{:xtdb.jdbc/connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
:pool-opts { ... }
:db-spec { ... }}
:xtdb/tx-log {:xtdb/module 'xtdb.jdbc/->tx-log
:connection-pool :xtdb.jdbc/connection-pool}
:xtdb/document-store {:xtdb/module 'xtdb.jdbc/->document-store
:connection-pool :xtdb.jdbc/connection-pool}
...}{:xtdb.jdbc/connection-pool {:dialect {:xtdb/module xtdb.jdbc.psql/->dialect}
:pool-opts { ... }
:db-spec { ... }}
:xtdb/tx-log {:xtdb/module xtdb.jdbc/->tx-log
:connection-pool :xtdb.jdbc/connection-pool}
:xtdb/document-store {:xtdb/module xtdb.jdbc/->document-store
:connection-pool :xtdb.jdbc/connection-pool}
...}