SQFLite

SQFLite is a plugin that allows you to work with SQLite.

Installing

To work with SQLite, we need to install SQFLite plugin for flutter. In your flutter project add the dependency:

dependencies:
  ...
  sqflite: ^1.3.0

Importaing the Package

After installation, you need to import the package:

import 'package:sqflite/sqflite.dart';

In order to be able to work with sqflite we also need to import another package called path.

import 'package:path/path.dart';

In top of these two we need to add async libraries as most SQL methods are asynchronous in nature.

import 'dart:async';

So in summary, include the following in your dart page:

import 'dart:async';

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

Opening the Database

After importing the packages, the first thing you would do is opening a database. To open a databse we need to use openDatabase of sqflite package:

final Future<Database> database = openDatabase(
  // Set the path to the database.
  join(await getDatabasesPath(), 'doggie_database.db'),
  // When the database is first created, create a table to store dogs.
  onCreate: (db, version) {
    // Run the CREATE TABLE statement on the database.
    return db.execute(
      "CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
    );
  },
  // Set the version. This executes the onCreate function and provides a
  // path to perform database upgrades and downgrades.
  version: 1,
);

The first argument of the method openDatabase is a positional argument which is the path of the database. The method getDatabasePath() from sqflite package returns the path of the directory where the database is stored. The method join() from the package path joins two string and creates a path to the database file and returns it. In the above case we are joining the database directory with the name doggie_database.db. But where did doggie_database.db come from? We haven't created the database yet. In this case, the method openDatabase() will create the database doggie_database.db as it is not found in the directory and then it will open it. Next time when you start the app, it will not create the database as it will exists there.

The named argument onCreate takes a callback which is run when the database is created for the first time. In this callback you would normally create the tables required for your project. This callback receives the newly created database (db) as the first argument and the version as the second argument. The callback onCreate is called when you run the application for the first time and it creates tables. After that it is not called anymore until you change the version dynamically.

The named argument version specifies the version of your table schema. You can set any value you want. In our case we have set it to 1.

The above code will successfully open the database and make the database ready for you. The above method will return the database and using the database you can now perform various operation like inserting data or updating data or deleting data from the table, joing, query you data etc.

Inserting Row

Now that you have a database with a table suitable for storing information about various dogs, it’s time to read and write data. We will first create a model to insert a dog into the table. Every table should have a corresponding model associated to perform insertion. Below is the model for the table dogs:

class Dog {
  final int id;
  final String name;
  final int age;

  Dog({this.id, this.name, this.age});
}

Now we will add a method called toMap which will convert the above dog data to the map object.

// Update the Dog class to include a `toMap` method.
class Dog {
  final int id;
  final String name;
  final int age;

  Dog({this.id, this.name, this.age});

  // Convert a Dog into a Map. The keys must correspond to the names of the
  // columns in the database.
  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'age': age,
    };
  }
}

Now lets create a function insertDog which will insert a dog into the table.

// Define a function that inserts dogs into the database
Future<void> insertDog(Dog dog) async {
  // Get a reference to the database.
  final Database db = await database;

  // Insert the Dog into the correct table. You might also specify the
  // `conflictAlgorithm` to use in case the same dog is inserted twice.
  //
  // In this case, replace any previous data.
  await db.insert(
    'dogs',
    dog.toMap(),
    conflictAlgorithm: ConflictAlgorithm.replace,
  );
}

In the above function the statement:

final Database db = await database;

is necessary. This statement creates a database object which is in our case db. The variable database is not a database object. It is a future object of type Database. Because the function openDatabase is asynchronous function and when then application starts we usually call this method. But we don't want our application to wait for database to be opened. Instead we let it run asynchronously while performing other stuff. So if you want to perform any operation with the database you need to wait for the database to be opened. The expression await database waits for database to be opened, when it does it returns the reference to the database. If the database is already opened it returns the reference immediately. Now using the db object you can perform various operation.

The insert method of db object inserts data to the table. The first argument is the name of the table and the second argument is the map object that we want to insert. So lets insert a dog:

// Create a Dog and add it to the dogs table.
final fido = Dog(
  id: 0,
  name: 'Fido',
  age: 35,
);

await insertDog(fido);

Updating Row

The method rawUpdate updates a row. The first argument is the query and the second argument is a list of parameters that will be provided to the query.

// Update some record
int count = await db.rawUpdate(
    'UPDATE dogs SET name = ?, age = ? WHERE id = ?',
    ['New name', 23, 1]);
print('updated: $count');

You can also update a record using update method. The first argument is the name of the table. The second argument is the map data and third argument is condition to match id and fourth argument is a list of parameters that will be passed to the condition in order to prevent SQL injection. The following function perform the update:

