Flutter SQLite with sqflite

Shared preferences store small settings. Apps that manage hundreds of notes, contacts, or tasks need a real database. SQLite is a compact database engine built into Android and iOS. The sqflite package connects Flutter to it. Data stays on the device, survives app restarts, and works without an internet connection.

Preferences or Database

Need	Best Tool
Theme choice, login flag	SharedPreferences
Lists of records, search, sorting	SQLite
Data shared across devices	Cloud database such as Firebase

Database Basics

A SQLite database holds tables, much like spreadsheets. Each row stores one record, and each column stores one property.

  Table: notes
  ┌────┬─────────────┬──────────────────┐
  │ id │ title       │ body             │
  ├────┼─────────────┼──────────────────┤
  │ 1  │ Groceries   │ Milk, eggs       │
  │ 2  │ Ideas       │ Build a new app  │
  └────┴─────────────┴──────────────────┘

Installing Packages

flutter pub add sqflite path

The path package builds a correct file path for the database on every platform. The sqflite package targets Android, iOS, and macOS. Web and desktop apps need a different package such as sqflite_common_ffi.

Creating a Model Class

A model converts between Dart objects and database rows. Rows travel as maps of text keys and values.

class Note {
  final int? id;
  final String title;
  final String body;

  Note({this.id, required this.title, required this.body});

  Map<String, Object?> toMap() {
    return {'id': id, 'title': title, 'body': body};
  }

  factory Note.fromMap(Map<String, Object?> map) {
    return Note(
      id: map['id'] as int,
      title: map['title'] as String,
      body: map['body'] as String,
    );
  }
}

The id field uses a nullable type because the database assigns the number after insertion.

Opening the Database

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

Future<Database> openNotesDb() async {
  final dbPath = await getDatabasesPath();
  return openDatabase(
    join(dbPath, 'notes.db'),
    version: 1,
    onCreate: (db, version) {
      return db.execute(
        'CREATE TABLE notes(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, body TEXT)',
      );
    },
  );
}

The onCreate callback runs only once, when the file does not exist yet. Raise the version number and add an onUpgrade callback when you change the table design in a later app release.

CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These four actions cover most database work.

  Create → insert()
  Read   → query()
  Update → update()
  Delete → delete()

Create

Future<int> addNote(Database db, Note note) async {
  return db.insert('notes', note.toMap());
}

Read

Future<List<Note>> getNotes(Database db) async {
  final rows = await db.query('notes', orderBy: 'id DESC');
  return rows.map((row) => Note.fromMap(row)).toList();
}

Update

Future<void> updateNote(Database db, Note note) async {
  await db.update(
    'notes',
    note.toMap(),
    where: 'id = ?',
    whereArgs: [note.id],
  );
}

Delete

Future<void> deleteNote(Database db, int id) async {
  await db.delete('notes', where: 'id = ?', whereArgs: [id]);
}

Always Use whereArgs

The question mark acts as a placeholder, and the whereArgs list fills it safely. Never join user text into a query string by hand, because that practice opens the door to SQL injection attacks.

Showing Notes in the UI

Combine the database with a FutureBuilder and ListView.builder.

FutureBuilder<List<Note>>(
  future: getNotes(db),
  builder: (context, snapshot) {
    if (!snapshot.hasData) {
      return Center(child: CircularProgressIndicator());
    }
    final notes = snapshot.data!;
    return ListView.builder(
      itemCount: notes.length,
      itemBuilder: (context, index) {
        return ListTile(
          title: Text(notes[index].title),
          subtitle: Text(notes[index].body),
        );
      },
    );
  },
)

Call setState after each insert or delete to reload the list.

One Database Helper

Open the database once and reuse it. A singleton class keeps one connection for the whole app.

class NotesDatabase {
  static final NotesDatabase instance = NotesDatabase._internal();
  NotesDatabase._internal();

  Database? _db;

  Future<Database> get database async {
    _db ??= await openNotesDb();
    return _db!;
  }
}

Good Practices

• Run all database calls with await so the screen stays smooth.
• Store dates as text in ISO format or as integer timestamps.
• Avoid storing passwords or sensitive personal data in plain form.
• Back up important data to a server when users expect it on a new phone.

Handling Schema Changes

Every app update may change the table design, such as adding a "created" column. Users already have the old database file on their phones. The onUpgrade callback receives the old and new version numbers and runs the commands that move the file to the new design. Test the upgrade path with an installed older build before you publish.

Practice Ideas

Build a notes app with a list screen and an add screen. Insert a note, reload the list, and delete a note with a swipe. Add a search box that uses a where clause with LIKE to filter titles. Close the app completely, reopen it, and confirm the notes remain.

Transactions and Batches

Saving fifty records one by one is slow and risky: a crash in the middle leaves half the data saved. A transaction groups many operations into a single unit that either completes fully or rolls back entirely. A batch sends many commands in one round trip. Use db.transaction or db.batch() for imports, bulk edits, and any change that must stay consistent.