Sqlite in flutter, how database assets work

Question

I am looking at this (https://github.com/tekartik/sqflite/blob/master/doc/opening_asset_db.md) for populating data that is already formatted and need for the app, for read functionality only.

So my understanding of creating an SQLite database when we already have all the information in an outside CSV file is to, create the class models in a .dart file in my app, such as

class User {

  int id;
  String _firstName;
  String _lastName;
  String _dob;

  User(this._firstName, this._lastName, this._dob);

  User.map(dynamic obj) {
    this._firstName = obj["firstname"];
    this._lastName = obj["lastname"];
    this._dob = obj["dob"];
  }

  String get firstName => _firstName;

  String get lastName => _lastName;

  String get dob => _dob;

  Map<String, dynamic> toMap() {
    var map = new Map<String, dynamic>();
    map["firstname"] = _firstName;
    map["lastname"] = _lastName;
    map["dob"] = _dob;
    return map;
  }
  void setUserId(int id) {
    this.id = id;
  }
}

then if I have a CSV file with all the user information inside of it (with values that correspond to the user class), could I be using the database asset to have that fill out the information and then call to it inside of the flutter app? I realize there are probably many ways to go about this, but What exactly is the .db file storing, and how is it formatted? Can i implement a .csv file into this .db file?

R. C. Howell · Accepted Answer · 2018-09-26 02:37:43Z

45

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

Create the necessary table (users.sql)

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

Create the sqlite database
```
sqlite3 database.db < users.sql
```

Insert the csv data

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

Put database.db into your assets and add that in pubspec.yaml.
```
flutter:
  # ...
  assets:
    - assets/database.db
```

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

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;

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

edited Sep 26, 2018 at 2:37

answered Jul 17, 2018 at 18:20

R. C. Howell

1,0811 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Mfreeman Over a year ago

You might need to edit ".import data.csv" to ".import data.csv users" . Syntax says it needs a table

Mfreeman Over a year ago

I am using android studio and when i use the .db file under assets, it says that .db isnt associated with any file type

R. C. Howell Over a year ago

That is correct. The file extension is not necessary. You could change .db to .sqlite3 (which I believe is common) or you could drop it altogether. That is a non-critical warning. You can see in the sqflite readme that the author uses ".db" as well.

Mfreeman Over a year ago

ok so now im getting an encoding error it says "file was loaded in the wrong encoding: 'utf-8'",

Bryon Nicoson Over a year ago

This answer is excellent. Note that FileSystemEntityType.NOT_FOUND has been deprecated. Use FileSystemEntityType.notFound.

|

Faiz · Accepted Answer · 2022-01-05 10:36:50Z

2

i use this function for copy database from assets folder

Future<void> _copyDatabase() async {
    final String databasePath = await getDatabasesPath();
    final String path = join(databasePath, 'dic.db');
    final File file = File(path);
    if (!file.existsSync()) {
      ByteData data = await rootBundle.load(join('assets', 'dic.db'));
      List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
      await file.writeAsBytes(bytes, flush: true);
      logger.i('database successfully copied to $path');
    } else {
      logger.i('database already exist');
    }
  }

answered Jan 5, 2022 at 10:36

Faiz

7,4266 gold badges27 silver badges38 bronze badges

1 Comment

SqueezeOJ Over a year ago

This was the easiest one for me. Thanks for sharing!

Yamin · Accepted Answer · 2018-07-17 15:17:05Z

1

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.

answered Jul 17, 2018 at 15:17

Yamin

3,0281 gold badge21 silver badges23 bronze badges

2 Comments

Mfreeman Over a year ago

Ok so what i did was do a mysql dump and used that .db as a document asset, how would i query against that file? Not to list it but for it to show up in the terminal

Yamin Over a year ago

R. C. Howell's answer is complete for using SQL and CSV file. But I recommend using JSON using key-value pairs. If you're interested in using that tell me to edit my answer and add JSON way.

live-love · Accepted Answer · 2020-04-07 16:42:32Z

1

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

answered Apr 7, 2020 at 16:42

live-love

53.2k27 gold badges257 silver badges220 bronze badges

1 Comment

Taba Over a year ago

Is it possible to update that db? Im using the same method to open the db but when I update it, no error occurs but data doesnt change... any idea?

Collectives™ on Stack Overflow

Sqlite in flutter, how database assets work

4 Answers 4

10 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related