SQLite Blog, Flutter: Final Part

In this final part of building SQLite Blog application in Flutter we will accomplish the basic principle of CRUD. As a consequence, we’ll create, retrieve, update and delete data in SQLite database.

We’ve already built a significant part of a SQLite Blog Application in Flutter. We might also see the progress of the initial phase in this section – SQLite Blog application in Flutter.

The previous section has discussed the application structure.

In the second part, we’d concentrated on database connection, data model classes.

In this final part we’ll take a look at the logic flow and see how we can convert this Blog application to a My Diary application.

We’ve also changed the layout in a significant way.

Therefore, before we jump in, let’s recapitulate a few things about SQLite database and Flutter.

Firstly, we’ll use a Flutter package or plugin, sqflite which is available in pub.dev.

Secondly, we also need to use Future API, async, await keywords, and then functions to make it successful.

Finally, we’ve discussed Future, await and async for absolute beginners in previous section, is Flutter single thread? 

Therefore, if you’re a beginner, you might take a look before we proceed towards the final section.

What packages we need for SQLite database in Flutter?

We’ll start with the pubspec.yaml file, where we must add all the dependencies.

dependencies:
  cupertino_icons: ^1.0.2
  flutter:
    sdk: flutter
  flutter_staggered_grid_view: ^0.4.1
  intl: ^0.17.0
  path: 
  provider: ^6.0.1
  sqflite: 

To give this SQLite Blog Application in Flutter a final touch, we need some packages or plugins.

The “sqflite”, “path”, “intl”, and “flutter_staggered_grid_view” packages will help us in many ways.

The “sqflite”, and “path” packages work at tandem. They help each other as we’ll find later. With the help of “path” package we define the path of the local database.

We need the packages “intl”, and “flutter_staggered_grid_view” for different purposes.

The package “intl” helps us to format the date in our Blog. And, the “flutter_staggered_grid_view” package helps us in building the layout while we display the blog or diary’s contents.

Does flutter need a backend?

Yes, Flutter needs a backend. Moreover, we can choose between two. Either we can go with a server, or we store our data locally with SQLite database.

In our case, we have decided to use SQLite database.

Consequently, we need a service or utility class that will create a database in local path first. Next, it will help us to use the SQL language to create a table with required fields.

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

class BlogDatabaseHandler {
  static final BlogDatabaseHandler instance = BlogDatabaseHandler._init();

  static Database? _database;

  BlogDatabaseHandler._init();

  Future<Database> get database async {
    if (_database != null) return _database!;

    _database = await _initDB('newblogs.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 {
    const idType = 'INTEGER PRIMARY KEY AUTOINCREMENT';
    const textType = 'TEXT NOT NULL';

    await db.execute('''
CREATE TABLE $tableOfBlogs ( 
  ${BlogFields.id} $idType, 
  ${BlogFields.title} $textType,
  ${BlogFields.description} $textType,
  ${BlogFields.time} $textType
  )
''');
  }

  Future<Blog> create(Blog blog) async {
    final db = await instance.database;

    final id = await db.insert(tableOfBlogs, blog.toJson());
    return blog.copy(id: id);
  }

  Future<Blog> readBlog(int id) async {
    final db = await instance.database;

    final maps = await db.query(
      tableOfBlogs,
      columns: BlogFields.values,
      where: '${BlogFields.id} = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      return Blog.fromJson(maps.first);
    } else {
      throw Exception('ID $id not found');
    }
  }

  Future<List<Blog>> readAllBlogs() async {
    final db = await instance.database;

    const orderBy = '${BlogFields.time} ASC';

    final result = await db.query(tableOfBlogs, orderBy: orderBy);

    return result.map((json) => Blog.fromJson(json)).toList();
  }

  Future<int> update(Blog blog) async {
    final db = await instance.database;

    return db.update(
      tableOfBlogs,
      blog.toJson(),
      where: '${BlogFields.id} = ?',
      whereArgs: [blog.id],
    );
  }

  Future<int> delete(int id) async {
    final db = await instance.database;

    return await db.delete(
      tableOfBlogs,
      where: '${BlogFields.id} = ?',
      whereArgs: [id],
    );
  }

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

    db.close();
  }
}

