If you write an app that needs to persist and query larger amounts of data onthe local device, consider using a database instead of a local file or key-valuestore. In general, databases provide faster inserts, updates, and queriescompared to other local persistence solutions.

Flutter apps can make use of the SQLite databases via thesqflite plugin available on pub.This recipe demonstrates the basics of using sqflite to insert, read, update,and remove data about various Dogs!

If you are new to SQLite and SQL statements, please review the SQLite Tutorialsite to learn the basics before completingthis recipe.

Directions

  • Add the dependencies
  • Define the Dog data model
  • Open the Database
  • Create the dogs table
  • Insert a Dog into the database
  • Retrieve the list of Dogs
  • Update a Dog in the database
  • Delete a Dog from the database

1. Add the dependencies

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

  • The sqflite package provides classes and functions that allow you to interact with a SQLite database.
  • The path package provides functions that allow you to correctly define the location to store the database on disk.
  1. dependencies:
  2.   flutter:
  3.     sdk: flutter
  4.   sqflite:
  5.   path:

2. Define the Dog data model

Before you create the table to store information on Dogs, take a few moments todefine the data that needs to be stored. For this example, define a Dog classthat contains three pieces of data: A unique id, the name, and the age ofeach dog.

  1. class Dog {
  2.   final int id;
  3.   final String name;
  4.   final int age;
  6.   Dog({this.id, this.name, this.age});
  7. }

3. Open the Database

Before you read and write data to the database, you need to open a connection to the database. This involves two steps:

  • Define the path to the database file using the getDatabasesPath from the sqflite package combined with the path function from the path package
  • Open the database with the openDatabase function from sqflite
  1. // Open the database and store the reference
  2. final Future<Database> database = openDatabase(
  3.   // Set the path to the database. Note: Using the `join` function from the
  4.   // `path` package is best practice to ensure the path is correctly
  5.   // constructed for each platform.
  6.   join(await getDatabasesPath(), 'doggie_database.db'),
  7. );

4. Create the dogs table

Next, you need to create a table to store information about various Dogs. Forthis example, create a table called dogs that defines the data that can bestored. In this case, each Dog contains an id, name, and age. Therefore,these will be represented as three columns in the dogs table.

  • The id is a Dart int, and will be stored as an INTEGER SQLite Datatype. It is also good practice to use an id as the primary key for the table to improve query and update times.
  • The name is a Dart String, and will be stored as a TEXT SQLite Datatype
  • The age is also a Dart int, and will be stored as an INTEGER DatatypeFor more information about the available Datatypes that can be stored in aSQLite database, please see the official SQLite Datatypesdocumentation. 
  1. final Future<Database> database = openDatabase(
  2.   // Set the path to the database. 
  3.   join(await getDatabasesPath(), 'doggie_database.db'),
  4.   // When the database is first created, create a table to store dogs
  5.   onCreate: (db, version) {
  6.     // Run the CREATE TABLE statement on the database
  7.     return db.execute(
  8.       "CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
  9.     );
  10.   },
  11.   // Set the version. This executes the onCreate function and provides a
  12.   // path to perform database upgrades and downgrades.
  13.   version: 1,
  14. );

5. Insert a Dog into the database

Now that you have a database with a table suitable for storing information about various dogs, it’s time to read and write data!

