Migration from manual sqlite3 to a more robust technique

[IamMuuo]

Migrate from SQLite to Drift and Freezed

Summary

I propose that we migrate our current data management implementation, which uses plain SQLite, to utilize Drift and Freezed. This migration will help streamline database operations, enforce better type safety, and improve maintainability.

Problem Statement

The current SQLite implementation, while functional, has the following drawbacks:

1. Lack of Type Safety: Writing raw SQL queries increases the risk of runtime errors and makes it harder to catch issues during compile-time.
2. Manual Data Mapping: Data mapping between models and database records has to be done manually, which is error-prone and time-consuming.
3. Limited Abstraction: Working directly with SQLite involves more boilerplate code, making it difficult to maintain and refactor.
4. No Immutability Enforcement: The existing models lack immutability, making state management less predictable.

Proposed Solution

1. Use Drift for Database Management:

   • Drift (formerly known as moor) provides type-safe queries, schema migrations, and integrates well with Dart’s language features.
   • It offers an ORM-like experience, reducing boilerplate code and making database operations more intuitive.

2. Leverage Freezed for Immutable Models:

   • Freezed helps create immutable data models with automatic JSON serialization support.
   • Using Freezed, we can define our data classes once and have the necessary copyWith, toJson, and fromJson methods auto-generated.

3. Single Database Connection:

   • We can create a single AppDatabase class that will manage all our tables and provide a centralized point for database interactions.

Benefits

1. Increased Type Safety: With Drift’s type-safe queries, we can catch potential issues during compile time, reducing runtime errors.
2. Reduced Boilerplate Code: Using Freezed and Drift, we can remove a lot of repetitive code, making the codebase cleaner and easier to maintain.
3. Better Model Management: With Freezed, data models are immutable, making state management simpler and more predictable.
4. Improved Maintainability: Future changes to database schemas and queries will be easier to manage with Drift’s migration capabilities.

Tasks

1. Set up drift and freezed dependencies in pubspec.yaml.
2. Create Freezed models for existing tables (e.g., Story, Organization, etc.).
3. Set up Drift tables and DAOs (Data Access Objects) for existing models.
4. Refactor the existing SQLite operations to use Drift and Freezed models.
5. Update the documentation to reflect the new data management approach.

Additional Information

• Drift Documentation
• Freezed Documentation

I believe this migration will significantly enhance the project’s overall architecture and maintainability. Looking forward to feedback and thoughts from the maintainers!

@IamMuuo