Flutter Database Concepts

A database is an organized collection of data, which supports the storage and manipulation of data and accessed electronically from a computer system. We can organize data into rows, columns, tables, and indexes. It makes data management easy. We can store many things in the database, like name, age, picture, image, file, pdf, etc.

Flutter provides many packages to work with the database. The most used and popular packages are:

• sqflite database: It allows to access and manipulate SQLite database.
• Firebase database: It will enable you to access and manipulate the cloud database.

SQLite Database

SQLite is a popular database software library that provides a relational database management system for local/client storage. It is a light-weight and time-tested database engine and contains features like self-contained, server-less, zero-configuration, transactional SQL database engine.

Flutter SDK does not support SQLite directly. Instead, it provides a plugin sqflite, which performs all operations on the database as similar to the SQLite library. The sqflite provides most of the core functionalities related to the database are as follows:

• It creates or opens the SQLite database.
• It can execute SQL statements easily.
• It also provides an advanced query method to get information from the SQLite database.

Let us see step by step how we can store and fetch data in the Flutter.

Step 1: First, create a new project in Android Studio and add the dependencies in pubspec.yaml file.

1. dependencies:  
2.   flutter:  
3.     sdk: flutter  
4.   sqflite: any  
5.   path_provider: any  

• The sqflite package provides classes and functions to interact with the SQLite database.
• The path_provider package provides functions to define the location of your database on the local system, such as TemporaryDirectory and ApplicationDocumentsDirectory.

Step 2: Create a model class. In this step, we have to define the data that needs to be stored before creating a table to store information. The following code explains it easily.

1. class Book {  
2.   final int id;  
3.   final String title;  
4.   final int price;  
5.   
6.   Book({this.id, this.title, this.price});  
7. }  

Step 3: Open the database. Here, we need to open the connection to the database. It requires two steps:

1. Set the path to the database by using the getDtabasePath() method and combined it with the path package.
2. Use openDatabase() function to open the database.

1. // It allows to open the database and store the reference.  
2. final Future<Database> database = openDatabase(  
3.   join(await getDatabasesPath(), 'book_database.db'),  
4. );  

Step 4: Create the table. In this step, we have to create a table that stores information about the books. Here, we are going to create a table named books, which contains the id, title, and price of the books. They are represented as three columns in the book table.

1. final Future<Database> database = openDatabase(  
2.   join(await getDatabasesPath(), 'book_database.db'),  
3.   // When you create a database, it also needs to create a table to store books.  
4.   onCreate: (db, version) {  
5.     // Run the CREATE TABLE statement.  
6.     return db.execute(  
7.       "CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, price INTEGER)",  
8.     );  
9.   },  
10.   // Set the version to perform database upgrades and downgrades.  
11.   version: 1,  
12. );  

Step 5: Insert a Book into the database. Here, you have to store information on the table about the various books. Inserting a book into the table involves two steps:

• Convert the Book into a Map
• Uses insert() method

The following code explains it more clearly.

1. // Update the Book class.  
2. class Book{  
3.   final int id,  
4.   final String title;  
5.   final int price;  
6.   
7.   Book({this.id, this.title, this.price});  
8.   
9.   // It converts a Book into a Map. The keys correspond to the names of the columns in the database.  
10.   Map<String, dynamic> toMap() {  
11.     return {  
12.       'id': id,  
13.       'title': title,  
14.       'price': price,  
15.     };  
16.   }  
17. }  
18.   
19. Future<void> insertBook(Book book) async {  
20.   final Database db = await database;  
21.   await db.insert(  
22.     'books',  
23.     book.toMap(),  
24.     conflictAlgorithm: ConflictAlgorithm.replace,  
25.   );  
26. }  
27.   
28. // Create a Book and add it to the books table.  
29. final b1 = Book(  
30.   id: 0,  
31.   title: 'Let Us C',  
32.   price: 350,  
33. );  
34.   
35. await insertBook(b1);  

Step 6: Retrieve the list of books. Now, we have stored the book into the database, and you can use a query to retrieve a particular book or list of all books. It involves two steps:

• Run a query that returns List<Map>.
• Convert the List<Map> into the List<Book>.

You can see the following code to understand it easily.

