sqflite code example

Example 1: delete query in sqlite flutter

// 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 {
  // When creating the db, create the table
  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("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');
});

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

// 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);
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 = ?', ['another name']);
assert(count == 1);

// Close the database
await database.close();

Example 2: import 'package:sqfLite/sqfLite.dart';

import 'package:sqflite/sqflite.dart';

Example 3: flutter sqflite DatabaseHelper

import 'dart:io' as io;

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

class DatabaseHelper {
 static final DatabaseHelper _instance = new DatabaseHelper.internal();

 factory DatabaseHelper() => _instance;
 static Database _db;

 DatabaseHelper.internal();

 initDb() async {
   io.Directory documentsDirectory = await getApplicationDocumentsDirectory();
   String path = join(documentsDirectory.path, "database_name.db");
   _db = await openDatabase(path, version: 1, onCreate: _onCreate);
 }

 Database get db {
   return _db;
 }

 void _onCreate(Database db, int version) async {
   // When creating the db, create the table
   await db.execute(
       'CREATE TABLE todos (id INTEGER PRIMARY KEY AUTOINCREMENT,'
'item_name TEXT,'
           'date_created TEXT')';
 }
}

Example 4: flutter sqflite DatabaseHelper

class Todo{
 int id;
 String itemName;
 String dateCreated;

 Todo(
     {this.id,
     this.itemName,
     this.dateCreated})

 //to be used when inserting a row in the table
 Map<String, dynamic> toMapWithoutId() {
   final map = new Map<String, dynamic>();
   map["item_name"] = itemName;
   map["date_created"] = dateCreated;
   return map;
 }

Map<String, dynamic> toMap() {
   final map = new Map<String, dynamic>();
   map["id"] = id;
   map["item_name"] = itemName;
   map["date_created"] = dateCreated;
   return map;
 }

 //to be used when converting the row into object
 factory Todo.fromMap(Map<String, dynamic> data) => new Todo(
     id: data['id'],
     itemName: data['item_name'],
     dateCreated: data['date_created']
 );
}