SQLite in Flutter

Jul 7 · 16 min read

Demonstrating the Dart package, dbutils.

This article introduces the Dart package, dbutils. It 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.

dbutils on pub.dev

Screenshots! Not Gists!

As always, I prefer using screenshots over gists to show code in my articles. I find them easier to work with, and easier to read. However, you can click/tap on them to see the code as a gist or in Github. Ironically, it’s better to read this article about mobile development on your computer and not on your phone. Besides, we program mostly on our computers; not on our phones. For now.

Let’s begin.

Other Stories by Greg Perry

What’s on the Table?

In the example app, we have the class, Employee, that extends the class library called, DBInterface. It itself is found in the libary file, Employee.dart, and 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 all the tables you would then define in the function, onCreate(). The ‘version’ of course is the version number of the database. Pretty straightforward so far.

Employee.dart *

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.

Employee class *

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.

MyEmployeeState class *

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.

MyEmployeeState class *

It’s An Open and Closed Case

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.

EmployeeListPageState class *

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.

_DBInterface class *

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.

Employee class *

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 to call the functions disposed() or close() in the State object’s dispose() function to ensure the database is closed properly when terminating your app.

DBInterface class *

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 if you wish. While, at the other end, the Map object is further used to save any new information to the data table.

MyEmployee class *

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<bool>, 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.

MyEmployee class *

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.

Employee class *
DBInterface class *

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.

DBInterface class *

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.

_DBInterface class *

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.

MyEmployeeList class *

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.

MyEmployee class *

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.

Employee class *

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.

DBInterface class *

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.

_DBInterface class *

TL;DR

Let’s See the Interface

Let’s use the rest of this article to walk through this class library, DBInterface.dart, from top to bottom. It’ll show you where and how Alex Tekartik’s plugin is utilized to work with SQLite databases and how to take advantage of that fact in your own Flutter apps.

On Five Occasions

At most, you have five functions that you can override when using this class library. Each handles five different events: onCreate, onConfigure, onOpen, onUpgrade, and 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 read in the comments when and why you would implement them.

DBInterface class *

Initially Initialized

The screenshot of the library’s constructor shows it has an ‘initializer list’ where a final ‘library-private’ variable is assigned the class that deals with any errors that may occur. It’s the ‘helper’ class, _DBError, and is also defined in this library file. Inside the constructor itself, we see another library-private variable being assigned another ‘helper’ class called, _dbInt. It’s this class that takes in those five routines mentioned above, and it’s this class that does most the heavy lifting around here working directly with the SQFlite plugin in its designated operations.

DBInterface class *

It’s an Open and Closed Code

After the five routines, we see the functions involved in the opening and closing of the database next. Again, we see the init() function and disposed() function commonly used to ‘interface’ with a State object. 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 into play to record the resulting exception.

DBInterface class *

All You Can Get

Next, are all the getters offered by the class library. Most are concerned with determining the type of common errors that may have occurred when dealing with databases. However, the first getter allows you to access the fields names of each table found in the database. Next to that is the function that returns the name of each table’s key field. An essential feature when updating records in relational databases like SQLite.

What follows that is a getter to supply an ‘empty’ record when needed. After that, you see you have access to the plugin itself in the form of the Database reference, db. Finally, any and all the information required to identify the many errors that may occur with databases supplied by the all those getters.

DBInterface class *

Another Save or Update

The next bit of code in the class library is responsible for the saving of data to the database. The first function you’ve seen before. It’s the saveRec() function. Again, in turn, it calls the function, updateRec(), which in turn calls the updateRec() function in the helper class, _dbInt.

After that is the function, saveMap(). It’s designed to take in a Map object records from multiple tables, but to save only those for the table specified. A specialty function. However, that’s to be expected for a class library such as this. It’s to make life a little easier when working with this SQLite plugin.

There is the runTxn() function next. Admittedly, it’s not been fully tested, but I wrote it to manage transnational capabilities of the plugin. Give it a shot! Let me know how it works for you.

Note how the next 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.

DBInterface class *

Get the Rec or Delete it

Next, is the section of code used to retrieve a record or to delete one. The first function, getRecord(), returns one record, if any, using the primary key field value 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 referenced in, _dbInt. It’s this Map object that lists all the fields of all the tables found in the current database, but the particular table of interest is specified to the function using its first parameter, table. If there is an error, an empty Map object is returned in a List.

The third function in this code below we’ve already visited. It’s the delete() function and returns the number of rows (i.e. records) that are deleted. Of course, in most cases you’ve a normalized data table, and so that number should always be one as it searches by the primary key field. If something goes wrong, an empty List object is returned instead.

Again, when dealing with this SQFlite plugin, sqflite, you’re dealing with Future objects, and so, all the functions are returning Future objects as well.

DBInterface class *

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 SQL Select options. You can see the second function, getTable(), merely calls the third function, query() supplying the list of fields associated with the named table.

DBInterface class *

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 SQLite. In this case, one to list the table names found in the database, and one to list the field names of a specific data table.

DBInterface class *

Record An Error

The next two functions are to record any exceptions that may occur. Notice how the setError() function will record it as an exception even if the passing object is not one. The getError() returns that exception but not before ‘clearing it’ first. Ready now to receive any future exceptions that may occur. Finally, notice they’re both static functions. That means the record any error having to do with databases. Even if there are multiple instances of the class.

DBInterface class *

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 failed.

Note: This implies, that with every database operation, you could check the getters, inError or noError, if the last database operation preformed was successful or not.

DBInterface class *

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.

DBInterface class *

The Helper

The ‘library-private’ class, _DBInterface, indeed does the heavy lifting actually performing all the database operations. You’ll recognize the parameters in its constructor as those in the class library’s constructor.

DBInterface class *

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. A getter called, localpath, uses the standard Flutter function called, getApplicationDocumentsDirectory(), to determine the database’s actual location on your phone. Finally, there’s the function call, _tableFields(), to collect the table names that reside in that database as well as their respective field names. All into their appropriate Map objects.

_DBInterface class *

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 an integer value.

_DBInterface class *

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.

_DBInterface class *

Delete the Record

The next function deletes a record. It returns the number of records deleted. In most cases that will be an integer of one. It will return a value of zero, if the record is not found or, for some reason, a database fails to open.

_DBInterface class *

The Two Queries

The next two function, rawQuery() and query() are called by the class library, DBInterface inside try..catch statements. However, in both cases, it’s the SQFlite plugin, db, that actually performs the queries. The second function, query(), passes on to the SQFlite plugin the many options traditionally available to an SQL Select statement.

_DBInterface class *

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 data table respectively.

_DBInterface class *

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 _keyFields, with all the field names of the tables contained in the database as well as their respective key field names.

Lastly, it fills up a Map object of Map objects called, _newRec, containing the ‘empty’ values of each table. Again, used primarily when a brand new record of a particular table is to be created.

_DBInterface class *

Conclusion

The intent of this article is to highlight how you can use a Dart package to take a little of the effort out of working with databases in your Flutter apps. It’s a consistent approach to manipulate data tables and their records. The package is intended to expand in the future and not only work with SQLite but with any future plugins accessing the many other types of databases out there — rational or otherwise.

Cheers.

* Source code as of July 07, 2019

→ Other Stories by Greg Perry

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade