Using SQLite in Flutter

Published on January 08, 2019

Using SQLite in Flutter

  • Tutorial
Hi, Habr! We present to your attention the translation of the article “Using SQLite in Flutter” .



Saving data is very important for users, since it is impractical to load the same data from the network. It would be wiser to save them locally.

In this article, I will demonstrate how to do this using SQLite in Flutter-e.

Why SQLite?


SQLite is the most popular way to store data on mobile devices. In this article, we will use the sqflite package to use SQLite. Sqflite is one of the most frequently used and relevant libraries for connecting SQLite databases in Flutter.

1. Add dependencies


In our project, open the file pubspec.yaml . Under dependencies add the latest version of sqflite and path_provider.

dependencies:
  flutter:
    sdk: flutter
  sqflite: any
  path_provider: any

2. Create DB Client


Now create a new Database.dart file. In it we will create singleton.

Why we need a singleton: we use this pattern to make sure that we only have one class entity and to provide a global entry point to it.

1. Create a private constructor that can only be used inside this class.

class DBProvider {
  DBProvider._();
  static final DBProvider db = DBProvider._();
}

2. Set up the database

The next step is to create a database object and provide a getter, where we will create a database object if it has not yet been created (lazy initialization)

static Database _database;
  Future<Database> get database async {
    if (_database != null)
    return _database;
    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

If there is no object assigned to the database, then we call the initDB function to create the database. In this function, we will get a path to save the database and create the desired tables.

initDB() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "TestDB.db");
    return await openDatabase(path, version: 1, onOpen: (db) {
    }, onCreate: (Database db, int version) async {
      await db.execute("CREATE TABLE Client ("
          "id INTEGER PRIMARY KEY,"
          "first_name TEXT,"
          "last_name TEXT,"
          "blocked BIT"
          ")");
    });
  }

3. Create a model class


The data inside the database will be converted to Dart Maps. We need to create classes of models with toMap and fromMap methods.

To create classes of models, I am going to use this site

Our model:

/// ClientModel.dart
import 'dart:convert';
Client clientFromJson(String str) {
    final jsonData = json.decode(str);
    return Client.fromJson(jsonData);
}
String clientToJson(Client data) {
    final dyn = data.toJson();
    return json.encode(dyn);
}
class Client {
    int id;
    String firstName;
    String lastName;
    bool blocked;
    Client({
        this.id,
        this.firstName,
        this.lastName,
        this.blocked,
    });
    factory Client.fromJson(Map<String, dynamic> json) => new Client(
        id: json["id"],
        firstName: json["first_name"],
        lastName: json["last_name"],
        blocked: json["blocked"],
    );
    Map<String, dynamic> toJson() => {
        "id": id,
        "first_name": firstName,
        "last_name": lastName,
        "blocked": blocked,
    };
}

4. CRUD operations


Create

Using rawInsert:

newClient(Client newClient) async {
    final db = await database;
    var res = await db.rawInsert(
      "INSERT Into Client (id,first_name)"
      " VALUES (${newClient.id},${newClient.firstName})");
    return res;
  }

Using insert:

newClient(Client newClient) async {
    final db = await database;
    var res = await db.insert("Client", newClient.toMap());
    return res;
  }

Another example of using a large ID as a new ID.

newClient(Client newClient) async {
    final db = await database;
    //get the biggest id in the table
    var table = await db.rawQuery("SELECT MAX(id)+1 as id FROM Client");
    int id = table.first["id"];
    //insert to the table using the new id 
    var raw = await db.rawInsert(
        "INSERT Into Client (id,first_name,last_name,blocked)"
        " VALUES (?,?,?,?)",
        [id, newClient.firstName, newClient.lastName, newClient.blocked]);
    return raw;
  }

Read

Get Client by id

getClient(int id) async {
    final db = await database;
    var res =await  db.query("Client", where: "id = ?", whereArgs: [id]);
    return res.isNotEmpty ? Client.fromMap(res.first) : Null ;
  }

Get all Clients with a condition

getAllClients() async {
    final db = await database;
    var res = await db.query("Client");
    List<Client> list =
        res.isNotEmpty ? res.map((c) => Client.fromMap(c)).toList() : [];
    return list;
  }

Get blocked customers only

getBlockedClients() async {
    final db = await database;
    var res = await db.rawQuery("SELECT * FROM Client WHERE blocked=1");
    List<Client> list =
        res.isNotEmpty ? res.toList().map((c) => Client.fromMap(c)) : null;
    return list;
  }

