This article was written on Pragma Engine version 0.0.94.

Using Database Storage for Custom Services #

In the third article of this series, we’ll go over the different ways you can enable database storage for a custom service in Pragma Engine.

Choosing how to store custom service data #

Depending on the type of data you need stored, it’s recommended that you model your custom service’s data storage as inventory data and build your custom service on top of our existing inventory service. This method is incredibly useful if your custom service needs to work with player data, since all inventory features inherently have access to all player data systems.

If you need a more customized solution, Pragma Engine supports data storage for your custom services via Data Access Objects (DAOs). Any time you need to access a database (whether it’s from a custom or provided service), you’ll need to use a DAO node. These objects are built by creating a custom DaoNodeService class in a new (but alongside your original) custom service Kotlin file, and usually you’ll want to name this file [CustomServiceName]DaoNodeService.kt.

Example: Use a DAO to store any data in our database that needs to persist during Pragma service reboots and cache clears.

Choosing how to store custom service data in Pragma Engine.

Pragma Engine uses a MySQL database–which in turn uses DaoNodeServices–to house persistent data from databases such as accounts, social services, and player inventory services. By creating your own custom service’s DAO classes following the same pattern as Pragma Engine, you’ll maintain consistency and reliability with your own services and the rest of the engine.

Extending a Dao Node service #

Before we walk through how to create a custom DaoNodeService class, let’s go over the two different ways you can extend your DAO for your custom service: by using the UnpartationedDaoNodeService, or the PartitionedDaoNodeService.

You’ll see how these two different types of DAO extensions impact a custom DaoNodeService class in the section below. But before you design any custom DAO, make sure you consider whether your custom service needs an unpartitioned or partitioned database.

Building a custom Dao Node service #

To get started, create the Kotlin file for your custom DaoNodeService in the same service directory as your custom service (5-ext/ext/src/main/kotlin/yourcustomproject). In this file, create the DaoNodeService class and have it inherit either the UnpartitionedDaoNodeService or PartitionedDaoNodeService class.

When creating a custom DaoNodeService, there are two methods that must be overridden from the parent DaoNodeService class in your custom DaoNodeService.kt file: changelogFilepath (a Liquibase SQL script) and getDatabaseConfigFrom (a config file). You’ll also need to include the SharedDatabaseServiceConfig dependency in the service using the DaoNodeService. We’ll explain how to override these functions with your specific and unique script and config logic in the next section.

In the example below, you’ll see a CustomDaoNodeService class defined as an UnpartitionedDaoNodeService and the two required override functions.

    class MyCustomTechBlogDaoNodeService(
    pragmaNode: PragmaNode,
    databaseValidator: DatabaseValidator = pragmaNode.databaseValidator,
) : UnpartitionedDaoNodeService<MyCustomDaoConfig>(pragmaNode, databaseValidator) {
    override fun changelogFilepath() = "db-changelogs/mycustom.sql"
    override fun getDatabaseConfigFrom(serviceConfig: MyCustomDaoConfig): UnpartitionedDatabaseConfig =
        serviceConfig.databaseConfig
}

If you’re customizing a PartitionedDaoNodeService, you’ll need to have an additional override value with a key to hash on. playerId is an example of a possible hash key.

     override val nameOfPropertyToConsistentlyHashOn: String
        get() = "[key to hash on]"

Overriding the log file path #

changelogFilepath is a function used for directing a class’s path for its SQL migration script. Whenever you create a custom DaoNodeService class, you’ll need to override this function and update its SQL script path with the service’s own custom one, which you’ll need to also create and define.

    override fun changelogFilepath() = "db-changelogs/mycustom.sql"

You should create your SQL scripts in 5-ext/ext/src/main/resources/db-changelogs/ with any relevant changesets. All files in this directory are included in the packaged .jar and used throughout Pragma Engine to apply database migrations on startup.

Each SQL script is defined differently depending on if it is for a partitioned or unpartitioned service. Additionally, since you’ll be defining tables in these files, remember not to rely on auto-increment keys when selecting data from multiple tables.

If you’re defining an unpartitioned custom service’s SQL script, go to 5-ext/ext/src/main/resources/db-changelogs/, create a new file with the .sql extension, and define the script’s logic in said file. It should look like the example below, which runs two separate changesets in succession (the first one creates the table mycustom, and the second one modifies the mycustom table). Remember that each changeset represents a separate migration step, and on engine startup or when the database migrate command is executed, Pragma Engine verifies each changeset has been executed on every database configured using the specific SQL file.

 --liquibase formatted sql