Firstly, we’ve mentioned a path so the database gets created locally and stores data locally.

Secondly, we’ve created the database and table with fields. As we see, we’ve kept it simple.

Thirdly, we’ve created methods that will read, update, delete items.

Finally, we’ve closed the database.

At the same time, we’ve changed the code of our home page slightly. As a result, we can now add or update the blog items either from AppBar, or through the floating action button.

Subsequently, to help the utility class we need a data model class.

const String tableOfBlogs = 'Blogs';

class BlogFields {
  static final List<String> values = [
    /// Adding all fields
    id, title, description, time
  ];

  static const String id = '_id';
  static const String title = 'title';
  static const String description = 'description';
  static const String time = 'time';
}

class Blog {
  final int? id;
  final String title;
  final String description;
  final DateTime createdTime;

  const Blog({
    this.id,
    required this.title,
    required this.description,
    required this.createdTime,
  });

  Blog copy({
    int? id,
    String? title,
    String? description,
    DateTime? createdTime,
  }) =>
      Blog(
        id: id ?? this.id,
        title: title ?? this.title,
        description: description ?? this.description,
        createdTime: createdTime ?? this.createdTime,
      );

  static Blog fromJson(Map<String, Object?> json) => Blog(
        id: json[BlogFields.id] as int?,
        title: json[BlogFields.title] as String,
        description: json[BlogFields.description] as String,
        createdTime: DateTime.parse(json[BlogFields.time] as String),
      );

  Map<String, Object?> toJson() => {
        BlogFields.id: id,
        BlogFields.title: title,
        BlogFields.description: description,
        BlogFields.time: createdTime.toIso8601String(),
      };
}

We’ve kept these files in our “model” sub-folder.

After that, we have built three pages and to keep them we have created a “view” sub-folder.

Which database is used for flutter?

As we’ve been discussing the topic we find, the Flutter team also recommends to use SQLite database. They say, “Flutter apps can make use of the SQLite databases via the sqflite plugin available on pub.”

Why?

The reason is simple. And it’s explained below.

If our app needs to persist and query large amounts of data on the local device, it’s always better to use a database instead of a local file or key-value store.

In general, databases provide faster inserts, updates, and queries compared to other local persistence solutions, and SQLite is the best choice.

Let’s proceed with our code.

First, we have a home page, that will handle the layout and backend at the same time.

If there is no entry it shows us a blank page and we start adding items.

If not, it shows like the following screenshot.

We’ve already added three entries. As a result it shows like the above screenshot.

Next, we’ll see the code snippet of the home page.

import 'package:flutter/material.dart';
import 'package:flutter_staggered_grid_view/flutter_staggered_grid_view.dart';
import '/model/blogs.dart';
import '/model/blog.dart';
import 'edit.dart';
import 'detail.dart';
import '/controller/blog_card.dart';

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

  @override
  _AllPagesState createState() => _AllPagesState();
}

class _AllPagesState extends State<AllPages> {
  late List<Blog> blogs;
  bool isLoading = false;

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

