Skip to contents

Deploying learnrTrackR with PostgreSQL

Source: vignettes/deployment-postgresql.Rmd
deployment-postgresql.Rmd

Scope

This guide describes a small PostgreSQL deployment rehearsal for learnrTrackR. It is suitable for local testing, a teaching prototype, or a conversation with technical staff. It is not a complete production deployment or institutional authentication design.

SQLite remains the simplest backend for local demonstrations. In this package, PostgreSQL is the intended next step when several users may write to the same tracking database.

Local Docker PostgreSQL

The package includes a Docker Compose example:

system.file(
  "examples/postgres-docker",
  package = "learnrTrackR",
  mustWork = TRUE
)

From an installed package, copy that directory to a working location. From the package source tree, it is available at:

inst/examples/postgres-docker/

Create the local environment file:

cd inst/examples/postgres-docker
cp env.example .env

Edit .env and replace LEARNRTRACKR_POSTGRES_PASSWORD with a long random password. Then start PostgreSQL:

docker compose --env-file .env up -d

Run the smoke test:

Rscript smoke-test.R

The smoke test creates the tracking schema, inserts one demo student, one demo tutorial, registered questions, and one attempt, then prints a gradebook row.

Stop the service:

docker compose --env-file .env down

Delete the local data volume only when the test data can be discarded:

docker compose --env-file .env down -v

Connecting from R

Install the optional PostgreSQL dependency:

install.packages("RPostgres")

Then connect to an existing PostgreSQL database:

library(learnrTrackR)

con <- connect_postgres_tracking_db(
  dbname = Sys.getenv("LEARNRTRACKR_POSTGRES_DB", "learnrtrackr"),
  host = Sys.getenv("LEARNRTRACKR_POSTGRES_HOST", "127.0.0.1"),
  port = as.integer(Sys.getenv("LEARNRTRACKR_POSTGRES_PORT", "5432")),
  user = Sys.getenv("LEARNRTRACKR_POSTGRES_USER", "learnrtrackr"),
  password = Sys.getenv("LEARNRTRACKR_POSTGRES_PASSWORD"),
  initialize = TRUE
)

load_tracking_config(con, "config")

For an already initialized database, use initialize = FALSE. The function will still verify that the required tracking tables exist.

Testing with a real PostgreSQL server

The unit test suite includes an optional PostgreSQL integration test. It is skipped by default. To run it, set LEARNRTRACKR_TEST_POSTGRES_DSN to a test database connection string:

export LEARNRTRACKR_TEST_POSTGRES_DSN="postgresql://learnrtrackr:password@127.0.0.1:5432/learnrtrackr"
Rscript -e 'devtools::test(filter = "db")'

Use a disposable test database. The test creates and drops a temporary schema.

Dashboard

The exported data preparation function dashboard_data(con, ...) works with an open DBI connection. The interactive dashboard can also use an open DBI connection:

run_dashboard_connection(
  con,
  tutorial_id = "module_01",
  group_id = "A",
  access_token = Sys.getenv("LEARNRTRACKR_DASHBOARD_TOKEN")
)

For a PostgreSQL launch based on LEARNRTRACKR_POSTGRES_* environment variables, use:

run_dashboard_postgres(
  postgres_schema = Sys.getenv("LEARNRTRACKR_POSTGRES_SCHEMA"),
  tutorial_id = "module_01",
  group_id = "A",
  access_token = Sys.getenv("LEARNRTRACKR_DASHBOARD_TOKEN")
)

Minimum operational safeguards

Use PostgreSQL only in a controlled environment. At minimum:

  1. Use a database dedicated to learnrTrackR.
  2. Use a database user dedicated to the application.
  3. Store only the student identifiers required for teaching operations.
  4. Avoid committing .env files, passwords, database dumps, or student exports.
  5. Restrict network access to the PostgreSQL service.
  6. Back up the database before using it for a real course.
  7. Document who can access the dashboard and exported CSV files.

PostgreSQL documents standard libpq environment variables such as PGHOST, PGDATABASE, PGUSER, and PGPASSWORD. The PostgreSQL documentation warns that environment-variable passwords can be exposed on some systems and suggests password files as an alternative. Docker Compose uses a YAML file to define services, networks, and volumes. The official postgres container image uses environment variables such as POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD when initializing a container.

References