In this tutorial, we’re gonna look at how to make SQLite CRUD Operations in a Flutter App using sqflite plugin.
More Practice:
– Flutter SQLite example – CRUD Operations with ListView & sqflite plugin
With Firebase:
– Flutter Firebase Database example – Firebase Database CRUD with ListView
– Flutter Firestore example – Firebase Firestore CRUD with ListView
Contents
Flutter SQLite CRUD with sqflite
Import sqflite
Add sqflite plugin to the dependencies section of our pubspec.yaml:
dependencies: sqflite: <latest_version> |
Open a Database
First, we find a location path for the database using getDatabasesPath()
function. Then we call openDatabase()
with onCreate()
callback method where we populate data.
initDb() async { String databasesPath = await getDatabasesPath(); String path = join(databasesPath, 'notes.db'); 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)'); } |
CRUD Operations
Create an Item
Future<int> saveNote(Note note) async { var result = await db.insert(tableNote, note.toMap()); return result; } |
Or use raw Query:
Future<int> saveNote(Note note) async { var result = await db.rawInsert( 'INSERT INTO $tableNote ($columnTitle, $columnDescription) VALUES (\'${note.title}\', \'${note.description}\')' ); return result; } |
Read All Items
Future<List> getAllNotes() async { var result = await db.query(tableNote, columns: [columnId, columnTitle, columnDescription]); return result.toList(); } |
Or use raw Query:
Future<List> getAllNotes() async { var result = await db.rawQuery('SELECT * FROM $tableNote'); return result.toList(); } |
Read an Item
Future<Note> getNote(int id) async { List<Map> result = await db.query(tableNote, columns: [columnId, columnTitle, columnDescription], where: '$columnId = ?', whereArgs: [id]); if (result.length > 0) { return new Note.fromMap(result.first); } return null; } |
Or use raw Query:
Future<Note> getNote(int id) async { var result = await db.rawQuery('SELECT * FROM $tableNote WHERE $columnId = $id'); if (result.length > 0) { return new Note.fromMap(result.first); } return null; } |
Update an Item
Future<int> updateNote(Note note) async { return await db.update(tableNote, note.toMap(), where: "$columnId = ?", whereArgs: [note.id]); } |
Or use raw Query:
Future<int> updateNote(Note note) async { return await db.rawUpdate( 'UPDATE $tableNote SET $columnTitle = \'${note.title}\', $columnDescription = \'${note.description}\' WHERE $columnId = ${note.id}' ); } |
Delete an Item
Future<int> deleteNote(int id) async { return await db.delete(tableNote, where: '$columnId = ?', whereArgs: [id]); } |
Or use raw Query:
Future<int> deleteNote(int id) async { return await db.rawDelete('DELETE FROM $tableNote WHERE $columnId = $id'); } |
Close the Database
await db.close(); |
Practice
Import sqflite
Add sqflite plugin to the dependencies section of our pubspec.yaml:
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']; } } |
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_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(); } } |
Main for testing
lib/main.dart
import 'dart:async'; import 'package:sqlite_example/model/note.dart'; import 'package:sqlite_example/util//database_helper.dart'; Future main() async { List notes; var db = new DatabaseHelper(); await db.saveNote(new Note("Flutter Tutorials", "Create SQLite Tutorial")); await db.saveNote(new Note("Android Development", "Build Firebase Android Apps")); await db.saveNote(new Note("Mobile App R&D", "Research more cross-flatforms")); print('=== getAllNotes() ==='); notes = await db.getAllNotes(); notes.forEach((note) => print(note)); int count = await db.getCount(); print('Count: $count'); print('=== getNote(2) ==='); Note note = await db.getNote(2); print(note.toMap()); print('=== updateNote[id:1] ==='); Note updatedNote = Note.fromMap({'id': 1, 'title': 'Flutter Tuts', 'description': 'Create SQLite Tut'}); await db.updateNote(updatedNote); notes = await db.getAllNotes(); notes.forEach((note) => print(note)); print('=== deleteNote(2) ==='); await db.deleteNote(2); notes = await db.getAllNotes(); notes.forEach((note) => print(note)); await db.close(); } |
Run & Check Results
In Console:
I/flutter ( 3238): === getAllNotes() === I/flutter ( 3238): {id: 1, title: Flutter Tutorials, description: Create SQLite Tutorial} I/flutter ( 3238): {id: 2, title: Android Development, description: Build Firebase Android Apps} I/flutter ( 3238): {id: 3, title: Mobile App R&D, description: Research more cross-flatforms} I/flutter ( 3238): Count: 3 I/flutter ( 3238): === getNote(2) === I/flutter ( 3238): {id: 2, title: Android Development, description: Build Firebase Android Apps} I/flutter ( 3238): === updateNote[id:1] === I/flutter ( 3238): {id: 1, title: Flutter Tuts, description: Create SQLite Tut} I/flutter ( 3238): {id: 2, title: Android Development, description: Build Firebase Android Apps} I/flutter ( 3238): {id: 3, title: Mobile App R&D, description: Research more cross-flatforms} I/flutter ( 3238): === deleteNote(2) === I/flutter ( 3238): {id: 1, title: Flutter Tuts, description: Create SQLite Tut} I/flutter ( 3238): {id: 3, title: Mobile App R&D, description: Research more cross-flatforms} |
Why we use these codes :
Hi Furkan,
That’s the way we implement Singleton pattern in Dart.
Regards,
JSA.
i try to get note with this code:
print(‘=== getNote(2) ===’);
Note note = await db.getNote(2);
print(note.toMap());
but its returned NULL !!!
Thanks 🙂