top of page

SQFlite in Flutter - SQL Database for Flutter app - Offline Database

Updated: Dec 14, 2022

SQL stands for structured query language which is a domain-specific language used in programming and designed for managing data held in a relational database management system. SQL is used as an offline database in Mobile applications and is popularly used by developers to create a large class of applications.


To use SQLite(SQL database for Android), the SQFLite plugin is used. SQFLite supports iOS, Android and macOS. Some of the features are as follows:

  • Support transactions and batches

  • Automatic version management during open

  • Helpers for insert/query/update/delete queries

  • DB operation is executed in a background thread on iOS and Android.

Installation

To install the SQFLite plugin, add the plugin to the pubspec.yaml file as follows:

dependencies:
    sqflite:

Then use the plugin as follows:



import 'package:sqflite/sqflite.dart';

Creating an Application with SQLite database

In this tutorial, we will be creating a Notes application that will have the following common features of a database

  • Create

  • Read

  • Update

  • Delete

which is in short a CRUD application using SQFLite. We will be learning to create a database and perform the above operations. So let's start.


Step 1: Create and Setup the project

Open the Command Prompt/Terminal in your desired folder/directory. (You can also follow your way of creating the project). Enter the following command to create a new project.

flutter create aaf_sqflite

Under the lib directory, create a file three files as follows:

  1. database.dart: This file will contain the code for all the CRUD functions.

  2. home_screen.dart: This file will contain the UI of the Main screen containing all the notes.

  3. view_note.dart: This file will be used to view the note, edit the note as well as create the note.

  4. note_model.dart: We will need a model file or the structure of the note. When we save a note or get a note, this is done in String format, so to format them in Dart, this file is required.

Your final folder structure will look as follows:


Next, add the plugin of SQFlite as mentioned above.


Step 2: Create the Note Model

As stated before, the model file is a structure compared similarly to Schema in a database. It contains the data type of each attribute and how conversions from JSON to Dart and vice-versa will take place.

A single Note will require the following attributes:

  1. id: To uniquely identify.

  2. title: The note title

  3. date: When it was created

  4. content: The content of the note.

So the model of the file will be as follows:

note_model.dart

import 'dart:convert';

class Note {
  final int id;
  final String title;
  final DateTime dateCreated;
  final String content;
  Note({
    required this.id,
    required this.title,
    required this.dateCreated,
    required this.content,
  });

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

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

  String toJson() => json.encode(toMap());

  factory Note.fromJson(String source) =>
      Note.fromMap(json.decode(source) as Map<String, dynamic>);
}

Here the toMap and fromJson are used for transactions with SQL database.



Step 3: Create the database

Next, we will create the database and add the functionalities to it. Before that, we need to create the database class and add some boilerplate code to it.


database.dart

import 'package:sqflite/sqflite.dart';

class NotesDatabase {
  static final NotesDatabase instance = NotesDatabase._init();
  static Database? _database;
  NotesDatabase._init();
  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('notes.db');
    return _database!;
  }
}

The _initDB function is used to create a new database if not exist or else return the existing database. The function also opens the database with the help of the function openDatabase which is responsible for creating the database.

database.dart

Future<Database> _initDB(String filePath) async {
  final dbPath = await getDatabasesPath();
  final path = dbPath + filePath;
  return await openDatabase(path, version: 1, onCreate: _createDB);
}

Here as we can see the onCreate parameter takes the _createDB function. This function is used to create the notes table and which is done with absolute SQL commands. The function takes the version and the database as parameters.

database.dart

Future _createDB(Database db, int version) async {
  final idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
  final textType = 'TEXT NOT NULL';
  final intType = 'INTEGER NOT NULL';

  await db.execute('''
CREATE TABLE notesTable (
  id $idType,
  title $textType,
  dateCreated $textType,
  content $textType
)
''');
}

To create a note, we will use db.insert() function. Here we need to pass table name and notes as Map(Map is a data structure).

database.dart

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


To read a note, we need to pass the id to our read function and use the db.query() method. We also need to create readAll() method, to get all the notes.

database.dart

Future<Note> read(int id) async {
  final db = await instance.database;
  final maps = await db.query(
    'notesTable',
    where: 'id = ?',
    whereArgs: [id],
  );
  if (maps.isNotEmpty) {
    return Note.fromMap(maps.first);
  } else {
    throw Exception('ID $id not found');
  }
}
Future<List<Note>> readAll() async {
  final db = await instance.database;
  const orderBy = 'dateCreated DESC';
  final result = await db.query('notesTable', orderBy: orderBy);
  return result.map((json) => Note.fromMap(json)).toList();
}

Similarly we can create the update and delete methods. The update method will be used to update a note and the delete method will be used to delete a note. The methods are created as follows:

database.dart

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

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

We have completed creating the database. Here is the full code.

database.dart

import 'package:aaf_sqflite/note_model.dart';
import 'package:sqflite/sqflite.dart';

class NotesDatabase {
  static final NotesDatabase instance = NotesDatabase._init();
  static Database? _database;
  NotesDatabase._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 = dbPath + filePath;
    return await openDatabase(path, version: 1, onCreate: _createDB);
  }

  Future _createDB(Database db, int version) async {
    final idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    final textType = 'TEXT NOT NULL';
    final intType = 'INTEGER NOT NULL';

    await db.execute('''
      CREATE TABLE notesTable (
        id $idType,
        title $textType,
        dateCreated $intType,
        content $textType
      )
      ''');
  }

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

  Future<Note> read(int id) async {
    final db = await instance.database;
    final maps = await db.query(
      'notesTable',
      where: 'id = ?',
      whereArgs: [id],
    );
    if (maps.isNotEmpty) {
      return Note.fromMap(maps.first);
    } else {
      throw Exception('ID $id not found');
    }
  }

  Future<List<Note>> readAll() async {
    final db = await instance.database;
    const orderBy = 'dateCreated DESC';
    final result = await db.query('notesTable', orderBy: orderBy);
    return result.map((json) => Note.fromMap(json)).toList();
  }

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

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



