Flutter sqflite database

In this blog, we are going to learn about Flutter sqflite database integration. the SQLite plugin for the flutter application. We create a demo app in which we have an edit text for the values, a button at this button tap we add the values to the database and also we use a list view to show all the values from the database. We also add a delete button in the listview item view on the press of this delete button that item value will be deleted from the database.

Check out more about our Flutter app development company.

I hope by this example you will know about creating the database, insert query, delete query, getting all the values from the database.

Sqflite is a plugin for flutter to integrate the SQLite database in flutter projects.

Getting Started :

First of all, you have to install the dependency in your pubspec.yaml l file in your flutter project.

dependencies:
  ...
  sqflite: ^1.3.0

dependencies:

...

sqflite: ^1.3.0

There are some more useful dependencies to integrate the sqflite database.

dependencies: 
 ... 
 sqflite: ^1.3.0  
 path_provider: ^2.0.2
 path: ^1.8.0

dependencies:

...

sqflite: ^1.3.0

path_provider: ^2.0.2

path: ^1.8.0

We have added path_provider dependency which is used for finding commonly used locations on the filesystem. Supports iOS, Android, Linux, and macOS. Not all methods are supported on all platforms.

We also use the path plugin which is used for manipulating paths: joining, splitting, normalizing, etc.

After that, we are going to create a helper class (DBHelper.dart) in this we are creating the database in this class we have some methods to perform queries on the database.

getTextList() => To get all the values that is inserted in database it will return Future<List<Map>>

addText()=> addText method to insert the values in the database it will return Future<String>.

deleteText()=> deleteText to perform delete query.

DBHelper.dart :

import 'dart:io';

import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';

/**
 * Created by Anand_Kashyap on 27/5/21.
 */


class DBHelper {
  static Database _db;
  static const int DATABASE_VERSION = 1;
  static const String DATABASE_NAME = "OfflineDatabase.db";
  static const String TABLE_NAME = "SEARCH_TABLE";
  static const String TABLE_COLUMN_ID = "id";
  static const String TABLE_COLUMN_TEXT= "search";

  DBHelper._privateConstructor();
  static final DBHelper instance = DBHelper._privateConstructor();

  Future<Database> get db async {
    if (_db != null) {
      return _db;
    }
    _db = await initDb();
    return _db;
  }

  //Creating a database with name test.dn in your directory
  initDb() async {
    Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, DATABASE_NAME);
    var theDb = await openDatabase(path,
        version: DATABASE_VERSION, onCreate: _onCreate);
    return theDb;
  }

  // Creating a tables
  void _onCreate(Database db, int version) async {
    var onCreateRecentSearchDataTable =
        "CREATE TABLE $TABLE_NAME (" +
            "$TABLE_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT ," +
            " $TABLE_COLUMN_TEXT TEXT)";
    await db.execute(onCreateRecentSearchDataTable);
  }

  // To get all the values that is inserted in database
  Future<List<Map>> getTextList() async {
    var dbClient = await db;
    List<Map<String, dynamic>> list = await dbClient.query(TABLE_NAME);
    return list;
  }

  // addText method to inset the values in database.
  Future<String> addText( Map<String, dynamic> search) async{
    var dbClient = await db;
    try{
        await dbClient.insert(TABLE_NAME, search);
    }catch (e) {
      print("Error--> $e");
    }
  }

  // deleteText to perform delete query.
  deleteText(String productId) async {
    var dbClient = await db;
    return await dbClient.delete(TABLE_NAME,
        where: '$TABLE_COLUMN_TEXT = ?',
        whereArgs: [productId]);
  }
}

import 'dart:io';

import 'package:path/path.dart';

import 'package:path_provider/path_provider.dart';

import 'package:sqflite/sqflite.dart';