    refreshingAllBogs();
  }

  @override
  void dispose() {
    BlogDatabaseHandler.instance.close();

    super.dispose();
  }

  Future refreshingAllBogs() async {
    setState(() => isLoading = true);

    blogs = await BlogDatabaseHandler.instance.readAllBlogs();

    setState(() => isLoading = false);
  }

  @override
  Widget build(BuildContext context) => Scaffold(
        appBar: AppBar(
          title: const Text(
            'My Diary',
            style: TextStyle(fontSize: 24),
          ),
        ),
        body: Center(
          child: isLoading
              ? const CircularProgressIndicator()
              : blogs.isEmpty
                  ? const Text(
                      'No Entry in the beginning...',
                      style: TextStyle(color: Colors.white, fontSize: 60),
                    )
                  : buildingAllBlogs(),
        ),
        floatingActionButton: FloatingActionButton.extended(
          tooltip: 'Write Diary...',
          foregroundColor: Colors.white,
          backgroundColor: Colors.pink.shade900,
          onPressed: () async {
            await Navigator.of(context).push(
              MaterialPageRoute(builder: (context) => const EditPage()),
            );

            refreshingAllBogs();
          },
          label: const Text(
            'Write Diary...',
            style: TextStyle(
              fontSize: 30,
            ),
          ),
        ),
      );

  Widget buildingAllBlogs() => StaggeredGridView.countBuilder(
        padding: const EdgeInsets.all(8),
        itemCount: blogs.length,
        staggeredTileBuilder: (index) => const StaggeredTile.fit(2),
        crossAxisCount: 4,
        mainAxisSpacing: 4,
        crossAxisSpacing: 4,
        itemBuilder: (context, index) {
          final blog = blogs[index];

          return GestureDetector(
            onTap: () async {
              await Navigator.of(context).push(MaterialPageRoute(
                builder: (context) => DetailPage(blogId: blog.id!),
              ));

              refreshingAllBogs();
            },
            child: BlogCard(blog: blog, index: index),
          );
        },
      );
}

We have a list of all blog entries. If it’s empty, it will show an empty page with no contents. If not, it will take us to a controller.

import 'package:flutter/material.dart';
import 'package:intl/intl.dart';
import '../model/blog.dart';

/// these shades of colors will appear on
/// the display screen and it will appear
/// based on the index of the list
final shadeOfColors = [
  Colors.pink.shade100,
  Colors.purple.shade100,
  Colors.teal.shade200,
  Colors.orange.shade200,
  Colors.white10,
];

class BlogCard extends StatelessWidget {
  const BlogCard({
    Key? key,
    required this.blog,
    required this.index,
  }) : super(key: key);

  final Blog blog;
  final int index;

  @override
  Widget build(BuildContext context) {
    final color = shadeOfColors[index % shadeOfColors.length];
    final time = DateFormat.yMMMd().format(blog.createdTime);
    final minHeight = getMinHeight(index);

    return Card(
      color: color,
      child: Container(
        constraints: BoxConstraints(minHeight: minHeight),
        padding: const EdgeInsets.all(8),
        child: Column(
          mainAxisSize: MainAxisSize.min,
          crossAxisAlignment: CrossAxisAlignment.start,
          children: [
            Text(
              time,
              style: TextStyle(color: Colors.grey.shade700),
            ),
            const SizedBox(height: 4),
            Text(
              blog.title,
              style: const TextStyle(
                color: Colors.black,
                fontSize: 20,
                fontWeight: FontWeight.bold,
              ),
            ),
            const SizedBox(
              height: 5,
            ),
            Text(
              blog.description,
              style: const TextStyle(
                color: Colors.blueAccent,
                fontSize: 16,
                fontWeight: FontWeight.w300,
              ),
            ),
          ],
        ),
      ),
    );
  }

  double getMinHeight(int index) {
    switch (index % 4) {
      case 0:
        return 100;
      case 1:
        return 150;
      case 2:
        return 150;
      case 3:
        return 100;
      default:
        return 100;
    }
  }
}

The each Card widget has been defined here to show all the items.

Moreover, we can also start writing the content also. In that case, the home page will take us to the Edit page.

import 'package:flutter/material.dart';
import '../model/blogs.dart';
import '../model/blog.dart';
import '../controller/blog_form.dart';

class EditPage extends StatefulWidget {
  final Blog? blog;

  const EditPage({
    Key? key,
    this.blog,
  }) : super(key: key);
  @override
  _EditPageState createState() => _EditPageState();
}

