explain

Flutter native does not support database operation. It uses SQLlit plug-in to make applications have the ability to use database. In fact, flutter communicates with the native system through plug-ins to operate the database.

Platform support

• FLutter's SQLite plug-in supports IOS, Android, and MacOS platforms
• If you want to support Linux / Windows / DartVM, use sqflite_common_ffi
• web platform 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 dependency

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

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

dependencies:
  sqflite: ^1.3.0
  path:Version number
 Copy code

use

Import sqflite dart

import 'dart:async';

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

Open database

SQLite database is the 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');
Copy code

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 automatically release resources, you can use the following methods:

await db.close();
Copy code

Execute the original SQL query

1. Get the database location using getDatabasesPath()

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

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

1. Open database

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

1. increase

Insert several pieces of data into the table in the 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');
});
Copy code

1. Delete

Delete a piece of data in the table

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

1. change

Modify data in table

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

1. check

Data in query 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);
Copy code

Total number of data stored in query table

count = Sqflite.firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
Copy code

1. close database

await database.close();
Copy code

1. Delete database

await deleteDatabase(path);
Copy code

Using SQL assistant

1. Create fields and associated classes in the 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;

  //Convert the current class into 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;
  }
  //Nonparametric structure
  Todo();

  //Convert the data of map type into the constructor of the current class object.
  Todo.fromMap(Map<String, Object?> map) {
    id = map[columnId];
    title = map[columnTitle];
    done = map[columnDone] == 1;
  }
}
Copy code

1. Use the above classes to create and delete databases and add, delete, modify and query data.

class TodoProvider {
  Database db;

  Future open(String path) async {
    db = await openDatabase(path, version: 1,
        onCreate: (Database db, int version) async {
      await db.execute('''
  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 {
    todo.id = await db.insert(tableTodo, todo.toMap()，conflictAlgorithm: ConflictAlgorithm.replace,);
    return todo;
  }

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

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

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

  Future close() async => db.close();
}
Copy code

1. Query all data in the table

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

Copy code

1. Get the first data in the result

Map<String, Object?> mapRead = records.first;
Copy code

1. 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
 Copy code

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

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

1. Convert the queried data of list < Map > type into list < todo > type, so that we can use it happily.

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

Batch processing

1. You can use batch processing to avoid frequent interaction 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();
Copy code

It costs to get the result of each operation (the 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 of the result

await batch.commit(noResult: true);
Copy code

1. Using batch processing in transactions

Batch processing is performed in a transaction. Batch processing will not be submitted until 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();

  //  ...
});
Copy code

1. Batch exception ignored

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

await batch.commit(continueOnError: true);
Copy code

About table and column names

In general, we should avoid using SQLite keyword to name table name and column name. For example:

"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"
Copy code

Supported storage types

• Avoid using unsupported types because the value has not been validated. 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

SQLite type	dart type	Value range
integer	int	From - 2 ^ 63 to 2 ^ 63-1
real	num
text	String
blob	Uint8List

reference resources

sqflile official address

Official introduction document of flutter on using SQLite for data storage