dbutils 0.3.2

  • Readme
  • Changelog
  • Example
  • Installing
  • 80

Flutter and a SQLite Database #

A Flutter Example

Build Status

I’ve taken Raja Jawahar’s sample app from his article, SQFlite Database in flutter, to help introduce a class library I’ve written to make it easier to work a SQLite database. Like Raja Jawahar, I’m using the SQFlite plugin for Flutter, sqflite. To follow along, you can get copies of the Dart files that make up the sample app at the gist, SQFlite Database in Flutter Example. You then copy the last three lines listed below and place them in your own pubspec.yaml file. You’re then on your way to working with SQFlite in your Flutter app. dbutilsgit

Better still, download the Dart file, dbutils.dart, as there's no guarantee that this repo., dbutils.git, will last forever.

apache sqlitedbutils

What's on the Table? #

In the sample app the table, Employee, extends the class library I have written called DBInterface. It implements the three required properties: Two getters called name and version and one function called onCreate(). The 'name' is the name of the database to contain the table you'll then define in the function, onCreate(). The 'version' is the version number of the database. Pretty straightforward so far. The function, onCreate(), of course, creates the table. Looking at the onCreate() function in the sample code below, it is required you're comfortable with SQL as it is SQL that creates the table. So, in the Dart file, Employee.dart, all that's required of you is to 'define' the database table in the onCreate() function ('Employee' in this example) and then possibly further define any functions to save and or query that data table. 01employee

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. For example, once you've entered an employee in this sample app, if you then 'list' all the employees entered in so far, you're not creating a new instance of the 'Employee' class with the following command used to list them:

It's Opened and Closed #

When you look at the sample app, you'll see where the Employee table is first instantiated. As many of my class library, I call an init() and dispose() functions respectively in the State object. In this case, the init() function opens the database, and the dispose() function closes the database. 03myhomepagestate You'd literally see that that's the case, if you take a peek in the class library: 04init

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 you see that you specify which table you're to assign the value ('Employee' in this case) and then to which field in that table. 05build

Make the Save #

In the sample app, you click in a button labelled, Login, to save the employee information. Looking at the code below, you see the save() function you saw defined in the Employee class. As you've 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

Let's See Your Saves #

The sample app has a 'hamburger button' in the upper right-hand corner of its screen. Clicking on that button will, as I've mentioned earlier, produce the list of employees you've entered so far. Below, is the code responsible to displaying that list. A query of the 'Employee' data table is performed, and when there's data, the first name and last name of each employee is listed out. 07saveemployees 08myemployeelist

Let's See the Interface: #

Again, you see the employee class in the Dart file, employee.dart, extends the class, DBInterface. This is the class library I've written to, in turn, work with the SQFlite plugin, sqflite. In review, typically this is how to use this class library: Create a class that extends the class, DBInterface, defines the table it represents and the name of the database (and database version number) that will contain it. There you have it! The rest of this article will take a walk through this class library.

On Five Occasions #

At most, you have five functions that you can override when using this class library. Each to handle five different events: onCreate, onConfigure, onOpen, onUpgrade, onDowngrade

The function, onCreate(), is an abstract function and has to be implemented. The others need only be overridden when you need them. Below, in the code, you can see in the comments when and why you would implement these functions. 09dbinterface

Initially Initialized #

You'll see in the code above, in the 'initializer' list, two final 'library-private' variables are assigned 'helper' classes. The first one deals with any errors that may occur, but it is the second one that we're most interested. Admittedly, it's that class called _DBInterface that does the heavy lifting and works directly with the SQFlite plugin, sqflite. It's found later on in the very same Dart file that makes up this class library. 10dberror

It's an Open and Closed Code #

The next section in the class library involves the opening and closing of the database. You can see I wasn't lying when I said the init() function and dispose() function do the opening and closing of the database. You'll also see the 'helper' class, _dbInt, actually makes the attempt to open the database. If it fails to open the database, the other 'helper' class comes in and records the resulting exception if any. 11open

All You CanGet #

Next, are all the getters offered by the class library. Most are concerned with determining the error, if any, that may have occurred at any point. The first two, however, are Map objects that allow you to access the fields names of each table found in the database, and the value of each field of each table found in the database. In fact, you already saw the Map object, values, being used in the sample app above where the inputted employee information is being assigned to the appropriate data table field. 12getfields

Another Save or Update #

Next bit of code is responsible for the saving of data to the database. The first function, saveRec(), calls the second function, updateRec(), and so let's review the second function first. The second function will either 'insert' a new record because the primary key field is null or will update an existing record because the primary key is a number (i.e. already assigned by the database). You'll see all that when you review the 'helper' later on. The primary field and all the other fields are supplied by the second parameter, fields, which is a Map object. The first function, saveRec(), is supplied this Map object from the 'helper' class: _dbInt._fldValues.

Note how the second function, updateRec(), returns an 'empty' Map object if an error occurs. With an error, the exception is recorded in the other 'helper' class, _dbError. If the update was successful, any previous exception that may have been recorded before is then cleared. 13saverec

Get the Rec or Delete it #

Next, is the section code used to retrieve a record or delete one. The first function, getRecord(), returns one record, if any, with a primary key field value of the one found in the integer variable, id. You can see it merely calls the second function, getRow(), but supplies the Map object, fields, from the "Helper" class, _DBInterface. This Map object lists all the fields of all the tables found in the current database. In the second function, you can see the name of the table is specified to retrieve all the fields values for that particular record, fields[table]. If there is an error, an empty Map object is returned in a List. The third function in this code below appears to be pretty straight forward. It's the 'delete' function and returns the number of rows (i.e. records) that are deleted. If you've a normalized data table, of course, that number should always be one as it searches by the primary key field.

