How do I prevent the conversion of my STRING datatype into INT in sqflite? (flutter)

Question

onCreate: (db, version) {
        // Run the CREATE TABLE statement on the database.
        return db.execute(
          'CREATE TABLE favourites(id STRING PRIMARY KEY, stop STRING, stop_name STRING, bus STRING)',
        );
        //id shall consist of both stop and bus numbers
      },

This is a segment of my code I used to create the table.

Future<List<Favourite>> getFavourites() async {
    // Get a reference to the database.
    final db = await database();

    // Query the table for all The Dogs.
    final List<Map<String, dynamic>> maps = await db.query('favourites');
    print('maps line 165: $maps');
    // Convert the List<Map<String, dynamic> into a List<Dog>.
    return List.generate(maps.length, (i) {
      return Favourite(
        id: maps[i]['id'],
        stop: maps[i]['stop'],
        stopName: maps[i]['stopName'],
        bus: maps[i]['bus'],
      );
    });
  }

This function above is what I used to retrieve my data. However I got this error instead. "Unhandled Exception: type 'int' is not a subtype of type 'String'". This means that the numbers that I had inserted into the table as String were converted to int. For example, stop = "09038" would be converted to 9038 as an int.

All my objects were in String.

String id = '0';
String stop = '0';
String stopName = '0';
String bus = '0';

Any solution to this?

Answer 1

There is no STRING data type in SQLite but it is allowed to be used because SQLite allows anything to be used as a data type.

When you define a column as STRING it is actually considered to have NUMERIC affinity as it is described in Determination Of Column Affinity.

From Type Affinity:

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively.....

See a simplified demo.

All you have to do is define the columns that you want to behave as strings with the proper data type which is TEXT:

CREATE TABLE favourites(
  id TEXT PRIMARY KEY, 
  stop TEXT, 
  stop_name TEXT, 
  bus TEXT
);