SQLite/SQFlite CRUD Database Operations in Flutter

Watch Video Tutorial

SQLite CRUD Operations in Flutter

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: any

Let’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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vipin Vijayan

Vipin Vijayan

Flutter, React Native, Android, iOS App developer.