sqflite 0.6.0

  • README.md
  • CHANGELOG.md
  • Example
  • Installing
  • Versions
  • 98

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

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:

"add","all","alter","and","as","autoincrement","between","case","check","collate","commit","constraint","create","default","deferrable","delete","distinct","drop","else","escape","except","exists","foreign","from","group","having","if","in","index","insert","intersect","into","is","isnull","join","limit","not","notnull","null","on","or","order","primary","references","select","set","table","then","to","transaction","union","unique","update","using","values","when","where"

the helper will escape the name i.e.

db.query("table")

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

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

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

2018/01/04

  • 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

example/lib/main.dart

import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_example/exp_test_page.dart';
import 'model/main_item.dart';
import 'open_test_page.dart';
import 'package:sqflite_example/exception_test_page.dart';
import 'raw_test_page.dart';
import 'slow_test_page.dart';
import 'src/main_item_widget.dart';
import 'type_test_page.dart';
import 'todo_test_page.dart';

void main() {
  runApp(new MyApp());
}

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

  @override
  _MyAppState createState() => new _MyAppState();
}

const String testRawRoute = "/test/simple";
const String testOpenRoute = "/test/open";
const String testSlowRoute = "/test/slow";
const String testThreadRoute = "/test/thread";
const String testTodoRoute = "/test/todo";
const String testExceptionRoute = "/test/exception";
const String testExpRoute = "/test/exp";

class _MyAppState extends State<MyApp> {
  var routes = <String, WidgetBuilder>{
    '/test': (BuildContext context) => new MyHomePage(),
    testRawRoute: (BuildContext context) => new SimpleTestPage(),
    testOpenRoute: (BuildContext context) => new OpenTestPage(),
    testSlowRoute: (BuildContext context) => new SlowTestPage(),
    testTodoRoute: (BuildContext context) => new TodoTestPage(),
    testThreadRoute: (BuildContext context) => new TypeTestPage(),
    testExceptionRoute: (BuildContext context) => new ExceptionTestPage(),
    testExpRoute: (BuildContext context) => new ExpTestPage(),
  };
  @override
  Widget build(BuildContext context) {
    return new MaterialApp(
        title: 'Sqflite Demo',
        theme: new 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: new MyHomePage(title: 'Sqflite Demo Home Page'),
        routes: routes);
  }
}

class MyHomePage extends StatefulWidget {
  final List<MainItem> items = [];

  MyHomePage({Key key, this.title}) : super(key: key) {
    items.add(new MainItem("Raw tests", "Raw SQLite operations",
        route: testRawRoute));
    items.add(new MainItem("Open tests", "Open onCreate/onUpgrade/onDowngrade",
        route: testOpenRoute));
    items.add(
        new MainItem("Type tests", "Test value types", route: testThreadRoute));
    items.add(
        new MainItem("Slow tests", "Lengthy operations", route: testSlowRoute));
    items.add(new MainItem(
        "Todo database example", "Simple Todo-like database usage example",
        route: testTodoRoute));
    items.add(new MainItem("Exp tests", "Experimental and various tests",
        route: testExpRoute));
    items.add(new MainItem("Exception tests", "Tests that trigger exceptions",
        route: testExceptionRoute));

    // Uncomment to view all logs
    //Sqflite.devSetDebugModeOn(true);
  }

  // This widget is the home page of your application. It is stateful,
  // meaning that it has a State object (defined below) that contains
  // fields that affect how it looks.

  // This class is the configuration for the state. It holds the
  // values (in this case the title) provided by the parent (in this
  // case the App widget) and used by the build method of the State.
  // Fields in a Widget subclass are always marked "final".

  final String title;

  @override
  _MyHomePageState createState() => new _MyHomePageState();
}

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

  get _itemCount => widget.items.length;

  @override
  initState() {
    super.initState();
    initPlatformState();
  }

  // Platform messages are asynchronous, so we initialize in an async method.
  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);
  }

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

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

  Widget _itemBuilder(BuildContext context, int index) {
    return new MainItemWidget(widget.items[index], (MainItem item) {
      Navigator.of(context).pushNamed(item.route);
    });
  }
}

1. Depend on it

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


dependencies:
  sqflite: "^0.6.0"

2. Install it

You can install packages from the command line:

with Flutter:


$ flutter packages get

Alternatively, your editor might support packages 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';
        
Version Uploaded Documentation Archive
0.6.0 Jan 22, 2018 Go to the documentation of sqflite 0.6.0 Download sqflite 0.6.0 archive
0.5.0 Jan 19, 2018 Go to the documentation of sqflite 0.5.0 Download sqflite 0.5.0 archive
0.4.0 Jan 9, 2018 Go to the documentation of sqflite 0.4.0 Download sqflite 0.4.0 archive
0.3.1 Jan 4, 2018 Go to the documentation of sqflite 0.3.1 Download sqflite 0.3.1 archive
0.3.0 Jan 4, 2018 Go to the documentation of sqflite 0.3.0 Download sqflite 0.3.0 archive
0.2.4 Oct 15, 2017 Go to the documentation of sqflite 0.2.4 Download sqflite 0.2.4 archive
0.2.3 Oct 10, 2017 Go to the documentation of sqflite 0.2.3 Download sqflite 0.2.3 archive
0.2.2 Aug 19, 2017 Go to the documentation of sqflite 0.2.2 Download sqflite 0.2.2 archive
0.2.1 Jul 7, 2017 Go to the documentation of sqflite 0.2.1 Download sqflite 0.2.1 archive
0.2.0 Jun 29, 2017 Go to the documentation of sqflite 0.2.0 Download sqflite 0.2.0 archive

All 11 versions...

Analysis

This feature is new.
We welcome feedback.
More details: scoring.

We analyzed this package, and provided a score, details, and suggestions below.

  • completed on Jan 22, 2018
  • Dart: 2.0.0-dev.15.0
  • pana: 0.10.0
  • Flutter: 0.0.20

Scores

Popularity:
Describes how popular the package is relative to other packages. [more]
98 / 100
Health:
Code health derived from static analysis. [more]
100 / 100
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
95 / 100
Overall score:
Weighted score of the above. [more]
98

Platforms

Detected platforms: Flutter

References Flutter, and has no conflicting libraries.

Suggestions

  • Use constrained dependencies.

    The pubspec.yaml contains 2 dependencies without version constraints. Specify version ranges for the following dependencies: flutter, synchronized.

  • The description is too short.

    Add more detail about the package, what it does and what is its target use case. Try to write at least 60 characters.

  • Package is pre-v1 release.

    While there is nothing inherently wrong with versions of 0.*.*, it usually means that the author is still experimenting with the general direction API.

Dependencies

Package Constraint Resolved Available
Direct dependencies
flutter 0.0.40
synchronized >=1.1.0 1.1.0
Transitive dependencies
async 1.13.3 2.0.3
charcode 1.1.1
collection 1.14.3 1.14.5
http 0.11.3+14 0.11.3+16
http_parser 3.1.1
meta 1.1.1 1.1.2
path 1.5.1
sky_engine 0.0.99
source_span 1.4.0
stack_trace 1.9.1
string_scanner 1.0.2
typed_data 1.1.4 1.1.5
vector_math 2.0.5
Dev dependencies
flutter_test
test any