Spring Boot and SQL Server are two great tools, but when used with Docker Compose they can make an outstanding app. In this article we’ll see how using Docker Compose with Spring boot and SQL Server can speed up development and ease the problems when installing in a new enviroment. Let’s dive into it.
Building the project
For this project we’ll be using Spring Boot, so after generating the project add the Spring Boot parent:
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>3.3.2</version> </parent>
Dependencies:
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-sqlserver</artifactId> </dependency> </dependencies>
Plugin for building the jar:
<build>
<finalName>${project.artifactId}</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>Now let’s create the Account entity
@Entity
@Table(name = "account")
public class Account {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
private String username;
private String name;
private String lastname;
// getters and setters
}
Then create the repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
}
After that, create the service
@Service
public class AccountService {
private final AccountRepository accountRepo;
public AccountService(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
public List<Account> findAll() {
return this.accountRepo.findAll();
}
}
And finally create the controller
@RestController
public class AccountController {
private final AccountService accountService;
public AccountController(AccountService accountService) {
this.accountService = accountService;
}
@GetMapping(value = "/accounts")
public ResponseEntity<List<Account>> findAll() {
return ResponseEntity.ok(accountService.findAll());
}
}
Now that we’ve defined the back-end part, next step is to create a frontend to consume th endpoint.
In the resources source folder, create a folder named static. Inside that folder create a file named index.html and paste the following code
<h1>Accounts:</h1>
<div id="account-container"></div>
<script>
fetch('./accounts')
.then((response) => response.json())
.then(data => {
let container = document.getElementById("account-container");
for (var row of data) {
let node = document.createElement("h2");
node.appendChild(document.createTextNode(row.username));
container.appendChild(node);
}
})
</script>With the above code we fetch all the accounts and display them as soon as the page loads.
Now we need to make sure we have the table and the data once we initialize the database with Docker. In order to do so, we’ll use Flyway and we’ll create two scripts to create the table and insert some rows.
In the resources source folder, create a folder named db and inside that another folder named migration. Inside the latter, create a file named V1_0_0__create_account_table.sql.
CREATE TABLE account
(
id int PRIMARY KEY IDENTITY(1, 1),
username nvarchar(50),
name nvarchar(50),
lastname nvarchar(50)
)Now create another file named V1_1_0__insert_account_rows.sql.
INSERT INTO account values('test', 'test name', 'test lastname')
INSERT INTO account values('example', 'example name', 'example lastname')After configuring Flyway we need to tell Spring what database we want to use. To do so, create a file named application.yml in the resources folder.
spring:
datasource:
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
url: jdbc:sqlserver://sql-server;databaseName=DockerSpringSqlServer;verifyServerCertificate=false;useSSL=false;encrypt=false;
username: sa
password: 1lx5quwranlcLp44xu@r
In this file we’re connecting to sql-server which is the name of the docker container we’ll use later, then username and password are defined in the docker compose configuration.
Docker Compose Configuration
Now that the project is configured we need to configure Docker Compose but before we do that we need to tell Docker how our project needs to be built.
Creating Dockefile
Create a Dockerfile in the project root directory
FROM maven:3.9.4-amazoncorretto-17-debian AS build WORKDIR /app COPY pom.xml . RUN mvn dependency:resolve COPY src ./src RUN mvn package FROM openjdk:17-jdk-slim WORKDIR /app COPY --from=build app/target/docker-spring-sql-server.jar docker-spring-sql-server.jar CMD ["java", "-jar", "docker-spring-sql-server.jar"]
In the above configuration we just create a directory named app, move our project there, download dependencies, create the jar and lastly run the jar. It’s important to note that in this case we are running docker-spring-sql-server.jar because this is how this project is named, if your project has a different name you need to change it in the Dockerfile.
Enviroment Variables
Now it’s time to configure Docker Compose, the first thing we’ll do is creating a directory named docker-compose in the project root directory.
Inside this directory create a file named .env
GLOBAL_NETWORK=docker-spring-sql-server DB_PWD=1lx5quwranlcLp44xu@r
In this file we can place enviroment variables to use inside the docker compose configuration files, in this case we’ve defined how we want to name our Docker network and the password of the database that we will use.
Database Scripts
Now create a directory named database. Inside the latter, create a file named init.sql.
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DockerSpringSqlServer') BEGIN CREATE DATABASE DockerSpringSqlServer; END; GO
In this file we check if a database named DockerSpringSqlServer exists, if not then we create it. We’ll use this script later in the docker compose file.
Now navigate up one directory (to the docker-compose directory) and then create a file named dbInit.sh.
#!/bin/bash /opt/mssql-tools/bin/sqlcmd -S sql-server -U sa -P "$DB_PWD" -d master -i docker-entrypoint-initdb.d/init.sql echo "All done!"
This script is used to connect to the database server and run the previous sql script that creates the database.
At this point let’s create another file, named dbHealthcheck.sh.
#!/bin/bash
if /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "$DB_PWD" -Q 'SELECT 1'; then
exit 0 # Exit with code 0 for success
else
exit 1 # Exit with a non-zero code for failure
fi
This file is used to connect to the database server and check if it’s running or not. As you can see the previous two shell scripts are using this variable $DB_PWD that we defined in the .env file.
Docker Compose file
In the docker-compose directory, create a file named docker-compose.yml(backwards compatibility) or compose.yml.
services:
docker-spring-sql-server:
container_name: docker-spring-sql-server
volumes:
- "docker-spring-sql-server:/app/logs"
build:
context: ../
ports:
- 8080:8080
depends_on:
- sql-server-configurator
environment:
JAR: docker-spring-sql-server.jar
restart: always
sql-server:
container_name: sql-server
image: mcr.microsoft.com/mssql/server:2022-CU9-ubuntu-20.04
ports:
- "1433:1433"
restart: always
healthcheck:
test: ["CMD-SHELL", "sh", "-c", "dbHealthcheck.sh"]
interval: 10s
retries: 10
start_period: 10s
timeout: 3s
environment:
SA_PASSWORD: ${DB_PWD}
ACCEPT_EULA: "Y"
volumes:
- docker-spring-sql-server-db:/var/opt/mssql/
- docker-spring-sql-server-db:/var/opt/sqlserver/data
- docker-spring-sql-server-db:/var/opt/sqlserver/log
- docker-spring-sql-server-db:/var/opt/sqlserver/backup
sql-server-configurator:
image: mcr.microsoft.com/mssql/server:2022-CU9-ubuntu-20.04
volumes:
- ./database:/docker-entrypoint-initdb.d
- ./dbInit.sh:/dbInit.sh
depends_on:
sql-server:
condition: service_healthy
command: ["sh", "-c", "./dbInit.sh"]
environment:
DB_PWD: ${DB_PWD}
volumes:
docker-spring-sql-server:
docker-spring-sql-server-db:- docker-spring-sql-server: This is our application, we define the container name, map the directory where it writes the log to the docker volume and tell docker that it should use the previous Dockerfile that we created to build the image. Besides that we map the port (by default Spring will start on port 8080) and we tell Docker that this service actually depends on another one, so it must start the other one first.
- sql-server: As the name suggest, this is the database server. We define the container name, image, port mapping and then the healthcheck. The healthcheck is a way to check if the service is running properly, so that other services can rely on the condition of our service and run only if it’s healthy. In this case we test our service condition by running our script dbHealthcheck.sh which will try to connect to the database server and check if it’s running. Another important thing to mention is the volumes. We are mapping all the directories where SQL Server writes (database included) to our docker volumes. We’re doing this to persist the data and not lose it when the container restarts.
- sql-server-configurator: This service is the one that our app depends on and It starts only if the sql-server service condition is healthy. This service uses sql-server image as well because we’re using a sql-server tool to connect to the database server. In the command property we run our script dbInit.sh which will first connect to sql-server service and then it will run the init.sql that we created previously which then will create the database DockerSpringSqlServer for our application.
- volumes: The last part of the configuration is volumes. We define two volumes because we want to persist the data otherwise we would lose it everytime the containers restart. In this way Docker saves our data on the OS and we can access it anytime. The volume docker-spring-sql-server is used to store our application logs data while docker-spring-sql-server-db to store database data.
Running Docker Compose
Now that everything is set up all we have to do is running our app. Make sure you have Docker Desktop running on your computer. Open the terminal and move to the docker-compose directory. Run the following command:
docker-compose up
With the above command Docker will build and then run all the services we defined in the docker-compose file.
Testing the application
Now that our containers are running we can finally test our application and see if it works properly. Open your browser and type the following url: http://localhost:8080.
Conclusion
In this article we’ve seen how using Docker Compose with Spring Boot and SQL Server can be powerful. After the configuration with just one command we were able to start a database server, create a database, build and run our application. This can definitely speed up development and it’s a great way to make sure your application work wherever you deploy it as long as you use Docker.
The source code of this project is on GitHub.
