Flutter SQLite example - CRUD Operations with ListView & sqflite plugin

In previous post, we knew how to make SQLite CRUD Operations using sqflite plugin. In this tutorial, we’re gonna build a Flutter App that allows us to make CRUD interactions with SQLite in a ListView.

Firebase:
– Flutter Firebase Database example – Firebase Database CRUD with ListView
– Flutter Firestore example – Firebase Firestore CRUD with ListView

Contents

Example Overview
- - Goal
  - Project Structure
Practice
Source Code

Example Overview

Goal

Our Flutter App will retrieve list of Notes from SQLite Database and show it in ListView widget. Then we can click on Add button to create new Note, click on a Note item to update that Note, or delete a Note with remove Button.

flutter-sqlite-example-listview-sqflite-overview

Project Structure

flutter-sqlite-example-listview-sqflite-structure

– Note class (note.dart) is a Data Model.
– DatabaseHelper class (database_helper.dart) handles CRUD Operations with SQLite using sqflite plugin.
– ListViewNote (listview_note.dart) is a StatefulWidget with List<Note> is state variable. It shows the list using ListView.builder, sends/gets data to/from NoteScreen using Navigator.
– NoteScreen (note_screen.dart) is the user interface for adding/updating Note.
– We add sqflite dependency in pubspec.yaml.

For more detailsa about ListView and Navigator, please visit:
– Flutter ListView example with ListView.builder
– Flutter Navigator example – Send/Return data to/from new Screen

Practice

Import sqflite

Add sqflite plugin to the dependencies section of our pubspec.yaml:

dependencies:
  sqflite: "^0.11.0+1"

1 2	dependencies: sqflite: "^0.11.0+1"

Create Data Model

lib/model/note.dart

class Note {
  int _id;
  String _title;
  String _description;

  Note(this._title, this._description);

  Note.map(dynamic obj) {
    this._id = obj['id'];
    this._title = obj['title'];
    this._description = obj['description'];
  }

  int get id => _id;
  String get title => _title;
  String get description => _description;

  Map<String, dynamic> toMap() {
    var map = new Map<String, dynamic>();
    if (_id != null) {
      map['id'] = _id;
    }
    map['title'] = _title;
    map['description'] = _description;

    return map;
  }

  Note.fromMap(Map<String, dynamic> map) {
    this._id = map['id'];
    this._title = map['title'];
    this._description = map['description'];
  }
}

class Note {

int _id;

String _title;

String _description;

Note(this._title, this._description);

Note.map(dynamic obj) {

this._id = obj['id'];

this._title = obj['title'];

this._description = obj['description'];

}

int get id => _id;

String get title => _title;

String get description => _description;

Map<String, dynamic> toMap() {

var map = new Map<String, dynamic>();

if (_id != null) {

map['id'] = _id;

}

map['title'] = _title;

map['description'] = _description;

return map;

}

Note.fromMap(Map<String, dynamic> map) {

this._id = map['id'];

this._title = map['title'];

this._description = map['description'];

}

Create Database Helper Class

lib/util/database_helper.dart

import 'dart:async';

import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:sqlite_listview_example/model/note.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = new DatabaseHelper.internal();

  factory DatabaseHelper() => _instance;

  final String tableNote = 'noteTable';
  final String columnId = 'id';
  final String columnTitle = 'title';
  final String columnDescription = 'description';

  static Database _db;

  DatabaseHelper.internal();

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

    return _db;
  }

  initDb() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, 'notes.db');

