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);
   }