SQLite

SQLite is a compact, efficient database management system. Unlike conventional databases that require a server, SQLite is serverless and embeds directly into applications.

It's perfect for mobile apps where resources are limited, and a full-fledged database server is impractical. For example, it's ideal for a mobile app that needs to store data locally, such as a personal finance tracker or a health record app, especially when offline functionality is required.

Currently, we don't support SQLite on Web-based apps.

Let's understand how you can utilize SQLite in your app with an example. An app where users can add, update, and delete Notes. Here's how it looks when completed:

Here are the steps to build such an example:

1. Enable SQLite

2. Database configuration

3. Add SQL queries

4. Display all notes

5. Add note

6. Update note

7. Delete note

8. Running app on a device

9. Example project

1. Enable SQLite

To enable SQLite in FlutterFlow, navigate to Settings and Integrations > Integrations > SQLite > switch on the Enable SQLite toggle.

Enable SQLite

2. Database configuration

In the database configuration step, you'll need to upload your SQLite database file and assign a name to it. This process is crucial for initializing the database when your app launches.

If you don't yet have an SQLite database, you can easily create one using tools like sqlitebrowser. Simply download sqlitebrowser, create a new database, set up your tables, and optionally add some data. After preparing your database, upload the file to FlutterFlow to integrate it with your app.

For this example, we'll create a "Notes" table with ID, Title, Details, DueDate, and IsCompleted as columns.

Here's how you can create and configure the database:

It's important to note that SQLite does not have dedicated date-time or boolean data types. For storing date-time values like 'DueDate', we use the integer data type and represent the date-time as a UNIX timestamp. Similarly, for boolean values, such as checking if a note is completed, SQLite uses integers where '0' represents 'false' (or not completed) and '1' represents 'true' (or completed).

3. Add SQL queries

SQL queries are statements used to interact with a database. We allow you to add queries in two different sections:

1. Read Queries

This includes statements that retrieve data from the database but do not modify anything. Some common examples:

• SELECT * FROM customers; - retrieve all rows and columns.

• SELECT name, city FROM customers; - retrieve specific columns.

• SELECT * FROM customers WHERE city = 'New York'; - retrieve rows that match a condition.

2. Update Queries

This includes statements that modify the database, such as:

• INSERT INTO customers (name, address, city) VALUES ('John', '555 Main St', 'New York') - add new rows.

• UPDATE customers SET address = '123 Main Street' WHERE name = 'John' - update existing rows.

• DELETE FROM customers WHERE city = 'Chicago' - delete rows that match a condition.

In general, to add any query, you need to provide a name, the query statement, and variables that are used to pass values from your app to queries. For Read Queries, you have to define the output columns as well. This will help you display the row data in the UI by selecting the column name.

Tips:

• To use variables, simply use the syntax ${variableName}. For example: SELECT * FROM Notes WHERE id = ${noteId}

• When passing string or text data in queries, enclose variables in single quotes, like '${title}', to signify them as strings.

Below are the queries that we'll require for this example:

1. GetAllNotes

This will retrieve all notes from the database.

Select * from Notes

2. AddNote

This will add a new note to the database.

INSERT INTO Notes (Title, Details, DueDate, IsCompleted) VALUES ('${title}', '${details}', ${dueDate}, 0);

3. UpdateNote

This will update the existing note based on the note ID.

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

4. DeleteNote

This will delete the note based on the note ID.

DELETE FROM Notes WHERE ID = ${id};

4. Display all notes

To show a list of notes, you can use the ListView > Container widgets to design a page that looks like the following:

Page for displaying a list of notes

Now, on the ListView widget, add a SQLite backend query and display data in UI elements.

In our example, the due date is stored as a Unix timestamp, which isn't user-friendly for display purposes. Therefore, we've included a custom function in the example project that converts this timestamp into a human-readable date format.

5. Add note

You can add a new note in the database using the SQLite query action with the type set to Update Query and Query Name to AddNote.

Here's how you do it:

6. Update note

For updating note values, like marking a note as completed or modifying other fields, utilize the SQLite query action and set the type to Update Query. Here, set the Query Name to UpdateNote.

• In this example, we are updating the note on a bottom sheet component. To provide a better user experience, we initially display the current values of the note, ensuring that users have a clear idea of what they are going to edit. To display the note values in bottom sheet, we pass the current note with Type set to SQLite Row.

  
• When updating a date value, we also verify if the date has been modified. If there's no change, we simply pass back the same value we received.

Here's how you do it:

7. Delete note

You can delete an existing note from the database using the SQLite query action with the type set to Update Query and Query Name to DeleteNote.

Prot tip: To refresh the page, simply add an update App State action action with the Update Type set to 'Rebuild Current Page'.

Here's how you do it:

8. Running app on a device

Download and test the app using the instructions here.

9. Example project

Here, you have the complete example project.

FAQs

Can SQLite handle complex data structures compared to App State Variables?

Yes, SQLite can handle complex data structures much more effectively. It allows for structured data storage, complex queries, sorting, and filtering, which are challenging to implement with app state variables.

Is SQLite a good choice for apps that require offline functionality?

Absolutely. SQLite stores data locally, making it an excellent choice for apps that need to operate offline. Users can access and manipulate data without needing an internet connection.

Will using SQLite affect my app's performance compared to using App State Variables?

SQLite is designed to be lightweight and efficient, so it generally won't negatively impact your app's performance. In fact, for larger data sets, it's more efficient than storing data in app state variables.

How does SQLite ensure data security and integrity?

SQLite maintains data integrity and supports transactional operations. This means it ensures the database state remains consistent even in cases of unexpected interruptions, like app crashes or power failures.

---

Published Date: January 12, 2024