Sqlite in flutter, how database assets work

First off, you will need to construct a sqlite database from your csv. This can be done in the following way:

1. Create the necessary table (users.sql)

   CREATE TABLE users(
      firstname TEXT NOT NULL,
      lastname TEXT NOT NULL,
      dob TEXT NOT NULL
   );
   

2. Create the sqlite database

   sqlite3 database.db < users.sql
   

3. Insert the csv data

   sqlite3 database.db
   .mode csv
   .import data.csv users
   

4. Put database.db into your assets and add that in pubspec.yaml.

   flutter:
     # ...
     assets:
       - assets/database.db
   

5. In your app, you'll have to copy the asset file into "documents". This is slightly complicated.

   // Construct a file path to copy database to
   Directory documentsDirectory = await getApplicationDocumentsDirectory();
   String path = join(documentsDirectory.path, "asset_database.db");
   
   // Only copy if the database doesn't exist
   if (FileSystemEntity.typeSync(path) == FileSystemEntityType.notFound){
     // Load database from asset and copy
     ByteData data = await rootBundle.load(join('assets', 'database.db'));
     List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
   
     // Save copied asset to documents
     await new File(path).writeAsBytes(bytes);
   }
   

6. Lastly, you can access the database like so.

   Directory appDocDir = await getApplicationDocumentsDirectory();
   String databasePath = join(appDocDir.path, 'asset_database.db');
   this.db = await openDatabase(databasePath);
   initialized = true;
   

7. Example query (this._initialize() is step 6)

   Future<List<Page>> search(String word, int parentId) async {
       if (!initialized) await this._initialize();
       String query = '''
         SELECT * FROM users
         LIMIT 25''';
       return await this.db.rawQuery(query);
   }
   

This .db file is used to save SQL data in it.so you could be able to save and get data with SQL commands. The plugin itself will parse the data in the .db file.

If you want to save your data as CSV and parse it in class, it's better to store data in the raw file (Look here for saving file) or use this plugin. Although, there are some other ways as save data as JSON file or save it in a key-value pair.

Add the asset in your file system at the root of your project. Create an assets folder and put your db file in it:

assets/examples.db

Specify the asset(s) in your pubspec.yaml in the flutter section

flutter:

  assets:

    - assets/example.db

Then you will need to open the database if it exists, or copy if it doesn't.

The link here shows the code to open/copy a pre-existing sqlite database:

https://github.com/tekartik/sqflite/blob/master/sqflite/doc/opening_asset_db.md