Edit: I no longer use this approach. I use pax-jdbc-config instead.  See the article Rewriting applications to use pax-jdbc-config and liquibase for details

When creating databases for my apache karaf based web applications, I want the following things:

1. A database-independent schema creation using liquibase (i.e. supporting multiple relational database systems)
2. Pluggable databases (each web application typically has an in-memory derby database for testing and evaluation and a PostgreSQL database for production use)
3. The ability to run multiple applications in the same apache karaf instance without the application databases colliding
4. The possibility to configure the JDBC connection information using the karaf configuration

The way I accomplish this, is:

1. Create an application specific OSGi service for connecting to the database
2. Create declarative services (DS) components implementing the service for each database system I wish to support
3. Create a bundle implementing the calls to liquibase (and containing the liquibase scripts as resources) and use this bundle from all DS components implementing the database service
4. Use high level karaf features to compose my webapps, with variants for each supported database system

OSGi 101: when I talk about “OSGi service” I mean “Java Interface”. Java interfaces form the basis of the OSGi plug-in model. When an OSGi bundle (a “bundle” is a JAR file with some OSGi specific headers added to its MANIFEST.MF) is loaded it can have dependencies to services provided by other bundles.  When those dependencies are satsified the bundle can go active and provide its own services to other bundles that may be listening.

I have created a Java interface called DatabaseService that implements the necessary operations I do against a JDBC database, and they aren’t many:

• getDatasource() that will return a DataSource  connected to a relational database with the expected schema (Note: if connecting to the database fails, the DS component providing the service never goes active, so the only way code ever gets to call this method is if the connection was successful. This simplifies the code using the method)
• getConnection() that returns a Connection to the database. This is a convenience method, because what the implementations actually do, is call Datasource.getConnection().  It makes sense to have this method in the interface, because:
  1. Fetching the connection is the only thing done with DataSource, so that it makes all usages a little bit simpler and shorter
  2. The DS components providing the service all have ended up implementing this method anyway, in the calls to liquibase

The application specific DatabaseService definitions are mostly without methods of their own, like e.g.

However, I’ve had one case where I needed to have very different syntax for the different database systems supported, and then I added methods returning database specific versions of the problematic SQL queries to the application specific database service definition:

For the curious: the SQL with different syntax had to do with aggregation over months and years where the SQL looks like this in derby and the SQL looks like this in PostgreSQL.

The next step is to create a bundle holding the liqubase schema definition of the database.  This bundle neither expects OSGi service injection, not exposes any services.  It’s an OSGi library bundle that is like using a regular jar, except that the exports and imports of packages is controlled by OSGi and OSGi-headers in the bundle’s MANIFEST.MF.

More OSGi terminology: “bundle” means a jar-file with an OSGi-compliant MANIFEST.MF.

The liquibase bundles contain a single class and liquibase scripts as resources. In OSGi the resources are bundle local, so other bundles can’t load them directly. But they can instantiate a class from the liquibase bundle and that class can in turn load the resources that resides in the same bundle as the class.

The classes in the liquibase bundles typically contains a method for initial schema setup, and a method for schema modifications.  In addition to the scripts in the liquibase bundle, the database plugins contain their own liquibase scripts (the liquibase scripts of the in-memory derby test database plugins contains sets of hopefully realistic test data, and the liquibase scripts of the PostgreSQL plugins contains initial data such as e.g. a single initial user).

• Examples of liquibase bundle classes:
  • UkelonnLiquibase
  • AuthserviceLiquibase
• Examples of liquibase bundle schema setup scripts
  • ukelonn
  • authservice
• Examples of initial data:
  • ukelonn
    • derby test database
    • PostgreSQL initial data
  • authservice
    • derby test database
    • PostgreSQL initial data

The declarative services (DS) components of the database plugins expect an injection of the DataSourceFactory OSGi service. The DataSourceFactory interface isn’t part of JDBC (like DataSource itself is), but is part of the OSGi service platform.

To ensure that the correct DataSourceFactory is injected it is possible to filter on the osgi.jdbc.driver.name service property (service properties is a Map<String, String> associated with an OSGi service), like e.g.

and

Once all @Reference service injections are satisfied, the method annotated with @Activate is called. This method needs to create a successful connection to the database and run liquibase scripts. A method for a derby memory database can look like this (the full source of the UkelonnDatabaseProvider class is here):

What the method does is to first create a DataSource object (stored in a field and accessible through the DatabaseService interface), and it then creates a connection in a try-with-resource and runs the liquibase scripts using that connection before the try-with-resource releases the connection. The scripts are typically an initial schema, followed by initial data for the database, followed by modifications to the schema.

It is possible to create configuration for a DS component using the karaf console command line, and creating JDBC connection info for a PostgreSQL database can be done with the following commands:

The resulting configuration will be injected into the @Activate method like e.g. so (the full source of the PGUkelonnDatabaseProvider class is here):

The injected config is sent into the method creating the DataSource instance.

