Database backend
Information about the lab's database backend
Basics
The lab's database is a high performance managed database cluster at Digital Ocean. It's backed-up daily and configured to automatically switch data handling to a standby node if the original node fails.
It's highly secure and can only be accessed directly by trusted sources or by lab members at https://childlanglab.retool.com/apps/database via two-factor authentication. Lab members are only granted access to the data for projects on IRB protocols to which they have been added.
Access
Lab members
Lab members access the database via a custom frontend on ReTool. Learn to access our database by following the Onboarding Tutorial below:
4. Working with lab dataLab manager
The lab manager also has read-only access to the database backend in order to perform backups. To grant a user read-only access, do the following for each schema in the database
Backup
The database is automatically backed up nightly by Digital Ocean, but it is good practice for us to also manually back it up monthly.
Overview of backup process
To perform the backup, you'll need access to the full database, which is not available in ReTool. Instead, you'll use TablePlus.
Connect to the database
If this is not your first time connecting to the database, you can skip to Perform the backup
If this is your first time accessing the database via TablePlus, you'll need to create a connection to the database. Open TablePlus and click create a new connection...
On the next screen, select the Import from URL
button on the bottom.
Then you'll need to enter the connection URL shared with you via LastPass.
On the next screen, you can change the Name
parameter to childlanglab-data
and then you must change the SSL mode
to REQUIRED.
After this, select Test
to test the connection to the database. Everything will turn green if the connection was successful. Click Save
to save this connection.
If your connection was not successful, create a Bug on the Bug Tracker and assign it to Katie. The most common reasons for failed connection is that your IP address has not been added to the database's list of trusted sources.
From now on, you will be able to connect to the database by selecting this connection from TablePlus.
Perform the backup
To backup the database, open TablePlus and select the Backup
option on the left, underneath.
Select the database, then defaultdb, then click Start backup...
Save the backup as YYYY-MM-DD-backup
and click Start backup
.
You will see the Dump database
screen. Dump database means to dump the entire database into the backup file you created. The backup can take up to several minutes to complete. When complete, you will see " Backup database complete" in the text box. Click OK
when finished.
Queries
Most often used in ReTool to create our database frontend.
Get all runs
The following gets all of the runs from the speaker fluency project
If you also want to restrict by experiment
Get a single run
Get data from single run
Get subset of data from run
Sometimes we want to reach into the data
column of a run and filter by particular properties. For example, here we are getting the data for a particular participant's run that is in block 1 of the test phase.
Add data to jsonb
Usually when we code data, we take the json from the data column and add coded data to it; then update the run's coded_data
column with the new data
Last updated