dbutils 1.0.0

  • Readme
  • Changelog
  • Example
  • Installing
  • 86

SQLite in Flutter #

Demonstrating the Dart package, dbutils.

Build Status

The Dart package, dbutils, was written to work with the SQLite plugin, sqflite, which was written by Alex Tekartik. The plugin knows how to ‘talk to’ a SQLite database, while the Dart package knows how to ‘talk to’ the plugin. The end result allows you to manipulate the SQLite database that much easier. Before continuing, I would suggest installing the Dart package now as it includes the very same example app demonstrated here in this article. Follow the three steps below, and you’re on your way to easily working with a SQLite database in your Flutter app. sqlitedbutils

Installing #

I don't always like the version number always suggested in the 'Installing' page. Instead, always go up to the 'Major' value in the semantic version number when installing my library packages. This means always entering a version number with then two trailing zeros, '.0.0'. This allows you to take in any 'minor' versions introducing new features, or in this case, any 'patch' versions that involves bugfixes. Semantic version numbers are always in this format: major.minor.patch.

  1. patch - I've made bugfixes
  2. minor - I've introduced new features
  3. major - I've essentially made a new app. It's broken backwards-compatibility and has a completely new user experience. You won't get this version until you increment the major number in the pubspec.yaml file.

And so, in this case, add this to your package's pubspec.yaml file instead:


For more information on this topic, read the article, The importance of semantic versioning.

What's on the Table? #

In the example app, we have the class, Employee, that extends the class library called, DBInterface. It’s found in the Dart package and implements the three required properties: To getters called name and version and one function called onCreate(). The ‘name’ is the name of the database to contain all the tables you would then define in the function, onCreate(). The ‘version’ is of course the version number of the database. Pretty straightforward so far. employee So, in the screenshot above, you see what makes up the Dart file, Employee.dart. Looking inside the onCreate() function, you’ll realize it’s required you be comfortable with SQL as it’s used to create and manipulate the data tables. Listed at the end of the Employee class, are functions used to save any changes to an Employee record be it editing an existing record or creating an brand new one. There’s the function to delete an Employee record, as well as, a function to retrieve the Employee records from the SQLite database. The last function listed provides an ‘empty’ Employee record used typically when creating a brand new Employee record.

Keep It Single #

Note, I’ve chosen to use a factory constructor for this Employee class. Doing so enforces the singleton pattern described in Item 1 of Joshua Bloch’s now famous 2001 book, Effective Java. Therefore, with each subsequent instantiation of this class, only ‘one instance’ of Employee is utilized. We don’t want more than one instance of the ‘Employee’ table running in this app.

In the screenshot below, you see the keyword, factory, allows for a return statement in the constructor. In this case, it returns the static property, _this, that will contain the one and only instance of the this class. factory

Once Is Enough #

For example, going to the ‘Employee details’ screen where you view an employee’s information, you have the option to delete that record. As it’s not a common operation, there’s no need to define a memory variable to take a instantiated reference of the Employee class. Merely call the constructor. We know, by time we visit this screen, the Employee class has already been instantiated and will return that one instance. delete

Saved By One #

Further on in that every same class there’s the function, _submit, called to save any changes made to the employee information. By the way, it’s also used to save brand new employee records. Note, it too calls the constructor, Employee(), to get that one instance to save that employee record. screen1

It's Opened and Closed #

When you look at the sample app, you’ll see where the Employee table is first instantiated in the State object’s initState() function. In most cases, that’s the appropriate place to do that. In fact, with most Dart packages and class libraries, the common practice is to initialize and dispose of them in a State object’s initState() and dispose() functions respectively. And so, in most cases, the dbutils Dart package’s has an init() function that opens the database, and a disposed() function that closes the database. Note, however, in the screenshot below, the init() function call is commented out. screen1 It’s commented out to demonstrate the ability of the library class to open the database whenever there’s a query to be performed. This is the case with the getEmployees() function highlighted above. And so, in the screenshot below, deep in the class library, the rawQuery() function that’s eventually called will open the database if not open already. query

All Or Nothing #

