Watch Video Tutorial
SQLite is a local database for storing structured data, so it can be called as a mini database. Most of the time we use SQLite Databases in Mobile Applications. To use SQLite in Flutter, we have an awesome plugin called SQFlite. To start with we need to add Dependency to our project’s pubspec.yaml file
Add Dependency
You can get it from here.
https://pub.dev/packages/sqflite
To Integrate SQFlite library in your project, Open the pubspec.yaml file…
dependencies:
// your other dependencies goes here
...
sqflite: anyLet’s create a simple database of employees
Create a new file named ‘employee.dart’ and the contents will look like this
class Employee {
int id;
String name;
Employee(this.id, this.name);
Map<String, dynamic> toMap() {
var map = <String, dynamic>{
'id': id,
'name': name,
};
return map;
}
Employee.fromMap(Map<String, dynamic> map) {
id = map['id'];
name = map['name'];
}
}Here each employee will have an ‘id’ and ‘name’.
Database Utils
We will write a database utility file where we will write our database utility functions.
Create a file named “DBHelper.dart” and copy the below code into it.
import 'dart:async';
import 'dart:io' as io;
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path_provider/path_provider.dart';
import 'employee.dart';
class DBHelper {
static Database _db;
static const String ID = 'id';
static const String NAME = 'name';
static const String TABLE = 'Employee';
static const String DB_NAME = 'employee1.db';
Future<Database> get db async {
if (_db != null) {
return _db;
}
_db = await initDb();
return _db;
}
initDb() async {
io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, DB_NAME);
var db = await openDatabase(path, version: 1, onCreate: _onCreate);
return db;
}
_onCreate(Database db, int version) async {
await db
.execute("CREATE TABLE $TABLE ($ID INTEGER PRIMARY KEY, $NAME TEXT)");
}
Future<Employee> save(Employee employee) async {
var dbClient = await db;
employee.id = await dbClient.insert(TABLE, employee.toMap());
return employee;
/*
await dbClient.transaction((txn) async {
var query = "INSERT INTO $TABLE ($NAME) VALUES ('" + employee.name + "')";
return await txn.rawInsert(query);
});
*/
}
Future<List<Employee>> getEmployees() async {
var dbClient = await db;
List<Map> maps = await dbClient.query(TABLE, columns: [ID, NAME]);
//List<Map> maps = await dbClient.rawQuery("SELECT * FROM $TABLE");
List<Employee> employees = [];
if (maps.length > 0) {
for (int i = 0; i < maps.length; i++) {
employees.add(Employee.fromMap(maps[i]));
}
}
return employees;
}
Future<int> delete(int id) async {
var dbClient = await db;
return await dbClient.delete(TABLE, where: '$ID = ?', whereArgs: [id]);
}
Future<int> update(Employee employee) async {
var dbClient = await db;
return await dbClient.update(TABLE, employee.toMap(),
where: '$ID = ?', whereArgs: [employee.id]);
}
Future close() async {
var dbClient = await db;
dbClient.close();
}
}Let’s see what the above code does…
initDb() async {
io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
String path = join(documentsDirectory.path, DB_NAME);
var db = await openDatabase(path, version: 1, onCreate: _onCreate);
return db;
}the initDb() function will create a database file in the App’s Documents directory
Future<Employee> save(Employee employee) async {
var dbClient = await db;
employee.id = await dbClient.insert(TABLE, employee.toMap());
return employee;
/*
await dbClient.transaction((txn) async {
var query = "INSERT INTO $TABLE ($NAME) VALUES ('" + employee.name + "')";
return await txn.rawInsert(query);
});
*/
}The save function will insert the employee data into the table in the database.
There are two ways to write queries.
await dbClient.transaction((txn) async {
var query = "INSERT INTO $TABLE ($NAME) VALUES ('" + employee.name + "')";
return await txn.rawInsert(query);
});if you want to write the raw query, you can use the above method.
Select
Future<List<Employee>> getEmployees() async {
var dbClient = await db;
List<Map> maps = await dbClient.query(TABLE, columns: [ID, NAME]);
//List<Map> maps = await dbClient.rawQuery("SELECT * FROM $TABLE");
List<Employee> employees = [];
if (maps.length > 0) {
for (int i = 0; i < maps.length; i++) {
employees.add(Employee.fromMap(maps[i]));
}
}
return employees;
}Update
Future<int> update(Employee employee) async {
var dbClient = await db;
return await dbClient.update(TABLE, employee.toMap(),
where: '$ID = ?', whereArgs: [employee.id]);
}Delete
Future<int> delete(int id) async {
var dbClient = await db;
return await dbClient.delete(TABLE, where: '$ID = ?', whereArgs: [id]);
}Now we will write a Screen where we will implement the above functions
import 'package:flutter/material.dart';
import 'employee.dart';
import 'dart:async';
import 'db_helper.dart';
class DBTestPage extends StatefulWidget {
final String title;
DBTestPage({Key key, this.title}) : super(key: key);
@override
State<StatefulWidget> createState() {
return _DBTestPageState();
}
}
class _DBTestPageState extends State<DBTestPage> {
//
Future<List<Employee>> employees;
TextEditingController controller = TextEditingController();
String name;
int curUserId;
final formKey = new GlobalKey<FormState>();
var dbHelper;
bool isUpdating;
@override
void initState() {
super.initState();
dbHelper = DBHelper();
isUpdating = false;
refreshList();
}
refreshList() {
setState(() {
employees = dbHelper.getEmployees();
});
}
clearName() {
controller.text = '';
}
validate() {
if (formKey.currentState.validate()) {
formKey.currentState.save();
if (isUpdating) {
Employee e = Employee(curUserId, name);
dbHelper.update(e);
setState(() {
isUpdating = false;
});
} else {
Employee e = Employee(null, name);
dbHelper.save(e);
}
clearName();
refreshList();
}
}
form() {
return Form(
key: formKey,
child: Padding(
padding: EdgeInsets.all(15.0),
child: Column(
mainAxisAlignment: MainAxisAlignment.center,
mainAxisSize: MainAxisSize.min,
verticalDirection: VerticalDirection.down,
children: <Widget>[
TextFormField(
controller: controller,
keyboardType: TextInputType.text,
decoration: InputDecoration(labelText: 'Name'),
validator: (val) => val.length == 0 ? 'Enter Name' : null,
onSaved: (val) => name = val,
),
Row(
mainAxisAlignment: MainAxisAlignment.spaceEvenly,
children: <Widget>[
FlatButton(
onPressed: validate,
child: Text(isUpdating ? 'UPDATE' : 'ADD'),
),
FlatButton(
onPressed: () {
setState(() {
isUpdating = false;
});
clearName();
},
child: Text('CANCEL'),
)
],
),
],
),
),
);
}
SingleChildScrollView dataTable(List<Employee> employees) {
return SingleChildScrollView(
scrollDirection: Axis.vertical,
child: DataTable(
columns: [
DataColumn(
label: Text('NAME'),
),
DataColumn(
label: Text('DELETE'),
)
],
rows: employees
.map(
(employee) => DataRow(cells: [
DataCell(
Text(employee.name),
onTap: () {
setState(() {
isUpdating = true;
curUserId = employee.id;
});
controller.text = employee.name;
},
),
DataCell(IconButton(
icon: Icon(Icons.delete),
onPressed: () {
dbHelper.delete(employee.id);
refreshList();
},
)),
]),
)
.toList(),
),
);
}
list() {
return Expanded(
child: FutureBuilder(
future: employees,
builder: (context, snapshot) {
if (snapshot.hasData) {
return dataTable(snapshot.data);
}
if (null == snapshot.data || snapshot.data.length == 0) {
return Text("No Data Found");
}
return CircularProgressIndicator();
},
),
);
}
@override
Widget build(BuildContext context) {
return new Scaffold(
appBar: new AppBar(
title: new Text('Flutter SQLITE CRUD DEMO'),
),
body: new Container(
child: new Column(
mainAxisAlignment: MainAxisAlignment.start,
mainAxisSize: MainAxisSize.min,
verticalDirection: VerticalDirection.down,
children: <Widget>[
form(),
list(),
],
),
),
);
}
}Let’s see what the above code does…
The ‘form()’ function will create a Form widget with a TextField.
validator: (val) => val.length == 0 ? 'Enter Name' : null,
onSaved: (val) => name = val,The above code will check if the textfield is empty or null and if it returns true, the validate() function will be triggered assigning the name to the value from the TextField.
We are setting a boolean isUpdating to true if we are updating and employee.
The below code fetches the employees and refreshes the list.
refreshList() {
setState(() {
employees = dbHelper.getEmployees();
});
}Then we update the DataTable in the UI.
SingleChildScrollView dataTable(List<Employee> employees) {
return SingleChildScrollView(
scrollDirection: Axis.vertical,
child: DataTable(
columns: [
DataColumn(
label: Text('NAME'),
),
DataColumn(
label: Text('DELETE'),
)
],
rows: employees
.map(
(employee) => DataRow(cells: [
DataCell(
Text(employee.name),
onTap: () {
setState(() {
isUpdating = true;
curUserId = employee.id;
});
controller.text = employee.name;
},
),
DataCell(IconButton(
icon: Icon(Icons.delete),
onPressed: () {
dbHelper.delete(employee.id);
refreshList();
},
)),
]),
)
.toList(),
),
);
}Each row in the DataTable is made up of DataCell
DataCell(
Text(employee.name),
onTap: () {
setState(() {
isUpdating = true;
curUserId = employee.id;
});
controller.text = employee.name;
},
),The second column in the DataTable will have an IconButton with a delete button.
DataCell(
IconButton(
icon: Icon(Icons.delete),
onPressed: () {
dbHelper.delete(employee.id);
refreshList();
},
),
),onPress of the delete button, we will call the delete function from the dbHelper class and refresh the list from the database.
employees.map((employee)
....The above code will map the list of employees in the DataTable.
That’s it. We have successfully, fetched, inserted, updated and deleted employee records from the SQLite Database.
Please leave your valuable comments below.
Thanks for reading.
