SQLite in Flutter

Demonstrating the Dart package, dbutils.

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.

Installing

I don't always like the version number 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:

dependencies:
  dbutils:^5.0.0

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

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.

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.

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,