Update

Update an existing Client

updateClient(Client newClient) async {
    final db = await database;
    var res = await db.update("Client", newClient.toMap(),
        where: "id = ?", whereArgs: [newClient.id]);
    return res;
  }

Lock / Unlock Client


blockOrUnblock(Client client) async {
    final db = await database;
    Client blocked = Client(
        id: client.id,
        firstName: client.firstName,
        lastName: client.lastName,
        blocked: !client.blocked);
    var res = await db.update("Client", blocked.toMap(),
        where: "id = ?", whereArgs: [client.id]);
    return res;
  }

Delete

Delete one Client

deleteClient(int id) async {
    final db = await database;
    db.delete("Client", where: "id = ?", whereArgs: [id]);
  }

Delete All Clients

deleteAll() async {
    final db = await database;
    db.rawDelete("Delete * from Client");
  }

Demo



For our demo, we will create a simple application that displays our database.

To begin with we will impose the screen

Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text("Flutter SQLite")),
      body: FutureBuilder<List<Client>>(
        future: DBProvider.db.getAllClients(),
        builder: (BuildContext context, AsyncSnapshot<List<Client>> snapshot) {
          if (snapshot.hasData) {
            return ListView.builder(
              itemCount: snapshot.data.length,
              itemBuilder: (BuildContext context, int index) {
                Client item = snapshot.data[index];
                return ListTile(
                  title: Text(item.lastName),
                  leading: Text(item.id.toString()),
                  trailing: Checkbox(
                    onChanged: (bool value) {
                      DBProvider.db.blockClient(item);
                      setState(() {});
                    },
                    value: item.blocked,
                  ),
                );
              },
            );
          } else {
            return Center(child: CircularProgressIndicator());
          }
        },
      ),
      floatingActionButton: FloatingActionButton(
        child: Icon(Icons.add),
        onPressed: () async {
          Client rnd = testClients[math.Random().nextInt(testClients.length)];
          await DBProvider.db.newClient(rnd);
          setState(() {});
        },
      ),
    );
  }

Notes:

1. FutureBuilder is used to retrieve data from the database

2. FAB to initialize test clients

List<Client> testClients = [
    Client(firstName: "Raouf", lastName: "Rahiche", blocked: false),
    Client(firstName: "Zaki", lastName: "oun", blocked: true),
    Client(firstName: "oussama", lastName: "ali", blocked: false),
  ];

3. CircularProgressIndicator is shown when there is no data

4. When a user clicks on the client’s checkboxes is locked / unlocked

Now it’s very easy to add new features, for example, if we want to delete a client, when he’s tapping, just wrap the ListTile in the Dismissible widget like this:

return Dismissible(
   key: UniqueKey(),
   background: Container(color: Colors.red),
   onDismissed: (direction) {
    DBProvider.db.deleteClient(item.id);
   },
    child: ListTile(...),
  );


Refactoring to use the BLoC pattern


We have done a lot in this article, but in applications in the real world, initializing states in the UI layer is not a good idea. Separate the logic from the UI.

There are many patterns in Flutter, but we will use BLoC as it is the most flexible to configure.

Create a BLoC

class ClientsBloc {
  ClientsBloc() {
    getClients();
  }
  final _clientController =     StreamController<List<Client>>.broadcast();
  get clients => _clientController.stream;
  dispose() {
    _clientController.close();
  }
  getClients() async {
    _clientController.sink.add(await DBProvider.db.getAllClients());
  }
}

Notes:
Notes:

1. getClients retrieves data from the database (Client table) asynchronously. We will use this method whenever we need to update the table, therefore it is worth putting it into the body of the constructor.

2. We created StreamController.broadcast, in order to listen to broadcast events more than once. In our example, this does not really matter, since we listen to them only once, but it would be nice to implement this for the future.

3. Do not forget to close the threads. This way we prevent the memory of the faces. In our example, we close them using the dispose method in StatefulWidget.

Now let's look at the code.

blockUnblock(Client client) {
  DBProvider.db.blockOrUnblock(client);
  getClients();
}
delete(int id) {
  DBProvider.db.deleteClient(id);
  getClients();
}
add(Client client) {
  DBProvider.db.newClient(client);
  getClients();
}

Finally, the final result


Sources can be viewed here - Github