Comment on page
Database
Data from your web experiment can be stored in exbuilder's PostgreSQL database.
exbuilder's database is initialized with a schema called
experiments and a single table called runs , because this is the structure we use in my lab. exbuilder's database is initialized via the init.sql file inside the database directory. The runs table has 13 columns:
column_name | data_type | default | intended for |
id | int8 | nextval() | auto-generated run id |
daterun | timestamp | now() | auto-added timestamp |
randomid | varchar | | anonymized participant id created by exbuilder |
participant | varchar | | participant id from recruitment database |
project | varchar | | name of exbuilder project |
experiment | varchar | | name or id of experiment |
condition | varchar | | name or id of condition |
researcher | varchar | | name or id of researcher who ran the participant |
sourcedb | varchar | | name of id of recruitment database |
location | varchar | | location of run (e.g. online, lab) |
data | jsonb | | json formatted data |
exclude | bool | | whether to exclude the run, to be updated after run |
notes | text | | any notes about the run, to be updated after run |
To help you write to the
experiments.runs table, the experiments/exbuilder directory comes with some php and javascript already setup to do this for you. You only need to take three steps. First, you need to initialize the Exbuilder object (part of exbuilder.js ) somewhere at the beginning in your experiment's javascript code.Exbuilder.init();
Then, insert a new row into the database for each participant by adding:
Exbuilder.insertRun();
You normally only insert a run once per run of the experiment (e.g. once per participant). After the run has been inserted, you can update the data column with the following, either once per experiment or after every trial.
Exbuilder.updateRun(data);
Where
data is json formatted data. You can read more about this on the Experiments project component page, or on the reference sheet for exbuilder.jsYou can enter the container directly and use the database query tool
pgsl to access exbuilder's database. With the container running, use:docker exec -it postgres psql -U exbuilder
where
postgres is the service name and exbuilder is the user. Here are some useful commands once you've entered the database with the above.\q # exit the psql shell
\l # list available databases
\dt # list available tables
\d table_name # describe a table (column, type, etc)
\du # list all users and their assigned roles
SELECT * FROM exbuilder.runs; # any SQL statements (for example)
If you prefer something that behaves more like a spreadsheet, exbuilder will also work with any database management tool that supports PostgresSQL. Table Plus is a nice, free option that we use in my lab. Select
New Connection, select PostgreSQL, and enter your database configuration:
New connection in Table Plus
Below, we show how each of theses values maps on to your project's
.env variables. Host/Socket: DB_HOST
Port: DB_PORT
User: DB_USER
Password: DB_PASSWORD
Database: DB_NAME
SSL mode: DB_SSLMODE
Finally, you can communicate with the database and send queries directly from your analysis scripts. We've included some sample code below for how to do this in R, Julia, and Python.
#load the tidyverse, but be quiet about it
options(tidyverse.quiet = TRUE)
library(tidyverse)
#load the libraries for working with the database
library(DBI)
library(RPostgreSQL)
library(getPass)
library(tidyjson)
#set the db driver to postgres, because that is what the exbuilder's database is
pgdrv <- dbDriver(drvName = "PostgreSQL")
# connect to our database
db <-DBI::dbConnect(pgdrv,
dbname="exbuilder",
host="postgres",
port=5432,
user = 'exbuilder',
password = "password") # default is password
# replace password = "password" with password = getPass("Enter password: ")
# if you want to enter a password instead.
# pass SQL queries to the database with dbGetQuery
rawdata <- dbGetQuery(db, "SELECT * FROM experiments.runs")
# turn json data into a table with tidyjson package
data <- rawdata %>%
as.tbl_json(json.column="data") %>% gather_array %>% spread_all %>%
as_tibble()
Coming soon...
Coming soon...
We use the
init.sql file to initialize exbuilder's database with the structure we use in my lab. We've refined this structure over several years and find this approach to be the right combination of simple and powerful. Before you change it, allow me to explain why we like it so much (or skip to the how-to)First, by storing the data for each run in a single column as a json object, exbuilder's database structure works for every experiment in our lab. We like this because: (1) we don't have to make decisions about what the database should be like for every experiment and (2) we can post all data to a single table in the lab's external database, so all of our data is housed in this one (very safe) place.
Second, we don't store any personally identifying information (PII) in our database. Instead, we rely on recruitment databases to house these details, storing only the participant's id number from that database to link them. This is important because we can conform to our University's (annoying but necessary) requirements for PII, but still choose to maintain our database anyway we like.
Still, an important feature of exbuilder is that it is completely customizable. You can change the database structure if you like. You can choose to have a single structure for all of your exbuilder projects or customize the database anew for every new project -- it's up to you.
Changing the database is as simple as changing the
init.sql file. To illustrate, we added a column age_group to the database (line 11 below). -- create the schema
DROP SCHEMA IF EXISTS experiments;
CREATE SCHEMA experiments;
-- create the runs table
CREATE TABLE IF NOT EXISTS experiments.runs (
id BIGSERIAL PRIMARY KEY,
daterun TIMESTAMP DEFAULT NOW(),
randomid VARCHAR,
participant VARCHAR,
age_group VARCHAR, # here we added a column called age_group
project VARCHAR,
experiment VARCHAR,
condition VARCHAR,
researcher VARCHAR,
sourcedb VARCHAR,
location VARCHAR,
data JSONB,
exclude BOOLEAN,
notes TEXT
);
You might be wondering why we start the
init.sql file with this line. DROP SCHEMA IF EXISTS experiments;
We do so because we want the database we use for local development to start fresh each time we start the container. When we deploy our experiment to a server, we use exbuilder deployer's database, which does not include this
DROP SCHEMA command.In my lab, we store all of our data in our external database: a managed database from DigitalOcean. We have just a single table called
runs that holds every run of every experiment in the lab. We have a second table called projects where we keep track of information about each exbuilder project we've started.Deployed experiments connect to our external database to store data from participants. To connect to this database, when we create our
.env file for the project on the server, we set our database-relevant environment variables to this external database. For example, here is a sample database from DigitalOcean:
Sample DigitalOcean database
To connect to this database, you would set your
.env variables for the project to the following on your server. Note that you'd also need to add your server as a trusted source on DigitalOcean.# configure your database;
DB_HOST: example-database-do-user-6607903-0.a.db.ondigitalocean.com
DB_PORT: 25060
DB_NAME: defaultdb
DB_USER: example-user
DB_PASSORD: <your password here>
DB_SSLMODE: require
Note: it is unlikely that you'd want to connect to a production database like this while developing your experiments locally. Set the
.env variables to your external database settings only for experiments deployed on your server. Last modified 2yr ago