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

Contents

Flutter SQLite CRUD with sqflite
Practice
Source Code

Flutter SQLite CRUD with sqflite

Import sqflite

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

dependencies:
  sqflite: <latest_version>

1 2	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)');
}

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;
}

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;
}

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();
}

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();
}

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;
}

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;
}

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]);
}

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}'
  );
}

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]);
}

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');
}

Future<int> deleteNote(int id) async {

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

}

Close the Database

await db.close();

1	await db.close();

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_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_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();
}

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}

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.

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

Furkan says:

July 19, 2018 at 10:55 pm

Why we use these codes :

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

1
2

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

1. JavaSampleApproach says:
  
  July 20, 2018 at 8:47 am
  
  Hi Furkan,
  
  That’s the way we implement Singleton pattern in Dart.
  
  Regards,
  JSA.
  
david says:

November 9, 2018 at 11:26 pm

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

but its returned NULL !!!

Visal says:

December 18, 2018 at 5:53 am

Thanks 🙂

Flutter SQLite example – CRUD operations with sqflite plugin

Flutter SQLite CRUD with sqflite

Import sqflite

Open a Database

CRUD Operations

Create an Item

Read All Items

Read an Item

Update an Item

Delete an Item

Close the Database

Practice

Import sqflite

Create Data Model

Create Database Helper Class

Main for testing

Run & Check Results

Source Code

Post Tags

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

Got Something To Say: Cancel reply

Flutter SQLite CRUD with sqflite

Import sqflite

Open a Database

CRUD Operations

Create an Item

Read All Items

Read an Item

Update an Item

Delete an Item

Close the Database

Practice

Import sqflite

Create Data Model

Create Database Helper Class

Main for testing

Run & Check Results

Source Code

Related Posts

Post Tags

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

Got Something To Say: Cancel reply