Spring AI JDBC Chat Memory: Building Persistent Conversational Applications with PostgreSQL and MariaDB

Learn how to implement persistent chat memory using Spring AI JDBC Chat memory. Complete guide with code examples using PostgreSQL and MariaDB for robust conversational experiences..

1. Introduction

When building AI chat applications, it’s important to remember past conversations so users can continue from where they left off. In our previous blog, we used an in-memory chat memory solution, which is quick and easy to set up. However, it comes with some serious limitations, especially when used in real-world, production-level applications.

In this blog, we’ll take things a step further and explore how to store chat history permanently using a relational database like PostgreSQL or MariaDB with Spring AI’s JDBC Chat Memory. This approach helps us overcome the limitations of in-memory chat memory by making chat data persistent, shareable across multiple app instances, and easier to manage.

Before we dive into the JDBC solution, let’s quickly understand why in-memory chat memory might not be enough for your production needs.

2. Limitations of In-Memory Chat Memory

• Data Loss on App Restart: When your application restarts, all chat history stored in memory is lost, creating a disjointed user experience. Users would need to reintroduce themselves and provide context again, leading to frustration.
• Not Suitable for Multiple Instances: If you’re running multiple instances of your application (common in production environments with load balancing), each instance has its own isolated memory. This means a user might interact with one instance, then get routed to another instance that has no memory of previous interactions.
• Unbounded Memory Growth: Without proper management, chat memory can grow indefinitely as conversations continue, potentially causing memory leaks and performance degradation over time.
• No Persistence Layer: There’s no way to back up conversation histories since they exist only in volatile memory.

3. Spring AI JDBC Chat Memory to the Rescue

Spring AI JDBC Chat Memory implementation provides a solution to these challenges by storing conversation history in a relational database. This approach offers:

• Persistence across restarts: Chat history remains intact even when your application restarts.
• Support for distributed environments: Multiple application instances can access the same conversation data.
• Centralized storage: Better management of memory usage with database-level optimizations.
• Data analysis possibilities: Stored conversations can be analyzed for insights or compliance purposes.
• Backup and recovery: Standard database backup procedures can protect the conversation history.

Let’s take a closer look at how JDBC Chat Memory works under the hood before implementing it with PostgreSQL and MariaDB.

4. How Spring AI JdbcChatMemory Works Under the Hood

The JdbcChatMemory class is at the heart of Spring AI JDBC chat memory implementation. It implements the ChatMemory interface by persisting messages in a database table via SQL operations.

Here’s how the core functionality works:

public class JdbcChatMemory implements ChatMemory {

    private static final String QUERY_ADD = "INSERT INTO ai_chat_memory (conversation_id, content, type) VALUES (?, ?, ?)";

    private static final String QUERY_GET = "SELECT content, type FROM ai_chat_memory WHERE conversation_id = ? ORDER BY "timestamp" DESC LIMIT ?";

    private static final String QUERY_CLEAR = "DELETE FROM ai_chat_memory WHERE conversation_id = ?";

    private final JdbcTemplate jdbcTemplate;

    // Constructor and factory method
    // ...

    // Implementation of ChatMemory interface methods
    @Override
    public void add(String conversationId, List<Message> messages) {
        this.jdbcTemplate.batchUpdate(QUERY_ADD, new AddBatchPreparedStatement(conversationId, messages));
    }

    @Override
    public List<Message> get(String conversationId, int lastN) {
        return this.jdbcTemplate.query(QUERY_GET, new MessageRowMapper(), conversationId, lastN);
    }

    @Override
    public void clear(String conversationId) {
        this.jdbcTemplate.update(QUERY_CLEAR, conversationId);
    }

    // Helper classes for batch operations and row mapping
    // ...
}

Key aspects of this implementation:

1. Table Structure: Messages are stored in an ai_chat_memory table with columns for conversation ID, message content, message type, and timestamp.
   • conversation_id: A unique identifier for each conversation, allowing multiple conversations to be stored in the same table.
   • content: The actual text content of the message.
   • type: The role of the message sender (USER, ASSISTANT, SYSTEM, or TOOL).
   • timestamp: When the message was added, used for ordering messages chronologically.
