Databases

Introduction

With TRAX LRS, you can choose between 3 scenarios to store your data:

Several database drivers are supported by TRAX LRS, depending of your preferred scenario, including MySQL, PostgreSQL, TimescaleDB, MongoDB, Elasticsearch and OpenSearch, with some specificities to know about TimescaleDB and Elasticsearch/OpenSearch.

All these drivers guarantee a perfect partition into multiple stores, but with a different technique depending of whether the database is a relational database (MySQL, PostgreSQL, TimescaleDB) or a NoSQL database (Elasticsearch, OpenSearch).

Scenario 1: single database

All the data is stored in a single database, including the application data and xAPI data. MySQL and PostgreSQL are supported.

First, create an empty database (utf8mb4_unicode_ci encoding for MySQL).

Then, configure the database in the .env file.

Example with MySQL:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=trax3
DB_USERNAME=root
DB_PASSWORD=

Example with PostgreSQL:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=trax3
DB_SCHEMA=public
DB_USERNAME=postgres
DB_PASSWORD=aaaaaa

As you can see, the PostgreSQL configuration has an optional setting which is DB_SCHEMA. Its default value is public. For TimescaleDB, use the PostgreSQL driver as TimescaleDB is just an extension of PostgreSQL.

Finally, run the following command:

php artisan database:install

Scenario 2: isolated xAPI database

The application has its own database and the xAPI data is stored in a separated database. The application database supports MySQL and PostgreSQL. The xAPI database supports MySQL, PostgreSQL, MongoDB, Elasticsearch and OpenSearch.

First, create the desired empty databases (utf8mb4_unicode_ci encoding for MySQL).

Then, configure the databases in the .env file. Here is an example:

Application database with PostgreSQL:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=trax3
DB_SCHEMA=public
DB_USERNAME=postgres
DB_PASSWORD=aaaaaa

xAPI database with MongoDB:

XAPI_DB_DRIVER=mongodb
XAPI_DB_HOST=127.0.0.1
XAPI_DB_PORT=27017
XAPI_DB_DATABASE=trax3
XAPI_DB_USERNAME=
XAPI_DB_PASSWORD=

The DB_CONNECTION variable can take the mysql and pgsql values. The XAPI_DB_DRIVER variable can take the mysql, pgsql, mongodb, elasticsearch and opensearch values. For TimescaleDB, use the pgsql driver as TimescaleDB is just an extension of PostgreSQL.

Finally, run the following command:

php artisan database:install

Scenario 3: one database per service

In this scenario, the application core has its own database, and each independent service has its own database.

The application core and the following services support MySQL and PostgreSQL:

  • The auth service
  • The vocab service
  • The logging service

The xAPI services support MySQL, PostgreSQL, MongoDB, Elasticsearch and OpenSearch:

  • The statements service
  • The activities service
  • The agents service
  • The activity profiles service
  • The agent profiles service
  • The states service
  • The dev service (for testing)

First, create the desired empty databases (utf8mb4_unicode_ci encoding for MySQL).

Then, configure the databases in the .env file. Here is an example of complex configuration:

Application, vocab and auth in the same PostgreSQL schema:

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=trax3
DB_SCHEMA=main
DB_USERNAME=postgres
DB_PASSWORD=aaaaaa

Logging in an isolated PostgreSQL schema:

LOGGING_DB_DRIVER=pgsql
LOGGING_DB_HOST=127.0.0.1
LOGGING_DB_PORT=5432
LOGGING_DB_DATABASE=trax3
LOGGING_DB_SCHEMA=logging
LOGGING_DB_USERNAME=postgres
LOGGING_DB_PASSWORD=aaaaaa

xAPI data (except statements) isolated in a MongoDB database:

XAPI_DB_DRIVER=mongodb
XAPI_DB_HOST=127.0.0.1
XAPI_DB_PORT=27017
XAPI_DB_DATABASE=trax3_xapi
XAPI_DB_USERNAME=
XAPI_DB_PASSWORD=

Statements isolated in a second MongoDB database:

STATEMENTS_DB_DRIVER=mongodb
STATEMENTS_DB_HOST=127.0.0.1
STATEMENTS_DB_PORT=27017
STATEMENTS_DB_DATABASE=trax3_statements
STATEMENTS_DB_USERNAME=
STATEMENTS_DB_PASSWORD=

As you can see, if you want to define a specific database for a service, you just have to add a set of settings named SERVICE_DB_SETTING, where SERVICE should be the name of the service and SETTING should be the name of a setting.

The DRIVER setting can take the mysql and pgsql values for the application database, the logging, vocab and auth services. It can take the mysql, pgsql, mongodb, elasticsearch and opensearch values for the xAPI databases. For TimescaleDB, use the pgsql driver as TimescaleDB is just an extension of PostgreSQL.

Finally, run the following command:

php artisan database:install

TimescaleDB

TimescaleDB is "PostgreSQL for time series and events". It is an extension of PostgreSQL which optimizes queries based on timestamps. So this is a perfect use case for the statements table where the stored column can be used for time-based queries.

If you want to use TimescaleDB, you must set the following configuration before running the migration:

STATEMENTS_DB_TIMESERIES=true

Or, if all your xAPI data is store in the same database:

XAPI_DB_TIMESERIES=true

With this option, the primary key of the statements table will be a combination of the id, store and stored columns. The presence of the stored column in the primary key is required to create an hypertable on the statements table.

Be aware that this change may have an impact on the behavior of the statements API. Indeed, if you disable Check ID conflicts in the xAPI pipeline and insert several statements with the same ID, all these statements will be stored separately as their stored column will defer.

Elasticsearch / OpenSearch

By default, when Elasticsearch or OpenSearch are used, all the database requests are synchronous. This means that the database responds to the application when the query is completely processed. This behavior is required to pass the ADL Conformance Test Suite. However, if you don't care about the ADL Conformance Test Suite and want to improve performances, you can choose the asynchronous mode of Elasticsearch and OpenSearch with the DB_ASYNC setting.

For example with the statements database:

STATEMENTS_DB_ASYNC=true

Partition into multiple stores

All the xAPI tables have a store column which contains the key of the store.

With MySQL, PostgreSQL and TimescaleDB, the application uses the store column to distinguish data from different stores. That's why there are always composite primary keys relying on the id and store columns.

With MongoDB, Elasticsearch and OpenSearch, the store column is also present. However, the primary key only relies on the id column (or _id) and each store is recorded in a different collection (MongoDB) or index (Elasticsearch / OpenSearch).

So for example, if you have 2 stores named store1 and store2, you should have collections (MongoDB) or indexes (Elasticsearch / OpenSearch) such as xapi_statements_store1 and xapi_statements_store2.