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:
database.dart: This file will contain the code for all the CRUD functions.
home_screen.dart: This file will contain the UI of the Main screen containing all the notes.
view_note.dart: This file will be used to view the note, edit the note as well as create the note.
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:
id: To uniquely identify.
title: The note title
date: When it was created
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
