Flutter SQLite CRUD Example — Learn Local Database with Ease
Introduction
When building Flutter apps, sometimes you don’t need a cloud database like Firebase. Instead, a local lightweight database is enough — for example, when storing notes, tasks, or offline data. Flutter provides an easy way to work with SQLite using the sqflite plugin.
In this post, we’ll walk through a working SQLite CRUD (Create, Read, Update, Delete) example taken from my GitHub repo: SQL-Lite.
Keywords
Flutter SQLite, Flutter local database, sqflite CRUD, SQLite in Flutter, DatabaseHandler Flutter
Importance
- Works offline — perfect for apps that don’t always have internet.
- Lightweight yet powerful.
- Easy to integrate with Flutter using the sqflite package.
- Supports structured queries, making it great for CRUD-based apps.
👉 Contact on WhatsApp
Explanation
1. The Model — book.dart
class Book {
int? id;
String name;
double price;
Book({this.id, required this.name, required this.price});
Map<String, dynamic> toMap() {
return {
'id': id,
'name': name,
'price': price,
};
}
@override
String toString() {
return 'Book{id: $id, name: $name, price: $price}';
}
}
2. Database Handler — databasehandler.dart
class DatabaseHandler {
static Future<Database> initialize() async {
final dbPath = await getDatabasesPath();
return openDatabase(
join(dbPath, 'books.db'),
onCreate: (db, version) {
return db.execute(
'CREATE TABLE books(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price REAL)',
);
},
version: 1,
);
}
Future<int> insertBook(Book book) async {
final db = await initialize();
return await db.insert('books', book.toMap());
}
Future<List<Book>> books() async {
final db = await initialize();
final List<Map<String, dynamic>> maps = await db.query('books');
return List.generate(maps.length, (i) {
return Book(
id: maps[i]['id'],
name: maps[i]['name'],
price: maps[i]['price'],
);
});
}
}
3. The UI — vsjsqlite.dart
The app uses a simple Flutter UI with buttons and text fields. You can add a book, view the list, update, or delete it.
4. Entry Point — main.dart
void main() async {
WidgetsFlutterBinding.ensureInitialized();
await DatabaseHandler.initialize();
runApp(MyApp());
}
New: DartPad (web) version — using localStorage
This single-file Flutter app runs in DartPad (Flutter) and uses the browser localStorage for persistence.
Copy the code below and paste it into DartPad (choose Flutter), then press Run.
// Paste this entire file into DartPad (Flutter) and run.
// Uses browser localStorage to store a list of books as JSON.
// Works only in Flutter web (DartPad Flutter).
import 'dart:convert';
import 'dart:html' as html; // for localStorage
import 'package:flutter/material.dart';
void main() {
runApp(const MyApp());
}
/// Keys used in localStorage
const String storageKey = 'vsj_books';
/// Simple Book model
class Book {
int? id; // generated client-side
String name;
double price;
Book({this.id, required this.name, required this.price});
Map toMap() => {
'id': id,
'name': name,
'price': price,
};
factory Book.fromMap(Map m) => Book(
id: m['id'] as int?,
name: m['name'] as String? ?? '',
price: (m['price'] is int)
? (m['price'] as int).toDouble()
: (m['price'] as double? ?? 0.0),
);
@override
String toString() => 'Book{id: $id, name: $name, price: $price}';
}
/// Simple storage wrapper using browser localStorage
class LocalBookStorage {
/// Read all books
static List readAll() {
final raw = html.window.localStorage[storageKey];
if (raw == null) return [];
try {
final List list = jsonDecode(raw) as List;
final books = list
.map((e) => Book.fromMap(Map.from(e)))
.toList();
return books;
} catch (e) {
// If parsing fails, return empty and reset storage to avoid repeated errors
html.window.localStorage.remove(storageKey);
return [];
}
}
/// Write the full list
static void writeAll(List books) {
final List maps =
books.map((b) => b.toMap()).toList();
html.window.localStorage[storageKey] = jsonEncode(maps);
}
/// Insert book (generates id)
static Book insert(Book book) {
final books = readAll();
final nextId = (books.isEmpty) ? 1 : (books.map((b) => b.id ?? 0).reduce((a, b) => a > b ? a : b) + 1);
final toSave = Book(id: nextId, name: book.name, price: book.price);
books.add(toSave);
writeAll(books);
return toSave;
}
/// Update book by id
static bool update(Book book) {
final books = readAll();
final idx = books.indexWhere((b) => b.id == book.id);
if (idx == -1) return false;
books[idx] = book;
writeAll(books);
return true;
}
/// Delete by id
static bool deleteById(int id) {
final books = readAll();
final initialLen = books.length;
books.removeWhere((b) => b.id == id);
if (books.length == initialLen) return false;
writeAll(books);
return true;
}
/// Clear all (for convenience)
static void clearAll() {
html.window.localStorage.remove(storageKey);
}
}
class MyApp extends StatelessWidget {
const MyApp({super.key});
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Flutter localStorage CRUD',
theme: ThemeData(
primarySwatch: Colors.blue,
),
home: const BookHomePage(),
debugShowCheckedModeBanner: false,
);
}
}
class BookHomePage extends StatefulWidget {
const BookHomePage({super.key});
@override
State createState() => _BookHomePageState();
}
class _BookHomePageState extends State {
List books = [];
final TextEditingController nameController = TextEditingController();
final TextEditingController priceController = TextEditingController();
@override
void initState() {
super.initState();
_loadBooks();
}
void _loadBooks() {
setState(() {
books = LocalBookStorage.readAll();
});
}
void _addBook() {
final name = nameController.text.trim();
final priceText = priceController.text.trim();
if (name.isEmpty || priceText.isEmpty) {
_showSnack('Please enter name and price');
return;
}
final price = double.tryParse(priceText);
if (price == null) {
_showSnack('Invalid price');
return;
}
final saved = LocalBookStorage.insert(Book(name: name, price: price));
nameController.clear();
priceController.clear();
_showSnack('Added "${saved.name}" (id:${saved.id})');
_loadBooks();
}
void _editBook(Book b) async {
final edited = await showDialog(
context: context,
builder: (_) => _EditDialog(book: b),
);
if (edited != null) {
final ok = LocalBookStorage.update(edited);
if (ok) {
_showSnack('Updated "${edited.name}"');
_loadBooks();
} else {
_showSnack('Update failed (id not found)');
}
}
}
void _deleteBook(Book b) {
final ok = LocalBookStorage.deleteById(b.id ?? -1);
if (ok) {
_showSnack('Deleted "${b.name}"');
_loadBooks();
} else {
_showSnack('Delete failed');
}
}
void _clearAll() {
LocalBookStorage.clearAll();
_loadBooks();
_showSnack('All books cleared');
}
void _showSnack(String msg) {
ScaffoldMessenger.of(context).showSnackBar(SnackBar(content: Text(msg)));
}
Widget _buildList() {
if (books.isEmpty) {
return const Center(
child: Text('No books saved yet. Add one!'),
);
}
return ListView.separated(
itemCount: books.length,
separatorBuilder: (_, __) => const Divider(height: 1),
itemBuilder: (context, index) {
final b = books[index];
return ListTile(
leading: CircleAvatar(child: Text('${b.id}')),
title: Text(b.name),
subtitle: Text('Price: ₹${b.price.toStringAsFixed(2)}'),
trailing: Row(
mainAxisSize: MainAxisSize.min,
children: [
IconButton(
icon: const Icon(Icons.edit),
tooltip: 'Edit',
onPressed: () => _editBook(b),
),
IconButton(
icon: const Icon(Icons.delete),
tooltip: 'Delete',
onPressed: () => _confirmDelete(b),
),
],
),
);
},
);
}
void _confirmDelete(Book b) {
showDialog(
context: context,
builder: (_) => AlertDialog(
title: const Text('Delete book'),
content: Text('Delete "${b.name}" (id: ${b.id})?'),
actions: [
TextButton(onPressed: () => Navigator.pop(context, false), child: const Text('Cancel')),
TextButton(
onPressed: () {
Navigator.pop(context, true);
},
child: const Text('Delete')),
],
),
).then((confirmed) {
if (confirmed == true) _deleteBook(b);
});
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(
title: const Text('Flutter localStorage CRUD'),
actions: [
IconButton(
icon: const Icon(Icons.download),
tooltip: 'Print JSON',
onPressed: () {
final json = html.window.localStorage[storageKey] ?? '[]';
showDialog(
context: context,
builder: (_) => AlertDialog(
title: const Text('Stored JSON'),
content: SingleChildScrollView(child: SelectableText(json)),
actions: [TextButton(onPressed: () => Navigator.pop(context), child: const Text('Close'))],
),
);
},
),
IconButton(
icon: const Icon(Icons.delete_forever),
tooltip: 'Clear all',
onPressed: () {
showDialog(
context: context,
builder: (_) => AlertDialog(
title: const Text('Clear all data'),
content: const Text('This will remove all saved books from localStorage. Continue?'),
actions: [
TextButton(onPressed: () => Navigator.pop(context, false), child: const Text('Cancel')),
TextButton(onPressed: () => Navigator.pop(context, true), child: const Text('Clear')),
],
),
).then((ok) {
if (ok == true) _clearAll();
});
},
),
],
),
body: Column(
children: [
Padding(
padding: const EdgeInsets.all(12.0),
child: _buildAddCard(),
),
const Divider(height: 1),
Expanded(child: _buildList()),
],
),
);
}
Widget _buildAddCard() {
return Card(
elevation: 2,
child: Padding(
padding: const EdgeInsets.all(12.0),
child: Column(
children: [
const Align(
alignment: Alignment.centerLeft,
child: Text('Add a book', style: TextStyle(fontSize: 16, fontWeight: FontWeight.bold)),
),
const SizedBox(height: 8),
TextField(
controller: nameController,
decoration: const InputDecoration(labelText: 'Book name', border: OutlineInputBorder()),
),
const SizedBox(height: 8),
TextField(
controller: priceController,
decoration: const InputDecoration(labelText: 'Price', border: OutlineInputBorder()),
keyboardType: const TextInputType.numberWithOptions(decimal: true),
),
const SizedBox(height: 8),
Row(
children: [
ElevatedButton.icon(
icon: const Icon(Icons.add),
label: const Text('Add Book'),
onPressed: _addBook,
),
const SizedBox(width: 12),
ElevatedButton.icon(
icon: const Icon(Icons.refresh),
label: const Text('Reload'),
style: ElevatedButton.styleFrom(backgroundColor: Colors.grey[600]),
onPressed: _loadBooks,
)
],
),
],
),
),
);
}
}
/// Dialog to edit an existing book
class _EditDialog extends StatefulWidget {
final Book book;
const _EditDialog({required this.book});
@override
State<_EditDialog> createState() => _EditDialogState();
}
class _EditDialogState extends State<_EditDialog> {
late TextEditingController nameC;
late TextEditingController priceC;
@override
void initState() {
super.initState();
nameC = TextEditingController(text: widget.book.name);
priceC = TextEditingController(text: widget.book.price.toString());
}
@override
void dispose() {
nameC.dispose();
priceC.dispose();
super.dispose();
}
void _submit() {
final name = nameC.text.trim();
final price = double.tryParse(priceC.text.trim());
if (name.isEmpty || price == null) {
ScaffoldMessenger.of(context).showSnackBar(const SnackBar(content: Text('Please enter valid name and price')));
return;
}
final edited = Book(id: widget.book.id, name: name, price: price);
Navigator.pop(context, edited);
}
@override
Widget build(BuildContext context) {
return AlertDialog(
title: const Text('Edit book'),
content: Column(
mainAxisSize: MainAxisSize.min,
children: [
TextField(controller: nameC, decoration: const InputDecoration(labelText: 'Book name')),
const SizedBox(height: 8),
TextField(controller: priceC, decoration: const InputDecoration(labelText: 'Price')),
],
),
actions: [
TextButton(onPressed: () => Navigator.pop(context, null), child: const Text('Cancel')),
TextButton(onPressed: _submit, child: const Text('Save')),
],
);
}
}
Tip: After copying, open DartPad (choose the Flutter pad), paste the code into main.dart, and press Run.
Complexity
The DartPad/localStorage example uses simple JSON read/write:
- Insert / Update / Delete — O(1) average per item
- Read all — O(n) where n is number of books
How to Run
git clone https://github.com/Varanasi-Software-Junction/SQL-Lite.git
cd SQL-Lite
flutter pub get
flutter run
Conclusion
SQLite is great for mobile apps; for quick web demos DartPad + localStorage is simpler. Both approaches show the same CRUD flow and help you get a working app fast.
- ⭐ Star the repo on GitHub: SQL-Lite
- ⬇️ Download Project (ZIP)
- 💬 Need help? Contact on WhatsApp
0 Comments