Dynamic database schema based on user modules

[Darius1323]

Hi, I am working on an internship project for a company that wants to implement a modular flutter application with an offline-first functionality. Therefore, I decided to use the Drift library to help implement the application's persistence layer as soon as possible and help handle the relations between modules.

Although I've read the entire documentation, I'm still new to Drift so please excuse my possible misunderstandings. :D

I was trying to leverage Migrations to update the database schema and create new tables without defining them into @DriftDatabase annotations from different modules, but I haven't succeeded.

Is it possible to update the initial database schema with new tables and relations at runtime using Drift Migrations?

[simolus3]

So my first question here is: Why? Drift (at runtime) can support flexible tables, but the generated code obviously assumes a static schema. If dynamic schema only refers to enabling different tables (as a subset of a known set of available tables) depending on modules, that's easily possible (add an onCreate parameter to your MigrationStrategy that doesn't call m.createAll() but instead filters for tables depending on enabled modules).
But it will still be complex to work with (because you need to react to new modules being added or removed). And an empty table that you're not using at runtime costs almost nothing in terms of disk usage or performance. So I don't see what advantages you expect from a dynamic schema that justifies the complexity.

If you want to define tables in different Dart packages to keep the architecture of your app modular, you can! Drift supports module code generation for this, which you can enable on all packages to generate code per-module. You can still use a central app database that references tables from other modules, which drift will then import and use in generated code. Does that go into the direction you were looking for?

[Darius1323]

Hi Simon! Thank you so much for your reply!

I completely agree with your point for creating empty tables this will be the most straightforward approach.

However, the only reason why I was thinking to implement a dynamic table inclusion by using migration is because over time the actual Database class that creates the connection and the schema from the all possible modules will become quite cluttered with imports for DAOs and Tables. Therefore, I approached the problem by generating the code for each module by enabling the modular build from build.yaml. Afterwards, I wanted to just get the TableInfo that is generated by a module and migrate it into the initialised database.

And of course I ran into problems with this and I haven't succeeded due to the increased complexity.

Anyway, I think I will go with the definition of the entire schema and then just use the table that is needed based on the enabled module for a user.

[simolus3]

the actual Database class that creates the connection and the schema from the all possible modules will become quite cluttered with imports for DAOs and Tables

DAOs aren't strongly linked to database classes, this is more of a convenience feature (especially in modular buillds). You don't have to register your DAOs in the database, you can just instantiate them by passing your database instance to the DAO constructor and that'll work.

Afterwards, I wanted to just get the TableInfo that is generated by a module and migrate it into the initialised database.

That actually sounds possible :) If you have details of what went wrong that I could take a look at, I'm happy to do that.
But centrally managing the schema also works of course.

[Darius1323]

I will keep things simple for now after this dicussion since I will have to implement a robust logic for versioning the schema, to update it and remove then the tables when a user logs in and out from different accounts which can bring lots of headaches.

Thank you for your time and willing help.