Important: the DataSource instance is cached and is used to represent the connected database.  The Connection instance are not cached. The Connection instances are created on demand in try-with-resource, to ensure that they are closed and all transactions are completed.

The method creating the DataSource instance passes the config on to a method that picks the JDBC connection info out of the injected config and puts into a Properties instance that can be used to connect to a JDBC database.

Using the pluggable database is done by having the DS component implementing the application’s business logic have the application specific database service injected, e.g. like so (full UkelonnServiceProvider source here):

Using the injected service is calling the getConnection() method in a try-with-catch, do the SQL operations and then let the end of the try-with-catch release the connection, like e.g. so

It probably helps for this approach for pluggable databases that I’m using JDBC prepared statements directly instead of using an ORM like e.g. hibernate (but that’s a story/rant for another day…).

Picking what database to use is done using karaf features.

The karaf-maven-plugin is used on all maven modules that create an OSGi bundle, to create a karaf feature repository file and attach it to the OSGi bundle artifact.

In addition I create a handwritten feature repository file where I include all of the feature files of the OSGi bundle artifacts using their maven coordinates. And then in the same file, create high level features that start the application with either database.

Examples of hand-written feature repositories:

1. aggregate feature repository for ukelonn (maven URL mvn:no.priv.bang.ukelonn/karaf/LATEST/xml/features )
2. aggregate feature repository for authservice (maven URL mvn:no.priv.bang.authservice/authservice/1.6.0/xml/features )

Using the authservice as an example:

1. First define a feature that loads the features required for the application, except for the database
   

   	<feature name="authservice-with-dbrealm-and-session">
   	<feature>authservice-web-security-dbrealm</feature>
   	<feature>authservice-web-security-memorysession</feature>
   	<feature>authservice-users</feature>
   	<feature>authservice-web-security</feature>
   	</feature>

   view raw authservice-with-dbrealm-and-session hosted with ❤ by GitHub
2. Then define a feature that uses that feature in addition with the feature created when building the OSGi bundle for the derby test database
   

   	<feature name="authservice-with-derby-dbrealm-and-session">
   	<feature>authservice-db-derby-test</feature>
   	<feature>authservice-with-dbrealm-and-session</feature>
   	</feature>

   view raw authservice-with-derby-dbrealm-and-session hosted with ❤ by GitHub
3. Finally define a feature that uses the first feature in addition with the feature created when building the OSGi bundle for the PostgreSQL database
   

   	<feature name="authservice-with-postgresql-dbrealm-and-session">
   	<feature>authservice-db-postgresql</feature>
   	<feature>authservice-with-dbrealm-and-session</feature>
   	</feature>

   view raw authservice-with-postgresql-dbrealm-and-session hosted with ❤ by GitHub

This means that trying out the authservice application can be done by:

1. Download and start apache karaf using the quick start guide
2. Install authservice with derby by loading the feature repository using the maven coordinates, and then loading the feature that pulls in the application and the derby database
   

   	karaf@root()> feature:repo-add mvn:no.priv.bang.authservice/authservice/LATEST/xml/features
   	Adding feature url mvn:no.priv.bang.authservice/authservice/LATEST/xml/features
   	karaf@root()> feature:install authservice-with-derby-dbrealm-and-session
   	karaf@root()>

   view raw karaf-install-derby-feature hosted with ❤ by GitHub
3. All bundles required by the features are pulled in from maven central and the application is started and becomes available on http://localhost:8181/authservice

Installing and starting the PostgreSQL takes a little more work (but not much):

1. Install and start PostgreSQL (left as an exercies for the reader)
2. Create a PostgreSQL user named karaf with a password (in the examples “karaf”)
3. Create a blank database named “authservice” owned by user karaf
4. Download and start apache karaf using the quick start guide
5. Create database connection configuration from the karaf console
   

   	karaf@root()> config:edit no.priv.bang.ukelonn.db.postgresql.PGUkelonnDatabaseProvider
   	karaf@root()> config:property-set ukelonn.db.jdbc.url "jdbc:postgresql:///ukelonn"
   	karaf@root()> config:property-set ukelonn.db.jdbc.user "karaf"
   	karaf@root()> config:property-set ukelonn.db.jdbc.password "karaf"
   	karaf@root()> config:update
   	karaf@root()>

   view raw karaf-create-config hosted with ❤ by GitHub
6. Install authservice with PostgreSQL by loading the feature repository using the maven coordinates, and then loading the feature that pulls in the application and the PostgreSQL database
   

   	karaf@root()> feature:repo-add mvn:no.priv.bang.authservice/authservice/LATEST/xml/features
   	Adding feature url mvn:no.priv.bang.authservice/authservice/LATEST/xml/features
   	karaf@root()> feature:install authservice-with-postgresql-dbrealm-and-session
   	karaf@root()>

   view raw karaf-install-postgresql-feature hosted with ❤ by GitHub
7. All bundles required by the features are pulled in from maven central and the application is started and becomes available on http://localhost:8181/authservice