Using SQLite in FlutterFlow

FlutterFlow has supported Firebase and other cloud based data stores for ages now, and more recently has added App State variable supports for data so as to make app development more straightforward. These can be used with very effective cacheing but there are many cases where completely offline and local data storage capabilities would be useful. Our mobile apps that pre-date FlutterFlow have used SQLite for years since it is built-in to both iOS and Android phones and is a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is highly stable, and used in countless mobile apps you use all the time so it is a very welcome addition to FlutterFlow.

What can you do with SQLite in FlutterFlow?

FlutterFlow provide a working example note taking app that demonstrates the SQLite functionality, So this article will implement it and build it onto a real device to check out the functionality.

To get started open the example project from FlutterFlow and clone it into your own FlutterFlow environment https://app.flutterflow.io/project/note-taking-app-zto2ua

TL:DR — SQLite is an important addition to the FlutterFlow toolbox so lets build out the example app for ourselves and see how well the functionaluty is integrated

Enabling SQLite

To enable SQLite just turn it on in FlutterFlow > Settings and Integrations > App Settings > Integrations > SQLite

The functionality is intentionally limited but of production quality and for what it provides it is fully featured. You'll need to upload a SQLite database file to your project. Remember this is for local data, so a good use case is for data that can be long lived enough to be updated only when your app is itself updated. You'll need to consider carefully what happens on app update as you can also create, read, update and delete data inside your app. Theres no sync in the implementation in FlutterFlow, and theres no web support, so you'll need to test on real devices, but if you're serious about mobile apps you should be dong that anyway.

Designing and editing SQLite Databases

You'll see that the Database Configuration is already set in our newly cloned project.

To set up a new SQLite database or inspect a SQLite databse requires tools. "DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite. DB4S is for users and developers who want to create, search, and edit databases. DB4S uses a familiar spreadsheet-like interface, and complicated SQL commands do not have to be learned". (https://sqlitebrowser.org/). It is also cross platform so you should have no probelm installing it in your environment. I use macOS and Homebrew so % brew install --cask db-browser-for-sqlite is the command to install it for me.

Go ahead and launch it and see how easy it is to create a SQLite database.

Creating a database in DB Browser for SQLite

• Click "New Database", and give the database a meaningful name, like NoteTakingAppDB then click "Save"
• Enter the name for your table Notes and then click "Add" to add each field
• This example table has ID, as an Integer, not null, primary key and auto increment. Use the checkboxes to select these attributes.
• Add Title and Details as text fields and then DueDate and IsCompleted, again as integers.
• Click "Browse Data" and then the "Insert Row" button and populate the row with data, remembering to click 'Apply', then "Save"
• Now upload the database to your Database Configuration in FlutterFlow (You can see it already in the screenshot above).
  
  

Create, Read, Update, Delete (CRUD) of records via FlutterFlow in your SQLite database

FlutterFlow separates CRUD actions into two sections, Reading and Updating. Reading is a SQL SELECT query of all or specific columns like the infamous SELECT * from EMP somewhere near page one of the Oracle DBA1 training course.

Updating covers INSERT, UPDATE and DELETE for new, changed and removed records respectively.

In FlutterFlow, as usual, you pass variables using the syntax ${variableName} from your app to the scripted queries, and for Read queries you define the output columns too.

e.g. SELECT * FROM EMP WHERE salary = ${empSalary}

in the example note app, there are just a few queries to show you how to create them and wire them into your app.

GetAllNotes

This query gets all the notes from the database, ready for you to use in your User interface. SELECT * FROM Notes;

AddNote

This query adds a note record to the database using the variables title, details and dueDate:
INSERT INTO Notes (Title, Details, DueDate, IsCompleted) VALUES ('${title}', '${details}', ${dueDate}, 0); 

UpdateNote

This query updates an exisitng note record in the database using the variables title, details, dueDate, isCompleted, and id

UPDATE Notes
SET 
    Title = '${title}',
    Details = '${details}',
    DueDate = ${dueDate},
    IsCompleted = ${isCompleted}
WHERE ID = ${id};

DeleteNote

This query deletes an exisitng note in the database usinf the  id.
DELETE FROM Notes WHERE ID = ${id};

Building your User Interface

Show all Notes

Create a FlutterFlow page using the Listview, and containers, or just examine the example page.

• Add a SQLite backend query (only read queries will be available) and click Confirm
• For each widget in your Listview, open the 'Set from Variable' and select your query Row and then the column data you want to be displayed in the widget. Repeat for each field you wish to display from the database record.
  
  Note — A custom function in the example provided by FlutterFlow converts the UNIX epoch value aka timestamp stored in SQLite into a human readable date for display in the widget.

UNIX epoch value (timestamp)

Add a Note

Adding a note is accomplished using a FlutterFlow SQLite Query Action

• Select the Widget for which you want to add the action

• Open an Action Flow Editor and click on "+ Add Action"

• Select the SQLite Query action under Backend/Database

• Select Update Query and choose "AddNote"

Update a Note

Updating an existing note is accomplished using a FlutterFlow SQLite Query Action

• Select the Widget for which you want to add the action

• Open an Action Flow Editor and click on "+ Add Action"

• Select the SQLite Query action under Backend/Database

• Select Update Query and choose UpdateNote

Delete a Note

Adding a note is accomplished using a FlutterFlow SQLite Query Action

• Select the Widget for which you want to add the action

• Open an Action Flow Editor and click on "+ Add Action"

• Select the SQLite Query action under Backend/Database

• Select Update Query and choose DeleteNote

Pros and Cons of SQLite in FlutterFlow

Overall, SQLite is an excellent choice for mobile apps with modest database requirements, because it can function without a data connection, completely offline.