description |
---|
This article explains how to configure a JDBC repository |
The JDBC plugin is part of the default distribution of APIM. However, you need to install the correct driver for the database you are using in order to use JDBC as a repository.
Database | Version tested | JDBC Driver |
---|---|---|
PostgreSQL | 11.x / 12.x / 13.x / 14.x / 15.x | Download page |
MySQL | 5.7.x / 8.0.x | Download page |
MariaDB | 10.4.x / 10.5.x / 10.6.x / 10.10.x / 10.11.x / 11.x | Download page |
Microsoft SQL Server | 2017-x / 2019-x / 2022-x | Download page |
Repeat these steps on each component (APIM Gateway and APIM API) where the SQL database is used:
- Download the JDBC driver corresponding to your database version
- Place the driver in
$GRAVITEE_HOME/plugins/ext/repository-jdbc
- Configure your
gravitee.yml
files, as described in the next section
{% hint style="info" %} Before moving on
If you are using Docker to install and run APIM, you should place the driver in the plugins-ext
folder and configure it by using the Docker compose file or command-line arguments. For more information, see Further Customization of a Docker installation.
{% endhint %}
The example below shows the minimum configuration needed to get started with a JDBC database.
management:
type: jdbc # repository type
jdbc: # jdbc repository
url: # jdbc url
You can configure the following additional properties to fine-tune your JDBC connection and control the behavior of your JDBC database.
management:
type: jdbc # repository type
jdbc: # jdbc repository
prefix: # tables prefix
url: # jdbc url
username: # jdbc username
password: # jdbc password
pool:
autoCommit: # jdbc auto commit (default true)
connectionTimeout: # jdbc connection timeout (default 10000)
idleTimeout: # jdbc idle timeout (default 600000)
maxLifetime: # jdbc max lifetime (default 1800000)
minIdle: # jdbc min idle (default 10)
maxPoolSize: # jdbc max pool size (default 10)
You can use a custom prefix for your table names. For example, this is useful if you want to use the same databases for APIM and AM.
The following steps explain how to rename your tables with a custom prefix, using the prefix prefix_
as an example.
If you are installing APIM for the first time, you need to update the following two values in the APIM Gateway and APIM API gravitee.yml
files:
management.jdbc.prefix
ratelimit.jdbc.prefix
By default, these values are empty.
{% hint style="info" %} Before moving on
Before running any scripts, you need to create a dump of your existing database. You need to repeat these steps on both APIM Gateway and APIM API. {% endhint %}
If you are migrating an existing installation, follow these steps:
- Update values
management.jdbc.prefix
andratelimit.jdbc.prefix
in yourgravitee.yml
configuration file. - Run the application on a new database to generate
prefix_databasechangelog
. - Replace the content of the
databasechangelog
table with the content you generated fromprefix_databasechangelog
. - Rename your tables using format
prefix_tablename
. - Rename your indexes using format
idx_prefix_indexname
. - Rename your primary keys using format
pk_prefix_pkname
.
Some databases have an option to enforce the use of a primary key on all tables, e.g., MySQL 8.0.13+ with sql_require_primary_key
set to true
.
If you are using a database with such an option activated, you will need to do the following during the installation of APIM:
- Disable this option.
- Start APIM Management API to allow the database migration tool, Liquibase, to create the APIM tables and add the primary keys.
- Re-enable this option.
{% hint style="info" %} APIM does not currently set primary keys when creating tables
By default, Liquibase creates 2 tables without primary keys for its own use. To avoid a compatibility issue with Liquibase, Gravitee does not override the creation of these tables. See here for more information. {% endhint %}