Access SQLite DB in Flutter

Question

I am creating a DB in my native Android code using SQLite. Now I want to access the same DB in Flutter, below is my code:

class DatabaseHelper
{
    static final _dbName="abc.db";
    static final _dbVersion=1;
    DatabaseHelper._privateConstructor();
    static final DatabaseHelper instance=DatabaseHelper._privateConstructor();
    static Database _database;
    Future<Database> get datatbase async
    {
        if(_database!=null) {
            print(_database.path);
            return _database;
        }/* */
    }


    Future<List<Map<String,dynamic>>> getAllLogs()async
    {
        final Database db= await instance.datatbase;
        return await db.query("calls_history");
    }

Whenever I call getAllLogs in my Flutter Widget I get the error: calls_history doesn't exist.

However, when I run the same query on native, it returns me the result

Solution 1

On flutter side you can use sqflite plugin, this plugin is built on top of native SQLite so you don't need to do anything special just refer to the same database name in flutter.

In one of my flutter projects I have used native android to code to receive messages and save them in SQLite database and on the flutter side I have used same database using sqflite to display the contents of database. Here is the flutter side code

import 'dart:async';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class SMSHelper {
  Database? db;

  Future open() async {
    db = await openDatabase(
        //  by default path for database on the device is /data/data/<your app id>/databases/<your database file.db>
        join(await getDatabasesPath(), 'ofs_sms_database.db'),
        version: 1, onCreate: (Database db, int version) async {
      await db.execute(
          "CREATE TABLE smslogs(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, employeeID TEXT, department TEXT, module TEXT, message TEXT, safeUnsafeStatus TEXT, contactNo Text, dateTime INTEGER)");
    });
  }

  Future<void> insertSMS(SMSLog smsLog) async {
    await db?.insert(
      'smslogs',
      smsLog.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<List<SMSLog>> getAllSMS() async {
    if (db == null) {
      open();
    }
    final List<Map<String, dynamic>>? maps = await db?.query('smslogs');

    // Convert the List<Map<String, dynamic> into a List<Dog>.
    if (maps != null) {
      return List.generate(maps.length, (i) {
        return SMSLog(
            employeeID: maps[i]['employeeID'],
            department: maps[i]['department'],
            module: maps[i]['module'],
            message: maps[i]['message'],
            safeUnsafeStatus: maps[i]['safeUnsafeStatus'],
            contactNo: maps[i]['contactNo'],
            dateTime: maps[i]['dateTime']);
      });
    } else {
      return [];
    }
  }

  Future close() async => db?.close();
}

class SMSLog {
  final String employeeID;
  final String department;
  final String module;
  final String message;
  final String safeUnsafeStatus;
  final String contactNo;
  final int dateTime;

  SMSLog(
      {required this.employeeID,
      required this.department,
      required this.module,
      required this.message,
      required this.safeUnsafeStatus,
      required this.contactNo,
      required this.dateTime});

  Map<String, dynamic> toMap() {
    return {
      'employeeID': employeeID,
      'department': department,
      'module': module,
      'message': message,
      'safeUnsafeStatus': safeUnsafeStatus,
      'contactNo': contactNo,
      'dateTime': dateTime
    };
  }
}

Solution 2

To work with SQLite databases, import the sqflite and path packages.

dependencies:
  flutter:
    sdk: flutter
  sqflite:
  path:

Make sure to import the packages in the file you’ll be working in.

import 'dart:async';

import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Open the database

1. Define the path to the database file using getDatabasesPath() from the sqflite package, combined with the join function from the path package.
2. Open the database with the openDatabase() function from sqflite.

In order to use the keyword await, the code must be placed inside an async function. You should place all the following table functions inside void main() async {}.

// Avoid errors caused by flutter upgrade.
// Importing 'package:flutter/widgets.dart' is required.
WidgetsFlutterBinding.ensureInitialized();
// Open the database and store the reference.
final database = openDatabase(
  // Set the path to the database. Note: Using the `join` function from the
  // `path` package is best practice to ensure the path is correctly
  // constructed for each platform.
  join(await getDatabasesPath(), 'doggie_database.db'),
);