The SQL backend stores all SMP data in a relational database. It is appropriate for production deployments that require high concurrency, operational tooling, or integration with existing database infrastructure.
Supported databases
| Database | target-database value | JDBC driver class |
|---|
| MySQL 5.7+ / 8.x | MySQL | com.mysql.cj.jdbc.Driver |
| PostgreSQL 12+ | PostgreSQL | org.postgresql.Driver |
| Oracle 12c+ | Oracle | oracle.jdbc.driver.OracleDriver |
| IBM DB2 | DB2 | com.ibm.db2.jcc.DB2Driver |
| Microsoft SQL Server | SQLServer | com.microsoft.sqlserver.jdbc.SQLServerDriver |
Selecting the SQL backend
Set the following property in application.properties:
Use the phoss-smp-webapp-sql WAR when deploying.
Required configuration
All jdbc.* and target-database properties must be set for the backend to start.
jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/smp?useUnicode=true&serverTimezone=UTC&autoReconnect=true
jdbc.user = smp
jdbc.password = smp
jdbc.schema = smp
target-database = MySQL
Full property reference
| Property | Required | Description |
|---|
jdbc.driver | Yes | Fully-qualified JDBC driver class name |
jdbc.url | Yes | JDBC connection URL |
jdbc.user | Yes | Database username |
jdbc.password | Yes | Database password |
jdbc.schema | No | Schema name. When set, Flyway targets this schema |
jdbc.schema-create | No | Create the schema automatically if it does not exist (default: false) |
target-database | Yes | Database dialect: MySQL, PostgreSQL, Oracle, DB2, SQLServer |
jdbc.execution-time-warning.enabled | No | Log a warning when a query exceeds the threshold (default: true) |
jdbc.execution-time-warning.ms | No | Threshold in milliseconds (default: 5000) |
jdbc.cache.sg.enabled | No | In-memory cache for service groups (default: true) |
Database-specific examples
MySQL
PostgreSQL
Oracle
DB2
SQL Server
jdbc.driver = com.mysql.cj.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/smp?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&autoReconnect=true
jdbc.user = smp
jdbc.password = smp
jdbc.schema = smp
target-database = MySQL
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.x.x</version>
</dependency>
jdbc.driver = org.postgresql.Driver
jdbc.url = jdbc:postgresql://localhost:5432/postgres
jdbc.user = peppol
jdbc.password = peppol
jdbc.schema = smp
target-database = PostgreSQL
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.x.x</version>
</dependency>
# Note: Oracle is case-sensitive for username and password
jdbc.driver = oracle.jdbc.driver.OracleDriver
jdbc.url = jdbc:oracle:thin:@localhost:1521/ORCLPDB1
jdbc.user = SMP
jdbc.password = smp
jdbc.schema = SMP
target-database = Oracle
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.x.x</version>
</dependency>
jdbc.driver = com.ibm.db2.jcc.DB2Driver
jdbc.url = jdbc:db2://localhost:50000/smp
jdbc.user = smp
jdbc.password = smp
jdbc.schema = SMP
target-database = DB2
jdbc.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url = jdbc:sqlserver://localhost:1433;databaseName=smp;encrypt=true;trustServerCertificate=true
jdbc.user = smp
jdbc.password = smp
jdbc.schema = dbo
target-database = SQLServer
Connection pooling (DBCP2)
phoss SMP uses Apache Commons DBCP2 for connection pooling. The following properties are available since v8.0.11:
| Property | Default | Description |
|---|
jdbc.pooling.max-connections | 8 | Maximum number of active connections that can be allocated from the pool at the same time. Negative value means no limit. |
jdbc.pooling.max-wait.millis | 10000 | Maximum milliseconds to wait for an available connection before throwing an exception. -1 waits indefinitely. |
jdbc.pooling.between-evictions-runs.millis | 300000 | Milliseconds between idle-connection eviction runs (5 minutes). Non-positive disables the evictor. |
jdbc.pooling.min-evictable-idle.millis | 1800000 | Minimum milliseconds a connection may sit idle before becoming eligible for eviction (30 minutes). |
jdbc.pooling.remove-abandoned-timeout.millis | 300000 | Milliseconds before an abandoned connection can be removed (5 minutes). |
jdbc.pooling.test-on-borrow | false | Validate connections before lending them from the pool (since v8.0.12). |
# Example: increase pool size and borrow timeout for a busy deployment
jdbc.pooling.max-connections = 20
jdbc.pooling.max-wait.millis = 30000
jdbc.pooling.between-evictions-runs.millis = 300000
jdbc.pooling.min-evictable-idle.millis = 1800000
jdbc.pooling.test-on-borrow = true
Set jdbc.pooling.test-on-borrow = true in environments where connections may be silently dropped by a firewall or load balancer between eviction runs.
Schema migration with Flyway
The SQL backend uses Flyway to manage database schema versions. Flyway applies versioned migration scripts automatically on startup, so the schema is always in sync with the running code.
Migration scripts live under db/migrate-{dialect}/ inside the phoss-smp-backend-sql JAR:
db/
├── migrate-mysql/
├── migrate-postgresql/
├── migrate-oracle/
├── migrate-db2/
└── migrate-sqlserver/
| Property | Default | Description |
|---|
smp.flyway.enabled | true | Enable or disable automatic schema migration |
smp.flyway.baseline.version | 0 | Flyway baseline version for existing databases |
smp.flyway.jdbc.url | (same as jdbc.url) | Override JDBC URL for Flyway only (e.g., use a privileged account for DDL) |
smp.flyway.jdbc.user | (same as jdbc.user) | Override database user for Flyway migrations |
smp.flyway.jdbc.password | (same as jdbc.password) | Override database password for Flyway migrations |
If your DBA policy requires schema changes to run under a privileged account separate from the application account, set smp.flyway.jdbc.user, smp.flyway.jdbc.password, and smp.flyway.jdbc.url to the privileged credentials. The runtime connection pool will still use the regular jdbc.* credentials.
Initial database setup
The database (schema) must exist before phoss SMP starts. Flyway will create tables and seed data automatically, but it will not create the database itself.
Create the database
Create an empty database and a dedicated user:-- MySQL example
CREATE DATABASE smp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'smp'@'%' IDENTIFIED BY 'smp';
GRANT ALL PRIVILEGES ON smp.* TO 'smp'@'%';
FLUSH PRIVILEGES;
Configure application.properties
Set the jdbc.* and target-database properties as shown in the examples above.
Start phoss SMP
On first startup, Flyway detects that the schema is empty and applies all migration scripts in order, creating all required tables.
Debugging
Enable additional SQL-level logging to troubleshoot connection or query issues:
jdbc.debug.connections = false
jdbc.debug.transactions = false
jdbc.debug.sql = true # Logs every SQL statement
Do not enable jdbc.debug.sql in production — it logs every SQL statement and can produce very large log files.
SQL status endpoint
The SQL backend exposes connection pool statistics via the /smp-status/ endpoint when both smp.status.enabled = true and smp.status.sql.enabled = true are set (the SQL status is enabled by default):
smp.status.enabled = true
smp.status.sql.enabled = true