sqflite

SQLite plugin for Flutter. Supports both iOS and Android.

• Support recursive inTransaction calls
• Automatic version managment
• Helpers for insert/query/update/delete queries
• DB operation executed in a background thread on iOS and Android

Getting Started

In your flutter project add the dependency:

dependencies:
  ...
  sqflite: any

For help getting started with Flutter, view the online documentation.

Usage example

Import sqflite.dart

import 'package:sqflite/sqflite.dart';

Raw SQL queries

Demo code to perform Raw SQL queries

// Get a location using path_provider
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "demo.db");

// Delete the database
deleteDatabase(path);

// open the database
Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  // When creating the db, create the table
  await db.execute(
      "CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)");
});

// Insert some records in a transaction
await database.inTransaction(() async {
  int id1 = await database.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print("inserted1: $id1");
  int id2 = await database.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ["another name", 12345678, 3.1416]);
  print("inserted2: $id2");
});

// Update some record
int count = await database.rawUpdate(
    'UPDATE Test SET name = ?, VALUE = ? WHERE name = ?',
    ["updated name", "9876", "some name"]);
print("updated: $count");

// Get the records
List<Map> list = await database.rawQuery('SELECT * FROM Test');
List<Map> expectedList = [
  {"name": "updated name", "id": 1, "value": 9876, "num": 456.789},
  {"name": "another name", "id": 2, "value": 12345678, "num": 3.1416}
];
print(list);
print(expectedList);
assert(const DeepCollectionEquality().equals(list, expectedList));

// Count the records
count = Sqflite
    .firstIntValue(await database.rawQuery("SELECT COUNT(*) FROM Test"));
assert(count == 2);

// Delete a record
count = await database
    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
assert(count == 1);

// Close the database
await database.close();

SQL helpers

Example using the helpers

final String tableTodo = "todo";
final String columnId = "_id";
final String columnTitle = "title";
final String columnDone = "done";

class Todo {
  int id;
  String title;
  bool done;

  Map toMap() {
    Map map = {columnTitle: title, columnDone: done == true ? 1 : 0};
    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }

  Todo();

  Todo.fromMap(Map map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
create table $tableTodo ( 
  $columnId integer primary key autoincrement, 
  $columnTitle text not null,
  $columnDone integer not null)
''');
    });
  }

  Future<Todo> insert(Todo todo) async {
    todo.id = await db.insert(tableTodo, todo.toMap());
    return todo;
  }

  Future<Todo> getTodo(int id) async {
    List<Map> maps = await db.query(tableTodo,
        columns: [columnId, columnDone, columnTitle],
        where: "$columnId = ?",
        whereArgs: [id]);
    if (maps.length > 0) {
      return new Todo.fromMap(maps.first);
    }
    return null;
  }

  Future<int> delete(int id) async {
    return await db.delete(tableTodo, where: "$columnId = ?", whereArgs: [id]);
  }

  Future<int> update(Todo todo) async {
    return await db.update(tableTodo, todo.toMap(),
        where: "$columnId = ?", whereArgs: [todo.id]);
  }

  Future close() async => db.close();
}

Batch support

To avoid ping-pong between dart and native code, you can use Batch:

batch = db.batch();
batch.insert("Test", {"name": "item"});
batch.update("Test", {"name": "new_item"}, where: "name = ?", whereArgs: ["item"]);
batch.delete("Test", where: "name = ?", whereArgs: ["item"]);
results = await batch.commit();

Getting the result for each operation has a cost (id for insertion and number of changes for update and delete), especially on Android where an extra SQL request is executed. If you don't care about the result and worry about performance in big batches, you can use

await batch.commit(noResult: true);

Supported SQLite types

No validity check is done on values yet so please avoid non supported types

INTEGER

• Dart type: int
• Supported values: from -2^63 to 2^63 - 1
• Android can accept any value

REAL

• Dart type: num

TEXT

• Dart type: String

BLOB

• Dart type: Uint8List
• Dart type List<int> is supported but not recommended (slow conversion)

Current issues

• Due to the way transaction works in SQLite (threads), concurrent read and write transaction are not supported. All calls are currently synchronized and transactions block are exclusive. I thought that a basic way to support concurrent access is to open a database multiple times but it only works on iOS as Android reuses the same database object. I also thought a native thread could be a potential future solution however on android accessing the database in another thread is blocked while in a transaction...
• Currently INTEGER are limited to -2^63 to 2^63 - 1 (although Android supports bigger ones)

More

• How to guide