SQLite plugin for Flutter. Supports both iOS and Android.
In your flutter project add the dependency:
dependencies:
...
sqflite: any
For help getting started with Flutter, view the online documentation.
Import sqflite.dart
import 'package:sqflite/sqflite.dart';
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}
];
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();
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();
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 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();
}
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)");
});
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 commited until the transaction is commited
await database.transaction((txn) async {
var batch = txn.batch();
// ...
// commit but the actual commit will happen when the transaction is commited
// however the data is available in this transaction
await batch.commit();
// ...
});
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"]);
No validity check is done on values yet so please avoid non supported types. DateTime is not a supported SQL type (https://www.sqlite.org/datatype3.html). Personally I store them as int (millisSinceEpoch) or string (iso8601)
int
num
String
Uint8List
List<int>
is supported but not recommended (slow conversion)Database.isOpen
which becomes false once the database is closedSqlflite.hex
to allow querying on blob fieldsgetDatabasesPath
to use as the base location to create a databasepath
), to use the
old behavior use singleInstance = false
when opening a databaseTransaction.batch
:memory:
path)openReadOnlyDatabase
Transaction.applyBatch
Batch.commit
to use outside a transactionTransaction
mechanism not using Zone (old one still supported for now)Batch.apply
instead of Batch.commit
Database.inTransaction
and Database.synchronized
so that Zones are not used anymoreBatch.query
, Batch.rawQuery
and Batch.execute
--preview-dart-2
onConfigure
to allow for database configuration2018/01/04
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 'package:sqflite_example/deprecated_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";
const String testDeprecatedRoute = "/test/deprecated";
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(),
testDeprecatedRoute: (BuildContext context) => new DeprecatedTestPage(),
};
@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));
items.add(new MainItem("Deprecated test",
"Keeping some old tests for deprecated functionalities",
route: testDeprecatedRoute));
// 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';
int 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);
});
}
}
Add this to your package's pubspec.yaml file:
dependencies:
sqflite: ^0.11.2+1
You can install packages from the command line:
with Flutter:
$ flutter packages get
Alternatively, your editor might support flutter packages get
.
Check the docs for your editor to learn more.
Now in your Dart code, you can use:
import 'package:sqflite/sqflite.dart';
Version | Uploaded | Documentation | Archive |
---|---|---|---|
1.1.0 | Feb 3, 2019 |
|
|
1.0.0 | Jan 8, 2019 |
|
|
0.13.0+1 | Dec 21, 2018 |
|
|
0.13.0 | Dec 5, 2018 |
|
|
0.12.2+1 | Oct 11, 2018 |
|
|
0.12.2 | Oct 11, 2018 |
|
|
0.12.1 | Sep 20, 2018 |
|
|
0.12.0 | Sep 14, 2018 |
|
|
0.11.2+4 | Sep 13, 2018 |
|
|
0.11.2+3 | Sep 13, 2018 |
|
|
Popularity:
Describes how popular the package is relative to other packages.
[more]
|
100
|
Health:
Code health derived from static analysis.
[more]
|
98
|
Maintenance:
Reflects how tidy and up-to-date the package is.
[more]
|
100
|
Overall:
Weighted score of the above.
[more]
|
99
|
We analyzed this package on Feb 4, 2019, and provided a score, details, and suggestions below. Analysis was completed with status completed using:
Detected platforms: Flutter
References Flutter, and has no conflicting libraries.
Fix lib/src/sqflite_impl.dart
. (-1 points)
Analysis of lib/src/sqflite_impl.dart
reported 2 hints:
line 5 col 8: Don't import implementation files from another package.
line 14 col 47: Avoid using braces in interpolation when not needed.
Fix lib/src/database.dart
. (-0.50 points)
Analysis of lib/src/database.dart
reported 1 hint:
line 462 col 13: Avoid using braces in interpolation when not needed.
Fix lib/src/exception.dart
. (-0.50 points)
Analysis of lib/src/exception.dart
reported 1 hint:
line 79 col 79: Avoid using braces in interpolation when not needed.
Package | Constraint | Resolved | Available |
---|---|---|---|
Direct dependencies | |||
Dart SDK | >=2.0.0-dev.35 <3.0.0 | ||
flutter | 0.0.0 | ||
path | >=1.5.1 <3.0.0 | 1.6.2 | |
synchronized | >=1.5.1 <3.0.0 | 1.5.3+2 | |
Transitive dependencies | |||
collection | 1.14.11 | ||
meta | 1.1.6 | 1.1.7 | |
sky_engine | 0.0.99 | ||
typed_data | 1.1.6 | ||
vector_math | 2.0.8 | ||
Dev dependencies | |||
flutter_test |