First, insert a Dog into the dogs table. This involves two steps:

  • Convert the Dog into a Map
  • Use the insert method to store the Map in the dogs table
  1. // First, update the Dog class to include a `toMap` method.
  2. class Dog {
  3.   final int id;
  4.   final String name;
  5.   final int age;
  7.   Dog({this.id, this.name, this.age});
  9.   // Convert the dog into a Map. The keys must correspond to the names of the 
  10.   // columns in the database.
  11.   Map<String, dynamic> toMap() {
  12.     return {
  13.       'id': id,
  14.       'name': name,
  15.       'age': age,
  16.     };
  17.   }
  18. }
  20. // Next, define a function that inserts dogs into the database
  21. Future<void> insertDog(Dog dog) async {
  22.   // Get a reference to the database
  23.   final Database db = await database;
  25.   // Insert the Dog into the correct table. You may also specify the 
  26.   // `conflictAlgorithm` to use in case the same dog is inserted twice. 
  27.   // 
  28.   // In this case, replace any previous data.
  29.   await db.insert(
  30.     'dogs',
  31.     dog.toMap(),
  32.     conflictAlgorithm: ConflictAlgorithm.replace,
  33.   );
  34. }
  36. // Now, you can create a Dog to and add it to the dogs table!
  37. final fido = Dog(
  38.   id: 0, 
  39.   name: 'Fido', 
  40.   age: 35,
  41. );
  43. await insertDog(fido);

6. Retrieve the list of Dogs

Now that you have a Dog stored in the database, you can query the databasefor a specific dog or a list of all dogs! This involves two steps:

  • Run a query against the dogs table. This will return a List<Map>
  • Convert the List<Map> into a List<Dog>
  1. // A method that will retrieve all the dogs from the dogs table
  2. Future<List<Dog>> dogs() async {
  3.   // Get a reference to the database
  4.   final Database db = await database;
  6.   // Query the table for All The Dogs.
  7.   final List<Map<String, dynamic>> maps = await db.query('dogs');
  9.   // Convert the List<Map<String, dynamic> into a List<Dog>.
  10.   return List.generate(maps.length, (i) {
  11.     return Dog(
  12.       id: maps[i]['id'],
  13.       name: maps[i]['name'],
  14.       age: maps[i]['age'],
  15.     );
  16.   });
  17. }
  19. // Now, you can use the method above to retrieve all the dogs!
  20. print(await dogs()); // Prints a list that include Fido

7. Update a Dog in the database

After you’ve inserted some information into the database, you may want to updatethat information at a later time. To do so, use theupdatemethod from the sqflite library.

This involves two steps:

  • Convert the Dog into a Map
  • Use a where clause to ensure you update the correct Dog
  1. Future<void> updateDog(Dog dog) async {
  2.   // Get a reference to the database
  3.   final db = await database;
  5.   // Update the given Dog
  6.   await db.update(
  7.     'dogs',
  8.     dog.toMap(),
  9.     // Ensure we only update the Dog with a matching id
  10.     where: "id = ?",
  11.     // Pass the Dog's id through as a whereArg to prevent SQL injection
  12.     whereArgs: [dog.id],
  13.   );
  14. }
  16. // Now, you can update Fido's age!
  17. await updateDog(Dog(
  18.   id: 0, 
  19.   name: 'Fido', 
  20.   age: 42,
  21. ));
  23. // And you could print the updated results
  24. print(await dogs()); // Prints Fido with age 42.

Warning:Always use whereArgs to pass arguments to a where statement. This helps safeguard against SQL injection attacks.

Do not use string interpolation, such as where: "id = ${dog.id}"!

8. Delete a Dog from the database

In addition to inserting and updating information about Dogs, you can alsoremove dogs from the database. To delete data, use thedeletemethod from the sqflite library.

In this portion, create a function that takes in an id and deletes the dog witha matching id from the database. To make this work, you must provide a whereclause to limit the records being deleted.

  1. Future<void> deleteDog(int id) async {
  2.   // Get a reference to the database
  3.   final db = await database;
  5.   // Remove the Dog from the Database
  6.   await db.delete(
  7.     'dogs',
  8.     // Use a `where` clause to delete a specific dog
  9.     where: "id = ?",
  10.     // Pass the Dog's id through as a whereArg to prevent SQL injection
  11.     whereArgs: [id],
  12.   );
  13. }

Example

