Spring Boot on Choreo: Connecting a Database

In our previous article, we looked at running a Spring Boot application on Choreo. We used an in-memory H2 database, which isn't robust enough for a real-world application. For instance, if the application restarts, all the added employee data disappears. In this article, we’ll address how to configure a persistent database, moving our app one step closer to being production-ready.

To create the database, I’m using the newly introduced DB as a Service feature in Choreo, which allows you to create MySQL or PostgreSQL databases on any cloud provider of your choice.

Creating a Database

Navigate to the Choreo Console and go to your Organization’s Home page. Make sure you're in the view that lists all projects in your organization. Select Dependencies -> Databases from the left menu (this option only appears in the organization’s view).

It will bring up the following screen.
Click 'Create' and select a database type. I’ll be using MySQL. Give a name like employee-db and click 'Next'.
You’ll be prompted to select a cloud provider and a plan. I’ll go with Digital Ocean as the Provider and Hobbyist as the plan. Since my application runs in the US region, I’ll choose the United States for the Database location. Click Next.
Your database will now start provisioning, which will take a few minutes.

While the database is provisioning, we can make some changes to our Spring Boot application. If you want to follow along, you can get the base version from here - where we stopped from the previous article. Or you can use this branch if you prefer to skip ahead and go straight to deployment.

Changing the Spring Boot App

Clone the repository and check out the code:

git clone [email protected]:jaadds/employee.git

cd employee

Since we’re transitioning from an in-memory H2 database, we need to replace the H2 dependency with the MySQL driver in the pom.xml.

Replace:

<dependency>
	<groupId>com.h2database</groupId>
	<artifactId>h2</artifactId>
	<version>2.2.222</version>
</dependency>

with

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>8.0.27</version>
</dependency>

Then, you need to add the following properties specifying connection details for your database. Add these to src/main/resources/application.properties.

spring.datasource.url=jdbc:mysql://<HOST>:<PORT>/<DEFAULT DATABASE>
spring.datasource.username=<DEFAULT USER>
spring.datasource.password=<PASSWORD>
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

If the database has finished provisioning, you can start the application and verify its connectivity:
```
./mvnw spring-boot:run
```

Since the database is empty, the GET operation won’t return any results. Instead, try adding an employee with a POST request:

curl -H "Content-Type: application/json" -X POST http://localhost:8080/api/employees -d '{"firstName":"Frodo","lastName":"Baggins","email":"[email protected]"}'

If you’re getting a response without errors, then you’ve configured the database correctly. But don’t hastily commit the code just yet. There are a few more changes to be made.

Using Environment Variables for Connection Parameters

In practice, we don’t commit sensitive details like database connection parameters in plain text. Since they’re in a repository, anyone with access to the repo could then change the database. Instead, we use environment variables as placeholders. We can set these variables in our local environment to get the values resolved properly.

Let’s change the application.properties file to use environment variables.

In Spring Boot, when you specify a name in the ${VAR_NAME} format, the value is read from the environment. Replace our connection details with the following:
```
spring.datasource.url=jdbc:mysql://${DB_HOST}:${DB_PORT}/${DB_NAME}
spring.datasource.username=${DB_USER}
spring.datasource.password=${DB_PASSWORD}
```

Set the values for these variables in your environment like this:

export DB_PORT=<PORT>
export DB_HOST=<HOST>
export DB_NAME=<DEFAULT DATABASE>
export DB_PASSWORD=<PASSWORD>
export DB_USER=<DEFAULT USER>

Restart the Spring Boot application and make sure it starts without errors:
```
./mvnw spring-boot:run
```
Call the GET operation and verify that the application can access the database and return results:
```
curl http://localhost:8080/api/employees
```

Now, you’re ready to commit the changes and push them to your repository.

Deploying Changes to Choreo

If you’re using a single branch to keep your changes, you can proceed directly to the deployment section. In my case, I've used a different branch named database to maintain the changes discussed here. So, I need to inform Choreo about this new branch.

Pointing the new branch (Optional)

Go to your component's overview page on Choreo. Select the 'Deployment Track' dropdown in the top-left corner and click 'Create New'.
Choose the branch and enter v1.1 for the API version, then click 'Create'. We're effectively creating a new deployment track by pointing to our new branch. This feature allows you to run two versions of the same app simultaneously. Changes in the 'main' branch are exposed as API version 1.0, and changes in the 'database' branch will be under v1.1. You can learn more about this feature here.
Now, if you go to the 'Deploy' page, you'll see changes from the 'Database' branch ready for deployment.

Deploying the App

In this section, we’ll continue with deploying the app and look at how to securely set the connection details using the environment variables we defined earlier.

Go to the Deploy page and make sure the Build Area shows your latest commit.
Click the 'Configure & Deploy’ button to start the wizard. The first step is to set environment variables.
Click the 'Add' button and input the following environment variables:
- DB_HOST - The database host URL
- DB_PORT - The database port
- DB_NAME - The database name
- DB_USER - The default username
- DB_PASSWORD - The database password (mark this as a secret)
After specifying all the values, click 'Next' to proceed through the remaining steps, which we won't need to modify. Finally, click 'Deploy' to initiate the deployment.

This will trigger the build and deployment, which will take a couple of minutes to complete. So, while the deployment is in progress, let’s recap what we’ve done.

By setting the environment variables, we've specified the connection details for that particular environment. When deploying an app through Choreo, underneath, they get deployed to a Kubernetes cluster. The environment variables we specified, are set in a config map. Since the app and the config map are in the same namespace, these values are available only to that specific app. What about the password? Since we marked it as a secret, Choreo stores it as a Kubernetes secret, and its value is kept in an Azure KeyVault. Because the app, secret, and ConfigMap are all deployed in the same namespace, only the intended app gets to read the values. Different apps from different organizations cannot read the values just by guessing their names.

If the deployment is done by now, we can test the app.

Testing the App

Open the Swagger console and test the API by invoking the GET /employees operation. Check whether the details you added locally are correctly retrieved.

Well done!! You've configured the database successfully.

In this post, our goal was to add persistence to our application. We connected a DB to our application running on the Development environment. However, when dealing with micro-services, it's common practice to use separate databases for different environments. As an exercise, you can create another database and use it while promoting your app to Production. Let me know how it goes.