Skip to main content

Databases

Many Kotlin applications need a database, and following our microservices approach that database should only be accessible to a single application.

Databases should be hosted by Cloud Platform using AWS RDS.

Postgres should be the database of choice, unless you have a very good reason to pick another.

The technical stack we use for databases is:

  • Terraform to create the DB instance
  • Flyway to maintain DB schema
  • Spring Data JPA for reading/writing (with Hibernate under the covers)
  • Docker for local/test databases

Creating the database

Cloud Platform provides a terraform module for creating RDS instances. Check the README for configuration options. Their RDS documentation is also worth a read.

Have a look around in the Cloud Platform environments repo for examples of HMPPS projects to see what other teams are doing.

Note that you’ll need to authenticate your application with AWS so it can access the database. See the guide on AWS service accounts for how to create the IRSA module. Add the RDS policy to the service account to allow access.

Before submitting a PR to #ask-cloud-platform consider the following.

Postgres version

The Postgres version should be the latest supported. You’ll have to upgrade to the latest version anyway at some point, and upgrades are a little painful.

DB instance class

There’s a multitude of options when selecting an instance class and we generally go for the burstable performance types. You should probably start with a t4g.micro and be prepared to upgrade if you have performance issues.

Kubernetes secrets

Don’t forget to include a kubernetes_secret resource. This is how DB secrets are injected into your Kubernetes namespace, and you’ll need those later.

Preprod refresh

There’s a convention within DPS to refresh preprod data from production, usually every 2 weeks after NOMIS preprod is refreshed. Support for this is built into our HMPPS helm chart which will run the refresh automatically.

Note that the preprod refresh won’t work if your preprod schema is different to production. Avoid deploying schema changes to preprod just before the weekend.

Failing terraform builds

Keep an eye on your Cloud Platform PR. The terraform build runs on a different CI server which updates your PR with comments containing the terraform plan and details of build failures.

Configuring Spring

Firstly you’ll need to tell Spring that you’re using Postgres by adding a dependency to your project. You don’t need to set the version of the dependency, Spring’s Dependency Manager handles that.

Spring expects some configuration properties to be populated so it can connect to and authenticate with your database.

These secrets are injected into your Kubernetes namespace by Cloud Platform and need mapping to environment variables to be used by Spring.

Example

To map the Kubernetes secrets to environment variables add a namespace_secrets entry to your helm values-prod configuration.

These env vars are then used to configure the Spring configuration property spring.datasource.

Database schema

We use Flyway to manage our schemas. This allows us to create incremental SQL updates to the schema which are applied by Spring Boot during application startup.

We generally stick to the default Flyway convention and store SQL scripts in src/main/resource/db/migration. You can find lots of examples of these scripts in our codebase. Be aware that the versioned SQL script filenames are important and updating previously deployed migrations will not work.

To configure Flyway you’ll need to add the runtime dependency to your project and tell Flyway the DB connection details.

Database access

We use the Spring Data JPA persistence framework for accessing the database.

There are loads of examples of declaring entities to model tables and repositories to query/update them.

To use Spring Data JPA you’ll need to add a dependency to your project.

Integration Testing

We generally use Testcontainers to spin up a real Postgres instance for integration testing. While there are good arguments for testing with in-memory databases this option isn’t available for Postgres; we’ve had various syntax problems with other in-memory databases; the Postgres Docker container starts pretty fast these days.

Add the Testcontainers Postgres dependency to your project.

Declare the Postgres container in your test suite and configure it to override your Spring database configuration properties in tests.

Your integration tests will now run the application against this local Postgres instance. For a lightweight approach to testing the database consider using a Spring test slice with the @DataJpaTest annotation.

When populating the database for testing each test should set up and tear down its own data. Using SQL scripts for each test is OK (though not great for readability) but we prefer things like test data builders.

Running locally

To spin up a Postgres database locally provide a docker compose file and override Spring database configuration properties. You’d then need to start the application using a Spring profile named dev.

Database upgrades

You’ll need to upgrade the Postgres version as old versions become outdated and are no longer supported by AWS. It’s recommended to keep up to date rather than be forced to move from an unsupported version in haste.

Cloud Platform have a guide to assist with database version upgrades. Bear in mind the upgrade paths supported by AWS when choosing your next version.