sqflite is a popular and commonly used SQLite database plugin for Flutter. Using Sqflite, you can create, read, update, and delete records in a local SQLite database.
Before starting you need to create a new Flutter project and add the following dependencies:
dependencies:
...
sqflite:
Opening a Database
In Flutter you can open an SQLite database using the openDatabase method from the sqflite package. Here’s a basic example of how to open a database:
var db = await openDatabase('my_db.db');* We opened a database named ‘my_db.db’.
* The path returned by getDatabasePath(), which is the documents directory on iOS and macOS and the default database directory on Android, is what matters if the file path is relative. This means that don’t need to specify an absolute path; Flutter handles the databases.
* Flutter sqflite package provides a basic migration mechanism to handle these changes during database opening. This allows you to update your database schema without losing data. When you open a database, the package checks whether the database schema matches the expected schema, and if not, it runs specified migration scripts.
* To close an SQLite database use the close method:
await db.close();
Raw SQLite Queries
* Get a location using getDatabasesPath
var databasesPath = await getDatabasesPath();
String path = join(databasesPath, ‘demo.db’);
* Delete the database
await deleteDatabase(path);
* Open the database
Database database = await openDatabase(path, version: 1,
onCreate: (Database db, int version) async {
await db.execute(‘CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)’);
});
* Insert some records in a transaction
await database.transaction((txn) async {
int id1 = await txn.rawInsert(‘INSERT INTO Test(name, value, num) VALUES(“name1”, 1234, 456, 789)’);
int id1 = await txn.rawInsert(‘INSERT INTO Test(name, value, num) VALUES(“name2”, 1234, 456, 789)’);
* Update record
int count = await database.rawUpdate(‘UPDATE Test SET name = name, value = value WHERE name = name2’, [‘updated name’, ‘9878’, ‘name3’]);
* Get the records
List<Map> list = await database.rawQuery(‘SELECT * FROM Test’);
List<Map> expectedList = [
{‘name’: ‘updated name’, ‘id’:1, ‘value’: value, ‘num’: number},
{‘name’: ‘updated name2’, ‘id’:2, ‘value’: value, ‘num’:number}
];
assert(const DeepCollectionEquality().equals(list, expectedList));
* Count the records
count = Sqflite.firstIntValue(await database.rawQuery(‘SELECT COUNT(*) FROM Test’));
assert(count == 2);
* Delete a record
count = await database.rawDelete(‘DELETE FROM Test WHERE name = name’, [‘name2’]);
* Close the database
await database.close();
Supported SQLite types
* INTEGER (Dart type : int)
* REAL (Dart type : num)
* TEXT (Dart type : String)
* BLOB (Dart type: Uint8List)
Table and Column names
* "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"
Transaction
* Flutter’s sqflite package provides a convenient way to work with transactions using the database.transaction method.When using this method, you provide a callback function that includes the database’s txn object, and any operations within this callback will be treated as part of the same transaction.
* await database.transaction((txn) async {
await txn.execute(‘CREATE TABLE Test1 (id INTEGER PRIMARY KEY)’);
await database.execute(‘CREATE TABLE Test2 (id INTEGER PRIMARY KEY)’);
});
Flutter App Example:
Create a new flutter project and create a main menu.For that in your project lib create a file named main.dart.
import 'package:flutter/material.dart';
import 'package:testapp/add_student.dart';
import 'package:testapp/list_students.dart';
void main() {
runApp(MyApp());
}
class MyApp extends StatelessWidget {
@override
Widget build(BuildContext context) {
return MaterialApp(
home: Home(),
);
}
}
class Home extends StatefulWidget {
@override
_HomeState createState() => _HomeState();
}
class _HomeState extends State<Home> {
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: Text("Sqlite and Sqflite"),
),
body: Container(
alignment: Alignment.topCenter,
padding: EdgeInsets.all(20),
child: Column(
children:[
ElevatedButton(
onPressed: (){
Navigator.push(context, MaterialPageRoute(builder: (BuildContext context){
return AddStudent();
}));
},
child: Text("Add Student"),
),
ElevatedButton(
onPressed: (){
Navigator.push(context, MaterialPageRoute(builder: (BuildContext context){
return ListStudents();
}));
},
child: Text("List Student Record"),
),
]
),
),
);
}
}
In this way, you can create data on SQlite database in Flutter.