How to use Flutter database

illustrate

Flutter natively does not support database operations, it uses SQLlit plug-in to enable applications to use databases. In fact, Flutter communicates with the native system through plug-ins to perform database operations.

Platform support

• FLutter's SQLite plugin supports IOS, Android, and MacOS platforms
• If you want to support Linux/Windows/DartVM, use sqflite_common_ffi
• No web platform supported
• Database operations are executed in the background of Android or iOS

Use Cases

notepad_sqflite Simple Notepad app that can run on iOS/Android/Windows/linux/Mac

Simple use

Add dependencies

In order to use SQLite database, you need to import the two packages: sqflite and path

• sqflite provides a wealth of classes and methods so that you can easily and practically use SQLite databases.
• path provides a number of ways so that you can correctly define where the database is stored on disk.

dependencies:
  sqflite: ^1.3.0
  path:Version number

use

Import

import 'dart:async';

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

Open the database
SQLite database is a file in the file system. If it is a relative path, the path is the path obtained by getDatabasesPath(), which is associated with the default database directory on Android and the documents directory on iOS.

var db = await openDatabase('my_db.db');

Many times we don't need to manually close the database when we use it, because the database will be closed when the program is closed. If you want to automatically release resources, you can use the following method:

await ();

Execute the original SQL query

Use getDatabasesPath() to get the database location

Use the getDatabasesPath method in the sqflite package and use the join method in the path package to define the path to the database. Using the join method in the path package is a best practice to ensure the correctness of each platform path.

var databasesPath = await getDatabasesPath();
String path = join(databasesPath, '');

Open the database

Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  // Create tables when creating database  await (
      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
});

increase

Insert several pieces of data into a table in a transaction

await ((txn) async {
  int id1 = await (
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print('inserted1: $id1');
  int id2 = await (
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ['another name', 12345678, 3.1416]);
  print('inserted2: $id2');
});

delete

Delete a data in the table

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

change

Modify the data in the table

int count = await ('UPDATE Test SET name = ?, value = ? WHERE name = ?',
    ['updated name', '9876', 'some name']);
print('updated: $count');

check

Query the data in the table

// Get the records
List<Map> list = await ('SELECT * FROM Test');
List<Map> expectedList = [
  {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
  {'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
];
print(list);
print(expectedList);

Total number of data stored in the query table

count = (await ('SELECT COUNT(*) FROM Test'));

Close the database

await ();

Delete the database

await deleteDatabase(path);

Using SQL Assistant

Create fields and associated classes in tables

//Fieldfinal String tableTodo = 'todo';
final String columnId = '_id';
final String columnTitle = 'title';
final String columnDone = 'done';

//Responsive Classclass Todo {
  int id;
  String title;
  bool done;

  //Convert the current class to a map for external use  Map<String, Object?> toMap() {
    var map = <String, Object?>{
      columnTitle: title,
      columnDone: done == true ? 1 : 0
    };
    if (id != null) {
      map[columnId] = id;
    }
    return map;
  }
  //No parametric structure  Todo();
  
  //Convert map type data into the constructor of the current class object.  (Map<String, Object?> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

Use the above class to create and delete databases and add, delete, modify and search data.

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await ('''
  create table $tableTodo ( 
  $columnId integer primary key autoincrement, 
  $columnTitle text not null,
  $columnDone integer not null)
                        ''');
    });
  }

  //Insert a piece of data into the table. If it has been inserted, replace the previous one.  Future<Todo> insert(Todo todo) async {
     = await (tableTodo, ()，conflictAlgorithm: ,);
    return todo;
  }

  Future<Todo> getTodo(int id) async {
    List<Map> maps = await (tableTodo,
        columns: [columnId, columnDone, columnTitle],
        where: '$columnId = ?',
        whereArgs: [id]);
    if ( > 0) {
      return ();
    }
    return null;
  }

  Future<int> delete(int id) async {
    return await (tableTodo, where: '$columnId = ?', whereArgs: [id]);
  }

  Future<int> update(Todo todo) async {
    return await (tableTodo, (),
        where: '$columnId = ?', whereArgs: []);
  }

  Future close() async => ();
}

= Query all data in the table

List<Map<String, Object?>> records = await ('my_table');

Get the first piece of data in the result

Map<String, Object?> mapRead = ;

The Map in the list of query results above is read-only data. Modifying this data will throw an exception

mapRead['my_column'] = 1;
// Crash... `mapRead` is read-only

Create a copy of the map and modify the fields in it

// Create a map copy based on the above mapMap<String, Object?> map = Map<String, Object?>.from(mapRead);
// Modify the field value stored in this copy in memorymap['my_column'] = 1;

Convert the query List<map> data into List<Todo> type, so that we can use it happily.

// Convert the List<Map<String, dynamic> into a List<Todo>.
  return (, (i) {
    return Todo(
      id: maps[i][columnId],
      title: maps[i][columnTitle],
      done: maps[i][columnDown],
    );
  });

Batch processing

You can use batch processing to avoid frequent interactions between dart and native.

batch = ();
('Test', {'name': 'item'});
('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
('Test', where: 'name = ?', whereArgs: ['item']);
results = await ();

Getting the results of each operation is costly (inserted Ids and the number of changes to be updated and deleted). If you don't care about the results of the operation, you can perform the following operations to close the response of the result

await (noResult: true);

Use batch processing in transactions

Batch operations are performed in a transaction, and batch processing will not be submitted after the transaction is committed.

await ((txn) async {
  var batch = ();
  
  // ...
  
  // commit but the actual commit will happen when the transaction is committed
  // however the data is available in this transaction
  await ();
  
  //  ...
});

Batch exceptions are ignored

By default, an error will stop in batch processing once an error occurs (unexecuted statements will not be executed). You can ignore the error so that subsequent operations can continue to be executed.

await (continueOnError: true);

About table and column names

Normally we should avoid using SQLite keywords to name table names and column names. like:

"add","all","alter","and","as","autoincrement","between","case","check","collate",
"commit","constraint","create","default","deferrable","delete","distinct","drop",
"else","escape","except","exists","foreign","from","group","having","if","in","index",
"insert","intersect","into","is","isnull","join","limit","not","notnull","null","on",
"or","order","primary","references","select","set","table","then","to","transaction",
"union","unique","update","using","values","when","where"

Supported storage types

• As value has not been validated, avoid unsupported types. See:
• DateTime type is not supported, it can be stored as int or String
• Bool type is not supported, it can be stored as int type 0:false, 1:true

refer to

sqflile official address
flutter official introduction document for using SQLite for data storage

This is the end of this article about how to use Flutter database. For more related content on using Flutter database, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!