/**

* Created by Anand_Kashyap on 27/5/21.

class DBHelper {

static Database _db;

static const int DATABASE_VERSION = 1;

static const String DATABASE_NAME = "OfflineDatabase.db";

static const String TABLE_NAME = "SEARCH_TABLE";

static const String TABLE_COLUMN_ID = "id";

static const String TABLE_COLUMN_TEXT= "search";

DBHelper._privateConstructor();

static final DBHelper instance = DBHelper._privateConstructor();

Future<Database> get db async {

if (_db != null) {

return _db;

}

_db = await initDb();

return _db;

}

//Creating a database with name test.dn in your directory

initDb() async {

Directory documentsDirectory = await getApplicationDocumentsDirectory();

String path = join(documentsDirectory.path, DATABASE_NAME);

var theDb = await openDatabase(path,

version: DATABASE_VERSION, onCreate: _onCreate);

return theDb;

}

// Creating a tables

void _onCreate(Database db, int version) async {

var onCreateRecentSearchDataTable =

"CREATE TABLE $TABLE_NAME (" +

"$TABLE_COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT ," +

" $TABLE_COLUMN_TEXT TEXT)";

await db.execute(onCreateRecentSearchDataTable);

}

// To get all the values that is inserted in database

Future<List<Map>> getTextList() async {

var dbClient = await db;

List<Map<String, dynamic>> list = await dbClient.query(TABLE_NAME);

return list;

}

// addText method to inset the values in database.

Future<String> addText( Map<String, dynamic> search) async{

var dbClient = await db;

try{

await dbClient.insert(TABLE_NAME, search);

}catch (e) {

print("Error--> $e");

}

// deleteText to perform delete query.

deleteText(String productId) async {

var dbClient = await db;

return await dbClient.delete(TABLE_NAME,

where: '$TABLE_COLUMN_TEXT = ?',

whereArgs: [productId]);

}

After this DBHelper class, we just have to make the view. In this view, we have an edit text that is for values that we want to add to the database. Below of the edit text we have and the list view that will iterate the values that is stored in the database. Last thing we have a floating action button on this button tap we will insert the value in the database.

main.dart :

import 'package:flutter/material.dart';
import 'package:flutter/services.dart';
import 'package:sqflite/sqflite.dart';

import 'DBHelper.dart';

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Flutter Demo',
      theme: ThemeData(
        // This is the theme of your application.
        //
        // Try running your application with "flutter run". You'll see the
        // application has a blue toolbar. Then, without quitting the app, try
        // changing the primarySwatch below to Colors.green and then invoke
        // "hot reload" (press "r" in the console where you ran "flutter run",
        // or simply save your changes to "hot reload" in a Flutter IDE).
        // Notice that the counter didn't reset back to zero; the application
        // is not restarted.
        primarySwatch: Colors.blue,
      ),
      home: MyHomePage(title: 'Flutter Demo Home Page'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  // This widget is the home page of your application. It is stateful, meaning
  // that it has a State object (defined below) that contains fields that affect
  // how it looks.

  // This class is the configuration for the state. It holds the values (in this
  // case the title) provided by the parent (in this case the App widget) and
  // used by the build method of the State. Fields in a Widget subclass are
  // always marked "final".

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  int _counter = 0;
  String searchText;
  TextEditingController _searchQuery = new TextEditingController();
  Future<List<Map<dynamic, dynamic>>> _RecentSearch;
  List<Map<dynamic, dynamic>> searchList = List();


  @override
  void initState() {
    // TODO: implement initState
    super.initState();
    getSearchFromDB();
  }

  void _addText() {
    setState(() {
      searchText = _searchQuery.text;
    });
    DBHelper.instance.addText({
      DBHelper.TABLE_COLUMN_TEXT:
      searchText
    });
    getSearchFromDB();
  }


  void getSearchFromDB() {
    _RecentSearch = DBHelper.instance.getTextList();
    _RecentSearch.then((value) => this.setState(() {
      searchList = value;
    }));
  }


  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Center(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            Container(child: TextField(
              controller: _searchQuery,
            ),margin: EdgeInsets.all(10),)
            ,
        Expanded(child:
        ListView.builder(
            itemCount: searchList.length,
            itemBuilder: (BuildContext context,int index){
              return ListTile(
                  leading: Icon(Icons.list),
                  trailing: GestureDetector(child: Icon(Icons.remove_circle_outline), onTap: ()=>{DBHelper.instance
                      .deleteText(
                      searchList[index]
                      [DBHelper
                          .TABLE_COLUMN_TEXT]),
                    getSearchFromDB()}),
                  title:Text(searchList[index][DBHelper.TABLE_COLUMN_TEXT]),
              );
            }
        ),
        )
          ],
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addText,
        tooltip: 'Increment',
        child: Icon(Icons.add),
      ), // This trailing comma makes auto-formatting nicer for build methods.
    );
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

import 'package:flutter/material.dart';

import 'package:flutter/services.dart';

import 'package:sqflite/sqflite.dart';

import 'DBHelper.dart';

void main() {

runApp(MyApp());

}

class MyApp extends StatelessWidget {

// This widget is the root of your application.

@override

Widget build(BuildContext context) {

return MaterialApp(

title: 'Flutter Demo',

theme: ThemeData(

// This is the theme of your application.

// Try running your application with "flutter run". You'll see the

// application has a blue toolbar. Then, without quitting the app, try

// changing the primarySwatch below to Colors.green and then invoke

// "hot reload" (press "r" in the console where you ran "flutter run",

// or simply save your changes to "hot reload" in a Flutter IDE).

// Notice that the counter didn't reset back to zero; the application

// is not restarted.

primarySwatch: Colors.blue,

home: MyHomePage(title: 'Flutter Demo Home Page'),

);

}

class MyHomePage extends StatefulWidget {

MyHomePage({Key key, this.title}) : super(key: key);

// This widget is the home page of your application. It is stateful, meaning

// that it has a State object (defined below) that contains fields that affect

// how it looks.

// This class is the configuration for the state. It holds the values (in this

// case the title) provided by the parent (in this case the App widget) and

// used by the build method of the State. Fields in a Widget subclass are

// always marked "final".

final String title;

@override

_MyHomePageState createState() => _MyHomePageState();

}

class _MyHomePageState extends State<MyHomePage> {

int _counter = 0;

String searchText;

TextEditingController _searchQuery = new TextEditingController();

Future<List<Map<dynamic, dynamic>>> _RecentSearch;

List<Map<dynamic, dynamic>> searchList = List();

@override

void initState() {

// TODO: implement initState

super.initState();

getSearchFromDB();

}

void _addText() {

setState(() {

searchText = _searchQuery.text;

});

DBHelper.instance.addText({

DBHelper.TABLE_COLUMN_TEXT:

searchText

});

getSearchFromDB();

}

void getSearchFromDB() {

_RecentSearch = DBHelper.instance.getTextList();

_RecentSearch.then((value) => this.setState(() {

searchList = value;

}));

}

@override

Widget build(BuildContext context) {

return Scaffold(

appBar: AppBar(

title: Text(widget.title),

body: Center(

child: Column(

mainAxisAlignment: MainAxisAlignment.center,

children: <Widget>[

Container(child: TextField(

controller: _searchQuery,

),margin: EdgeInsets.all(10),)

Expanded(child:

ListView.builder(

itemCount: searchList.length,

itemBuilder: (BuildContext context,int index){

return ListTile(

leading: Icon(Icons.list),

trailing: GestureDetector(child: Icon(Icons.remove_circle_outline), onTap: ()=>{DBHelper.instance

.deleteText(

searchList[index]

[DBHelper

.TABLE_COLUMN_TEXT]),

getSearchFromDB()}),

title:Text(searchList[index][DBHelper.TABLE_COLUMN_TEXT]),

);

}

)

floatingActionButton: FloatingActionButton(

onPressed: _addText,

tooltip: 'Increment',

child: Icon(Icons.add),

), // This trailing comma makes auto-formatting nicer for build methods.

);

}

In conclusion, We have completed Flutter sqflite database integration blog.

Hope this blog will help you to integrate sqflite database.

SQFlte –> https://pub.dev/packages/sqflite

Path Provider–> https://pub.dev/packages/path_provider

Path–> https://pub.dev/packages/path

Anand Kashyap

28 Apr, 2023
Updated by - anchit
31 May, 2021
Created by - Anand Kashyap

. . .

Flutter sqflite database