Flutter SQLite CRUD with sqflite: Complete Tutorial (2026)
Last Updated: | Author: Deep Singh
SQLite is the most widely deployed database in the world — and for good reason. It's fast, it runs entirely on-device, and it needs no server. In Flutter, the sqflite plugin gives you full access to SQLite, so you can store structured data locally without touching a backend.
In this tutorial, you'll build a small student records app that performs the two most common database operations: insert and fetch. We'll cover setting up the database, creating a table, inserting rows, and displaying them in a list. By the end, you'll have a solid foundation for any local persistence feature in Flutter.
Adding the Plugins
We need two plugins: one for the SQLite database itself, and one for finding the right path on the device to store the database file.
Run these commands in your project folder:
flutter pub add sqflite
flutter pub add path_provider
flutter pub add pathOr add them manually to your pubspec.yaml file:
dependencies:
sqflite: ^2.3.3
path_provider: ^2.1.4
path: ^1.9.0Then run flutter pub get.
Note on versions: The versions above are current as of 2026. Older tutorials may reference sqflite: ^2.0.0+3 and path_provider: ^2.0.1, which still work but miss years of fixes and improvements. Use the latest unless you have a specific reason to pin an older version.
The Database Class (operations.dart)
This file contains all the SQLite logic. It follows the singleton pattern — there's only ever one instance, and one open database connection.
import 'dart:io';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'main.dart';
class MyDatabase {
static const String DATABASE_NAME = 'mycollege.db';
static const String TABLE_NAME = 'students';
static const String ROLL = 'roll';
static const String NAME = 'name';
static const String MARKS = 'marks';
static const int DATABASE_VERSION = 1;
MyDatabase._();
static final MyDatabase instance = MyDatabase._();
static Database? _database;
Future<Database> get database async {
if (_database != null) return _database!;
_database = await _initDB();
return _database!;
}
Future<Database> _initDB() async {
final Directory documentsDirectory =
await getApplicationDocumentsDirectory();
final String dbPath = join(documentsDirectory.path, DATABASE_NAME);
return await openDatabase(
dbPath,
version: DATABASE_VERSION,
onCreate: _onCreate,
);
}
Future<void> _onCreate(Database db, int version) async {
await db.execute('CREATE TABLE $TABLE_NAME ('
'$ROLL INTEGER PRIMARY KEY, '
'$NAME TEXT, '
'$MARKS REAL)');
}
Future<int> insert(Student student) async {
final db = await database;
return await db.insert(
TABLE_NAME,
student.toMap(),
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
Future<List<Student>> getAllStudents() async {
final db = await database;
final List<Map<String, dynamic>> maps = await db.query(TABLE_NAME);
return maps.map((map) => Student.fromMap(map)).toList();
}
Future<Student?> fetchStudentByRoll(int roll) async {
final db = await database;
final res = await db.query(
TABLE_NAME,
where: '$ROLL = ?',
whereArgs: [roll],
);
if (res.isEmpty) return null;
return Student.fromMap(res.first);
}
}The UI (main.dart)
The UI has three text fields, three buttons, and a list area. Each button does one thing: insert, fetch a single student, or fetch everything.
import 'package:flutter/material.dart';
import 'operations.dart';
void main() {
runApp(const MyApp());
}
class MyApp extends StatelessWidget {
const MyApp({super.key});
@override
Widget build(BuildContext context) {
return MaterialApp(
title: 'Flutter Demo',
theme: ThemeData(primarySwatch: Colors.blue),
home: const MyHomePage(title: 'SQLite Demo'),
);
}
}
class MyHomePage extends StatefulWidget {
const MyHomePage({super.key, required this.title});
final String title;
@override
State<MyHomePage> createState() => _MyHomePageState();
}
class _MyHomePageState extends State<MyHomePage> {
final TextEditingController _rollController = TextEditingController();
final TextEditingController _nameController = TextEditingController();
final TextEditingController _marksController = TextEditingController();
final FocusNode _rollFocus = FocusNode();
final FocusNode _nameFocus = FocusNode();
final FocusNode _marksFocus = FocusNode();
Widget _listWidget = const SizedBox.shrink();
@override
void dispose() {
_rollController.dispose();
_nameController.dispose();
_marksController.dispose();
_rollFocus.dispose();
_nameFocus.dispose();
_marksFocus.dispose();
super.dispose();
}
@override
Widget build(BuildContext context) {
return Scaffold(
appBar: AppBar(title: const Text('Form Data')),
body: SingleChildScrollView(
padding: const EdgeInsets.all(20),
child: Column(
children: [
TextFormField(
controller: _rollController,
focusNode: _rollFocus,
keyboardType: TextInputType.number,
decoration: const InputDecoration(
labelText: 'Enter Roll',
border: OutlineInputBorder(),
),
),
const SizedBox(height: 10),
TextFormField(
controller: _nameController,
focusNode: _nameFocus,
decoration: const InputDecoration(
labelText: 'Enter Name',
border: OutlineInputBorder(),
),
),
const SizedBox(height: 10),
TextFormField(
controller: _marksController,
focusNode: _marksFocus,
keyboardType: TextInputType.number,
decoration: const InputDecoration(
labelText: 'Enter Marks',
border: OutlineInputBorder(),
),
),
const SizedBox(height: 10),
ButtonBar(
alignment: MainAxisAlignment.start,
children: [
ElevatedButton(
onPressed: _onInsert,
child: const Text('Insert'),
),
ElevatedButton(
onPressed: _onFetchByRoll,
child: const Text('Fetch By Roll'),
),
ElevatedButton(
onPressed: _onFetchAll,
child: const Text('Fetch All'),
),
],
),
_listWidget,
],
),
),
);
}
Future<void> _onInsert() async {
if (!_validate()) return;
final student = Student(
roll: int.parse(_rollController.text),
name: _nameController.text.trim(),
marks: double.parse(_marksController.text),
);
final id = await MyDatabase.instance.insert(student);
debugPrint('Inserted row id: $id');
_clearFields();
}
Future<void> _onFetchByRoll() async {
if (!_validate()) return;
final roll = int.parse(_rollController.text);
final student = await MyDatabase.instance.fetchStudentByRoll(roll);
if (student != null) {
debugPrint('Found: ${student.name} (${student.marks})');
} else {
debugPrint('No student with roll $roll');
}
}
Future<void> _onFetchAll() async {
setState(() {
_listWidget = _buildList();
});
}
Widget _buildList() {
return FutureBuilder<List<Student>>(
future: MyDatabase.instance.getAllStudents(),
builder: (context, snapshot) {
if (snapshot.connectionState == ConnectionState.waiting) {
return const Padding(
padding: EdgeInsets.all(20),
child: CircularProgressIndicator(),
);
}
if (snapshot.hasError) {
return Padding(
padding: const EdgeInsets.all(20),
child: Text('Error: ${snapshot.error}'),
);
}
final students = snapshot.data ?? [];
if (students.isEmpty) {
return const Padding(
padding: EdgeInsets.all(20),
child: Text('No records yet.'),
);
}
return ListView.builder(
shrinkWrap: true,
physics: const NeverScrollableScrollPhysics(),
itemCount: students.length,
itemBuilder: (context, index) {
final s = students[index];
return ListTile(
leading: Text(s.roll.toString()),
title: Center(child: Text(s.name)),
trailing: Text(s.marks.toString()),
);
},
);
},
);
}
bool _validate() {
if (_rollController.text.trim().isEmpty) {
_showSnack('Please enter roll');
_rollFocus.requestFocus();
return false;
}
if (_nameController.text.trim().isEmpty) {
_showSnack('Please enter name');
_nameFocus.requestFocus();
return false;
}
if (_marksController.text.trim().isEmpty) {
_showSnack('Please enter marks');
_marksFocus.requestFocus();
return false;
}
return true;
}
void _showSnack(String message) {
ScaffoldMessenger.of(context)
.showSnackBar(SnackBar(content: Text(message)));
}
void _clearFields() {
_rollController.clear();
_nameController.clear();
_marksController.clear();
}
}
class Student {
final int roll;
final String name;
final double marks;
const Student({required this.roll, required this.name, required this.marks});
factory Student.fromMap(Map<String, dynamic> map) {
return Student(
roll: map['roll'] as int,
name: map['name'] as String,
marks: (map['marks'] as num).toDouble(),
);
}
Map<String, dynamic> toMap() {
return {
'roll': roll,
'name': name,
'marks': marks,
};
}
}How the Code Works
Let's walk through the important parts.
1. The Singleton Database Class
MyDatabase has a private constructor (MyDatabase._()) and a single static instance. This ensures there's only ever one connection to the database — opening it multiple times would cause conflicts and waste resources.
2. Lazy Initialization
Future<Database> get database async {
if (_database != null) return _database!;
_database = await _initDB();
return _database!;
}The database is only opened the first time someone asks for it. Subsequent calls return the cached connection. This is important for performance — opening a SQLite database is not free.
3. Finding the Right Path
final Directory documentsDirectory =
await getApplicationDocumentsDirectory();
final String dbPath = join(documentsDirectory.path, DATABASE_NAME);path_provider gives you a platform-appropriate directory where the app can store persistent files. Then path.join() builds a platform-correct path to the database file. The path package handles the slash direction for you — don't concatenate strings manually.
4. Creating the Table
await db.execute('CREATE TABLE $TABLE_NAME ('
'$ROLL INTEGER PRIMARY KEY, '
'$NAME TEXT, '
'$MARKS REAL)');onCreate is called only the first time the database is created. Use INTEGER PRIMARY KEY for the roll number — this enforces uniqueness automatically. TEXT is for strings, REAL for floating-point numbers.
5. Inserting
conflictAlgorithm: ConflictAlgorithm.replace means: if a row with the same primary key already exists, replace it instead of throwing an error. This effectively makes insert behave like an upsert.
6. Fetching All
db.query(TABLE_NAME) returns a List<Map<String, dynamic>> — one map per row. We map each one through Student.fromMap to get typed objects, which is much safer to work with than raw maps.
7. Fetching One
final res = await db.query(
TABLE_NAME,
where: '$ROLL = ?',
whereArgs: [roll],
);The ? placeholder plus whereArgs is the safe way to pass values into a SQL query. Never build the query by string concatenation — that opens the door to SQL injection.
8. The FutureBuilder for the List
When the "Fetch All" button is tapped, we rebuild the widget tree and let a FutureBuilder handle the async database query. It shows a spinner while loading, an error message if something goes wrong, an empty state if no records exist, and the list itself when data is available.
Common Pitfalls
- Don't call
getApplicationDocumentsDirectory()from a widget's build method. It's an async call — put it ininitStateor, better, inside the database class as shown. - Don't open the database more than once. If you call
openDatabase()in multiple places without caching, you'll end up with file lock errors. - Remember to dispose of controllers and focus nodes. Failing to do so leaks memory, especially if the widget is rebuilt many times.
- Don't return raw maps from the database layer. Always convert to typed objects. It catches schema errors at the boundary instead of somewhere deep in the UI.
- Use transactions for bulk inserts. If you're inserting hundreds of rows, wrap them in a transaction — SQLite will be dramatically faster.
- Handle migration. This tutorial uses
version: 1with noonUpgrade. When you change the schema later, you'll need to provide one, or you'll crash on existing installs. - Don't use SQLite on the web.
sqflitedoesn't work in browsers. For Flutter web, usesqflite_common_ffi_webor a different storage solution entirely.
Wrapping Up
You now have a working SQLite setup in Flutter — insert, fetch one, and fetch all. The same pattern extends naturally to update, delete, and complex queries. Once you're comfortable with the basics here, the next steps are:
- Add update and delete methods —
db.update()anddb.delete()follow the same shape asinsert. - Add migration logic — implement
onUpgradeso schema changes don't break existing users. - Use transactions for bulk operations. They're a huge speed win.
- Consider Drift or Moor if you want compile-time safe queries instead of raw SQL strings.
For anything more than simple key-value or list storage, SQLite is usually a better choice than shared_preferences. It's fast, reliable, and works entirely offline — a perfect fit for mobile.
You might also find these helpful:
No comments:
Post a Comment