2. Core Operations:
   • add(): Inserts new messages into the database with the given conversation ID.
   • get(): Retrieves the most recent messages for a specific conversation ID.
   • clear(): Deletes all messages for a given conversation ID.
3. SQL Queries: The class uses prepared SQL statements for secure and efficient database operations.

Now let’s implement this solution with PostgreSQL and MariaDB!

5. Implementing JDBC Chat Memory: PostgreSQL & MariaDB

Let’s build two chat applications demonstrating Spring AI JDBC chat memory implementation. We’ll create:

• Single-User Chat Application: A simple chatbot where all users share the same conversation history.
• Multi-User Chat Application: An advanced chatbot that maintains separate conversation histories for different users.

5.1. Setting Up Project

Let’s start by setting up the core dependencies and configurations for both database types.

Step 1: Add Required Dependencies

Both implementations share similar core dependencies and configurations:

<dependencies>
    <!-- Spring Boot Web for REST endpoints -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    
    <!-- OpenAI Model Support - we'll configure it for Google Gemini -->
    <dependency>
        <groupId>org.springframework.ai</groupId>
        <artifactId>spring-ai-starter-model-openai</artifactId>
    </dependency>
    
    <!-- JDBC Chat Memory Support -->
    <dependency>
        <groupId>org.springframework.ai</groupId>
        <artifactId>spring-ai-starter-model-chat-memory-jdbc</artifactId>
    </dependency>
    
    <!-- JPA for database operations -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    
    <!-- Database-specific driver (choose one based on your database) -->
    <!-- PostgreSQL driver dependency -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    
    <!-- OR MariaDB driver dependency -->
    <dependency>
        <groupId>org.mariadb.jdbc</groupId>
        <artifactId>mariadb-java-client</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

<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>

In this configuration:

• spring-boot-starter-web: Enables us to build a web application with REST endpoints
• spring-ai-starter-model-openai: Provides integration with OpenAI’s API (though we’ll configure it for Google Gemini)
• spring-ai-starter-model-chat-memory-jdbc: Provides a JDBC-based memory backend to store conversation history. This is useful when you want your application to “remember” previous interactions by storing context in a relational database like PostgreSQL or MariaDB.
• spring-boot-starter-data-jpa: Adds support for Java Persistence API (JPA) with Spring Data. This simplifies database interactions using repositories and entities, making CRUD operations easier to implement.
• postgresql (runtime scope): Includes the PostgreSQL JDBC driver required to connect to a PostgreSQL database at runtime. You’ll need this if your project uses PostgreSQL as the backend database.
• mariadb-java-client (runtime scope): Includes the MariaDB JDBC driver. If your database is MariaDB instead of PostgreSQL, this driver is used to connect at runtime. You only need one of these two driver dependencies based on the database you’re using.
• spring-ai-bom: The dependencyManagement section uses Spring AI’s Bill of Materials (BOM) to ensure compatibility between Spring AI components. By importing the BOM, you don’t need to manually specify versions for each Spring AI artifact—it ensures compatibility and prevents version conflicts automatically.

Step 2: Configure Application Properties

Spring Boot auto-configures a DataSource and JdbcTemplate from application.yml (or .properties). Only the datasource block changes between PostgreSQL and MariaDB—everything else remains the same.

spring:
  application:
    name: spring-ai-jdbc-chat-memory   # Name of your Spring Boot application

  # ─── PostgreSQL Configuration ─────────────────────────────────────────────
  datasource:
    url: jdbc:postgresql://localhost:5432/springai   # JDBC connection URL for PostgreSQL
    username: <postgres_user>                        # PostgreSQL username
    password: <postgres_pass>                        # PostgreSQL password
    driver-class-name: org.postgresql.Driver         # PostgreSQL JDBC driver class

  # ─── OR MariaDB Configuration ─────────────────────────────────────────────
  # Uncomment the below section if you're using MariaDB instead of PostgreSQL
  # datasource:
  #   url: jdbc:mariadb://localhost:3306/springai     # JDBC connection URL for MariaDB
  #   username: <mariadb_user>                        # MariaDB username
  #   password: <mariadb_pass>                        # MariaDB password
  #   driver-class-name: org.mariadb.jdbc.Driver      # MariaDB JDBC driver class

