Flutter and SQLite: A Deep Dive into Local Data Persistence

Photo by Lia Trevarthen on Unsplash

Given that the Flutter framework stands out as one of the most widely adopted development frameworks for mobile applications, it becomes imperative for us to harness its functionalities effectively. There exist various practices that we employ when it comes to locally storing values. Notable among these are Shared Preferences, Hive (a No-SQL database), Dio (for Network Caching), and SQLite.

Within the realm of Flutter, an SQLite database denotes a localized, nimble, and self-contained relational database system seamlessly integrate into Flutter applications. As a C library, SQLite furnishes a Relational Database Management System (R.D.B.M.S.), and its widespread utilization in mobile app development owes to its inherent simplicity, efficiency, and portability.

Let me show you some of it’s usage for SQLite in Flutter.

dependencies:
  # SQLite Database
  sqflite: ^2.3.0

It is crucial to designate a storage path for the database file and articulate the table structure along with its respective column data types. This entails defining the schema and initializing it at the outset. By doing so, we establish a foundation for efficient data management, ensuring a structured and organized approach to handling information within the application. Have a look at the function below.

Future<void> inititialzeDB() async {
  database = openDatabase(
    "${await getDatabasesPath()}staff.db",
    onCreate: (db, version) {
      return db.execute('''
  CREATE TABLE IF NOT EXISTS staff(
    id INTEGER PRIMARY KEY,
    full_name TEXT,
    datetime INTEGER,
    propertyName TEXT,
    postalCode INTEGER,
    street TEXT,
    city TEXT,
    is_active BOOLEAN, 
  );
''');
    },
    version: 2,
  );

  String val_ = "";
  await getDatabasesPath().then((value) => val_ = value);

  print("value is $val_");
}

Here, the version specifies that if suppose the schema changes over time, we can increase the version in order to maintain a smooth migration flow while performing alterations. The path can be provided using the path_provider library.

Now we can finally perform CRUD operations on our table.

To Insert Data into the table :-

  final db = await database;
  Map<String, dynamic> jsonFromDatabase = {
    'id': 1,
    'full_name': 'Jane Smith',
    'datetime': DateTime.now().millisecondsSinceEpoch,
    'propertyName': 'Another Property',
    'postalCode': 54321,
    'street': '456 Oak Avenue',
    'city': 'Townsville',
    'is_active': 0,
  };
  Staff staff = Staff.fromJson(jsonFromDatabase);

  await db.insert(
    "staff",
    staff.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
  );

The Plot Twist

While SQLite is not inherently designed to handle hierarchical data in relational databases, I’ve discovered a clever workaround for storing objects that include child objects. While it might not be considered a best practice for extensive databases with voluminous data, this workaround proves effective for smaller datasets, especially when dealing with concise values. Let’s delve into this ingenious solution that, despite its limitations, offers an interesting approach to managing hierarchical structures within SQLite databases!

Assume your data model to be something like this example given below.

class ParentModel {
  String? id;
  String? name;
  String? model;

  ParentModel({this.id, this.name, this.model});

  ParentModel.fromJson(Map<String, dynamic> json) {
    id = json['id'];
    name = json['name'];
    model = json['model'] != null ? jsonDecode(Model.fromJson(json['model'])) : null;
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = <String, dynamic>{};
    data['id'] = id;
    data['name'] = name;
    if (model != null) {
      data['model'] = model!.toJson();
    }
    return data;
  }
}

class Model {
  String? id;
  String? firstName;
  String? lastName;

  Model({this.id, this.firstName, this.lastName});

  Model.fromJson(Map<String, dynamic> json) {
    id = json['id'];
    firstName = json['first_name'];
    lastName = json['last_name'];
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = <String, dynamic>{};
    data['id'] = id;
    data['first_name'] = firstName;
    data['last_name'] = lastName;
    return data;
  }
}

Storing data in SQLite for a ParentModel that includes a child Model parameter is straightforward. You can achieve this by defining the Model as TEXT in the database schema and inserting its values in the form of a JSON string.

https://media.giphy.com/media/vPk9gCdlEYPa0IQUbn/giphy.gif

While retrieving the values, just encode the string value (which is indeed a json) to get in the object. Let me give you an example.

var data = {
  "id": "parent_id_123",
  "name": "Parent Name",
  "model": {
    "id": "child_id_456",
    "first_name": "Child First Name",
    "last_name": "Child Last Name"
  }
}

// To endcode the json object. This string can be inserted.
String jsonString = jsonEncode(data);

//To decode the values
Map<Stirng, dynamic> json = jsonDecode(jsonString);
Staff staff = Staff.fromJson(map);

Storing object values in Relational Databases presents a valuable solution, offering a means to address and resolve various use cases for local data storage. While this approach proves effective, it’s important to acknowledge that alternative solutions may also exist, providing developers with a diverse set of options to cater to different requirements and scenarios.

In conclusion, I hope this blog has provided you with valuable insights into local data storage in Flutter applications, particularly the use of SQLite databases. As you embark on your Flutter development journey, may these strategies enhance your understanding and empower you to build robust, efficient, and user-friendly applications.

Your feedback and engagement are greatly appreciated. If you found this blog helpful or have any questions, feel free to share your thoughts in the comments section. Thank you for being a part of our community, and happy coding!