supabase - only option?

[dylanpieper]

Hello! I love this development and had a question/suggestion as someone who would like to use a package like this someday in production. Is supabase the only option? To reach a wider audience, I suggest a bypass for the supabase integration for users who want to use their database, regardless of the platform or technology they decide to use. Maybe that's already part of the plan, but I wanted to check. Keep up the fine work!

[jhelvy]

Thanks for the feedback! And yes, in general this is something I think we could support in the future. We've modularized the overall architecture such that the database is configured using a separate sd_database() function, which is then passed to the sd_server() function. One major motivation for this was that in the future it should help make it easier to support other types of databases. For now, we're still just trying to get a fully functioning version up and running that is well-tested and well-documented. Supabase seems like a pretty easy setup for users with limited database experience, and so far it's working quite well. We also attempted to use Googlesheets (similar feel to Google Forms), but the authentication is just so terribly annoying that we abandoned it.

[dylanpieper]

Thank you for such a fast response! That makes sense. Based on my reading of those functions, dropping in several SQL options should be pretty straightforward. I'm thinking of RMySQL / RMariaDB. If I have some time, I will try testing it on my MySQL server.

[jhelvy]

Yeah the code for updating the database is relatively simple. The shiny app keeps a named list of all of the survey question ids and their values based on user responses. The goal is to send the data to a database on every user interaction (e.g. each time they click on an option in a multiple-choice question). That way the data is always up to date with whatever interaction the respondent has had with the survey. So all we're doing is checking the row in the database for the session_id (unique to each survey session) and updating the row if it already exists or creating it if it doesn't yet exist.

The db.R file contains the main sd_database() function, which is primarily a setup function. All it does is try to connect to the database. If successful, it returns the connected db object. Updating the database row happens in the database_uploading() function.

It's pretty simplistic right now and fairly brittle. Like, if the user changes anything in the survey (e.g. changes the name of a question id, or adds a new question, etc.), the database breaks. For now we're assuming that a user will set up a database at the very end of designing everything, so they can just define a table_name and then make it live. But if they change anything after that point, the best thing to do is just choose a new table_name and it will make a new table in the database. @Buneabt is currently working on updating this so that the same table can be used and new columns just get appended to the end.

[dylanpieper]

This is unrelated, but I thought you might find it interesting or valuable. Our team is exploring options for survey packages/libraries to use with Shiny. We are highly considering using the SurveyJS library as the most mature open-source project we can find. I put an example script on GitHub Gist to showcase an example. The integration worked out really well. it made me think that a similar approach could be used. Quarto (for generating the JSON) + Shiny + SurveyJS.

[jhelvy]

This is a great idea, and it was something I looked into originally. I even referenced it in my original blog post when I was trying to think of ways to pull together this package. Ultimately, I went for the simplest approach of just using Shiny input widgets for now just to get a proof of concept up and running. I wasn't even sure if the idea of leveraging Quarto would work. But now that I know it can, I'd be curious to see if SurveyJS could be swapped in place. I don't see why not. The surveydown::question() function could be used to generate the json, and the rest would be using some relatively boilerplate code to bring it all together into a shiny app. Database backend would more or less be the same. It would be a pretty big fork from what we're doing now, but it's still a relatively young package so perhaps do-able. I'm by no means a web developer though, so this would probably require more work that I can commit for now.

[dylanpieper]

As of now, I've opted not to clone or fork this repository and leverage the Quarto-to-JSON feature, given that SurveyJS provides a comprehensive free survey creation tool that produces the JSON. Instead, I've built a straightforward app framework that integrates Shiny with SurveyJS. This framework can handle multiple surveys and dynamically update fields, on top of the existing functionalities of SurveyJS (like survey logic, etc.): https://github.com/dylanpieper/ShinySurveyJS. The backend setup is still underway, but feel free to review the current status. I appreciate the opportunity to share and discuss my ideas here!

[jhelvy]

Very cool! I just tried running a survey locally and couldn't get it to work, but I must be doing something wrong. In any case, the idea of defining the whole survey as a simple json file is very attractive, and that's something I wanted to originally achieve with the quarto approach. But the way the quarto doc works (at least when running it as a shiny app) is that it renders to a static html page that forms the UI of the app. It seems quite different in nature and probably difficult to integrate with SurveyJS. That said, the fact that you can design a survey using SurveyJS's website and then just export the json, drop it into a folder, and have a shiny app version of it is very attractive.

