Flutter SQFlite Tutorial and Examples

How to work with SQLite datbase in flutter.

What is Flutter?

Flutter is Google’s mobile app SDK for crafting high-quality native interfaces on iOS and Android in record time.

Flutter works with existing code, is used by developers and organizations around the world, and is free and open source.

Here are the three main features of Flutter:

1. Fast Development

Flutter can paint your app to life in milliseconds through hot Reload.

You can alos employ a rich set of fully-customizable widgets to build native interfaces in minutes.

2. Expressive and Flexible UI

Flutter allows you to quickly ship features with a focus on native end-user experiences.

Layered architecture allows full customization, which results in incredibly fast rendering and expressive and flexible designs.

3. Native Performance

Flutter’s widgets incorporate all critical platform differences such as scrolling, navigation, icons and fonts to provide full native performance on both iOS and [Android](/android/introduction.

What is SQFlite?

SQflite is a plugin for flutter. It allows us store, retrieve and manipulate our SQLite databases via flutter code. SQflite supports both Android and iOS platforms.

Here are some of the features of SQFlite:

  1. SQFlite provides for both database transactions as well as batches.
  2. SQlite has inbuilt automatic version managment.
  3. SQFlite provides easy to use methods for inserting, querying, updating as well as deleting data from database.
  4. These CRUD operations are performed in the background thread on both iOS and Android. This frees the UI to remain responsive.

How to Install SQFlite

SQFlite installation is as easy as any other flutter packages. It’s hosted inDart Package Repository and can be added as a dependenncy.

To do that:

1. Go to Your pubspec.yaml file:

And add it as a dependency:

dependencies:
  sqflite: ^0.12.1

You may check the latest version here.

2. Download it

Android Studio will provide you with a package get button which will fetch it and add to your project as long as you are online.

If you are using Visual Studio code then making a change in the pubspec.yaml file will cause the flutter packages get command to be automatically invoked.

Otherwise if you are not using the IDE’s above then you can install it from the command line

$ flutter packages get

3. Import into You Project

Go over to your dart code where you want to use it and import it at the top of your file.

import 'package:sqflite/sqflite.dart';

Quick Flutter SQFLite Database HowTo Snippets

1. How to get Database Path

        // Check if we have an existing copy first
        var databasesPath = await getDatabasesPath();
        String path = join(databasesPath, "demo_asset_example.db");

2. How to Open a Database

        // try opening (will work if it exists)
        Database db;
        try {
          db = await openDatabase(path, readOnly: true);
        } catch (e) {
          print("Error $e");
        }

You can put in a helper class:

class Helper {
  final String path;
  Helper(this.path);
  Database _db;
  final _lock = Lock();

  Future<Database> getDb() async {
    if (_db == null) {
      await _lock.synchronized(() async {
        // Check again once entering the synchronized block
        if (_db == null) {
          _db = await openDatabase(path);
        }
      });
    }
    return _db;
  }

Or:

  Future<Database> get db async {
    if (_db != null) return _db;
    _db = await initDb();
    return _db;
  }

whereby initDb() is a custom method to obtain us the path and open it as:

  initDb() async {
    io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
    String path = join(documentsDirectory.path, "test.db");
    var theDb = await openDatabase(path, version: 1, onCreate: _onCreate);
    return theDb;
  }

3. How to Create a Database Table

Let’s see how we can create a database table in flutter with S

      var db = await openDatabase(path);
      await db.execute("CREATE TABLE IF NOT EXISTS Test(id INTEGER PRIMARY KEY)");

or:

  void _onCreate(Database db, int version) async {
    // When creating the db, create the table
    await db.execute(
        "CREATE TABLE Employee(id INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, mobileno TEXT,emailId TEXT )");
    print("Created tables");
  }

The db is the Database object. We invoke it’s execute() method and pass in our raw sql statement to create us the table.

4. How to Insert into a database table

      await db.insert("Test", {"id": 1});

or:

  Future<int> insertTodo(Todo todo) async {
    Database db = await this.db;
    var result = await db.insert(tblTodo, todo.toMap());
    return result;
  }

or let’s say I want to insert a Person with his properties into Database, then return the inserted person:

  Future insertPerson(Person person) async {
    person.id = await _db.insert("people", person.toMap());
    return person;
    }

or

  void saveEmployee(Employee employee) async {
    var dbClient = await db;
    await dbClient.transaction((txn) async {
      return await txn.rawInsert(
          'INSERT INTO Employee(firstname, lastname, mobileno, emailid ) VALUES(' +
              ''' +
              employee.firstName +
              ''' +
              ',' +
              ''' +
              employee.lastName +
              ''' +
              ',' +
              ''' +
              employee.mobileNo +
              ''' +
              ',' +
              ''' +
              employee.emailId +
              ''' +
              ')');
    });
  }

5. How to Query or Select From SQLite Table

      await db.query("Test")

Say I want to return a select and return a list of People from the database:

    Future fetchEveryone() async {
    List results = await _db.query("people", columns: Person.columns);
    List people = new List();
    results.forEach((map) {
      people.add(Person.fromMap(map));
    });
    return people;
    }

Or say we want to query a list our SQlite database in flutter and populate a List. We can also use a raw query using the rawQuery method. You pass into it the SQL statement to be executed.

  Future<List<Employee>> getEmployees() async {
    var dbClient = await db;
    List<Map> list = await dbClient.rawQuery('SELECT * FROM Employee');
    List<Employee> employees = new List();
    for (int i = 0; i < list.length; i++) {
      employees.add(new Employee(list[i]["firstname"], list[i]["lastname"], list[i]["mobileno"], list[i]["emailid"]));
    }
    print(employees.length);
    return employees;
  }

Or another example:

  Future<List> getTodos() async {
    Database db = await this.db;
    var result = await db.rawQuery("SELECT * FROM $tblTodo ORDER BY $colPriority ASC");
    return result;
  }

Let’s say I want to check if a user is logged in:

  Future<bool> isLoggedIn() async {
    var dbClient = await db;
    var res = await dbClient.query("User");
    return res.length > 0 ? true : false;
  }

7 . How to Count number of items in SQLite

  Future<int> getCount() async {
    Database db = await this.db;
    var result = Sqflite.firstIntValue(
      await db.rawQuery("SELECT COUNT (*) FROM $tblTodo")
    );
    return result;
  }

7. How to Update Data in SQLite database using SQFLite

  Future<int> updateTodo(Todo todo) async {
    var db = await this.db;
    var result = await db.update(tblTodo, todo.toMap(),
      where: "$colId = ?", whereArgs: [todo.id]);
    return result;
  }

7. How to Delete From SQLite database using SQFLite

  Future<int> deleteTodo(int id) async {
    int result;
    var db = await this.db;
    result = await db.rawDelete('DELETE FROM $tblTodo WHERE $colId = $id');
    return result;
  }

Or:

  Future<int> deleteUsers() async {
    var dbClient = await db;
    int res = await dbClient.delete("User");
    return res;
  }

6. How to Close Database

      await db.close();

Or:

  Future close() async {
    await _db.close();
  }