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 data

Lab 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

GRANT USAGE ON SCHEMA schemaname TO <user>;
GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO <user>;

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

select * from experiments.runs where project = 'speaker-fluency-project';

If you also want to restrict by experiment

select * from experiments.runs where project = 'speaker-fluency-project'
    and experiment='experiment3'

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.

with A as (select jsonb_array_elements(data) as trialdata 
    from experiments.runs where randomid = '_7cma0mr67el')
    select * from A where 
    (trialdata->>'block') = '1' and (trialdata->>'exp_phase') = 'test';

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