You can see in the getEmployees() function an SQL statement is executed to retrieve all the records that may be found in the table, Employee. It returns a List of Map objects—any and all the records found in the Employee table. The key of each Map object is, of course, a field name. Note, the class has a Map object, values, that takes in the last record in the table or an empty record if the table is also empty. getEmployee

Just For Show #

Of course, we could have just as well removed the comment on that line and have the init() called in the initState() function. In fact, it would be better form to do so as it adds a little consistency to the code. You can see in the screenshot of the library below, the database is opened in the init() function. It’s then always appropriate call the functions disposed() or close() in the State object’s dispose() function to ensure the database is closed properly when terminating your app. screen1

Map It Out #

The SQFlite plugin for Flutter, sqflite, deals with Map objects. This class library continues that approach and allows you to assign values to your table using Map objects. Below is a screenshot of the ‘Employee’ screen that displays an individual employee’s details. Note, a Map object, employee, is utilized to pass on the employee’s information to the TextFormField Widgets displayed and to take on any new values entered by the user. You can see where the Map object is used to also delete the Employee record it you wish. While, at the other end, the Map object is further used to save any new information to the data table. MyEmployee

Make the Save #

In the sample app, you click in a button labelled, Save, to save the employee information. Looking at the code below, you see the save() function defined in the Employee class is called to perform the operation. As you’ve likely guessed by now, most operations involving the database are asynchronous and hence we’re working with Future objects. In this case, the save() function returns a Future object of type Boolean, Future, and we use the callback function, then(), to then notify the user if the save was successful as well as return to the previous screen. then

A Record Save #

Inside the Employee class, the save() function, in turn, calls the saveRec() function. It passes the Map object containing the employee information as well as the name of the data table to be updated. save saveRec You can see the function, updateRec(), is called in turn to take the data deeper into the Dart package’s class library. We’re working with Future objects now. You can see below, we’re getting closer to the plugin itself calling an internal ‘helper’ class with its own updateRec() function. As you see, it’s enclosed in a try..catch statement. If there’s an error, another ‘helper’ class called _dbError will record the error and not crash the whole app. updateRec Finally, the internal class, _dbInt, has its updateRec() function perform the actual database operation. Notice, if the key field value is null, that implies this is a new Employee record and so the plugin, db, will call its insert() function to add this new record to the data table inside the database. Otherwise, it’s an existing record, and using the key field value to update the data table record. keyfield

Let's See Your Saves #

When this example app starts up it’ll produce a list of employees entered so far. Below, is the code responsible to displaying that list. A query of the ‘Employee’ data table is performed with the use of a FutureBuilder widget, and when there’s data, the first and last name of each employee is listed out. EmployeeList displaying

Add Anew #

Note the last little red arrow in the screenshot above. It reveals how an ‘empty’ Employee Map object is produced and passed to the ‘Employee’ screen so the user can enter a brand new employee.

To A Delete #

As a counter, let’s walk through the process involved when deleting an Employee record. Let’s return to the ‘Employee’ screen that displays an individual employee, and note there is the ‘trash’ icon on the AppBar. You press that icon; you’ll call the Employee class’ deleteRec() function. Note, it passes that Map object containing the employee’s info. In particular, the key field id for that Employee record. screen1 In the deleteRec() function, the delete() function is called passing in the name of the data table as well as the key field value specified by the field name, id. Note, if no Map object was passed to the function, the class turns to its internal Map object, values, in the hope to provide the id value. deleteRec Further, in the dbutils class library, again the try..catch statement prevents the app from crashing all together if there’s an exception. Again, an internal helper class comes into play calling its own delete() function to perform the actual deletion. delete The final function returns a Future object of type int (the number of records deleted) upon a successful operation. It too requires the key field value to find the appropriate Employee record. Note, if the database is not open yet, it’s opened before the deletion is performed. delete

The Medium Article #

Turn to the article, SQLite in Flutter, for more information: sqlite

Other Dart Packages #

packages Other Dart packages from the author can also be found at Pub.dev

1.0.0 #

Sep 19, 2019

  • Released to Production and so the major semantic version number must be used: 1.0.0

