1. Introduction

During this exercise we will configure Logback to write log events to an H2 in-memory database using the DBAppender.

This is a useful technique for scenarios where you want to query, search, or analyse your application logs using SQL — for example during development or in audit-heavy environments.

2. Background info

Some definitions

  • SLF4J is the de-facto standard logging API used by Spring Boot

  • The physical logging implementation (Logback, Log4j2, etc.) is decoupled from SLF4J

  • Spring Boot uses Logback as the default logging implementation

  • Logback’s DBAppender can write log events directly into database tables

As of Logback version 1.2.8, DBAppender no longer ships with the main Logback distribution. It has been moved to a separate project called logback-db. Spring Boot 4 ships with Logback 1.5.x, so you must add the logback-db dependency explicitly.

3. Exercise: Logging to H2

3.1. Step 1: Add dependencies

Make sure H2 is in your pom.xml (it may already be there from the training setup):

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Add the logback-db dependency required for DBAppender:

<dependency>
    <groupId>ch.qos.logback.db</groupId>
    <artifactId>logback-classic-db</artifactId>
    <version>1.2.11.1</version>
</dependency>
The logback-classic-db artifact automatically pulls in logback-core-db as a transitive dependency.

3.2. Step 2: Configure H2 in application.properties

Make sure the following properties are set in your application.properties (or in your profile-specific properties file):

# H2 in-memory database for this exercise
spring.datasource.url=jdbc:h2:mem:carDb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

# Enable the H2 web console
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

# Let Hibernate manage the entity tables
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
The H2 console will be available at http://localhost:8080/h2-console after starting the application. Use the JDBC URL jdbc:h2:mem:carDb and username sa (empty password) to connect.

3.3. Step 3: Add logback-spring.xml

Create the file logback-spring.xml in src/main/resources:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!-- Include Spring Boot's default console and file logging -->
    <include resource="org/springframework/boot/logging/logback/defaults.xml"/>
    <include resource="org/springframework/boot/logging/logback/console-appender.xml"/>

    <!-- Read datasource properties from application.properties -->
    <springProperty name="db.driver" source="spring.datasource.driver-class-name"/>
    <springProperty name="db.url" source="spring.datasource.url"/>
    <springProperty name="db.username" source="spring.datasource.username"/>
    <springProperty name="db.password" source="spring.datasource.password"/>

    <!-- Database appender -->
    <appender name="DB" class="ch.qos.logback.classic.db.DBAppender">
        <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
            <driverClass>${db.driver}</driverClass>
            <url>${db.url}</url>
            <user>${db.username}</user>
            <password>${db.password}</password>
        </connectionSource>
    </appender>

    <root level="info">
        <appender-ref ref="CONSOLE"/>
        <appender-ref ref="DB"/>
    </root>

</configuration>
We use <springProperty> instead of regular <property> so that the values are resolved from Spring’s Environment (i.e. your application.properties). This is why the file must be named logback-spring.xml — the -spring suffix enables Spring Boot extensions like <springProperty> and <springProfile>.

3.4. Step 4: Create the logging tables

The DBAppender requires three tables: logging_event, logging_event_property, and logging_event_exception. These must exist before the appender can write to them.

Option A: Create tables via a schema.sql file (recommended)

Create the file src/main/resources/schema.sql with the following content. Spring Boot will automatically execute it at startup when using an embedded database like H2:

CREATE TABLE IF NOT EXISTS logging_event (
    timestmp         BIGINT NOT NULL,
    formatted_message TEXT NOT NULL,
    logger_name       VARCHAR(254) NOT NULL,
    level_string      VARCHAR(254) NOT NULL,
    thread_name       VARCHAR(254),
    reference_flag    SMALLINT,
    arg0              VARCHAR(254),
    arg1              VARCHAR(254),
    arg2              VARCHAR(254),
    arg3              VARCHAR(254),
    caller_filename   VARCHAR(254) NOT NULL,
    caller_class      VARCHAR(254) NOT NULL,
    caller_method     VARCHAR(254) NOT NULL,
    caller_line       CHAR(4) NOT NULL,
    event_id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS logging_event_property (
    event_id     BIGINT NOT NULL,
    mapped_key   VARCHAR(254) NOT NULL,
    mapped_value TEXT,
    PRIMARY KEY (event_id, mapped_key),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
);

CREATE TABLE IF NOT EXISTS logging_event_exception (
    event_id   BIGINT NOT NULL,
    i          SMALLINT NOT NULL,
    trace_line VARCHAR(254) NOT NULL,
    PRIMARY KEY (event_id, i),
    FOREIGN KEY (event_id) REFERENCES logging_event(event_id)
);

Add this to your application.properties to ensure the schema is always initialised:

spring.sql.init.mode=always
Option B: Create tables manually via the H2 console

If you prefer to create the tables manually, start the application, open http://localhost:8080/h2-console, connect to the database, and execute the SQL script above.

With Option B, the DBAppender will fail to write logs during startup (before you create the tables). Option A is the cleaner approach.

3.5. Step 5: Restart and test

Restart your application and make some requests using Postman (e.g. create and retrieve some cars).

Then open the H2 console at http://localhost:8080/h2-console and run:

SELECT * FROM logging_event ORDER BY event_id DESC;

You should see all your application’s log events stored in the database, including the timestamp, log level, logger name, formatted message, thread name, and caller information.

Other useful queries:
-- Only errors and warnings
SELECT * FROM logging_event WHERE level_string IN ('ERROR', 'WARN') ORDER BY event_id DESC;

-- Logs from your application package only
SELECT * FROM logging_event WHERE logger_name LIKE 'com.acme.carapp%' ORDER BY event_id DESC;

-- Check if any exceptions were logged
SELECT e.event_id, e.formatted_message, ex.trace_line
FROM logging_event e
JOIN logging_event_exception ex ON e.event_id = ex.event_id
ORDER BY e.event_id DESC;

4. Takeaway

In this exercise, you learned how to configure Logback’s DBAppender to persist log events to an H2 database. This technique allows you to query your logs using SQL, which can be very useful for debugging and auditing purposes.

In a production environment, you would typically use a dedicated database (e.g. PostgreSQL or MySQL) for log storage, and consider connection pooling for the DBAppender’s connection source (e.g. using `DataSourceConnectionSource with a HikariCP pool).

5. Further reading

Logback DBAppender documentation

https://logback.qos.ch/manual/appenders.html

logback-db project on GitHub

https://github.com/qos-ch/logback-db

Spring Boot Logging

https://docs.spring.io/spring-boot/how-to/logging.html