SQLite Support Implementation#

#Overview

This document outlines the implementation of SQLite database support for the Firmiana Matrix Chat Server, providing an alternative to MongoDB for deployments that require a lightweight, file-based database solution.

#Objectives

• Add SQLite as an alternative database backend alongside MongoDB
• Maintain compatibility with existing MongoDB implementations
• Provide configuration options to switch between database backends
• Ensure data persistence and query performance

#Scope

• Add SQLite JDBC driver dependency
• Configure Micronaut Data for SQLite support
• Create database schema and migrations
• Update repository layer to support both databases
• Add configuration properties for database selection

#Implementation Details

#Dependencies

• sqlite-jdbc: SQLite JDBC driver
• Micronaut Data JPA: ORM support
• Liquibase or Flyway: Database migrations

#Configuration

• Add database.type property (mongodb or sqlite)
• Add sqlite.path property for database file location
• Conditional bean creation based on database type

#Database Schema

• Create schema for all entities
• Define indexes for performance
• Set up relationships and constraints

#Benefits

• Lightweight deployment option
• No external database server required
• Suitable for development and testing
• Reduced infrastructure complexity

#Timeline

• Phase 1: Add dependencies and configuration
• Phase 2: Create database schema
• Phase 3: Update repositories
• Phase 4: Testing and validation