//    await deleteDatabase(path); // just for testing

    var db = await openDatabase(path, version: 1, onCreate: _onCreate);
    return db;
  }

  void _onCreate(Database db, int newVersion) async {
    await db.execute(
        'CREATE TABLE $tableNote($columnId INTEGER PRIMARY KEY, $columnTitle TEXT, $columnDescription TEXT)');
  }

  Future<int> saveNote(Note note) async {
    var dbClient = await db;
    var result = await dbClient.insert(tableNote, note.toMap());
//    var result = await dbClient.rawInsert(
//        'INSERT INTO $tableNote ($columnTitle, $columnDescription) VALUES (\'${note.title}\', \'${note.description}\')');

    return result;
  }

  Future<List> getAllNotes() async {
    var dbClient = await db;
    var result = await dbClient.query(tableNote, columns: [columnId, columnTitle, columnDescription]);
//    var result = await dbClient.rawQuery('SELECT * FROM $tableNote');

    return result.toList();
  }

  Future<int> getCount() async {
    var dbClient = await db;
    return Sqflite.firstIntValue(await dbClient.rawQuery('SELECT COUNT(*) FROM $tableNote'));
  }

  Future<Note> getNote(int id) async {
    var dbClient = await db;
    List<Map> result = await dbClient.query(tableNote,
        columns: [columnId, columnTitle, columnDescription],
        where: '$columnId = ?',
        whereArgs: [id]);
//    var result = await dbClient.rawQuery('SELECT * FROM $tableNote WHERE $columnId = $id');

    if (result.length > 0) {
      return new Note.fromMap(result.first);
    }

    return null;
  }

  Future<int> deleteNote(int id) async {
    var dbClient = await db;
    return await dbClient.delete(tableNote, where: '$columnId = ?', whereArgs: [id]);
//    return await dbClient.rawDelete('DELETE FROM $tableNote WHERE $columnId = $id');
  }

  Future<int> updateNote(Note note) async {
    var dbClient = await db;
    return await dbClient.update(tableNote, note.toMap(), where: "$columnId = ?", whereArgs: [note.id]);
//    return await dbClient.rawUpdate(
//        'UPDATE $tableNote SET $columnTitle = \'${note.title}\', $columnDescription = \'${note.description}\' WHERE $columnId = ${note.id}');
  }

  Future close() async {
    var dbClient = await db;
    return dbClient.close();
  }
}

import 'dart:async';

import 'package:sqflite/sqflite.dart';

import 'package:path/path.dart';

import 'package:sqlite_listview_example/model/note.dart';

class DatabaseHelper {

static final DatabaseHelper _instance = new DatabaseHelper.internal();

factory DatabaseHelper() => _instance;

final String tableNote = 'noteTable';

final String columnId = 'id';

final String columnTitle = 'title';

final String columnDescription = 'description';

static Database _db;

DatabaseHelper.internal();

Future<Database> get db async {

if (_db != null) {

return _db;

}

_db = await initDb();

return _db;

}

initDb() async {

String databasesPath = await getDatabasesPath();

String path = join(databasesPath, 'notes.db');

// await deleteDatabase(path); // just for testing

var db = await openDatabase(path, version: 1, onCreate: _onCreate);

return db;

}

void _onCreate(Database db, int newVersion) async {

await db.execute(

'CREATE TABLE $tableNote($columnId INTEGER PRIMARY KEY, $columnTitle TEXT, $columnDescription TEXT)');

}

Future<int> saveNote(Note note) async {

var dbClient = await db;

var result = await dbClient.insert(tableNote, note.toMap());

// var result = await dbClient.rawInsert(

// 'INSERT INTO $tableNote ($columnTitle, $columnDescription) VALUES (\'${note.title}\', \'${note.description}\')');

return result;

}

Future<List> getAllNotes() async {

var dbClient = await db;

var result = await dbClient.query(tableNote, columns: [columnId, columnTitle, columnDescription]);

// var result = await dbClient.rawQuery('SELECT * FROM $tableNote');

return result.toList();

}

Future<int> getCount() async {

var dbClient = await db;

return Sqflite.firstIntValue(await dbClient.rawQuery('SELECT COUNT(*) FROM $tableNote'));

}

Future<Note> getNote(int id) async {

var dbClient = await db;

List<Map> result = await dbClient.query(tableNote,

columns: [columnId, columnTitle, columnDescription],

where: '$columnId = ?',

whereArgs: [id]);

// var result = await dbClient.rawQuery('SELECT * FROM $tableNote WHERE $columnId = $id');

if (result.length > 0) {

return new Note.fromMap(result.first);

}

return null;

}

Future<int> deleteNote(int id) async {

var dbClient = await db;

return await dbClient.delete(tableNote, where: '$columnId = ?', whereArgs: [id]);

// return await dbClient.rawDelete('DELETE FROM $tableNote WHERE $columnId = $id');

}

Future<int> updateNote(Note note) async {

var dbClient = await db;

return await dbClient.update(tableNote, note.toMap(), where: "$columnId = ?", whereArgs: [note.id]);

// return await dbClient.rawUpdate(

// 'UPDATE $tableNote SET $columnTitle = \'${note.title}\', $columnDescription = \'${note.description}\' WHERE $columnId = ${note.id}');

}

Future close() async {

var dbClient = await db;

return dbClient.close();

}

Create UI Widgets

lib/ui/listview_note.dart

import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/model/note.dart';
import 'package:sqlite_listview_example/util/database_helper.dart';
import 'package:sqlite_listview_example/ui/note_screen.dart';

class ListViewNote extends StatefulWidget {
  @override
  _ListViewNoteState createState() => new _ListViewNoteState();
}

class _ListViewNoteState extends State<ListViewNote> {
  List<Note> items = new List();
  DatabaseHelper db = new DatabaseHelper();

