Adding database storage #

You can add data storage for a custom service using a custom Data Access Object (DAO) service. To add data storage, extend one of the DAO Node Services classes.

Pragma Engine uses a MySQL database to maintain service data. To remain consistent with the new custom service and Pragma Engine, create a DAO Node Service class.

Your custom DAO can extend from two different kinds of DaoNodeService classes:

  • UnpartitionedDaoNodeService

    • Represents a single database.
    • Useful for centralized data that doesn’t need a lot of scaling.
  • PartitionedDaoNodeService

    • Uses a collection of databases to distribute data for scaling.

Extend a Dao Node service #

To extend an existing DaoNodeService class, you must overwrite the following methods in the parent class: changelogFilepath (a Liquibase changelog file) and getDatabaseConfigFrom (a config file). You must also include the SharedDatabaseConfigServiceConfig dependency in the service using the DAO Node Service. Partitioned services also need an override with a key to hash on, such as playerId.

For a detailed example of an unpartitioned Dao Node service, see the Arbiter leaderboard service.

The following procedure walks you through how to set up a service that extends the partitioned and unpartitioned classes:

  1. Override the change log filepath To override changelogFilepath, your service must return a path to a SQL migration script.
    1. Create a SQL script in 5-ext/ext/src/main/resources/db-changelogs/. Pragma Engine packages files in 5-ext into a .jar, and uses them to apply database migrations on startup.
    2. Add any relevant change sets to the script.
    3. For partitioned services, add a hash key column.
Example: Unpartitioned SQL script
--changeset engineering-team:1
CREATE TABLE `mycustom` (
    ....

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

Each changeset represents a separate migration step.

Example: Partitioned SQL script
CREATE TABLE `mycustom` (
  ...
  `playerId` binary(16) NOT NULL,
  ...
  KEY `playerId`

When selecting data from multiple tables, don’t rely on auto-increment keys.

  1. Override getDatabaseConfigFrom and add the DaoConfig

To override getDatabaseConfigFrom, add the DaoConfig Kotlin file to your custom service directory. For partitioned services, you must include multiple hostPortSchemas. If you are following along in the Arbiter leaderboard service this is found in the ArbiterLeaderboardDaoConfig.kt file.

Example: Unpartitioned custom service database configuration class
class MyCustomServiceDaoConfig private constructor(type: BackendType) : ServiceConfig<<MyCustomServiceDaoConfig>(type) {
    override val description = "Configuration for the MyCustomServiceDaoConfig."

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

    companion object : ConfigBackendModeFactory<MyCustomServiceDaoConfig> {
        override fun getFor(type: BackendType) = MyCustomServiceDaoConfig(type).apply {
            databaseConfig = UnpartitionedDatabaseConfig.getFor(type)
        }
    }
}
Example: Partitioned custom service database configuration class
class MyCustomServiceDaoConfig private constructor(type: BackendType) : ServiceConfig<MyCustomServiceDaoConfig>(type) {
    override val description = "Database configuration for myCustomService"

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

    companion object : ConfigBackendModeFactory<MyCustomServiceDaoConfig> {
        override fun getFor(type: BackendType) = MyCustomServiceDaoConfig(type).apply {
                databaseConfig = PartitionedDatabaseConfig.getFor(type).apply {
                    columnNameOfPropertyToConsistentHash = "playerId"
                }
            }
        }
    }
}
  1. Create the custom Dao Node service.

If you are following along in the Arbiter leaderboard service this is found in the ArbiterLeaderboardDaoNodeService.kt file.

  1. Create a Kotlin file in the custom service directory.
  2. Inherit the UnpartitionedDaoNodeService or PartitionedDaoNodeService class.
  3. Add the required changelogFilepath and getDatabaseConfigFrom overrides.
Example: Unpartitioned custom database service
@PragmaService(
    backendTypes = [BackendType.GAME],
    dependencies = [SharedDatabaseConfigNodeService::class]
)
class MyCustomServiceDaoNodeService(
    pragmaNode: PragmaNode, 
    databaseValidator: DatabaseValidator = pragmaNode.databaseValidator
    ) : UnpartitionedDaoNodeService<MyCustomServiceDaoConfig>(pragmaNode, databaseValidator) {

    companion object {
    ...
    }
    
    override fun getDatabaseConfigFrom(serviceConfig: MyCustomServiceDaoConfig ): UnpartitionedDatabaseConfig = serviceConfig.databaseConfig

    override fun changelogFilepath(): String = "db-changelogs/mycustom.sql"
Example: Partitioned custom database service
@PragmaService(
    backendTypes = [BackendType.GAME],
    dependencies = [pragma.databases.SharedDatabaseConfigNodeService::class]
)
class MyCustomServiceDaoNodeService(
    pragmaNode: PragmaNode,
    databaseValidator: DatabaseValidator = pragmaNode.databaseValidator
    ) : PartitionedDaoNodeService<MyCustomServiceDaoConfig>(pragmaNode = pragmaNode, databaseValidator = databaseValidator) {
    
    companion object {
        ...
    }

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

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

    override fun getDatabaseConfigFrom(serviceConfig: MyCustomServiceDaoConfig): PartitionedDatabaseConfig {
        return serviceConfig.databaseConfig
    }
    
    ...
  1. Implement the database service in your custom service.

For more information about setting up a custom service see, Creating a custom service. If you are following along in the Arbiter leaderboard service this step is found in the ArbiterLeaderboardService.kt file.

@PragmaService(
    backendTypes = [BackendType.GAME],
    dependencies = [MyCustomServiceDaoNodeService::class]
)

internal class MyCustomService(
    pragmaNode: PragmaNode,
    instanceId: UUID
) : DistributedService(pragmaNode, instanceId) {

    private lateinit var myCustomServiceDaoNodeService: MyCustomServiceDaoNodeService

    override fun run() {
        myCustomServiceDaoNodeService = nodeServicesContainer[MyCustomServiceDaoNodeService::class]
    }
  1. Add your database details to you service configuration.
serviceConfigs:
    # Collections of database configurations
    SharedDatabaseConfigServiceConfig:
        databaseConfigsByIdentifier:
            testIdentifier:
                username: "superuser"
                password: "password"
                host: "${databaseHost}"
    
    # PartitionedDaoNodeService configuration
    MyCustomServiceDaoConfig:
        databaseConfig:
            identifierSchemas:
                1:
                  identifier: "testIdentifier"
                  schema: "test_customservice1"
                2:
                  identifier: "testIdentifier"
                  schema: "test_customservice2"

    # UnpartitionedDaoNodeService configuration
    MyCustomServiceDaoConfig:
        databaseConfig:
            identifierSchema:
                identifier: "testIdentifier"
                schema: "test_customservice"