Flutter & SQLite: CRUD Example using sqflite package
Published in
6 min readJul 28, 2024
Create a Project
flutter create -e --org com.example sqlite
Packages
SQLite:- https://pub.dev/packages/sqflite
flutter pub add sqlite
config/database.dart
class Database {
static String database = "database.db";
// Tables
static String items = "items";
}
core/classes/sqlite.dart
import 'package:sqflite/sqflite.dart' as sql;
import '../../config/database.dart';
import '../../models/item_model.dart';
class Sqlite {
// ,createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
// created_at: the time that the item was created. It will be automatically handled by SQLite
static Future<sql.Database> db() async {
return sql.openDatabase(
Database.database,
version: 1,
onCreate: (sql.Database database, int version) async {
await database.execute(ItemModel().createTableQuery);
},
);
}
}
models/base_sql_model.dart
import 'package:flutter/material.dart';
import 'package:sqflite/sqflite.dart' as sql;
import '../core/classes/sqlite.dart';
abstract class BaseSQLModel {
String get table;
String get orderBy;
Future<List<Map<String, dynamic>>> getAll() async {
final db = await Sqlite.db();
return db.query(table, orderBy: orderBy);
}
Future<Map<String, dynamic>?> getById(int id) async {
final db = await Sqlite.db();
final List<Map<String, dynamic>> maps = await db.query(
table,
where: "id = ?",
whereArgs: [id],
);
return maps.isNotEmpty ? maps.first : null;
}
Future<Map<String, dynamic>?> getBySlug(String slug) async {
final db = await Sqlite.db();
final List<Map<String, dynamic>> maps = await db.query(
table,
where: "slug = ?",
whereArgs: [slug],
);
return maps.isNotEmpty ? maps.first : null;
}
static Future<Map<String, dynamic>?> getBy(
Map<String, dynamic> criteria) async {
final db = await Sqlite.db();
String whereClause = criteria.keys.map((key) => '$key = ?').join(' AND ');
List<dynamic> whereArgs = criteria.values.toList();
final result = await db.query('your_table_name',
where: whereClause, whereArgs: whereArgs);
return result.isNotEmpty ? result.first : null;
}
Future<int> store(obj) async {
try {
final db = await Sqlite.db();
final int id = await db.transaction<int>((txn) async {
// Perform the insert operation inside the transaction
return await txn.insert(table, obj,
conflictAlgorithm: sql.ConflictAlgorithm.replace);
});
return id;
} catch (e) {
debugPrint("MySql Error storing data: $e");
return -1; // Return a specific value to indicate the failure.
}
}
Future<int> update(int id, dynamic obj) async {
try {
final db = await Sqlite.db();
final int result = await db.transaction<int>((txn) async {
// Perform the update operation inside the transaction
return await txn.update(table, obj, where: "id = ?", whereArgs: [id]);
});
return result;
} catch (e) {
debugPrint("MySql Error updating data: $e");
return -1; // Return a specific value to indicate the failure.
}
}
Future<void> delete(int id) async {
final db = await Sqlite.db();
try {
await db.delete(table, where: "id = ?", whereArgs: [id]);
} catch (err) {
debugPrint("MySql Error deleting an item: $err");
}
}
Future<void> truncate() async {
final db = await Sqlite.db();
try {
await db.transaction((txn) async {
await txn.rawDelete('DELETE FROM $table');
await txn.delete('sqlite_sequence',
where: "name = ?",
whereArgs: [table]); // This resets the autoincrement counter.
});
} catch (err) {
debugPrint("MySql Error truncate an item: $err");
}
}
Future<int> updateOrCreate(
Map<String, dynamic> criteria, Map<String, dynamic> record) async {
try {
var dbRecord = await getBy(criteria);
if (dbRecord != null) {
return update(dbRecord['id'], record);
} else {
return store(record);
}
} catch (e) {
debugPrint("MySql Error updateOrCreate data: $e");
return -1; // Return a specific value to indicate the failure.
}
}
// create and update function using slug as unique key
Future updateOrCreateBySlug(String slug, dynamic record) async {
try {
var dbRecord = await getBySlug(slug);
if (dbRecord != null) {
return update(dbRecord['id'], record);
} else {
return store(record);
}
} catch (e) {
debugPrint("MySql Error storing data: $e");
return -1; // Return a specific value to indicate the failure.
}
}
}
models/item_model.dart
import '../config/database.dart';
import 'base_sql_model.dart';
class ItemModel extends BaseSQLModel {
@override
String get table => Database.items;
@override
String get orderBy => 'title';
String get createTableQuery => """
CREATE TABLE $table (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
title TEXT,
description TEXT,
createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)
""";
}
views/sql_view.dart
import 'package:flutter/material.dart';
import '../models/item_model.dart';
import '../widgets/widgets.dart';
class SqlView extends StatefulWidget {
const SqlView({super.key});
@override
State<SqlView> createState() => _SqlViewState();
}
class _SqlViewState extends State<SqlView> {
final ItemModel _recordModel = ItemModel();
List<Map<String, dynamic>> _records = [];
bool _isLoading = true;
// Form
final TextEditingController _titleCtr = TextEditingController();
final TextEditingController _descriptionCtr = TextEditingController();
@override
void initState() {
super.initState();
// _controller.orderBy = "title";
_refreshRecords();
}
_refreshRecords() async {
final data = await _recordModel.getAll();
setState(() {
_records = data;
_isLoading = false;
});
}
void _showForm(int? id) async {
if (id != null) {
final existingRecord =
_records.firstWhere((element) => element['id'] == id);
_titleCtr.text = existingRecord['title'];
_descriptionCtr.text = existingRecord['description'];
}
showModalBottomSheet(
context: context,
elevation: 5,
isScrollControlled: true,
builder: (_) => Container(
padding: EdgeInsets.only(
top: 15,
left: 15,
right: 15,
// this will prevent the soft keyboard from covering the text fields
bottom: MediaQuery.of(context).viewInsets.bottom + 120,
),
child: Column(
mainAxisSize: MainAxisSize.min,
crossAxisAlignment: CrossAxisAlignment.end,
children: [
MLTextField(labelText: "Title", controller: _titleCtr),
const SizedBox(
height: 10,
),
MLTextField(
labelText: "Description", controller: _descriptionCtr),
const SizedBox(
height: 10,
),
ElevatedButton(
onPressed: () async {
if (id != null) {
await _updateItem(id);
} else {
await _addItem();
}
// Clear the text fields
_titleCtr.text = '';
_descriptionCtr.text = '';
// Close the bottom sheet
Navigator.pop(context);
},
child: Text(id == null ? 'Create New' : 'Update'),
)
],
),
));
}
// Insert a new record to the database
Future<void> _addItem() async {
await _recordModel.store({
'title': _titleCtr.text,
'description': _descriptionCtr.text,
});
showMessage(context, "Successfully created a record!");
_refreshRecords();
}
// Update an existing record
Future<void> _updateItem(int id) async {
await _recordModel.update(id, {
'title': _titleCtr.text,
'description': _descriptionCtr.text,
});
showMessage(context, "Successfully update a record!");
_refreshRecords();
}
// Delete an item
void _deleteItem(int id) async {
await _recordModel.delete(id);
showMessage(context, "Successfully deleted a record!");
_refreshRecords();
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text("Sql View"),
actions: [
IconButton(
icon: const Icon(Icons.refresh),
onPressed: _refreshRecords,
)
],
),
floatingActionButton: FloatingActionButton(
child: const Icon(Icons.add),
onPressed: () => _showForm(null),
),
body: _isLoading
? fullScreenLoader()
: (_records.isEmpty)
? noDataFound()
: ListView.builder(
itemCount: _records.length,
itemBuilder: (context, index) => Card(
margin: const EdgeInsets.all(15),
child: ListTile(
title: Text(_records[index]['title']),
subtitle: Text(_records[index]['description']),
trailing: SizedBox(
width: 100,
child: Row(
children: [
IconButton(
icon: const Icon(Icons.edit),
onPressed: () =>
_showForm(_records[index]['id']),
),
IconButton(
icon: const Icon(Icons.delete),
onPressed: () =>
_deleteItem(_records[index]['id']),
),
],
),
)),
),
),
);
}
}
widgets/input.dart
import 'package:flutter/material.dart';
InputDecoration inputDecoration(String labelText, IconData iconData,
{String? prefix, String? helperText, String? hintText}) {
return InputDecoration(
// contentPadding: const EdgeInsets.symmetric(vertical: 12, horizontal: 12),
helperText: helperText,
labelText: labelText,
// labelStyle: TextStyle(color: Colors.grey),
// fillColor: Colors.grey.shade200,
// filled: true,
prefixText: prefix,
// prefixIcon: Icon(
// iconData,
// size: 20,
// ),
// prefixIconConstraints: const BoxConstraints(minWidth: 60),
// enabledBorder: OutlineInputBorder(
// borderRadius: BorderRadius.circular(30),
// borderSide: const BorderSide(color: Colors.black)),
// focusedBorder: OutlineInputBorder(
// borderRadius: BorderRadius.circular(30),
// borderSide: const BorderSide(color: Colors.black)),
// errorBorder: OutlineInputBorder(
// borderRadius: BorderRadius.circular(30),
// borderSide: const BorderSide(color: Colors.black)),
// border: OutlineInputBorder(
// borderRadius: BorderRadius.circular(30),
// borderSide: const BorderSide(color: Colors.black)),
);
}
class MLTextField extends StatelessWidget {
String labelText;
String? hintText;
TextEditingController controller = TextEditingController();
MLTextField(
{required this.labelText,
this.hintText,
required this.controller,
super.key});
@override
Widget build(BuildContext context) {
return TextFormField(
// validator: (value) {
// return customTextValidator(value);
// },
controller: controller,
// decoration: inputDecoration(labelText, Icons.abc),
decoration: InputDecoration(hintText: labelText),
);
}
}
widgets/widgets.dart
import 'package:flutter/material.dart';
// Fields
export 'input.dart';
showMessage(BuildContext context, String massage) {
return ScaffoldMessenger.of(context).showSnackBar(
SnackBar(
content: Text(massage),
),
);
}
Widget onScreenLoader() {
return const Padding(
padding: EdgeInsets.only(top: 10, bottom: 40),
child: Center(
child: CircularProgressIndicator(),
),
);
}
Widget noMoreContent() {
return Container(
padding: const EdgeInsets.only(top: 15, bottom: 15),
// color: Colors.amber,
child: const Center(
child: Text('You have fetched all of the content'),
),
);
}
Widget noDataFound() {
return const Center(
child: Text('No Data Found'),
);
}
Widget fullScreenLoader() {
return const Scaffold(
body: Center(
child: CircularProgressIndicator(),
),
);
}
main.dart
import 'package:flutter/material.dart';
import 'views/sql_view.dart';
void main() {
runApp(const MainApp());
}
class MainApp extends StatelessWidget {
const MainApp({super.key});
@override
Widget build(BuildContext context) {
return const MaterialApp(
home: SqlView(),
);
}
}
Learning Flutter more Example