How to Use Flutter Database

Description

Flutter Native does not support database operations. It uses SQLlit plug-ins to enable applications to use databases. In fact, Flutter communicates with native systems through plug-ins to operate databases.

Platform support

The SQLite plug-in of FLutter supports IOS, Android, and MacOS platforms Use sqflite_common_ffi if you want to support Linux/Windows/DartVM The web platform is not supported Database operations are performed in the background of Android or ios

Use case

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

Simple use

Add dependencies

In order to use the SQLite database, you first need to import two package, sqflite and path

sqflite provides a wealth of classes and methods so that you can easily use the SQLite database. path provides a number of methods so that you can correctly define the storage location of the database on disk.

dependencies:
  sqflite: ^1.3.0
  path: Version number 

Use

Import sqflite. dart

import 'dart:async';

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

Open the database
An SQLite database is a file in a file system. If it is a relative path, it 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 close the database manually when we use it, because the database will be closed when the program is closed. If you want to release resources automatically, you can use the following methods:

await db.close();

Execute the original SQL query

Use getDatabasesPath () to get the database location

Use the getDatabasesPath method in sqflite package in conjunction with the join method in path package to define the path to the database. Using the join method in the path package is a best practice to ensure path correctness across platforms.

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

Open the database

Database database = await openDatabase(path, version: 1,
    onCreate: (Database db, int version) async {
  //  Create a table when you create a database 
  await db.execute(
      '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 database.transaction((txn) async {
  int id1 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
  print('inserted1: $id1');
  int id2 = await txn.rawInsert(
      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
      ['another name', 12345678, 3.1416]);
  print('inserted2: $id2');
});

Delete

Delete 1 piece of data from a table

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

Change

Modify the data in the table

int count = await database.rawUpdate('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 database.rawQuery('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);

The total number of pieces of data stored in the lookup table

import 'dart:async';

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

0

Close the database

import 'dart:async';

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

1

Delete a database

import 'dart:async';

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

2

Using the SQL Assistant

Create fields and associated classes in a table

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

// Corresponding class 
class Todo {
  int id;
  String title;
  bool done;

  // Converts the current class to 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;
  }
  // Parametric structure 
  Todo();
  
  // Put map Converts data of type to the constructor of the current class object. 
  Todo.fromMap(Map<String, Object?> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}

Use the above class to create and delete the database and add, delete and change the data.

import 'dart:async';

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

4

= Query all data in the table

import 'dart:async';

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

5

Get the first data in the result

import 'dart:async';

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

6

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

import 'dart:async';

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

7

Create a copy of map and modify the fields in it

//  According to the above map Create 1 A map Copy 
Map<String, Object?> map = Map<String, Object?>.from(mapRead);
//  Modify the field values stored in this copy in memory 
map['my_column'] = 1;

Put the queried List < map > Convert data of type to List < Todo > Type, so that we can use it happily.

import 'dart:async';

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

9

Batch processing

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

batch = db.batch();
batch.insert('Test', {'name': 'item'});
batch.update('Test', {'name': 'new_item'}, where: 'name = ?', whereArgs: ['item']);
batch.delete('Test', where: 'name = ?', whereArgs: ['item']);
results = await batch.commit();

Getting the results of each operation is costly (the number of Id inserted and the number of changes updated and deleted). If you don't care about the result of the operation, you can do the following to turn off the response to the result

await batch.commit(noResult: true);

Using batch processing in transactions

Batch operations are performed in transactions, and the batch is committed only after the transaction is committed.

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

Batch Exception Ignore

By default, a batch will stop once an error occurs (unexecuted statements will not be executed), and you can ignore the error so that subsequent operations can continue.

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

3

About table and column names

In general, we should avoid using the SQLite keyword to name table and column names. Such as:

"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

Avoid using unsupported types because the value has not been validated. See: The DateTime type is not supported and can be stored as int or String bool type not supported, can be stored as int type 0: false, 1: true

SQLite类型	dart类型	值范围
integer	int	从-2 ^ 63到2 ^ 63-1
real	num
text	String
blob	Uint8List

Reference

sqflile Official Address
flutter official documentation for data storage using SQLite