SQFlite in Flutter - SQL

Database for Flutter appto our newsletter

Subscribe
- Oin the loop!
Stay ineGetDatabase
new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
Manav Sarkar
Continue with Google
Nov 24, 2023 · 9 min read
More options
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:
COPY

dependencies:
sqflite:

Then use the plugin as follows:

Subscribe
COPY
to our newsletter
Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
import 'package:sqflite/sqflite.dart';
to your inbox.
Creating an Application with SQLite
database Continue with Google
In this tutorial, we will be creating a Notes
application that will have the following More options
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.
COPY

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.Subscribe
When we to our newsletter
save Stay
a note or get
in the loop!aGet
note,newthisarticles
is done
frominAll About Flutter | Flutter and Dart delivered straight
String format, so to format them in Dart,to your inbox.
this file is required.
Your final folder structure will look as Continue with Google
follows:
More options
 Subscribe to our newsletter
Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
Next, add the plugin of SQFlite as
mentioned above. Continue with Google
Step 2 Create the Note Model
As stated before, the model file is a More options
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
COPY

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() {

Subscribe to our newsletter

return <String, dynamic>{

Stay'id': id, Get new articles from All About Flutter | Flutter and Dart delivered straight
in the loop!
'title': title, to your inbox.
'dateCreated': dateCreated.millisecondsSinceEpoch,
'content': content, Continue with Google
};
} More options

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
COPY

import 'package:sqflite/sqflite.dart';

class NotesDatabase {

Subscribe
static final NotesDatabase to our=newsletter
instance NotesDatabase._init();

Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
static Database? _database;
NotesDatabase._init(); to your inbox.
Future<Database> get database async {
Continue with Google
if (_database != null) return _database!;
_database = await _initDB('notes.db');
return _database!; More options
}
}

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
COPY
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
COPY

Future _createDB(Database db, int version) async {

final idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
final textType = 'TEXTSubscribe to our newsletter
NOT NULL';

Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
final intType = 'INTEGER NOT NULL';
to your inbox.
await db.execute('''
CREATE TABLE notesTable ( Continue with Google
id $idType,
title $textType, More options
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
COPY
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
COPY

Future<Note> read(int id) async {

final db = await instance.database;
final maps = await db.query(
'notesTable',
where: 'id = ?', Subscribe to our newsletter
Stay Get new articles from All About Flutter | Flutter and Dart delivered straight
in the loop![id],
whereArgs:
); to your inbox.
if (maps.isNotEmpty) {
return Note.fromMap(maps.first); Continue with Google
} else {
throw Exception('ID $id not found'); More options
}
}
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
COPY

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', Subscribe to our newsletter
Stay in the'id
where: new articles from All About Flutter | Flutter and Dart delivered straight
loop!=Get?',
whereArgs: [id], to your inbox.
);
} Continue with Google

We have completed creating the More options

database. Here is the full code.
database.dart
COPY

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, Subscribe to our newsletter
Stay intitle Get new articles from All About Flutter | Flutter and Dart delivered straight
the loop!$textType,
dateCreated $intType, to your inbox.
content $textType
) Continue with Google
''');
} More options

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 {

Subscribe to our newsletter

final db = await instance.database;

Stay loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
in thedb.update(
return
'notesTable', to your inbox.
note.toMap(),
where: 'id = ?', Continue with Google
whereArgs: [note.id],
); More options
}

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
COPY

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

import 'note_model.dart';

class HomeScreen extendsSubscribe to our newsletter

StatefulWidget {

Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
const HomeScreen({Key? key}) : super(key: key);
to your inbox.
@override
Continue with Google
State<HomeScreen> createState() => _HomeScreenState();
}
More options
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,

Subscribe
isEditing: true, to our newsletter

Stay in the loop!);Get new articles from All About Flutter | Flutter and Dart delivered straight
}), to your inbox.
).then((value) => getNotes());
}, Continue with Google
child: const Icon(Icons.add),
), More options
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());
},
);
},
), Subscribe to our newsletter
); in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
Stay
} to your inbox.
}
Continue with Google
In the view_note.dart, we first check that
if it is editing mode or note. If yes, then More options
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
COPY

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,
); Subscribe to our newsletter
Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
getNote(id) async {
to your inbox.
final note = await NotesDatabase.instance.read(id);
setState(() {
this.note = note; Continue with Google
titleController.text = note.title;
More options
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;
});

Subscribe to our newsletter

super.didChangeDependencies();
} Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
@override
Widget build(BuildContext context) { Continue with Google
return Scaffold(
appBar: AppBar( More options
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 Subscribe to our newsletter
note = Note(

Stay in the loop! Get newid:articles from All About Flutter | Flutter and Dart delivered straight
this.note.id,
to your inbox.
title: titleController.text,
content: contentController.text,
Continue with Google
dateCreated: DateTime.now(),
);
More options
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
COPY

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

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

Subscribe to our
class MyApp extends StatelessWidget { newsletter

Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
const MyApp({Key? key}) : super(key: key);
to your inbox.
@override
Widget build(BuildContext context) { Continue with Google
return MaterialApp(
title: 'AAF SQFlite', More options
theme: ThemeData(
primarySwatch: Colors.blue,
),
home: const HomeScreen(),
);
}
}

Output
 Subscribe to our newsletter
Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
Continue with Google
Subscribe to our newsletter
More options
Read articles from All About Flutter |
Flutter and Dart directly inside your inbox.
Subscribe to the newsletter, and don't
miss out.
Enter your email address SUBSCRIBE

Did you nd this

article valuable?
Support All About
Flutter | Flutter and
 Dart by becoming a
sponsor. Any amount is
appreciated!
Sponsor
See recent sponsors
| Learn more about
Hashnode Sponsors

Flutter Flutter Widgets Databases

SQL Tutorial

Written by
Manav Sarkar
Follow
Subscribe to our newsletter
Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
Published on
All About Flutter | Flutter andContinue with Google
Dart
More options
AllAboutFlutter provides the best tutorials for
Flutter and Dart. Easy and up-to-date
tutorials for Flutter and Dart. It is a one-stop
website for the Flutter framework.
Follow

MORE ARTICLES
Manav Sarkar
Language Translation using
MLKit in Flutter - Part 2
Language Translation is a difficult task
and training our model for it makes it
even Harder. But Goo…

Manav Sarkar

Language Translation using

MLKit in Flutter - PartSubscribe 1 to our newsletter
Stay in the
Language loop! Getisnew
Translation from All About Flutter | Flutter and Dart delivered straight
articlestask
a difficult
and training our model for it makes it to your inbox.
even Harder. But Goo…
Continue with Google
Manav Sarkar More options

Implementing Biometric
Authentication in Flutter
Biometric authentication in the Flutter
app allows developers to allow only
authenticated users to a…
 ©2024 All About Flutter | Flutter and Dart
Archive · Privacy policy · Terms

Publish with Hashnode

Powered by Hashnode - Home for tech writers and

readers

Subscribe to our newsletter

Stay in the loop! Get new articles from All About Flutter | Flutter and Dart delivered straight
to your inbox.
Continue with Google
More options