Mark
#1
When building a Dropwizard service that needs relational storage, Microsoft SQL Server can be a great choice—especially if you're already using it elsewhere. This guide shows you how to configure SQL Server in Dropwizard using Docker for local development and Kubernetes for production. We'll manage configurations through config-dev.yml and config-prod.yml, ensuring a clean separation between environments and secure handling of credentials.

1. Add the Necessary Dependencies

In your pom.xml, include these key dependencies: 
<dependencies>
  <!-- Dropwizard core framework -->
  <dependency>
    <groupId>io.dropwizard</groupId>
    <artifactId>dropwizard-core</artifactId>
    <version>${dropwizard.version}</version>
  </dependency>

  <!-- Microsoft SQL Server JDBC driver -->
  <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>${mssql.version}</version>
  </dependency>

  <!-- Kubernetes client (optional) -->
  <dependency>
    <groupId>io.fabric8</groupId>
    <artifactId>kubernetes-client</artifactId>
    <version>${fabric8.version}</version>
  </dependency>
</dependencies>
This gives your application Dropwizard for web services, a JDBC driver for MSSQL, and optional Kubernetes support for dynamic production configuration.

2. Run SQL Server via Docker for Local Development

Use Docker to get a local SQL Server instance up and running: 
docker run -e “ACCEPT_EULA=Y” \
  -e “SA_PASSWORD=YourStrong!Passw0rd” \
  -p 1433:1433 \
  --name sqlserver-dev \
  -d mcr.microsoft.com/mssql/server:2022-latest
This creates a running SQL Server container accessible at localhost:1433 with admin credentials ready for development.

3. Create config-dev.yml for Development

Define how your Dropwizard service should connect to the local SQL Server: 
server:
  applicationConnectors:
    - type: http
      port: 8080

database:
  driverClass: com.microsoft.sqlserver.jdbc.SQLServerDriver
  user: sa
  password: YourStrong!Passw0rd
  url: jdbc:sqlserver://localhost:1433;databaseName=master
  validationQuery: SELECT 1

# Optional: pool settings
databasePool:
  minSize: 1
  maxSize: 5
In your config class, mirror the structure: 
public class DatabaseConfig {
  @JsonProperty public String driverClass;
  @JsonProperty public String user;
  @JsonProperty public String password;
  @JsonProperty public String url;
  @JsonProperty public String validationQuery;
  @JsonProperty public int minSize;
  @JsonProperty public int maxSize;
}

public class AppConfig extends Configuration {
  @JsonProperty("database")
  public DatabaseConfig database;
}
In your Application class, initialize the connection: 
ManagedDataSource ds = configuration.database.build(environment.metrics(), "db");
environment.lifecycle().manage(ds);
Launch the service locally using: 
java -jar target/myapp.jar server config-dev.yml
Your Dropwizard app now reads and writes data through SQL Server!

4. Configure Kubernetes for Production

For production, use Kubernetes ConfigMaps and Secrets to avoid hardcoding credentials.

Create a ConfigMap:

kubectl create configmap sqlserver-config \
  --from-literal=DB_URL=jdbc:sqlserver://sqlserver-service:1433;databaseName=prodDb \
  --from-literal=DB_DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver \
  --from-literal=DB_VALIDATION_QUERY=SELECT 1

Create a Secret for Credentials:

kubectl create secret generic sqlserver-secret \
  --from-literal=DB_USER=produser \
  --from-literal=DB_PASSWORD=prodStrongPassword
This lets Kubernetes inject secure values into your application environment.

5. Create config-prod.yml for Production

Define environment-variable-based configuration: 
server:
  applicationConnectors:
    - type: http
      port: 8080

database:
  driverClass: ${DB_DRIVER}
  user: ${DB_USER}
  password: ${DB_PASSWORD}
  url: ${DB_URL}
  validationQuery: ${DB_VALIDATION_QUERY}

databasePool:
  minSize: 2
  maxSize: 10
Dropwizard will substitute environment values at runtime.

6. Kubernetes Deployment Snippet

Set up your deployment YAML to inject the env variables: 
env:
  - name: DB_URL
    valueFrom:
      configMapKeyRef:
        name: sqlserver-config
        key: DB_URL
  - name: DB_DRIVER
    valueFrom:
      configMapKeyRef:
        name: sqlserver-config
        key: DB_DRIVER
  - name: DB_VALIDATION_QUERY
    valueFrom:
      configMapKeyRef:
        name: sqlserver-config
        key: DB_VALIDATION_QUERY
  - name: DB_USER
    valueFrom:
      secretKeyRef:
        name: sqlserver-secret
        key: DB_USER
  - name: DB_PASSWORD
    valueFrom:
      secretKeyRef:
        name: sqlserver-secret
        key: DB_PASSWORD
Then run the service with: 
java -jar myapp.jar server config-prod.yml
Your Dropwizard app now connects to a managed SQL Server instance in Kubernetes, using secure credentials and config.

7. Why This Pattern Is Useful

  • Local Developer Experience: Quick setup via Docker speeds up development iterations.
  • Secure Production Setup: Only Kubernetes stored credentials are used—no secrets in code.
  • Configuration Clarity: Separate config files for dev and prod environments keep things organized.
  • Scalable Design: Easily scale deployment with connection pooling and load-balanced SQL Server setups.
By defining Docker-based local environments and Kubernetes-based secure deployment for SQL Server, paired with Dropwizard’s flexible configuration, you gain a maintainable, secure, and scalable architecture. Whether managing configs locally or in production, you now have a roadmap for integrating MS SQL Server into your Dropwizard projects with confidence and clarity.

image quote pre code
Share: