SQFlite is a plugin used in flutter to perform database operations for both Andoid and IOS apps
DB operations are executed in background on iOS and Android
sqflite automatically updates version at the time of open
Sqflite supports crud operations, transactions and batch operations
Check Flutter installation to setup Flutter
Use flutter create command to create a Flutter project (here local_db_app :
flutter create local_db_app Add sqflite package to pubspec.yaml
dependencies:
sqflite:
flutter:
sdk: flutter
Run following command to add dependency
flutter pub getImport the package into dart code
import ‘package:sqflite/sqflite.dart’;Example 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’);
// Count the records
count = Sqflite.firstIntValue(await database.rawQuery(“SELECT COUNT(*) FROM Test”));
// Delete a record
count = await database.rawDelete(‘DELETE FROM Test WHERE name = ?’, [‘another name’]);
// Close the database
await database.close();Use the Transaction object in a transaction to access the database
await database.transaction((txn) async {
await txn.execute(“CREATE TABLE Test1 (id INTEGER PRIMARY KEY)”);
// DON’T use the database object in a transaction to avoid deadlock
await database.execute(“CREATE TABLE Test2 (id INTEGER PRIMARY KEY)”);
});This example app code displays and allows to create and delete contacts which are stored in database
Data structure for a Contact and conversions to and from String are declared in contact_model.dart
import 'dart:convert';
Contact contactFromJson(String str) {
final jsonData = json.decode(str);
return Contact.fromMap(jsonData);
}
String contactToJson(Contact data) {
final dyn = data.toMap();
return json.encode(dyn);
}
class Contact {
int id;
String name;
String number;
Contact({
this.id,
this.name,
this.number,
});
factory Contact.fromMap(Map<String, dynamic> json) => new Contact(
id: json["id"],
name: json["name"],
number: json["number"],
);
Map<String, dynamic> toMap() => {
"id": id,
"name": name,
"number": number,
};
} database.dart declares class DBProvider which contains functions to interact with database
import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'contact_model.dart';
import 'package:sqflite/sqflite.dart';
class DBProvider {
DBProvider._();
static final DBProvider db = DBProvider._();
Database _database;
Future<Database> get database async {
if (_database != null) return _database;
// if _database is null, it is instantiated
_database = await initDB();
return _database;
}
initDB() async {
Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, "TestDB.db");
return await openDatabase(path, version: 1, onOpen: (db) {},
onCreate: (Database db, int version) async {
await db.execute("CREATE TABLE Contact ("
"id INTEGER PRIMARY KEY,"
"name TEXT,"
"number TEXT,"
")");
});
}
insertContact(Contact newContact) async {
final db = await database;
//get max id of table and increment to get id for insertion
var table = await db.rawQuery("SELECT MAX(id)+1 as id FROM Contact");
int id = table.first["id"];
var raw = await db.rawInsert(
"INSERT Into Contact (id,name,number)"
" VALUES (?,?,?)",
[id, newContact.name, newContact.number]);
return raw;
}
updateContact(Contact newContact) async {
final db = await database;
var res = await db.update("Contact", newContact.toMap(),
where: "id = ?", whereArgs: [newContact.id]);
return res;
}
getContact(int id) async {
final db = await database;
var res = await db.query("Contact", where: "id = ?", whereArgs: [id]);
return res.isNotEmpty ? Contact.fromMap(res.first) : null;
}
Future<List<Contact>> getAllContacts() async {
final db = await database;
var res = await db.query("Contact");
List<Contact> list =
res.isNotEmpty ? res.map((c) => Contact.fromMap(c)).toList() : [];
return list;
}
deleteContact(int id) async {
final db = await database;
return db.delete("Contact", where: "id = ?", whereArgs: [id]);
}
deleteAll() async {
final db = await database;
db.rawDelete("Delete * from Contact");
}
}Main app code with a stateful widget to display contacts present in db which also allows to add and remove contacts
import 'package:flutter/material.dart';
import 'contact_model.dart';
import 'database.dart';
import 'dart:math' as math;
import 'dart:async';
void main() => runApp(MaterialApp(home: NcSqliteApp()));
class NcSqliteApp extends StatefulWidget {
@override
_NcSqliteAppState createState() => _NcSqliteAppState();
}
class _NcSqliteAppState extends State<NcSqliteApp> {
bool isEdit = false;
List<Contact> testContacts = [
Contact(name: "Takzia", number: "8941722312"),
Contact(name: "Grazi", number: "8217391734"),
Contact(name: "Hyung", number: "8721461846"),
Contact(name: "Abibeide", number: "9326724233"),
];
Future _openAddUserDialog(isEdit, id) async {
Contact contact = null;
if( id!=null ) {
contact = await DBProvider.db.getContact(id);
}
showDialog(
context: context,
builder: (BuildContext context) =>
new AddUserDialog().buildAboutDialog(context, this, isEdit, contact),
);
setState(() {});
}
@override
Widget build(BuildContext context) {
return Scaffold(
// to avoid overflow during keyboard display
resizeToAvoidBottomInset: false,
resizeToAvoidBottomPadding: false,
appBar: AppBar(title: Text("Flutter SQLite Example")),
body: Column(
children: [
Container(
height:400,
child: FutureBuilder<List<Contact>>(
future: DBProvider.db.getAllContacts(),
builder: (BuildContext context, AsyncSnapshot<List<Contact>> snapshot) {
if (snapshot.hasData) {
return ListView.builder(
shrinkWrap: true,
physics: const AlwaysScrollableScrollPhysics(),
itemCount: snapshot.data.length,
itemBuilder: (BuildContext context, int index) {
Contact item = snapshot.data[index];
return Dismissible(
key: UniqueKey(),
background: Container(color: Colors.red),
onDismissed: (direction) {
DBProvider.db.deleteContact(item.id);
},
child: ListTile(
title: Text("${item.name} ${item.number}"),
leading: Text(item.id.toString()),
trailing: Transform.scale(
scale: 0.5,
child: IconButton(
onPressed: (){
_openAddUserDialog(true, item.id);
},
icon: Icon(Icons.edit),
),
),
),
);
},
);
}
else {
return Center(child: CircularProgressIndicator());
}
},
),
),
new GestureDetector(
onTap: () {
_openAddUserDialog(false, null);
},
child: new Container(
margin: EdgeInsets.fromLTRB(10.0, 0.0, 10.0, 0.0),
child: getBorderButton(
"Add", EdgeInsets.fromLTRB(0.0, 10.0, 0.0, 0.0)
),
),
),
],
),
floatingActionButton: FloatingActionButton(
child: Icon(Icons.add),
onPressed: () async {
Contact rnd = testContacts[math.Random().nextInt(testContacts.length)];
await DBProvider.db.insertContact(rnd);
setState(() {});
},
),
);
}
}
Widget getBorderButton(String buttonLabel, EdgeInsets margin) {
return new Container(
margin: margin,
padding: EdgeInsets.all(8.0),
alignment: Alignment.center,
decoration: new BoxDecoration(
border: Border.all(color: const Color(0xFF28324E)),
borderRadius: new BorderRadius.all(const Radius.circular(6.0)),
),
child: new Text(
buttonLabel,
style: new TextStyle(
color: const Color(0xFF28324E),
fontSize: 20.0,
fontWeight: FontWeight.w300,
letterSpacing: 0.3,
),
),
);
}
class AddUserDialog {
final teFirstName = TextEditingController();
final teNumber = TextEditingController();
Contact contact;
static const TextStyle linkStyle = const TextStyle(
color: Colors.blue,
decoration: TextDecoration.underline,
);
Widget buildAboutDialog(BuildContext context, _NcSqliteAppState appstate, bool isEdit, Contact contact) {
if (contact != null) {
this.contact=contact;
teFirstName.text = contact.name;
teNumber.text = contact.number;
}
return new AlertDialog(
title: new Text(isEdit ? 'Edit' : 'Add new User'),
content: new SingleChildScrollView(
child: new Column(
mainAxisSize: MainAxisSize.min,
crossAxisAlignment: CrossAxisAlignment.start,
children: <Widget>[
getTextField("Enter name", teFirstName),
getTextField("Enter number", teNumber),
new GestureDetector(
onTap: () {
addRecord(isEdit);
appstate.setState(() {});
Navigator.of(context).pop();
},
child: new Container(
margin: EdgeInsets.fromLTRB(10.0, 0.0, 10.0, 0.0),
child: getBorderButton(
isEdit?"Update":"Add", EdgeInsets.fromLTRB(0.0, 10.0, 0.0, 0.0)),
),
),
],
),
),
);
}
Widget getTextField(
String inputBoxName, TextEditingController inputBoxController) {
return new Padding(
padding: const EdgeInsets.all(5.0),
child: new TextFormField(
controller: inputBoxController,
decoration: new InputDecoration(
hintText: inputBoxName,
),
),
);
}
Future addRecord(bool isEdit) async {
Contact newContact = Contact(name: teFirstName.text, number: teNumber.text);
if(!isEdit)
await DBProvider.db.insertContact(newContact);
else {
newContact.id = contact.id;
await DBProvider.db.updateContact(newContact);
}
}
}Ensure a supported device is connected or emulator/simulator is started
Go to project directory
Use flutter run command to run
flutter runIt builds and runs app on an available android/ios device
