Flutter Question posted in
The official Flutter documentation can be found here.

Migrate Flutter Sqlite to Kotlin Room

I have a production app made with Flutter that I started as a side project, now it has couple hundred users and I want to migrate the codebase to Kotlin (yes Im going to rewrite all the code).

The problem I’m facing is with the database, I currently use Sqlite in Flutter and I want to use Room without losing any of the user data.

Is this posible? If not, can I still use the same Sqlite after the user update the new version of the app made with Kotlin?

Tags:

2

Answers


  1. 0

    You can retain all your data with no issues.
    Put your database file .db to the assets folder
    and build the database like this:

        Room.databaseBuilder(context,YourDb::class.java,"YourDBName")
.createFromAsset("assets/YourDBName.db")
.build()

    make sure that your model classes representing the entities match the columns and datatypes of your database file

    Login or Signup to reply.
  2. 0

    I currently use Sqlite in Flutter and I want to use Room without losing any of the user data. Is this posible?

    Yes (albeit potentially complex).

    Assuming that each existing user(device) has data unique to that user then you can effectively utilise that existing data.

    Basically you need to change the App to introduce @Entity annotated classes, one per existing table.

    • Each will have fields, which equate to columns as per the existing database. – The class names can either be as per the existing table names or you can utilise the tableName parameter of the @Entity annotation to specify the table name to be utilised.

    You should then create the @Database annotated class with the list of @Entity annotated classes provided via the entities parameter of the @Database annotation.

    At this stage you should successfully compile the project. The java(generated) directory would then include a subdirectory with a class that is the same name as the @Database annotated class but suffixed with _Impl.

    Within this class there will be a function/method called createAllTables. This will have an execSQL statement for creating each table (you can ignore the room_master table), and if any, a statement for the creation of indexes.

    This SQL is exactly the schema that Room EXPECTS. Room will not accept any difference.

    If the definition exactly matches the existing tables then in theory you could continue as such (database version number being the same). However, if there are any differences then you would need to convert(migrate) the tables.

    Migration from non-room compliant tables to room-compliant tables could be reached by following the following steps (assuming the number of columns per table remain unchanged)

    In the migration/conversion code per table:-

    1. rename the original table using the ALTER TABLE the_table RENAME TO the_table_renamed;
      1. the identifiers here are just descriptive
      2. the _renamed part could be whatever suits but it is probably best to be consistent.
    2. Using the SQL as copied from the generated java create the replacement table.
    3. Use an INSERT …. SELECT to copy the data from the renamed original table into the newly created replacement table.
      1. In it’s simplest form INSERT INTO the_table SELECT * FROM the_table_renamed;.
      2. Note that you may well have to use a more complex form explicitly naming the columns, e.g.:-
        1.if the columns are not defined in the same order, or if you need to convert data

        1. you had a column or columns that did not originally have a NOT NULL constraint but have the NOT NULL constraint, then you may need to convert the null into a non-null value (perhaps using the SQLite coalesce function)
    4. Optionally DROP the renamed original table e.g. DROP TABLE IF EXISTS the_table_renamed;
    5. If indexes are to be created then for each index:-
      1. Drop the index e.g. DROP INDEX IF EXISTS the_index;
      2. Create the INDEX using the SQL as copied from the generated java

    You may want to test that the above works before proceeding.

    You would very likely have to convert all the functions that access the database INSERTs, UPDATEs, DELETEs and QUERYs. Using either the equivalent convenience methods @Insert, @Update and @Delete. Noting:-

    1. the @Update and @Delete convenience methods on the @PrimaryKey (required for all Room tables) to identify the row that is to be affected, the methods have the advantage of return an Int the value being the number of affected rows.
    2. the @Insert convenience method returns the rowid of the inserted row (not necessarily the PrimaryKey value)
    3. You can use an @Query("the_respective_sql") to get around the shortfalls of the convenience methods. e.g. it is impossible to use the convenience @Update to change the value of the primary key, but this could be done utilising e.g. @Query("UPDATE the_table SET pkey=:newPkey WHERE pkey=:oldPkey")
      1. it should be noted that such uses do not return values like the convenience methods do.

    In regard to Queries and Relationships you typically utilise a POJO (class not annotated with @Entity) where a field for the parent is annotated with @Embedded and the child(ren) field (typically a List<the_respective_@entity_annotated_child_class>) annotated with @Relation annotation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search