Flutter & SQL

SQLite Database in Flutter

 

  • If you are working on an application in which some API gives the same supposed low-level response again and again then at that time there is no need to hit the API every time. So, to overcome this we store that data in some storage for eg: we use SharedPrefs(For storing low-level data) at that time. So suppose if we have some big data like user data then at that time local DB comes into role.

  • There are n number of local DB options available in flutter but in n number of databases options, the most valuable and easy to use database is “SQLite”.

  • In this article, we will discuss “How we can integrate SQLite database into our flutter application and perform some crud operations”.

  • So, to integrate SQLite we have to follow some steps which are given below: 

Add dependency or package into your pubspec.yaml file.

You can add something like this:

dependencies:

  flutter:

    sdk: flutter

  sqflite: ^2.0.0+3

  path_provider: any

Note: path_provider package is for accessing the path of the database where we have to save.

Keep in mind that at the time of your reading the version of the package may be changed. So, add the latest version from: https://pub.dev/packages/sqflite

Create a DatabaseHelper class and initialize the database

Create a private constructor of the DatabaseHelper class that can be used only inside this class.

class DatabaseHelper{

DatabaseHelper._();

static final DatabaseHelper dbHelper = DatabaseHelper._();

}

Now set up the database.

Future<Database> get database async {

if (_database != null) {

return _database;

}

_database = await initializeDatabase();

return _database;

}

Future<Database> initializeDatabase() async {

// Get the directory path for both Android and iOS to store the database.

Directory directory = await getApplicationDocumentsDirectory();

String path = directory.path + ourdb.db';

// Open/create the database at a given path

var ourDatabase = await openDatabase(path, version: 1, onCreate: _createDb);

return ourDatabase ;

}

void _createDb(Database db, int newVersion) async {


await db.execute('CREATE TABLE “MyTable”(“col_id” INTEGER PRIMARY KEY AUTOINCREMENT, “col_title” TEXT)’);


}

 

Now SQLite data work in a map, so you have to create a model class in which two methods will be there “toMap” and “fromMap”.

Now you are ready to perform CRUD operations:

SQLite gives the predefined methods to insert, update and delete the data. You can perform something like this.

So, to perform the insert operation you have to do something like this:

Future<int> insertTheData(Data data) async {

Database db = await this.database;

var result = await db.insert(data.toMap());

return result;

}

You have to just call this method where you want to use this insert function and you have to pass the data which you want to store in the argument.

For update the data:

Future<int> updateData(Data data) async {

var db = await this.database;

var result = await db.update(data.toMap(), where: '$colId = ?', whereArgs: [data.id]);

return result;

}

// you have to pass the column_id means for which id you want to update the data.

For delete the specific data:

Future<int> deleteData(int id) async {

var db = await this.database;

int result = await db.rawDelete('DELETE FROM MyTable WHERE $colId = $id');

return result;

}

// here you have to pass the column_id for which you have to delete the specific id.

For Read the data:

Future<PassTheTypeOfData> getTheData() async {

Database db = await this.database;

var result = await db.query(MyTable, orderBy: “priority” ASC');

return result;

}

So, it’s all about the integration of SQLite databases.

Leave a Reply