Step 4: Create the UI.

We have already created the home_screen.dart file where we are going to place the code for the UI. On init, we are going to call the function readAll() to get all the Notes and display there title with date. On clicking, the view_notes screen will open where we can edit it. So here is the code for our Home screen.

home_screen.dart

import 'package:aaf_sqflite/database.dart';
import 'package:aaf_sqflite/view_note.dart';
import 'package:flutter/material.dart';

import 'note_model.dart';

class HomeScreen extends StatefulWidget {
  const HomeScreen({Key? key}) : super(key: key);

  @override
  State<HomeScreen> createState() => _HomeScreenState();
}

class _HomeScreenState extends State<HomeScreen> {
  final notes = <Note>[];
  getNotes() async {
    final notes = await NotesDatabase.instance.readAll();
    setState(() {
      this.notes.clear();
      this.notes.addAll(notes);
    });
  }

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

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: const Text('Notes App AAF'),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () async {
          Navigator.push(
            context,
            MaterialPageRoute(builder: (context) {
              return const ViewNote(
                id: 0,
                isEditing: true,
              );
            }),
          ).then((value) => getNotes());
        },
        child: const Icon(Icons.add),
      ),
      body: ListView.builder(
        itemCount: notes.length,
        itemBuilder: (context, index) {
          final note = notes[index];
          return ListTile(
            title: Text(note.title),
            subtitle: Text(note.dateCreated.toString()),
            trailing: IconButton(
              icon: const Icon(Icons.delete),
              onPressed: () async {
                await NotesDatabase.instance.delete(note.id);
                getNotes();
              },
            ),
            onTap: () {
              Navigator.push(
                context,
                MaterialPageRoute(
                  builder: (context) {
                    return ViewNote(
                      id: note.id,
                      isEditing: false,
                    );
                  },
                ),
              ).then((value) => getNotes());
            },
          );
        },
      ),
    );
  }
}



In the view_note.dart, we first check that if it is editing mode or note. If yes, then first we need to create a note and then display it as editing mode. Else we will fetch the note from database directly.

view_note.dart

import 'package:aaf_sqflite/note_model.dart';
import 'package:flutter/material.dart';

import 'database.dart';

class ViewNote extends StatefulWidget {
  final int id;
  final bool isEditing;
  const ViewNote({Key? key, required this.id, required this.isEditing})
      : super(key: key);

  @override
  State<ViewNote> createState() => _ViewNoteState();
}

class _ViewNoteState extends State<ViewNote> {
  bool isEditing = false;
  TextEditingController titleController = TextEditingController();
  TextEditingController contentController = TextEditingController();
  Note note = Note(
    title: '',
    dateCreated: DateTime.now(),
    content: '',
    id: 0,
  );
  getNote(id) async {
    final note = await NotesDatabase.instance.read(id);
    setState(() {
      this.note = note;
      titleController.text = note.title;
      contentController.text = note.content;
    });
  }

  createNote() async {
    final note = Note(
      title: titleController.text,
      dateCreated: DateTime.now(),
      content: contentController.text,
      id: 0,
    );
    final id = await NotesDatabase.instance.create(note);
    getNote(id);
  }

  @override
  void initState() {
    if (widget.isEditing) {
      createNote();
    } else {
      getNote(widget.id);
    }
    super.initState();
  }

  @override
  void didChangeDependencies() {
    setState(() {
      isEditing = widget.isEditing;
    });
    super.didChangeDependencies();
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(note.title),
      ),
      body: Padding(
        padding: const EdgeInsets.all(8.0),
        child: Column(
          children: [
            TextField(
              controller: titleController,
              decoration: const InputDecoration(
                hintText: 'Title',
              ),
              enabled: isEditing,
            ),
            const SizedBox(
              height: 8,
            ),
            TextField(
              controller: contentController,
              decoration: const InputDecoration(
                hintText: 'Content',
              ),
              enabled: isEditing,
            ),
            const SizedBox(
              height: 8,
            ),
            ElevatedButton(
              onPressed: () {
                if (isEditing) {
                  final note = Note(
                    id: this.note.id,
                    title: titleController.text,
                    content: contentController.text,
                    dateCreated: DateTime.now(),
                  );
                  NotesDatabase.instance.update(note);
                  setState(() {
                    this.note = note;
                  });
                }
                setState(() {
                  isEditing = !isEditing;
                });
              },
              child: Text(isEditing ? 'Save' : 'Edit'),
            )
          ],
        ),
      ),
    );
  }
}

Finally our app is ready, it's time to run it and check it. Before launching the app, add the home screen to main.dart file.

main.dart

import 'package:aaf_sqflite/home_screen.dart';
import 'package:flutter/material.dart';

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

class MyApp extends StatelessWidget {
  const MyApp({Key? key}) : super(key: key);

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'AAF SQFlite',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      ),
      home: const HomeScreen(),
    );
  }
}

Output



Congratulations, you created your own Notes app.

Related Posts

See All

In this tutorial, we will learn to create Alert Dialogs in Flutter. Alert Dialogs are used to acknowledge the user's current situation that

bottom of page