The simplest app for combining flutter and sqlite

As a newbie to flutter and app development, I wanted to make an application which need to support saving, deleting, and updating data in local storage. After a lot of searching, I found that those sample applications were either too complicated or outdated. The flutter cookbook provides an good example about how to use sqlite in flutter, but it contains only the database part. Therefore, I decided to write maybe the simplest application using flutter and sqlite.

This application is called “Fridge Manager”. You can store, update, and delete the food information in that app. I only test it in Emulator in Android Studio since I do not have an Android mobile phone.

This scripts are very naive. I modified them based on the cookbook and some GitHub repository. My priority was to make it work.

1. Add the dependencies

Firstly, you need sqflite, path, and intl packages.

The sqflite package provides classes and functions to interact with a SQLite database.
The path package provides functions to define the location for storing the database on disk.
The intl package provides a common entry point for internationalization related tasks including formating date and time.

dependencies:
  flutter:
    sdk: flutter
  sqflite:
  path:
  intl:

Click Pub get to install referenced packages.

2. Database helper

In you lib folder, right-click > New > Dart File and name it db_helper

import packages

import 'dart:async';
import 'package:flutter/widgets.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

We need to define a model to store the food information. It is a class in Dart. For this example, define a Food class that contains four pieces of data: A unique id, the name, the date, and the description of each food.
The data need to be converted to Map to store in the database.

class Food {
  int? id;
  String name;
  String date;
  String? description;

  Food({
    required this.id,
    required this.name,
    required this.date,
    required this.description,
  });
  // Convert a Food into a Map. The keys must correspond to the names of the
  // columns in the database.
  Map<String, dynamic> toMap() {
    var map = Map<String, dynamic>();
    if (id != null) {
      map['id'] = id;
    }
    map['name'] = name;
    map['date'] = date;
    map['description'] = description;
    return map;
  }

  // Implement toString to make it easier to see information about
  // each food when using the print statement.
  @override
  String toString() {
    return 'Food{id: $id, name: $name, date: $date, description: $description}';
  }
}

Add database helper

Create a class named DatabaseHelper and create instance. The symbols including ? and ! are related to not_initialized_non_nullable_variable. I would figure it out one day. Anyways, if I did not write like this, I got a bunch of errors.

class DatabaseHelper {
  static DatabaseHelper? _databaseHelper;
  static Database? _database;

  DatabaseHelper._createInstance();

  factory DatabaseHelper() {
    if(_databaseHelper == null) {
      _databaseHelper = DatabaseHelper._createInstance();
    }
    return _databaseHelper!;
  }
}

This is the basic part of the DatabaseHelper class. Then you need to add more functions to that class (inside the biggest {}).

Open the database and create a Food table

Before reading and writing data to the database, open a connection to the database. This involves two steps:

Define the path to the database file using getDatabasesPath() from the sqflite package, combined with the join function from the path package.
Open the database with the openDatabase() function from sqflite.

Next, use onCreate to create a table to store information about various Food. For this example, create a table called food that defines the data that can be stored. Each Food contains an id, name, date and description. Therefore, these are represented as four columns in the food table.

The id is a Dart int, and is stored as an INTEGER SQLite Datatype. It is also good practice to use an id as the primary key for the table to improve query and update times.
The name is a Dart String, and is stored as a TEXT SQLite Datatype.
The date is also a Dart String, and is stored as an TEXT SQLite Datatype.

The description is a Dart String, and is stored as an TEXT SQLite Datatype.

Future<Database> get database async => _database ??= await initializeDatabase();

Future<Database> initializeDatabase() async {
  // Avoid errors caused by flutter upgrade.
  // Importing 'package:flutter/widgets.dart' is required.
  WidgetsFlutterBinding.ensureInitialized();
  // Open the database and store the reference.
  Database database = await openDatabase(
    // Set the path to the database. Note: Using the `join` function from the
    // `path` package is best practice to ensure the path is correctly
    // constructed for each platform.
    join(await getDatabasesPath(), 'food_database.db'),
    // When the database is first created, create a table to store food.
    onCreate: (db, version) {
      // Run the CREATE TABLE statement on the database.
      return db.execute(
        'CREATE TABLE food(id INTEGER PRIMARY KEY, name TEXT, date TEXT, description TEXT)',
      );
    },
    // Set the version. This executes the onCreate function and provides a
    // path to perform database upgrades and downgrades.
    version: 1,
  );
  return database;
}

