Support other data formats when database is not used

[rgmerck]

Description

I like the fact that the package falls back on flat file to save the results when there is no database connection. In the spirit of supporting easy deployments in complete isolation (where no connection to the external network is possible), it would be great to support SQLite as another option. It's well supported in R with DBI and RSQLite packages so it should not be a major amount of work to implement it, I believe.

Also, even if you stay with flat files, I would recommend against csv as a file format, since it's especially bad when it comes to non-English languages support. If you can give options such as xlsx (with writexl package) or feather (with arrow package), this may be more portable formats at this stage.

[jhelvy]

The data.csv file was not intended as a fall back - it's just there so you can preview your results when testing locally. In fact, it only stores one row - if you refresh the page, it just over-writes that row again. It shows you what the results from one respondent will look like.

Perhaps we should emphasize this in the docs, as we certainly wouldn't want anyone to run their survey live and think that there is some data.csv file storing all the results (it wouldn't be!).

As for supporting SQLite, we could consider that, but it's probably going to be put further back on the priority list. Right now using supabase you can have free, robust service that stores your data, and it's pretty easy to set up. And if you have any other PostgreSQL database available, you can use that too, so users have a good deal of flexibility in storing data already. So we'll keep fixing other things before adding support for a secondary service. I'll leave this ticket open though. Happy also to accept a PR if anyone wants to give this a shot.

[rgmerck]

Thanks. I have experience with SQLite, I may give it a shot at some point if you don't get there first.

[jhelvy]

As of v0.5.0 we now save the local data as preview_data.csv, which is entirely intended for previewing purposes (hence the file name). When ready for fielding, users should use a supabase database to store the data (or any other PostgreSQL database).