0.3.6 #

Sep 16, 2019

  • Supplied Github homepage
  • Longer package description.

0.3.5 #

Sep 16, 2019

  • updated README.md file
  • recs = List();

0.3.4 #

July 10, 2019

  • updated README.md file
  • lengthened pubspec.yaml description
  • updated example app code

0.3.3 #

July 08, 2019

  • updated README.md file
  • updated pubspec.yaml description
  • formatted source code

0.3.2 #

July 07, 2019

  • employee.dart Future
  • employeedetail.dart Employee().save(widget.employee).then((
  • employeelist.dart dispose()
  • DBInterface.dart Future

0.3.1 #

July 06, 2019

  • library sqllite_db; to library dbutils;
  • Update pubspec.yaml info.

0.3.0 #

July 05, 2019

  • keyField(String table) in DBInterface
  • Includes a working example

0.2.1 #

Apr 23, 2019

  • Allow for Hot Reload with temp?.close();

0.2.0 #

Dec. 20, ‎2018

  • dispose() renamed disposed()

0.1.3 #

Dec 10, 2018

  • Include DECODE Flutter in README.md

0.1.2 #

Nov 25, 2018

  • Update README.md

0.1.1 #

Nov 25, 2018

  • Update README.md

0.1.0 #

Nov 24, 2018

  • Public Release

0.0.1 #

Jun 25, 2018

  • Initial Release


/// Copyright (C) 2018 Andrious Solutions
/// This program is free software; you can redistribute it and/or
/// modify it under the terms of the GNU General Public License
/// as published by the Free Software Foundation; either version 3
/// of the License, or any later version.
/// You may obtain a copy of the License at
///  http://www.apache.org/licenses/LICENSE-2.0
/// Unless required by applicable law or agreed to in writing, software
/// distributed under the License is distributed on an "AS IS" BASIS,
/// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
/// See the License for the specific language governing permissions and
/// limitations under the License.
///          Created  24 Nov 2018
import 'dart:async' show Future;

import 'package:flutter/material.dart';

import 'employeelist.dart';

void main() {
  /// The default is to dump the error to the console.
  /// Instead, a custom function is called.
  FlutterError.onError = (FlutterErrorDetails details) async {
    await _reportError(details);

class MyApp extends StatelessWidget {
  const MyApp({this.key});
  final Key key;

  // This widget is the root of your application.
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'SQFLite DataBase Demo',
      theme: ThemeData(
        primarySwatch: Colors.blue,
      home: MyHomePage(key: key),
      debugShowCheckedModeBanner: false,

class MyHomePage extends StatefulWidget {
  MyHomePage({this.key}) : super();
  final Key key;
  MyHomePageState createState() => MyHomePageState();

class MyHomePageState extends State<MyHomePage> {
  Widget build(BuildContext context) => MyEmployeeList(key: widget.key);

/// Reports [error] along with its [stackTrace]
Future<void> _reportError(FlutterErrorDetails details) async {
  /// Turn to details.exception, details.stack

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:

  dbutils: ^1.0.0

2. Install it

You can install packages from the command line:

with Flutter:

$ flutter pub get

Alternatively, your editor might support flutter pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:

import 'package:dbutils/sqllitedb.dart';
Describes how popular the package is relative to other packages. [more]
Code health derived from static analysis. [more]
Reflects how tidy and up-to-date the package is. [more]
Weighted score of the above. [more]
Learn more about scoring.

We analyzed this package on Oct 16, 2019, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.5.1
  • pana: 0.12.21
  • Flutter: 1.9.1+hotfix.4


Detected platforms: Flutter

References Flutter, and has no conflicting libraries.


Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.3.0 <3.0.0
flutter 0.0.0
path_provider ^1.0.0 1.3.1
sqflite ^1.0.0 1.1.7+1
Transitive dependencies
collection 1.14.11 1.14.12
meta 1.1.7
path 1.6.4
platform 2.2.1
sky_engine 0.0.99
synchronized 2.1.0+1
typed_data 1.1.6
vector_math 2.0.8
Dev dependencies