Insert data to the database

It takes two steps to insert a kind of food into the food table:

Convert the Food into a Map
Use the insert() method to store the Map in the food table.

// Define a function that inserts food into the database
Future<int> insertFood(Food food) async {
  // Get a reference to the database.
  final db = await database;

  // Insert the Food into the correct table. You might also specify the
  // `conflictAlgorithm` to use in case the same food is inserted twice.
  //
  // In this case, replace any previous data.
  var result = await db.insert(
    'food',
    food.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
  return result;
}

Retrieve the list of Food

Now the food is store in the database, query the database for a specific kind of food or a list of all food. This involves two steps:

Run a query against the food table. This returns a List.

Convert the List into a List.

// A method that retrieves all the food from the food table.
Future<List<Food>> getFoodList() async {
  // Get a reference to the database.
  final db = await database;

  // Query the table for all The Food.
  final List<Map<String, dynamic>> maps = await db.query('food');

  // Convert the List<Map<String, dynamic> into a List<Food>.
  return List.generate(maps.length, (i) {
    return Food(
      id: maps[i]['id'],
      name: maps[i]['name'],
      date: maps[i]['date'],
      description: maps[i]['description'],
    );
  });
}

Update a kind of food in the database

After inserting information into the database, you might want to update that information at a later time. You can do this by using the update() method from the sqflite library.

Future<int> updateFood(Food food) async {
  // Get a reference to the database.
  final db = await database;

  // Update the given Food.
  var result = await db.update(
    'food',
    food.toMap(),
    // Ensure that the Food has a matching id.
    where: 'id = ?',
    // Pass the Food's id as a whereArg to prevent SQL injection.
    whereArgs: [food.id],
  );
  return result;
}

Delete a kind of food from the database

In addition to inserting and updating the information about the food, you can also remove a kind of food from the database. To delete data, use the delete() method from the sqflite library.

  Future<int> deleteFood(int id) async {
    // Get a reference to the database.
    final db = await database;

    // Remove the Food from the database.
    var result = await db.delete(
      'food',
      // Use a `where` clause to delete a specific Food.
      where: 'id = ?',
      // Pass the Food's id as a whereArg to prevent SQL injection.
      whereArgs: [id],
    );
    return result;
  }
}

Now this database helper is done.

3. Screens

Now it is time to deal with the user interface. In this app, you need to screens, one for list, and the other for detail. You can check all your food storage in list screen, and add new items in detail screen.

In you lib folder, right-click > New > Directory and name it screens. Then right-click screens > New > Dart File and create two files: food_detail.dart and food_list.dart.

food_detail screen

Import packages

1
2
3

import 'package:flutter/material.dart';
import 'package:fridge_manager/db_helper.dart';
import 'package:intl/intl.dart';

Create FoodDetail StatefulWidget

Create a StatefulWidget by typing stful, then name it FoodDetail. It has two properties: appBarTitle and food.

class FoodDetail extends StatefulWidget {
  final String appBarTitle;
  final Food food;

  const FoodDetail(this.appBarTitle, this.food, {Key? key}) : super(key: key);

  @override
  State<FoodDetail> createState() => _FoodDetailState(appBarTitle, food);
}

All you need to do is creating a variable like this to call functions created in db_helper.dart:

class _FoodDetailState extends State<FoodDetail> {
  final String appBarTitle;
  final Food food;

  DatabaseHelper databaseHelper = DatabaseHelper();
}

Then you can call databaseHelper.deleteFood(food.id), databaseHelper.updateFood(food), and databaseHelper.insertFood(food) in Widget.

food_list screen

To be continue…

Tips

There are many errors caused by non-nullable by default (NNBD) feature in outdated sample codes. Try to add ? and ! to solve them.
A ? means this variable could be null when you initialize it.
A ! means this variable is converted to a non-nullable variable. Before you apply this !, make sure add this if:

1
2
3

if (something != null) {
    new_something = something!;
}

References

The flutter cookbook > Persist_data_with_SQLite: https://docs.flutter.dev/cookbook/persistence/sqlite
A GitHub repository: https://github.com/ElmouradiAmine/note_keeper