ai:
  openai:
    api-key: ${GEMINI_API_KEY}                        # Inject your API key securely
    base-url: https://generativelanguage.googleapis.com/v1beta/openai
    chat:
      completions-path: /chat/completions
      options:
        model: gemini-2.0-flash-exp

📄 Configuration Overview

This configuration demonstrates how to set up a Spring Boot application using Spring AI with support for PostgreSQL or MariaDB as a chat memory backend, and integrates with Google’s Gemini model using the OpenAI starter. The base-url and completions-path settings direct requests to Google’s API instead of OpenAI’s.

🔌 Database Configuration (PostgreSQL or MariaDB)

The datasource section configures the database connection details for storing chat memory. Spring AI supports JDBC-based chat memory, so you can choose between PostgreSQL or MariaDB as the backend.

• The PostgreSQL section sets up a connection to a local PostgreSQL database using the standard JDBC URL, credentials, and driver.
• The MariaDB section is provided as a commented alternative. Simply uncomment it and comment out the PostgreSQL section to switch databases.

🛠️ Tip: Make sure the database is running locally with the appropriate username, password, and schema created (e.g., springai).

🤖 Google Gemini APIs are great for proof-of-concept (POC) projects since they offer limited usage without requiring payment. For more details, check out our blog, where we dive into how Google Gemini works with OpenAI and how to configure it in case of our Spring AI application.

Step 3: Configuring the Chat Client with JDBC Memory

We need to create a configuration class to set up our ChatClient with memory capabilities. For both database implementations, the ChatClient configuration remains identical:

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.jdbc.JdbcChatMemory;
import org.springframework.ai.chat.memory.jdbc.JdbcChatMemoryConfig;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
public class ChatClientConfig {

    @Bean
    public ChatClient chatClient(ChatClient.Builder chatClientBuilder, ChatMemory chatMemory) {
        return chatClientBuilder
                .defaultAdvisors(new MessageChatMemoryAdvisor(chatMemory))
                .build();
    }

    @Bean
    public ChatMemory jdbcChatMemory(JdbcTemplate jdbcTemplate) {
        return JdbcChatMemory.create(JdbcChatMemoryConfig.builder().jdbcTemplate(jdbcTemplate).build());
    }
}

Let’s break down what’s happening here:

1. We’re defining two Spring beans: chatMemory and chatClient
2. The chatMemory bean creates an instance of JdbcChatMemory, which uses Spring’s JdbcTemplate under the hood to persist chat messages in a relational database like PostgreSQL or MariaDB. This allows our chat memory to survive application restarts and makes it suitable for multi-user or production-ready environments.
3. The chatClient bean uses a builder pattern to create a chat client with the MessageChatMemoryAdvisor

The MessageChatMemoryAdvisor is a crucial component here. It:

• Automatically captures user messages and AI responses
• Stores them in the provided JDBC based chat memory implementation
• Makes past conversations available to the AI for context in future interactions

This advisor handles all the memory management work behind the scenes, so you don’t need to manually save each message.

5.2. Single-User Chat Application

Our first application is a simple chatbot where all users share the same conversation context. This is useful for:

• Educational resources where context should be preserved
• Public information kiosks
• FAQ chatbots on websites
• Shared assistant systems in team environments

import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.ai.chat.messages.Message;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api/chat")
public class SimpleJdbcChatMemoryController {

    private final ChatClient chatClient;

    private final ChatMemory chatMemory;

    public SimpleJdbcChatMemoryController(ChatClient chatClient, ChatMemory chatMemory) {
        this.chatClient = chatClient;
        this.chatMemory = chatMemory;
    }

    // Endpoint to send messages and get responses
    @PostMapping
    public String chat(@RequestBody String request) {
        return chatClient.prompt()
                .user(request)
                .call()
                .content();
    }

    // Endpoint to view conversation history
    @GetMapping("/history")
    public List<Message> getHistory() {
        return chatMemory.get("default", 100);
    }

