Learn SQLite using flutter

What Is SQLite

SQLite is an open source relational database, it is used to create a database, perform different operation like add, delete and remove data.

SQLite setup

Add the required dependencies. For this article, you’ll need the sqflite package for using the SQLite database.

Open a simulator device or connect a real device to your system and start the app using the following command:

flutter run

Add your SQLite file which you want to read and write.

Place your file under assets folder like below image

Create DBQueries and DBProvider.

DBProvider is require to connect your app with database.

import 'dart:io';

import 'package:flutter/services.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

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

  factory DBProvider() => _instance;
  static Database _database;

  Future<Database> get database async {
    if (_database != null) return _database;

    // if _database is null we instantiate it
    _database = await initDB();
    return _database;
  }

  DBProvider.internal();

  initDB() async {
    var documentsDirectory = await getDatabasesPath();
    String path = join(documentsDirectory, "app.db");
  //  var taskDb = await openDatabase(path, version: 1);
    await deleteDatabase(path);
    // Create the writable database file from the bundled demo database file:
    ByteData data = await rootBundle.load("assets/db/mydb.sqlite");
    List<int> bytes = data.buffer.asUint8List(data.offsetInBytes, data.lengthInBytes);
    await File(path).writeAsBytes(bytes);
    _database  = await openDatabase(path);
    return _database;
  }
  
}

Inside DB queries you have write all the SQL queries that wants in your app.All the sql queries must be asynchronised.

class DBQueries {
  var _database;
  DBProvider dbProvider;

  DBQueries() {
    getDb();
  }

  getDb() async {
    /// Initialize sq-lite
    dbProvider = await DBProvider();
  }

  initializeDB() async {
    _database = await dbProvider.initDB();
  }

  Future<int> createCategory(Category category) async {
    var result = await _database.insert("Category", category.toJson());
    return result;
  }

  Future<List> getCategory() async {
    var result = await _database.rawQuery('SELECT * FROM Category');
    return result.toList();
  }

  Future<Category> getCategoryById(int id) async {
    var results =
        await _database.rawQuery('SELECT * FROM Category WHERE id = $id');

    if (results.length > 0) {
      return new Category.fromJson(results.first);
    }

    return null;
  }

  Future<int> updateCategory(Category category) async {
    return await _database.rawUpdate(
        'UPDATE Category SET name = ${category.name} WHERE id = ${category.id}');
  }

  Future<int> deleteCategory(int id) async {
    return await _database.rawDelete('DELETE FROM Category WHERE id = $id');
  }
  

  closedatabase() async {
    await _database.close();
  }
}

Let's access DBQueries from your main.dart
@override
void initState() {
  // TODO: implement initState
  _accessDatabase();
  super.initState();
}
 Future<void> _accessDatabase() async {
  var db = await DBQueries();
  await db.initializeDB();
  var category = await db.getCategory();
  for (int i = 0; i < category.length; i++) {
      categories.add(Category.fromJson(category[0]));
  }
  print( Category.fromJson(category[0]).name);
}