How to store JSON data into database table in flutter using SQFLite or Moor Library

Question

How do I insert the list of String in database in flutter app, I tried saving it in string datatype by encoding the json array into the string, but then the skills array requires explicit decoding every time, like below whenever I need the object back from database.

List<User> userResponse = await tempDatabase.allItems;
      jsonData = Result.fromJson({
        "name": userResponse[0].name,
        "skills": jsonDecode(userResponse[0].skills)
      });

This is my json response

{
  "result": [
    {
      "name":"Sidhant Rajora",
      "skills": [
        "C++",
        "Java",
        "Python",
        "React"
      ]
    },
    {
      "name":"Adity Rajora",
      "skills": [
        "C++",
        "Java",
        "Python"
      ]
    }
  ]
}

I have this kind of JSON response and the model PODO created by it is like

class UsersJson {
  List<Result> result;

  UsersJson({this.result});

  UsersJson.fromJson(Map<String, dynamic> json) {
    if (json['result'] != null) {
      result = new List<Result>();
      json['result'].forEach((v) {
        result.add(new Result.fromJson(v));
      });
    }
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    if (this.result != null) {
      data['result'] = this.result.map((v) => v.toJson()).toList();
    }
    return data;
  }
}

class Result {
  String name;
  int id;
  List<String> skills;

  Result({this.name, this.skills});

  Result.fromJson(Map<String, dynamic> json) {
    name = json['name'];
    skills = json['skills'].cast<String>();
  }

  Map<String, dynamic> toJson() {
    final Map<String, dynamic> data = new Map<String, dynamic>();
    data['name'] = this.name;
    data['skills'] = this.skills;
    return data;
  }
}

and now I am not sure about the approach should i take to insert the model into database and get it back from database. I have tried using the SQFLite library as well as Moor Library(https://moor.simonbinder.eu)

Answer 1

In moor you can use TypeConverter. Convert any type into a supported moor column type. For example you can stringify json and store it in a TextColumn. See the docs for how to. But with deeply nested json data it might get a bit tricky to encode and decode.

Answer 2

I have a similar problem I'm dealing with while learning Flutter, SQLite, Moor et al. and the only solution I can see working for me is joining two separate tables, one table contains the names and ids and the other one contains two kinds of ids, one unique identifier for each skill, and one "grouping" id that links a bunch of them to the first table. So for your case it should be something like this: