Configuring jBPM to use PostgreSQL Database

This tutorial discusses how to configure jBPM to use PostgreSQL as database to store process data.

jBPM out of the box uses H2 database to store the state of processes using a file located under $JBOSS_HOME/standalone/data/jbpm-db. In order to switch to another database, you can use any of the following scripts which are available in $JBOSS_HOME/bin.

In order to swtich to PostgreSQL:

$ ./jboss-cli.sh --file=jbpm-postgres-config.cli      (Unix / Linux)

The above commands needs to be executed when the server is stopped as an embedded server process will be used to update the configuration.

These scripts assume that database is installed and some default configuration is present:

• host → localhost
• port → 5432
• database name → jbpm
• user name → jbpm

Changing jBPM to PostgreSQL

As an example, we will show how to change the default database to PostgreSQL. Start PostgreSQL for example with Docker:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name jbpm -e POSTGRES_USER=jbpm -e POSTGRES_PASSWORD=jbpm -e POSTGRES_DB=jbpm -p 5432:5432 postgres

Then, run the jbpm-postgres-config.cli

$ ./jboss-cli.sh --file=jbpm-postgres-config.cli      (Unix / Linux)

Now start jBPM:

$ ./standalone.sh

As you can see from the XML configuration, the datasource named “jBPMXADS” has been added:

<xa-datasource jndi-name="java:jboss/datasources/jBPMDS" pool-name="jBPMXADS">
                    <xa-datasource-property name="ServerName">
                        localhost
                    </xa-datasource-property>
                    <xa-datasource-property name="PortNumber">
                        5432
                    </xa-datasource-property>
                    <xa-datasource-property name="DatabaseName">
                        jbpm
                    </xa-datasource-property>
                    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
                    <driver>postgres</driver>
                    <security>
                        <user-name>jbpm</user-name>
                        <password>jbpm</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
                        <validate-on-match>true</validate-on-match>
                        <background-validation>true</background-validation>
                        <background-validation-millis>120000</background-validation-millis>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
                    </validation>
                </xa-datasource>
                <drivers>
                    <driver name="h2" module="com.h2database.h2">
                        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                    </driver>
                    <driver name="postgres" module="org.postgres">
                        <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
                    </driver>
                </drivers>

You can also log into PostgreSQL and check that tables have been created:

jbpm=# \dt
                    List of relations
 Schema |              Name              | Type  | Owner
 public | attachment                     | table | jbpm
 public | audittaskimpl                  | table | jbpm
 public | bamtasksummary                 | table | jbpm
 public | booleanexpression              | table | jbpm
 public | casefiledatalog                | table | jbpm
 public | caseidinfo                     | table | jbpm
 public | caseroleassignmentlog          | table | jbpm
 public | content                        | table | jbpm
 public | contextmappinginfo             | table | jbpm
 public | correlationkeyinfo             | table | jbpm
 public | correlationpropertyinfo        | table | jbpm
 public | deadline                       | table | jbpm
 public | delegation_delegates           | table | jbpm
 public | deploymentstore                | table | jbpm
 public | email_header                   | table | jbpm
 public | errorinfo                      | table | jbpm
 public | escalation                     | table | jbpm
 public | eventtypes                     | table | jbpm
 public | executionerrorinfo             | table | jbpm
 public | i18ntext                       | table | jbpm
 public | nodeinstancelog                | table | jbpm
 public | notification                   | table | jbpm
 public | notification_bas               | table | jbpm
 public | notification_email_header      | table | jbpm
 public | notification_recipients        | table | jbpm
 public | organizationalentity           | table | jbpm
 public | peopleassignments_bas          | table | jbpm
 public | peopleassignments_exclowners   | table | jbpm
 public | peopleassignments_potowners    | table | jbpm
 public | peopleassignments_recipients   | table | jbpm
 public | peopleassignments_stakeholders | table | jbpm
 public | processinstanceinfo            | table | jbpm
 public | processinstancelog             | table | jbpm
 public | querydefinitionstore           | table | jbpm
 public | reassignment                   | table | jbpm
 public | reassignment_potentialowners   | table | jbpm
 public | requestinfo                    | table | jbpm
 public | sessioninfo                    | table | jbpm
 public | task                           | table | jbpm
 public | task_comment                   | table | jbpm
 public | taskdef                        | table | jbpm
 public | taskevent                      | table | jbpm
 public | taskvariableimpl               | table | jbpm
 public | variableinstancelog            | table | jbpm
 public | workiteminfo                   | table | jbpm
(45 rows)

Configuring Applications to use jBPM with PostgreSQL

Let’s see how to configure jBPM embedded in your applications. When using Spring Boot it’s pretty easy. What you need is to add the JDBC driver in your configuration:

<dependency>
    <groupId>org.kie</groupId>
    <artifactId>kie-server-spring-boot-starter</artifactId>
    <version>${version.org.kie}</version>
</dependency>

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
</dependency>

Then, you can configure the Datasource connectivity in your application.properties

server.address=0.0.0.0
server.port=8090

cxf.path=/rest

#kie server config
kieserver.serverId=business-application-service
kieserver.serverName=business-application-service
kieserver.location=http://localhost:8090/rest/server
#kieserver.controllers=


#kie server capabilities
kieserver.drools.enabled=true
kieserver.dmn.enabled=true
kieserver.jbpm.enabled=true
kieserver.jbpmui.enabled=true
kieserver.casemgmt.enabled=true


#jbpm configuration
jbpm.executor.enabled=false
#jbpm.executor.retries=5
#jbpm.executor.interval=3
#jbpm.executor.threadPoolSize=1
#jbpm.executor.timeUnit=SECONDS

spring.datasource.username=jbpm
spring.datasource.password=jbpm
spring.datasource.url=jdbc:postgresql://localhost:5432/jbpm
spring.datasource.driver-class-name=org.postgresql.xa.PGXADataSource

#hibernate configuration
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

#transaction manager configuration
spring.jta.narayana.transaction-manager-id=1

#banner
spring.banner.location=classpath:banner.txt

narayana.transaction-manager-id=1
narayana.default-timeout=120
narayana.dbcp.enabled=true

As an example, see the following jBPM application which requires a PostgreSQL database connection: https://github.com/fmarchioni/mastertheboss/tree/master/jbpm/business-application-postgresql