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

101 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;
    
  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 !!!

  3. I think other web site proprietors should take this web site as an model, very clean and wonderful user genial style and design, let alone the content. You are an expert in this topic!

  4. It is perfect time to make some plans for the future and it’s time to be happy. I’ve read this post and if I could I wish to suggest you some interesting things or tips. Perhaps you can write next articles referring to this article. I desire to read even more things about it!

  5. I have been exploring for a little bit for any high quality articles or weblog posts in this kind of space . Exploring in Yahoo I at last stumbled upon this website. Studying this info So i am glad to express that I have a very good uncanny feeling I found out just what I needed. I so much surely will make certain to do not fail to remember this website and give it a look regularly.

  6. Today, I went to the beach front with my kids. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone!

  7. I discovered your weblog site on google and verify a few of your early posts. Continue to keep up the superb operate. I simply additional up your RSS feed to my MSN Information Reader. Looking for ahead to studying more from you in a while!?

  8. Thanks for your write-up on this website. From my experience, often times softening upwards a photograph may provide the photography with a chunk of an imaginative flare. More often than not however, that soft clouds isn’t just what exactly you had at heart and can sometimes spoil a normally good image, especially if you intend on enlarging that.

  9. Thanks for this wonderful article. One other thing is that a lot of digital cameras can come equipped with a new zoom lens so that more or less of that scene to be included by ‘zooming’ in and out. All these changes in the aim length are generally reflected in the viewfinder and on significant display screen right at the back of the camera.

  10. You really make it appear really easy with your presentation but I find this matter to be actually something which I feel I would by no means understand. It seems too complex and very wide for me. I’m taking a look ahead for your next publish, I抣l attempt to get the cling of it!

  11. Whats up this is somewhat of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML. I’m starting a blog soon but have no coding expertise so I wanted to get advice from someone with experience. Any help would be greatly appreciated!

  12. 830322 843150Hi. Cool article. Theres a issue with the site in chrome, and you might want to check this The browser could be the marketplace chief and a big component of other folks will miss your exceptional writing due to this issue. I like your Post and I am recommend it for a Website Award. 392993

  13. Wow that was unusual. I just wrote an extremely long comment but after I clicked submit my comment didn’t appear.
    Grrrr… well I’m not writing all that over again. Anyways, just wanted to say excellent blog!

  14. A person necessarily assist to make critically articles I would state.
    This is the first time I frequented your web page and
    so far? I surprised with the analysis you made to make this actual post amazing.
    Wonderful job!

  15. I am really loving the theme/design of your blog. Do you ever run into any web browser compatibility problems?
    A handful of my blog visitors have complained about my website not operating correctly in Explorer but looks great in Opera.
    Do you have any ideas to help fix this issue?

  16. I’m truly enjoying the design and layout of your site. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a developer to create your theme? Excellent work!|

  17. Excellent read, I just passed this onto a friend who was doing a little research on that. And he just bought me lunch because I found it for him smile So let me rephrase that: Thank you for lunch! “We have two ears and one mouth so that we can listen twice as much as we speak.” by Epictetus.

  18. You could certainly see your skills in the paintings you write. The world hopes for more passionate writers like you who aren’t afraid to say how they believe. At all times follow your heart. “What power has law where only money rules.” by Gaius Petronius.

  19. Normally I do not read post on blogs, however I would like to say that this write-up very pressured me to try and do it! Your writing style has been surprised me. Thank you, quite great post.

  20. you are in point of fact a just right webmaster. The website loading pace is amazing. It kind of feels that you’re doing any distinctive trick. Moreover, The contents are masterwork. you have done a excellent activity on this topic!

  21. Appreciate it for helping out, great information. “If you would convince a man that he does wrong, do right. Men will believe what they see.” by Henry David Thoreau.

  22. Hello, you used to write great, but the last few posts have been kinda boring… I miss your super writings. Past few posts are just a little bit out of track! come on!

  23. I’m truly enjoying the design and layout of your
    website. It’s a very easy on the eyes which makes it much more
    enjoyable for me to come here and visit more often. Did you hire out a developer to
    create your theme? Excellent work!

  24. My brother suggested I might like this web site. He was totally right. This put up actually made my day. You can not believe simply how so much time I had spent for this info! Thanks!|

  25. Some truly nice and utilitarian info on this web site, besides I believe the design and style has got good features.

  26. Good day very nice web site!! Man .. Excellent .. Amazing .. I’ll bookmark your site and take the feeds also…I’m satisfied to find a lot of helpful information right here in the publish, we want work out more strategies on this regard, thanks for sharing.

  27. I have recently started a website, the information you offer on this site has helped me tremendously. Thank you for all of your time & work. “Character is much easier kept than recovered.” by Thomas Paine.

  28. you are truly a just right webmaster. The web site loading pace is amazing. It kind of feels that you’re doing any distinctive trick. Also, The contents are masterpiece. you’ve performed a excellent job in this topic!

  29. I will right away clutch your rss as I can not find your email subscription link or e-newsletter service. Do you’ve any? Please allow me know in order that I may just subscribe. Thanks.

  30. Its excellent as your other content : D, appreciate it for posting . “As experience widens, one begins to see how much upon a level all human things are.” by Joseph Farrell.

  31. It is in reality a great and useful piece of information. I am happy that you simply shared this helpful info with us. Please keep us up to date like this. Thanks for sharing.

  32. naturally like your web site but you have to check the spelling on several of your posts. Many of them are rife with spelling issues and I in finding it very bothersome to inform the truth however I will surely come back again.

  33. Great – I should definitely pronounce, impressed with your site. I had no trouble navigating through all tabs and related information ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Reasonably unusual. Is likely to appreciate it for those who add forums or anything, site theme . a tones way for your customer to communicate. Excellent task.

  34. Simply desire to say your article is as amazing. The clarity in your publish is simply nice and i could suppose you are knowledgeable on this subject. Well together with your permission allow me to snatch your RSS feed to keep updated with impending post. Thanks 1,000,000 and please keep up the enjoyable work.

  35. I do agree with all the ideas you’ve introduced in your post. They’re really convincing and will certainly work. Nonetheless, the posts are too brief for novices. May you please lengthen them a little from next time? Thanks for the post.

  36. Thank you for the sensible critique. Me & my neighbor were just preparing to do a little research on this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such magnificent information being shared freely out there.

  37. certainly like your web site but you need to take a look at the spelling on quite a few of your posts. Many of them are rife with spelling problems and I to find it very bothersome to inform the reality however I’ll certainly come back again.

  38. I do accept as true with all the ideas you’ve presented to your post. They’re very convincing and will certainly work. Still, the posts are very short for starters. May just you please prolong them a little from next time? Thank you for the post.

  39. I have been absent for some time, but now I remember why I used to love this website. Thank you, I’ll try and check back more frequently. How frequently you update your site?

  40. My partner and I absolutely love your blog and find the majority of
    your post’s to be just what I’m looking for. Do you offer guest writers to write content in your case?

    I wouldn’t mind writing a post or elaborating on a few of
    the subjects you write regarding here. Again, awesome website!

  41. Excellent post. I was checking continuously this blog and I am impressed!
    Very helpful information specially the last part 🙂 I care
    for such information a lot. I was looking for this particular information for
    a long time. Thank you and best of luck.

  42. What’s Going down i’m new to this, I stumbled upon this I’ve discovered It absolutely useful and it
    has aided me out loads. I am hoping to give a contribution & aid different users like
    its aided me. Great job.

  43. Hi there! This is my first visit to your blog! We are a collection of volunteers and starting a new initiative in a community in the same niche.

    Your blog provided us useful information to work on. You have
    done a marvellous job!

  44. I think everything published made a bunch of sense. However, what about
    this? what if you added a little information? I mean, I don’t
    want to tell you how to run your website, however what if you added a title to possibly get a person’s attention? I mean ozenero | Mobile & Web Programming Tutorials is kinda plain. You should glance at Yahoo’s home page and note how they create post titles to grab viewers
    to open the links. You might add a related video or a picture or two to get people excited about what you’ve got to say.
    In my opinion, it might make your blog a little bit more interesting.

  45. Heya i’m for the first time here. I found this board and I find It really useful & it helped me out a
    lot. I hope to give something back and help others like
    you aided me.

  46. Wonderful site you have here but I was curious about if you knew of any user discussion forums that cover the same topics talked about in this article?
    I’d really love to be a part of online community where I can get suggestions from
    other experienced people that share the same interest.
    If you have any recommendations, please let me know.
    Thanks a lot!

  47. Good – I should certainly pronounce, impressed with your site. I had no trouble navigating through all the tabs and related information ended up being truly simple to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or something, web site theme . a tones way for your client to communicate. Nice task.

  48. Thanks for a marvelous posting! I truly enjoyed reading it, you’re a great author.I will be sure to bookmark your blog and will often come back
    from now on. I want to encourage continue your great work,
    have a nice weekend!

  49. May I simply just say what a relief to uncover a person that
    genuinely understands what they’re talking about over the
    internet. You definitely understand how to bring an issue to light and make it important.
    A lot more people really need to look at this and
    understand this side of your story. It’s surprising
    you are not more popular given that you most certainly have
    the gift.

  50. you are really a good webmaster. The web site loading velocity is incredible. It seems that you are doing any distinctive trick. In addition, The contents are masterpiece. you’ve performed a great activity on this subject!

  51. When someone writes an article he/she maintains the plan of a user in his/her mind that how a user
    can be aware of it. Thus that’s why this piece of writing is outstdanding.
    Thanks!

Leave a Reply

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