Using Sqflite in Flutter Application

Mobile Apps Development A-Z Guide.

Flutter and SQLite

Give us a message if you’re interested in Blockchain and FinTech software development or just say Hi at Pharos Production Inc.

Or follow us on Youtube to know more about Software Architecture, Distributed Systems, Blockchain, High-load Systems, Microservices, and Enterprise Design Patterns.

Pharos Production Youtube channel

Flutter is a framework for cross-platform mobile application development. And it goes without saying that no modern mobile application can do without a database. To date, this framework supports the only SQLite database. To fully use this database, you must use the Sqflite plugin.

In this article, I would like to demonstrate an example of using the Sqflite plugin in the Flutter application.

Step 1. The very first step is to add the necessary dependencies to the pubspec.yaml and run in your terminal “flutter packages get” command.

dependencies:
  sqflite: ^1.1.0
  path_provider: ^0.5.0+1

Step 2.

First, we need to create a data model with which we will work. As a described object we will take the Car.

class Car {
  @required
  final int id;
  @required
  final String model;
  @required
  final int power;
  @required
  final int topSpeed;
  @required
  final bool isElectro;
}

Sqflite accepts a key-value collection as input parameters. Create a method that converts Car model into the map.

Map<String, dynamic> toMapForDb() {
  var map = Map<String, dynamic>();
  map['id'] = id;
  map['model'] = model;
  map['power'] = power;
  map['top_speed'] = topSpeed;
  map['is_electro'] = isElectro ? 1 : 0;
  return map;
}

And accordingly, it is necessary to create a constructor, which takes the Map<String, dynamic> as input parameters:

Car.fromDb(Map<String, dynamic> map)
    : id = map['id'],
      model = map['model'],
      power = map['power'],
      topSpeed = map['top_speed'],
      isElectro = map['is_electro'] == 1;

Step 3.

The next step is to create a helper class for working with the database. In this class, we also implement a singleton pattern. Since the file structures of Android and IOs are different from each other, in order to specify the path to store the database, you must import and use the following library — path_provider.dart

class CarDatabase {
  static final CarDatabase _instance = CarDatabase._();
  static Database _database;
CarDatabase._();
factory CarDatabase() {
    return _instance;
  }
Future<Database> get db async {
    if (_database != null) {
      return _database;
    }
_database = await init();
    
    return _database;
  }
Future<Database> init() async {
    Directory directory = await getApplicationDocumentsDirectory();
    String dbPath = join(directory.path, 'database.db');
    var database = openDatabase(dbPath, version: 1, onCreate: _onCreate, onUpgrade: _onUpgrade);
return database;
  }

Next, let’s specifically look at the _onCreate function. Here we need to manually create a table using the SQL script:

void _onCreate(Database db, int version) {
  db.execute('''
    CREATE TABLE car(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      model TEXT,
      power INTEGER,
      top_speed INTEGER,
      is_electro INTEGER)
  ''');
  print("Database was created!");
}

If the database has already been created before and it is necessary to roll out the migrations it’s performed in onUpgrade function:

void _onUpgrade(Database db, int oldVersion, int newVersion) {
  // Run migration according database versions
}

Step 4.

All database operations are asynchronous. So let’s look at examples of basic operations:

Do not forget also to specify conflictAlgorithm parameter in create and update operations.

Create:

Future<int> addCar(Car car) async {
  var client = await db;
  return client.insert('car', car.toMapForDb(), conflictAlgorithm: ConflictAlgorithm.replace);
}

Read:

Future<Car> fetchCar(int id) async {
  var client = await db;
  final Future<List<Map<String, dynamic>>> futureMaps = client.query('car', where: 'id = ?', whereArgs: [id]);
  var maps = await futureMaps;
if (maps.length != 0) {
    return Car.fromDb(maps.first);
  }
return null;
}

Update:

Future<int> updateCar(Car newCar) async {
  var client = await db;
  return client.update('car', newCar.toMapForDb(), where: 'id = ?', whereArgs: [newCar.id], conflictAlgorithm: ConflictAlgorithm.replace);
}

Delete:

Future<void> removeCar(int id) async {
  var client = await db;
  return client.delete('car', where: 'id = ?', whereArgs: [id]);
}

An example of the layout of the main screen and the entire application you can find on the link below.

PharosProduction/tutorial-flutter-sqflite

Thanks for reading!