class _EditPageState extends State<EditPage> {
  final _formKey = GlobalKey<FormState>();
  late bool isImportant;
  late int number;
  late String title;
  late String description;

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

    title = widget.blog?.title ?? '';
    description = widget.blog?.description ?? '';
  }

  @override
  Widget build(BuildContext context) => Scaffold(
        appBar: AppBar(
          actions: [buildButton()],
        ),
        body: Form(
          key: _formKey,
          child: BlogForm(
            title: title,
            description: description,
            onChangedTitle: (title) => setState(() => this.title = title),
            onChangedDescription: (description) =>
                setState(() => this.description = description),
          ),
        ),
      );

  Widget buildButton() {
    final isFormValid = title.isNotEmpty && description.isNotEmpty;

    return Padding(
      padding: const EdgeInsets.symmetric(vertical: 8, horizontal: 12),
      child: ElevatedButton(
        style: ElevatedButton.styleFrom(
          onPrimary: Colors.white,
          onSurface: Colors.pink.shade900,
          shadowColor: Colors.grey.shade600,
          primary: isFormValid ? Colors.pink.shade900 : Colors.pink.shade900,
        ),
        onPressed: addOrUpdateBlog,
        child: const Text(
          'Add or Update',
          style: TextStyle(
            fontSize: 20,
          ),
        ),
      ),
    );
  }

  void addOrUpdateBlog() async {
    final isValid = _formKey.currentState!.validate();

    if (isValid) {
      final isUpdating = widget.blog != null;

      if (isUpdating) {
        await updateBlog();
      } else {
        await addBlog();
      }

      Navigator.of(context).pop();
    }
  }

  Future updateBlog() async {
    final blog = widget.blog!.copy(
      title: title,
      description: description,
    );

    await BlogDatabaseHandler.instance.update(blog);
  }

  Future addBlog() async {
    final blog = Blog(
      title: title,
      description: description,
      createdTime: DateTime.now(),
    );

    await BlogDatabaseHandler.instance.create(blog);
  }
}

Here we can start writing the fresh content just like the following screenshot.

After the writing is over, we can press the “Add or Update” button and insert the data to SQLite database.

How Do we insert data to SQLite Database?

Well, we need a text controller or a Form that will take care of it? Right?

The user must be able to write her posts in this application. Whether, she is writing a blog post or adding her diary entry, that does not matter.

To make it happen, we need another blog form controller.

import 'package:flutter/material.dart';

class BlogForm extends StatelessWidget {
  final String? title;
  final String? description;

  final ValueChanged<String> onChangedTitle;
  final ValueChanged<String> onChangedDescription;

  const BlogForm({
    Key? key,
    this.title = '',
    this.description = '',
    required this.onChangedTitle,
    required this.onChangedDescription,
  }) : super(key: key);

  @override
  Widget build(BuildContext context) => SingleChildScrollView(
        child: Padding(
          padding: const EdgeInsets.all(16),
          child: Column(
            mainAxisSize: MainAxisSize.min,
            children: [
              buildTitle(),
              const SizedBox(height: 8),
              buildDescription(),
              const SizedBox(height: 16),
            ],
          ),
        ),
      );

  Widget buildTitle() => TextFormField(
        maxLines: 1,
        initialValue: title,
        style: const TextStyle(
          color: Colors.white70,
          fontWeight: FontWeight.bold,
          fontSize: 24,
        ),
        decoration: const InputDecoration(
          border: InputBorder.none,
          hintText: 'Here Title...',
          hintStyle: TextStyle(color: Colors.white70),
        ),
        validator: (title) =>
            title != null && title.isEmpty ? 'Title cannot be empty' : null,
        onChanged: onChangedTitle,
      );

  Widget buildDescription() => TextFormField(
        maxLines: 5,
        initialValue: description,
        style: const TextStyle(color: Colors.white60, fontSize: 18),
        decoration: const InputDecoration(
          border: InputBorder.none,
          hintText: 'Here description...',
          hintStyle: TextStyle(color: Colors.white60),
        ),
        validator: (title) => title != null && title.isEmpty
            ? 'Description cannot be empty'
            : null,
        onChanged: onChangedDescription,
      );
}

