Brand

Flutter Database Concepts

A database is an organized collection of large data, which supports us in the storage, access and manipulation of data. We can organize the data according to the need of the application. This makes data management easy.

Flutter has many packages to work with the database. One of the package to interact with the local database is:

  • sqflite database: It allows access and manipulation of local sqlite database.

SQLite Database

SQLite is a popular local relational database management system used in mobile devices. It’s a light-weight and time-tested database and it contains features like self-contained, server-less, zero-configuration, transactional SQL database engine.

Flutter SDK does not support SQLite directly. Instead, it can use sqflite package, which is similar to the SQLite library. The sqflite provides the core functionalities related to the sqlite database:

  • Creates or Opens the SQLite database.
  • Execute SQL statements easily.
  • Provides an advanced queries method to do sql operations in sqlite.

Let us see how we can work with data in Flutter.

Step 1: First, add the dependencies in pubspec.yaml file.

dependencies:  
  flutter:  
    sdk: flutter  
  sqflite: any  
  path_provider: any
  • The sqflite package provides classes and methods we can use to interact with the SQLite database.
  • The path_provider package provides functions to find the location of the database file on the local system, such as TemporaryDirectory and ApplicationDocumentsDirectory.

Step 2: Create a model class. We will define the data that we need for storing before creating a table to storing information.

class Book {  
  final int id;  
  final String title;  
  final int price;  
  
  Book({this.id, this.title, this.price});  
}

Step 3: Opening the database.

  1. Set the path of the database file using the getDtabasePath().
  2. Now use the openDatabase() function to open the database.
final Future database = openDatabase(  
  join(await getDatabasesPath(), 'book_database.db'),  
);

Step 4: Create the table.

final Future database = openDatabase(  
  join(await getDatabasesPath(), 'book_database.db'),  
  // When you create a database, it also needs to create a table to store books.  
  onCreate: (db, version) {  
    // Run the CREATE TABLE statement.  
    return db.execute(  
      "CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, price INTEGER)",  
    );  
  },  
  version: 1,  
);

Step 5: Insert a First element into the database.

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

The following code explains it more clearly.

// Update the Book class.  
class Book{  
  final int id,  
  final String title;  
  final int price;  
  
  Book({this.id, this.title, this.price});  
  
  // It converts a Book into a Map. The keys correspond to the names of the columns in the database.  
  Map toMap() {  
    return {  
      'id': id,  
      'title': title,  
      'price': price,  
    };  
  }  
}  
  
Future insertBook(Book book) async {  
  final Database db = await database;  
  await db.insert(  
    'books',  
    book.toMap(),  
    conflictAlgorithm: ConflictAlgorithm.replace,  
  );  
}  
  
// Create a Book and add it to the books table.  
final b1 = Book(  
  id: 0,  
  title: 'Let Us C',  
  price: 350,  
);  
  
await insertBook(b1);

Step 6: Retrieve the list of Objects.

  • Run a query that returns List
  • Convert the List into the List.

You can see the following code.

// This method retrieves all the books from the books table.  
Future> books() async {  
  final Database db = await database;  
  
  // Use query for all Books.  
  final List> maps = await db.query('maps');  
  
  return List.generate(maps.length, (i) {  
    return Book(  
      id: maps[i]['id'],  
      title: maps[i]['title'],  
      price: maps[i]['price'],  
    );  
  });  
}  
  
// It prints all the books.  
print(await books());

Step 7: Update an Object in the database

  • Convert Object into the Map.
  • Then, use where clause to update the object.

You can see the following code to understand it.

Future updateBook(Book book) async {  
  final db = await database;  
  
  // Update the given Book.  
  await db.update(  
    'books',  
    book.toMap(),  
    // It ensure the matching id of a Book.  
    where: "id = ?",  
    whereArgs: [book.id],  
  );  
}  
  
// Update b1 price.  
await updateBook(Book(  
  id: 0,  
  title: 'Let Us C',  
  price: 420,  
));  
  
// Print the updated results.  
print(await books());

Step 8: Delete an Object from the database. We can use the delete() method to delete the database. 

Future deleteBook(int id) async {  
  final db = await database;  
  
  // This function removes books from the database.  
  await db.delete(  
    'books',  
    where: "id = ?",  
    whereArgs: [id],  
  );  
}
Lets see the complete code.
import 'dart:async';  
  
import 'packprice:path/path.dart';  
import 'packprice:sqflite/sqflite.dart';  
  
void main() async {  
  final database = openDatabase(  
    join(await getDatabasesPath(), 'book_database.db'),  
    onCreate: (db, version) {  
      return db.execute(  
        "CREATE TABLE books(id INTEGER PRIMARY KEY, title TEXT, price INTEGER)",  
      );  
    },  
    version: 1,  
  );  
  
  Future insertBook(Book book) async {  
    // Get a reference to the database.  
    final Database db = await database;  
  
    await db.insert(  
      'books',  
      book.toMap(),  
      conflictAlgorithm: ConflictAlgorithm.replace,  
    );  
  }  
  
  Future> books() async {  
    final Database db = await database;  
  
    final List> maps = await db.query('books');  
  
    return List.generate(maps.length, (i) {  
      return Book(  
        id: maps[i]['id'],  
        title: maps[i]['title'],  
        price: maps[i]['price'],  
      );  
    });  
  }  
  
  Future updateBook(Book book) async {  
    final db = await database;  
    await db.update(  
      'books',  
      book.toMap(),  
      where: "id = ?",  
      whereArgs: [book.id],  
    );  
  }  
  
  Future deleteBook(int id) async {  
    final db = await database;  
    await db.delete(  
      'books',  
      where: "id = ?",  
      whereArgs: [id],  
    );  
  }  
  
  var b1 = Book(  
    id: 0,  
    title: 'Let Us C',  
    price: 300,  
  );  
  
  await insertBook(b1);  
  
  print(await books());  
  
  b1 = Book(  
    id: b1.id,  
    title: b1.title,  
    price: b1.price,  
  );  
  await updateBook(b1);  
  
  print(await books());  
  
  await deleteBook(b1.id);  
  
  print(await books());  
}  
  
class Book {  
  final int id;  
  final String title;  
  final int price;  
  
  Book({this.id, this.title, this.price});  
  
  Map toMap() {  
    return {  
      'id': id,  
      'title': title,  
      'price': price,  
    };  
  }  
  @override  
  String toString() {  
    return 'Book{id: $id, title: $title, price: $price}';  
  }  
}
Previous
Next
Subscribe Now