    // Endpoint to clear conversation history
    @DeleteMapping("/history")
    public String clearHistory() {
        chatMemory.clear("default");

        return "Conversation history cleared";
    }
}

This controller exposes three REST endpoints that allow users to interact with an AI model using a conversational interface. It uses Spring AI’s ChatClient to send user messages and get AI responses, and ChatMemory to persist conversation history using a JDBC-backed implementation (like PostgreSQL or MariaDB).

Endpoints Summary:

1. POST /api/chat – To send a message and get a response
2. GET /api/chat/history – To view the conversation history
3. DELETE /api/chat/history – To clear the conversation history

Notice the use of the “default” conversation ID when accessing memory. Chat memory uses this ID as the default identifier for conversations when none is specified.

🧩 Chat Memory in Action: Key Endpoints Explained

a. Send Message – POST /api/chat

How It Works: When a user sends a message via the /api/chat endpoint:

• The chatClient.prompt().user(request).call() method is triggered.
• It takes the user’s input message from the request.
• The MessageChatMemoryAdvisor (configured behind the scenes in the ChatClient) automatically retrieves any previous messages stored in memory for the “default” conversation.
• It sends both the new user message and the previous context to the configured AI model (e.g., Google Gemini).
• The AI processes the full conversation context and returns a response.
• Both the user’s input and the AI’s response are stored in the database-backed memory (using JDBC), ensuring that context is preserved for future interactions.

b. View Chat History – GET /history

How It Works: When a user calls the /history endpoint:

• The chatMemory.get(“default”, 100) method retrieves up to 100 of the most recent messages stored in memory.
• These messages include both user inputs and AI responses from the “default” conversation.
• This helps you understand the full conversation context currently stored in JDBC memory, which is useful for debugging or displaying chat history in the UI.

c. Clear Chat History – DELETE /history

How It Works: When the /history endpoint is called with the DELETE method:

• The chatMemory.clear(“default”) method wipes all stored messages for the “default” conversation.
• This removes any context previously stored—essentially resetting the chat memory.
• The next time the user sends a message, it will be treated as a completely new conversation.
• This is especially useful when the user wants to start fresh or if you want to reset the AI’s memory programmatically after a certain point in the flow.

🖥️ Verify the output

Here’s a detailed walkthrough of testing the single-user chat memory implementation:

• Send an initial message
  • 💬 POST /api/chat with: "Hello, my name is Bunty Raghani"
  • 🤖 LLM responds with a greeting and remembers the name.
  • 💾 Behind the scenes: A new record is inserted into ai_chat_memory with conversation_id = ‘default’, storing the message “Hello, my name is Bunty Raghani” This allows the system to remember the name and context for future conversations.
    
• Ask a follow-up
  • 💬 POST /api/chat with: "what's my name?"
  • 🤖 LLM replies: "Your name is Bunty Raghani." (thanks to memory)
  • 🔍 Behind the scenes: The system retrieves the stored message for conversation_id = ‘default’ from ai_chat_memory, recognizing the name “Bunty Raghani” and providing a relevant response based on the context.

• Check memory
  • 💬 GET /api/chat/history
  • 🤖 Returns full conversation so far.
  • 🔍 Behind the scenes: The system fetches all records associated with conversation_id = ‘default’ from ai_chat_memory, returning a list of user inputs and AI responses from the current session. This helps track the context of the ongoing conversation.

• Clear the memory
  • 🧹 DELETE /api/chat/history
  • 🤖 Returns: "Conversation history cleared"
  • 🗑️ Behind the scenes: The system executes an SQL query: DELETE FROM ai_chat_memory WHERE conversation_id = ‘default’, removing all previously stored conversation data. This effectively resets the memory, clearing any prior context.

• Test again after clearing
  • 💬 POST /api/chat with: "what's my name?"
  • 🤖 LLM responds: "As a large language model, I have no memory of past conversations. Therefore, I don't know your name. You haven't told me!"
  • 🔍 Behind the scenes: Since the memory has been cleared for conversation_id = ‘default’, there are no stored records to refer to. The LLM responds with a default reply indicating that no previous context is available, allowing the user to share their name again if they choose.

