Multi-Database SQLite Rails Application

Multi-Database SQLite Rails Application

This project demonstrates a modern implementation of a multi-database architecture using Ruby on Rails 8 and SQLite, where each user has their own isolated database. Such a setup is particularly beneficial for Software as a Service (SaaS) applications that require strict data isolation and multi-tenancy. It ensures that users can safely store and manage their data independently.

Features

• Multi-Database Architecture: Assigns a dedicated SQLite database to each user for complete data isolation.
• Automatic Database Creation: User databases are created seamlessly on first login.
• Database Migration Management: Streamlined process for easy migration management across user databases.
• Modern Rails Stack: Utilizes the latest Rails framework, along with Datastar, Stimulus, and Tailwind CSS for a rich UI.
• Built-in Authentication: Integrates user authentication with Rodauth.
• Database Tools: Includes Rake tasks for effective database management and maintenance.
• Production Ready: Configured for deployment using Kamal and Docker, making it ready for real-world applications.

Architecture Overview

The application consists of a unique multi-database architecture that separates user data through:

• A Primary Database that holds user accounts and authentication data.
• Individual User Databases assigned to each user, ensuring complete data privacy.

Key Components

1. UserDatabaseService: Manages all user database operations including creation, connection handling, and migrations.
2. UserDatabaseSwitching: A controller concern that automatically switches to the correct user's database upon login, maintaining the integrity of user sessions.
3. UserScopedRecord: Serves as the base class for models that are stored in user databases, allowing them to inherit data handling features.

   class Post < UserScopedRecord
       # This model will be stored in the user's database
   end
   

4. Database Management Tasks: Provides Rake tasks to list, create, destroy, back up, migrate, and check the integrity of user databases.

Development and Testing

This project allows easy integration of new user-scoped models, ensuring that each model aligns with the user’s isolated database setup. Testing can be performed using the built-in Rails testing framework with commands such as:

bin/rails test

Security Considerations

• Database isolation ensures no cross-user data leakage.
• Automatic validation and sanitation of user database paths during authentication.

Tech Stack

• Backend: Rails 8.0.2, Ruby 3.2+
• Database: SQLite 3.8+
• Authentication: Rodauth
• Frontend: Datastar, Tailwind CSS
• Deployment: Kamal, Docker

This architecture may fit small to medium SaaS applications needing stringent data isolation, rapid prototyping, and single-server deployment. For larger applications, alternative strategies like PostgreSQL or database sharding may be more appropriate.

Contributions are welcome, allowing developers to build upon and enhance this framework with new features and functionalities. Acknowledgments are due to the developers of the foundational tools utilized in this project.