The soaring technology trend in the mobile app industry is nothing but Flutter, a cross-platform SDK launched by Google. With its cross-platform features and easy to implement methods, Flutter has attracted the interest of many developers.

Starting with the configuration and architecture of a Flutter app is a straightforward process. But, when you jump to the next step that is creating a Database, you might get stuck. To be precise, there is no such database method available for Flutter but one can integrate the SQLite database system to the Flutter app using SQFLite plugin.

In this article, we are going to discuss how to set up a local SQLite database in a Flutter app. We are going to demonstrate it on a TODO list example.

What is SQLite?

SQLite is an open-source relational database. SQLite does not have a separate server process which means that it essentially stores the data in a file (also known as an embedded mode). SQLite is the world’s most used database and is used in almost every connected device. Both Android & iOS relies heavily on SQLite.

SQFLite is a plugin for Flutter. It supports both the native platforms i.e Android & iOS.

Why use the SQFlite plugin?

Flutter does not provide a built-in abstraction for accessing the SQLite Database. However, using the SQFlite plugin, one can access the SQLite database on both Android & iOS. The plugin is well-maintained and is recommended by the Flutter team as well.

If you are new to SQLite and SQL statements, go ahead & learn the basics of SQLite from SQL Tutorial

In this article, we will be demonstrating an example (TODO App) of using the SQFlite plugin in the Flutter application.

Depend on SQFlite

Add the following dependencies to pubspec.yml and run “flutter packages get” command in the terminal.

Dependencies:

sqflite: ^1.1.0 #sqflite package flutter_simple_dependency_injection: ^1.0.1 #to perform dependency injection path_provider: ^0.5.0+1 #helper package used by database helper class to get the database file

Create a Database Helper Class

This class is going to be used throughout the app’s lifecycle. Ideally, it should be used with some kind of Dependency Injection technique.

import 'dart:io' as io; import 'package:path/path.dart'; import 'package:path_provider/path_provider.dart'; import 'package:sqflite/sqflite.dart'; class DatabaseHelper { static final DatabaseHelper _instance = new DatabaseHelper.internal(); factory DatabaseHelper() => _instance; static Database _db; DatabaseHelper.internal(); initDb() async { io.Directory documentsDirectory = await getApplicationDocumentsDirectory(); String path = join(documentsDirectory.path, "database_name.db"); _db = await openDatabase(path, version: 1, onCreate: _onCreate); } Database get db { return _db; } void _onCreate(Database db, int version) async { // When creating the db, create the table await db.execute( 'CREATE TABLE todos (id INTEGER PRIMARY KEY AUTOINCREMENT,' 'item_name TEXT,' 'date_created TEXT')'; } }

In the class above, the initDb() method initializes the and calls onCreate() when the database is created for the first time. The version parameter is the database version and is useful when migrating/updating the DB structure when the app is updated.

Create an Injection class

import 'dart:async'; import 'package:eknumber/utils/database_helper.dart'; import 'package:flutter_simple_dependency_injection/injector.dart'; class Injection { static DatabaseHelper _databaseHelper = DatabaseHelper(); static Injector injector; static Future initInjection() async { await _databaseHelper.initDb(); injector = Injector.getInjector(); injector.map<DatabaseHelper>((i) => _databaseHelper, isSingleton: true); } }

The above class makes sure that there’s only one instance of the database available throughout the app’s lifecycle. In order to use the DB from any other class, you can get it easily by doing-

DatabaseHelper _databaseHelper = Injection.injector.get();

Create a Model Class

In this example, we’re going to create a simple data model with few properties and few methods which will be used when doing CRUD operations on the DB table.

class Todo{ int id; String itemName; String dateCreated; Todo( {this.id, this.itemName, this.dateCreated}) //to be used when inserting a row in the table Map<String, dynamic> toMapWithoutId() { final map = new Map<String, dynamic>(); map["item_name"] = itemName; map["date_created"] = dateCreated; return map; } Map<String, dynamic> toMap() { final map = new Map<String, dynamic>(); map["id"] = id; map["item_name"] = itemName; map["date_created"] = dateCreated; return map; } //to be used when converting the row into object factory Todo.fromMap(Map<String, dynamic> data) => new Todo( id: data['id'], itemName: data['item_name'], dateCreated: data['date_created'] ); }

Performing basic CRUD operations

Now that we’ve set up the basic structure along with dependency injection, we can easily perform CRUD operations on our model. Note that the below example methods are redacted and can be used in conjunction with other techniques like Repository pattern.

Create

To create a row in the table, we’re going to use the toMapWithoutId() in the model class-

Future<int> create(String itemName, String dateCreated) async{ final todo = new Todo(itemName: itemName, dateCreated: dateCreated); //databaseHelper has been injected in the class int id = await _databaseHelper.db.insert(“todos”, todo.toMapWithoutId()); return id; }

Read



To read a single record, we’ll write a raw SQL query. This query will return a List of Maps. The maps are nothing but rows in the table and we’ll use the Todo.fromMap() factory method to get our desired objects.

Future<int> getItemById(int id) async{ //databaseHelper has been injected in the class List<Map> list = await _databaseHelper.db.rawQuery("Select * from todos where id = ?",[id]); if(list.length > 0){ return Todo.fromMap(list[0]); } return null; }

Update

To update data, use the update() method from the SQFlite package. Note that you must always use where clause to avoid undesirable effects.

Future<int> updateItem(Todo item) async { //databaseHelper has been injected in the class return await _databaseHelper.db.update("todos", item.toMap(), where: "id = ?", whereArgs: [item.id]); }

Delete

Delete is rather simple. Just pass the column name and its value in the where clause. Note that you must always use where clause to avoid undesirable effects.

Future<int> deleteItem(int id) async { return await _databaseHelper.db.delete("todos", where: "id = ?", whereArgs: [id]); }

Update the main()

In order to initialize the db when the app starts, we need to call the init() method of the Injection class.

This is how your main() method should look like-

void main() async { WidgetsFlutterBinding.ensureInitialized(); await Injection.initInjection(); runApp(MyApp()); }

Setting up a local database is the most crucial part of any application development which stores a lot of data along with offline capabilities. We have demonstrated how one can implement CRUD operations against the SQLite database system.

Looking to get an offline Flutter app with SQFlite? Hire one of the best Flutter app development companies! Reach out to us at hello@citrusleaf.in