1 – Initial Message – Spring AI JDBC Chat Memory2 – Follow-up Question – Spring AI JDBC Chat Memory3 – Check Memory Content – Spring AI JDBC Chat Memory4 – Clear the Memory – Spring AI JDBC Chat Memory5 – Test Again After Clearing the Memory – Spring AI JDBC Chat Memory

This confirms that the chat memory is working as intended — storing, retrieving, and resetting context effectively.

The single-user approach works well for many applications, but what if we need to support multiple users with individual conversation histories?

5.3. Multi-User Chat Application

When your application needs to handle multiple users, each with their own conversation history, storing all chats in a single memory won’t work. Instead, we need to isolate conversations by a unique identity like a user ID.

Our second application maintains separate conversation histories for different users. This is essential for:

• Personal assistant applications
• Customer support systems
• Personalized learning platforms
• Any application where user-specific context matters

import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.ai.chat.messages.Message;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

import static org.springframework.ai.chat.client.advisor.AbstractChatMemoryAdvisor.CHAT_MEMORY_CONVERSATION_ID_KEY;

@RestController
@RequestMapping("/api/users")
public class MultiUserJdbcChatMemoryController {

    private final ChatClient chatClient;

    private final ChatMemory chatMemory;

    public MultiUserJdbcChatMemoryController(ChatClient chatClient, ChatMemory chatMemory) {
        this.chatClient = chatClient;
        this.chatMemory = chatMemory;
    }

    @PostMapping("/{userId}/chat")
    public String chat(
            @PathVariable String userId,
            @RequestBody String request) {

        return chatClient
                .prompt()
                .advisors(advisorSpec -> advisorSpec.param(CHAT_MEMORY_CONVERSATION_ID_KEY, userId))
                .user(request)
                .call().content();
    }

    @GetMapping("/{userId}/history")
    public List<Message> getHistory(@PathVariable String userId) {
        return chatMemory.get(userId, 100);
    }

    @DeleteMapping("/{userId}/history")
    public String clearHistory(@PathVariable String userId) {
        chatMemory.clear(userId);

        return "Conversation history cleared for user: " + userId;
    }
}

The key difference here is how we’re using the userId as a conversation identifier:

• Dynamic Conversation IDs: Each user gets their own conversation ID (the user ID).
• Isolated Contexts: Users only see responses informed by their own previous interactions.
• User-Specific Endpoints: All endpoints include the user ID in the path.
• Conversation ID Parameter: We explicitly set the conversation ID when prompting the LLM. In the chat endpoint, we use .advisors(advisorSpec -> advisorSpec.param(CHAT_MEMORY_CONVERSATION_ID_KEY, userId)) to tell the MessageChatMemoryAdvisor which conversation ID to use. In our case, the userId path variable acts as the conversation ID.
• The history and clear endpoints now work with user-specific conversation IDs instead of the default one.

This design pattern is perfect for applications requiring personalized conversation experiences. The beauty is that we’re using the same underlying JdbcChatMemory implementation—we’re just using different conversation IDs.

🖥️ Verify the Output

Let’s test how chat memory works when handling multiple users. We’ll walk through two separate conversations — one for Alice and one for Bob — and see how their chat histories are independently managed.

🧑‍💻 Alice’s Conversation Flow

• Send a message
  💬 Alice says: “Hi, I’m Alice”
  🤖 The LLM replies: “Hi Alice! It’s nice to meet you! How can I help you today?”
  💾 Behind the scenes: A record is inserted into ai_chat_memory with conversation_id = 'Alice'

• Ask for name
  💬 Alice says: “What’s my name?”
  🤖 The LLM responds using memory: “Your name is Alice.”
  🔍 Behind the scenes: The system retrieves the previous messages for conversation_id = 'Alice' to maintain context

🧑‍💻 Bob’s Conversation Flow

• Send a message
  💬 Bob says: “Hey, I’m Bob”
  🤖 The LLM replies: “Hi Bob! How can I help you today?”
  💾 Behind the scenes: A new set of records is created in ai_chat_memory with conversation_id = 'Bob'

