sqflite 1.1.7-dev.1

  • Readme
  • Changelog
  • Example
  • Installing
  • --

sqflite #

SQLite plugin for Flutter. Supports both iOS and Android.

  • Support transactions and batches
  • Automatic version managment during open
  • 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:

  sqflite: ^1.1.6

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

Usage example #

Import sqflite.dart

import 'package:sqflite/sqflite.dart';

Opening a database #

A SQLite database is a file in the file system identified by a path. If relative, this path is relative to the path obtained by getDatabasesPath(), which is the default database directory on Android and the documents directory on iOS.

var db = await openDatabase('my_db.db');

There is a basic migration mechanism to handle schema changes during opening.

Many applications use one database and would never need to close it (it will be closed when the application is terminated). If you want to release resources, you can close the database.

await db.close();

Raw SQL queries #

Demo code to perform Raw SQL queries

// Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, 'demo.db');

// Delete the database
await 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.transaction((txn) async {
  int id1 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print('inserted1: $id1');
  int id2 = await txn.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}
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();

Basic information on SQL here.

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<String, dynamic> toMap() {
    var map = <String, dynamic>{
      columnTitle: title,
      columnDone: done == true ? 1 : 0
    if (id != null) {
      map[columnId] = id;
    return map;


  Todo.fromMap(Map<String, dynamic> 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 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();

Read results #

Assuming the following read results:

List<Map<String, dynamic>> records = await db.query('my_table');

Resulting map items are read-only

// get the first record
Map<String, dynamic> mapRead = records.first;
// Update it in memory...this will throw an exception
mapRead['my_column'] = 1;
// Crash... `mapRead` is read-only

You need to create a new map if you want to modify it in memory:

// get the first record
Map<String, dynamic> map = Map<String, dynamic>.from(mapRead);
// Update it in memory now
map['my_column'] = 1;

Transaction #

Don't use the database but only use the Transaction object in a transaction to access the database

await database.transaction((txn) async {
  // Ok
  await txn.execute('CREATE TABLE Test1 (id INTEGER PRIMARY KEY)');
  // DON'T  use the database object in a transaction
  // this will deadlock!
  await database.execute('CREATE TABLE Test2 (id INTEGER PRIMARY KEY)');

A transaction is committed if the callback does not throw an error. If an error is thrown, the transaction is cancelled. So to rollback a transaction one way is to throw an exception.

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

Warning, during a transaction, the batch won't be committed until the transaction is committed

await database.transaction((txn) async {
  var batch = txn.batch();
  // ...
  // commit but the actual commit will happen when the transaction is committed
  // however the data is available in this transaction
  await batch.commit();
  //  ...

By default a batch stops as soon as it encounters an error (which typically reverts the uncommitted changes). You can ignore errors so that every successfull operation is ran and committed even if one operation fails:

await batch.commit(continueOnError: true);

Table and column names #

In general it is better to avoid using SQLite keywords for entity names. If any of the following name is used:


the helper will escape the name i.e.


will be equivalent to manually adding double-quote around the table name (confusingly here named table)

db.rawQuery('SELECT * FROM "table"');

However in any other raw statement (including orderBy, where, groupBy), make sure to escape the name properly using double quote. For example see below where the column name group is not escaped in the columns argument, but is escaped in the where argument.

db.query('table', columns: ['group'], where: '"group" = ?', whereArgs: ['my_group']);

Supported SQLite types #

No validity check is done on values yet so please avoid non supported types https://www.sqlite.org/datatype3.html

DateTime is not a supported SQLite type. Personally I store them as int (millisSinceEpoch) or string (iso8601)

bool is not a supported SQLite type. Use INTEGER and 0 and 1 values.

More information on supported types here.


  • Dart type: int
  • Supported values: from -2^63 to 2^63 - 1


  • Dart type: num


  • Dart type: String


  • 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 #

1.1.7-dev.1 #

  • Bump flutter/dart dependency version (1.9.1/2.5.0)
  • Fix hot and warm restart for opened databases

1.1.6+5 #

  • Open database in a background thread on Android.
  • Prevent database deletion on Android when opening a corrupted database in read-only.
  • Fix hot restart ROLLBACK warning
  • Fix indexed parameter binding on iOS

1.1.5 #

  • Add databaseExists as a top level function
  • handle relative path in databaseExists and deleteDatabase
  • Supports hot-restart while in a transaction on iOS and Android by recovering the database from the native world and executing ROLLBACK to prevent SQLITE_BUSY error
  • If in a transaction, execute ROLLBACK before closing to prevent SQLITE_BUSY error

1.1.4 #

  • Make all db operation happen in a separate thread on iOS

1.1.3 #

  • Fix deadlock issue on iOS when using isolates

1.1.2 #

  • Sqflite now uses a thread handler with a background thread priority by default on Android

1.1.1 #

  • Use mixin and extract non flutter code into sqlite_api.dart
  • Deprecate SqfliteOptions which is only used internally

1.1.0 #

  • Breaking change. Migrate from the deprecated original Android Support Library to AndroidX. This shouldn't result in any functional changes, but it requires any Android apps using this plugin to also migrate if they're using the original support library.

    You might say thay version should be bumped to 2.0.0, however it is just a tooling issue, code is not changed. This is a copy of the changes made in the flutter plugins

1.0.0 #

  • Upgrade 0.13.0 version as 1.0.0
  • Remove deprecated API (applyBatch, apply)

0.13.0 #

  • Add support for continueOrError for batches

0.12.0 #

  • iOS objective C prefix added to prevent conflict
  • on iOS create the directory of the database if it does not exist

0.11.2 #

  • add Database.isOpen which becomes false once the database is closed

0.11.1 #

  • add Sqlflite.hex to allow querying on blob fields

0.11.0 #

  • add getDatabasesPath to use as the base location to create a database
  • Warning: database are now single instance by default (based on path), to use the old behavior use singleInstance = false when opening a database
  • dart2 stable support

0.10.0 #

  • Preparing for 1.0
  • Remove deprecated methods (re-entrant transactions)
  • Add Transaction.batch
  • Show developer warning to prevent deadlock

0.9.0 #

  • Support for in-memory database (:memory: path)
  • Support for single instance
  • new database factory for handling the new options

0.8.9 #

  • Upgrade to sdk 27

0.8.8 #

  • Allow testing for constraint exception

0.8.6 #

  • better sql error report
  • catch android native errors
  • no longer print an error when deleting a database fails

0.8.4 #

  • Add read-only support using openReadOnlyDatabase

0.8.3 #

  • Allow running a batch during a transaction using Transaction.applyBatch
  • Restore Batch.commit to use outside a transaction

0.8.2 #

  • Although already in a transaction, allow creating nested transactions during open

0.8.1 #

  • New Transaction mechanism not using Zone (old one still supported for now)
  • Start using Batch.apply instead of Batch.commit
  • Deprecate Database.inTransaction and Database.synchronized so that Zones are not used anymore

0.7.1 #

  • add Batch.query, Batch.rawQuery and Batch.execute
  • pack query result as colums/rows instead of List

0.7.0 #

  • Add support for --preview-dart-2

0.6.2+1 #

  • Add longer description to pubspec.yaml

0.6.2 #

  • Fix travis warning

0.6.1 #

  • Add Flutter SDK constraint to pubspec.yaml

0.6.0 #

  • add support for onConfigure to allow for database configuration

0.5.0 #

  • Escape table and column name when needed in insert/update/query/delete
  • Export ConflictAlgorithm, escapeName, unescapeName in new sql.dart

0.4.0 #

  • Add support for Batch (insert/update/delete)

0.3.1 #

  • Remove temp concurrency experiment

0.3.0 #


  • Breaking change. Upgraded to Gradle 4.1 and Android Studio Gradle plugin 3.0.1. Older Flutter projects need to upgrade their Gradle setup as well in order to use this version of the plugin. Instructions can be found here.

0.2.4 #

  • Dependency on synchronized updated to >=1.1.0

0.2.3 #

  • Make Android sends the reponse in the same thread then the caller to prevent unexpected behavior when an error occured

0.2.2 #

  • Fix unchecked warning on Android

0.2.0 #

  • Use NSOperationQueue for all db operation on iOS
  • Use ThreadHandler for all db operation on Android

0.0.3 #

  • Add exception handling

0.0.2 #

  • Add sqlite helpers based on Razvan Lung suggestions

0.0.1 #

  • Initial experimentation


import 'dart:async';

import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_example/batch_test_page.dart';
import 'package:sqflite_example/deprecated_test_page.dart';
import 'package:sqflite_example/exception_test_page.dart';
import 'package:sqflite_example/exp_test_page.dart';
import 'package:sqflite_example/manual_test_page.dart';
import 'package:sqflite_example/src/dev_utils.dart';

import 'model/main_item.dart';
import 'open_test_page.dart';
import 'raw_test_page.dart';
import 'slow_test_page.dart';
import 'src/main_item_widget.dart';
import 'todo_test_page.dart';
import 'type_test_page.dart';

void main() {

class MyApp extends StatefulWidget {
  // This widget is the root of your application.

  _MyAppState createState() => _MyAppState();

const String testRawRoute = "/test/simple";
const String testOpenRoute = "/test/open";
const String testSlowRoute = "/test/slow";
const String testTypeRoute = "/test/type";
const String testBatchRoute = "/test/batch";
const String testTodoRoute = "/test/todo";
const String testExceptionRoute = "/test/exception";
const String testManualRoute = "/test/manual";
const String testExpRoute = "/test/exp";
const String testDeprecatedRoute = "/test/deprecated";

class _MyAppState extends State<MyApp> {
  var routes = <String, WidgetBuilder>{
    '/test': (BuildContext context) => MyHomePage(),
    testRawRoute: (BuildContext context) => RawTestPage(),
    testOpenRoute: (BuildContext context) => OpenTestPage(),
    testSlowRoute: (BuildContext context) => SlowTestPage(),
    testTodoRoute: (BuildContext context) => TodoTestPage(),
    testTypeRoute: (BuildContext context) => TypeTestPage(),
    testManualRoute: (BuildContext context) => ManualTestPage(),
    testBatchRoute: (BuildContext context) => BatchTestPage(),
    testExceptionRoute: (BuildContext context) => ExceptionTestPage(),
    testExpRoute: (BuildContext context) => ExpTestPage(),
    testDeprecatedRoute: (BuildContext context) => DeprecatedTestPage(),

  Widget build(BuildContext context) {
    return MaterialApp(
        title: 'Sqflite Demo',
        theme: ThemeData(
          // This is the theme of your application.
          // Try running your application with "flutter run". You'll see
          // the application has a blue toolbar. Then, without quitting
          // the app, try changing the primarySwatch below to Colors.green
          // and then invoke "hot reload" (press "r" in the console where
          // you ran "flutter run", or press Run > Hot Reload App in IntelliJ).
          // Notice that the counter didn't reset back to zero -- the application
          // is not restarted.
          primarySwatch: Colors.blue,
        home: MyHomePage(title: 'Sqflite Demo Home Page'),
        routes: routes);

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key) {
        MainItem("Raw tests", "Raw SQLite operations", route: testRawRoute));
    items.add(MainItem("Open tests", "Open onCreate/onUpgrade/onDowngrade",
        route: testOpenRoute));
    items.add(MainItem("Type tests", "Test value types", route: testTypeRoute));
    items.add(MainItem("Batch tests", "Test batch operations",
        route: testBatchRoute));
        MainItem("Slow tests", "Lengthy operations", route: testSlowRoute));
        "Todo database example", "Simple Todo-like database usage example",
        route: testTodoRoute));
    items.add(MainItem("Exp tests", "Experimental and various tests",
        route: testExpRoute));
    items.add(MainItem("Exception tests", "Tests that trigger exceptions",
        route: testExceptionRoute));
    items.add(MainItem("Manual tests", "Tests that requires manual execution",
        route: testManualRoute));
    items.add(MainItem("Deprecated test",
        "Keeping some old tests for deprecated functionalities",
        route: testDeprecatedRoute));

    // Uncomment to view all logs

  final List<MainItem> items = [];
  final String title;

  _MyHomePageState createState() => _MyHomePageState();

String _debugAutoStartRouteName;

String get debugAutoStartRouteName => _debugAutoStartRouteName;

/// Deprecated to avoid calls
set debugAutoStartRouteName(String routeName) =>
    _debugAutoStartRouteName = routeName;

class _MyHomePageState extends State<MyHomePage> {
  String _platformVersion = 'Unknown';

  int get _itemCount => widget.items.length;

  void initState() {

  // Platform messages are asynchronous, so we initialize in an async method.
  Future initPlatformState() async {
    String platformVersion;
    // Platform messages may fail, so we use a try/catch PlatformException.
    try {
      platformVersion = await Sqflite.platformVersion;
    } on PlatformException {
      platformVersion = "Failed to get platform version";

    // If the widget was removed from the tree while the asynchronous platform
    // message was in flight, we want to discard the reply rather than calling
    // setState to update our non-existent appearance.
    if (!mounted) return;

    setState(() {
      _platformVersion = platformVersion;

    print("running on: " + _platformVersion);

    // Use it to auto start a test page
    if (debugAutoStartRouteName != null) {
      // only once

      // await Navigator.of(context).pushNamed(testExpRoute);
      // await Navigator.of(context).pushNamed(testRawRoute);
      var future = Navigator.of(context).pushNamed(debugAutoStartRouteName);
      // ignore: deprecated_member_use_from_same_package
      debugAutoStartRouteName = null;
      await future;
      // await Navigator.of(context).pushNamed(testExceptionRoute);

  Widget build(BuildContext context) {
    return Scaffold(
        appBar: AppBar(
              Center(child: Text('Sqflite demo', textAlign: TextAlign.center)),
            ListView.builder(itemBuilder: _itemBuilder, itemCount: _itemCount));

  //new Center(child: new Text('Running on: $_platformVersion\n')),

  Widget _itemBuilder(BuildContext context, int index) {
    return MainItemWidget(widget.items[index], (MainItem item) {

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:

  sqflite: ^1.1.7-dev.1

2. Install it

You can install packages from the command line:

with pub:

$ pub get

with Flutter:

$ flutter pub get

Alternatively, your editor might support pub get or flutter pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:

import 'package:sqflite/sqflite.dart';
Awaiting analysis to complete.