Using SQLite in Flutter

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. Configure 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 been created yet (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'm 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 start, we impose a 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 the user clicks on the checkbox, the client locks / unlocks

Now it’s very easy to add new features, for example, if we want to delete a client, at the moment when it’s added, simply 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 did a lot in this article, but in applications in the real world, initializing states in the UI layer is not a good idea. Let's separate logic from 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 gets 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 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 it for the future.

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

Now 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