SQLite - wurzelsand/flutter-memos GitHub Wiki

SQLite

My SQLite memos

sqflite

Save a table with SQLite

Create a class that is suitable for storing your data, here it is the class DataPair. Map<String, Object?> is the format for exchange with sqflite:

class DataPair {
  DataPair({this.id, required this.first, required this.second});

  int? id;
  String first;
  String second;

  Map<String, Object?> toMap() {
    return {
      'id': id,
      'first': first,
      'second': second,
    };
  }
}

I use Provider with ChangeNotifier and notifyListeners to synchronize the status of the database:

class MyDatabaseProvider with ChangeNotifier {

  late Database _sqlDatabase;

  Future<void> get initialized async {...}

  Future<List<DataPair>> getAll() async {...}

  Future<void> add(DataPair dataPair) async {
    ...
    notifyListeners();
  }
  ...
}

Determine the location for the application data and create a subfolder for the database:

import 'package:path/path.dart' as p;
import 'dart:io' as io;

io.Directory appDirectory = await getApplicationDocumentsDirectory();
io.Directory databaseDirectory = io.Directory(p.join(appDirectory.path, 'name_of_subdirectory'));

// Create directory if not exists:
databaseDirectory.createSync();

String databasePath = p.join(databaseDirectory.path, 'my_database.sql');
const tableName = 'vocabularies';
Database sqlDatabase = await openDatabase(
  databasePath,
  onCreate: (db, version) {
    return db.execute(
      'CREATE TABLE $tableName (id INTEGER PRIMARY KEY AUTOINCREMENT, first TEXT, second TEXT)',
    );
  },
  version: 1,
);
List<DataPair> table = await ...
...

Insert data:

int id = await _sqlDatabase.insert(
  tableName,
  dataPair.toMap(),
  conflictAlgorithm: ConflictAlgorithm.replace,
);
notifyListeners();

Get all data:

List<Map<String, Object?>> jsonMap = await _sqlDatabase.query(tableName);
return [
  for (final {
        'id': id as int,
        'first': first as String,
        'second': second as String,
      } in jsonMap)
    DataPair(id: id, first: first, second: second)
];

Get data with id = 42 from List<DataPair>:

final rows =
    await _sqlDatabase.query(tableName, where: 'id = ?', whereArgs: [42]);
final jsonMap = rows.firstOrNull;
switch (jsonMap) {
  case {'id': int id, 'first': String first, 'second': String second}:
    return DataPair(id: id, first: first, second: second);
  default:
    return null;
}

Remove data with id = 42 from both database and List<DataPair>:

await sqlDatabase.delete(tableName, where: 'id = ?', whereArgs: [42]);
notifyListeners();

Replace the data in the row with id = 42:

DataPair dataPair = DataPair(42, 'good', 'gut');

if (dataPair.id == null) {
  return;
}
await sqlDatabase.update(
  tableName,
  dataPair.toMap(),
  where: 'id = ?',
  whereArgs: [dataPair.id],
);
notifyListeners();

Complete: my_sqlite

⚠️ **GitHub.com Fallback** ⚠️