In Flutter, while analyzing some of the great database plugins/packages, you might have thought about the best practices for implementing those. So, out of all of them, for this blog post, here comes SQFLite for you. A highly reliable local database engine solely made for the best productive outputs. Let’s dive deep down and explore more SQFlite Local Database Integration in Flutter – A Quick Tour.
Table of Contents
Before we jump onto our target, let us first get an idea of why we need local databases, plus core concepts, vis-à-vis, what help they do to our Apps.
Core purpose and usage of local databases
The core purpose is to create persistent storage for the application’s data, which can be accessed when the device has no internet connectivity.
Moreover, there are several other factors about the same subject, which are as follows:
Offline Capabilities:
This tool is quite useful for Flutter apps that require user interaction regardless of network connectivity. Such example apps are notes, task management, media playback, etc.
Performance Improvement:
Fetching data from a local database is typically faster as compared to a remote server source. In addition to that, the SQFlite database is solely made for such efficient data retrieval and is also capable of handling even larger amounts of data.
Data Persistence:
Local databases’ top priority is to provide reliable, robust, and persistent solutions. Furthermore, it preserves data even at the time when a device is rooted or rebooted, keeping the database intact.
Data Privacy:
Storing the data locally can enhance the privacy and security of sensitive user information. Keeping specific data on the device itself may reduce the risk of data breaches or unauthorized access that could occur when the record gets transmitted to the remote source.
Now let’s discuss the project scenario for this blog post.
Project Scenario
We shall have a Books App with the basic CRUD operations. There will be two screens, one for the book listing and the rest for adding and modifying the records.
Let’s begin with our implementation stuff.
SQFlite Integration
Initially, create a new Flutter project, and add these packages to the pubspec.yaml file:
sqflite: The local database enginepath_provider: to get the directory and store the database filepath: provides path manipulating common operations like splitting, normalizing, etc.
Note: You can make use of Flutter’s in-built command:
flutter pub add package_name
To get the suitable version of it, depending upon your Flutter’s version, so as for Dart SDK too.
The next thing is to create folders inside the lib. For that, you can use any State Management tool you like the most.
However, we will be keeping it simple for now. So, add a new directory database, and create a dart file, namely database_config.dart.
This class will handle all of our database functions.
In this file, create a class DbConfig and make it a singleton one, to ensure that this class has only one instance of it.
class DbConfig {
static final DbConfig dbConfig = DbConfig._();
factory DbConfig() => dbConfig;
DbConfig._();
}Next, initialize the database function, and create a handler to get the DB instance each time while operating. In addition to this, execute a query to create the book table.
Here is the modified code snippet
import 'dart:io';
import 'package:flutter_useful_tasks/sqflite/database/db_literals.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
class DbConfig {
static final DbConfig dbConfig = DbConfig._();
factory DbConfig() => dbConfig;
DbConfig._();
static Database? db;
Future<Database> get dbInstance async {
if (db != null) {
return db!;
}
db = await onInit();
return db!;
}
onInit() async {
Directory directory = await getApplicationDocumentsDirectory();
String dbPath = join(directory.path, dbTitle);
await openDatabase(dbPath, onCreate: onCreate, version: dbVersion);
}
onCreate(Database database, int version) async {
await database.execute("""
CREATE TABLE $table($booKId INTEGER PRIMARY KEY AUTOINCREMENT, $bookTitle
TEXT, $bookAuthor TEXT, $bookPublishYear TEXT)
""");
}Note: For the sake of simplicity, I have saved the repetitive variables inside another file db_literals.dart.
/* LITERALS */
int dbVersion = 1;
String dbTitle = 'book_store.db';
String table = 'books';
/// TABLE SUB CONTENTS ///
String booKId = "book_id";
String bookTitle = "book_title";
String bookAuthor = "book_author";
String bookPublishYear = "book_publish_year";Up till now, we have covered the initialization process, and now it’s time to create the CRUD operations, but before that, we need to create a model class for our BookStore.
Create a new folder model, and under this create file books_model.dart.
Code Snippet
class BooksModel {
int bookId;
String bookTitle, bookAuthor, bookPublishYear;
BooksModel({
required this.bookId,
required this.bookPublishYear,
required this.bookAuthor,
required this.bookTitle,
});
factory BooksModel.fromMap(Map<dynamic, dynamic> json) => BooksModel(
bookId: json['book_id'],
bookTitle: json['book_title'],
bookAuthor: json['book_author'],
bookPublishYear: json['book_publish_year'],
);
dynamic toMap() => {
'book_id': bookId,
'book_title': bookTitle,
'book_author': bookAuthor,
'book_publish_year': bookPublishYear,
};
}Note: Because we don’t need to put each variable to be modified, while we use the update and add operations, we have rather created a model with a function toMap() for ease. This reflects only those variables with changed values on the whole.
Let’s now put on CRUD functions.
Create:
Future<BooksModel> addBook(BooksModel booksModel) async {
Database? database = await dbInstance;
booksModel.bookId = await database.insert(table, booksModel.toMap());
return booksModel;
}Update:
Future<int> updateBook(BooksModel booksModel) async {
Database? database = await dbInstance;
return database.update(
table,
booksModel.toMap(),
where: '$booKId = ?',
whereArgs: [booksModel.bookId],
);Delete:
Future<int> deleteBook(int id) async {
Database? database = await dbInstance;
return database.delete(table, where: '$booKId = ?', whereArgs: [id]);
}Retrieve:
This one’s a bit tricky.
Stream<List<BooksModel>> retrieveBooks() async* {
Database? database = await dbInstance;
List<Map> map = await database.query(table);
List<BooksModel> bookList = [];
for (int i = 0; i < map.length; i++) {
bookList.add(BooksModel.fromMap(map[i]));
}
yield bookList;
}We’re done with the DB stuff, let’s now set up our UI structure.
Create a new folder named presentation. Under this, create two files, book_listing.dart, and add_book.dart respectively.
These files shall cover all CRUD operations.
book_listing.dart
StreamBuilder<List<BooksModel>>(
key: Key('${Random().nextDouble()}'),
stream: config.retrieveBooks(),
builder: (context, bookSp) {
if (bookSp.hasData) {
return bookSp.data!.isEmpty
? Center(
child: Text(
"No Books",
style:
TextStyle(fontSize: 32, fontWeight: FontWeight.w700),
),
)
: ListView.builder(
shrinkWrap: true,
itemCount: bookSp.data!.length,
itemBuilder: (context, i) {
BooksModel item = bookSp.data![i];
return Dismissible(
key: UniqueKey(),
child: BookCustomWidget(model: item),
onDismissed: (direction) {
setState(() {
config.deleteBook(item.bookId!);
});
});
},
);
}
return const SizedBox.shrink();
},
),add_book.dart
Column(
children: [
Expanded(
child: ListView(
shrinkWrap: true,
padding: EdgeInsets.symmetric(horizontal: 14, vertical: 14),
children: [
formField(_bookC, 'Book Title'),
formField(_bookAuthorC, 'Book Author'),
formField(_bookPublishC, 'Publishing Year'),
],
),
),
Container(
margin: EdgeInsets.symmetric(vertical: 12, horizontal: 12),
height: 50,
width: MediaQuery.of(context).size.width,
child: ElevatedButton(
onPressed: _id == -1 ? () async {
BooksModel model = BooksModel(
bookPublishYear: _bookPublishC.text,
bookAuthor: _bookAuthorC.text,
bookTitle: _bookC.text,
);
await config.addBook(model);
Navigator.of(context).pop(true);
} : () async {
BooksModel model = BooksModel(
bookId: _id,
bookPublishYear: _bookPublishC.text,
bookAuthor: _bookAuthorC.text,
bookTitle: _bookC.text,
);
await config.updateBook(model);
Navigator.of(context).pop(true);
},
child: Text(_id == -1 ? "Add Book" : "Edit Book"),
),
),
],
)Note: It’s just a sample code. You can find the complete source code repository link in the last section.
————— Let’s now run the App ——————-
The UI View
Here is the Video Preview
Thanks for being with me till here. It means a lot.
Summing Up
So, I hope this blog post – SQFlite Local Database Integration in Flutter – A Quick Tour gets all the points covered via its code snippets, various examples in between, and other stuff. However, if you think something is missed or has insufficient information, you can jot it down in the comment section under this post. I shall be more than happy to respond to that.
Link to the repository – Click Here
Follow me on GitHub, and LinkedIn.
P.S. Many thanks for your precious time.
Also read – Mastering Flutter/Dart Keywords – An Interesting Guide for Developers