--changeset pragma-engineering-team:1
CREATE TABLE `mycustom` (
    ....

--changeset pragma-engineering-team:2
ALTER TABLE `mycustom`
    MODIFY `customdata` VARCHAR(256) NOT NULL;
    ...

If you’re defining a partitioned custom service’s SQL script, create the .sql file in the same /db-changelogs directory and define your script’s logic. It should look something like the code block below.

 CREATE TABLE `mycustom` (
  ...
  `playerId` binary(16) NOT NULL,
  ...
  KEY `playerId`

If you’re defining a SQL script for a partitioned service, it’s a good idea to add the column for the hash in the SQL script.

Overriding the database config source #

getDatabaseConfigFrom() is used for returning a serviceConfig that includes all of the database configurations for your service. This service config, labeled using your custom service (such as CustomServiceDaoConfig.kt), should be created and defined in your custom service’s directory (5-ext/ext/src/main/kotlin/mycustomproject/).

When designing the code you write for your DaoConfig.kt, it should look something like the code block below (this specific example is written for an unpartitioned service’s DaoConfig):

class MyCustomTechBlogDaoConfig private constructor(type: BackendType) : ServiceConfig<MyCustomTechBlogDaoConfig>(type) {
    override val description = "Configuration for the MyCustomTechBlogDaoConfig"

    var databaseConfig by types.embeddedObject(UnpartitionedDatabaseConfig::class, "database config for the custom dao")

    companion object : ConfigBackendModeFactory<MyCustomTechBlogDaoConfig> {
        override fun getFor(type: BackendType) {}
    }
}

If you’re creating a partitioned custom service’s DaoConfig, make sure your custom service has the same list of hostPortSchemas as your config and define the hostPortSchemas map like the code block below. Additionally, make sure you include several hostPortSchemas instead of a single hostPortSchema for partitioned services.

hostPortSchemas = configMapOfPrimitiveOf(
  "1" to "localhost:3306/local_game_inventory1",
  "2" to "localhost:3306/local_game_inventory2",
)

When defining the hostPortSchemas of a partitioned service, be sure not to skip any numbers of the map and avoid swapping them once they’ve been created. Making any changes to these numbers can corrupt user data and missing any can cause database errors.

Overriding YAML configs for your custom Dao config #

Once you’ve fleshed out the custom DaoNodeService.kt file and defined the database’s SQL script and DaoConfig.kt, you can override your DaoConfigs for different dev environments in your YAML configurations. Though this is optional, it’s a great tool for separating persistent data for testing from persistent data for production. All you’ll need to do is author your DaoConfigs in a way that leaves certain configuration options open for our YAML configs to read and map specific properties.

Below you’ll find two examples of social unpartitioned and partitioned custom services configured in Pragma Engine’s YAML configs.

 social:
  serviceConfigs:
    # Collections of database configurations
    SharedDatabaseServiceConfig:
        testIdentifier:
            username: "superuser"
            password: "password"
            host: "${databaseHost}"

    # Partitioned Custom Service
    MyCustomTechBlogDaoConfig:
      databaseConfig:
            identifierSchemas:
                1:
                  identifier: "TechBlogIdentifier"
                  schema: "localhost:3306/local_game_mycustom1"
                2:
                  identifier: "TechBlogIdentifier"
                  schema: "localhost:3306/local_game_mycustom2"


    # Unpartitioned Custom Service
    MyCustomTechBlogDaoConfig:
      databaseConfig:
            identifierSchema:
                identifier: "TechBlogIdentifier"
                schema: "localhost:3306/local_game_mycustom"

Never put the username or password info of your production database within a Kotlin config file. Instead, encrypt the values and place them within your production YAML config file.

And that’s how you create a database storage for a custom service! In the next article, we’ll go over how to activate and integrate your custom service with Pragma Engine’s provided services and RPC flow.

For more information, check out the rest of the articles in this series:

Part I: Introduction to Custom Services
Part II: Creating a Custom Service
Part III: Using Database Storage for Custom Services (this article)
Part IV: Integrating Custom Services with Provided Services