Again, when dealing with this SQFlite plugin, sqflite, you'll be dealing with Future objects, and so, this class library deals with Future objects as well. All the functions are returning Future objects. 14getrecord

A Query Here #

The next two functions call on the plugin's query capabilities. The first function, rawQuery(), works with a 'raw' SQL statement while the next two functions, getTable() and query(), work with a specific list of parameters recognized as the options traditionally found in an SQL Select statement. The second function, getTable(), requires only the name of the table to perform the query. The list of field names are supplied by the Map object, _dbInt._fields. The remaining 'named parameters' make up the options, again, traditionally found in an SQL Select statement and so, by design, are optional. You can see the second function, getTable(), merely calls the third function, query() supplying the list of fields associated with the named table. The third function, query(), will further ensure a list of field names is provided by using the operator,??. 15rawquery

What's in a Name or Column #

The last two functions in the class, DBInterface, are used to directly query the 'system tables' found in the database and, in this case, list the table names found in the database, and the field names of a specific table respectively. 16tablenames

To Err is…Excepted #

The 'helper' class, _DBError, is next listed in the Dart file, DBInterface.dart. It's used to record any and all errors that might occur when dealing with databases. The function, set(), is found in multiple locations in the class library where errors may occur. Errors are recorded in the instance variables: _message and _e. Note, the set() function returns the String, _message, describing the recorded error. The function, clear(), is also called throughout the class library. With every successful database operation, those two instance variables are 'cleared' - so not to mistakenly indicate the last successful database operation did, in fact, fail.

Note: This implies, that with every database operation, you could check the getters, inError or noError, to help determine if the last database operation you preformed with this class library was successful or not.
17dberror The getters and functions that follow further help you determine if the last database operation caused an error or not, and, if so, the type of error that may have occurred. 18isdatabaseexception

The Helper #

The 'library-private' class, _DBInterface, indeed does the heavy lifting and actually performs all the database operations. You'll recognize the parameters in its constructor as those in the class library's constructor. Note, since the class itself is a 'library-private' class, I didn't bother to start its variables with underscores as well. 19dbinterface

There's an Open and There's a Close #

The first two functions are to open and to close the database. Of course, Future objects are involved here. In the first function, _open(), there is a Boolean value returned to indicate if the database was opened or not. There's another class library I've written called, Files, that retrieves the "app's directory" using the function, getApplicationDocumentsDirectory(). It's here where you see the function call, _tableFields(). You'll see later in the code how that function fills up the two Map objects, _fields and _fldValues, with the table's field names. 20open

Let's Get An Update #

Next is the code responsible for 'updating' an existing record or creating a new record. If there is an error, the instance variable, rowsUpdated, will contain a value of zero. Otherwise, likely a value of one since we're either creating one new record or updating one record using its primary key. And so, it's here where it'll do an 'insert' or an 'update' depending on if the primary key field is null or already assigned a number. 21updaterec

Get On Record #

Next, is the function used to retrieve a record from the table by looking up its primary key. Notice, this function opens the database if the instance variable, db, is null. It's not likely to be null if the class library was 'initialized' properly, but, if not, this class library will make the attempt to get the record by first trying to open the database. If that fails, an 'empty' List of an 'empty' Map is returned. 22getrec

Delete the Record #

The next function deletes a record. It returns the number of records deleted. Being that we're dealing with the record's primary key field, a successful delete will return an integer of one. It will return a value of zero, if the record is not found or, for some reason, an 'unopened' database fails to do so. 23delete

The Two Queries #

The next two function, rawQuery() and query() are called by the class library, DBInterface inside 'try..catch' statements. However, it is here, where the database is opened if not opened already and where the SQFlite plugin, db, actually performs the queries. The second function passes on the to the SQFlite plugin the many options traditionally available to an SQL Select statement. 24rawquery These next two functions are primarily used by the function, _tableList(). It is these two that directly query the 'system tables' found in the database and, in this case, list the table names found in the database, and the field names of a specific table respectively. 25tablenames

Make the List #

Finally, at the end of the class library is the function, _tableList(). It is called when the database is opened in the function, _open(). It is this function that fills the two Map objects, _fields and _fldValues, with the names of all the fields of all the tables contained in the database. 26list

The Medium Article:


DECODE Flutter #

Live Streaming every week. Everything to do about Flutter.


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;
  final MyHomePageState state = MyHomePageState();
  MyHomePageState createState() => state;

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

/// Reports [error] along with its [stackTrace]
Future<Null> _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: ^0.3.2

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 Feb 12, 2020, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.7.1
  • pana: 0.13.5
  • Flutter: 1.12.13+hotfix.7

Maintenance issues and suggestions

Homepage URL doesn't exist. (-20 points)

At the time of the analysis the homepage field https://medium.com/p/2ef1ef87e5af was unreachable.

The package description is too short. (-20 points)

Add more detail to the description field of pubspec.yaml. Use 60 to 180 characters to describe the package, what it does, and its target use case.


Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.1.0 <3.0.0
flutter 0.0.0
path_provider ^1.0.0 1.6.0
sqflite ^1.0.0 1.2.0 1.2.1-dev.2
Transitive dependencies
collection 1.14.11 1.14.12
meta 1.1.8
path 1.6.4
platform 2.2.1
sky_engine 0.0.99
synchronized 2.2.0
typed_data 1.1.6
vector_math 2.0.8
Dev dependencies