SQLite Database for Flutter Beginners

If you’re building a Flutter app that needs offline data persistence, SQLite is one of the best lightweight solutions available. This guide is tailored for Flutter beginners who want to integrate SQLite step-by-step using sqflite, the most popular Flutter plugin for working with SQLite databases.

SQLite is a relational database that stores data in tables, rows, and columns. It’s embedded within your Flutter application and doesn’t require any server or setup, making it perfect for offline-first mobile apps.

Step-by-Step Implementation of SQLite in Flutter

Let’s build a simple CRUD (Create, Read, Update, Delete) app for managing notes.

Step 1: Add Dependencies

Open your pubspec.yaml and add the following:

dependencies:
  flutter:
    sdk: flutter
  sqflite: ^2.3.0
  path: ^1.8.3

Then run:

flutter pub get

Step 2: Create Model Class

// file: models/note.dart

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

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

  Map<String, dynamic> toMap() {
    return {
      'id': id,
      'title': title,
      'content': content,
    };
  }

  factory Note.fromMap(Map<String, dynamic> map) {
    return Note(
      id: map['id'],
      title: map['title'],
      content: map['content'],
    );
  }
}

Step 3: Create Database Helper Class

// file: db/database_helper.dart

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

class DatabaseHelper {
  static final DatabaseHelper instance = DatabaseHelper._init();
  static Database? _database;

  DatabaseHelper._init();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('notes.db');
    return _database!;
  }

  Future<Database> _initDB(String filePath) async {
    final dbPath = await getDatabasesPath();
    final path = join(dbPath, filePath);

    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future _createDB(Database db, int version) async {
    await db.execute('''
      CREATE TABLE notes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL
      )
    ''');
  }

  Future<int> insertNote(Note note) async {
    final db = await instance.database;
    return await db.insert('notes', note.toMap());
  }

  Future<List<Note>> getNotes() async {
    final db = await instance.database;
    final result = await db.query('notes');
    return result.map((map) => Note.fromMap(map)).toList();
  }

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

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

  Future close() async {
    final db = await instance.database;
    db.close();
  }
}

Step 4: Use in UI

// file: main.dart

import 'package:flutter/material.dart';
import 'db/database_helper.dart';
import 'models/note.dart';

void main() {
  runApp(const MyApp());
}

class MyApp extends StatelessWidget {
  const MyApp({super.key});
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'SQLite Demo',
      home: NotePage(),
    );
  }
}

class NotePage extends StatefulWidget {
  @override
  _NotePageState createState() => _NotePageState();
}

class _NotePageState extends State<NotePage> {
  final _titleController = TextEditingController();
  final _contentController = TextEditingController();
  List<Note> _notes = [];

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

  Future<void> _refreshNotes() async {
    final data = await DatabaseHelper.instance.getNotes();
    setState(() {
      _notes = data;
    });
  }

  Future<void> _addNote() async {
    final note = Note(title: _titleController.text, content: _contentController.text);
    await DatabaseHelper.instance.insertNote(note);
    _titleController.clear();
    _contentController.clear();
    _refreshNotes();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text("SQLite Note App")),
      body: Column(
        children: [
          Padding(
            padding: EdgeInsets.all(8),
            child: Column(
              children: [
                TextField(controller: _titleController, decoration: InputDecoration(labelText: "Title")),
                TextField(controller: _contentController, decoration: InputDecoration(labelText: "Content")),
                ElevatedButton(onPressed: _addNote, child: Text("Add Note")),
              ],
            ),
          ),
          Expanded(
            child: ListView.builder(
              itemCount: _notes.length,
              itemBuilder: (context, index) {
                final note = _notes[index];
                return ListTile(
                  title: Text(note.title),
                  subtitle: Text(note.content),
                );
              },
            ),
          )
        ],
      ),
    );
  }
}

External Reference

For more advanced database features in Flutter, you can refer to Flutter SQLite Guide on RayWenderlich.