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.
Better still, download the Dart file, dbutils.dart, as there's no guarantee that this repo., dbutils.git, will last forever.
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.
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:
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.
You'd literally see that that's the case, if you take a peek in the class library:
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.
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, and we use the callback function, then(), to then notify the user if the save was successful or not.
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.
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.
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.
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.
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.
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.
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.
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.
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,??.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
