surveydown::sd_db_config()Storing Data
Survey response data is stored in a PostgreSQL database. We recommend using Supabase as a free and open-source option, though you can use any service you want. In this guide, we’ll walk you through the steps for setting up a Supabase project and connecting your surveydown survey to it.
Local data preview
When you run a survey without a database connection, response data will be locally stored in a preview_data.csv file. As the name suggests, this is for preview purposes only. It is there to help you understand what the actual survey response data will look like once you get your database connection properly configured.
This file is not where your actual survey response data will be saved. If you deploy your survey live without a database connection, no responses will be saved.
Setting up a Supabase project
First, navigate to the Supabase website and create an account.
Once you are logged in, the page will prompt you to create a project (it’s a green button). Click on it and select your organization. A dialog box will pop up like this:
Fill in the project name and give it a strong password. Choose a region that is close to you (or close to your survey audience). All settings can be modified at any time.
Note
Each Supabase project is a database that can store multiple tables. Since each surveydown survey requires only one table, you can use the same Supabase project for multiple surveydown surveys.
Getting your Supabase credentials
Once your Supabase project is ready, click on the “connect” button at the top, it should look like this:
On the connection page, scroll down to the “Transaction pooler” section. There you can click on the “View parameters” drop down to see your connection parameters. It should look somethinglike this:
You will need these parameters and your password to connect to your database in surveydown.
Storing your database credentials
Before connecting to your database, you need to store your credentials. You can do this by running the following code in your R console:
This function will prompt you to enter your database credentials and password, one by one. The current credential values will be shown in square brackets. When done it should look like this:
Once you have entered your credentials, the function will store them in a .env file in your project folder. We strongly recommend that you do not manually edit this file or share it with others as it stores all of your database credentials, including your password.
Modifying credentials
If you want to modify your credentials stored in the .env file, you can just run sd_db_config() again and press ‘Enter’ on any parameter you want to leave unchanged while modifying the ones you want to change.
You can also pass any of the parameters as arguments to sd_db_config() to change them. For example, if you wanted to only change the table name, you could do this:
sd_db_config(table = 'mytable')Once run in the R console, a message will print out confirming that the stored table parameter will now be mytable.
You can pass any of the following as arguments to update them: host, dbname, port, user, table, and password.
Finally, you can also view / modify your database credentials in the surveydown dashboard app. To do this, launch the dashboard by running this command in the R console:
surveydown::sd_dashboard()This will open a new browser window where you can navigate to the dashboard for your project. Click on the “Connection Settings” tab to see and edit your database credentials. Once you have made changes, click on the “Test Connection” button to save your updated credentials.
See the Local Dashboard page for more information.
Connecting to your database in surveydown
Now that you have set your credentials stored, you can connect to your database in surveydown by running the following code in your app.R file:
db <- sd_db_connect()You do not need to specify any arguments to this function as it will automatically use the credentials stored in your .env file. If the connection is successful, you should see a message in the console that says “✔ Successfully connected to the database.”
You can also ignore the database connection by setting ignore = TRUE in sd_db_connect():
db <- sd_db_connect(ignore = TRUE)With this setting, no database connection will be attempted, and the db object will store the value NULL. Instead, data will be stored in a local preview_data.csv file for previewing purposes only. This is useful when you are still editing your survey and do not want to store any data in your database yet.
Table creation and data operations
You never need to manually create a table in the database - it gets automatically created after you first run the survey. After you first set up the config and a .env file is properly created, run your survey locally and click past the first page, then you should see the table get created in the database. You can view it directly on supabase (if you’re using supabase), or you can see the table using the dashboard by running sd_dashboard().
The data gets updated in the database on every page turn (each time you push a next button) and after you close the browser, which is usually at the end of the survey, but just in case you close it early it will write to the database then too. This is why you usually have to click past the first page after initial configuration to see the table in the database.