sqlite in flutter

1// Get a location using getDatabasesPath
2var databasesPath = await getDatabasesPath();
3String path = join(databasesPath, 'demo.db');
4
5// Delete the database
6await deleteDatabase(path);
7
8// open the database
9Database database = await openDatabase(path, version: 1,
10    onCreate: (Database db, int version) async {
11  // When creating the db, create the table
12  await db.execute(
13      'CREATE TABLE Test (id INTEGER PRIMARY KEY, name TEXT, value INTEGER, num REAL)');
14});
15
16// Insert some records in a transaction
17await database.transaction((txn) async {
18  int id1 = await txn.rawInsert(
19      'INSERT INTO Test(name, value, num) VALUES("some name", 1234, 456.789)');
20  print('inserted1: $id1');
21  int id2 = await txn.rawInsert(
22      'INSERT INTO Test(name, value, num) VALUES(?, ?, ?)',
23      ['another name', 12345678, 3.1416]);
24  print('inserted2: $id2');
25});
26
27// Update some record
28int count = await database.rawUpdate(
29    'UPDATE Test SET name = ?, value = ? WHERE name = ?',
30    ['updated name', '9876', 'some name']);
31print('updated: $count');
32
33// Get the records
34List<Map> list = await database.rawQuery('SELECT * FROM Test');
35List<Map> expectedList = [
36  {'name': 'updated name', 'id': 1, 'value': 9876, 'num': 456.789},
37  {'name': 'another name', 'id': 2, 'value': 12345678, 'num': 3.1416}
38];
39print(list);
40print(expectedList);
41assert(const DeepCollectionEquality().equals(list, expectedList));
42
43// Count the records
44count = Sqflite
45    .firstIntValue(await database.rawQuery('SELECT COUNT(*) FROM Test'));
46assert(count == 2);
47
48// Delete a record
49count = await database
50    .rawDelete('DELETE FROM Test WHERE name = ?', ['another name']);
51assert(count == 1);
52
53// Close the database
54await database.close();
55

1import 'dart:async'; 
2import 'dart:io'; 
3import 'package:path/path.dart'; 
4import 'package:path_provider/path_provider.dart'; 
5import 'package:sqflite/sqflite.dart'; 
6import 'Product.dart'; 
7
8class SQLiteDbProvider {
9   SQLiteDbProvider._(); 
10   static final SQLiteDbProvider db = SQLiteDbProvider._(); 
11   static Database _database; 
12   
13   Future<Database> get database async {
14      if (_database != null) 
15      return _database; 
16      _database = await initDB(); 
17      return _database; 
18   } 
19   initDB() async {
20      Directory documentsDirectory = await 
21      getApplicationDocumentsDirectory(); 
22      String path = join(documentsDirectory.path, "ProductDB.db"); 
23      return await openDatabase(
24         path, version: 1, 
25         onOpen: (db) {}, 
26         onCreate: (Database db, int version) async {
27            await db.execute(
28               "CREATE TABLE Product (" 
29               "id INTEGER PRIMARY KEY," 
30               "name TEXT," 
31               "description TEXT," 
32               "price INTEGER," 
33               "image TEXT"")"
34            ); 
35            await db.execute(
36               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
37               values (?, ?, ?, ?, ?)", 
38               [1, "iPhone", "iPhone is the stylist phone ever", 1000, "iphone.png"]
39            ); 
40            await db.execute( 
41               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
42               values (?, ?, ?, ?, ?)", 
43               [2, "Pixel", "Pixel is the most feature phone ever", 800, "pixel.png"]
44            );
45            await db.execute(
46               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
47               values (?, ?, ?, ?, ?)", 
48               [3, "Laptop", "Laptop is most productive development tool", 2000, "laptop.png"]
49            ); 
50            await db.execute( 
51               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
52               values (?, ?, ?, ?, ?)", 
53               [4, "Tablet", "Laptop is most productive development tool", 1500, "tablet.png"]
54            ); 
55            await db.execute( 
56               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
57               values (?, ?, ?, ?, ?)", 
58               [5, "Pendrive", "Pendrive is useful storage medium", 100, "pendrive.png"]
59            );
60            await db.execute( 
61               "INSERT INTO Product ('id', 'name', 'description', 'price', 'image') 
62               values (?, ?, ?, ?, ?)", 
63               [6, "Floppy Drive", "Floppy drive is useful rescue storage medium", 20, "floppy.png"]
64            ); 
65         }
66      ); 
67   }
68   Future<List<Product>> getAllProducts() async {
69      final db = await database; 
70      List<Map> results = await db.query(
71         "Product", columns: Product.columns, orderBy: "id ASC"
72      ); 
73      List<Product> products = new List();   
74      results.forEach((result) {
75         Product product = Product.fromMap(result); 
76         products.add(product); 
77      }); 
78      return products; 
79   } 
80   Future<Product> getProductById(int id) async {
81      final db = await database; 
82      var result = await db.query("Product", where: "id = ", whereArgs: [id]); 
83      return result.isNotEmpty ? Product.fromMap(result.first) : Null; 
84   } 
85   insert(Product product) async { 
86      final db = await database; 
87      var maxIdResult = await db.rawQuery("SELECT MAX(id)+1 as last_inserted_id FROM Product"); 
88      var id = maxIdResult.first["last_inserted_id"]; 
89      var result = await db.rawInsert(
90         "INSERT Into Product (id, name, description, price, image)" 
91         " VALUES (?, ?, ?, ?, ?)", 
92         [id, product.name, product.description, product.price, product.image] 
93      ); 
94      return result; 
95   } 
96   update(Product product) async { 
97      final db = await database; 
98      var result = await db.update(
99         "Product", product.toMap(), where: "id = ?", whereArgs: [product.id]
100      ); 
101      return result; 
102   } 
103   delete(int id) async { 
104      final db = await database; 
105      db.delete("Product", where: "id = ?", whereArgs: [id]);
106   } 
107}