SQLite with sqflite in Flutter: solved CRUD exercise

  2 minutes

If you are looking for SQLite with sqflite in Flutter, this solved exercise gives you a practical implementation pattern you can reuse in real projects.

Build a screen with:

  • create local tasks table
  • insert records
  • list records from local database
import 'package:flutter/material.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

Future<Database> openDb() async {
  return openDatabase(
    join(await getDatabasesPath(), 'tasks.db'),
    version: 1,
    onCreate: (db, _) {
      return db.execute('CREATE TABLE tasks(id INTEGER PRIMARY KEY, title TEXT)');
    },
  );
}

void main() => runApp(const MaterialApp(home: TasksPage()));

class TasksPage extends StatefulWidget {
  const TasksPage({super.key});

  @override
  State<TasksPage> createState() => _TasksPageState();
}

class _TasksPageState extends State<TasksPage> {
  final ctrl = TextEditingController();
  List<Map<String, dynamic>> tasks = [];

  Future<void> refreshTasks() async {
    final db = await openDb();
    tasks = await db.query('tasks', orderBy: 'id DESC');
    setState(() {});
  }

  Future<void> addTask() async {
    final db = await openDb();
    await db.insert('tasks', {'title': ctrl.text.trim()});
    ctrl.clear();
    await refreshTasks();
  }

  @override
  void initState() {
    super.initState();
    refreshTasks();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: const Text('sqflite CRUD')),
      body: Column(
        children: [
          Padding(
            padding: const EdgeInsets.all(12),
            child: Row(
              children: [
                Expanded(child: TextField(controller: ctrl, decoration: const InputDecoration(labelText: 'Task'))),
                IconButton(onPressed: addTask, icon: const Icon(Icons.add)),
              ],
            ),
          ),
          Expanded(
            child: ListView.builder(
              itemCount: tasks.length,
              itemBuilder: (_, i) => ListTile(title: Text(tasks[i]['title'] as String)),
            ),
          ),
        ],
      ),
    );
  }
}

Tasks are stored locally and persist between app sessions.

  • Recreating the DB layer repeatedly.
  • Missing await on write operations.
  • Mixing DB logic and UI rendering.

A standard base for offline-first flows like notes, trackers, and local catalogs.

Yes, for many local persistence scenarios.

Not always. For simple apps, direct queries can be enough.

Many apps use both: local cache plus remote source of truth.