Flutter SQLite example – CRUD operations with sqflite plugin

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

More:
– Reactjs Jwt SpringBoot Token Authentication Example
– React Node Jwt Authentication without Redux – using LocalStorage and Axios
– Angular 11 ElasticSearch Example – Start Guide
– Django Angular 10 CRUD Example
– Reactjs CRUD Firebase Realtime Database Example

Flutter SQLite CRUD with sqflite

Import sqflite

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


dependencies:
  sqflite:

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 saveNote(Note note) async {
  var result = await db.insert(tableNote, note.toMap());

  return result;
}

Or use raw Query:


Future 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 getAllNotes() async {
  var result = await db.query(tableNote, columns: [columnId, columnTitle, columnDescription]);

  return result.toList();
}

Or use raw Query:


Future getAllNotes() async {
  var result = await db.rawQuery('SELECT * FROM $tableNote');

  return result.toList();
}

Read an Item


Future getNote(int id) async {
  List 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 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 updateNote(Note note) async {
  return await db.update(tableNote, note.toMap(), where: "$columnId = ?", whereArgs: [note.id]);
}

Or use raw Query:


Future 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 deleteNote(int id) async {
  return await db.delete(tableNote, where: '$columnId = ?', whereArgs: [id]);
}

Or use raw Query:


Future 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 toMap() {
    var map = new Map();
    if (_id != null) {
      map['id'] = _id;
    }
    map['title'] = _title;
    map['description'] = _description;

    return map;
  }

  Note.fromMap(Map 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 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 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 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 getCount() async {
    var dbClient = await db;
    return Sqflite.firstIntValue(await dbClient.rawQuery('SELECT COUNT(*) FROM $tableNote'));
  }

  Future getNote(int id) async {
    var dbClient = await db;
    List 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 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 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}

Source Code

sqlite_example



By grokonez | July 16, 2018.

Last updated on April 23, 2021.


Related Posts

Post Tags


4 thoughts on “Flutter SQLite example – CRUD operations with sqflite plugin”

  1. Why we use these codes :

    static final DatabaseHelper _instance = new DatabaseHelper.internal();
factory DatabaseHelper() => _instance;
    Reply

  2. i try to get note with this code:
    print(‘=== getNote(2) ===’);
    Note note = await db.getNote(2);
    print(note.toMap());

    but its returned NULL !!!

    Reply

Got Something To Say:

Your email address will not be published. Required fields are marked *

*