• Clear Bob’s memory
  🧹 Call DELETE /api/users/Bob/history
  🤖 Response: “Conversation history cleared for user: Bob”
  🗑️ SQL executed: DELETE FROM ai_chat_memory WHERE conversation_id = 'Bob'

• Ask for name
  💬 Bob says: “What’s my name?”
  🤖 Since Bob’s memory was cleared, the LLM says: “As an AI, I don’t know your name. You haven’t told me! You can tell me if you’d like.”
  ❌ No previous context is available for conversation_id = 'Bob'

🔁 Alice’s Memory Is Still Intact

• Ask again
  💬 Alice says: “What’s my name?”
  🤖 The LLM responds correctly: “Your name is Alice.”
  🔍 The system successfully retrieves Alice’s conversation context and responds accordingly.

This confirms that Alice’s conversation memory remains intact and unaffected by Bob’s actions — proving that each user has their own isolated memory space.

1 – Alice sends an initial message – Multi-User JDBC Chat Memory2 – Alice ask for name – Multi-User JDBC Chat Memory3 – Bob sends an initial message – Multi-User JDBC Chat Memory4 – Clear memory for Bob – Multi-User JDBC Chat Memory5 – Bob ask for name post memory clean operation – Multi-User JDBC Chat Memory6 – Alice ask for name again – Multi-User JDBC Chat Memory

This demonstrates the isolation between different users’ conversation histories in the database. Each user has their own dedicated conversation space, making this approach ideal for personalized assistant applications.

6. Enabling SQL Logging

When implementing or troubleshooting your JDBC chat memory, it can be helpful to see the SQL queries being executed. You can enable SQL logging by adding these settings to your application.yaml (or application.properties)

logging:
  level:
    org.springframework.jdbc.core.JdbcTemplate: TRACE
    org.springframework.jdbc.core.StatementCreatorUtils: TRACE

With this configuration, you’ll see detailed logs like:

// ================================
// 1. User sends initial message: "Hello, my name is Bunty Raghani"
// Operations: Retrieve previous context + Insert user and AI response into database
// ================================
2025-04-26T16:11:27.665+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
2025-04-26T16:11:27.666+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT content, type FROM ai_chat_memory WHERE conversation_id = ? ORDER BY "timestamp" DESC LIMIT ?]
2025-04-26T16:11:27.676+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [default], value class [java.lang.String], SQL type unknown
2025-04-26T16:11:27.676+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [100], value class [java.lang.Integer], SQL type unknown
2025-04-26T16:11:27.683+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing SQL batch update [INSERT INTO ai_chat_memory (conversation_id, content, type) VALUES (?, ?, ?)]
2025-04-26T16:11:27.683+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO ai_chat_memory (conversation_id, content, type) VALUES (?, ?, ?)]
2025-04-26T16:11:28.797+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing SQL batch update [INSERT INTO ai_chat_memory (conversation_id, content, type) VALUES (?, ?, ?)]
2025-04-26T16:11:28.797+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-2] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO ai_chat_memory (conversation_id, content, type) VALUES (?, ?, ?)]



// ================================
// 2. User checks conversation history
// Operations: Retrieve last 100 messages from database
// ================================
2025-04-26T16:11:41.984+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
2025-04-26T16:11:41.985+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT content, type FROM ai_chat_memory WHERE conversation_id = ? ORDER BY "timestamp" DESC LIMIT ?]
2025-04-26T16:11:41.987+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [default], value class [java.lang.String], SQL type unknown
2025-04-26T16:11:41.987+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [100], value class [java.lang.Integer], SQL type unknown



// ================================
// 3. User clears conversation history
// Operations: Delete all records for conversation_id = 'default'
// ================================
2025-04-26T16:11:50.719+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-4] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL update
2025-04-26T16:11:50.722+05:30 DEBUG 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-4] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [DELETE FROM ai_chat_memory WHERE conversation_id = ?]
2025-04-26T16:11:50.726+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [default], value class [java.lang.String], SQL type unknown
2025-04-26T16:11:50.746+05:30 TRACE 2719 --- [spring-boot-ai-jdbc-chat-memory-mariadb] [nio-8080-exec-4] o.s.jdbc.core.JdbcTemplate               : SQL update affected 2 rows

