Comment on page
Data from your web experiment can be stored in exbuilder's PostgreSQL database.
exbuilder's database is initialized with a schema called
experimentsand a single table called
runs, because this is the structure we use in my lab. exbuilder's database is initialized via the
init.sqlfile inside the
The runs table has 13 columns:
To help you write to the
Exbuilderobject (part of
Then, insert a new row into the database for each participant by adding:
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.
You can enter the container directly and use the database query tool
pgslto access exbuilder's database. With the container running, use:
docker exec -it postgres psql -U exbuilder
postgresis the service name and
exbuilderis 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
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)
#load the libraries for working with the database
#set the db driver to postgres, because that is what the exbuilder's database is
pgdrv <- dbDriver(drvName = "PostgreSQL")
# connect to our database
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 %>%
We use the
init.sqlfile 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.sqlfile. To illustrate, we added a column
age_groupto 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(),
age_group VARCHAR, # here we added a column called age_group
You might be wondering why we start the
init.sqlfile 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
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
runsthat holds every run of every experiment in the lab. We have a second table called
projectswhere 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
.envfile 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
.envvariables 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_PASSORD: <your password here>
Note: it is unlikely that you'd want to connect to a production database like this while developing your experiments locally. Set the
.envvariables to your external database settings only for experiments deployed on your server.