The server has built-in support for different databases. You can query the available databases by viewing the expected values for the `db` configuration option. The following table lists the supported databases and their tested versions.
|Oracle Database | `oracle` | ${properties["oracledb.version"]} | 23.x (i.e 23.5+), 19c (19.3+) (*Note:* Oracle RAC is also supported if using the same database engine version, e.g 23.5+, 19.3+)
NOTE: It is not a supported configuration if the underlying database specific Hibernate dialect allows the use of a version that differs from those shown.
By default, the server uses the `dev-file` database. This is the default database that the server will use to persist data and
only exists for development use-cases. The `dev-file` database is not suitable for production use-cases, and must be replaced before deploying to production.
.. *Zipped JDBC driver and Companion Jars* version ${properties["oracle-jdbc.version"]} from the https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html[Oracle driver download page].
.. Maven Central via `link:++https://repo1.maven.org/maven2/com/oracle/database/jdbc/ojdbc17/${properties["oracle-jdbc.version"]}/ojdbc17-${properties["oracle-jdbc.version"]}.jar++[ojdbc17]` and `link:++https://repo1.maven.org/maven2/com/oracle/database/nls/orai18n/${properties["oracle-jdbc.version"]}/orai18n-${properties["oracle-jdbc.version"]}.jar++[orai18n]`.
. When running containers: Build a custom {project_name} image and add the JARs in the `providers` folder. When building a custom image for the Operator, those images need to be optimized images with all build-time options of {project_name} set.
A minimal Containerfile to build an image which can be used with the {project_name} Operator and includes Oracle Database JDBC drivers downloaded from Maven Central looks like the following:
. Download the `mssql-jdbc` JAR file from one of the following sources:
.. Download a version from the https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server[Microsoft JDBC Driver for SQL Server page].
.. Maven Central via `link:++https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/${properties["mssql-jdbc.version"]}/mssql-jdbc-${properties["mssql-jdbc.version"]}.jar++[mssql-jdbc]`.
.. Installation media recommended by the database vendor for the specific database in use.
. When running containers: Build a custom {project_name} image and add the JARs in the `providers` folder. When building a custom image for the {project_name} Operator, those images need to be optimized images with all build-time options of {project_name} set.
A minimal Containerfile to build an image which can be used with the {project_name} Operator and includes Microsoft SQL Server JDBC drivers downloaded from Maven Central looks like the following:
For each supported database, the server provides some opinionated defaults to simplify database configuration. You complete the configuration by providing some key settings such as the database host and credentials.
WARNING: The examples above include the minimum settings needed to connect to the database but it exposes the database password and is not recommended. Use the `conf/keycloak.conf` as shown above, environment variables, or keystore for at least the password.
The server uses JDBC as the underlying technology to communicate with the database. If the default connection settings are insufficient, you can specify a JDBC URL using the `db-url` configuration option.
Be aware that you need to escape characters when invoking commands containing special shell characters such as `;` using the CLI, so you might want to set it in the configuration file instead.
Unicode support for all fields depends on whether the database allows VARCHAR and CHAR fields to use the Unicode character set.
* If these fields can be set, Unicode is likely to work, usually at the expense of field length.
* If the database only supports Unicode in the NVARCHAR and NCHAR fields, Unicode support for all text fields is unlikely to work because the server schema uses VARCHAR and CHAR fields extensively.
Otherwise, characters are limited to those contained in database encoding, which is often 8-bit. However, for some database systems, you can enable UTF-8 encoding of Unicode characters and use the full Unicode character set in all text fields. For a given database, this choice might result in a shorter maximum string length than the maximum string length supported by 8-bit encodings.
Unicode characters are supported in an Oracle database if the database was created with Unicode support in the VARCHAR and CHAR fields. For example, you configured AL32UTF8 as the database character set. In this case, the JDBC driver requires no special settings.
If the database was not created with Unicode support, you need to configure the JDBC driver to support Unicode characters in the special fields. You configure two properties. Note that you can configure these properties as system properties or as connection properties.
Unicode characters are supported in a MySQL/MariaDB database if the database was created with Unicode support in the VARCHAR and CHAR fields when using the following SQL statement.
Note that the utf8mb4 character set is not supported due to different storage requirements for the utf8 character set. See MySQL documentation for details. In that situation, the length restriction on non-special fields does not apply because columns are created to accommodate the number of characters, not bytes. If the database default character set does not allow Unicode storage, only the special fields allow storing Unicode values.
==== Database default charset and collation considerations
Different MySQL and MariaDB versions may have different default settings for character sets and collations. {project_name} uses the database default charset and collation when creating new tables and columns. This design allows you to manage your own data lifecycle by defining the charset and collation that best fits your requirements.
[IMPORTANT]
====
Before migrating {project_name} to a new version, ensure that your database has a consistent charset and collation configuration. Inconsistent settings between existing tables can cause foreign key constraint errors during migration.
To avoid potential mismatches:
. Set your desired charset and collation as the database default *before* installing or upgrading {project_name}.
. Ensure all existing {project_name} tables use the same charset and collation.
. When upgrading, {project_name} will align new tables and columns with your database default settings.
By relying on the database default, {project_name} respects your intent and allows you to maintain control over your database configuration throughout its lifecycle.
Unicode is supported for a PostgreSQL database when the database character set is UTF8. Unicode characters can be used in any field with no reduction of field length for non-special fields. The JDBC driver requires no special settings. The character set is determined when the PostgreSQL database is created.
When running PostgreSQL reader and writer instances, {project_name} needs to always connect to the writer instance to do its work.
When using the original PostgreSQL driver, {project_name} sets the `targetServerType` property of the PostgreSQL JDBC driver to `primary` to ensure that it always connects to a writable primary instance and never connects to a secondary reader instance in failover or switchover scenarios.
You can override this behavior by setting your own value for `targetServerType` in the DB URL or additional properties.
[NOTE]
====
The `targetServerType` is only applied automatically to the primary datasource, as requirements might be different for additional datasources.
Ensure that the database user has `SELECT` permissions to the following tables to ensure an efficient upgrade: `pg_class`, `pg_namespace`.
This is used during upgrades of {project_name} to determine an estimated number of rows in a table.
If {project_name} does not have permissions to access these tables, it will log a warning and proceed with the less efficient `+SELECT COUNT(*) ...+` operation during the upgrade to determine the number of rows in tables affected by schema changes.
When using Amazon Aurora PostgreSQL, the https://github.com/awslabs/aws-advanced-jdbc-wrapper[Amazon Web Services JDBC Driver] offers additional features like transfer of database connections when a writer instance changes in a Multi-AZ setup.
This driver is not part of the distribution and needs to be installed before it can be used.
To install this driver, apply the following steps:
. When running the unzipped distribution: Download the JAR file from the https://github.com/awslabs/aws-advanced-jdbc-wrapper/releases/[Amazon Web Services JDBC Driver releases page] and place it in {project_name}'s `providers` folder.
. When running containers: Build a custom {project_name} image and add the JAR in the `providers` folder.
See the <@links.server id="containers" /> {section} for details on how to build optimized images, and the <@links.operator id="customizing-keycloak" /> {section} on how to run optimized and non-optimized images with the {project_name} Operator.
. Configure {project_name} to run with the following parameters:
`db-url`:: Insert `aws-wrapper` to the regular PostgreSQL JDBC URL resulting in a URL like `+jdbc:aws-wrapper:postgresql://...+`.
`db-driver`:: Set to `software.amazon.jdbc.Driver` to use the AWS JDBC wrapper.
NOTE: When overriding the `wrapperPlugins` option of the AWS JDBC Driver, always include the `failover` or `failover2` plugin to ensure that {project_name} always connects to the writer instance even in failover or switchover scenarios.
Beginning with MySQL 8.0.30, MySQL supports generated invisible primary keys for any InnoDB table that is created without an explicit primary key (more information https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html[here]).
If this feature is enabled, the database schema initialization and also migrations will fail with the error message `Multiple primary key defined (1068)`.
You then need to disable it by setting the parameter `sql_generate_invisible_primary_key` to `OFF` in your MySQL server configuration before installing or upgrading {project_name}.
On MS SQL Server, the default transaction isolation level is `READ_COMMITTED`, which can lead to deadlocks during high load. Therefore, the recommended isolation level for {project_name} is `READ_COMMITTED_SNAPSHOT`. This isolation level is used by default on Azure SQL.
However, on MS SQL Server, the database isolation level needs to be modified by executing the following command on your database:
[source,sql]
----
ALTER DATABASE <your-database-name> SET READ_COMMITTED_SNAPSHOT ON;
Because cluster nodes can boot concurrently, they take extra time for database actions. For example, a booting server instance may perform some database migration, importing, or first time initializations. A database lock prevents start actions from conflicting with each other when cluster nodes boot up concurrently.
The maximum timeout for this lock is 900 seconds. If a node waits on this lock for more than the timeout, the boot fails. The need to change the default value is unlikely, but you can change it by entering this command:
Both options accept values as an ISO 8601 duration, an integer number of seconds, or an integer followed by one of `ms` (milliseconds), `s` (seconds), `m` (minutes), `h` (hours), or `d` (days).
These options can be configured via CLI, environment variables, or the `conf/keycloak.conf` configuration file.
[NOTE]
====
The `transaction-default-timeout` option takes precedence over the unsupported `quarkus.transaction-manager.default-transaction-timeout` Quarkus property.
If you are using the Quarkus property, migrate to the supported `transaction-default-timeout` option and remove the Quarkus property from your configuration.
{project_name} allows you to specify additional datasources in case you need to access another database from your extensions. This is useful when using the main {project_name} datasource is not a viable option for storing custom data, like users.
You can find more details on how to connect to your own users database in the link:{developerguide_userstoragespi_link}[{developerguide_userstoragespi_name}] documentation.
Defining multiple datasources works like defining a single datasource, with one important change - you have to specify a name for each datasource as part of the config option name.
=== Required configuration
In order to enable an additional datasource, you need to set up 2 things - the JPA `persistence.xml` file and {project_name} configuration.
The `persistence.xml` file serves to specify persistence units as part of the Jakarta Persistence API standard, and is required for proper configuration propagation to the Hibernate ORM framework.
When you complete the part with the `persistence.xml` file, you need to set up {project_name} configuration accordingly.
The additional datasource properties might be specified via the standard config sources like CLI, `keycloak.conf`, or environment variables.
The additional datasources can be configured in a similar way as the main datasource.
This is achieved by using analogous names for config options, which additionally include the name of the additional datasource.
For example, when the main datasource uses the `db-username`, the additional one would be `db-username-<datasource>`.
See the Relevant options chapter for the complete list of them.
==== 1. JPA `persistence.xml` file
The `persistence.xml` provides configuration for Jakarta Persistence API (JPA) such as what entities it should manage, the datasource name, JDBC settings, JPA/Hibernate custom settings, and more.
The file needs to be placed in the `META-INF/persistence.xml` folder of your custom {project_name} extension.
NOTE: Be aware that Quarkus provides the ability to set up the JPA persistence unit via Hibernate ORM properties instead of using the `persistence.xml` file.
However, the supported way for {project_name} is using the `persistence.xml` file, and if the file is present, the Quarkus properties are ignored.
In {project_name}, most of the configuration is automatic, and you just need to provide fundamental configuration details - the datasource name and transaction type.
{project_name} requires setting the transaction type for the additional datasource to `JTA`.
You can set the transaction type and datasource name as follows for this minimal `persistence.xml` file:
NOTE: To properly set the datasource name, you should set the `jakarta.persistence.jtaDataSource` property.
If it is not set, the persistence unit name will be used as the datasource name instead (so `user-store-pu` in this case).
In the example above, the resulting datasource name is `user-store`. The datasource name can be the same as the persistence unit name.
In order to use your own JPA entities, you need to provide the `<class>` properties that mark JPA entities that will be managed by this persistence unit, directed to a specific datasource.
In the example above, the `org.your.extension.UserEntity` JPA entity will be managed by the persistence unit `user-store-pu`, directed to the `user-store` datasource.
==== 2. Required properties
Once you have set up your `persistence.xml`, the minimal configuration on the {project_name} side is the setup of the DB kind/vendor for the specified datasource.
You need to specify the build time option `db-kind-<name>`, where the `<name>` is the name of your datasource and must be the **same** as specified in the `persistence.xml` file.
Therefore, you can enable the additional datasource `user-store` as follows (`postgres` as an example):
After specifying the db-kind for the datasource, all database-kind–specific defaults (such as the driver and dialect) are automatically applied, just like for the main datasource.
=== Configuration via environment variables
If you do not want to configure the datasource via CLI or `keycloak.conf` properties, you can use the environment variables.
You can set the DB kind via environment variables (for the `user-store` datasource) as follows:
[source,bash]
----
export KC_DB_KIND_USER_STORE=postgres
export KC_DB_USERNAME_USER_STORE=my-username
----
It maps to the `db-kind-user-store` and `db-username-user-store` {project_name} properties due to the default mapping of the `\_` (underscore) to the `-` (dash) for environment variables.
However, sometimes, the name of the datasource might contain some special characters like `_`, `$` or `.`
In order to have it properly configured via the {project_name} environment variables, you need to explicitly say what the key for the datasource should look like.
You can use a pair of unique {project_name} environment variables with a special case of the `KCKEY_`.
For instance, for a datasource with the name __user_store$marketing__, you can set environment variables as follows:
You can find more information in the guide <@links.server id="configuration"/>, in subsection _Formats for environment variable keys with special characters_.
=== Backward compatibility for the `quarkus.properties`
In the past, we instructed users to use raw Quarkus properties to configure additional datasources in some places.
However, as using Quarkus properties in the `conf/quarkus.properties` file is considered **unsupported**, it is strongly recommended to use the dedicated additional datasources options as described above.
Before you are able to migrate to the dedicated options, you can still specify the datasource settings via the Quarkus properties as follows:
WARNING: Use Quarkus properties **without quotation** for the datasource name, as properties with the quoted datasource name clash with the new datasource options mapping.
Therefore, use `quarkus.datasource.user-store.db-kind=h2`, instead of `quarkus.datasource."user-store".db-kind=h2` to prevent any issues.