To run the example:

  • Create a new Flutter project
  • Add the sqfite and path packages to your pubspec.yaml
  • Paste the following code into a new file called lib/db_test.dart
  • Run the code with flutter run lib/db_test.dart
  1. import 'dart:async';
  3. import 'package:path/path.dart';
  4. import 'package:sqflite/sqflite.dart';
  6. void main() async {
  7.   final database = openDatabase(
  8.     // Set the path to the database. Note: Using the `join` function from the
  9.     // `path` package is best practice to ensure the path is correctly
  10.     // constructed for each platform.
  11.     join(await getDatabasesPath(), 'doggie_database.db'),
  12.     // When the database is first created, create a table to store dogs
  13.     onCreate: (db, version) {
  14.       return db.execute(
  15.         "CREATE TABLE dogs(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)",
  16.       );
  17.     },
  18.     // Set the version. This executes the onCreate function and provides a
  19.     // path to perform database upgrades and downgrades.
  20.     version: 1,
  21.   );
  23.   Future<void> insertDog(Dog dog) async {
  24.     // Get a reference to the database
  25.     final Database db = await database;
  27.     // Insert the Dog into the correct table. We will also specify the
  28.     // `conflictAlgorithm` to use in this case. If the same dog is inserted
  29.     // multiple times, it will replace the previous data.
  30.     await db.insert(
  31.       'dogs',
  32.       dog.toMap(),
  33.       conflictAlgorithm: ConflictAlgorithm.replace,
  34.     );
  35.   }
  37.   Future<List<Dog>> dogs() async {
  38.     // Get a reference to the database
  39.     final Database db = await database;
  41.     // Query the table for All The Dogs.
  42.     final List<Map<String, dynamic>> maps = await db.query('dogs');
  44.     // Convert the List<Map<String, dynamic> into a List<Dog>.
  45.     return List.generate(maps.length, (i) {
  46.       return Dog(
  47.         id: maps[i]['id'],
  48.         name: maps[i]['name'],
  49.         age: maps[i]['age'],
  50.       );
  51.     });
  52.   }
  54.   Future<void> updateDog(Dog dog) async {
  55.     // Get a reference to the database
  56.     final db = await database;
  58.     // Update the given Dog
  59.     await db.update(
  60.       'dogs',
  61.       dog.toMap(),
  62.       // Ensure we only update the Dog with a matching id
  63.       where: "id = ?",
  64.       // Pass the Dog's id through as a whereArg to prevent SQL injection
  65.       whereArgs: [dog.id],
  66.     );
  67.   }
  69.   Future<void> deleteDog(int id) async {
  70.     // Get a reference to the database
  71.     final db = await database;
  73.     // Remove the Dog from the Database
  74.     await db.delete(
  75.       'dogs',
  76.       // Use a `where` clause to delete a specific dog
  77.       where: "id = ?",
  78.       // Pass the Dog's id through as a whereArg to prevent SQL injection
  79.       whereArgs: [id],
  80.     );
  81.   }
  83.   var fido = Dog(
  84.     id: 0,
  85.     name: 'Fido',
  86.     age: 35,
  87.   );
  89.   // Insert a dog into the database
  90.   await insertDog(fido);
  92.   // Print the list of dogs (only Fido for now)
  93.   print(await dogs());
  95.   // Update Fido's age and save it to the database
  96.   fido = Dog(
  97.     id: fido.id,
  98.     name: fido.name,
  99.     age: fido.age + 7,
  100.   );
  101.   await updateDog(fido);
  103.   // Print Fido's updated information
  104.   print(await dogs());
  106.   // Delete Fido from the Database
  107.   await deleteDog(fido.id);
  109.   // Print the list of dogs (empty)
  110.   print(await dogs());
  111. }
  113. class Dog {
  114.   final int id;
  115.   final String name;
  116.   final int age;
  118.   Dog({this.id, this.name, this.age});
  120.   Map<String, dynamic> toMap() {
  121.     return {
  122.       'id': id,
  123.       'name': name,
  124.       'age': age,
  125.     };
  126.   }
  128.   // Implement toString to make it easier to see information about each dog when
  129.   // using the print statement.
  130.   @override
  131.   String toString() {
  132.     return 'Dog{id: $id, name: $name, age: $age}';
  133.   }
  134. }