Future<void> updateDog(Dog dog) async {
  // Get a reference to the database.
  final db = await database;

  // Update the given Dog.
  await db.update(
    'dogs',
    dog.toMap(),
    // Ensure that the Dog has a matching id.
    where: "id = ?",
    // Pass the Dog's id as a whereArg to prevent SQL injection.
    whereArgs: [dog.id],
  );
}

Here is how you can call the method to update the dog:

// Update Fido's age.
await updateDog(Dog(
  id: 0,
  name: 'Fido',
  age: 42,
));

Retrive Data

Use rawQuery method to retrive the data from the table:

List<Map> list = await database.rawQuery('SELECT * FROM Test');

The output will be like this:

[
  {'id': 0, 'name': 'Fido', 'age': 42},
]

Delete a Record

You can delete a record using rawDelete or delete method on db object. Here are example:

count = await database.rawDelete('DELETE FROM dogs WHERE name = ?', ['Fido']);

Here is an example using Dog model method:

Future<void> deleteDog(int id) async {
  // Get a reference to the database.
  final db = await database;

  // Remove the Dog from the Database.
  await db.delete(
    'dogs',
    // Use a `where` clause to delete a specific dog.
    where: "id = ?",
    // Pass the Dog's id as a whereArg to prevent SQL injection.
    whereArgs: [id],
  );
}

Full Example

import 'dart:async';

import 'package:flutter/widgets.dart';

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

void main() async {
  // Avoid errors caused by flutter upgrade.
  // Importing 'package:flutter/widgets.dart' is required.
  WidgetsFlutterBinding.ensureInitialized();
  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'),
    // When the database is first created, create a table to store dogs.
    onCreate: (db, version) {
      return db.execute(
        "CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
      );
    },
    // Set the version. This executes the onCreate function and provides a
    // path to perform database upgrades and downgrades.
    version: 1,
  );

  Future<void> insertDog(Dog dog) async {
    // Get a reference to the database.
    final Database db = await database;

    // Insert the Dog into the correct table. Also specify the
    // `conflictAlgorithm`. In this case, if the same dog is inserted
    // multiple times, it replaces the previous data.
    await db.insert(
      'dogs',
      dog.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<List<Dog>> dogs() async {
    // Get a reference to the database.
    final Database db = await database;

    // Query the table for all The Dogs.
    final List<Map<String, dynamic>> maps = await db.query('dogs');

    // Convert the List<Map<String, dynamic> into a List.
    return List.generate(maps.length, (i) {
      return Dog(
        id: maps[i]['id'],
        name: maps[i]['name'],
        age: maps[i]['age'],
      );
    });
  }

  Future<void> updateDog(Dog dog) async {
    // Get a reference to the database.
    final db = await database;

    // Update the given Dog.
    await db.update(
      'dogs',
      dog.toMap(),
      // Ensure that the Dog has a matching id.
      where: "id = ?",
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [dog.id],
    );
  }

  Future<void> deleteDog(int id) async {
    // Get a reference to the database.
    final db = await database;

    // Remove the Dog from the database.
    await db.delete(
      'dogs',
      // Use a `where` clause to delete a specific dog.
      where: "id = ?",
      // Pass the Dog's id as a whereArg to prevent SQL injection.
      whereArgs: [id],
    );
  }

  var fido = Dog(
    id: 0,
    name: 'Fido',
    age: 35,
  );

  // Insert a dog into the database.
  await insertDog(fido);

  // Print the list of dogs (only Fido for now).
  print(await dogs());

  // Update Fido's age and save it to the database.
  fido = Dog(
    id: fido.id,
    name: fido.name,
    age: fido.age + 7,
  );
  await updateDog(fido);

  // Print Fido's updated information.
  print(await dogs());

  // Delete Fido from the database.
  await deleteDog(fido.id);

  // Print the list of dogs (empty).
  print(await dogs());
}

class Dog {
  final int id;
  final String name;
  final int age;

  Dog({this.id, this.name, this.age});

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'name': name,
      'age': age,
    };
  }

  // Implement toString to make it easier to see information about
  // each dog when using the print statement.
  @override
  String toString() {
    return 'Dog{id: $id, name: $name, age: $age}';
  }
}

Retrived Data are read only

The data you are fetching from the query are read only. So if you assign any value to the query result it will cause an error. For example:

List<Map<String, dynamic>> records = await db.query('my_table');

Resulting map items are read-only

// get the first record
Map<String, dynamic> mapRead = records.first;
// Update it in memory...this will throw an exception
mapRead['my_column'] = 1;
// Crash... `mapRead` is read-only

You need to create a new map if you want to modify it in memory:

// get the first record
Map<String, dynamic> map = Map<String, dynamic>.from(mapRead);
// Update it in memory now
map['my_column'] = 1;