How to use SQLite in Flutter apps and save data locally in Flutter.
As you know almost in every app, storing data on local storage is an essential functionality. This is helpful when we want to give offline access to our users and don’t want to fetch data again and again from server. So, we load data from server once and save data in local storage and use that to perform next actions. In this article we will learn how to use SQLite in flutter apps and save data locally in flutter apps.
To store data in device’s local storage, Android provides built in open source SQL database which stores data in it. It is a C-language library and is serverless and lightweight solution. SQLite supports all the relational database operations such as store, manipulate and retrieving data from database.
SQLite in Flutter
Flutter apps can also use SQLite data base using sqflite plugin which is available on pub dev. It supports almost all SQL standards. This plugin contains helpers for common CRUD operations, but it also provides ability to write your own SQL queries in String. In flutter, all the database operations are handled in background thread.
Let’s start implementation of sqflite in Flutter apps.
Add dependency to Flutter project
To use SQLite in flutter apps. You need to add sqflite plugin in your project. First create a new Flutter project, then open pubspec.yml file, and add the following code.
dependencies:
flutter:
sdk: flutter
sqflite: ^2.0.2
path: 1.8.0
cupertino_icons: ^1.0.2
Here we have added two plugins one for SQLite database and other one is for specifying the location of the file that will contain the database.
Now you can use SQLite in your Flutter app.
Initialize Database in Flutter
Now create DatabaseHelper class. This class will contain the methods to create database and perform all database related operations. Like create database, create table, insert data in tables, fetch data from tables, update and delete data etc. Now first create a method initializeDB().
Future<Database> initializeDB() async {
String path = await getDatabasesPath();
return openDatabase(
join(path, "users.db"),
onCreate: (database, version) async {
await database.execute(
"CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL )");
},
version: 1,
);
}
Here we initialize database and create a table in it. The openDatabase method is in sqflite package and is used to open the database connection. It accepts the path of database and a version number and a callback named onCreate. This onCreate method executes once when database is created for first time. Therefore we have created users table in this method. The users table contains id, first_name and second_name columns.
Model Class
The data In SQLite will be save in Dart Maps data type. So you need to create model class with toMap and fromMap mothods. Here we will do simple example of database operations. So we just create User model class with only three data members that are id, first name and last name.
class User {
int id = 0;
String firstName = '';
String lastName = '';
User.empty();
User({
required this.id,
required this.firstName,
required this.lastName,
});
factory User.fromMap(Map<String, dynamic> json) {
return User(
id: json['id'],
firstName: json['first_name'],
lastName: json['last_name'],
);
}
Map<String, dynamic> toMap() => {
'first_name': firstName,
'last_name': lastName,
};
}
SQLite CRUD Operations
Insert Data in Database
As we have discussed that all the db operations will be performed in database helper class. So to insert data in database we will write method in this class. We are working on users so we will create inserUser(User user) method with user as a parameter.
Future<int> insertUser(User user) async {
final Database db = await initializedDB();
return await db.insert('users', user.toMap());
}
Retrieve Data from Database
To fetch all data from database, create a method getAllUser() method in database helper class. Which will return all rows of that table.
Future<List<User>> getAllUsers() async {
final Database db = await initializedDB();
List<Map<String, dynamic>> result = await db.query('users');
return result.map((e) => User.fromMap(e)).toList();
}
Delete Data from Database
To delete a single user form database we will use delete method. Delete method requires three parameters. First one in table name, Second one is for where clause and third one is for arguments.
Future<void> deleteUser(int id) async {
final Database db = await initializedDB();
db.delete('users', where: 'id= ?', whereArgs: [id]);
}
Update Data in Database
To update data in database we will use update method. This method requires 4 parameters.
Future<void> updateUsingHelper(User user) async {
final Database db = await initializedDB();
await db.update('users', user.toMap(), where: 'id= ?', whereArgs: [user.id]);
}
Complete Code of Sqflite CRUD example in Flutter
main.dart
import 'dart:io';
import 'package:flutter/material.dart';
import 'package:my_demo/data_base_handler.dart';
import 'package:my_demo/user.dart';
import 'package:my_demo/user_form.dart';
import 'package:my_demo/users_list.dart';
import 'package:sqflite/sqflite.dart';
void main() {
runApp(const MyApp());
}
class MyApp extends StatelessWidget {
const MyApp({Key? key}) : super(key: key);
// This widget is the root of your application.
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Flutter Demo',
theme: ThemeData(
primarySwatch: Colors.blue,
),
home: const MyHomePage(),
);
}
}
class MyHomePage extends StatefulWidget {
const MyHomePage({Key? key}) : super(key: key);
@override
State<MyHomePage> createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
late Future<List<User>> users;
TextEditingController firstNameTextController = TextEditingController();
TextEditingController lastNameTextController = TextEditingController();
DatabaseHandler dbHandler = DatabaseHandler();
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('Users'),
),
body: Padding(
padding: const EdgeInsets.all(8.0),
child: Column(
children: [
TextField(
controller: firstNameTextController,
decoration: const InputDecoration(hintText: 'First name'),
),
TextField(
controller: lastNameTextController,
decoration: const InputDecoration(hintText: 'Last name'),
),
Container(
width: double.infinity,
margin: const EdgeInsets.symmetric(horizontal: 10, vertical: 10),
child: ElevatedButton(
onPressed: () async {
User user = User.empty();
user.firstName = firstNameTextController.text.toString();
user.lastName = lastNameTextController.text.toString();
await dbHandler.insertUser(user);
initState();
setState(() {});
},
child: const Text('Save'),
),
),
Row(children: const <Widget>[
Expanded(
child: Divider(
color: Colors.black,
)),
]),
Expanded(
child: FutureBuilder(
future: users,
builder: (context, snapshot) {
if (snapshot.hasData) {
var usersList = snapshot.data as List<User>;
return ListView.builder(
itemCount: usersList.length,
itemBuilder: (BuildContext context, int index) {
User user = usersList[index];
return ListTile(
title: Text(user.firstName + ' ' + user.lastName),
trailing: IconButton(
onPressed: () {
dbHandler.deleteUser(user.id);
initState();
setState(() {});
},
icon: const Icon(Icons.delete),
),
);
});
} else {
return const CircularProgressIndicator();
}
}),
),
],
),
),
);
}
@override
void initState() {
users = this.getUsersLIst();
}
Future<List<User>> getUsersLIst() async {
return await DatabaseHandler().getAllUsers();
}
}
data_base_handler.dart
import 'package:my_demo/user.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
class DatabaseHandler {
Future<Database> initializedDB() async {
String path = await getDatabasesPath();
return openDatabase(
join(path, "users.db"),
onCreate: (database, version) async {
await database.execute(
"CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, first_name TEXT NOT NULL, last_name TEXT NOT NULL )");
},
version: 1,
);
}
Future<int> insertUser(User user) async {
final Database db = await initializedDB();
return await db.insert('users', user.toMap());
}
Future<List<User>> getAllUsers() async {
final Database db = await initializedDB();
List<Map<String, dynamic>> result = await db.query('users');
return result.map((e) => User.fromMap(e)).toList();
}
Future<void> deleteUser(int id) async {
final Database db = await initializedDB();
db.delete('users', where: 'id= ?', whereArgs: [id]);
}
Future<void> updateUsingHelper(User user) async {
final Database db = await initializedDB();
await db.update('users', user.toMap(), where: 'id= ?', whereArgs: [user.id]);
}
}
Output
