Persisting Spring AI ChatMemory with JDBC to MySQL: Practical Example and Architecture

Scenario

Spring AI ChatMemory can be configured to store conversation history in a relational database via JDBC instead of the default in‑memory repository.

Core components

JdbcChatMemoryRepository

Implements ChatMemoryRepository using Spring's JdbcTemplate, writes messages to the table SPRING_AI_CHAT_MEMORY. Supports MySQL, PostgreSQL, SQL Server, HSQLDB, H2. Each conversation is identified by conversation_id; the framework serializes and deserializes messages automatically.

MessageWindowChatMemory

Recommended implementation that keeps a fixed‑size sliding window of messages (configurable via maxMessages). When the window exceeds the limit, the oldest non‑system messages are dropped while system messages are retained. The window logic is unchanged when the underlying repository is switched to JDBC.

MessageChatMemoryAdvisor

Advisor that injects conversation history before each request and writes new messages back to the repository after the model replies.

Database schema

The framework expects a table named SPRING_AI_CHAT_MEMORY with columns: id BIGINT primary key, auto‑increment conversation_id VARCHAR(36) – unique conversation identifier content TEXT – JSON‑serialized message type VARCHAR(10) – USER / ASSISTANT / SYSTEM / TOOL timestamp TIMESTAMP – message time

A composite index idx_conv_ts (conversation_id, timestamp) covers the typical query pattern WHERE conversation_id = ? ORDER BY timestamp, giving high‑performance reads.

The framework can create the table automatically; the property spring.ai.chat.memory.repository.jdbc.initialize-schema accepts always, embedded, or never. Development usually uses always, production prefers never and external migration tools.

Architecture comparison

Data lifecycle : In‑memory data is lost on JVM restart; JDBC persists across restarts.

Cross‑instance sharing : In‑memory does not support; JDBC allows multiple instances to share the same database.

Memory pressure : In‑memory consumes heap; JDBC offloads storage to the database.

Auditing : In‑memory provides no audit; JDBC enables direct SQL queries.

Configuration complexity : In‑memory is zero‑config; JDBC requires a datasource and JDBC starter.

Implementation steps

MySQL preparation

CREATE DATABASE IF NOT EXISTS spring_ai DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

pom.xml

Add Spring AI BOM, the web starter, the Ollama model starter, the JDBC chat‑memory starter and the MySQL driver.

<properties>
    <java.version>17</java.version>
    <spring-ai.version>1.1.2</spring-ai.version>
</properties>
...
<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>org.springframework.ai</groupId>
            <artifactId>spring-ai-bom</artifactId>
            <version>${spring-ai.version}</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>
...
<dependency>
    <groupId>org.springframework.ai</groupId>
    <artifactId>spring-ai-starter-model-ollama</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.ai</groupId>
    <artifactId>spring-ai-starter-model-chat-memory-repository-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

Schema script

CREATE TABLE IF NOT EXISTS SPRING_AI_CHAT_MEMORY (
    id BIGINT NOT NULL AUTO_INCREMENT,
    conversation_id VARCHAR(36) NOT NULL,
    content TEXT NOT NULL,
    type VARCHAR(10) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_conv_ts (conversation_id, timestamp),
    CONSTRAINT type_check CHECK (type IN ('USER','ASSISTANT','SYSTEM','TOOL'))
);

application.yml

server:
  port: 886
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/spring_ai?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
  ai:
    ollama:
      base-url: http://localhost:11434
      chat:
        model: qwen2.5:7b-instruct
        options:
          temperature: 0.7
          num-ctx: 4096
    chat:
      memory:
        repository:
          jdbc:
            initialize-schema: always
            schema: classpath:sql/schema-mysql.sql
logging:
  level:
    org.springframework.ai.chat.client.advisor: DEBUG
    org.springframework.jdbc: DEBUG

Chat configuration class

package com.badao.ai.config;

import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.client.advisor.MessageChatMemoryAdvisor;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.ai.chat.memory.MessageWindowChatMemory;
import org.springframework.ai.chat.memory.repository.jdbc.JdbcChatMemoryRepository;
import org.springframework.ai.chat.model.ChatModel;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class ChatConfig {

    @Bean
    public ChatMemory chatMemory(JdbcChatMemoryRepository jdbcChatMemoryRepository) {
        // Replace the repository with the JDBC implementation, window strategy unchanged
        return MessageWindowChatMemory.builder()
                .chatMemoryRepository(jdbcChatMemoryRepository)
                .maxMessages(20)   // keep at most 20 recent messages
                .build();
    }

    @Bean
    public ChatClient chatClient(ChatModel chatModel, ChatMemory chatMemory) {
        return ChatClient.builder(chatModel)
                .defaultAdvisors(MessageChatMemoryAdvisor.builder(chatMemory).build())
                .build();
    }
}

Switching from InMemory to JDBC only requires injecting JdbcChatMemoryRepository into the ChatMemory bean; all other code remains unchanged.

Controller

package com.badao.ai.controller;

import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/api")
public class MemoryChatController {

    private final ChatClient chatClient;

    public MemoryChatController(ChatClient chatClient) {
        this.chatClient = chatClient;
    }

    @PostMapping("/chat/memory")
    public ChatResponse chatWithMemory(@RequestBody MemoryChatRequest request) {
        String result = chatClient.prompt()
                .user(request.message())
                .advisors(advisor -> advisor.param(ChatMemory.CONVERSATION_ID, request.conversationId()))
                .call()
                .content();
        return new ChatResponse(200, "success", result);
    }

    public record MemoryChatRequest(String message, String conversationId) {}
    public record ChatResponse(int code, String msg, String data) {}
}

Underlying mechanisms

Auto‑configuration discovery

When the JDBC starter is on the classpath, Spring Boot’s ChatMemoryAutoConfiguration detects JdbcChatMemoryRepository. If a DataSource bean is present, it creates the repository bean and disables the default in‑memory bean via conditional annotations.

Message persistence flow

After the model generates a response, MessageChatMemoryAdvisor calls ChatMemory.add(conversationId, messages). MessageWindowChatMemory merges the new message into its window.

The merged list is passed to JdbcChatMemoryRepository.saveAll, which deletes existing rows for the conversation and inserts the full list.

When reading, findByConversationId returns messages ordered by timestamp; the window then selects the most recent maxMessages entries for the prompt.

Window‑database interaction

The window retrieves only the latest maxMessages rows via an ordered query, keeping the prompt size bounded even if the conversation contains thousands of messages. Old rows remain in the table for full‑history queries.

Best‑practice recommendations

Set maxMessages to 10‑20 depending on the model’s context window.

Use initialize-schema: never in production and manage the table with Flyway or Liquibase.

Store custom schema scripts under classpath:sql/ and reference them via the schema property.

Monitor SQL statements with org.springframework.jdbc logging if needed.