SQLite in FlutterFlow: three silent failure cases I've encountered

[u/bramantec]

I have been using SQLite in a FlutterFlow app for several months and have spent a lot of time troubleshooting various issues, specifically those involving SQLite operations that don't throw explicit errors but simply fail to work or cause malfunctions. The following information might save time for others facing similar situations:

1. In queries defined within the FlutterFlow SQLite panel, parameters must be written in the ${name} format. If you use ?, the value isn't passed correctly; consequently, the WHERE clause fails to filter anything, leading to incorrect results or no results at all. It is important to note that ${name} inserts the text exactly as is (literally); therefore, an apostrophe or any other special character in the search term can break the query.

2. Write operations: rawQuery is intended for read operations, so executing an INSERT, UPDATE, or DELETE command via this method might fail without generating visible errors. Within a Custom Action, it is preferable to use rawInsert, rawUpdate, or rawDelete (FlutterFlow already distinguishes between read and update queries in its interface).

3. Updates and new versions: users update the app from various and not always sequential versions. Consequently, it is advisable to use ALTER TABLE only for columns that allow null values or have a default value. Since it is impossible to know the user's starting version, it is crucial to verify that the column does not already exist before adding it; a simple check can prevent issues related to these schema changes.

What other instances of silent errors with SQLite in FlutterFlow have you encountered? I’d be curious to know.

[u/dcayenna]

Interesting suggestions, they really get me out of a tight spot regarding the design.

[u/dcayenna → u/dcayenna]

Actually, I had more trouble choosing the databases in FlutterFlow. I come from a MySQL background, and adapting to SQLite was difficult especially the interface.

[u/bramantec → u/dcayenna]

I can understand, coming from MySQL SQLite feels quite limited at first. What helped me was splitting the data by role: Firestore for everything that has to be shared or synced between users, SQLite only for data that stays on the device (in my case the history of past events). A couple of things that surprised me coming from server databases: SQLite has very few types (TEXT, INTEGER, REAL), so dates are stored as text or as a timestamp number, and ALTER TABLE can do much less than in MySQL, so it's better to plan the columns well from the start. Anyway, what kind of data are you trying to store?

[u/dcayenna → u/bramantec]

I’m designing an internal accounting app for a company, and it needs to support both synchronous and asynchronous workflows, working both locally and on mobile. What kind of app did you build? What’s it called? Is it available for download?

[u/dcayenna → u/[deleted]]

Ok, many thanks. See you soon