The biggest distinction with that approach compared to what I'm trying to achieve with surveydown is that it doesn't allow me to design a survey in plain text / code. My goal is to be able to write some markdown and define some questions in plain text and have that render to a survey app. And since I can also render code, I could do some interesting / dynamics things, like make a ggplot that shows the respondent their choice(s) on a question compared to all prior respondents (e.g. by just pulling in the data with R code and making a plot). That should all be (I think) rendered in real time with a quarto-based shiny app, so I imagine there are use cases where things could get pretty creative. Anyway, looks like you're doing some pretty cool stuff! If you figure out an efficient backend to store the data somewhere flexibly, let me know as I could perhaps port over some of those ideas.

[dylanpieper]

Absolutely, if you are encountering a blank page when starting the app, it's because there's no default JSON to fall back on. To start a survey, you need to define one in the query parameter of the URL (e.g., ?survey=dynamicSurvey).

I'm also quite impressed with your idea of rendering everything in real time using a quarto-based shiny app. This could certainly open doors to a whole range of creative use cases. It's clear that you're onto something exciting!

Keep up the good work! I'm looking forward to seeing how your project evolves.

[jhelvy]

Absolutely, if you are encountering a blank page when starting the app, it's because there's no default JSON to fall back on. To start a survey, you need to define one in the query parameter of the URL (e.g., ?survey=dynamicSurvey).

I tried the query parameter the first time and it didn't work, but tried again and now it does! I don't know what I did wrong first time, but it works great now.

[jhelvy]

Hey, just bringing this back up to say that we've made a lot of changes since your original post. We now have a much more comprehensive documentation site: surveydown.org. I'm curious if you'd like to give it a try and have any thoughts? We also have some demos of different features if you want to try them out.

As for alternative databases, we're still using supabase by default as this gives users an option to get started without having to set anything up themselves. Under the hood, the sd_database() function is just making a pool connection to a postgres database, then returning the connection and table name as a list (see here). I suspect that any other database that you have access to could work the same way. For example, you should be able to make your own db object like this:

# Pool connection to database
pool <- pool::dbPool(
    RPostgres::Postgres(),
    host = 'host',
    dbname = 'dbname',
    port = 'port',
    user = 'user',
    password = 'password'
)

db <- list(db = pool, table = 'table')

where you replace the parameters with your database configuration parameters, and provide a 'table' name for the table where you want to store your data. If you use this db object inside sd_server() at the end of the server code chunk (bottom of the survey qmd file), it should work and connect to your database.

[dylanpieper]

This is so cool! 😀 Congrats on the quick dev. This is by far the best survey package for R that I've seen to date. This is much closer to something useable for our organization. I will test it out (changing the db connection) and get back to you. Quick question: What is your advice for using shiny session data? For example, what if I wanted to pass the URL search query to my QMD for an extra layer of logic e.g., query <- parseQueryString(session$clientData$url_search)?

Sorry for the repeat question, I just found this discussion (https://github.com/orgs/surveydown-dev/discussions/92).

[dylanpieper]

New thought...(sorry for piling on here - we can start a new issue/discussion if it makes sense). The last big need I would have is to be able to have the flexibility to custom insert/update to different tables, because we do utilize the relational aspect of the RDB design, rather than having a seperate table for each survey. For example, we have a project table (project_id, name, etc.), demographics table (subject_id, project_id, race, age, etc.), and a survey table. I'm sure I could customize the server to do this. I know not all entity-relationship models are the same, and people will want to design them differently, so I can understand not trying to implement it. Just throwing it out there!

[jhelvy]

The last big need I would have is to be able to have the flexibility to custom insert/update to different tables

Yeah this is something we probably wouldn't try to implement within surveydown. Our goal is to make it easy for users to define their survey with plain text (markdown & code chunks), so our development focus is the functionality and features rather than the database backend. I also feel like this could probably be handled from a separate set of code after data collection. Like, you could pull from a main table with all the survey responses and create other tables according to the desired structure you want.

And yes, going forward if you have other thoughts / suggestions please post them in the discussion (or issues if it's an actual bug report). Just saw that you found the discussion about url parameters - we just added that and will be posting up some documentation on it soon.

[warnes]

@jhelvy Perhaps you could consider the table generated by shinydown as a 'staging' table and write code to extract the data from it and add/update the data to your custom tables.

[jhelvy]

@warnes Yes this is what I meant in my reply above. Basically, surveydown generates a single table of all the survey responses, and then users can do anything they need with that resulting table, like creating other custom tables. This can all be done outside of surveydown.