  @override
  void initState() {
    super.initState();

    db.getAllNotes().then((notes) {
      setState(() {
        notes.forEach((note) {
          items.add(Note.fromMap(note));
        });
      });
    });
  }

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'JSA ListView Demo',
      home: Scaffold(
        appBar: AppBar(
          title: Text('ListView Demo'),
          centerTitle: true,
          backgroundColor: Colors.blue,
        ),
        body: Center(
          child: ListView.builder(
              itemCount: items.length,
              padding: const EdgeInsets.all(15.0),
              itemBuilder: (context, position) {
                return Column(
                  children: <Widget>[
                    Divider(height: 5.0),
                    ListTile(
                      title: Text(
                        '${items[position].title}',
                        style: TextStyle(
                          fontSize: 22.0,
                          color: Colors.deepOrangeAccent,
                        ),
                      ),
                      subtitle: Text(
                        '${items[position].description}',
                        style: new TextStyle(
                          fontSize: 18.0,
                          fontStyle: FontStyle.italic,
                        ),
                      ),
                      leading: Column(
                        children: <Widget>[
                          Padding(padding: EdgeInsets.all(10.0)),
                          CircleAvatar(
                            backgroundColor: Colors.blueAccent,
                            radius: 15.0,
                            child: Text(
                              '${items[position].id}',
                              style: TextStyle(
                                fontSize: 22.0,
                                color: Colors.white,
                              ),
                            ),
                          ),
                          IconButton(
                              icon: const Icon(Icons.remove_circle_outline),
                              onPressed: () => _deleteNote(context, items[position], position)),
                        ],
                      ),
                      onTap: () => _navigateToNote(context, items[position]),
                    ),
                  ],
                );
              }),
        ),
        floatingActionButton: FloatingActionButton(
          child: Icon(Icons.add),
          onPressed: () => _createNewNote(context),
        ),
      ),
    );
  }

  void _deleteNote(BuildContext context, Note note, int position) async {
    db.deleteNote(note.id).then((notes) {
      setState(() {
        items.removeAt(position);
      });
    });
  }

  void _navigateToNote(BuildContext context, Note note) async {
    String result = await Navigator.push(
      context,
      MaterialPageRoute(builder: (context) => NoteScreen(note)),
    );

    if (result == 'update') {
      db.getAllNotes().then((notes) {
        setState(() {
          items.clear();
          notes.forEach((note) {
            items.add(Note.fromMap(note));
          });
        });
      });
    }
  }

  void _createNewNote(BuildContext context) async {
    String result = await Navigator.push(
      context,
      MaterialPageRoute(builder: (context) => NoteScreen(Note('', ''))),
    );

    if (result == 'save') {
      db.getAllNotes().then((notes) {
        setState(() {
          items.clear();
          notes.forEach((note) {
            items.add(Note.fromMap(note));
          });
        });
      });
    }
  }
}

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

129

130

131

132

133

134

135

136

137

import 'package:flutter/material.dart';

import 'package:sqlite_listview_example/model/note.dart';

import 'package:sqlite_listview_example/util/database_helper.dart';

import 'package:sqlite_listview_example/ui/note_screen.dart';