1. // This method retrieves all the books from the books table.  
2. Future<List<Book>> books() async {  
3.   final Database db = await database;  
4.   
5.   // Use query for all Books.  
6.   final List<Map<String, dynamic>> maps = await db.query('maps');  
7.   
8.   return List.generate(maps.length, (i) {  
9.     return Book(  
10.       id: maps[i]['id'],  
11.       title: maps[i]['title'],  
12.       price: maps[i]['price'],  
13.     );  
14.   });  
15. }  
16.   
17. // It prints all the books.  
18. print(await books());  

Step 7: Update a Book in the database. You can use an update() method to update the book that you want. It involves two steps:

• Convert Book into the Map.
• Then, use where clause to update the book.

You can see the following code to understand it.

1. Future<void> updateBook(Book book) async {  
2.   final db = await database;  
3.   
4.   // Update the given Book.  
5.   await db.update(  
6.     'books',  
7.     book.toMap(),  
8.     // It ensure the matching id of a Book.  
9.     where: "id = ?",  
10.     whereArgs: [book.id],  
11.   );  
12. }  
13.   
14. // Update b1 price.  
15. await updateBook(Book(  
16.   id: 0,  
17.   title: 'Let Us C',  
18.   price: 420,  
19. ));  
20.   
21. // Print the updated results.  
22. print(await books());  

Step 8: Delete a Book from the database. You can use the delete() method to delete the database. For this, you need to make a function that takes an id and delete the database of the matching id.

1. Future<void> deleteBook(int id) async {  
2.   final db = await database;  
3.   
4.   // This function removes books from the database.  
5.   await db.delete(  
6.     'books',  
7.     where: "id = ?",  
8.     whereArgs: [id],  
9.   );  
10. }  

Let us see the complete code to understand how we can create a database file with sqflite plugin. For this, create a new Flutter project and the sqflite and path package into the pubspec.yaml file. Next, create a new file into the lib folder and insert the following code in this file. Now, connect the database with your UI and run the code.

1. import 'dart:async';  
2.   
3. import 'packprice:path/path.dart';  
4. import 'packprice:sqflite/sqflite.dart';  
5.   
6. void main() async {  
7.   final database = openDatabase(  
8.     join(await getDatabasesPath(), 'book_database.db'),  
9.     onCreate: (db, version) {  
10.       return db.execute(  
11.         "CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, price INTEGER)",  
12.       );  
13.     },  
14.     version: 1,  
15.   );  
16.   
17.   Future<void> insertBook(Book book) async {  
18.     // Get a reference to the database.  
19.     final Database db = await database;  
20.   
21.     await db.insert(  
22.       'books',  
23.       book.toMap(),  
24.       conflictAlgorithm: ConflictAlgorithm.replace,  
25.     );  
26.   }  
27.   
28.   Future<List<Book>> books() async {  
29.     final Database db = await database;  
30.   
31.     final List<Map<String, dynamic>> maps = await db.query('books');  
32.   
33.     return List.generate(maps.length, (i) {  
34.       return Book(  
35.         id: maps[i]['id'],  
36.         title: maps[i]['title'],  
37.         price: maps[i]['price'],  
38.       );  
39.     });  
40.   }  
41.   
42.   Future<void> updateBook(Book book) async {  
43.     final db = await database;  
44.     await db.update(  
45.       'books',  
46.       book.toMap(),  
47.       where: "id = ?",  
48.       whereArgs: [book.id],  
49.     );  
50.   }  
51.   
52.   Future<void> deleteBook(int id) async {  
53.     final db = await database;  
54.     await db.delete(  
55.       'books',  
56.       where: "id = ?",  
57.       whereArgs: [id],  
58.     );  
59.   }  
60.   
61.   var b1 = Book(  
62.     id: 0,  
63.     title: 'Let Us C',  
64.     price: 300,  
65.   );  
66.   
67.   await insertBook(b1);  
68.   
69.   print(await books());  
70.   
71.   b1 = Book(  
72.     id: b1.id,  
73.     title: b1.title,  
74.     price: b1.price,  
75.   );  
76.   await updateBook(b1);  
77.   
78.   print(await books());  
79.   
80.   await deleteBook(b1.id);  
81.   
82.   print(await books());  
83. }  
84.   
85. class Book {  
86.   final int id;  
87.   final String title;  
88.   final int price;  
89.   
90.   Book({this.id, this.title, this.price});  
91.   
92.   Map<String, dynamic> toMap() {  
93.     return {  
94.       'id': id,  
95.       'title': title,  
96.       'price': price,  
97.     };  
98.   }  
99.   @override  
100.   String toString() {  
101.     return 'Book{id: $id, title: $title, price: $price}';  
102.   }  
103. }