Flutter SQFLITE:
- SQFlite is a plugin used in flutter to perform database operations in flutter for both Andoid and IOS apps. using sqflite we are performing CRUD operations. In flutter we are using helpers for insert/query/update/delete queries. DB operation are executed in a background on iOS and Android. sqflite automatically updates version at the time of open. sqflite also supports to perform transactions and batch operations.
Adding sqflite dependencies in flutter:
To use sqflite in flutter, We have to add its dependencies package first in pubspec.yaml file:
dependencies:
sqflite: ^0.12.2+1
Install sqflite package from terminal Editor:
You can install packages from the command line with Flutter by using below command:
$ flutter packages get
Importing sqflite in flutter:
To use sqflite in flutters Dart code we have to import into the dart code for coding. without importing sqflite if we use it in coding then it shows error or exception that is package not found error.
In Dart code use below code to import the package.
import ‘package:sqflite/sqflite.dart’;
Below is a example sample code to perform database CRUD operations:
// 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();
Below example code used for SQL 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();
}
Transaction:
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)”);
});
Batch support:
To perform batch operations 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, 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);
To perform 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();
// …
});
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”)
It will be equivalent to manually adding double-quote around the table name (confusingly here named table).
db.rawQuery(‘SELECT * FROM “table”‘);
Using the 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”]);
Complete Example of SQFLITE given Below:
example/lib/main.dart
import ‘dart:async’;
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(MyApp());
}
class MyApp extends StatefulWidget {
// This widget is the root of your application.
@override
_MyAppState createState() => _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) => MyHomePage(),
testRawRoute: (BuildContext context) => SimpleTestPage(),
testOpenRoute: (BuildContext context) => OpenTestPage(),
testSlowRoute: (BuildContext context) => SlowTestPage(),
testTodoRoute: (BuildContext context) => TodoTestPage(),
testThreadRoute: (BuildContext context) => TypeTestPage(),
testExceptionRoute: (BuildContext context) => ExceptionTestPage(),
testExpRoute: (BuildContext context) => ExpTestPage(),
testDeprecatedRoute: (BuildContext context) => DeprecatedTestPage(),
};
@override
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) {
items.add(
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: testThreadRoute));
items.add(
MainItem(“Slow tests”, “Lengthy operations”, route: testSlowRoute));
items.add(MainItem(
“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(“Deprecated test”,
“Keeping some old tests for deprecated functionalities”,
route: testDeprecatedRoute));
// Uncomment to view all logs
//Sqflite.devSetDebugModeOn(true);
}
final List<MainItem> items = [];
final String title;
@override
_MyHomePageState createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
String _platformVersion = ‘Unknown’;
int get _itemCount => widget.items.length;
@override
void initState() {
super.initState();
initPlatformState();
}
// 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);
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title:
Center(child: Text(‘Sqflite demo’, textAlign: TextAlign.center)),
),
body:
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) {
Navigator.of(context).pushNamed(item.route);
});
}
}