class ListViewNote extends StatefulWidget {

@override

_ListViewNoteState createState() => new _ListViewNoteState();

}

class _ListViewNoteState extends State<ListViewNote> {

List<Note> items = new List();

DatabaseHelper db = new DatabaseHelper();

@override

void initState() {

super.initState();

db.getAllNotes().then((notes) {

setState(() {

notes.forEach((note) {

items.add(Note.fromMap(note));

});

}

@override

Widget build(BuildContext context) {

return MaterialApp(

title: 'JSA ListView Demo',

home: Scaffold(

appBar: AppBar(

title: Text('ListView Demo'),

centerTitle: true,

backgroundColor: Colors.blue,

body: Center(

child: ListView.builder(

itemCount: items.length,

padding: const EdgeInsets.all(15.0),

itemBuilder: (context, position) {

return Column(

children: <Widget>[

Divider(height: 5.0),

ListTile(

title: Text(

'${items[position].title}',

style: TextStyle(

fontSize: 22.0,

color: Colors.deepOrangeAccent,

subtitle: Text(

'${items[position].description}',

style: new TextStyle(

fontSize: 18.0,

fontStyle: FontStyle.italic,

leading: Column(

children: <Widget>[

Padding(padding: EdgeInsets.all(10.0)),

CircleAvatar(

backgroundColor: Colors.blueAccent,

radius: 15.0,

child: Text(

'${items[position].id}',

style: TextStyle(

fontSize: 22.0,

color: Colors.white,

IconButton(

icon: const Icon(Icons.remove_circle_outline),

onPressed: () => _deleteNote(context, items[position], position)),

onTap: () => _navigateToNote(context, items[position]),

);

}),

floatingActionButton: FloatingActionButton(

child: Icon(Icons.add),

onPressed: () => _createNewNote(context),

);

}

void _deleteNote(BuildContext context, Note note, int position) async {

db.deleteNote(note.id).then((notes) {

setState(() {

items.removeAt(position);

});

}

void _navigateToNote(BuildContext context, Note note) async {

String result = await Navigator.push(

context,

MaterialPageRoute(builder: (context) => NoteScreen(note)),

);

if (result == 'update') {

db.getAllNotes().then((notes) {

setState(() {

items.clear();

notes.forEach((note) {

items.add(Note.fromMap(note));

});

}

void _createNewNote(BuildContext context) async {

String result = await Navigator.push(

context,

MaterialPageRoute(builder: (context) => NoteScreen(Note('', ''))),

);

if (result == 'save') {

db.getAllNotes().then((notes) {

setState(() {

items.clear();

notes.forEach((note) {

items.add(Note.fromMap(note));

});

}

lib/ui/note_screen.dart

import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/model/note.dart';
import 'package:sqlite_listview_example/util/database_helper.dart';

class NoteScreen extends StatefulWidget {
  final Note note;
  NoteScreen(this.note);

  @override
  State<StatefulWidget> createState() => new _NoteScreenState();
}

class _NoteScreenState extends State<NoteScreen> {
  DatabaseHelper db = new DatabaseHelper();

  TextEditingController _titleController;
  TextEditingController _descriptionController;

  @override
  void initState() {
    super.initState();

    _titleController = new TextEditingController(text: widget.note.title);
    _descriptionController = new TextEditingController(text: widget.note.description);
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Note')),
      body: Container(
        margin: EdgeInsets.all(15.0),
        alignment: Alignment.center,
        child: Column(
          children: <Widget>[
            TextField(
              controller: _titleController,
              decoration: InputDecoration(labelText: 'Title'),
            ),
            Padding(padding: new EdgeInsets.all(5.0)),
            TextField(
              controller: _descriptionController,
              decoration: InputDecoration(labelText: 'Description'),
            ),
            Padding(padding: new EdgeInsets.all(5.0)),
            RaisedButton(
              child: (widget.note.id != null) ? Text('Update') : Text('Add'),
              onPressed: () {
                if (widget.note.id != null) {
                  db.updateNote(Note.fromMap({
                    'id': widget.note.id,
                    'title': _titleController.text,
                    'description': _descriptionController.text
                  })).then((_) {
                    Navigator.pop(context, 'update');
                  });
                }else {
                  db.saveNote(Note(_titleController.text, _descriptionController.text)).then((_) {
                    Navigator.pop(context, 'save');
                  });
                }
              },
            ),
          ],
        ),
      ),
    );
  }
}

import 'package:flutter/material.dart';

import 'package:sqlite_listview_example/model/note.dart';

import 'package:sqlite_listview_example/util/database_helper.dart';

class NoteScreen extends StatefulWidget {

final Note note;

NoteScreen(this.note);

@override

State<StatefulWidget> createState() => new _NoteScreenState();

}

class _NoteScreenState extends State<NoteScreen> {

DatabaseHelper db = new DatabaseHelper();

TextEditingController _titleController;

TextEditingController _descriptionController;

@override

void initState() {

super.initState();

_titleController = new TextEditingController(text: widget.note.title);

_descriptionController = new TextEditingController(text: widget.note.description);

}

@override

Widget build(BuildContext context) {

return Scaffold(

appBar: AppBar(title: Text('Note')),

body: Container(

margin: EdgeInsets.all(15.0),

alignment: Alignment.center,

child: Column(

children: <Widget>[

TextField(

controller: _titleController,

decoration: InputDecoration(labelText: 'Title'),

Padding(padding: new EdgeInsets.all(5.0)),

TextField(

controller: _descriptionController,

decoration: InputDecoration(labelText: 'Description'),

Padding(padding: new EdgeInsets.all(5.0)),

RaisedButton(

child: (widget.note.id != null) ? Text('Update') : Text('Add'),

onPressed: () {

if (widget.note.id != null) {

db.updateNote(Note.fromMap({

'id': widget.note.id,

'title': _titleController.text,

'description': _descriptionController.text

})).then((_) {

Navigator.pop(context, 'update');

});

}else {

db.saveNote(Note(_titleController.text, _descriptionController.text)).then((_) {

Navigator.pop(context, 'save');

});

}

);

}

Main App

lib/main.dart

import 'package:flutter/material.dart';
import 'package:sqlite_listview_example/ui/listview_note.dart';

void main() => runApp(
      MaterialApp(
        title: 'Returning Data',
        home: ListViewNote(),
      ),
    );

import 'package:flutter/material.dart';

import 'package:sqlite_listview_example/ui/listview_note.dart';

void main() => runApp(

MaterialApp(

title: 'Returning Data',

home: ListViewNote(),

);

Source Code

sqlite_listview_example

By grokonez | July 21, 2018.

5 thoughts on “Flutter SQLite example – CRUD Operations with ListView & sqflite plugin”

enhusld says:

July 26, 2018 at 4:02 am

Hello. waiting new tutorial…

1. grokonez says:
  
  July 29, 2018 at 2:39 am
  
  Hi enhusld,
  
  Thank you for your visit 🙂
  We will post as soon as possible.
  
  Regards,
  grokonez.
  
Danny Van Geyte says:

October 15, 2018 at 12:00 pm

Starting flutter, and found the sample i just needed.
Going to try it…

Shubham says:

January 30, 2019 at 3:36 am

hey .. Great Post Buddy..
Can you tell me where should we close our DB connection.

Thanks.

1. grokonez says:
  
  January 30, 2019 at 9:24 am
  
  Hi Shubham,
  
  You can close DB connection inside dispose() method 🙂
  
  Regards,
  grokonez.

Flutter SQLite example – CRUD Operations with ListView & sqflite plugin

Example Overview

Goal

Project Structure

Practice

Import sqflite

Create Data Model

Create Database Helper Class

Create UI Widgets

Main App

Source Code

Post Tags

5 thoughts on “Flutter SQLite example – CRUD Operations with ListView & sqflite plugin”

Got Something To Say: Cancel reply

Example Overview

Goal

Project Structure

Practice

Import sqflite

Create Data Model

Create Database Helper Class

Create UI Widgets

Main App

Source Code

Related Posts

Post Tags

5 thoughts on “Flutter SQLite example – CRUD Operations with ListView & sqflite plugin”

Got Something To Say: Cancel reply