What you’ll see: INSERT, SELECT, and DELETE statements in your console—great for understanding and troubleshooting.

This detailed logging helps you:

• Verify that the correct SQL statements are being executed
• Debug issues with parameter binding
• Understand the timing and sequence of database operations
• Identify potential performance bottlenecks

7. Disabling Schema Initialization

If you’re using database migration tools like Flyway or Liquibase, you might want to disable Spring AI’s automatic schema initialization and manage the schema yourself.

Add this property to your application.yaml (or application.properties)

spring:
  ai:
    chat:
      memory:
        jdbc:
          initialize-schema: false
          

📝 Add Migration Scripts

After disabling automatic schema initialization, you can create the ai_chat_memory table yourself by adding migration scripts to your Flyway or Liquibase configuration.

🦭 For MariaDB (Flyway format: V1__create_ai_chat_memory.sql)

CREATE TABLE IF NOT EXISTS ai_chat_memory (
    conversation_id VARCHAR(36) NOT NULL,
    content TEXT NOT NULL,
    type VARCHAR(10) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT type_check CHECK (type IN ('USER', 'ASSISTANT', 'SYSTEM', 'TOOL'))
);

CREATE INDEX IF NOT EXISTS ai_chat_memory_conversation_id_timestamp_idx
ON ai_chat_memory(conversation_id, `timestamp` DESC);

🐘 For PostgreSQL (Flyway format: V1__create_ai_chat_memory.sql)

CREATE TABLE IF NOT EXISTS ai_chat_memory (
    conversation_id VARCHAR(36) NOT NULL,
    content TEXT NOT NULL,
    type VARCHAR(10) NOT NULL CHECK (type IN ('USER', 'ASSISTANT', 'SYSTEM', 'TOOL')),
    "timestamp" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS ai_chat_memory_conversation_id_timestamp_idx
ON ai_chat_memory(conversation_id, "timestamp" DESC);

Using migrations gives you more control over database schema evolution, especially in production environments where database changes need to be carefully managed.

8. Source Code

For complete working examples of both PostgreSQL and MariaDB implementations, check out our GitHub repositories:

🔗 PostgreSQL: https://github.com/BootcampToProd/spring-boot-ai-jdbc-chat-memory-postgres
🔗 MariaDB: https://github.com/BootcampToProd/spring-boot-ai-jdbc-chat-memory-mariadb

9. Things to Consider

When implementing chat memory in your Spring AI applications, consider these important factors:

1. Conversation Pruning: Implement logic to archive or delete old conversations to prevent unlimited database growth. Consider a TTL (Time-To-Live) approach based on your application’s needs.
2. Security: Ensure your database is properly secured, with encrypted connections, strong authentication, and appropriate access controls. Remember that conversation histories might contain sensitive information.
3. Backup Strategy: Implement a regular backup strategy for your database to prevent data loss in production.
4. Data Privacy: Consider implementing features that allow users to request deletion of their conversation history (relevant for GDPR and other privacy regulations).
5. Monitoring: Set up monitoring for database performance and connection pool usage to detect issues early.
6. Scaling: Plan for horizontal scaling if your application needs to handle a large number of concurrent users.

10. FAQs

11. Conclusion

Adding JDBC Chat Memory to your Spring AI applications is a game-changer for building better conversational experiences. By storing conversations in PostgreSQL or MariaDB databases instead of memory, your AI assistant can remember what users say even after your application restarts or across multiple servers. This approach solves the key problems of data loss, distributed deployment challenges, and memory growth that in-memory solutions face. Whether you’re building a single shared assistant or personalized experiences for many users, the implementation is straightforward with Spring AI’s built-in support. With just a few configuration steps, you can create AI applications that truly remember conversations long-term, leading to more natural, helpful, and personalized interactions that users will appreciate. As conversational AI becomes more common in everyday applications, this kind of persistent memory isn’t just nice to have—it’s essential for delivering the seamless experiences users expect.

12. Learn More