We need two text form field to accept the data from user.

Once we are over, the added items show on the screen.

If we want to edit any of the items, we can just tap the item and gesture detector will take us to detail page where we find the edit and delete buttons.

However, in this time, we can edit them. In addition, we can also delete them as well.

This page has been taken care of by another page, that basically assists our edit page to maintain the state and allow us to edit or delete.

import 'package:flutter/material.dart';
import 'package:intl/intl.dart';
import '../model/blogs.dart';
import '../model/blog.dart';
import 'edit.dart';

class DetailPage extends StatefulWidget {
  final int blogId;

  const DetailPage({
    Key? key,
    required this.blogId,
  }) : super(key: key);

  @override
  _DetailPageState createState() => _DetailPageState();
}

class _DetailPageState extends State<DetailPage> {
  late Blog blog;
  bool isLoading = false;

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

    refreshBlog();
  }

  Future refreshBlog() async {
    setState(() => isLoading = true);

    blog = await BlogDatabaseHandler.instance.readBlog(widget.blogId);

    setState(() => isLoading = false);
  }

  @override
  Widget build(BuildContext context) => Scaffold(
        appBar: AppBar(
          actions: [
            const Text(' ... '),
            editButton(),
            const Text(' ... '),
            deleteButton(),
          ],
        ),
        body: isLoading
            ? const Center(child: CircularProgressIndicator())
            : Padding(
                padding: const EdgeInsets.all(12),
                child: ListView(
                  padding: const EdgeInsets.symmetric(vertical: 8),
                  children: [
                    const SizedBox(height: 10),
                    Text(
                      blog.title,
                      style: const TextStyle(
                        color: Colors.white,
                        fontSize: 22,
                        fontWeight: FontWeight.bold,
                      ),
                    ),
                    const SizedBox(height: 10),
                    Text(
                      DateFormat.yMMMd().format(blog.createdTime),
                      style: const TextStyle(color: Colors.white38),
                    ),
                    const SizedBox(height: 10),
                    Text(
                      blog.description,
                      style:
                          const TextStyle(color: Colors.white70, fontSize: 18),
                    )
                  ],
                ),
              ),
      );

  Widget editButton() => ElevatedButton(
        style: ElevatedButton.styleFrom(
          onPrimary: Colors.white,
          onSurface: Colors.pink.shade900,
          shadowColor: Colors.grey.shade600,
          primary: Colors.pink.shade900,
        ),
        onPressed: () async {
          if (isLoading) return;

          await Navigator.of(context).push(
            MaterialPageRoute(
              builder: (context) => EditPage(blog: blog),
            ),
          );

          refreshBlog();
        },
        child: const Text(
          'Edit',
          style: TextStyle(
            fontSize: 20,
          ),
        ),
      );

  Widget deleteButton() => ElevatedButton(
        style: ElevatedButton.styleFrom(
          onPrimary: Colors.white,
          onSurface: Colors.pink.shade900,
          shadowColor: Colors.grey.shade600,
          primary: Colors.pink.shade900,
        ),
        onPressed: () async {
          await BlogDatabaseHandler.instance.delete(widget.blogId);

          Navigator.of(context).pop();
        },
        child: const Text(
          'Delete',
          style: TextStyle(
            fontSize: 20,
          ),
        ),
      );
}

As we can see in the above code, we have edit and delete buttons both in our AppBar.

Now. we’ve accomplished the task of building a Blog SQLite database application in Flutter. Of course, we can use the same application as a My Diary.

For the full code snippet for this SQLite database application in Flutter, please visit the respective GitHub repository.

What Next?

Books at Leanpub

Books in Apress

My books at Amazon

Courses at Educative

GitHub repository

Technical blog

Twitter